时间戳

首页 > 解决方案 > 在 Java 中读取 Excel 文件列

java - 在 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>");
        }

标签： javaexcelapache-poi

解决方案

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

推荐阅读