java - 如何解决在java中读取Excel xls文件时出现的错误

Question

我在名为 E:/portfolio.xls 的位置有一个 Excel 文件。我想通过java代码读取这个文件。我使用 java 编写了一些代码，但无法读取该文件并显示一些错误。我是这些类型编码的新手。有人请帮我解决这些问题。我的java代码如下:

/SampleExcelReading.java

package com.sampleexcelreading.core;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.Multimap;

public class SampleExcelReading {

    public static void main(String[] args) {
         try {

                Multimap<String, String> portfolioHoldingMap = ArrayListMultimap.create();   



                File f=new File("E:/portfolio.xls");
                org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(f);

                int numberOfSheets = workbook.getNumberOfSheets();
                org.apache.poi.ss.usermodel.Sheet sheet=null; 

                sheet = workbook.getSheetAt(0);

                Iterator<Row> rowIterator = sheet.iterator();

                String currentSchemeNameCode = "";
                String holding_date = "";

                while (rowIterator.hasNext())
                {
                     Row row = rowIterator.next();

                     int rowNo = row.getRowNum();

                     if(rowNo < 0 || rowNo >= 1500)
                     {
                         continue;
                     }
                         Iterator<Cell> cellIterator = row.cellIterator();

                         while (cellIterator.hasNext())
                         {
                             Cell cell = cellIterator.next();
                             String value = "";
                             switch (cell.getCellType())
                             {
                                 case Cell.CELL_TYPE_NUMERIC:
                                     if (DateUtil.isCellDateFormatted(cell)) 
                                     {
                                         SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
                                         value = sdf.format(cell.getDateCellValue());
                                     } 
                                     else 
                                     {
                                         value = String.valueOf(cell.getNumericCellValue());
                                     }
                                     break;
                                 case Cell.CELL_TYPE_STRING:
                                     value = cell.getStringCellValue();
                                     break;
                                 case Cell.CELL_TYPE_BLANK:
                                     value = "0";
                                     break;
                                 default:
                                     value = "0";
                                     break;
                             }

                             if(cell.getColumnIndex() == 0 && rowNo > 0 && !value.equalsIgnoreCase(""))
                             {                       
                                 value = value.trim().replaceAll(" +", " ");
                                 int firstHypenIndex = value.indexOf("-");                       
                                 value = value.substring(firstHypenIndex + 2, value.length());                       
                                 int firstAsAtIndex = value.indexOf(" as at ");                      
                                 holding_date = value.substring(firstAsAtIndex + 7, value.length());                     
                                 String[] nav_date_splitted = holding_date.split("[\\/]+");
                                 holding_date = nav_date_splitted[2] + "-" + nav_date_splitted[1] + "-" + nav_date_splitted[0];                      
                                 value = value.substring(0, firstAsAtIndex);                     
                                 value = value.replaceAll("'","�");                
                                 schemeName.add(value.trim());
                                 currentSchemeNameCode = value.trim();
                             }

                             if(rowNo == 0 && cell.getColumnIndex() != 0)
                             {
                                 value = value.replaceAll("'","�");
                                 companyName.add(value);
                             }


                             if(rowNo > 0 && cell.getColumnIndex() != 0)
                             { 
                                 portfolioHoldingMap.put(currentSchemeNameCode,cell.getColumnIndex() + "||" + value.trim().replaceAll(" +", " ") + "||" + holding_date);
                             }

                         }
                     }


         }
         catch (Exception e)
         {
             e.printStackTrace();
         }           

    }

}

/* 这是我的错误 */

org.apache.poi.POIXMLException: java.lang.reflect.InvocationTargetException
    at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:62)
    at org.apache.poi.POIXMLDocumentPart.read(POIXMLDocumentPart.java:403)
    at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:155)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:186)
    at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:91)
    at com.advisorkhoj.amfi.SampleExcelReading.main(SampleExcelReading.java:27)
Caused by: java.lang.reflect.InvocationTargetException
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at org.apache.poi.xssf.usermodel.XSSFFactory.createDocumentPart(XSSFFactory.java:60)
    ... 5 more
Caused by: java.io.CharConversionException: Characters larger than 4 bytes are not supported: byte 0x96 implies a length of more than 4 bytes
    at org.apache.xmlbeans.impl.piccolo.xml.UTF8XMLDecoder.decode(UTF8XMLDecoder.java:162)
    at org.apache.xmlbeans.impl.piccolo.xml.XMLStreamReader$FastStreamDecoder.read(XMLStreamReader.java:762)
    at org.apache.xmlbeans.impl.piccolo.xml.XMLStreamReader.read(XMLStreamReader.java:162)
    at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yy_refill(PiccoloLexer.java:3474)
    at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:3958)
    at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
    at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
    at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
    at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3439)
    at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1270)
    at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1257)
    at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
    at org.openxmlformats.schemas.spreadsheetml.x2006.main.StyleSheetDocument$Factory.parse(Unknown Source)
    at org.apache.poi.xssf.model.StylesTable.readFrom(StylesTable.java:121)
    at org.apache.poi.xssf.model.StylesTable.<init>(StylesTable.java:92)
    ... 10 more

/我的jar文件是

dom4j-1.6.jar
poi-3.9.jar
poi-ooxml-3.9.jar
poi-ooxml-schemas-3.7-betal1.jar
xmlbeans-2.30.jar

Answer 1

Apache POI 仅部分支持读取 Excel xlsb 文件。使用工作簿无法完成此操作，但从 POI 3.16-beta3 开始，它支持通过 XSSFBReader 流式读取此类文件。 。