导出excel
参考:/sunnyzyq/article/details/121994504
jra包
<!-- hutool工具包 --><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.4.1</version></dependency><!-- Excel包 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency>
1.ExcelByHuTools 工具代码
package com.user.service.dguser.uilt.excel;import cn.hutool.core.io.IoUtil;import cn.hutool.poi.excel.ExcelUtil;import cn.hutool.poi.excel.ExcelWriter;import cn.hutool.poi.excel.StyleSet;import com.user.service.dguser.config.ExportField;import com.user.service.dguser.uilt.RefUtil;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.SheetUtil;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.UnsupportedEncodingException;import java.lang.reflect.Field;import .URLEncoder;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;/*** @author dgzhang* @date /11/23 11:50*/@Slf4jpublic class ExcelByHuTools {public static <T> ExcelWriter exportExcelByHuTools(Class<T> pojo,List<T> data, String sheetName) throws InstantiationException, IllegalAccessException {Map<String, String> headers = getFieldAnnotation(pojo);return exportExcelByHuTools(headers, data, sheetName);}/*** 导出文件** @param sheetName* @param headers 表头* @param data数据* @param <T>*/public static <T> ExcelWriter exportExcelByHuTools(Map<String, String> headers,List<T> data, String sheetName) {long startTime = System.currentTimeMillis();//通过工具类创建writertry {//数据量特别大时，使用BigExcelWriter对象，可以避免内存溢出ExcelWriter writer = ExcelUtil.getBigWriter();//设置sheet的名称writer.renameSheet(sheetName);//设置表头的名称， 这个添加顺序和导出顺序相同headers.forEach(writer::addHeaderAlias);//写入数据writer.write(data, true);//表头设置字体CellStyle headCellStyle = writer.getHeadCellStyle();Font font = createFont(writer, true, false, "宋体", 18);headCellStyle.setFont(font);//普通单元格，全局样式设置Font font1 = createFont(writer, false, false, "宋体", 14);setBaseGlobalStyle(writer, font1);//调整自适应宽度int columnCount = writer.getColumnCount();for (int i = 0; i < columnCount; ++i) {double width = SheetUtil.getColumnWidth(writer.getSheet(), i, false);if (width != -1.0D) {width *= 256.0D;//此处可以适当调整，调整列空白处宽度width += 220D;writer.setColumnWidth(i, Math.toIntExact(Math.round(width / 256D)));}}log.info("写入记录耗时：[{}]ms", System.currentTimeMillis() - startTime);return writer;} catch (Exception e) {//如果导出异常，则生成一个空的文件e.printStackTrace();}return null;}/*** 设置基础字体样式** @param writer* @param bold是否粗体* @param italic* @param fontName 字体名称* @param fontSize 字体大小* @return*/public static Font createFont(ExcelWriter writer, boolean bold, boolean italic, String fontName, int fontSize) {Font font = writer.getWorkbook().createFont();//设置字体名称 宋体 / 微软雅黑 /等font.setFontName(fontName);//设置是否斜体font.setItalic(italic);//设置字体大小 以磅为单位font.setFontHeightInPoints((short) fontSize);//设置是否加粗font.setBold(bold);return font;}/*** 方法描述: 全局基础样式设置* 默认 全局水平居中+垂直居中* 默认 自动换行* 默认单元格边框颜色为黑色，细线条* 默认背景颜色为白色*/public static StyleSet setBaseGlobalStyle(ExcelWriter writer, Font font) {//全局样式设置StyleSet styleSet = writer.getStyleSet();//设置全局文本居中styleSet.setAlign(HorizontalAlignment.CENTER, VerticalAlignment.CENTER);//设置全局字体样式styleSet.setFont(font, true);//设置背景颜色 第二个参数表示是否将样式应用到头部styleSet.setBackgroundColor(IndexedColors.WHITE, false);//设置自动换行 当文本长于单元格宽度是否换行styleSet.setWrapText();// 设置全局边框样式styleSet.setBorder(BorderStyle.THIN, IndexedColors.BLACK);return styleSet;}//导出文档public static void export(HttpServletRequest request, HttpServletResponse response, ExcelWriter writer, String filename) throws IOException {setResponseHeader(request, response, filename);ServletOutputStream out = response.getOutputStream();writer.flush(out, true);writer.close();IoUtil.close(out);}//发送响应流方法public static void setResponseHeader(HttpServletRequest request, HttpServletResponse response, String fileName) throws UnsupportedEncodingException {response.setContentType("application/octet-stream;charset=UTF-8");String finalFileName = fileName;String userAgent = request.getHeader("user-agent");if (userAgent != null && userAgent.indexOf("Firefox") >= 0 || userAgent.indexOf("Chrome") >= 0 || userAgent.indexOf("Safari") >= 0) {finalFileName = new String((fileName).getBytes(), "ISO8859-1");} else {finalFileName = URLEncoder.encode(fileName, "UTF8"); //其他浏览器}response.setHeader("Content-Disposition", "attachment;filename=".concat(finalFileName));response.addHeader("Pargam", "no-cache");response.addHeader("Cache-Control", "no-cache");}/*** description 获取实体的所有字段，和对应注解值** @param pojo 实体* @author dgzhang* @version 1.0* @date /3/24 15:47*/public static Map<String, String> getFieldAnnotation(Class<?> pojo) throws IllegalAccessException, InstantiationException {Object object = pojo.newInstance();Field[] fields = object.getClass().getDeclaredFields();Map<String, String> resultMap = new LinkedHashMap<>();for (Field field : fields) {// 是否引用ApiModelProperty注解boolean bool = field.isAnnotationPresent(ExportField.class);if (bool) {String value = field.getAnnotation(ExportField.class).value();resultMap.put(field.getName(), value);}}return resultMap;}}
2.自定义注解
package com.user.service.dguser.config;import java.lang.annotation.*;@Retention(RetentionPolicy.RUNTIME)@Target(ElementType.FIELD)@Documentedpublic @interface ExportField {String value() default "";int type() default 0;}
3.实体类
package com.user.service.dguser.model;import com.user.service.dguser.config.ExportField;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.util.Date;@Data@AllArgsConstructor@NoArgsConstructorpublic class User2 {@ExportField(value = "学号")private String deptId;@ExportField(value = "姓名")private String name;@ExportField(value = "手机号")private String phone;@ExportField(value = "图像")private String avatar;@ExportField(value = "创建时间")private Date createdTime;}
4.测试
package com.user.service.dguser.controller;import cn.hutool.poi.excel.ExcelWriter;import com.user.service.dguser.model.User2;import com.user.service.dguser.uilt.excel.ExcelByHuTools;import org.springframework.beans.factory.annotation.Value;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.util.ArrayList;import java.util.Date;import java.util.LinkedHashMap;import java.util.List;@RestController@RequestMapping("test")public class ExcelOutController {@Value("${Excel.path:dd}")private String path;@GetMapping("export")public void export1(HttpServletRequest request, HttpServletResponse response) throws IOException {List<User2> list = new ArrayList<>();list.add(new User2("001", "小三", "13411112222", "三根毛", new Date()));list.add(new User2("002", "小四", "13411112222", "三根毛", new Date()));list.add(new User2("003", "", "13411112222", "三根毛", new Date()));list.add(new User2("004", "小六", "", "三根毛", new Date()));list.add(new User2("", "", "", "小七三根毛大家啊嘎啊噶跟家里刚啊刚来", new Date()));list.add(new User2("006", "小八", "13411112222", "三根毛", new Date()));try {ExcelWriter writer = ExcelByHuTools.exportExcelByHuTools(User2.class, list, "sheet1");ExcelByHuTools.export(request, response, writer, "dgzhang.xlsx");} catch (InstantiationException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}}}
