Bug 51601 - Excel 2003 and 2007 can not open file generated by poi if the number of sheet in a workbook exceed 1370 sheets
Summary: Excel 2003 and 2007 can not open file generated by poi if the number of sheet...
Status: RESOLVED WONTFIX
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.7-FINAL
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-08-02 16:06 UTC by Felix Sanchez
Modified: 2016-03-29 19:42 UTC (History)
0 users



Attachments
Corrupted file created by poi 3.7, workbook with 1371 sheets (447.00 KB, application/vnd.ms-excel)
2011-08-02 16:06 UTC, Felix Sanchez
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Felix Sanchez 2011-08-02 16:06:59 UTC
Created attachment 27341 [details]
Corrupted file created by poi 3.7, workbook with 1371 sheets

I use the version of POI(3.7) to create 1400 sheets in a workbook. And when I open the XLS file generated by the programe, the MS excel 2003 pops the error like bellow. 

Damage to the file was so extensive that repairs were not possible.  Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.

In addition,  if the number of sheet is under 1370, it can be opened normally.  Also when I use POI 2.5.1 the workbook is generated fine.

Attached is the generated file and here is the test code:

package com.test;

import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormat;
//import org.apache.poi.ss.util.Color;
//import org.apache.poi.ss.util.Region;
public class poiTest36 {

	Workbook m_wb;
	Font m_boldFont;
	CellStyle cellStyle;
	poiTest36() {
		m_wb = new HSSFWorkbook();

		m_boldFont = m_wb.createFont();
		m_boldFont.setBoldweight( Font.BOLDWEIGHT_BOLD );
		cellStyle=m_wb.createCellStyle();
		
	}
	
	public void createSheet(){
		Sheet sheet=m_wb.createSheet();
		
		
		
		cellStyle.setFont(m_boldFont);
		cellStyle.setBorderBottom( CellStyle.BORDER_MEDIUM );
		cellStyle.setBorderRight( CellStyle.BORDER_MEDIUM );
		cellStyle.setBorderLeft( CellStyle.BORDER_MEDIUM);
		cellStyle.setBorderTop( CellStyle.BORDER_MEDIUM);
		Row row=sheet.createRow(1);
		
		Cell cell=row.createCell((short)1);
		cell.setCellStyle(cellStyle);
		cell.setCellValue("Felix S");
	}
	
	public void save( String filename) throws Exception  {
		
		FileOutputStream fileout = new FileOutputStream( filename );
		m_wb.write( fileout );
		fileout.close();				
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		poiTest36 p = new poiTest36();
		for(int i=0; i<1371; i++) {
			p.createSheet();
		}
		try {
		p.save("/home/fsanchez/tmp/test.xls");
		}
		catch (Exception e){
			System.out.println("got exeption:" + e.getMessage());
			e.printStackTrace();
		}
	}

}
Comment 1 kalyan 2011-08-02 17:38:42 UTC
As I see, this is because content of an excel record cannot exceed certain value (in bytes). RecordInputStream.java's MAX_RECORD_DATA_SIZE value needs to be modified. Its current value is 8224 bytes.
Comment 2 Dominik Stadler 2016-03-29 19:42:07 UTC
I don't think there is much chance of fixing this unless a full patch with a number of unit-tests to cover the changes is contributed. 

Having so many sheets is a very rare case and changing the sizes of records has a high risk of breaking other stuff. 

Using XSSFWorkbook and the newer .xlsx files allows to create such Excel files just fine, so if possible please switch to using the newer .xlsx based format.