重庆网站建设 快速建站,罗湖网站建设哪家好,广东省消防建设工程申报网站,网页设计与制作教程资源目录 0 EasyExcel简介1 Excel导出工具类设置自定义表头样式设置自适应列宽添加合计行 2 调用导出工具类导出Excel表3 测试结果 0 EasyExcel简介
在数据处理和报表生成的过程中#xff0c;Excel是一个非常常用的工具。特别是在Java开发中#xff0c;EasyExcel库因其简单高效而… 目录 0 EasyExcel简介1 Excel导出工具类设置自定义表头样式设置自适应列宽添加合计行 2 调用导出工具类导出Excel表3 测试结果 0 EasyExcel简介
在数据处理和报表生成的过程中Excel是一个非常常用的工具。特别是在Java开发中EasyExcel库因其简单高效而备受欢迎。
EasyExcel是阿里巴巴开源的一个Excel处理库特别适用于大数据量的Excel文件的读写。它的优点在于使用简单、性能高效特别适合在Java项目中处理Excel文件。
1 Excel导出工具类
Excel导出工具类ExcelUtil类代码如下
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;Data
public class ExcelUtil{public static void downloadExcelWithHead(HttpServletResponse response, List? list, String sheetName, ListListString rowHead) throws IOException {if (list.isEmpty()) {throw new IllegalArgumentException(列表不能为空);}Class? clazz list.get(0).getClass();response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);String fileName URLEncoder.encode(sheetName, UTF-8).replaceAll(\\, %20);response.setHeader(Content-disposition, attachment;filename*utf-8 fileName .xlsx);ExcelWriter excelWriter EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).build();// 创建 ExcelWriterBuilderExcelWriterBuilder excelWriterBuilder EasyExcel.write(response.getOutputStream(), clazz).autoCloseStream(true).needHead(true);excelWriterBuilder.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());excelWriterBuilder.useDefaultStyle(false);// 创建 WriteSheet 并设置动态标题行WriteSheet writeSheet EasyExcel.writerSheet(sheetName).head(rowHead) // 设置标题行.registerWriteHandler(setCellStyle()) // 设置表头样式.registerWriteHandler(new CustomCellWriteUtil()) // 设置列宽自适应.build();// 写入数据excelWriter.write(list, writeSheet);excelWriter.finish();}}设置自定义表头样式
其中downloadExcelWithHead()中调用setCellStyle()方法用于设置表头样式。setCellStyle()方法的代码如下 public static HorizontalCellStyleStrategy setCellStyle() {// 表头策略WriteCellStyle headWriteCellStyle new WriteCellStyle();// 背景设置为白色headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//边框headWriteCellStyle.setBorderBottom(BorderStyle.THIN);headWriteCellStyle.setBorderLeft(BorderStyle.THIN);headWriteCellStyle.setBorderRight(BorderStyle.THIN);headWriteCellStyle.setBorderTop(BorderStyle.THIN);//自动换行headWriteCellStyle.setWrapped(true);WriteFont headWriteFont new WriteFont();headWriteFont.setBold(true);headWriteFont.setFontName(宋体);headWriteFont.setFontHeightInPoints((short)12);headWriteCellStyle.setWriteFont(headWriteFont);// 内容的策略WriteCellStyle contentWriteCellStyle new WriteCellStyle();// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 背景白色contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//边框contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);contentWriteCellStyle.setBorderRight(BorderStyle.THIN);contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//自动换行contentWriteCellStyle.setWrapped(true);//文字WriteFont contentWriteFont new WriteFont();// 字体大小contentWriteFont.setFontHeightInPoints((short)12);contentWriteFont.setFontName(宋体);contentWriteCellStyle.setWriteFont(contentWriteFont);return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);}设置自适应列宽
自适应列宽通过CustomCellWriteUtil类设置CustomCellWriteUtil类的代码如下
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** excel自适应列宽*/
public class CustomCellWriteUtil extends AbstractColumnWidthStyleStrategy {private static final int MAX_COLUMN_WIDTH 255;private MapInteger, MapInteger, Integer CACHE new HashMap(8);public CustomCellWriteUtil() {}protected void setColumnWidth(WriteSheetHolder writeSheetHolder, ListWriteCellData? cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {boolean needSetWidth isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {MapInteger, Integer maxColumnWidthMap (Map) CACHE.get(writeSheetHolder.getSheetNo());if (maxColumnWidthMap null) {maxColumnWidthMap new HashMap(16);CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);}Integer columnWidth this.dataLength(cellDataList, cell, isHead);if (columnWidth 0) {if (columnWidth 255) {columnWidth 255;}Integer maxColumnWidth (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());if (maxColumnWidth null || columnWidth maxColumnWidth) {((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), 7250);}}}}private Integer dataLength(ListWriteCellData? cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData cellData (CellData) cellDataList.get(0);CellDataTypeEnum type cellData.getType();if (type null) {return -1;} else {switch (type) {case STRING:return cellData.getStringValue().getBytes().length;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}
}添加合计行
合计行用于展示数据的总和或其他统计信息。在Controller中调用downloadExcelWithHead()方法导出Excel时添加合计行和自定义表头形式。
2 调用导出工具类导出Excel表
此处以导出产品明细表为例新建产品表如下 entity、mapper、service层省略在Controller层中添加一个导出产品明细表接口代码如下
import com.z.entity.Product;
import com.z.service.ProductService;
import com.z.util.ConvertUtil;
import com.z.util.ExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;RestController
RequestMapping(/product)
public class ProductController {Autowiredprivate ProductService productService;PostMapping(/export)public void export(HttpServletResponse response) throws IOException {ListProduct dataList productService.list();if(null dataList || dataList.isEmpty()){throw new RuntimeException(数据为空导出失败);}// 计算合计double totalProductCost dataList.stream().mapToDouble(data - data.getProductCost() ! null ? data.getProductCost() : 0.0).sum();int totalProductNum dataList.stream().mapToInt(data - data.getProductNum() ! null ? data.getProductNum() : 0).sum();// 创建合计行Product totalRow new Product();totalRow.setId(合计);totalRow.setProductCost(totalProductCost);totalRow.setProductNum(totalProductNum);// 添加合计行到列表dataList.add(totalRow);// 创建自定义表头ListListString rowHead createHead();// 导出 Excel(带标题列带合计列)ExcelUtil.downloadExcelWithHead(response, dataList, 产品明细表, rowHead);}/*** 设置Excel头* return*/private ListListString createHead() {ListListString list new ArrayList();// 第一列表头ListString headRow1 new ArrayList();headRow1.add(产品明细表报表);headRow1.add(test);headRow1.add(产品序号);list.add(headRow1);// 第二列表头ListString headRow2 new ArrayList();headRow2.add(产品明细表报表);headRow2.add(备注此处可调用方法取值);headRow2.add(产品编号);list.add(headRow2);// 第三列表头ListString headRow3 new ArrayList();headRow3.add(产品明细表报表);headRow3.add(备注此处可调用方法取值);headRow3.add(产品名称);list.add(headRow3);// 第四列表头ListString headRow4 new ArrayList();headRow4.add(产品明细表报表);headRow4.add(无);headRow4.add(产品进价);list.add(headRow4);// 第五列表头ListString headRow5 new ArrayList();headRow5.add(产品明细表报表);headRow5.add(测试);headRow5.add(产品库存);list.add(headRow5);return list;}其中在使用createHead()方法设置Excel头时可以调用service层中的方法自定义显示特定值。
3 测试结果
最终使用postman调用该导出接口导出的表格结果如下
