导出样式
代码
前端Vue
handleAcceptExport() {
this.$modal.confirm('是否确认导出有数据列表？').then(() => {
this.exportLoading = true;
return LicensingAcceptExport(this.queryParams).then(res => {
const file = new Blob([res], { type: "application/vnd.ms-excel" });
const url = URL.createObjectURL(file);
const a = document.createElement("a");
a.href = url;
a.click();
this.exportLoading = false;
});
});
},后端
实体
@Data
public class ExcelExportVo {
// 这两个就是一级表头，最后一级表头对应的是具体的某个属性，它们都是被包裹在一级表头下的
@ExcelCollection(name = "许可申请受理情况")
private List<ApplyInfo> applyInfoList;
@ExcelCollection(name = "许可续办受理情况")
private List<RenewInfo> renewInfoList;
/**
* 许可申请受理情况
*/
@Data
public static class ApplyInfo{
// 二级表头可以用 groupName 实现
@Excel(name = "日期",width = 20)
private String date;
@Excel(name = "申请数", groupName = "申请情况")
private Integer applyValue1;
@Excel(name = "采编", groupName = "申请情况")
private Integer applyCollection1;
@Excel(name = "转载", groupName = "申请情况")
private Integer applyReprint1;
@Excel(name = "平台",groupName = "申请情况")
private Integer applyPlatform1;
@Excel(name = "申请数", groupName = "审核通过情况")
private Integer applyValue2;
@Excel(name = "采编", groupName = "审核通过情况")
private Integer applyCollection2;
@Excel(name = "转载", groupName = "审核通过情况")
private Integer applyReprint2;
@Excel(name = "平台",groupName = "审核通过情况")
private Integer applyPlatform2;
public ApplyInfo(String date, Integer applyValue1, Integer applyCollection1, Integer applyReprint1, Integer applyPlatform1, Integer applyValue2, Integer applyCollection2, Integer applyReprint2, Integer applyPlatform2) {
this.date = date;
this.applyValue1 = applyValue1;
this.applyCollection1 = applyCollection1;
this.applyReprint1 = applyReprint1;
this.applyPlatform1 = applyPlatform1;
this.applyValue2 = applyValue2;
this.applyCollection2 = applyCollection2;
this.applyReprint2 = applyReprint2;
this.applyPlatform2 = applyPlatform2;
}
}
/**
* 许可续办受理情况
*/
@Data
public static class RenewInfo{
@Excel(name = "申请数", groupName = "申请情况")
private Integer renewValue1;
@Excel(name = "采编", groupName = "申请情况")
private Integer renewCollection1;
@Excel(name = "转载", groupName = "申请情况")
private Integer renewReprint1;
@Excel(name = "平台",groupName = "申请情况")
private Integer renewPlatform1;
@Excel(name = "申请数", groupName = "审核通过情况")
private Integer renewValue2;
@Excel(name = "采编", groupName = "审核通过情况")
private Integer renewCollection2;
@Excel(name = "转载", groupName = "审核通过情况")
private Integer renewReprint2;
@Excel(name = "平台",groupName = "审核通过情况")
private Integer renewPlatform2;
public RenewInfo(Integer renewValue1, Integer renewCollection1, Integer renewReprint1, Integer renewPlatform1, Integer renewValue2, Integer renewCollection2, Integer renewReprint2, Integer renewPlatform2) {
this.renewValue1 = renewValue1;
this.renewCollection1 = renewCollection1;
this.renewReprint1 = renewReprint1;
this.renewPlatform1 = renewPlatform1;
this.renewValue2 = renewValue2;
this.renewCollection2 = renewCollection2;
this.renewReprint2 = renewReprint2;
this.renewPlatform2 = renewPlatform2;
}
}
}方法
@GetMapping("/LicensingAcceptExport")
public void LicensingAcceptExport(HttpServletResponse response, StatisSearchVo statis) {
List<String> nameList;
if (null != statis && null != statis.getBeginTime() && null != statis.getEndTime()) {
// 获取间隔时间。
nameList = DateUtil.getDateList(statis.getBeginTime(), statis.getEndTime(), 20);
} else {
// 为空获取前20天时间。
nameList = DateUtil.getDateList(new Date(), DateUtil.DIRECTION_NEGATIVE, 20);
}
String beginTime = nameList.get(0);
String endTime = nameList.get(nameList.size() - 1);
//查询单位注册审核柱图
String provinceId = statis.getProvinceId();
String[] provinceIdArray = null;
if (null != statis.getProvinceId()) {
provinceIdArray = provinceId.split("，");
}
List<ExcelExportVo> dataList = new ArrayList<>();
List<ExcelExportVo.ApplyInfo> applyInfoList = new ArrayList<>();
List<ExcelExportVo.RenewInfo> renewInfoList = new ArrayList<>();
StatisTaskVo taskVo = new StatisTaskVo();
taskVo.setProvinceIdArray(provinceIdArray);
taskVo.setBeginTime(beginTime);
taskVo.setEndTime(endTime);
//许可情况申请、续办受理数据列表
List<LicenseAcceptanceVo> acceptanceVo = statisTaskService.getApplyandRenew(taskVo);
SimpleDateFormat simpleDateFormat=new SimpleDateFormat("yyyy-MM-dd");
for (LicenseAcceptanceVo lists : acceptanceVo) {
String transformDate = simpleDateFormat.format(lists.getStatisDate());
applyInfoList.add(new ExcelExportVo.ApplyInfo(transformDate, lists.getApplySubmitCount(), lists.getApplySubmitCollectionCount(),
lists.getApplySubmitReprintCount(), lists.getApplySubmitPlatformCount(), lists.getApplyPassCount(), lists.getApplyPassCollectionCount(),
lists.getApplyPassReprintCount(), lists.getApplyPassPlatformCount()));
renewInfoList.add(new ExcelExportVo.RenewInfo(lists.getRenewSubmitCount(), lists.getRenewSubmitCollectionCount(), lists.getRenewSubmitReprintCount(),
lists.getRenewSubmitPlatformCount(), lists.getRenewPassCount(), lists.getRenewPassCollectionCount(), lists.getRenewPassReprintCount(),
lists.getRenewPassPlatformCount()));
}
ExcelExportVo vo = new ExcelExportVo();
vo.setApplyInfoList(applyInfoList);
vo.setRenewInfoList(renewInfoList);
dataList.add(vo);
// 第一个sheet
ExportParams params1 = new ExportParams();
params1.setStyle(ExcelStyleUtil.class);
params1.setSheetName("sheet1");
params1.setTitle("许可受理情况");
Map<String, Object> params1Map = new HashMap<>();
params1Map.put("title", params1);
params1Map.put("entity", ExcelExportVo.class);
params1Map.put("data", dataList);
// 将sheet封装
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(params1Map);
Workbook workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
ExcelUtilTwo.downLoadExcel(response, workBook);
}//多个sheet
// 第二个sheet
ExportParams params2 = new ExportParams();
params2.setStyle(ExcelStyleUtil.class);
params2.setSheetName("用户信息2");
Map<String, Object> params2Map = new HashMap<>();
params2Map.put("title", params2);
params2Map.put("entity", ExcelExportVo.class);
params2Map.put("data", new ArrayList<>());
// 第三个sheet
ExportParams params3 = new ExportParams();
params3.setStyle(ExcelStyleUtil.class);
params3.setSheetName("用户信息3");
Map<String, Object> params3Map = new HashMap<>();
params3Map.put("title", params3);
params3Map.put("entity", ExcelExportVo.class);
params3Map.put("data", new ArrayList<>());
// 将3个sheet封装
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(params1Map);
sheetsList.add(params2Map);
sheetsList.add(params3Map);
Workbook workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
ExcelUtil.downLoadExcel("用户信息",response,workBook);
}工具类ExcelUtil、ExcelStyleUtil
/**
* excel操作工具类
*/
public class ExcelUtil {
/**
* 偶数行设置背景色
*/
private static void setRowBackground(Workbook workbook){
Sheet sheet = workbook.getSheetAt(0);
CellStyle styles = ExcelStyleUtil.getStyles(workbook,false,(short) 12);
for(int i = 0; i <= sheet.getLastRowNum(); i ++) {
Row row = sheet.getRow(i);
if (i%2==0){
for(int j = 0; j < row.getPhysicalNumberOfCells(); j ++) {
Cell cell = row.getCell(j);
cell.setCellStyle(styles);
}
}
}
}
/**
* 导出设置隔行背景色
* @param params 导出参数
* @param list 数据
* @param pojoClass pojo类型
* @param isSetRowBackground 是否设置隔行背景色
*/
public static void exportExcel(ExportParams params, List<?> list, Class<?> pojoClass, boolean isSetRowBackground, HttpServletResponse response){
Workbook workbook = ExcelExportUtil.exportExcel(params,pojoClass,list);
if (workbook != null);
if (isSetRowBackground) setRowBackground(workbook);
downLoadExcel(response, workbook);
}
/**
* excel 导出
* @param list 数据
* @param title 标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 文件名称
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response){
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setStyle(ExcelStyleUtil.class);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* excel 导出
* @param list 数据
* @param pojoClass pojo类型
* @param fileName 文件名称
*/
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response){
ExportParams exportParams = new ExportParams();
exportParams.setStyle(ExcelStyleUtil.class);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* list map 导出
* @param list 数据
* @param fileName 文件名称
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
ExportParams exportParams = new ExportParams();
exportParams.setStyle(ExcelStyleUtil.class);
defaultExport(list, fileName, response);
}
/**
* 默认的 excel 导出
* @param list 数据
* @param pojoClass pojo类型
* @param fileName 文件名称
* @param exportParams 导出参数
*/
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams){
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(response, workbook);
}
/**
* 默认的 excel 导出
* @param list 数据
* @param fileName 文件名称
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
downLoadExcel(response, workbook);
}
/**
* 下载
* @param response
* @param workbook excel数据
*/
public static void downLoadExcel(HttpServletResponse response, Workbook workbook){
try {
// 格式化时间。
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmssSSS");
// 获取当前时间。
Date date = new Date();
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + new String((sdf.format(date) + ".xls").getBytes(), "ISO8859-1") + "\"");
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* excel 导入
* @param filePath excel文件路径
* @param titleRows 标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
*/
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setNeedSave(true);
params.setSaveUrl("/excel/");
try {
return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("模板不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
* @param file 上传的文件
* @param titleRows 标题行
* @param headerRows 表头行
* @param needVerify 是否检验excel内容
* @param pojoClass pojo类型
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) throws IOException {
if (file == null) {
return null;
}
try {
return importExcel(file.getInputStream(), titleRows, headerRows, needVerify, pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
* @param inputStream 文件输入流
* @param titleRows 标题行
* @param headerRows 表头行
* @param needVerify 是否检验excel内容
* @param pojoClass pojo类型
*/
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerify, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setSaveUrl("upload/excel/");
params.setNeedSave(true);
params.setNeedVerify(needVerify);
try {
return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* Excel 类型枚举
*/
enum ExcelTypeEnum {
XLS("xls"), XLSX("xlsx");
private String value;
ExcelTypeEnum(String value) {
this.value = value;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
}
/**
* 上传文件，返回一个workbook
* @param file
*/
public static Workbook importExcel(MultipartFile file) throws IOException {
File toFile = new File(file.getOriginalFilename());
Workbook workbook = null;
if(toFile.getPath().endsWith("xls")){
workbook = new HSSFWorkbook(file.getInputStream());
}else if(toFile.getPath().endsWith("xlsx")){
workbook = new XSSFWorkbook(file.getInputStream());
}else {
throw new RuntimeException("请确认你上传的文件类型");
}
return workbook;
}
/**
* 读取指定sheet的数据
* @param file 上传的文件
* @param sheetName 要读取的sheetName
* @param titleRows 表头行数
* @param headRows 标题行数
* @param startRows 表头之前有多少行不要的数据，从1开始，忽略空行
* @param readRows 要读取多少行数据，从0开始，比如读取十行，值就是9; 不指定时默认为0
* @param pojoClass 实体
*/
public static <T> List<T> importExcel(MultipartFile file,String sheetName,Integer titleRows,Integer headRows, Integer startRows,Integer readRows,Class<T> pojoClass) throws Exception {
Workbook workbook = importExcel(file);
int numberOfSheets = workbook.getNumberOfSheets();
List<T> list = null;
for (int i = 0; i < numberOfSheets; i++) {
String name = workbook.getSheetName(i).trim();
if (name.equals(sheetName) || name.endsWith(sheetName)){
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headRows);
params.setStartRows(startRows);
params.setReadRows(readRows);
//第几个sheet页
params.setStartSheetIndex(i);
final ExcelImportService excelImportService = new ExcelImportService();
ExcelImportResult<T> result = excelImportService.importExcelByIs(file.getInputStream(), pojoClass, params, false);
list = result.getList();
break;
}
}
return list;
}
/**
* 以map的形式导出表格
* @param list 数据
*/
public static <T> List<Map<String, Object>> objectToMap(List<T> list){
List<Map<String, Object>> result = new ArrayList<>();
Map<String, Object> map = null;
try {
for (T item : list) {
map = new HashMap<>();
Class<?> clazz = item.getClass();
for (Field field : clazz.getDeclaredFields()) {
field.setAccessible(true);
String fieldName = field.getName();
Object value = field.get(item);
map.put(fieldName, value);
}
result.add(map);
}
return result;
}catch (IllegalAccessException e){
e.printStackTrace();
}
return null;
}
/**
* 动态导出列，根据Excel注解获取列的字段注释（表头名）、宽度
* @param clazz
* @param fields 选择要导出的列
* @param changeHead 要更改表头的列，格式是{"字段1":"更改的表头1","字段2":"更改的表头2"}
*/
public static List<ExcelExportEntity> dynamicExport(Class<?> clazz,String fields, JSONObject changeHead) {
List<ExcelExportEntity> beanList = new ArrayList<>();
String[] split = fields.split(",");
int length = split.length;
try {
for (int i = 0; i < length; i++) {
Field f = clazz.getDeclaredField(split[i]);
Excel annotation = f.getAnnotation((Excel.class));
String comment = annotation.name();
if (changeHead != null && Objects.nonNull(changeHead.get(f.getName()))){
comment = changeHead.get(f.getName()).toString();
}
Double width = annotation.width();
beanList.add(new ExcelExportEntity(comment, f.getName(),width.intValue()));
}
}catch (NoSuchFieldException e){
e.printStackTrace();
}
return beanList;
}
/**
* 动态导出列（选择要忽略的列），根据Excel注解获取列的字段注释（表头名）、宽度
* @param clazz
* @param fields 选择要忽略的列
* @param changeHead 要更改表头的列，格式是{"字段名1":"更改的表头1","字段名2":"更改的表头2"}
*/
public static List<ExcelExportEntity> dynamicIgnoreExport(Class<?> clazz, String fields, JSONObject changeHead) {
List<ExcelExportEntity> beanList = new ArrayList<>();
String[] split = fields.split(",");
int length = split.length;
Field[] declaredFields = clazz.getDeclaredFields();
for (int i = 0; i < length; i++) {
for (Field f : declaredFields) {
if (f.getName().equals(split[i])) continue;
Excel annotation = f.getAnnotation((Excel.class));
if (annotation != null){
String comment = annotation.name();
if (changeHead != null && Objects.nonNull(changeHead.get(f.getName()))){
comment = changeHead.get(f.getName()).toString();
}
Double width = annotation.width();
beanList.add(new ExcelExportEntity(comment, f.getName(),width.intValue()));
}
}
}
return beanList;
}
/**
* 导出Excel，并在最后追加图片
* @param sheetName sheet名称
* @param wb HSSFWorkbook对象
*/
public static Workbook getWorkbook(String sheetName,Workbook wb, String imgUrl) throws IOException {
// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
}
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.getSheet(sheetName);
/*生成图表*/
if(!StringUtils.isEmpty(imgUrl)) {
String[] imgUrlArr = imgUrl.split("base64,");//拆分base64编码后部分
byte[] buffer = new BASE64Decoder().decodeBuffer(imgUrlArr[1]);
String picPath = System.getProperty("user.dir")+"\\upload\\image\\pic.png";
File file = new File(picPath);//图片文件
try {
//生成图片
OutputStream out = new FileOutputStream(file);//图片输出流
out.write(buffer);
out.flush();//清空流
out.close();//关闭流
ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中
BufferedImage bufferImg = ImageIO.read(new File(picPath));
ImageIO.write(bufferImg, "PNG", outStream);
// 利用HSSFPatriarch将图片写入EXCEL
Drawing<?> drawing = sheet.createDrawingPatriarch();
//位置：第1个单元格中x轴的偏移量、第1个单元格中y轴的偏移量、 第2个单元格中x轴的偏移量、 第2个单元格中y轴的偏移量、第1个单元格的列号、第1个单元格的行号、 第2个单元格的列号、第2个单元格的行号
//HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 8, (short) 10, 40);
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 6, 9, 40);
drawing.createPicture(anchor, wb.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
} catch (Exception ex) {
ex.printStackTrace();
}
if (file.exists()) {
file.delete();//删除图片
}
}
return wb;
}
}/**
* @Description: excel导出样式设置工具类
*/
public class ExcelStyleUtil implements IExcelExportStyler {
private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 10;
private static final short FONT_SIZE_ELEVEN = 10;
private static final short FONT_SIZE_TWELVE = 12;
/**
* 大标题样式
*/
private CellStyle headerStyle;
/**
* 每列标题样式
*/
private CellStyle titleStyle;
/**
* 数据行样式
*/
private CellStyle styles;
public ExcelStyleUtil(Workbook workbook) {
this.init(workbook);
}
/**
* 初始化样式
* @param workbook
*/
private void init(Workbook workbook) {
this.headerStyle = initHeaderStyle(workbook);
this.titleStyle = initTitleStyle(workbook,true,FONT_SIZE_ELEVEN);
this.styles = initStyles(workbook);
}
@Override
public CellStyle getHeaderStyle(short i) {
return headerStyle;
}
@Override
public CellStyle getTitleStyle(short i) {
return titleStyle;
}
@Override
public CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) {
return null;
}
@Override
public CellStyle getStyles(boolean b, ExcelExportEntity excelExportEntity) {
return styles;
}
@Override
public CellStyle getStyles(Cell cell, int i, ExcelExportEntity entity, Object o, Object o1) {
return getStyles(true, entity);
}
/**
* 设置隔行背景色
*/
public static CellStyle getStyles(Workbook workbook,boolean isBold,short size) {
CellStyle style = initTitleStyle(workbook,isBold,size);
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 初始化--大标题样式
*/
private static CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
return style;
}
/**
* 初始化--每列标题样式
*/
private static CellStyle initTitleStyle(Workbook workbook,boolean isBold,short size) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, size, isBold));
//背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
/**
* 初始化--数据行样式
*/
private static CellStyle initStyles(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 基础样式
*/
private static CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(true);
return style;
}
/**
* 字体样式
* @param size 字体大小
* @param isBold 是否加粗
*/
private static Font getFont(Workbook workbook, short size, boolean isBold) {
Font font = workbook.createFont();
//字体样式
font.setFontName("Arial");
//是否加粗
font.setBold(isBold);
//字体大小
font.setFontHeightInPoints(size);
return font;
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
