POI导出Excel工具类
package com.jesse.commons; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.*; import java.lang.reflect.Field; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.List; import java.util.Set; /** * @author wpx * @date: 2019/10/21 */ public class ExportExcelUtil { /** * 固定列宽 */ private static final Integer WIDTH = 5120; /** * 导出excel * @param title sheet标题 * @param rowNames 第一行 * @param dataList 数据列表 * @param <T> 封装数据 * @return * @throws Exception */ public static <T> XSSFWorkbook export(String title, LinkedHashMap<String, String> rowNames, List<T> dataList) throws Exception{ XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(title); //产生标题行 XSSFRow rowm = sheet.createRow(0); XSSFCell cellTitle = rowm.createCell(0); //sheet样式定义【】 XSSFCellStyle columnTopStyle = getColumnTopStyle(workbook); sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowNames.size() - 1))); cellTitle.setCellStyle(columnTopStyle); cellTitle.setCellValue(title); // 定义所需列数 int columnNum = rowNames.size(); XSSFRow rowRowName = sheet.createRow(2); // 将列头设置到sheet的单元格中 Set<String> keySet = rowNames.keySet(); Iterator<String> iterator = keySet.iterator(); for (int n = 0; n < columnNum; n++) { XSSFCell cell = rowRowName.createCell(n); sheet.setColumnWidth(n, WIDTH); XSSFRichTextString text = new XSSFRichTextString(rowNames.get(iterator.next())); cell.setCellValue(text); cell.setCellStyle(columnTopStyle); } // 将查询到的数据设置到sheet对应的单元格中 XSSFCellStyle style = getStyle(workbook); for (int i = 0; i < dataList.size(); i++) { T t = dataList.get(i); XSSFRow row = sheet.createRow(i + 3); Class<?> aClass = t.getClass(); int cellCount = 0; for (String s : keySet) { XSSFCell cell = row.createCell(cellCount); Field field = aClass.getDeclaredField(s); field.setAccessible(true); cell.setCellValue(field.get(t) == null ? "" : field.get(t).toString()); cell.setCellStyle(style); cellCount++; } } return workbook; } /** * 列头单元格样式 * @param workbook * @return */ public static XSSFCellStyle getColumnTopStyle(XSSFWorkbook workbook) { // 设置字体 XSSFFont font = workbook.createFont(); // 设置字体大小 font.setFontHeightInPoints((short) 11); // 字体加粗 font.setBold(true); // 设置字体名字 font.setFontName("Courier New"); // 设置样式 XSSFCellStyle style = workbook.createCellStyle(); // 在样式中应用设置的字体 style.setFont(font); // 设置自动换行 style.setWrapText(true); // 设置水平对齐的样式为居中对齐; style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); return style; } /** * 其余列样式 * @param workbook * @return */ public static XSSFCellStyle getStyle(XSSFWorkbook workbook) { // 设置字体 XSSFFont font = workbook.createFont(); // 设置字体大小 font.setFontHeightInPoints((short) 10); // 字体加粗 font.setBold(true); // 设置字体名字 font.setFontName("Courier New"); // 设置样式; XSSFCellStyle style = workbook.createCellStyle(); // 在样式用应用设置的字体; style.setFont(font); // 设置自动换行; style.setWrapText(true); // 设置水平对齐的样式为居中对齐; style.setAlignment(HorizontalAlignment.CENTER); // 设置垂直对齐的样式为居中对齐; style.setVerticalAlignment(VerticalAlignment.CENTER); return style; } }
实体类:
package com.jesse.dao.entity; /** * @author wpx * @date: 2019/10/21 */ public class ResubmitData { public ResubmitData(String enName, String name, String mobile, String classCode, String courseType, String resubmitPerson, String classTeacherName, String appealData, String reviewDate) { this.enName = enName; this.name = name; this.mobile = mobile; this.classCode = classCode; this.courseType = courseType; this.resubmitPerson = resubmitPerson; this.classTeacherName = classTeacherName; this.appealData = appealData; this.reviewDate = reviewDate; } /** * 学员英文名 */ private String enName; /** * 学员中文名 */ private String name; /** * 学员手机号 */ private String mobile; /** * 班级编号 */ private String classCode; /** * 课程类型 */ private String courseType; /** * 续报关系人 */ private String resubmitPerson; /** * 班级班主任 */ private String classTeacherName; /** * 是否为申诉数据 */ private String appealData; /** * 审核时间 */ private String reviewDate; public String getEnName() { return enName; } public void setEnName(String enName) { this.enName = enName; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getMobile() { return mobile; } public void setMobile(String mobile) { this.mobile = mobile; } public String getClassCode() { return classCode; } public void setClassCode(String classCode) { this.classCode = classCode; } public String getCourseType() { return courseType; } public void setCourseType(String courseType) { this.courseType = courseType; } public String getResubmitPerson() { return resubmitPerson; } public void setResubmitPerson(String resubmitPerson) { this.resubmitPerson = resubmitPerson; } public String getClassTeacherName() { return classTeacherName; } public void setClassTeacherName(String classTeacherName) { this.classTeacherName = classTeacherName; } public String getAppealData() { return appealData; } public void setAppealData(String appealData) { this.appealData = appealData; } public String getReviewDate() { return reviewDate; } public void setReviewDate(String reviewDate) { this.reviewDate = reviewDate; } @Override public String toString() { return super.toString(); } }
测试类:
public class PoiTest {
@Test public void test3() { String title = "续报数据列表"; //首行 LinkedHashMap<String, String> rowNames = new LinkedHashMap<>(); rowNames.put("enName", "学员英文名"); rowNames.put("name", "学员中文名"); rowNames.put("mobile", "学员手机号"); rowNames.put("classCode", "班级编号"); rowNames.put("courseType", "课程类型"); rowNames.put("resubmitPerson", "续报关系人"); rowNames.put("classTeacherName", "班级班主任"); rowNames.put("appealData", "是否为申诉数据"); rowNames.put("reviewDate", "审核时间"); List<ResubmitData> resubmitDatas = new ArrayList<>(); for (int i = 0 ; i < 5; i++) { String value = i + ""; ResubmitData resubmitData = new ResubmitData("123456789258656255165456456", value, value, value, value, value, value, value, null); resubmitDatas.add(resubmitData); } try { FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\wangp\\Desktop\\test.xlsx"); XSSFWorkbook export = ExportExcelUtil.export(title, rowNames, resubmitDatas); export.write(fileOutputStream); } catch (Exception e) { e.printStackTrace(); } }
}
结果为: