com.dongyue.util.common.ExportUtil Maven / Gradle / Ivy
The newest version!
package com.dongyue.util.common;
import com.dongyue.util.StringUtil;
import com.dongyue.util.anno.ExcelFiledStyleAnno;
import com.dongyue.util.anno.ExcelTitleStyleAnno;
import com.dongyue.util.anno.StandardExcelAttr;
import com.dongyue.util.model.CellValue;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExportUtil {
public static void setCellValue(Sheet sheet, CellValue cellValue, CellStyle cellStyle) {
Row row = sheet.getRow(cellValue.getRow());
if (row == null) {
row = sheet.createRow(cellValue.getRow());
}
Cell cell = row.getCell(cellValue.getColumn());
if (cell == null) {
cell = row.createCell(cellValue.getColumn());
}
cell.setCellValue(cellValue.getValue());
if (cellValue.getStyle() != null) {
cell.setCellStyle(cellValue.getStyle());
} else {
cell.setCellStyle(cellStyle);
}
if (cellValue.getColspan() != null &&
cellValue.getRowspan() != null &&
(cellValue.getColspan() != 0
|| cellValue.getRowspan() != 0)) {
CellRangeAddress cellAddresses = new CellRangeAddress(cellValue.getRow(),
cellValue.getRow() + cellValue.getRowspan(),
cellValue.getColumn(),
cellValue.getColumn() + cellValue.getColspan());
sheet.addMergedRegionUnsafe(cellAddresses);
setBorderStyle(BorderStyle.THIN, cellAddresses, sheet);
}
}
private static void setBorderStyle(BorderStyle border, CellRangeAddress cellAddresses, Sheet sheet) {
RegionUtil.setBorderBottom(border, cellAddresses, sheet);//下边框
RegionUtil.setBorderLeft(border, cellAddresses, sheet); //左边框
RegionUtil.setBorderRight(border, cellAddresses, sheet); //右边框
RegionUtil.setBorderTop(border, cellAddresses, sheet); //上边框
}
public static void border(HSSFSheet sheet, HSSFRow row, HSSFCellStyle cellStyle, Integer sheetMaxClomun, Integer sheetMaxRow) {
for (int i = 0; i < sheetMaxRow; i++) {
for (int j = 0; j < sheetMaxClomun; j++) {
Row row1 = sheet.getRow(i);
if (row1 == null) {
row1 = sheet.createRow(i);
}
Cell cell1 = row.getCell(j);
if (cell1 == null) {
cell1 = row.createCell(j);
}
cell1.setCellStyle(cellStyle);
}
}
}
public static Integer title(Integer listRow, Integer startRow,
Integer maxColumn, Map cloumnFeild, Class listItem, List list, HSSFWorkbook wb)
throws NoSuchFieldException {
if (listRow.equals(startRow)) {
//第一行打印标题
for (int i = 0; i < maxColumn; i++) {
String s = cloumnFeild.get(i);
if (StringUtil.isNotNull(s)) {
Field field1 = listItem.getDeclaredField(s);
update(field1, listRow, i, list, wb);
}
}
return 1;
}
return 0;
}
public static Integer value(Object item, Integer maxColumn, Map cloumnFeild, Class listItem, Integer listRow, List list, HSSFWorkbook wb
) throws IllegalAccessException, NoSuchFieldException {
//判断item是否有List 有的话需要先打印list
Integer itemRow = maxRow(item, 0);
Boolean assignableFrom = isList(item);
Integer addRow = 0;
if (assignableFrom) {
for (int i = 0; i < maxColumn; i++) {
String s = cloumnFeild.get(i);
if (StringUtil.isNotNull(s)) {
Field field1 = listItem.getDeclaredField(s);
Class fieldType = field1.getType();
boolean isCollection = fieldType.isAssignableFrom(List.class);
if (isCollection) {
StandardExcelAttr annotation = field1.getAnnotation(StandardExcelAttr.class);
if (annotation != null && annotation.forIn()) {
field1.setAccessible(true);
List collection = (List) field1.get(item);
Integer collectionIndex = listRow;
for (Object o : collection) {
Type genericType = field1.getGenericType();
ParameterizedType pt1 = (ParameterizedType) genericType;
Class listItem1 = (Class) pt1.getActualTypeArguments()[0];
Integer maxcolumn1 = 0;
Map cloumnFeild1 = new HashMap<>();
Field[] declaredFields = listItem1.getDeclaredFields();
for (Field declaredField : declaredFields) {
StandardExcelAttr standardExcelAttr = declaredField.getAnnotation(StandardExcelAttr.class);
if (standardExcelAttr != null) {
//导出列表
int column = standardExcelAttr.excelColumn();
maxcolumn1 = Math.max(column + 1, maxcolumn1);
cloumnFeild1.put(column, declaredField.getName());
}
}
addRow = value(o, maxcolumn1, cloumnFeild1, listItem1, collectionIndex, list, wb);
collectionIndex += addRow;
}
} else {
field1.setAccessible(true);
List collection = (List) field1.get(item);
Integer collectionIndex = listRow;
for (Object o : collection) {
list.add(createCellValue(field1,collectionIndex, i, o, 0, 0, null, wb));
collectionIndex++;
}
}
} else {
field1.setAccessible(true);
list.add(createCellValue(field1, listRow, i,field1.get(item), 0, itemRow - 1, null, wb));
}
}
}
addRow = (itemRow);
} else {
for (int i = 0; i < maxColumn; i++) {
String s = cloumnFeild.get(i);
if (StringUtil.isNotNull(s)) {
Field field1 = listItem.getDeclaredField(s);
field1.setAccessible(true);
list.add(createCellValue(field1, listRow, i, field1.get(item), 0, 0, null, wb));
}
}
addRow = 1;
}
return addRow;
}
private static void update(Field field1, Integer listRow, Integer i, List list, HSSFWorkbook wb) {
StandardExcelAttr annotation1 = field1.getAnnotation(StandardExcelAttr.class);
if (annotation1.useListTitle()) {
Class fieldType = field1.getType();
boolean assignableFrom = fieldType.isAssignableFrom(List.class);
if (assignableFrom) {
Type genericType = field1.getGenericType();
ParameterizedType pt1 = (ParameterizedType) genericType;
Class listItem1 = (Class) pt1.getActualTypeArguments()[0];
Field[] fields1 = listItem1.getDeclaredFields();
for (Field field2 : fields1) {
StandardExcelAttr annotation2 = field2.getAnnotation(StandardExcelAttr.class);
update(field2, listRow, annotation2.excelColumn(), list, wb);
}
}
} else {
if (!annotation1.useListTitle()) {
ExcelTitleStyleAnno annotation = field1.getAnnotation(ExcelTitleStyleAnno.class);
if(annotation!=null){
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(annotation.hAlign());
cellStyle.setVerticalAlignment(annotation.vAlign());
cellStyle.setFillForegroundColor(annotation.bgColor().index);
cellStyle.setBorderBottom(annotation.borderBottom());
cellStyle.setBorderTop(annotation.borderTop());
cellStyle.setBorderRight(annotation.borderRight());
cellStyle.setBorderLeft(annotation.borderLeft());
HSSFFont font = wb.createFont();
font.setBold(annotation.bold());
font.setColor(annotation.fontColor().index);
font.setFontHeightInPoints(annotation.fontHeightInTwips());
font.setFontName(annotation.fontName());
cellStyle.setFont(font);
CellValue cellValue1 = createCellValue(field1, listRow, i, annotation1.title(), 0, 0, cellStyle, wb);
list.add(cellValue1);
}else{
CellValue cellValue1 = createCellValue(field1, listRow, i, annotation1.title(), 0, 0, null, wb);
list.add(cellValue1);
}
}
}
}
public static CellValue createCellValue(Field field1, int nameRow, int column, Object value, int cloSpan, int rowSpan, CellStyle cellStyle, HSSFWorkbook wb) {
ExcelFiledStyleAnno annotation = field1.getAnnotation(ExcelFiledStyleAnno.class);
if(annotation!=null){
if(cellStyle==null){
cellStyle = wb.createCellStyle();
}
cellStyle.setAlignment(annotation.hAlign());
cellStyle.setVerticalAlignment(annotation.vAlign());
cellStyle.setFillForegroundColor(annotation.bgColor().index);
cellStyle.setBorderBottom(annotation.borderBottom());
cellStyle.setBorderTop(annotation.borderTop());
cellStyle.setBorderRight(annotation.borderRight());
cellStyle.setBorderLeft(annotation.borderLeft());
HSSFFont font = wb.createFont();
font.setBold(annotation.bold());
font.setColor(annotation.fontColor().index);
font.setFontHeightInPoints(annotation.fontHeightInTwips());
font.setFontName(annotation.fontName());
cellStyle.setFont(font);
}
//解析object参数
CellValue cellValue = new CellValue();
cellValue.setRow(nameRow);
cellValue.setColumn(column);
cellValue.setValue(formatValue(value,annotation==null?null:annotation.format()));
cellValue.setColspan(cloSpan);
cellValue.setRowspan(rowSpan);
cellValue.setStyle(cellStyle);
return cellValue;
}
private static String formatValue(Object value,String format) {
if (value == null) {
return "";
}
if (value instanceof Integer) {
return String.format("%d", (Integer) value);
} else if (value instanceof Double) {
return String.format("%.2f", (Double) value);
} else if (value instanceof String) {
return (String) value;
} else if (value instanceof Date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format((Date) value);
} else if (value instanceof LocalDateTime) {
if(StringUtil.isNull(format)){
format = "YYYY-MM-dd HH:mm：ss";
}
return ((LocalDateTime) value).format(DateTimeFormatter.ofPattern(format));
} else if (value instanceof LocalDate) {
if(StringUtil.isNull(format)) {
format = "YYYY-MM-dd";
}
return ((LocalDate) value).format(DateTimeFormatter.ofPattern(format));
} else if (value instanceof Long) {
return String.format("%d", (Long) value);
} else if (value instanceof Float) {
return String.format("%.2f", (Float) value);
} else if (value instanceof Boolean) {
return Boolean.toString((Boolean) value);
} else if (value instanceof Character) {
return Character.toString((Character) value);
} else if (value instanceof BigDecimal) {
return String.format("%.2f", (BigDecimal) value);
} else if (value instanceof BigInteger) {
return value.toString();
} else if (value instanceof Short) {
return String.format("%d", (Short) value);
} else if (value instanceof Byte) {
return String.format("%d", (Byte) value);
} else {
return value.toString();
}
}
private static Integer maxRow(Object item, Integer maxRow) throws IllegalAccessException {
Field[] fields = item.getClass().getDeclaredFields();
boolean assignableFrom = false;
Integer row = 0;
for (Field field1 : fields) {
Class fieldType = field1.getType();
assignableFrom = fieldType.isAssignableFrom(List.class);
if (assignableFrom) {
field1.setAccessible(true);
List collection = (List) field1.get(item);
for (Object o : collection) {
row += maxRow(o, maxRow);
}
break;
}
}
if (!assignableFrom) {
return 1;
}
return maxRow + row;
}
private static Boolean isList(Object item) {
Field[] fields = item.getClass().getDeclaredFields();
for (Field field1 : fields) {
Class fieldType = field1.getType();
boolean assignableFrom = fieldType.isAssignableFrom(List.class);
if (assignableFrom) {
return true;
}
}
return false;
}
public static HSSFCellStyle setDefaultStyle(HSSFWorkbook wb) {
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
return cellStyle;
}
public static void columnWidth(Sheet sheet, Integer sheetMaxClomun) {
for (int i = 0; i < sheetMaxClomun; i++) {
// 调整每一列宽度
sheet.autoSizeColumn(i);
// 解决自动设置列宽中文失效的问题
int i1 = sheet.getColumnWidth(i) * 17 / 10;
if(sheet.getColumnWidth(i)<=255){
sheet.setColumnWidth(i, i1);
}
}
}
}