用Java读取Excel文件列

Question

我有一个Excel数据样本，需要读取并存储在数据库中。示例：

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

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

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

Answer 1

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

Answer 2

您还需要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();
}

Answer 3

我的方法是读取列名并将其存储在列表中，以便以后在读取具有实际数据的行时使用。在解析行中的数据后，结果被存储到映射中，代码作为关键字和对列表：单元格值和单元格标题名称。

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());
}

确定是否应跳过行的Helper方法：

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());
}
}
}