@Service
public class ExcelService {
/**
*
* @param titles 表格第一行,表头
* @param keys 实体类对象字典keys; 如果为null,则返回获取dataList所有字段;
* @param dataList 数据集合
* @param 如果是实体bean,header是反射获取所有字段;如果是JsonObject,header是获取所有key
* @return
* @throws Exception
*/
public HSSFWorkbook export(List titles, List keys, List dataList) throws Exception {
//1.创建工作薄
HSSFWorkbook wb = new HSSFWorkbook();
if (dataList == null || dataList.isEmpty()) {
return wb;
}
String[] excelHeader;
if(keys == null){
Collection fields = fields(dataList.get(0));
excelHeader = fields.toArray(new String[]{});
}else{
excelHeader = keys.toArray(new String[]{});
}
//2.创建表
HSSFSheet sheet = wb.createSheet("sheet1");
//2.1设置列宽(必须先设置列宽然后设置行高,不然列宽没有效果)
sheet.setDefaultColumnWidth(15);
//2.2设置列高
// sheet.setDefaultRowHeight((short) 100);
//3.创建行
//4.生成样式
HSSFCellStyle style = wb.createCellStyle();
//4.1设置水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//4.2设置垂直居中
// style.setVerticalAlignment(VerticalAlignment.CENTER);
//4.3设置背景颜色
// style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.ROYAL_BLUE.getIndex());
// style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.ROYAL_BLUE.getIndex());
//4.4设置边框
// style.setBorderBottom(BorderStyle.THIN);
// style.setBorderLeft(BorderStyle.THIN);
// style.setBorderRight(BorderStyle.THIN);
// style.setBorderTop(BorderStyle.THIN);
//5 设置字体
// HSSFFont font = wb.createFont();
// font.setFontHeightInPoints((short) 10);
// font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
// font.setBold(true);
// font.setFontName("宋体");
//6.把字体给当前样式
// style.setFont(font);
//7.设置表头
String[] titlesArray = titles.toArray(new String[]{});
HSSFRow headerRow = sheet.createRow(0);
for (int i = 0; i < titlesArray.length; i++) {
HSSFCell cell = headerRow.createCell(i);
cell.setCellValue(titlesArray[i]);
cell.setCellStyle(style);
}
//添加数据
for (int i = 0; i < dataList.size(); i++) {
HSSFRow dataRow = sheet.createRow(i + 1);
T line = dataList.get(i);
if (JSONObject.class.isInstance(line)) {
JSONObject data = (JSONObject) line;
for (int j = 0; j < excelHeader.length; j++) {
String header = excelHeader[j];
dataRow.createCell(j).setCellValue(data.getString(header));
}
} else {// java bean
for (int j = 0; j < excelHeader.length; j++) {
String header = excelHeader[j];
String UTitle = Character.toUpperCase(header.charAt(0)) + header.substring(1, header.length());
String methodName = "get" + UTitle;
Method method = line.getClass().getDeclaredMethod(methodName);
String data = method.invoke(line) == null ? "" : method.invoke(line).toString();
dataRow.createCell(j).setCellValue(data);
}
}
}
return wb;
}
private Collection fields(Object line) {
if (JSONObject.class.isInstance(line)) {
return ((JSONObject) line).keySet();
} else {// java bean
return Arrays.stream(line.getClass().getDeclaredFields()).map(field -> field.getName()).collect(Collectors.toList());
}
}
}