java导出exce出改进版---重点

        //List<Map<String, Object>> rows = JSONObject.parseObject(JSONObject.toJSONString(result), List.class);
        //List columnInfoBeanList = new ArrayList();
        //columnInfoBeanList.add(new ColumnInfoBean("姓名", "name", "20"));
//
        //LocalDateTime local = LocalDateTime.now();
        //DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        //String excelName = formatter.format(local) + "-案件信息统计报表";
        //ExcelUtil.exportExcel(response, columnInfoBeanList, rows, excelName);

/**
* Excel工具类
*/
public class ExcelUtil {
/**
* 导出操作
*
* @param response
* @param rows
* @param excelName
*/
public static void exportExcel(HttpServletResponse response, List columnInfoBeanList, List<Map<String, Object>> rows, String excelName) {
try {
SXSSFWorkbook workbook = createSXSSFWorkbook(columnInfoBeanList, rows);
String fileName = excelName + “.xlsx”;
fileName = new String(fileName.getBytes(“GBK”), “ISO-8859-1”); // 取消乱码
response.setContentType(“octets/stream”);
response.addHeader(“Content-Disposition”, “attachment;filename=”
+ fileName);
OutputStream out = response.getOutputStream();
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}

public static SXSSFWorkbook createSXSSFWorkbook(List columnInfoBeanList, List<Map<String, Object>> rows) {
SXSSFWorkbook workbook = new SXSSFWorkbook(); // 创建工作薄，相当于一个文件

Sheet sheet = workbook.createSheet(); // 创建一个表

sheet.setMargin(XSSFSheet.TopMargin, 0.64); // 页边距（上）
sheet.setMargin(XSSFSheet.BottomMargin, 0.64); // 页边距（下）
sheet.setMargin(XSSFSheet.LeftMargin, 0.64); // 页边距（左）
sheet.setMargin(XSSFSheet.RightMargin, 0.64); // 页边距（右）

// 标题样式
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
// 标题字体
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 12); // 字体大小
titleFont.setFontName(“宋体”);
titleStyle.setFont(titleFont);

// 填报单位的样式
CellStyle titleStyle_2 = workbook.createCellStyle();
titleStyle_2.setAlignment(XSSFCellStyle.ALIGN_RIGHT); // 水平居右
titleStyle_2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
// 标题字体
Font titleFont_2 = workbook.createFont();
titleFont_2.setFontHeightInPoints((short) 11);
titleFont_2.setFontName(“宋体”);
titleStyle_2.setFont(titleFont_2);

// 填报单位的样式
CellStyle titleStyle_u = workbook.createCellStyle();
titleStyle_u.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居左
titleStyle_u.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
// 标题字体
Font titleFont_u = workbook.createFont();
titleFont_u.setUnderline(XSSFFont.U_SINGLE);
titleFont_u.setFontHeightInPoints((short) 11);
titleFont_u.setFontName(“宋体”);
titleStyle_u.setFont(titleFont_u);

// 表头样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
headerStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
headerStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
headerStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 左边框
headerStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); // 上边框
headerStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); // 右边框
headerStyle.setWrapText(true); // 设置多行显示
DataFormat format = workbook.createDataFormat();
headerStyle.setDataFormat(format.getFormat(“@”));
// 表头字体
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 9);
headerFont.setFontName(“宋体”);
headerStyle.setFont(headerFont);

// 数据样式
CellStyle dataStyle = workbook.createCellStyle();
dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 水平居中
dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
dataStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
dataStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 左边框
dataStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); // 上边框
dataStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); // 右边框
dataStyle.setDataFormat(format.getFormat(“@”)); //将数据单元格格式设置为文本类型
// 数据字体
Font dataFont = workbook.createFont();
dataFont.setFontHeightInPoints((short) 9);
dataFont.setFontName(“宋体”);
dataStyle.setFont(dataFont);

// 尾部样式
CellStyle footStyle = workbook.createCellStyle();
footStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT); // 水平居中
footStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 垂直居中
// 尾部字体
Font footFont = workbook.createFont();
footFont.setFontHeightInPoints((short) 11);
footFont.setFontName(“宋体”);
footStyle.setFont(footFont);

CellStyle commonStyle = workbook.createCellStyle();
commonStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
commonStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); // 左边框
commonStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); // 上边框
commonStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); // 右边框

Cell cell = null;
Row row0 = sheet.createRow(0);
for (int i = 0; i <= columnInfoBeanList.size() – 1; i++) {
ColumnInfoBean columnInfoBean = (ColumnInfoBean) columnInfoBeanList.get(i);
row0.setHeight((short) (2 * 256));
cell = row0.createCell(i);
if (StringUtils.isNotBlank(columnInfoBean.getColumnWidth())) {
sheet.setColumnWidth(i, Integer.parseInt(columnInfoBean.getColumnWidth()) * 256);
}
cell.setCellValue(columnInfoBean.getColumnName());
cell.setCellStyle(headerStyle);
}

Object obj = null;
for (int i = 0; i < rows.size(); i++) {
Row dataRow = sheet.createRow(1 + i);
Map<String, Object> project = rows.get(i);
for (int j = 0; j < columnInfoBeanList.size(); j++) {
ColumnInfoBean columnInfoBean = (ColumnInfoBean) columnInfoBeanList.get(j);
Cell dataCell = dataRow.createCell(j);
dataCell.setCellStyle(dataStyle);
obj = project.get(columnInfoBean.getPropertyName());
dataCell.setCellValue(obj == null ? “” : obj.toString());
}
}
return workbook;
}
}

@Data
public class ColumnInfoBean {
public ColumnInfoBean(String columnName, String propertyName, String columnWidth) {
this.propertyName = propertyName;
this.columnName = columnName;
this.columnWidth = columnWidth;
}

private String columnName;
private String columnWidth;
private String propertyName;
}