[经验小结]jxl写excel时产生“java.lang.ArrayIndexOutOfBoundsException: 5”问题的解决办法

jiav_net

浏览: 110237 次

最近访客更多访客>>

谷静雯

mxxxs

tianrun

gen_ang20

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (186)

社区版块

存档分类

最近在做一个报表生成系统，为了方便，采用了Servlet + jxl的方式进行报表生成，以excel的形式导出。但是在导出过程中发现了一些问题，研究了很久，也在网上查了很多资料，但是解决方法都不一样，且相对较为复杂，于是自己瞎捣腾了一下，找到了一个适合于自己的解决方法，暂时贴出来供大家参考。本文不一定对适用于你遇到的同类问题，但是希望能在解决问题时可以多提供一种方案。

我遇到的问题是这样的，在收到客户端请求生成报表时，第一次可以顺利生成并下载，但是再发送一个生成请求的时候会抛出异常：java.lang.ArrayIndexOutOfBoundsException: 5

原始测试代码如下：

package com.myCompany.excel.test;

import java.io.File;
import java.io.IOException;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class App 
{
    public static void main( String[] args )
    {
    	try {
    		ExcelGenerator excel = new ExcelGenerator();
			for(int i = 1; i < 50; ++i)
			{
				String fileName = "Test" + i + ".xls";
				excel.CreateExcel(fileName);
				System.out.println(fileName + " Finished!");
			}
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
    }    
}

class ExcelGenerator
{
	private WritableFont fontTitle = new WritableFont(WritableFont.TIMES, 20, WritableFont.BOLD );
	private WritableFont fontHead = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD );
	private WritableFont fontBody = new WritableFont(WritableFont.TIMES, 16 );
	private WritableCellFormat cellTitle = new WritableCellFormat(fontTitle);
	private WritableCellFormat cellHead = new WritableCellFormat(fontHead);
	private WritableCellFormat cellBody = new WritableCellFormat(fontBody);
	
	public ExcelGenerator() throws WriteException
	{
		cellTitle.setAlignment(jxl.format.Alignment.CENTRE);
		cellTitle.setBackground(Colour.GREY_40_PERCENT);
		cellTitle.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
		
		cellHead.setAlignment(jxl.format.Alignment.CENTRE);
		cellHead.setBackground(Colour.GREY_25_PERCENT);
		cellHead.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
		
		cellBody.setAlignment(jxl.format.Alignment.LEFT);
		cellBody.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
	}
	
		
	public void CreateExcel(String fileName) throws IOException, RowsExceededException, WriteException
	{
		WritableWorkbook wb = Workbook.createWorkbook(new File(fileName));
		WritableSheet sheet = wb.createSheet("Test", 0);
		
		// 设置列宽
        sheet.setColumnView(0, 30); 
        sheet.setColumnView(1, 30); 
        sheet.setColumnView(2, 15);
        sheet.setColumnView(3, 15);
        sheet.setColumnView(4, 30);
        sheet.setColumnView(5, 40);
        
        sheet.addCell(new Label(0, 0, "公司信息", this.cellTitle));
        sheet.mergeCells(0, 0, 5, 0);
        
        // 插入标题内容
        sheet.addCell(new Label(0, 1, "条码", this.cellHead));
        sheet.addCell(new Label(1, 1, "公司", this.cellHead));
        sheet.addCell(new Label(2, 1, "部门", this.cellHead));
        sheet.addCell(new Label(3, 1, "姓名", this.cellHead));
        sheet.addCell(new Label(4, 1, "类型", this.cellHead));
        sheet.addCell(new Label(5, 1, "描述", this.cellHead));
        
        for(int i = 2; i < 2000; ++i)
        {
        	int col = 0;
        	sheet.addCell(new Label(col++, i, "1353761413" + i, cellBody));
        	sheet.addCell(new Label(col++, i, "XX有限责任公司", cellBody));
        	sheet.addCell(new Label(col++, i, "XX", cellBody));
        	sheet.addCell(new Label(col++, i, "XX", cellBody));
        	sheet.addCell(new Label(col++, i, "XXXXX", cellBody));
        	sheet.addCell(new Label(col++, i, "XXXXX", cellBody));
        }
        
        wb.write();
        wb.close();
	}
}

结果在第二次生成报表时抛出异常：

Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 5
	at jxl.biff.IndexMapping.getNewIndex(IndexMapping.java:68)
	at jxl.biff.FormattingRecords.rationalize(FormattingRecords.java:388)
	at jxl.write.biff.WritableWorkbookImpl.rationalize(WritableWorkbookImpl.java:1023)
	at jxl.write.biff.WritableWorkbookImpl.write(WritableWorkbookImpl.java:701)
	at com.myCompany.excel.test.ExcelGenerator.CreateExcel(App.java:101)
	at com.myCompany.excel.test.App.main(App.java:29)

在网上查资料，有人说程序中不能使用太多static，本测试程序中不含static，所以该解决方案不适用。有人说修改jxl的源文件，但是因为jxl.jar已经被其他同事集成到库中，不受我控制，所以只能另找他法。在分析打印出来的异常信息是，发现该异常的产生与单元格的格式控制有关系（因为项目时间紧，没时间分析太久。我想可能是对格式的引用次数有限制之类的，所以在生成不同的文件循环引用同样的格式时，产生了越界，姑且认为是这个很肤浅的原因吧，有时间再仔细研究）。于是先是尝试把格式控制都删除掉，再次运行程序时不会抛出异常。但是如果报表不进行格式控制根本没法看，所以不能去掉格式。于是为了避免出现引用次数过多而造成的越界情况，我决定在每次生成报表时都重新定义格式，而非是在第一次初始化时定义共享格式。修改后的代码如下：

package com.myCompany.excel.test;

import java.io.File;
import java.io.IOException;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class App 
{
    public static void main( String[] args )
    {
    	try {
    		ExcelGenerator excel = new ExcelGenerator();
			for(int i = 1; i < 50; ++i)
			{
				String fileName = "Test" + i + ".xls";
				excel.CreateExcel(fileName);
				System.out.println(fileName + " Finished!");
			}
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
    }    
}

class ExcelGenerator
{
	public ExcelGenerator() throws WriteException
	{
		
	}
		
	public void CreateExcel(String fileName) throws IOException, RowsExceededException, WriteException
	{
		// 定义格式
		WritableFont fontTitle = new WritableFont(WritableFont.TIMES, 20, WritableFont.BOLD );
		WritableFont fontHead = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD );
		WritableFont fontBody = new WritableFont(WritableFont.TIMES, 16 );
		WritableCellFormat cellTitle = new WritableCellFormat(fontTitle);
		WritableCellFormat cellHead = new WritableCellFormat(fontHead);
		WritableCellFormat cellBody = new WritableCellFormat(fontBody);
		
		cellTitle.setAlignment(jxl.format.Alignment.CENTRE);
		cellTitle.setBackground(Colour.GREY_40_PERCENT);
		cellTitle.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
		
		cellHead.setAlignment(jxl.format.Alignment.CENTRE);
		cellHead.setBackground(Colour.GREY_25_PERCENT);
		cellHead.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
		
		cellBody.setAlignment(jxl.format.Alignment.LEFT);
		cellBody.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
		
		//创建工作薄
		WritableWorkbook wb = Workbook.createWorkbook(new File(fileName));
		WritableSheet sheet = wb.createSheet("Test", 0);
		
		// 设置列宽
        sheet.setColumnView(0, 30); 
        sheet.setColumnView(1, 30); 
        sheet.setColumnView(2, 15);
        sheet.setColumnView(3, 15);
        sheet.setColumnView(4, 30);
        sheet.setColumnView(5, 40);
        
        // 设置标题
        sheet.addCell(new Label(0, 0, "公司信息", cellTitle));
        sheet.mergeCells(0, 0, 5, 0);
        
        // 插入标题内容
        sheet.addCell(new Label(0, 1, "条码", cellHead));
        sheet.addCell(new Label(1, 1, "公司", cellHead));
        sheet.addCell(new Label(2, 1, "部门", cellHead));
        sheet.addCell(new Label(3, 1, "姓名", cellHead));
        sheet.addCell(new Label(4, 1, "类型", cellHead));
        sheet.addCell(new Label(5, 1, "描述", cellHead));
        
        for(int i = 2; i < 2000; ++i)
        {
        	int col = 0;
        	sheet.addCell(new Label(col++, i, "1353761413" + i, cellBody));
        	sheet.addCell(new Label(col++, i, "XX有限责任公司", cellBody));
        	sheet.addCell(new Label(col++, i, "XX", cellBody));
        	sheet.addCell(new Label(col++, i, "XX", cellBody));
        	sheet.addCell(new Label(col++, i, "XXXXX", cellBody));
        	sheet.addCell(new Label(col++, i, "XXXXX", cellBody));
        }
        
        wb.write();
        wb.close();
	}
}

这样虽然程序效率降低，但是不会再抛出异常。而且考虑到报表生成不是一个频繁发生的事件，所以在每次生成报表时重新定义格式也是可以接受的。

分享到：