在Java中如何读取包含超过100000行的Excel文件？

Question

我正在尝试使用Apache POI在Java中读取超过100,000行的Excel文件，但是我遇到了一些问题。

1-) 从Excel文件中获取数据需要10到15分钟。

2-) 当我运行代码时，我的笔记本电脑开始卡顿。因此，获取数据变得困难，然后我不得不重新启动我的笔记本电脑。

有没有其他方法可以在Java中更快地获取Excel文件中的数据？

以下是我的当前代码：

public class ReadRfdsDump {

    public void readRfdsDump() {
        try {
            FileInputStream file = new FileInputStream(new File("C:\\Users\\esatnir\\Videos\\sprint\\sprintvision.sprint.com_Trackor Browser_RF Design Sheet_07062018122740.xlsx"));
             XSSFWorkbook workbook = new XSSFWorkbook(file);
             XSSFSheet sheet = workbook.getSheetAt(0);
             DataFormatter df = new DataFormatter();

             for(int i=0;i<2;i++) {
                 Row row= sheet.getRow(i);
                 System.out.println(df.formatCellValue(row.getCell(1)));
             }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

- Sat.N

---

使用SXXFWorkBook代替XXFWorkBook - undefined

读取10万行数据应该不会超过几秒钟，详细信息请参阅Apache POI FAQ！ - undefined

如何使用SXXFWorkBook来读取Excel文件？你能给我提供一个链接或其他信息吗？ - undefined

将代码进行更好的格式化，并修复问题中的一些错误。 - undefined

https://stackoverflow.com/questions/36473062/read-large-excel-file-xlsx - undefined

Answer 1

Apache poi的默认方法使用WorkbookFactory.create或new XSSFWorkbook打开工作簿时，将始终解析整个工作簿，包括所有工作表。如果工作簿包含大量数据，则会导致内存使用量高。使用File而不是InputStream打开工作簿可以减少内存使用量。但这会导致其他问题，因为使用的文件不能被覆盖，至少对于*.xlsx文件来说是如此。

有XSSF和SAX（事件API），它们可以获取底层XML数据，并使用SAX进行处理。

但是，如果我们已经到达了获取底层XML数据并处理它的级别，那么我们还可以再往后走一步。

*.xlsx文件是一个ZIP归档文件，其中包含目录结构中的XML文件中的数据。因此，我们可以解压缩*.xlsx文件，然后从XML文件中获取数据。

有/xl/sharedStrings.xml，其中包含所有字符串单元格值。还有描述工作簿结构的/xl/workbook.xml。还有存储工作表数据的/xl/worksheets/sheet1.xml，/xl/worksheets/sheet2.xml等。还有/xl/styles.xml，其中包含工作表中所有单元格的样式设置。

因此，我们所需要的就是使用Java处理ZIP文件系统。这可以使用java.nio.file.FileSystems来支持。

我们需要一种解析XML的方法。我最喜欢的是javax.xml.stream包。

以下是一个工作草案。它解析了/xl/sharedStrings.xml。它还解析了/xl/styles.xml。但它只获取数字格式和单元格数字格式设置。数字格式设置对于检测日期/时间值至关重要。然后解析了包含第一个工作表数据的/xl/worksheets/sheet1.xml。为了检测数字格式是否为日期格式，以及格式化的单元格是否包含日期/时间值，只使用了一个apache poi类org.apache.poi.ss.usermodel.DateUtil。这样做是为了简化代码。当然，我们甚至可以自己编写此类。

import java.nio.file.Paths;
import java.nio.file.Path;
import java.nio.file.Files;
import java.nio.file.FileSystems;
import java.nio.file.FileSystem;

import javax.xml.stream.*;
import javax.xml.stream.events.*;
import javax.xml.namespace.QName;

import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.HashMap;
import java.util.Date;

import org.apache.poi.ss.usermodel.DateUtil;

public class UnZipAndReadXLSXFileSystem {

 public static void main (String args[]) throws Exception {

  XMLEventReader reader = null;
  XMLEvent event = null;
  Attribute attribute = null;
  StartElement startElement = null; 
  EndElement endElement = null; 

  String characters = null;
  StringBuilder stringValue = new StringBuilder(); //for collecting the characters to complete values 

  List<String> sharedStrings = new ArrayList<String>(); //list of shared strings

  Map<String, String> numberFormats = new HashMap<String, String>(); //map of number formats
  List<String> cellNumberFormats = new ArrayList<String>(); //list of cell number formats

  Path source = Paths.get("ExcelExample.xlsx"); //path to the Excel file

  FileSystem fs = FileSystems.newFileSystem(source, null); //get filesystem of Excel file

  //get shared strings ==============================================================================
  Path sharedStringsTable = fs.getPath("/xl/sharedStrings.xml");
  reader = XMLInputFactory.newInstance().createXMLEventReader(Files.newInputStream(sharedStringsTable));
  boolean siFound = false;
  while (reader.hasNext()) {
   event = (XMLEvent)reader.next();
   if (event.isStartElement()){
    startElement = (StartElement)event;
    if (startElement.getName().getLocalPart().equalsIgnoreCase("si")) {
     //start element of shared string item
     siFound = true;
     stringValue = new StringBuilder();
    } 
   } else if (event.isCharacters() && siFound) {
    //chars of the shared string item
    characters = event.asCharacters().getData();
    stringValue.append(characters);
   } else if (event.isEndElement() ) {
    endElement = (EndElement)event;
    if (endElement.getName().getLocalPart().equalsIgnoreCase("si")) {
     //end element of shared string item
     siFound = false;
     sharedStrings.add(stringValue.toString());
    }
   }
  }
  reader.close();
System.out.println(sharedStrings);
  //shared strings ==================================================================================

  //get styles, number formats are essential for detecting date / time values =======================
  Path styles = fs.getPath("/xl/styles.xml");
  reader = XMLInputFactory.newInstance().createXMLEventReader(Files.newInputStream(styles));
  boolean cellXfsFound = false;
  while (reader.hasNext()) {
   event = (XMLEvent)reader.next();
   if (event.isStartElement()){
    startElement = (StartElement)event;
    if (startElement.getName().getLocalPart().equalsIgnoreCase("numFmt")) {
     //start element of number format
     attribute = startElement.getAttributeByName(new QName("numFmtId"));
     String numFmtId = attribute.getValue();
     attribute = startElement.getAttributeByName(new QName("formatCode"));
     numberFormats.put(numFmtId, ((attribute != null)?attribute.getValue():"null"));
    } else if (startElement.getName().getLocalPart().equalsIgnoreCase("cellXfs")) {
     //start element of cell format setting
     cellXfsFound = true;

    } else if (startElement.getName().getLocalPart().equalsIgnoreCase("xf") && cellXfsFound ) {
     //start element of format setting in cell format setting
     attribute = startElement.getAttributeByName(new QName("numFmtId"));
     cellNumberFormats.add(((attribute != null)?attribute.getValue():"null"));
    }
   } else if (event.isEndElement() ) {
    endElement = (EndElement)event;
    if (endElement.getName().getLocalPart().equalsIgnoreCase("cellXfs")) {
     //end element of cell format setting
     cellXfsFound = false;
    }
   }
  }
  reader.close();
System.out.println(numberFormats);
System.out.println(cellNumberFormats);
  //styles ==========================================================================================

  //get sheet data of first sheet ===================================================================
  Path sheet1 = fs.getPath("/xl/worksheets/sheet1.xml");
  reader = XMLInputFactory.newInstance().createXMLEventReader(Files.newInputStream(sheet1));
  boolean rowFound = false;
  boolean cellFound = false;
  boolean cellValueFound = false;
  boolean inlineStringFound = false; 
  String cellStyle = null;
  String cellType = null;
  while (reader.hasNext()) {
   event = (XMLEvent)reader.next();
   if (event.isStartElement()){
    startElement = (StartElement)event;
    if (startElement.getName().getLocalPart().equalsIgnoreCase("row")) {
     //start element of row
     rowFound = true;
System.out.print("<Row");

     attribute = startElement.getAttributeByName(new QName("r"));
System.out.print(" r=" + ((attribute != null)?attribute.getValue():"null"));
System.out.println(">");

    } else if (startElement.getName().getLocalPart().equalsIgnoreCase("c") && rowFound) {
     //start element of cell in row
     cellFound = true;
System.out.print("<Cell");

     attribute = startElement.getAttributeByName(new QName("r"));
System.out.print(" r=" + ((attribute != null)?attribute.getValue():"null"));

     attribute = startElement.getAttributeByName(new QName("t"));
System.out.print(" t=" + ((attribute != null)?attribute.getValue():"null"));
     cellType = ((attribute != null)?attribute.getValue():null);

     attribute = startElement.getAttributeByName(new QName("s"));
System.out.print(" s=" + ((attribute != null)?attribute.getValue():"null"));
     cellStyle = ((attribute != null)?attribute.getValue():null);

System.out.print(">");

    } else if (startElement.getName().getLocalPart().equalsIgnoreCase("v") && cellFound) {
     //start element of value in cell
     cellValueFound = true;
System.out.print("<V>");
     stringValue = new StringBuilder();

    } else if (startElement.getName().getLocalPart().equalsIgnoreCase("is") && cellFound) {
     //start element of inline string in cell
     inlineStringFound = true;
System.out.print("<Is>");
     stringValue = new StringBuilder();

    }
   } else if (event.isCharacters() && cellFound && (cellValueFound || inlineStringFound)) {
    //chars of the cell value or the inline string
    characters = event.asCharacters().getData();
    stringValue.append(characters);

   } else if (event.isEndElement()) {
    endElement = (EndElement)event;
    if (endElement.getName().getLocalPart().equalsIgnoreCase("row")) {
     //end element of row
     rowFound = false;
System.out.println("</Row>");

    } else if (endElement.getName().getLocalPart().equalsIgnoreCase("c")) {
     //end element of cell
     cellFound = false;
System.out.println("</Cell>");

    } else if (endElement.getName().getLocalPart().equalsIgnoreCase("v")) {
     //end element of value
     cellValueFound = false;

     String cellValue = stringValue.toString();

     if ("s".equals(cellType)) {
      cellValue = sharedStrings.get(Integer.valueOf(cellValue));
     }

     if (cellStyle != null) {
      int s = Integer.valueOf(cellStyle);
      String formatIndex = cellNumberFormats.get(s);
      String formatString = numberFormats.get(formatIndex);
      if (DateUtil.isADateFormat(Integer.valueOf(formatIndex), formatString)) {
       double dDate = Double.parseDouble(cellValue); 
       Date date = DateUtil.getJavaDate(dDate);
       cellValue = date.toString();
      }
     }

System.out.print(cellValue);
System.out.print("</V>");

    } else if (endElement.getName().getLocalPart().equalsIgnoreCase("is")) {
     //end element of inline string
     inlineStringFound = false;

     String cellValue = stringValue.toString();
System.out.print(cellValue);
System.out.print("</Is>");

    }
   }
  }
  reader.close();
  //sheet data ======================================================================================

  fs.close();

 }
}

- Axel Richter

---

我能否通过使用您的代码来获取特定行和列的数据？ - undefined

@George.S：正如所说，这只是一个工作草稿。但是它已经被注释了，System.out.print...会告诉你当前所在的行和单元格，从而帮助你开始确定。当然，你需要理解代码的工作原理。这并不是一段可以直接使用的代码。 - undefined

Answer 2

Apache POI是你的朋友 - 这是正确的。但是当我读取带有公式的非常大的Excel文件时，我遇到了内存溢出的问题。

我的解决方案是：如果你只想从XLSX文件中读取数据，并且不担心公式，那么可以将其作为简单的xml文件进行读取，并从中提取数据（这很容易）。

1. 将*.xlsx文件作为zip归档文件进行读取
2. 进入xl\worksheets文件夹，你会找到每个工作表对应的一个xml文件
3. 使用任何xml阅读器读取此文件，并检索所需的数据

- Oleg Cherednik