一、思路:
1、复制本地模板另存为另外的文件
2、准备数据
3、使用XSSFWorkbook,读写文本
二、代码
代码示例:
/**
*
* @param dir=request.getSession().getServletContext().getRealPath(""); 获取文件存放根路径
* @return
*/
public SimpleNetObject employeeExportNutrition(String dir) {
SimpleNetObject sno = new SimpleNetObject();
List<ExportNutrientVo> rows = "这一步是查询想要写到excel的数据,用list装起来";
if (rows != null) {
//模板路径
File template = new File(dir + "/data/product_nutrient.xlsx");
dir = dir + exportfile;
String filename = "/菜谱营养素" + DateUtil.getFormatDate(new Date(), "yyyyMMddHHmmss") + ".xlsx";
File filenewname = new File(dir + filename);
//先复制模板
fileChannelCopy(template, filenewname);
File file = new File(dir + filename);
try {
FileInputStream fileInputStream = new FileInputStream(file);
//因为操作的是xlsx,所以使用XSSFWorkbook
XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = wb.getSheetAt(0);
//公式强制执行
sheet.setForceFormulaRecalculation(true);
//填充数据的逻辑了,举个例子而已,其他逻辑不展示
for (int i = 0; i < rows.size(); i++) {
String skuname = String.valueOf(rows.get(i).getSku_name());
String dbz = String.valueOf(rows.get(i).getDbz());
sheet.getRow(i + 1).getCell(0).setCellValue("null".equals(skuname) ? "" : skuname);
sheet.getRow(i + 1).getCell(1).setCellValue("null".equals(dbz) ? "" : dbz);
}
FileOutputStream output = new FileOutputStream(dir + filename);
wb.write(output);//写入磁盘保存文件
output.close();
sno.setResult(1);
File pdf = new File(dir + filename);
if (pdf.exists()) {
//这里就是下载文件的逻辑了,下载逻辑不展示了
sno.setData(ossService.uploadFile(pdf, "export_file"));
return sno;
}
sno.setData(exportfile + filename);
} catch (Exception e) {
logger.error("导出菜谱营养素出错{}", e);
return new SimpleNetObject(99, "导出菜谱营养素出错");
}
}
return sno;
}
/**
* 文件复制
* 文件复制到其他路径,然后就单纯操作这个文件即可,不影响原本文件
* @param s
* @param t
*/
public void fileChannelCopy(File s, File t) {
try {
InputStream in = null;
OutputStream out = null;
try {
in = new BufferedInputStream(new FileInputStream(s), 1024);
out = new BufferedOutputStream(new FileOutputStream(t), 1024);
byte[] buffer = new byte[1024];
int len;
while ((len = in.read(buffer)) != -1) {
out.write(buffer, 0, len);
}
} finally {
if (null != in) {
in.close();
}
if (null != out) {
out.close();
}
}
} catch (Exception e) {
logger.error("文件复制出错{}",e);
}
}
三、模板结果展示