建设工程施工合同司法解释二,网站seo监测,好玩网页传奇,南通制作网站公司因为项目很多地方需要使用导出数据excel的功能#xff0c;所以开发了一个简易的统一生成导出方法。
依赖 dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version4.0.1/version… 因为项目很多地方需要使用导出数据excel的功能所以开发了一个简易的统一生成导出方法。
依赖 dependency groupIdorg.apache.poi/groupId artifactIdpoi-ooxml/artifactId version4.0.1/version /dependency dependency groupIdorg.apache.poi/groupId artifactIdpoi/artifactId version4.0.1/version /dependency 定义注解
标题栏注解
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.IndexedColors;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;Target({ElementType.METHOD, ElementType.FIELD})
Retention(RetentionPolicy.RUNTIME)
public interface ExcelTitle {/*** 标题名称* return 默认空*/String titleName() ;/*** 标题背景* return 默认空*/IndexedColors titleBack() default IndexedColors.WHITE;/*** 标题文字大小* return 默认空*/short titleSize() default 14;/*** 标题文字颜色* return 黑色*/HSSFColor.HSSFColorPredefined titleColor() default HSSFColor.HSSFColorPredefined.BLACK;/*** 边框格式* return 细*/BorderStyle borderStyle() default BorderStyle.THIN;/*** 边框颜色* return 默认*/IndexedColors borderColor() default IndexedColors.AUTOMATIC;/*** 标题文字加粗* return 黑色*/boolean boldFont() default true;/*** 是否忽略* return 黑色*/boolean ignore() default false;/*** 排序* return 0*/int order() default 0;
}
数据栏注解
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** description excel导出结果配置*/
Target({ElementType.METHOD, ElementType.FIELD})
Retention(RetentionPolicy.RUNTIME)
public interface ExcelProperty {/*** 背景* return 默认空*/IndexedColors textBack() default IndexedColors.WHITE;/*** 内容类型,查看* link org.apache.poi.ss.usermodel.BuiltinFormats* return 默认TEXT*/String textType() default TEXT;/*** 文字大小* return 默认18*/short textSize() default 12;/*** 数据属于key:value时可以自定义转换,配置格式为key1value;key2value2;.....* return 默认空*/String textKv() default ;/*** 文字颜色* return 黑色*/HSSFColor.HSSFColorPredefined textColor() default HSSFColor.HSSFColorPredefined.BLACK;/*** 水平位置* return 水平居中*/HorizontalAlignment horizontal() default HorizontalAlignment.CENTER;/*** 垂直位置* return 垂直居中*/VerticalAlignment vertical() default VerticalAlignment.CENTER;/*** 文字加粗* return 不加粗*/boolean boldFont() default false;
}代码 标题栏格式生成 解析对象属性的ExcelTitle注解的信息并设置相关选项。
private CellStyle initTitleCellStyle(SXSSFWorkbook wb, Field titleField) {// 单元格样式XSSFCellStyle cellStyle (XSSFCellStyle) wb.createCellStyle();//水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);ExcelTitle excelTitle titleField.getAnnotation(ExcelTitle.class);//背景颜色if(excelTitle ! null excelTitle.titleBack() ! null){cellStyle.setFillForegroundColor(excelTitle.titleBack().getIndex());} else {cellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());}//文字的设置字体颜色Font font wb.createFont();if(excelTitle ! null excelTitle.titleColor() ! null){font.setColor(excelTitle.titleColor().getIndex());} else {font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());}font.setBold(excelTitle ! null excelTitle.boldFont());font.setFontHeightInPoints(excelTitle ! null excelTitle.titleSize() ! 0 ? excelTitle.titleSize(): 12);cellStyle.setFont(font);//设置为文本格式cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat(TEXT));cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//边框if(excelTitle ! null excelTitle.borderStyle() ! null){cellStyle.setBorderTop(excelTitle.borderStyle());cellStyle.setBorderBottom(excelTitle.borderStyle());cellStyle.setBorderLeft(excelTitle.borderStyle());cellStyle.setBorderRight(excelTitle.borderStyle());} else {cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);}//边框if(excelTitle ! null excelTitle.borderColor() ! null){cellStyle.setTopBorderColor(excelTitle.borderColor().getIndex());cellStyle.setBottomBorderColor(excelTitle.borderColor().getIndex());cellStyle.setLeftBorderColor(excelTitle.borderColor().getIndex());cellStyle.setRightBorderColor(excelTitle.borderColor().getIndex());} else {cellStyle.setTopBorderColor(IndexedColors.RED.getIndex());cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex());cellStyle.setLeftBorderColor(IndexedColors.RED.getIndex());cellStyle.setRightBorderColor(IndexedColors.RED.getIndex());}return cellStyle;}
数据栏格式生成 解析对象属性的ExcelProperty注解的信息并设置相关选项。
private CellStyle initCellStyle(SXSSFWorkbook wb, Field titleField) {// 单元格样式垂直居中XSSFCellStyle cellStyle (XSSFCellStyle) wb.createCellStyle();ExcelProperty excelProperty titleField.getAnnotation(ExcelProperty.class);//水平居中if(excelProperty ! null excelProperty.horizontal() ! null) {cellStyle.setAlignment(excelProperty.horizontal());}//垂直居中if(excelProperty ! null excelProperty.vertical() ! null) {cellStyle.setVerticalAlignment(excelProperty.vertical());}//背景颜色if(excelProperty ! null excelProperty.textBack() ! null){cellStyle.setFillForegroundColor(excelProperty.textBack().getIndex());}//文字的设置字体颜色Font font wb.createFont();if(excelProperty ! null excelProperty.textColor() ! null){font.setColor(excelProperty.textColor().getIndex());}font.setBold(excelProperty ! null excelProperty.boldFont());font.setFontHeightInPoints(excelProperty ! null excelProperty.textSize() ! 0 ? excelProperty.textSize(): 12);cellStyle.setFont(font);cellStyle.setBorderTop(BorderStyle.THIN);cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);//设置为文本格式cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat(excelProperty ! null excelProperty.textType() ! null ? excelProperty.textType() : TEXT));cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);return cellStyle;}
同时提供一个默认的配置
private CellStyle initDefaultCellStyle(SXSSFWorkbook wb) {// 单元格样式垂直居中XSSFCellStyle cellStyle (XSSFCellStyle) wb.createCellStyle();//水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);//垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置为文本格式cellStyle.setDataFormat(BuiltinFormats.getBuiltinFormat(TEXT));//文字的设置Font font wb.createFont();font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); //设置字体颜色return cellStyle;}
解析数据生成sheet
private SXSSFWorkbook creatBook(ListExcelSheetVo list) {//创建工作簿SXSSFWorkbook wb new SXSSFWorkbook();for (ExcelSheetVo excelSheetVo : list) {createSXSSFSheet(excelSheetVo, wb);}return wb;}private SXSSFSheet createSXSSFSheet(ExcelSheetVo excelSheetVo, SXSSFWorkbook wb) {//创建工作簿SXSSFSheet sxssfSheet wb.createSheet(excelSheetVo.getSheetName());//设置默认的行宽sxssfSheet.setDefaultColumnWidth(20);//设置morning的行高不能设置太小可以不设置sxssfSheet.setDefaultRowHeight((short) 300);//设置morning的单元格格式//CellStyle style initCellStyle(wb);//标题单元格格式//CellStyle titleStyle initTitleCellStyle(wb, excelSheetVo.getDataClass());//初始化标题栏initTitle(wb, sxssfSheet, excelSheetVo.getDataClass());initSheetData(wb, sxssfSheet, excelSheetVo, 1);return sxssfSheet;}private void initSheetData(SXSSFWorkbook wb, SXSSFSheet sxssfSheet, ExcelSheetVo excelSheetVo, int dataStartLine) {if (CollectionUtils.isEmpty(excelSheetVo.getSheetData())) {return;}try {for (Object dataObject : excelSheetVo.getSheetData()) {SXSSFRow row sxssfSheet.createRow(dataStartLine);Field[] fields dataObject.getClass().getDeclaredFields();ListField fieldList Arrays.stream(fields).filter(item - item.getAnnotation(ExcelTitle.class) ! null !item.getAnnotation(ExcelTitle.class).ignore()).sorted(Comparator.comparingInt(item - {ExcelTitle excelTitle item.getAnnotation(ExcelTitle.class);return excelTitle.order();})).collect(Collectors.toList());for (int i 0; i fieldList.size(); i) {//根据title的值对应的值SXSSFCell cell row.createCell(i);Field field fieldList.get(i);cell.setCellStyle(initCellStyle(wb, field));field.setAccessible(true);cell.setCellValue(dealValue(field, dataObject));}dataStartLine;}} catch (Exception e){LOGGER.error(生成数据异常, e);}}
key:value数据解析
private String dealValue(Field field, Object dataObject) throws IllegalAccessException {ExcelProperty excelProperty field.getAnnotation(ExcelProperty.class);Object value field.get(dataObject);if(value null){return null;}if(excelProperty ! null StringUtils.isNotEmpty(excelProperty.textKv())){String[] kvs excelProperty.textKv().split(;);MapString, String map new HashMap();for(String str: kvs){map.put(str.split()[0], str.split()[1]);}return map.get(value.toString());}return value.toString();}
导出设置
/*** 导出excel数据** param response 亲求返回* param list 每个sheet页的数据一个elist表示一个sheet页* param fileName 导出的名称* return 结果*/public boolean creatExcel(HttpServletResponse response, ListExcelSheetVo list, String fileName) {SXSSFWorkbook wb creatBook(list);//导出数据try {//设置Http响应头告诉浏览器下载这个附件response.reset();response.setCharacterEncoding(utf-8);response.setContentType(application/vnd.ms-excel);//名称要从新进行 ISO8859-1 编码否则会文件名称会乱码response.setHeader(Content-Disposition, attachment;Filename encodeFileName(fileName) .xlsx);OutputStream outputStream response.getOutputStream();ByteArrayOutputStream baos new ByteArrayOutputStream();wb.write(baos);outputStream.write(baos.toByteArray());baos.flush();baos.close();outputStream.close();} catch (Exception ex) {LOGGER.error(导出excel失败, ex);}return true;}private String encodeFileName(String fileName) {try {//fileName java.net.URLEncoder.encode(fileName, UTF-8);fileName URLDecoder.decode(fileName, UTF-8);return new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);} catch (UnsupportedEncodingException e) {return 未命名;}}
测试
定义数据实体
数据标题和数据定义
Data
public class TestExcelVo {ExcelTitle(titleName 名称, titleColor HSSFColor.HSSFColorPredefined.BLUE, borderStyle BorderStyle.HAIR, titleSize 12, titleBack IndexedColors.YELLOW)ExcelProperty()private String name;ExcelTitle(titleName 年龄, titleColor HSSFColor.HSSFColorPredefined.GREEN, borderStyle BorderStyle.HAIR, titleBack IndexedColors.RED)ExcelProperty(textType 0, textColor HSSFColor.HSSFColorPredefined.RED)private Integer age;ExcelTitle(titleName 性别, titleColor HSSFColor.HSSFColorPredefined.BLUE, titleSize 12, titleBack IndexedColors.GREEN)ExcelProperty(textKv 0男;1女, textBack IndexedColors.BROWN)private Integer sex;ExcelTitle(titleName 描述, titleColor HSSFColor.HSSFColorPredefined.BLUE, titleBack IndexedColors.GREEN)ExcelProperty(boldFont true, textSize 18)private String des;
}
sheet的数据定义
Data
public class ExcelSheetVoT {/*** 每一个sheet页得名称*/private String sheetName;/*** 每个sheet里面得数据* 其中Object中得注解必须时包含 ExcelTitle 和 ExcelProperties*/private ListT sheetData;/*** 数据对象得类型*/private Class dataClass;
}
测试代码
RestController
RequestMapping(value exceExport)
public class ExcelExportController {GetMapping(testExport)public void testExport(HttpServletResponse response){String fileName 测试sheet;ListExcelSheetVo sheetVoList new ArrayList();for(int i 0; i 3; i){sheetVoList.add(createSheetData(i));}new ExcelCreateUtil().creatExcel(response, sheetVoList, fileName);}private ExcelSheetVoTestExcelVo createSheetData(int index){ExcelSheetVoTestExcelVo excelSheetVo new ExcelSheetVo();excelSheetVo.setDataClass(TestExcelVo.class);excelSheetVo.setSheetName(sheet index);ListTestExcelVo testExcelVos createTestExcelVo(new Random().nextInt(30) 10, sheet index);excelSheetVo.setSheetData(testExcelVos);return excelSheetVo;}private ListTestExcelVo createTestExcelVo(int size, String sheetName){ListTestExcelVo testExcelVos new ArrayList();for(int i 0; i size; i){TestExcelVo testExcelVo new TestExcelVo();testExcelVo.setName(sheetName - i);testExcelVo.setAge(i);testExcelVo.setSex(i % 2);testExcelVo.setDes(哈哈哈哈哈 i);testExcelVos.add(testExcelVo);}return testExcelVos;}
}
结果
