网站制作与设计,成都专业网站建设厂,win7自建网站,网站模板源码下载网目录
一、首先引入依赖
二、然后封装一个VO
三、Controller层
四、Service实现类
引用样式
自适应列宽
自适应行高
五、测试
postman
编辑 浏览器
异常 分配到这个任务了#xff0c;写个小demo记录下#xff0c;具体可参考EasyExcel官方文档
我用的是web上传…目录
一、首先引入依赖
二、然后封装一个VO
三、Controller层
四、Service实现类
引用样式
自适应列宽
自适应行高
五、测试
postman
编辑 浏览器
异常 分配到这个任务了写个小demo记录下具体可参考EasyExcel官方文档
我用的是web上传、下载那块代码 一、首先引入依赖 !-- easy Excel --dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion3.1.0/versionexclusionsexclusionartifactIdpoi-ooxml-schemas/artifactIdgroupIdorg.apache.poi/groupId/exclusion/exclusions/dependency
二、然后封装一个VO
Data
AllArgsConstructor
NoArgsConstructor
EqualsAndHashCode
public class ExportStudentInfoVO implements Serializable {private static final long serialVersionUID -3275970951989418695L;ExcelIgnore // 忽略导出private String stuId;ExcelProperty(学生姓名)private String stuName;ExcelProperty(学生性别)private String stuGender;ExcelProperty(学生年龄)private Integer stuAge;ExcelProperty(监护人联系方式)private String guardianPhone;DateTimeFormat(yyyy-MM-dd HH:mm:ss)ColumnWidth(21) //设置宽度ExcelProperty(value 入学时间)private Date createDate;
}三、Controller层
RestController
RequestMapping(info)
public class InfoController {Resourceprivate InfoService infoService;Operation(summary 学生信息导出)RequestMapping(value /excelDownload, method RequestMethod.GET, produces application/json; charsetutf-8)public void excelOrderContainerDownload(HttpServletResponse response){infoService.excelDownload(response);}}
四、Service实现类
这里的list模拟从DB中查到的数据
.registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/ .registerWriteHandler(new CustomCellWriteHeightConfig()) /*自适应行高*/ .registerWriteHandler(EasyExcelUtils.getStyleStrategy()) /*引用样式*/
以上三个是excel表格进行一个处理让其看起来更加美观如果要使用可以往下翻对应的代码复制使用不加也不影响导出
Service
Slf4j
public class InfoServiceImpl implements InfoService {Overridepublic void excelDownload(HttpServletResponse response) {ListExportStudentInfoVO list new ArrayList();list.add(new ExportStudentInfoVO(001,张三,男,18,18488789989, new Date()));list.add(new ExportStudentInfoVO(002,李四,女,21,15233337777, new Date()));list.add(new ExportStudentInfoVO(003,王五,男,19,15623332333, new Date()));try {response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);SimpleDateFormat sdf new SimpleDateFormat(yyyy.MM.dd);String currentDate sdf.format(new Date());// URLEncoder.encode 可以防止中文乱码String fileName URLEncoder.encode(学生信息列表 currentDate, UTF-8).replaceAll(\\, %20);response.setHeader(Content-disposition, attachment;filename*utf-8 fileName .xlsx);EasyExcel.write(response.getOutputStream(), ExportStudentInfoVO.class).sheet(学生信息).registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/.registerWriteHandler(new CustomCellWriteHeightConfig()) /*自适应行高*/.registerWriteHandler(EasyExcelUtils.getStyleStrategy()) /*引用样式*/.doWrite(list);} catch (Exception e) {log.error(导出失败~);e.printStackTrace();}}}
引用样式
package cn.homed.common.utils.excel;import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;public class EasyExcelUtils {/*** 设置excel样式*/public static HorizontalCellStyleStrategy getStyleStrategy() {// 头的策略 样式调整WriteCellStyle headWriteCellStyle new WriteCellStyle();// 头背景 浅绿headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());WriteFont headWriteFont new WriteFont();// 头字号headWriteFont.setFontHeightInPoints((short) 12);// 字体样式headWriteFont.setFontName(宋体);headWriteCellStyle.setWriteFont(headWriteFont);// 自动换行headWriteCellStyle.setWrapped(true);// 设置细边框headWriteCellStyle.setBorderBottom(BorderStyle.THIN);headWriteCellStyle.setBorderLeft(BorderStyle.THIN);headWriteCellStyle.setBorderRight(BorderStyle.THIN);headWriteCellStyle.setBorderTop(BorderStyle.THIN);// 设置边框颜色 25灰度headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());// 水平对齐方式headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 垂直对齐方式headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 内容的策略 宋体WriteCellStyle contentStyle new WriteCellStyle();// 设置垂直居中contentStyle.setWrapped(true);contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 设置 水平居中
// contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);WriteFont contentWriteFont new WriteFont();// 内容字号contentWriteFont.setFontHeightInPoints((short) 12);// 字体样式contentWriteFont.setFontName(宋体);contentStyle.setWriteFont(contentWriteFont);// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);}
}
自适应列宽
package cn.homed.common.utils.excel;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.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;import java.util.HashMap;
import java.util.List;
import java.util.Map;public class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {private final MapInteger, MapInteger, Integer CACHE new HashMap();Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, ListWriteCellData? cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {boolean needSetWidth isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {MapInteger, Integer maxColumnWidthMap CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k - new HashMap());Integer columnWidth this.dataLength(cellDataList, cell, isHead);// 单元格文本长度大于60换行if (columnWidth 0) {if (columnWidth 60) {columnWidth 60;}Integer maxColumnWidth maxColumnWidthMap.get(cell.getColumnIndex());if (maxColumnWidth null || columnWidth maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);Sheet sheet writeSheetHolder.getSheet();sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);}}}}/*** 计算长度* param cellDataList* param cell* param isHead* return*/private Integer dataLength(ListWriteCellData? cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData? cellData cellDataList.get(0);CellDataTypeEnum type cellData.getType();if (type null) {return -1;} else {switch (type) {case STRING:// 换行符数据需要提前解析好int index cellData.getStringValue().indexOf(\n);return index ! -1 ?cellData.getStringValue().substring(0, index).getBytes().length 1 : cellData.getStringValue().getBytes().length 1;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}
}
自适应行高
package cn.homed.common.utils.excel;import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;import java.util.Iterator;public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {/*** 默认高度*/private static final Integer DEFAULT_HEIGHT 300;Overrideprotected void setHeadColumnHeight(Row row, int relativeRowIndex) {}Overrideprotected void setContentColumnHeight(Row row, int relativeRowIndex) {IteratorCell cellIterator row.cellIterator();if (!cellIterator.hasNext()) {return;}// 默认为 1行高度int maxHeight 1;while (cellIterator.hasNext()) {Cell cell cellIterator.next();if (cell.getCellTypeEnum() CellType.STRING) {String value cell.getStringCellValue();int len value.length();int num 0;if (len 50) {num len % 50 0 ? len / 50 : len / 2 - 1;}if (num 0) {for (int i 0; i num; i) {value value.substring(0, (i 1) * 50 i) \n value.substring((i 1) * 50 i, len i);}}if (value.contains(\n)) {int length value.split(\n).length;maxHeight Math.max(maxHeight, length) 1;}}}row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));}
}
五、测试
测试的话可以用postman进行测试 或者把链接粘在浏览器上
postman
postman测试的时候记得点这个下拉框选择发送并下载 然后弹出这个界面点击保存 然后桌面上就可以看到已经成功的下载下来了数据也都是没问题的 浏览器
直接贴链接即可 可以看到数据也是没问题的 异常
最后讲一下刚开始我这个小demo没跑起来编译、运行都没问题一调接口就报错了
异常是 com.alibaba.excel.exception.ExcelGenerateException: java.lang.NoClassDefFoundError: org/apache/xmlbeans/impl/common/SystemCache
搜了一下是由于缺少了相关的依赖库或者版本不匹配所致可能需要添加 Apache POI 或者 XMLBeans 这些依赖库并且确保版本号是兼容的。
然后加上这两个依赖就可以了不知道你们有没有遇到 dependencygroupIdorg.apache.poi/groupIdartifactIdpoi/artifactIdversion4.1.2/version/dependencydependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion4.1.2/version/dependency
好了分享就到这里晚安
