EazyPOI导出数据到excel

EasyPoi动态列名导出

public void test() {
        try {
            List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
//构造对象等同于@Excel
            ExcelExportEntity excelentity = new ExcelExportEntity("姓名", "name");
            excelentity.setNeedMerge(true);
            entity.add(excelentity);
            entity.add(new ExcelExportEntity("性别", "sex"));
            excelentity = new ExcelExportEntity(null, "students");
            List<ExcelExportEntity> temp = new ArrayList<ExcelExportEntity>();
            temp.add(new ExcelExportEntity("姓名", "name"));
            temp.add(new ExcelExportEntity("性别", "sex"));
//构造List等同于@ExcelCollection 
            excelentity.setList(temp);
            entity.add(excelentity);
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
//把我们构造好的bean对象放到params就可以了
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("测试", "测试"), entity,
                list);
            FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportForMap.tt.xls");
            workbook.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

EasyPoi表头加粗或变色

点击ExportParams这个类，找到style属性，可以看到默认采用了ExcelExportStylerDefaultImpl这个类，点击进去复制这个类然后重命名为自己的类

public class ExcelExportsStyleImpl extends AbstractExcelExportStyler implements IExcelExportStyler {
    public ExcelExportsStyleImpl(Workbook workbook) {
        super.createStyles(workbook);
    }
    
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = this.workbook.createCellStyle();
        Font font = this.workbook.createFont();
        font.setBold(true);//表头加粗
        titleStyle.setFont(font);
        //titleStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());  //表头背景色
        //titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setWrapText(true);
        return titleStyle;
    }
    
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        
        return style;
    }
    
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = this.workbook.createCellStyle();
        Font font = this.workbook.createFont();
        font.setFontHeightInPoints((short)12);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return titleStyle;
    }
    
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        
        return style;
    }
}

ExportParams exportParams = new ExportParams(null, dataModel.getName());
exportParams.setStyle(ExcelExportsStyleImpl.class);

EasyPoi动态列多sheet导出，子表数据放在另一个sheet页

List<Map<String,Object>> sheetDataList1 = new ArrayList<>();//sheet1要导出的数据
List<Map<String,Object>> sheetDataList2 = new ArrayList<>();//sheet2要导出的数据


Workbook workbook = new HSSFWorkbook();
ExcelExportService service = new ExcelExportService();

//sheet1
ExportParams exportParams1 = new ExportParams(null, "sheet1名字");
List<ExcelExportEntity> entityList1 = new ArrayList<>();
entityList1.add(new ExcelExportEntity("name","key"));
service.createSheetForMap(workbook,exportParams1 ,entityList1 ,sheetDataList1 );//这里要注意一点，dataList数据被使用过之后就是空的了

//sheet2
ExportParams exportParams2 = new ExportParams(null, "sheet2名字");
List<ExcelExportEntity> entityList2 = new ArrayList<>();
entityList2.add(new ExcelExportEntity("name","key"));
service.createSheetForMap(workbook,exportParams2 ,entityList2 ,sheetDataList2 );

EasyPoi自动调整列宽

核心代码就一行  sheet.autoSizeColumn(j);

 给所有的sheet页的所有行加入自动调整列宽

//自动调整列宽
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
    Sheet sheet = workbook.getSheetAt(i);
    int columnNumber = sheet.getRow(0).getPhysicalNumberOfCells();
    for (int j = 0; j < columnNumber; j++) {
        sheet.autoSizeColumn(j);
        //解决自动设置列宽中文失效的问题
        sheet.setColumnWidth(j, sheet.getColumnWidth(j) * 12 / 10);
    }
}