java生成excel文件--利用easypoi
一、Maven依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
二、编辑导出数据对象类，使用注解的形式进行标记各个字段类型，name对应excel表格的表头，orderNum每一列排序，从左到右，width代表每一列的宽度，具体代码如下（省略Get和Set方法）
public class AssetsStatistics { @Excel(name = "序号", orderNum = "0",width = 10)
private String id;
@Excel(name = "所属公司", orderNum = "1",width = 20)
private String provincename;
@Excel(name = "公司编码", orderNum = "2",width = 10)
private String provincecode;
@Excel(name = "ip", orderNum = "3",width = 15)
private String ip;
@Excel(name = "资产URL", orderNum = "4",width = 20)
private String url;
}
三、设置excel自定义样式PoiExportStyle
public class PoiExportStyle extends ExcelExportStylerDefaultImpl implements IExcelExportStyler { public PoiExportStyle(Workbook workbook) {
super(workbook);
}
@Override
public CellStyle getHeaderStyle(short headerColor) {
CellStyle titleStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 20);
titleStyle.setFont(font);
titleStyle.setBorderLeft((short) 1); // 左边框
titleStyle.setBorderRight((short) 1); // 右边框
titleStyle.setBorderBottom((short) 1);
titleStyle.setBorderTop((short) 1);
titleStyle.setFillForegroundColor((short) 41); // 填充的背景颜色
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND.getCode());
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
return titleStyle;
}
@Override
public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
CellStyle style = workbook.createCellStyle();
style.setBorderLeft((short) 1); // 左边框
style.setBorderRight((short) 1); // 右边框
style.setBorderBottom((short) 1);
style.setBorderTop((short) 1);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setDataFormat(STRING_FORMAT);
if (isWarp) {
style.setWrapText(true);
}
return style;
}
@Override
public CellStyle getTitleStyle(short color) {
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setBorderLeft((short) 1); // 左边框
titleStyle.setBorderRight((short) 1); // 右边框
titleStyle.setBorderBottom((short) 1);
titleStyle.setBorderTop((short) 1);
titleStyle.setFillForegroundColor((short) 41); // 填充的背景颜色
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND.getCode());
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
titleStyle.setWrapText(true);
return titleStyle;
}
@Override
public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
return isWarp ? stringNoneWrapStyle : stringNoneStyle;
}
四、引用excel自定义样式封装成工具类
public class PoiUtil { public static void creatExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
String directory) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setHeaderHeight((double)100);
exportParams.setStyle(PoiExportStyle.class);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null);{
//将Excel文件放到本地路径下
String directory = "D:/data"; // 本地目录
FileOutputStream os = new FileOutputStream(directory+fileName);
workbook.write(os);
os.close();
}
}
}
五、list数据生成excel文件---单个sheet和多个sheet
//单个sheetpublic String creatAssetsQuarterExcel() throws Exception {
String title = "2020年第1季度资产清单";
List<AssetsStatistics> list = secAssetsService.selectListByTime();//从数据库查询数据
if(!CollectionUtils.isEmpty(list)) {
Integer no = 1;
for (AssetsStatistics v : list) {
v.setId(String.valueOf(no++));
}
}
PoiUtil.creatExcel(list, title, title, AssetsStatistics.class, title+".xls", excelReport);
return title+".xls";
}
//多sheetpublic String creatMonthExcelByAssets() throws Exception {
Workbook workBook = null;
try {
// 主机资产表
List<AssetsStatistics> hostlist = countHostAssets();
Map<String, Object> hostExportMap = getSheetMap("主机资产表",hostlist);
// 数据库资产表
List<AssetsStatistics> dblist = countDbAssets();
Map<String, Object> dbExportMap = getSheetMap("数据库资产表",dblist);
// 将sheet1、sheet2使用得map进行包装
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(hostExportMap);
sheetsList.add(dbExportMap);
workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
// String fileName = URLEncoder.encode("资产表.xls", "UTF-8");
String fileName ="资产表.xls";
if (workBook != null){
//将Excel文件放到本地路径下
FileOutputStream os = new FileOutputStream(excelReport+fileName);
workBook.write(os);
os.close();
}
return fileName;
}catch (Exception e){
e.printStackTrace();
return e.getMessage();
}finally {
if(workBook != null) {
try {
workBook.close();
} catch (IOException e) {
e.printStackTrace();
return e.getMessage();
}
}
}
}
/**
* 设置excel 中 sheet 的参数
* @param sheetName sheet名称
* @param list sheet 数据列表
* @return
*/
private Map<String, Object> getSheetMap(String sheetName,List<AssetsProblemStatistics> list) {
// 创建参数对象（用来设定excel得sheet得内容等信息）
ExportParams exportParams = new ExportParams();
exportParams.setHeaderHeight((double)100);
exportParams.setStyle(PoiExportStyle.class);
// 设置sheet得名称
exportParams.setSheetName(sheetName);
// 创建sheet1使用得map
Map<String, Object> sheetExportMap = new HashMap<>();
// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
sheetExportMap.put("title", exportParams);
// 模版导出对应得实体类型
sheetExportMap.put("entity", AssetsProblemStatistics.class);
// sheet中要填充得数据
sheetExportMap.put("data", list);
return sheetExportMap;
}
六、结果展示
以上是 java生成excel文件--利用easypoi 的全部内容， 来源链接： utcz.com/z/395014.html
