Wednesday, 29 May 2013

create multiple sheets in excel via java code



package com.ReadAndWriteExcel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Vector;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.XLSX.ExcelXLSX;

public class Main {
static XSSFWorkbook xssfWorkbook;
static XSSFSheet xssfSheet;

public static void main(String[] args) throws IOException {
int numOfSheet=3;
Vector vectorDataExcelXLSX = new Vector();
Vector<Vector> ParentVector = new Vector<Vector>();
String excelXLSXFileName = "C:\\task\\masterxls\\Config.xlsx";
Workbook wb = new XSSFWorkbook();
FileOutputStream fos = new FileOutputStream("Master.xlsx", true);
File file = new File("C:\\task\\modules");
String[] str = file.list();
String[] SheetNames ={"Test Cases","Test Steps","Test Data"};
for (int var = 0; var < numOfSheet; var++)
{
for (String st : str)
{
vectorDataExcelXLSX = ExcelXLSX.readDataExcelXLSX("C:\\task\\modules\\" + st, var);
ParentVector.add(vectorDataExcelXLSX);
ExcelXLSX.PrintInConsoleDataExcelXLSX(vectorDataExcelXLSX);
}
ExcelXLSX.writeExcelFile(ParentVector, wb, SheetNames[var]);
ParentVector.clear();
}
System.out.println("Excel file has been generated!");
wb.write(fos);
fos.close();
}
}

Read And write Excel:------>

 public static Vector readDataExcelXLSX(String fileName,int SheetNumber) {
    Vector vectorData = new Vector();
        try {
            FileInputStream fileInputStream = new FileInputStream(fileName);
            XSSFWorkbook xssfWorkBook = new XSSFWorkbook(fileInputStream);  
            // Read data at sheet 0
            XSSFSheet xssfSheet = xssfWorkBook.getSheetAt(SheetNumber);    
            Iterator rowIteration = xssfSheet.rowIterator();  
            // Looping every row at sheet 0
            while (rowIteration.hasNext()) {     
                XSSFRow xssfRow = (XSSFRow) rowIteration.next();  
                Iterator cellIteration = xssfRow.cellIterator();  
                Vector vectorCellEachRowData = new Vector();    
                // Looping every cell in each row at sheet 0
                while (cellIteration.hasNext())
                {
                XSSFCell xssfCell = (XSSFCell) cellIteration.next();
                    vectorCellEachRowData.addElement(xssfCell);
                }              
                vectorData.addElement(vectorCellEachRowData);
            }        
        } catch (Exception ex) {    
        ex.printStackTrace();
        }
        return vectorData;
    }


public static void writeExcelFile(Vector<Vector> vectorData, Workbook wb,String sheetName) throws IOException {
Row row;
Cell cell;
String[] cellvalue = null;
        int t=0;
       Sheet sh=wb.createSheet(sheetName); 
for (int i = 0; i < vectorData.size(); i++) {
Vector vectorCellEachRowData = (Vector) vectorData.get(i);
for (int j = 0; j < vectorCellEachRowData.size(); j++) {
String str1=vectorCellEachRowData.get(j).toString().replace("[","");
cellvalue =str1.split(",");
row = sh.createRow((short) t);
                                t++;
for (int k = 0; k < cellvalue.length; k++) {
cell = row.createCell((short) k);
cell.setCellValue(cellvalue[k].replace("]",""));
}
}
System.out.println();
}
}

IMP NOTE---->
Dont Forget to attach POI Jars for XSSf(5 Jars)

No comments:

Post a Comment