最近在做一个报表生成系统,为了方便,采用了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();
}
}
这样虽然程序效率降低,但是不会再抛出异常。而且考虑到报表生成不是一个频繁发生的事件,所以在每次生成报表时重新定义格式也是可以接受的。