`package com.haier.hsi.uap.util;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateTime;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import static cn.hutool.core.date.DateUtil.format;
/*
Easy Excel导出工具
@author
* @version 1.0
* @date
*/
@Slf4j
public class EasyexcelExportUtil {
**public static **HorizontalCellStyleStrategy generatorHorizontalCellStyleStrategy() {<br /> _// 头的策略<br /> _WriteCellStyle headWriteCellStyle = **new **WriteCellStyle();<br /> _// 背景设置<br /> _headWriteCellStyle.setFillForegroundColor(IndexedColors.**_WHITE_**.getIndex());<br /> WriteFont headWriteFont = **new **WriteFont();<br /> headWriteFont.setFontHeightInPoints((**short**) 10);<br /> headWriteFont.setBold(**false**);<br /> headWriteFont.setFontName(**"Arial"**);<br /> headWriteCellStyle.setWriteFont(headWriteFont);<br /> _// 内容的策略<br /> _WriteCellStyle contentWriteCellStyle = **new **WriteCellStyle();<br /> _// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定<br /> _contentWriteCellStyle.setFillPatternType(FillPatternType.**_SOLID_FOREGROUND_**);<br /> _// 背景<br /> _contentWriteCellStyle.setFillForegroundColor(IndexedColors.**_WHITE_**.getIndex());<br /> WriteFont contentWriteFont = **new **WriteFont();<br /> contentWriteFont.setFontHeightInPoints((**short**) 10);<br /> contentWriteFont.setBold(**false**);<br /> contentWriteFont.setFontName(**"Arial"**);<br /> contentWriteCellStyle.setWriteFont(contentWriteFont);<br /> contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.**_CENTER_**);<br /> contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.**_CENTER_**);<br /> contentWriteCellStyle.setBorderBottom(BorderStyle.**_THIN_**);<br /> contentWriteCellStyle.setBorderLeft(BorderStyle.**_THIN_**);<br /> contentWriteCellStyle.setBorderRight(BorderStyle.**_THIN_**);<br /> contentWriteCellStyle.setBorderTop(BorderStyle.**_THIN_**);<br /> contentWriteCellStyle.setShrinkToFit(**true**);<br /> _// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现<br /> _**return new **HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);<br /> }_/**<br /> * 导出Excel<br /> *<br /> * _**_@param __response<br /> _**_* _**_@param __fileName<br /> _**_* _**_@param __sheetName<br /> _**_* _**_@param __head<br /> _**_* _**_@param __list<br /> _**_*/<br /> _**public static void **exportExcel(HttpServletResponse response, String fileName, String sheetName, List<List<String>> head, List<List<Object>> list) {<br /> _// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman<br /> _response.setContentType(**"application/vnd.ms-excel"**);<br /> response.setCharacterEncoding(**"utf-8"**);<br /> _// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系<br /> _String excelFileName = **null**;<br /> **try **{<br /> excelFileName = URLEncoder._encode_(fileName + **"-"**, **"UTF-8"**).replaceAll(**"\\+"**, **"%20"**);<br /> response.setHeader(**"Content-disposition"**, **"attachment;filename*=utf-8''" **+ excelFileName + DateFormatUtils._format_(**new **Date(), DatePattern.**_PURE_DATETIME_PATTERN_**) + **".xlsx"**);<br /> ExcelWriter excelWriter = EasyExcel._write_(response.getOutputStream()).registerWriteHandler(EasyexcelExportUtil._generatorHorizontalCellStyleStrategy_()).build();<br /> _//这里 需要指定写用哪个class去写<br /> _WriteSheet writeSheet = EasyExcel._writerSheet_(0, sheetName).head(head).build();<br /> excelWriter.write(list, writeSheet);<br /> _//千万别忘记finish 会帮忙关闭流<br /> _excelWriter.finish();<br /> } **catch **(Exception e) {<br /> **_log_**.error(**"EasyExcel导出util异常，异常信息："**, e);<br /> }<br /> }<br />}
`
以下为依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.3.10</version>
</dependency>
以下为使用示例
`@RequestMapping(value = “/exportGgNoReaderList”, method = RequestMethod.GET)
public ExecuteResult
SysGgReaderDTO sysGgReaderDTO) throws Exception {
ExecuteResult
sysGgReaderDTO.setPageIndex(1);
sysGgReaderDTO.setPageSize(50000);
if (StringUtils.isEmpty(sysGgReaderDTO.getGgId())) {
result.setError(“公告ID不能为空!”);
return result;
}
String title = **"公告未读明细(导出数上限5万)"**;<br /> HashMap hashMap = **null**;<br /> **try **{<br /> hashMap = **sysGgCService**.queryGgNoReaderList(sysGgReaderDTO);<br /> List<SysGgReaderDTO> reportVoList = (List<SysGgReaderDTO>) hashMap.get(**"data"**);<br /> List<List<String>> headList = **new **ArrayList<>();<br /> headList.add(ListUtil._toLinkedList_(title, **"用户编码"**));<br /> headList.add(ListUtil._toLinkedList_(title, **"用户姓名"**));<br /> headList.add(ListUtil._toLinkedList_(title, **"用户类型"**));<br /> headList.add(ListUtil._toLinkedList_(title, **"工贸名称"**));<br /> headList.add(ListUtil._toLinkedList_(title, **"服务商名称"**));<br /> headList.add(ListUtil._toLinkedList_(title, **"未读时间"**));_// 数据<br /> _List<List<Object>> list = **new **ArrayList<>();<br /> **if **(CollectionUtil._isEmpty_(reportVoList)) {<br /> reportVoList = **new **ArrayList<>();<br /> }<br /> **for **(SysGgReaderDTO vo : reportVoList) {<br /> List<Object> data = **new **ArrayList<Object>();<br /> data.add(vo.getUserCode());<br /> data.add(vo.getFullName());<br /> data.add(vo.getUserType());<br /> data.add(vo.getRegionName());<br /> data.add(vo.getServicestationname());<br /> data.add(vo.getReaderDate());list.add(data);<br /> }<br /> EasyexcelExportUtil._exportExcel_(response, **"公告未读明细"**, title, headList, list);<br /> result.setSuccess(**true**);<br /> **return **result;<br /> } **catch **(Exception e) {<br /> result.setError(**"导出未读明细失败!"**);<br /> **return **result;<br /> }<br />}<br />`
若有收获，就点个赞吧
0 人点赞
