上一篇讲的是写一个带有标题头的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;
}