一、複制模闆
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();
}
}
}
代碼并不能直接運作，需根據自己實際需求修改。