Bug 53514

Summary: blank row getting created after row no 32767
Product: POI Reporter: Angshuman <asthanpati>
Component: SXSSFAssignee: POI Developers List <dev>
Status: RESOLVED INVALID    
Severity: normal CC: asthanpati
Priority: P3 Keywords: JDK1.5
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   

Description Angshuman 2012-07-06 12:09:04 UTC
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);
}
}
Comment 1 Yegor Kozlov 2012-07-16 08:14:03 UTC
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.
Comment 2 Angshuman 2012-07-16 09:03:55 UTC
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.
Comment 3 Angshuman 2012-07-16 09:08:20 UTC
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.
Comment 4 Yegor Kozlov 2012-07-16 09:20:30 UTC
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.
Comment 5 Angshuman 2012-07-16 10:09:22 UTC
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();
	}
Comment 6 Angshuman 2012-07-17 09:23:30 UTC
Any update...??
Comment 7 Nick Burch 2012-07-17 09:42:45 UTC
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...
Comment 8 Yegor Kozlov 2012-07-17 10:20:43 UTC
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();
        }