实现新建excel或者直接读取项目中某个excel文件进行写入操作

上一篇讲的是写一个带有标题头的excel文件，其实是为本篇文章打下基础，这次要说的是先读取某个路径下的excel文件，如果为空，则直接创建一个，直接写入，不为空，先删除掉原有数据，再进行写入
public void writeExcel(List dataList, String personid) throws UnsupportedEncodingException {
OutputStream out = null;
//通过类加载器获取resource资源的路径，由于获取到的只能是classes文件下的，所以适当的做个处理
String path1 = this.getClass().getClassLoader().getResource("").getPath().replaceAll(“WEB-INF/classes/”, “”);
String filename = personid + “.xlsx”;
String path = path1 + “template/” + filename;
try {

    // 读取Excel文档
    File finalXlsxFile = new File(path);
    //如果之前已存在改用户的预览excel，直接读取并删除信息后重新写入，如果不存在该用户的文档，则先创建一个excel，再进行信息写入
    if (!finalXlsxFile.exists()) {
        CreateBlankExcel(path);
    }
    Workbook workBook = getWorkbok(finalXlsxFile);
    // sheet 对应一个工作页
    Sheet sheet = workBook.getSheetAt(0);
    /**
     * 删除原有数据，除了属性列
     */
    int rowNumber = sheet.getLastRowNum();    // 第一行从0开始算
    //System.out.println("原始数据总行数，除属性列：" + rowNumber);
    for (int i = 1; i <= rowNumber; i++) {
        Row row = sheet.getRow(i);
        sheet.removeRow(row);
    }
    /**
     * 往Excel中写新数据
     */
    for (int j = 0; j < dataList.size(); j++) {
        // 创建一行：从第二行开始，跳过属性列
        Row row = sheet.createRow(j + 1);
        for (int k = 0; k < 1; k++) {
            // 在一行内循环
            TmpPayments tmpPayments = dataList.get(j);
            Cell first = row.createCell(0);
            first.setCellValue(tmpPayments.getOrders());

            Cell second = row.createCell(1);
            second.setCellValue(tmpPayments.getOrgid());

            Cell third = row.createCell(2);
            third.setCellValue(tmpPayments.getPaybankaccount());

            Cell forth = row.createCell(3);
            forth.setCellValue(tmpPayments.getPaytypeid());

            Cell fifth = row.createCell(4);
            fifth.setCellValue(tmpPayments.getSettlementmode());

            Cell sixth = row.createCell(5);
            sixth.setCellValue(tmpPayments.getRecbankaccountname());

            Cell seventh = row.createCell(6);
            seventh.setCellValue(tmpPayments.getRecbankaccount());

            Cell eigth = row.createCell(7);
            eigth.setCellValue(tmpPayments.getPaymoney().doubleValue());

            Cell ninth = row.createCell(8);
            ninth.setCellValue(tmpPayments.getReccurrencyid());

            Cell tenth = row.createCell(9);
            tenth.setCellValue(tmpPayments.getIsprivate());

            Cell eleventh = row.createCell(10);
            eleventh.setCellValue(tmpPayments.getRecbanksid());
                 SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
            if (tmpPayments.getPaydate() != null) {
                try {
                    String date = sd.format(tmpPayments.getPaydate());
                    Cell twentyfirst = row.createCell(20);
                    twentyfirst.setCellValue(date);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            Cell twentythsecond = row.createCell(21);
            twentythsecond.setCellValue(tmpPayments.getMsg());

        }
    }
    // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效

    out = new FileOutputStream(path);
    workBook.write(out);
} catch (Exception e) {
    e.printStackTrace();
} finally {
    try {
        if (out != null) {
            out.flush();
            out.close();
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
}
System.out.println("数据导出成功");

}
//通过传入过来的文件目录，读取文件

• 判断Excel的版本,获取Workbook
• @param in
• @param filename
• @return
• @throws IOException
  */
  public static Workbook getWorkbok(File file) throws IOException {
  Workbook wb = null;
  FileInputStream in = new FileInputStream(file);
  if (file.getName().endsWith(“xls”)) { //Excel 2003
  wb = new HSSFWorkbook(in);
  } else if (file.getName().endsWith(“xlsx”)) { // Excel 2007/2010
  wb = new XSSFWorkbook(in);
  }
  return wb;
  }