POI导出分颜色 热乎的

热乎的"/>

POI导出分颜色 热乎的

		今天是在原来导出工具中加入了添加颜色 

注解

/*** @Auther: Lming* @Date: 2020/5/28 11:37* @Description:*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {String value() default "";int col() default 0;
}

枚举

/*** @Auther: Lming* @Date: 2020/5/28 11:37* @Description:*/
public enum ExcelColorEnums {WHITE(0 ,"白", IndexedColors.WHITE.getIndex()),RED(1 ,"赤", IndexedColors.RED.getIndex()),ORANGE(2 ,"橙", IndexedColors.ORANGE.getIndex()),YELLOW(3 ,"黄", IndexedColors.YELLOW.getIndex()),GREEN(4 ,"绿", IndexedColors.GREEN.getIndex()),TEAL(5 ,"青", IndexedColors.TEAL.getIndex()),BLUE(6 ,"蓝", IndexedColors.BLUE.getIndex()),VIOLET(7 ,"紫", IndexedColors.VIOLET.getIndex());private int index;private String name;private int color;private ExcelColorEnums(int index, String name, int color) {this.index = index;this.name = name;this.color = color;}public int getIndex() {return index;}public String getName() {return name;}public int getColor() {return color;}public static ExcelColorEnums getType(int index) {for(ExcelColorEnums type : ExcelColorEnums.values()) {if(type.index == index) {return type;}}return null;}
}

util

/*** @Auther: Lming* @Date: 2020/5/28 11:37* @Description:*/
public class ExcelColorUtil {private final static Logger log = LoggerFactory.getLogger(ExcelUtils.class);private final static String EXCEL2003 = "xls";private final static String EXCEL2007 = "xlsx";public static <T> List<T> readExcel(String path, Class<T> cls,MultipartFile file){String fileName = file.getOriginalFilename();if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {log.error("上传文件格式不正确");}List<T> dataList = new ArrayList<>();Workbook workbook = null;try {InputStream is = file.getInputStream();if (fileName.endsWith(EXCEL2007)) {
//                FileInputStream is = new FileInputStream(new File(path));workbook = new XSSFWorkbook(is);}if (fileName.endsWith(EXCEL2003)) {
//                FileInputStream is = new FileInputStream(new File(path));workbook = new HSSFWorkbook(is);}if (workbook != null) {//类映射  注解 value-->bean columnsMap<String, List<Field>> classMap = new HashMap<>();List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());fields.forEach(field -> {ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);if (annotation != null) {String value = annotation.value();if (StringUtils.isBlank(value)) {return;//return起到的作用和continue是相同的 语法}if (!classMap.containsKey(value)) {classMap.put(value, new ArrayList<>());}field.setAccessible(true);classMap.get(value).add(field);}});//索引-->columnsMap<Integer, List<Field>> reflectionMap = new HashMap<>(16);//默认读取第一个sheetSheet sheet = workbook.getSheetAt(0);boolean firstRow = true;for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {Row row = sheet.getRow(i);//首行  提取注解if (firstRow) {for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {Cell cell = row.getCell(j);String cellValue = getCellValue(cell);if (classMap.containsKey(cellValue)) {reflectionMap.put(j, classMap.get(cellValue));}}firstRow = false;} else {//忽略空白行if (row == null) {continue;}try {T t = cls.newInstance();//判断是否为空白行boolean allBlank = true;for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {if (reflectionMap.containsKey(j)) {Cell cell = row.getCell(j);String cellValue = getCellValue(cell);if (StringUtils.isNotBlank(cellValue)) {allBlank = false;}List<Field> fieldList = reflectionMap.get(j);fieldList.forEach(x -> {try {handleField(t, cellValue, x);} catch (Exception e) {log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e);}});}}if (!allBlank) {dataList.add(t);} else {log.warn(String.format("row:%s is blank ignore!", i));}} catch (Exception e) {log.error(String.format("parse row:%s exception!", i), e);}}}}} catch (Exception e) {log.error(String.format("parse excel exception!"), e);} finally {if (workbook != null) {try {workbook.close();} catch (Exception e) {log.error(String.format("parse excel exception!"), e);}}}return dataList;}private static <T> void handleField(T t, String value, Field field) throws Exception {Class<?> type = field.getType();if (type == null || type == void.class || StringUtils.isBlank(value)) {return;}if (type == Object.class) {field.set(t, value);//数字类型} else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {if (type == int.class || type == Integer.class) {field.set(t, NumberUtils.toInt(value));} else if (type == long.class || type == Long.class) {field.set(t, NumberUtils.toLong(value));} else if (type == byte.class || type == Byte.class) {field.set(t, NumberUtils.toByte(value));} else if (type == short.class || type == Short.class) {field.set(t, NumberUtils.toShort(value));} else if (type == double.class || type == Double.class) {field.set(t, NumberUtils.toDouble(value));} else if (type == float.class || type == Float.class) {field.set(t, NumberUtils.toFloat(value));} else if (type == char.class || type == Character.class) {field.set(t, CharUtils.toChar(value));} else if (type == boolean.class) {field.set(t, BooleanUtils.toBoolean(value));} else if (type == BigDecimal.class) {field.set(t, new BigDecimal(value));}} else if (type == Boolean.class) {field.set(t, BooleanUtils.toBoolean(value));} else if (type == Date.class) {//field.set(t, value);} else if (type == String.class) {field.set(t, value);} else {Constructor<?> constructor = type.getConstructor(String.class);field.set(t, constructor.newInstance(value));}}private static String getCellValue(Cell cell) {if (cell == null) {return "";}if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {if (DateUtil.isCellDateFormatted(cell)) {return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();} else {return new BigDecimal(cell.getNumericCellValue()).toString();}} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {return StringUtils.trimToEmpty(cell.getStringCellValue());} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {return StringUtils.trimToEmpty(cell.getCellFormula());} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {return "";} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {return String.valueOf(cell.getBooleanCellValue());} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {return "ERROR";} else {return cell.toString().trim();}}private static CellStyle getCellStyle(Workbook wb, int  column) {CellStyle cellStyle = wb.createCellStyle();//short color = IndexedColors.WHITE.getIndex();short color = (short) ExcelColorEnums.getType(column).getColor();cellStyle.setFillForegroundColor(color);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//cellStyle.setAlignment(CellStyle.ALIGN_CENTER);cellStyle.setBorderBottom(BorderStyle.THIN);//下边框cellStyle.setBorderLeft(BorderStyle.THIN);//左边框cellStyle.setBorderRight(BorderStyle.THIN);//右边框cellStyle.setBorderTop(BorderStyle.THIN); //上边框Font font = wb.createFont();// font.setBoldweight(Font.BOLDWEIGHT_NORMAL);cellStyle.setFont(font);return  cellStyle;}public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls){Field[] fields = cls.getDeclaredFields();List<Field> fieldList = Arrays.stream(fields).filter(field -> {ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);if (annotation != null && annotation.col() > 0) {field.setAccessible(true);return true;}return false;}).sorted(Comparatorparing(field -> {int col = 0;ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);if (annotation != null) {col = annotation.col();}return col;})).collect(Collectors.toList());Workbook wb = new XSSFWorkbook();Sheet sheet = wb.createSheet("Sheet1");AtomicInteger ai = new AtomicInteger();{Row row = sheet.createRow(ai.getAndIncrement());AtomicInteger aj = new AtomicInteger();//写入头部fieldList.forEach(field -> {ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);String columnName = "";if (annotation != null) {columnName = annotation.value();}Cell cell = row.createCell(aj.getAndIncrement());CellStyle cellStyle = wb.createCellStyle();cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//cellStyle.setAlignment(CellStyle.ALIGN_CENTER);Font font = wb.createFont();// font.setBoldweight(Font.BOLDWEIGHT_NORMAL);cellStyle.setFont(font);cell.setCellStyle(cellStyle);cell.setCellValue(columnName);});}if (CollectionUtils.isNotEmpty(dataList)) {dataList.forEach(t -> {Row row1 = sheet.createRow(ai.getAndIncrement());AtomicInteger aj = new AtomicInteger();int  column = getColumn(t);CellStyle cellStyle = getCellStyle(wb, column);fieldList.forEach(field -> {Class<?> type = field.getType();Object value = "";try {value = field.get(t);} catch (Exception e) {e.printStackTrace();}Cell cell = row1.createCell(aj.getAndIncrement());if (value != null) {/*if (type == Date.class) {cell.setCellValue(value.toString());} else {cell.setCellValue(value.toString());}*/cell.setCellValue(value.toString());cell.setCellStyle(cellStyle);}});});}//冻结窗格wb.getSheet("Sheet1").createFreezePane(0, 1, 0, 1);//浏览器下载excelbuildExcelDocument("abbot.xlsx",wb,response);//生成excel文件
//        buildExcelFile(".\\default.xlsx",wb);}private static <T> int getColumn(T t) {int value = 0;Class<?> aClass = t.getClass();Field[] declaredFields = aClass.getDeclaredFields();for (Field f: declaredFields) {if ("cellColor".equals(f.getName())) {f.setAccessible(true);try {value = (int) f.get(t);} catch (IllegalAccessException e) {e.printStackTrace();}}}return  value;}/*** 浏览器下载excel* @param fileName* @param wb* @param response*/private static  void  buildExcelDocument(String fileName, Workbook wb,HttpServletResponse response){try {response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));response.flushBuffer();wb.write(response.getOutputStream());} catch (IOException e) {e.printStackTrace();}}/*** 生成excel文件* @param path 生成excel路径* @param wb*/private static  void  buildExcelFile(String path, Workbook wb){File file = new File(path);if (file.exists()) {file.delete();}try {wb.write(new FileOutputStream(file));} catch (Exception e) {e.printStackTrace();}}
}

示例

ExcelColorUtil.writeExcel(response, testList, test.class);