使用POI将不同的数据类型写入excel

Question

我试图将数据从数据库导出到Excel。我有导出的数据，当前存储在一个ArrayList中（这可以更改）。我已经能够将数据导出为ex​​cel，但所有值都以字符串形式导出，我需要它们保留其数据类型，即货币/数字。

我正在使用Apache POI，并且难以将字段的数据类型设置为字符串以外的任何其他字段。我错过了什么吗？有人可以建议我以更好的方式做到这一点？任何援助对此将不胜感激。

public static void importDataToExcel(String sheetName, ArrayList header, ArrayList data, File xlsFilename, int sheetNumber) 
     throws HPSFException, FileNotFoundException, IOException { 

    POIFSFileSystem fs = new POIFSFileSystem(); 
    HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(xlsFilename)); 
    HSSFSheet sheet = wb.createSheet(sheetName); 

    int rowIdx = 0; 
    short cellIdx = 0; 

    // Header 
    HSSFRow hssfHeader = sheet.createRow(rowIdx); 
    HSSFCellStyle cellStyle = wb.createCellStyle(); 
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
    for (Iterator cells = header.iterator(); cells.hasNext();) { 
     HSSFCell hssfCell = hssfHeader.createCell(cellIdx++); 
     hssfCell.setCellStyle(cellStyle); 
     hssfCell.setCellValue((String) cells.next()); 
    } 
    // Data 

    rowIdx = 1; 
    for (Iterator rows = data.iterator(); rows.hasNext();) { 
     ArrayList row = (ArrayList) rows.next(); 
     HSSFRow hssfRow = (HSSFRow) sheet.createRow(rowIdx++); 
     cellIdx = 0; 
     for (Iterator cells = row.iterator(); cells.hasNext();) { 
      HSSFCell hssfCell = hssfRow.createCell(cellIdx++); 
      hssfCell.setCellValue((String) cells.next()); 
     } 
    } 
    Logfile.log("sheetNumber = " + sheetNumber); 
    wb.setSheetName(sheetNumber, sheetName); 
    try { 
     FileOutputStream out = new FileOutputStream(xlsFilename); 
     wb.write(out); 
     out.close(); 
    } catch (IOException e) { 
     throw new HPSFException(e.getMessage()); 
    } 

} 

Answer 1

您需要检查类你的价值，你施放前：

public static void importDataToExcel(String sheetName, List<String> headers, List<List<Object>> data, File xlsFilename, int sheetNumber) 
     throws HPSFException, FileNotFoundException, IOException { 

    POIFSFileSystem fs = new POIFSFileSystem(); 
    Workbook wb; 
    try { 
     wb = WorkbookFactory.create(new FileInputStream(xlsFilename)); 
    } catch (InvalidFormatException ex) { 
     throw new IOException("Invalid workbook format"); 
    } 
    Sheet sheet = wb.createSheet(sheetName); 

    int rowIdx = 0; 
    int cellIdx = 0; 

    // Header 
    Row hssfHeader = sheet.createRow(rowIdx); 
    CellStyle cellStyle = wb.createCellStyle(); 
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
    for (final String header : headers) { 
     Cell hssfCell = hssfHeader.createCell(cellIdx++); 
     hssfCell.setCellStyle(cellStyle); 
     hssfCell.setCellValue(header); 
    } 
    // Data 

    rowIdx = 1; 
    for (final List<Object> row : data) { 
     Row hssfRow = sheet.createRow(rowIdx++); 
     cellIdx = 0; 
     for (Object value : row) { 
      Cell hssfCell = hssfRow.createCell(cellIdx++); 
      if (value instanceof String) { 
       hssfCell.setCellValue((String) value); 
      } else if (value instanceof Number) { 
       hssfCell.setCellValue(((Number) value).doubleValue()); 
      } else { 
       throw new RuntimeException("Cell value of invalid type " + value); 
      } 
     } 
    } 
    wb.setSheetName(sheetNumber, sheetName); 
    try { 
     FileOutputStream out = new FileOutputStream(xlsFilename); 
     wb.write(out); 
     out.close(); 
    } catch (IOException e) { 
     throw new HPSFException(e.getMessage()); 
    } 
} 

我还增加了仿制药 - 这使得代码更大量的可读性。您还需要尽可能避免使用实际的class，并使用interface，例如List而不是ArrayList和而不是HSSFRow。