POI复制Excel模板并填充数据

一、复制模板

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();
			}
		}
}

代码并不能直接运行，需根据自己实际需求修改。