package com.vincent.rsf.server.common.utils;
|
|
import com.vincent.rsf.framework.common.Cools;
|
import io.swagger.annotations.ApiModelProperty;
|
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
|
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.lang.reflect.Field;
|
import java.lang.reflect.Modifier;
|
import java.net.URLEncoder;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
|
/**
|
* Created by vincent on 2/17/2024
|
*/
|
public class ExcelUtil {
|
|
public static void build(Workbook workbook, HttpServletResponse response) {
|
response.reset();
|
Http.cors(response);
|
response.setContentType("application/octet-stream; charset=utf-8");
|
try {
|
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("export", "UTF-8"));
|
workbook.write(response.getOutputStream());
|
} catch (IOException ignore) {}
|
}
|
|
public static <T> Workbook create(List<T> list, Class<T> clz) {
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
Sheet sheet = workbook.createSheet(clz.getSimpleName());
|
|
Row header = sheet.createRow(0);
|
Field[] fields = Cools.getAllFields(clz);
|
int headerIdx = 0;
|
for (Field field : fields) {
|
if (Modifier.isFinal(field.getModifiers())
|
|| Modifier.isStatic(field.getModifiers())
|
|| Modifier.isTransient(field.getModifiers())) {
|
continue;
|
}
|
String memo = "Undefined";
|
if (field.isAnnotationPresent(ApiModelProperty.class)) {
|
memo = field.getAnnotation(ApiModelProperty.class).value();
|
}
|
header.createCell(headerIdx).setCellValue(memo);
|
headerIdx++;
|
}
|
|
int rowIndex = 1;
|
for (T t : list) {
|
Row row = sheet.createRow(rowIndex++);
|
|
int cellIndex = 0;
|
for (Field field : fields) {
|
if (Modifier.isFinal(field.getModifiers())
|
|| Modifier.isStatic(field.getModifiers())
|
|| Modifier.isTransient(field.getModifiers())) {
|
continue;
|
}
|
|
field.setAccessible(true); // 此行很重要，特别是字段为private时
|
Object value = null;
|
try {
|
value = field.get(t);
|
} catch (IllegalAccessException e) {
|
e.printStackTrace();
|
}
|
if (value != null) {
|
if (value instanceof Date) {
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
row.createCell(cellIndex).setCellValue(sdf.format((Date) value));
|
} else {
|
row.createCell(cellIndex).setCellValue(value.toString());
|
}
|
}
|
cellIndex++;
|
}
|
}
|
|
for (int i = 0; i <= fields.length; i++) {
|
sheet.autoSizeColumn(i);
|
}
|
|
return workbook;
|
}
|
|
/**
|
* Excel 导入
|
* @param file 文件
|
* @param keys 数据顺序
|
*/
|
public static List<Map<String, Object>> importExcel(MultipartFile file, String[] keys) throws Exception{
|
Workbook wb = null;
|
String fileName = file.getOriginalFilename();
|
if (fileName.endsWith("xls")) {
|
POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
|
wb = new HSSFWorkbook(pois);
|
} else if (fileName.endsWith("xlsx")) {
|
wb = new XSSFWorkbook(file.getInputStream());
|
}
|
Sheet sheet = wb.getSheetAt(0);
|
int rowCount = sheet.getPhysicalNumberOfRows();
|
if (sheet.getRow( 1).getPhysicalNumberOfCells() != keys.length){
|
throw new RuntimeException("导入的Excel和模板的列不匹配");
|
}
|
List<Map<String,Object>> result = new ArrayList<>();
|
for (int i = 0; i < rowCount - 1; i++) {
|
Row row = sheet.getRow(i + 1);
|
Map<String,Object> tmp = new HashMap<>();
|
for (int j = 0;j < keys.length; j++){
|
Cell cell = row.getCell(j);
|
// 把类型转行String
|
// cell.setCellType(CellType.STRING);
|
tmp.put(keys[j], cell.getStringCellValue());
|
}
|
result.add(tmp);
|
}
|
return result;
|
}
|
|
/**
|
* 表头样式
|
*/
|
private static CellStyle HeaderStyle(Workbook wb){
|
Font font = wb.createFont();
|
font.setFontName("宋体");
|
font.setFontHeightInPoints((short) 11);
|
CellStyle cellStyle = commonStyle(wb);
|
cellStyle.setFont(font);
|
return cellStyle;
|
}
|
|
/**
|
* 内容样式
|
*/
|
private static CellStyle contentStyle(Workbook wb){
|
Font font = wb.createFont();
|
font.setFontName("宋体");
|
font.setFontHeightInPoints((short) 10);
|
CellStyle cellStyle = commonStyle(wb);
|
cellStyle.setFont(font);
|
return cellStyle;
|
}
|
|
/**
|
* 公共样式
|
*/
|
private static CellStyle commonStyle(Workbook wb){
|
CellStyle style = wb.createCellStyle();
|
style.setAlignment(HorizontalAlignment.CENTER);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
style.setBorderBottom(BorderStyle.THIN);
|
style.setBorderLeft(BorderStyle.THIN);
|
style.setBorderTop(BorderStyle.THIN);
|
style.setBorderRight(BorderStyle.THIN);
|
style.setWrapText(true);// 自动换行
|
return style;
|
}
|
|
}
|
