1,创建模板
2.pox.xml引入jar包
导入hutool相关的jar包
<dependency>
<groupId>net.sf.jxls</groupId>
<artifactId>jxls-core</artifactId>
<version>1.0.3</version>
</dependency>
3.创建工具类
public class ExportExcelToTemplateUtil {
private final Logger log = LoggerFactory.getLogger(ExportExcelToTemplateUtil.class);
@SuppressWarnings({ "unchecked", "rawtypes" })
public void exportToProveExcel(HttpServletResponse response, String filePath, String fileName, Class<?> clazz,
Object obj) throws Exception {
if (!obj.getClass().equals(clazz)) {
log.error("数据类型与传入的数据类型不一致!");
throw new Exception("数据类型与传入的数据类型不一致!");
} else {
try {
Map map = new HashMap();
Field[] fields = ReflectUtil.getFields(clazz);
for (Field field : fields) {
map.put(field.getName(), ReflectUtil.getFieldValue(obj, field.getName()));
}
File file1 = ResourceUtils.getFile(filePath);
FileInputStream is = new FileInputStream(file1);
// 实例化 XLSTransformer 对象
XLSTransformer xlsTransformer = new XLSTransformer();
// 获取 Workbook ,传入 模板 和 数据
Workbook workbook = xlsTransformer.transformXLS(is, map);
// 写出文件
response.setContentType("application/vnd.ms-excel;charset=utf-8");
ServletOutputStream out = null;
try {
fileName += DateUtil.format(new Date(), "YYYYMMddHHmmss");
// 设置请求头属性
response.setHeader("Content-Disposition",
"attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
out = response.getOutputStream();
// 输出
workbook.write(out);
// 此处记得关闭输出Servlet流
IoUtil.close(out);
} catch (Exception e) {
log.error(e.getMessage());
}
is.close();
out.flush();
out.close();
} catch (Exception e) {
log.error(e.getMessage());
e.printStackTrace();
}
}
}
4.组合数据并调用
templateUtil.exportToProveExcel(response,filePath,fileName,clazz, entity);
二、按照模板导出多个sheet到一个excel里面
模板创建与导包方式同上方单个sheet
/**
* 方法说明:同一个模板多个sheet导出工具类
*
* @author :
* @创建时间:2021年2月25日 下午4:07:34
* @param response
* @param filePath //模板文件位置
* @param fileName //文件名称
* @param clazz //数据类型(实体字段与模板文件中的el表达式的字段相同)
* @param list //数据集合
* @param cellRangeAddress//合并位置[开始行,结束行,开始列,结束列]
* @param temp //用于sheet名称的数据实体字段名(可为空)
* @param sheetName//sheet名称
* @throws Exception
* @return void
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public void exportAllToProveExcel(HttpServletResponse response, String filePath, String fileName, Class<?> clazz,
List<?> list, List<Integer[]> cellRangeAddress, String temp, String sheetName) throws Exception {
if (CollUtil.isNotEmpty(list)) {
if (!list.get(0).getClass().equals(clazz)) {
log.error("数据类型与传入的数据类型不一致!");
throw new Exception("数据类型与传入的数据类型不一致!");
} else {
try {
File file1 = ResourceUtils.getFile(filePath);
InputStream is = new FileInputStream(file1);
XSSFWorkbook workbook = new XSSFWorkbook(is);
XSSFSheet sheetModel = workbook.getSheetAt(0);// 获取到sheet模板
// 隐藏Sheet
workbook.removeSheetAt(0);
for (int i = 0; i < list.size(); i++) {
if (StringUtils.isNoneBlank(temp)) {
sheetName = ReflectUtil.getFieldValue(list.get(i), temp) + sheetName;
}
XSSFSheet sheet = workbook.createSheet(sheetName);
copySheet(workbook, sheetModel, sheet, sheetModel.getFirstRowNum(), sheetModel.getLastRowNum());
if (CollUtil.isNotEmpty(cellRangeAddress)) {
for(int k =0;k<cellRangeAddress.size();k++) {
Integer[] indexs = cellRangeAddress.get(k);
CellRangeAddress cra = new CellRangeAddress(indexs[0], indexs[1], indexs[2],indexs[3]);// 合并行列
// 把合并样式添加到sheet中
sheet.addMergedRegion(cra);
}
}
Map map = new HashMap();
Field[] fields = ReflectUtil.getFields(clazz);
for (Field field : fields) {
map.put(field.getName(), ReflectUtil.getFieldValue(list.get(i), field.getName()));
}
// 实例化 XLSTransformer 对象
XLSTransformer xlsTransformer = new XLSTransformer();
// 获取 Workbook ,传入 模板 和 数据
xlsTransformer.transformWorkbook(workbook, map);
}
is = new BufferedInputStream(new FileInputStream(file1));
byte[] buffer = new byte[is.available()];
is.read(buffer);
is.close();
// 清空response
response.reset();
// 设置response的Header
response.setHeader("Content-Disposition", "attachment;filename=" + new String(
(fileName + DateUtil.format(new Date(), "YYYYMMddHHmmss")).getBytes(), "ISO-8859-1"));
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
workbook.write(toClient);
toClient.flush();
toClient.close();
} catch (Exception e) {
log.error(e.getMessage());
e.printStackTrace();
}
}
}
}
@SuppressWarnings("deprecation")
private static void copySheet(XSSFWorkbook wb, XSSFSheet fromsheet, XSSFSheet newSheet, int firstrow,
int lasttrow) {
// 复制一个单元格样式到新建单元格
if ((firstrow == -1) || (lasttrow == -1) || lasttrow < firstrow) {
return;
}
XSSFRow fromRow = null;
XSSFRow newRow = null;
XSSFCell newCell = null;
XSSFCell fromCell = null;
// 设置列宽
for (int i = firstrow; i < lasttrow; i++) {
fromRow = fromsheet.getRow(i);
if (fromRow != null) {
for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
int colnum = fromsheet.getColumnWidth((short) j);
if (colnum > 100) {
newSheet.setColumnWidth((short) j, (short) colnum);
}
if (colnum == 0) {
newSheet.setColumnHidden((short) j, true);
} else {
newSheet.setColumnHidden((short) j, false);
}
}
break;
}
}
// 复制行并填充数据
for (int i = 0; i < lasttrow; i++) {
fromRow = fromsheet.getRow(i);
if (fromRow == null) {
continue;
}
newRow = newSheet.createRow(i - firstrow);
newRow.setHeight(fromRow.getHeight());
for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) {
fromCell = fromRow.getCell((short) j);
if (fromCell == null) {
continue;
}
newCell = newRow.createCell((short) j);
newCell.setCellStyle(fromCell.getCellStyle());
int cType = fromCell.getCellType();
newCell.setCellType(cType);
switch (cType) {
case HSSFCell.CELL_TYPE_STRING:
newCell.setCellValue(fromCell.getRichStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
newCell.setCellValue(fromCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
newCell.setCellValue(fromCell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(fromCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
newCell.setCellValue(fromCell.getErrorCellValue());
break;
default:
newCell.setCellValue(fromCell.getRichStringCellValue());
break;
}
}
}
}
调用方式:
templateUtil.exportAllToProveExcel(response, filePath, fileName, exportExcelVo.class, list, indexs, "filedName", sheetName);