HI All, POI Version used : 3.8 Stable and poi-3.9-beta1-20120422, poi-3.9-beta1-20120628 (POI nightly builds from Jenkins) Below is the sample code. SXSSFWorkbook workbook = new SXSSFWorkbook(50); workbook.setCompressTempFiles(true); SXSSFSheet worksheet=null; String header = ""; CellStyle cellStyle = workbook.createCellStyle(); Font font = workbook.createFont(); Font fontd = workbook.createFont(); ArrayList rowdata = null; String data = ""; SXSSFCell cell=null; CellStyle cellStyle1=null; SXSSFRow row=null; cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.index); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.WHITE.index); font.setFontHeightInPoints((short)10); font.setFontName("Arial"); cellStyle.setFont(font); fontd.setColor(IndexedColors.BLACK.index); fontd.setFontHeightInPoints((short)10); fontd.setFontName("Arial"); System.out.println("Matrix Report noofrows::"+noofrows); for(long i = 0,rowNo=1 ; i < noofrows;i++,rowNo++){ worksheet = (SXSSFSheet) workbook.createSheet("Matrix Report "+sheetNo); row = (SXSSFRow) worksheet.createRow(0); for(int hl=0; hl<headerLength; hl++){ cell = (SXSSFCell) row.createCell(hl); header = (String)matrixReportHeader.get(hl); cell.setCellValue(header); cell.setCellStyle(cellStyle); cell=null; } row = (SXSSFRow) worksheet.createRow((int)rowNo); rowdata = (ArrayList)matrixReportdata.get((int)i); cellStyle1 = workbook.createCellStyle(); if(rowNo%2 != 0){ cellStyle1.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index); cellStyle1.setFillPattern(CellStyle.SOLID_FOREGROUND); } cellStyle1.setFont(fontd); for(int j=0; j<rowdata.size(); j++){ cell = (SXSSFCell) row.createCell(j); data = (String)rowdata.get(j); if(data==null) data=""; cell.setCellValue(data); cell.setCellStyle(cellStyle1); } }
Current code is invalid, it repeatedly inserts the header row in a loop and this is why you see a blank row in the output. Move creation of the header row outside of the main loop and you will be good.
Hi Yegor Kozlov, I have put the header row inside main loop because. In my case no of row is more than what xlsx can support. I am checking if it more than xlsx can support then create a new worksheet and then continue writing the cell value.
here is the actual code... int sheetNo=1; if(rowNo==1048575) { rowNo=1; sheetNo++; } if(rowNo==1) { worksheet = (SXSSFSheet) workbook.createSheet("Matrix Report "+sheetNo); row = (SXSSFRow) worksheet.createRow(0); for(int hl=0; hl<headerLength; hl++){ cell = (SXSSFCell) row.createCell(hl); header = (String)matrixReportHeader.get(hl); cell.setCellValue(header); cell.setCellStyle(cellStyle); } } i need to create the header in the next worksheet also, that why header is in the main loop.
please post the full Java code that I can run and see the problem. I still think the problem is in your code but without a working example it is hard to tell where the problem is.
Here is the full code that i am using.. try { int headerLength = 15; int noofrows = 2048575; SXSSFWorkbook workbook = new SXSSFWorkbook(50); workbook.setCompressTempFiles(true); SXSSFSheet worksheet=null; String header = ""; CellStyle cellStyle = workbook.createCellStyle(); Font font = workbook.createFont(); Font fontd = workbook.createFont(); String data = ""; SXSSFCell cell=null; CellStyle cellStyle1=null; SXSSFRow row=null; int sheetNo=1; cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.index); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.WHITE.index); font.setFontHeightInPoints((short)10); font.setFontName("Arial"); cellStyle.setFont(font); fontd.setColor(IndexedColors.BLACK.index); fontd.setFontHeightInPoints((short)10); fontd.setFontName("Arial"); System.out.println("Matrix Report noofrows::"+noofrows); for(long i = 0,rowNo=1 ; i < noofrows;i++,rowNo++){ if(rowNo==1048575) { rowNo=1; sheetNo++; } if(rowNo==1) { worksheet = (SXSSFSheet) workbook.createSheet("Matrix Report "+sheetNo); row = (SXSSFRow) worksheet.createRow(0); for(int hl=0; hl<headerLength; hl++){ cell = (SXSSFCell) row.createCell(hl); header = hl+""; cell.setCellValue(header); cell.setCellStyle(cellStyle); cell=null; } } row = (SXSSFRow) worksheet.createRow((int)rowNo); cellStyle1 = workbook.createCellStyle(); if(rowNo%2 != 0){ cellStyle1.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index); cellStyle1.setFillPattern(CellStyle.SOLID_FOREGROUND); } cellStyle1.setFont(fontd); for(int j=0; j<headerLength; j++){ cell = (SXSSFCell) row.createCell(j); data = j+""; if(data==null) data=""; try{ cell.setCellValue(Long.parseLong(data)); }catch(NumberFormatException e){ try{ cell.setCellValue(Double.parseDouble(data)); }catch(NumberFormatException ee){ cell.setCellValue(data); }catch(NullPointerException ee){ cell.setCellValue(data); } }catch(NullPointerException e){ cell.setCellValue(data); } cell.setCellStyle(cellStyle1); cell=null; data=null; } cellStyle1=null; row=null; } FileOutputStream fileOut = new FileOutputStream("MatrixReport.xlsx"); workbook.write(fileOut); fileOut.close(); }catch(Exception e) { e.printStackTrace(); }
Any update...??
Everyone involved in Apache POI is here as a volunteer, and works on problems on a best-effort basis. All software from the ASF is free! If your business needs quick responses, you should look into paying someone for a support contract...
You are creating a cell style per row and this is wrong, see http://poi.apache.org/faq.html#faq-N100EF Move creation of cell style outside of the loop and define two styles for odd and even rows. Below is my modification of your code that works properly: try { int headerLength = 15; int noofrows = 48575; SXSSFWorkbook workbook = new SXSSFWorkbook(50); workbook.setCompressTempFiles(true); SXSSFSheet worksheet = null; String header = ""; CellStyle cellStyle = workbook.createCellStyle(); Font font = workbook.createFont(); Font fontd = workbook.createFont(); String data = ""; SXSSFCell cell = null; CellStyle cellStyle1 = null; CellStyle cellStyle2 = null; SXSSFRow row = null; int sheetNo = 1; cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.index); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setColor(IndexedColors.WHITE.index); font.setFontHeightInPoints((short) 10); font.setFontName("Arial"); cellStyle.setFont(font); fontd.setColor(IndexedColors.BLACK.index); fontd.setFontHeightInPoints((short) 10); fontd.setFontName("Arial"); cellStyle1 = workbook.createCellStyle(); cellStyle1.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index); cellStyle1.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle1.setFont(fontd); cellStyle2 = workbook.createCellStyle(); cellStyle2.setFont(fontd); System.out.println("Matrix Report noofrows::" + noofrows); for (long i = 0, rowNo = 1; i < noofrows; i++, rowNo++) { if (rowNo == 1048575) { rowNo = 1; sheetNo++; } if (rowNo == 1) { worksheet = (SXSSFSheet) workbook.createSheet("Matrix Report " + sheetNo); row = (SXSSFRow) worksheet.createRow(0); for (int hl = 0; hl < headerLength; hl++) { cell = (SXSSFCell) row.createCell(hl); header = hl + ""; cell.setCellValue(header); cell.setCellStyle(cellStyle); cell = null; } } row = (SXSSFRow) worksheet.createRow((int) rowNo); for (int j = 0; j < headerLength; j++) { cell = (SXSSFCell) row.createCell(j); data = j + ""; if (data == null) data = ""; try { cell.setCellValue(Long.parseLong(data)); } catch (NumberFormatException e) { try { cell.setCellValue(Double.parseDouble(data)); } catch (NumberFormatException ee) { cell.setCellValue(data); } catch (NullPointerException ee) { cell.setCellValue(data); } } catch (NullPointerException e) { cell.setCellValue(data); } cell.setCellStyle(rowNo%2 != 0 ? cellStyle1 : cellStyle2); } } FileOutputStream fileOut = new FileOutputStream("MatrixReport.xlsx"); workbook.write(fileOut); fileOut.close(); } catch (Exception e) { e.printStackTrace(); }