Java读取Excel并合并表格中所需的列的数据

2018-01-13 11:15:43来源:http://575208196.iteye.com作者:小红想要人点击

分享
HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls

XSSFWorkbook:是操作Excel2007的版本,扩展名是.xlsx

对于不同版本的EXCEL文档要使用不同的工具类,如果使用错了,会提示如下错误信息。

org.apache.poi.openxml4j.exceptions.InvalidOperationException

org.apache.poi.poifs.filesystem.OfficeXmlFileException

.xlsx的文件支持1048576行,.xls只支持65535行(Row) 如果说文件数据量太大,要考虑分包处理,防止出现数据量太大 导致内存溢出等不必要的情况。
还有在取值时,要考虑的每列的数据类型,字符串对应字符串的取值,数字对应数字的取值方式,可以根据CellType来进行判断。

public class Sunny {

public static void main(String[] args) {
long startTime = System.currentTimeMillis();
// TODO Auto-generated method stub
String path = "d:/sunny"; // 存放excel文件路径

XSSFWorkbook hwb = new XSSFWorkbook();

// 设置工作簿
XSSFSheet sunnySheet = hwb.createSheet("sunny哎吆!");
// 设置表头
XSSFRow firstrow = sunnySheet.createRow(0);

firstrow.createCell(0).setCellValue("交易日期");
firstrow.createCell(1).setCellValue("摘要");
firstrow.createCell(2).setCellValue("借方发生额");
firstrow.createCell(3).setCellValue("贷方发生额");
firstrow.createCell(4).setCellValue("余额");
firstrow.createCell(5).setCellValue("凭证序号");
firstrow.createCell(6).setCellValue("营业机构号");
firstrow.createCell(7).setCellValue("对方户名");
firstrow.createCell(8).setCellValue("对方账号");

File f = new File(path);
File fa[] = f.listFiles();
List<Row> rowList = new ArrayList<Row>();
for (int i = 0; i < fa.length; i++) {
File targetFile = fa[i];
System.out.println(targetFile.getName());
Workbook book = null;
try {
InputStream is = new FileInputStream(targetFile);
book = new HSSFWorkbook(is);
} catch (Exception e) {
e.getStackTrace();
}
Sheet sheet = book.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
for (int j = 3; j <= rowNum; j++) {
Row row = sheet.getRow(j);
String cellValue = getCellValueWithString(row.getCell(2));
if (!(cellValue.equals("0")||cellValue.equals("0.0")||cellValue.equals("0.00"))) {
// String cell = row.getCell(1).getStringCellValue();
rowList.add(row);
// System.out.println(cell);
}else{
System.out.println("XXX");
}
}
}
System.out.println(rowList.size());
int k = 1;
for (Row row : rowList) {
XSSFRow createRow = sunnySheet.createRow(k);
createRow.createCell(0).setCellValue(row.getCell(0).getStringCellValue());
createRow.createCell(1).setCellValue(row.getCell(1).getStringCellValue());
Cell tmpCell2 = row.getCell(2);
Cell tmpCell3 = row.getCell(3);
switch (tmpCell2.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
createRow.createCell(2).setCellValue(tmpCell2.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
createRow.createCell(2).setCellValue(tmpCell2.getNumericCellValue());
break;
default:
createRow.createCell(2).setCellValue(tmpCell2.getNumericCellValue());
}
switch (tmpCell3.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
createRow.createCell(3).setCellValue(tmpCell3.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
createRow.createCell(3).setCellValue(tmpCell3.getNumericCellValue());
break;
default:
createRow.createCell(3).setCellValue(tmpCell3.getNumericCellValue());
}
createRow.createCell(4).setCellValue(row.getCell(4).getStringCellValue());
createRow.createCell(5).setCellValue(row.getCell(5).getStringCellValue());
createRow.createCell(6).setCellValue(row.getCell(6).getStringCellValue());
createRow.createCell(7).setCellValue(row.getCell(7).getStringCellValue());
createRow.createCell(8).setCellValue(row.getCell(8).getStringCellValue());
k++;
}

try {
hwb.write(new FileOutputStream("d://sunny//sunny_8-9.xls"));
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("合并代扣耗时{"+ (System.currentTimeMillis() - startTime)+"}ms");
}

private static String getCellValueWithString(Cell cell) {
if (cell == null) {
return "";
} else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
return String.valueOf(cell.getNumericCellValue());
} else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
return cell.getStringCellValue().trim();
} else {
return "";
}
}
}

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台