最近遇到的一个问题,从系统中导出Excle后,修改excel的某些字段内容,然后再导入Excel,通过jxl读取Excel,出现jxl读文件异常
- jxl.read.biff.BiffException: Unable to recognize OLE stream
- at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
- at jxl.read.biff.File.<init>(File.java:127)
- at jxl.Workbook.getWorkbook(Workbook.java:221)
- at jxl.Workbook.getWorkbook(Workbook.java:198)
jxl.read.biff.BiffException: Unable to recognize OLE stream
at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
at jxl.read.biff.File.<init>(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:221)
at jxl.Workbook.getWorkbook(Workbook.java:198)
说明导入的excel不是jxl认定的标准的OLE file,可是打开的看起来是Excle,然后我把这个Excel重新另存为一个Excel,发现他默认的保存类型是“单个文件网页”(通过EditPlus打开Excle可以看到是网页文件),然后把他保类型修改为Microsoft Office Excel,保存导入,就可以正确导入了。
另外一个可能的原因是:excel的版本问题
但是也可以不是通过另存为的方式,来进行导入。
1、读入Excel是按文本方式读入
2、导出Excel,按照符合jxl的方式导出
- public void outPutExcel(List<InvoiceRequisitionSo> result,HttpServletResponse response ){
- OutputStream os = null;
- try{
- os = response.getOutputStream();
- }catch (IOException el){
- }
- response.reset();
- response.setHeader("Content-disposition", "attachment; filename=InvoiceRequisitionListToExcel.xls");
- response.setContentType("application/msexcel");
- WritableWorkbook wwb=null;
- WritableSheet ws=null;
- WritableCellFormat cellFormat = new WritableCellFormat();
- WritableCellFormat cellFormatContent = new WritableCellFormat();
- WritableFont font= new WritableFont(WritableFont.createFont("宋体"),10, WritableFont.NO_BOLD);
- WritableFont font1= new WritableFont(WritableFont.createFont("宋体"),10, WritableFont.NO_BOLD);
- try{
- wwb = Workbook.createWorkbook(os);
- ws = wwb.createSheet("总部发票申请", 0);
- font.setColour(Colour.WHITE);
- WritableCellFormat cellFormatContentt = new WritableCellFormat(font);
- font1.setColour(Colour.RED);
- WritableCellFormat cellFormatContentred = new WritableCellFormat(font1);
- ws.getSettings().setDefaultColumnWidth(18);
- //设置单元格格式
- cellFormat.setBackground(Colour.GRAY_25);
- cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
- cellFormatContent.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
- cellFormatContentt.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
- int i = 0;
- for (InvoiceRequisitionSo so : result) {
- ws.addCell(new Label(0,i,so.getInvoice(),cellFormatContentred ));
- //合并单元格
- ws.mergeCells(1, i , 2, i );
- ws.addCell(new Label(1, i, so.getEno(), cellFormatContent));
- i++;
- }
- }catch (Exception e){
- } finally{
- try {
- wwb.write();
- wwb.close();
- os.close();
- } catch (Exception e) {
- }
- }
- }