Java对excel模板进行修改的通用类

导入处理excel的jar包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14</version>
</dependency>

然后是通用类

public class ExcelUtils {
	/*
	 * 导出excel
	 * 
	 * @param tempPath
	 * @param path
	 * @param list
	 * @return
	 */
	public String exportExcel(String tempPath, String path, List<Model> list) {
		File newFile = createNewFile(tempPath, path);
		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 {
				// 写数据
				FileOutputStream fos = new FileOutputStream(newFile);
				XSSFRow row = sheet.getRow(1);
				if (row == null) {
					row = sheet.createRow(1);
				}
				XSSFCell cell = row.getCell(0);
				if (cell == null) {
					cell = row.createCell(0);
				}
				cell.setCellValue("副标题");
				for (int i = 0; i < list.size(); i++) {
					Model vo = list.get(i);
					row = sheet.createRow(i + 4);// 从第五行开始
					CellStyle cellStyle = workbook.createCellStyle(); // 创建单元格样式
					cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置单元格水平方向对其方式
					cellStyle
							.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格垂直方向对其方式
					cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
					cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
					cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
					cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
					// 设置单元格的样式与内容
					createRowAndCellStyle(vo.getId(), row, cell, 0, cellStyle);
					createRowAndCellStyle(vo.getIssueTime(), row, cell, 1,
							cellStyle);
					createRowAndCellStyle(vo.getNeedTime(), row, cell, 2,
							cellStyle);
					createRowAndCellStyle(vo.getSignalType(), row, cell, 3,
							cellStyle);
					createRowAndCellStyle(vo.getSignalLevel(), row, cell, 4,
							cellStyle);
					createRowAndCellStyle(vo.getUnderWriter(), row, cell, 5,
							cellStyle);
				}

				workbook.write(fos);
				fos.flush();
				fos.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (null != is) {
						is.close();
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return newFile.getName();
	}

	/**
	 * 根据当前row行，来创建index标记的列数,并赋值数据(对样式做更改)
	 * 
	 * @param obj
	 * @param row
	 * @param cell
	 * @param index
	 */
	private void createRowAndCellStyle(Object obj, XSSFRow row, XSSFCell cell,
			int index, CellStyle cellStyle) {
		cell = row.getCell(index);
		if (cell == null) {
			cell = row.createCell(index);
		}
		if (obj != null) {
			cell.setCellValue(obj.toString());
		} else {
			cell.setCellValue("");
		}
		cell.setCellStyle(cellStyle);
	}

	/**
	 * 复制文件
	 * 
	 * @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) {
			e.printStackTrace();
		}
	}

	/**
	 * 读取excel模板，并复制到新文件中供写入和下载
	 * 
	 * @return
	 */
	public File createNewFile(String tempPath, String rPath) {
		// 读取模板，并赋值到新文件************************************************************
		// 文件模板路径
		String path = (tempPath);
		File file = new File(path);
		// 保存文件的路径
		String realPath = rPath;
		// 新的文件名
		String newFileName = 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;
	}

	/**
	 * 对时间格式进行设置
	 * 
	 * @param parseDate
	 * @return
	 */
	private String parseNeedDateFormat(String parseDate) {
		String afterTime = parseDate;
		SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat df2 = new SimpleDateFormat("yyyy年MM月dd日");
		try {
			Date time = df1.parse(parseDate);
			afterTime = df2.format(time);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return afterTime;
	}
}