java - 如何从excel文件中获取数据？

Question

实际上我正在开发一个从 Excel 文件中提取数据的 java 程序，我正在使用 POI 库，事实上我必须指定每个提取值的类型，但该文件包含大量不同类型的数据，所以我想问是否有另一种方法可以将所有数据作为字符串获取。

谢谢。最好的问候

package DAO;

import java.io.FileInputStream;
import java.util.Iterator;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class ReadExcelFile {

    public static void main(String[] args) {
        String fileName = "C:\\Users\\marrah\\Desktop\\TRIAL FILE1.xls";
        Vector dataHolder = ReadCSV(fileName);
        printCellData(dataHolder);
    }

    public static Vector ReadCSV(String fileName) {
        Vector cellVectorHolder = new Vector();

        try {
            FileInputStream myInput = new FileInputStream(fileName);
            POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
            HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
            HSSFSheet mySheet = myWorkBook.getSheetAt(0);
            Iterator rowIter = mySheet.rowIterator();

            while (rowIter.hasNext()) {
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator cellIter = myRow.cellIterator();
                Vector cellStoreVector = new Vector();
                while (cellIter.hasNext()) {
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                    cellStoreVector.addElement(myCell);
                }
                cellVectorHolder.addElement(cellStoreVector);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return cellVectorHolder;
    }

    private static void printCellData(Vector dataHolder) {
        for (int i = 0; i < dataHolder.size(); i++) {
            Vector cellStoreVector = (Vector) dataHolder.elementAt(i);
            for (int j = 0; j < cellStoreVector.size(); j++) {
                HSSFCell myCell = (HSSFCell) cellStoreVector.elementAt(j);
                Object stringCellValue="";
                stringCellValue =cellStoreVector.get(j).toString();
                System.out.print(stringCellValue.toString()+"\t");
            }
        }
    }
}

Answer 1

我有一个单元测试，我使用以下命令从 Excel 文件中提取所有文本，不带任何格式，对于某些用例，这可能比逐个遍历所有元素更快:

private POITextExtractor extractText(File file) throws IOException {

    InputStream inp = null;

    try {

        inp = new PushbackInputStream(

            new FileInputStream(file), 8);



        if(POIFSFileSystem.hasPOIFSHeader(inp)) {

            return createExtractor(new POIFSFileSystem(inp));

        }

        throw new IllegalArgumentException("Your File was neither an OLE2 file, nor an OOXML file");

    } finally {

        if(inp != null) inp.close();

    }

}



private static POITextExtractor createExtractor(POIFSFileSystem fs) throws IOException {

    return createExtractor(fs.getRoot(), fs);

}



private static POITextExtractor createExtractor(DirectoryNode poifsDir, POIFSFileSystem fs) throws IOException {

    for(Iterator<Entry> entries = poifsDir.getEntries(); entries.hasNext(); ) {

        Entry entry = entries.next();



        if(entry.getName().equals("Workbook")) {

           {

              return new ExcelExtractor(poifsDir, fs);

           }

        }

    }

    throw new IllegalArgumentException("No supported documents found in the OLE2 stream");

}



private String assertContains(File file, String... contents) throws IOException {

    assertTrue(file.exists());

    POITextExtractor extractor = extractText(file);

    assertNotNull(extractor);

    String str = extractor.getText();



    for(String s : contents) {

        assertTrue("Did expect to find text '" + s + "' in resulting Excel file, but did not find it in str: " + str, str.contains(s));

    }



    return str;

}