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;
   }
}
17
  • 1
    What issue you facing? Feb 5, 2015 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) Feb 5, 2015 at 11:29
  • Can you please specify absolute path for excel file instead of just file name? then run your program. Feb 5, 2015 at 11:35
  • i tried putting absolute path but it doesn't work,i am facing same issue. Feb 5, 2015 at 11:44
  • Can you please check your POI Jars? make sure that is not corrupted. Feb 5, 2015 at 11:54

0

Browse other questions tagged or ask your own question.