在 Java 中读取 Excel 文件列

我有一个需要读取并存储在数据库中的 Excel 数据样本。例子:

Code  | BO1    | BO2    | BO3    | .....
10001 | 200.00 | 400.00 | 0.00   | .....
10002 | 0.00   | 100.00 | 500.00 | .....

我曾尝试使用 Apache POI 逐行读取数据。但是如何同时从列中获取名称和金额?像这样的东西

10001,BO1,200.00
10001,BO2,400.00
10001,BO3,0.00
10002,BO1,0.00
10002,BO2,100.00
10002,BO3,500.00

任何帮助,将不胜感激。谢谢

FileInputStream fis = (FileInputStream) files.getInputStream();
        POIFSFileSystem fs = new POIFSFileSystem(fis);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        Row row;
        for(int i=4; i<=sheet.getLastRowNum(); i++)
        {
            row = sheet.getRow(i);
            String gl;
            Cell cell1 = row.getCell(0);
            if(cell1.getCellType()==Cell.CELL_TYPE_NUMERIC)
            {
                gl = String.valueOf(cell1.getNumericCellValue()).replace(".0", "").trim();
            }
            else
            {
                gl = cell1.getStringCellValue();
            }
            write.println("<p>"+gl+"</p>");
        } 
stack overflow Reading Excel File Column in Java
原文答案
author avatar

接受的答案

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class XlReader {

    public static void main(String[] args) {
        try (InputStream inp = (FileInputStream) files.getInputStream()) {
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);
            Row row = sheet.getRow(0);
            List<String> columnNames = new ArrayList<>();
            for (Iterator<Cell> iterator = row.cellIterator(); iterator.hasNext();) {
                Cell cell = iterator.next();
                String value = cell.getStringCellValue();
                columnNames.add(value);
            }
            Iterator<Row> iterator = sheet.iterator();
            if (iterator.hasNext()) {
                iterator.next();
            }
            List<String> rows = new ArrayList<>();
            while (iterator.hasNext()) {
                row = iterator.next();
                Cell code = row.getCell(0);
                double d = code.getNumericCellValue();
                int k = Double.valueOf(d).intValue();
                StringBuilder sb = new StringBuilder();
                for (int i = 1; i < columnNames.size(); i++) {
                    sb = new StringBuilder();
                    sb.append(k);
                    sb.append(',');
                    sb.append(columnNames.get(i));
                    sb.append(',');
                    Cell cell = row.getCell(i);
                    sb.append(String.format("%.2f", cell.getNumericCellValue()));
                    rows.add(sb.toString());
                }
            }
            rows.forEach(System.out::println);
        }
        catch (IOException xIo) {
            xIo.printStackTrace();
        }
    }
}

结果:

10001,BO1,200.00
10001,BO2,400.00
10001,BO3,0.00
10002,BO1,0.00
10002,BO2,100.00
10002,BO3,500.00

答案:

作者头像

您还需要 Apache POI ooxml 才能使用表格。下面的代码应该实现与您描述的格式类似的东西。

DataFormatter formatter = new DataFormatter();
try (XSSFWorkbook workbook = new XSSFWorkbook(Main.class.getResourceAsStream("workbook.xlsx"))){
    int sheetCount = workbook.getNumberOfSheets();
    // iterate through all sheets
    for (int i = 0; i < sheetCount; i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        // iterate through all tables
        for (XSSFTable table : sheet.getTables()) {
            int startRow = table.getStartRowIndex();
            int startCol = table.getStartColIndex();
            // iterate through all cells in the table
            for (CellReference cellReference : table.getCellReferences()
                  .getAllReferencedCells()) {
                if (cellReference.getRow() == startRow) {
                    // skip header row
                    continue;
                } else if (cellReference.getCol() == startCol) {
                    // skip 'Code' column to obtain the desired format
                    continue;
                }
                Cell codeCell = sheet.getRow(cellReference.getRow())
                      .getCell(startCol);
                Cell cell = sheet.getRow(cellReference.getRow())
                      .getCell(cellReference.getCol());
                XSSFTableColumn column = table.getColumns()
                      .get(cellReference.getCol() - startCol);
                System.out.printf("%s,%s,%sn", formatter.formatCellValue(codeCell), column.getName(),
                      formatter.formatCellValue(cell));
            }
        }
    }
} catch (IOException e) {
    e.printStackTrace();
}
作者头像

我的方法是读取列名并将它们存储在列表中,以供以后在读取具有实际数据的行期间使用。将被解析的行中的数据存储到 map 中,其中代码作为键和对列表:单元格值和单元格标题名称。

  private static void readExcel(XSSFSheet sheet) {
        Map<Long, List<Column>> data = new HashMap<>();
        List<String> columnNames = new ArrayList<>();
        boolean isTableHeaderFound = false;
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            XSSFRow currentRow = sheet.getRow(i);
            if (skipRow(currentRow)) continue;
            if (!isTableHeaderFound) {
                setTableHeader(currentRow, columnNames);
                isTableHeaderFound = true;
                continue;
            }
            readRowValues(data, currentRow, columnNames);
        }
        printData(data);
    }

读取行方法:

private static void readRowValues(Map<Long, List<Column>> data, XSSFRow currentRow, List<String> columnNames) {
        Long code = -1L;
        List<Column> rowValues = new ArrayList<>();
        for (short cellIdx = currentRow.getFirstCellNum(); cellIdx < currentRow.getLastCellNum(); cellIdx++) {
            if (cellIdx == currentRow.getFirstCellNum()) {
                code = Long.valueOf((int)currentRow.getCell(cellIdx).getNumericCellValue());
            } else {
                rowValues.add(new Column(columnNames.get(cellIdx), Double.valueOf(currentRow.getCell(cellIdx).getNumericCellValue())));
            }
        }
        data.put(code, rowValues);
    }

读取单元格列名称:

    private static void setTableHeader(XSSFRow currentRow, List<String> columnNames) {
        for (short cellIdx = currentRow.getFirstCellNum(); cellIdx < currentRow.getLastCellNum(); cellIdx++)
            columnNames.add(currentRow.getCell(cellIdx).getStringCellValue());
    }

确定是否应跳过行的辅助方法:

    private static boolean skipRow(XSSFRow currentRow) {
        if(currentRow == null) return  true;
        if (currentRow.getLastCellNum() == currentRow.getFirstCellNum()) return true;
        for (short cellIdx = currentRow.getFirstCellNum(); cellIdx < currentRow.getLastCellNum(); cellIdx++)
            if (currentRow.getCell(cellIdx) != null)
                return false;
        return true;
    }

最后打印结果:

  private static void printData(Map<Long, List<Column>> data) {
        for (Map.Entry<Long, List<Column>> entry : data.entrySet()) {
            for (Column column : entry.getValue()) {
                System.out.println(entry.getKey() + "," + column.getColumnName() + "," + column.getValue());
            }
        }
    }