Bug 56999 - SXSSF Workbook with Java5 is producing unreadable xlsx document
Summary: SXSSF Workbook with Java5 is producing unreadable xlsx document
Status: RESOLVED DUPLICATE of bug 55594
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 3.10-FINAL
Hardware: PC other
: P1 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-09-21 05:52 UTC by Sai
Modified: 2014-09-22 13:44 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Sai 2014-09-21 05:52:02 UTC
Hi ,

I am using Apache POI 3.10 Final version to generate an excel document as per my application requirement. My application runs on JDK 1.5 version and it is producing an unreadable xlsx document saying " Excel found unreadable content in XXX.XLSX. Do you want to recover the contents of the workbook?"

Please find the simulation of code i have used

public static void main(String[] args) {
	
	SXSSFWorkbook workbook = new SXSSFWorkbook(300);

	SXSSFSheet sheet = (SXSSFSheet)   workbook.createSheet("Teller_Discrepancy");   
	
		Map<String, Object[]> data = new HashMap<String, Object[]>(); 
		
		 final String[] titles = {
				 "Col1","Col2","Col3",.......,"Col15"
	    };

		 createHeaderRow(workbook, sheet, titles);
		 writeToExcel(sheet, data, getColumnStyle(workbook));
		 sheet.setDefaultColumnWidth(15);

		FileOutputStream out = new FileOutputStream(new File("C:\Temp"));     
		workbook.write(out);     
		out.close();   

}

private void writeToExcel(SXSSFSheet sheet, Map<String, Object[]> data, CellStyle columnStyle){
		Set<String> keyset = data.keySet(); 
		int rownum = 1; 
		for (String key : keyset) {     
			SXSSFRow row = (SXSSFRow) sheet.createRow(rownum++);     
			Object [] objArr = data.get(key);     
			int cellnum = 0;     
			for (Object obj : objArr) {         
				SXSSFCell cell = (SXSSFCell) row.createCell(cellnum++); 
				cell.setCellStyle(columnStyle);
				if (obj == null) {
					cell.setCellValue("");
				}
				else {
					cell.setCellValue(String.valueOf(obj));
				}
			}
		}
	}
	
	private void createHeaderRow(SXSSFWorkbook workbook, SXSSFSheet sheet, String[] titles) {
		// Setting Title Font properties
	     Font titleFont = workbook.createFont();
	     titleFont.setFontHeightInPoints((short)9);
	     titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	     
	     // Setting Header Style
	     CellStyle style = workbook.createCellStyle();
	     style.setAlignment(CellStyle.ALIGN_CENTER);
	     style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	     style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
	     style.setFillPattern(CellStyle.SOLID_FOREGROUND);
	     style.setFont(titleFont);
	     style.setWrapText(true);
	     style.setBorderBottom(CellStyle.BORDER_THIN);
	     style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	     style.setBorderLeft(CellStyle.BORDER_THIN);
	     style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	     style.setBorderRight(CellStyle.BORDER_THIN);
	     style.setRightBorderColor(IndexedColors.BLACK.getIndex());
	     style.setBorderTop(CellStyle.BORDER_THIN);
	     style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	    
	     
	     // Creating Header Row
		SXSSFRow headerRow = (SXSSFRow) sheet.createRow(0);
		headerRow.setHeightInPoints(40);
		SXSSFCell headerCell;
			for (int i = 0; i < titles.length; i++) {
		         headerCell = (SXSSFCell) headerRow.createCell(i);
		         headerCell.setCellValue(titles[i]);
		         headerCell.setCellStyle(style);
			}
	}
	
	private CellStyle getColumnStyle(SXSSFWorkbook workbook) {
		 CellStyle columnStyle = workbook.createCellStyle();
	     columnStyle.setAlignment(CellStyle.ALIGN_CENTER);
	     columnStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	     columnStyle.setBorderBottom(CellStyle.BORDER_THIN);
	     columnStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	     columnStyle.setBorderLeft(CellStyle.BORDER_THIN);
	     columnStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	     columnStyle.setBorderRight(CellStyle.BORDER_THIN);
	     columnStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
	     columnStyle.setBorderTop(CellStyle.BORDER_THIN);
	     columnStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
	     return columnStyle;
	}
Comment 1 Sai 2014-09-21 09:54:56 UTC
The server is mainframe Z/OS
Comment 2 Sai 2014-09-21 09:55:54 UTC
If i run the same in my local system which is windows XP, it is working absolutely fine. Kindly help me as soon as possible.
Comment 3 Andreas Beeker 2014-09-21 10:10:33 UTC
What's the difference between the file generated on Z/OS and Windows?
(linebreaks,character encoding,...)
Comment 4 Sai 2014-09-21 11:08:52 UTC
I am not sure about it.. I guess encoding is EBCDIC in Z/OS. Bit when i have used XSSFWorkbook , it worked fine. The reason i have shifted to SXSSFWorkbook is i have faced heap memory exception if number of records are more.
Comment 5 Andreas Beeker 2014-09-21 11:47:06 UTC
> I am not sure about it.. 
You can find the difference when you unzip the files and diff the directories.
Alternatively you can attach both .xlsx to this bug entry.

Before we investigate what's wrong with SXSSF, we need to know which artifact is malformed. The next problem would be, that we probably won't provide a patch for 3.10-final. Is jdk 1.6 available in your Z/OS environment?
Comment 6 Sai 2014-09-22 12:21:29 UTC
The below patch has worked for me.

https://issues.apache.org/bugzilla/attachment.cgi?id=31421&action=diff
Comment 7 Dominik Stadler 2014-09-22 13:44:13 UTC

*** This bug has been marked as a duplicate of bug 55594 ***