com.eworkcloud.excel.util.ExportUtils Maven / Gradle / Ivy
package com.eworkcloud.excel.util;
import com.eworkcloud.excel.enmus.ValueType;
import com.eworkcloud.excel.model.ExcelColumn;
import com.eworkcloud.excel.model.ExportParams;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import static com.eworkcloud.excel.util.PoiUtils.COL_RATIO;
import static com.eworkcloud.excel.util.PoiUtils.DATE_FORMAT;
import static com.eworkcloud.excel.util.PoiUtils.DATE_TIME_FORMAT;
import static com.eworkcloud.excel.util.PoiUtils.ROW_RATIO;
import static com.eworkcloud.excel.util.PoiUtils.TIME_FORMAT;
public abstract class ExportUtils {
private static int getImageType(byte[] value) {
String type = PoiUtils.getExtendName(value);
if ("PNG".equalsIgnoreCase(type)) {
return Workbook.PICTURE_TYPE_PNG;
}
return Workbook.PICTURE_TYPE_JPEG;
}
private static String formatDate(Object value, String format) {
if (value instanceof LocalDate) {
DateTimeFormatter formatter;
if (StringUtils.hasText(format)) {
formatter = DateTimeFormatter.ofPattern(format);
} else {
formatter = DateTimeFormatter.ofPattern(DATE_FORMAT);
}
return formatter.format((LocalDate) value);
} else if (value instanceof LocalTime) {
DateTimeFormatter formatter;
if (StringUtils.hasText(format)) {
formatter = DateTimeFormatter.ofPattern(format);
} else {
formatter = DateTimeFormatter.ofPattern(TIME_FORMAT);
}
return formatter.format((LocalTime) value);
} else if (value instanceof LocalDateTime) {
DateTimeFormatter formatter;
if (StringUtils.hasText(format)) {
formatter = DateTimeFormatter.ofPattern(format);
} else {
formatter = DateTimeFormatter.ofPattern(DATE_TIME_FORMAT);
}
return formatter.format((LocalDateTime) value);
} else {
SimpleDateFormat formatter;
if (StringUtils.hasText(format)) {
formatter = new SimpleDateFormat(format);
} else {
formatter = new SimpleDateFormat(DATE_TIME_FORMAT);
}
return formatter.format((Date) value);
}
}
private static String formatNumber(Number value, String format) {
if (StringUtils.hasText(format)) {
DecimalFormat formatter = new DecimalFormat(format);
return formatter.format(value);
}
if (value instanceof BigDecimal) {
return ((BigDecimal) value).toPlainString();
} else {
return value.toString();
}
}
private static String replaceValue(String value, String[] replace) {
if (!ObjectUtils.isEmpty(replace)) {
for (String item : replace) {
String[] text = item.split("_");
if (value.equals(text[0])) {
value = text[1];
break;
}
}
}
return value;
}
private static String appendSuffix(String value, String suffix) {
if (StringUtils.hasText(suffix)) {
return value + suffix;
} else {
return value;
}
}
private static String outputValue(Object value, ExcelColumn column) {
String output;
if (value instanceof Boolean) {
output = value.toString();
} else if (value instanceof Character) {
output = value.toString();
} else if (value instanceof Number) {
output = formatNumber(((Number) value), column.getFormat());
} else if (value instanceof Date) {
output = formatDate(value, column.getFormat());
} else if (value instanceof LocalDate) {
output = formatDate(value, column.getFormat());
} else if (value instanceof LocalTime) {
output = formatDate(value, column.getFormat());
} else if (value instanceof LocalDateTime) {
output = formatDate(value, column.getFormat());
} else {
return value.toString();
}
output = replaceValue(output, column.getReplace());
output = appendSuffix(output, column.getSuffix());
return output;
}
@SuppressWarnings("unchecked")
private static void createImageCell(Row row, int index, Object value, CellStyle style) {
Cell cell = row.createCell(index);
Collection images = new ArrayList<>();
if (value instanceof Collection) {
images = (Collection) value;
} else if (value instanceof byte[]) {
images.add((byte[]) value);
}
images.removeIf(ObjectUtils::isEmpty);
if (ObjectUtils.isEmpty(images)) {
return;
}
Workbook workbook = cell.getSheet().getWorkbook();
Drawing drawing = PoiUtils.getPatriarch(cell.getSheet());
for (byte[] image : images) {
ClientAnchor anchor;
int dx1 = 0;
int dy1 = 0;
int dx2 = 0;
int dy2 = 0;
short col1 = (short) cell.getColumnIndex();
int row1 = cell.getRow().getRowNum();
short col2 = (short) (cell.getColumnIndex() + 1);
int row2 = cell.getRow().getRowNum() + 1;
if (cell instanceof HSSFCell) {
anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
} else {
anchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
}
int imageIndex = workbook.addPicture(image, getImageType(image));
drawing.createPicture(anchor, imageIndex);
}
if (style != null) {
cell.setCellStyle(style);
}
}
private static void createStringCell(Row row, int index, String value, CellStyle style) {
Cell cell = row.createCell(index);
cell.setCellValue(value);
if (style != null) {
cell.setCellStyle(style);
}
}
private static int createTitleRow(Sheet sheet, ExportParams params, int size) {
int rows = 0;
if (StringUtils.hasText(params.getTitle())) {
Row row = sheet.createRow(0);
row.setHeight((short) (params.getTitleHeight() * ROW_RATIO));
CellStyle cellStyle = params.getExportStyle().titleStyle(sheet.getWorkbook());
createStringCell(row, 0, params.getTitle(), cellStyle);
if (size > 1) {
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, size - 1));
}
rows += 1;
}
return rows;
}
private static int createHeaderRow(Sheet sheet, ExportParams params, List columns, int rownum) {
int rows = 0;
if (!CollectionUtils.isEmpty(columns)) {
Row row = sheet.createRow(rownum);
row.setHeight((short) (params.getHeaderHeight() * ROW_RATIO));
for (int i = 0; i < columns.size(); i++) {
ExcelColumn column = columns.get(i);
CellStyle cellStyle = params.getExportStyle().headerStyle(sheet.getWorkbook(), column.isRequired());
createStringCell(row, i, column.getName(), cellStyle);
}
rows += 1;
}
return rows;
}
private static void exportSheetRow(Sheet sheet, ExportParams params, Class clazz, Collection dataSet) {
int rownum = 0, freezeRow = 0, freezeCol = 0;
List columns = PoiUtils.getColumns(clazz);
// 创建标题
rownum += createTitleRow(sheet, params, columns.size());
// 创建表头
rownum += createHeaderRow(sheet, params, columns, rownum);
// 冻结表格
if ((rownum > 0 && params.isFixedTitle())) {
freezeRow = rownum;
}
if (params.getFreezeCol() > 0) {
freezeCol = params.getFreezeCol();
}
if (freezeRow > 0 || freezeCol > 0) {
sheet.createFreezePane(freezeCol, freezeRow, freezeCol, freezeRow);
}
// 列宽隐藏
for (int i = 0; i < columns.size(); i++) {
ExcelColumn column = columns.get(i);
sheet.setColumnWidth(i, column.getWidth() * COL_RATIO + 212);
sheet.setColumnHidden(i, column.isHidden());
}
for (T obj : dataSet) {
Row row = sheet.createRow(rownum);
// 如果存在图片列会重新设置高度
row.setHeight((short) (params.getRowHeight() * ROW_RATIO));
for (int i = 0; i < columns.size(); i++) {
ExcelColumn column = columns.get(i);
CellStyle cellStyle = params.getExportStyle().getCellStyle(sheet.getWorkbook(), column);
Object value = ClassUtils.getValue(column.getField(), obj);
if (ObjectUtils.isEmpty(value)) {
createStringCell(row, i, "", cellStyle);
} else if (column.getType() == ValueType.PICTURE) {
createImageCell(row, i, value, cellStyle);
} else {
createStringCell(row, i, outputValue(value, column), cellStyle);
}
}
rownum += 1;
}
}
/**
* 导出Excel
*
* @param workbook Workbook
* @param dataSet 数据
* @param clazz 类
* @param params 参数
* @param 类型
*/
public static void exportExcel(Workbook workbook, Collection dataSet, Class clazz, ExportParams params) {
Sheet sheet = PoiUtils.createSheet(workbook, params.getSheetName());
exportSheetRow(sheet, params, clazz, dataSet);
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy