Java读取Excel并合并表格中所需的列的数据_JAVA_编程开发_程序员俱乐部

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