1

I am writing a function which accepts array data as input,and according to the size of array I am filling to excel sheet. Also let me know is there any method get column size of array as array.leght gives row size. please check the function to write data into excel

public class xls_read {
   public String[][] read_xlsx(String filepath, String tab)throws Exception {
        try{                                                                        // create a new "excel" file with location"filename"
        FileInputStream input_document = new FileInputStream(new File(filepath));// link "excel" to "program"as InputStream
        XSSFWorkbook xlsx_workbook = new XSSFWorkbook(input_document);// connect the excel using InputStream
        XSSFSheet xlsx_worksheet = xlsx_workbook.getSheet(tab);// connect to the tab of excel
        int rownum = xlsx_worksheet.getLastRowNum();
        int colnum = xlsx_worksheet.getRow(0).getLastCellNum();
        String[][] data = new String[rownum][colnum];
        for (int i = 0; i < rownum; i++) {
            Row row = xlsx_worksheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < colnum; j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        try {
                            data[i][j] = cell.getStringCellValue();
                        } catch (IllegalStateException e) {
                            data[i][j] = new BigDecimal(cell.getNumericCellValue()).toPlainString().toString().trim();
                        }
                    }
                }
            }
        }
         return data; 

        }
        catch(Exception e){
            System.out.print("ERROR OCCURED WHILE READING EXCEL"+filepath+e);
        }
        return null;

 }
   public String[][] write_xlsx(String[][] input,String filename,String tabname,int col) {
       System.out.println("input data lenght"+input.length);

       try {
           File excel = new File("E:\\output.xlsx");
           System.out.print("\t"+filename+"\t"+tabname+"\t"+col+input.length+"col"+col+"\n");
           XSSFWorkbook workbook = new XSSFWorkbook();
           XSSFSheet sheet= workbook.createSheet();

           // XSSFCell myCell = null;
            String excelData[][] = input;
            System.out.println("lenght excel data"+excelData.length);
            XSSFRow myRow1 = sheet.createRow(input.length);

            /*for (int cellNum = 0; cellNum < col; cellNum++) {
                myCell = myRow1.createCell(cellNum);
                myCell.setCellValue(excelData[0][cellNum]);

            }*/
            XSSFCell myCell = myRow1.createCell(col);
        //    myCell.setCellValue(value)

            for (int i = 0; i < input.length; i++) {
                for (int j = 0; j < col; j++) {
                    // = myRow1.createCell(i);
                    myCell.setCellValue(input[i][j]);
                    System.out.println(">>"+excelData[i][j]);
                }
            }
                FileOutputStream out = new FileOutputStream(filename);
                workbook.write(out);
                out.close();
                System.out.println("report genrated..");
            } catch (Exception e) {
                e.printStackTrace();
            }
        return null;
   }
}

closed as off-topic by corsiKa Apr 18 '15 at 3:02

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions about software development unrelated to testing are off-topic here, but can be asked on Stack Overflow." – corsiKa
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What issue you facing? – Helping Hands Feb 5 '15 at 11:03
  • am getting error as, java.lang.IllegalStateException: Zip File is closed at org.apache.poi.openxml4j.util.ZipFileZipEntrySource.getEntries(ZipFileZipEntrySource.java:45) at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:186) at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:673) – Rajesh Rathod Feb 5 '15 at 11:29
  • Can you please specify absolute path for excel file instead of just file name? then run your program. – Helping Hands Feb 5 '15 at 11:35
  • i tried putting absolute path but it doesn't work,i am facing same issue. – Rajesh Rathod Feb 5 '15 at 11:44
  • Can you please check your POI Jars? make sure that is not corrupted. – Helping Hands Feb 5 '15 at 11:54