一、复制模板
private static File createNewFile() {
// 读取模板，并赋值到新文件************************************************************
// 文件模板路径
String path = (getSispPath());
File file = new File(path);
if (!file.exists()) {
logger.error("原模板文件不存在");
System.out.println("原模板文件不存在");
}
// 保存文件的路径
String realPath = file.getParent();
// 新的文件名
String newFileName = "statement-" + System.currentTimeMillis() + ".xlsx";
// 判断路径是否存在
File dir = new File(realPath);
if (!dir.exists()) {
dir.mkdirs();
}
// 写入到新的excel
File newFile = new File(realPath, newFileName);
try {
newFile.createNewFile();
// 复制模板到新文件
fileChannelCopy(file, newFile);
} catch (Exception e) {
e.printStackTrace();
}
return newFile;
}
写入数据
public static void exportExcel(SettlementBean bean,HttpServletResponse response) {
File newFile = createNewFile();
InputStream is = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
workbook = new XSSFWorkbook(is);// 创建个workbook，
// 获取第一个sheet
sheet = workbook.getSheetAt(0);
} catch (Exception e1) {
e1.printStackTrace();
}
if (sheet != null) {
try {
sheet.setForceFormulaRecalculation(true);//保留模板格式 计算公式
FileOutputStream fos = new FileOutputStream(newFile);
//写入数据
//batch
XSSFCell cell1 = sheet.getRow(2).getCell(3);
cell1.setCellValue("batch."+bean.getBatch());
sheet.shiftRows(rowIndex, sheet.getLastRowNum(), extenSize,true,false);//插入行
for (int j = rowIndex; j < rowIndex+extenSize; j++) {//创建表格
sheet.createRow(j);
for (int j2 = 4; j2 < 8; j2++) {
sheet.getRow(j).createCell(j2);
}}
workbook.write(fos);
fos.flush();
fos.close();
// 下载
InputStream fis = new BufferedInputStream(new FileInputStream(
newFile));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
response.reset();
response.setContentType("text/html;charset=UTF-8");
OutputStream toClient = new BufferedOutputStream(
response.getOutputStream());
response.setContentType("application/x-msdownload");
String newName = URLEncoder.encode(
"statement-" + bean.getMerchantNo() + ".xlsx",
"UTF-8");
response.addHeader("Content-Disposition",
"attachment;filename=\"" + newName + "\"");
response.addHeader("Content-Length", "" + newFile.length());
toClient.write(buffer);
toClient.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != is) {
is.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
代码并不能直接运行，需根据自己实际需求修改。