依赖:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
1.导出自定义Excel文件:
public void excel(HttpServletRequest request, HttpServletResponse response) throws IOException {
try {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
OutputStream out = response.getOutputStream();
//报头用于提供一个推荐的文件名,并强制浏览器显示保存对话框
//attachment表示以附件方式下载。如果要在页面中打开,则改为 inline
response.setHeader("Content-Disposition", "attachment; filename=TestExcel1.xls ");
Workbook workbook = new HSSFWorkbook();
//创建工作表
Sheet sheet = workbook.createSheet("fromName"); //formName 为创建sheet页的名称
//设置列宽
sheet.setColumnWidth(0, 256*18);
sheet.setColumnWidth(1, 256*18);
sheet.setColumnWidth(2, 256*18);
sheet.setColumnWidth(3, 256*20);
sheet.setColumnWidth(4, 256*20);
sheet.setColumnWidth(5, 256*20);
//sheet.autoSizeColumn(3);
//设置单元格样式
HSSFCellStyle hssfCellStyle = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中显示
Font headFontCell = workbook.createFont();
headFontCell.setFontName("宋体");
headFontCell.setFontHeightInPoints((short) 10);// 字体大小
hssfCellStyle.setFont(headFontCell);
HSSFCellStyle hssfCellRemarkStyle = (HSSFCellStyle) workbook.createCellStyle();
hssfCellRemarkStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);//居左显示
hssfCellRemarkStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);// 上下居中
Font headFontLeft = workbook.createFont();
headFontCell.setFontName("宋体");
headFontCell.setFontHeightInPoints((short) 10);// 字体大小
hssfCellStyle.setFont(headFontLeft);
HSSFCellStyle hssfCellStyleHead = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
hssfCellStyleHead.setWrapText(true);
hssfCellStyleHead.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);// 上下居中
HSSFCellStyle hssfCellStyleNum = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyleNum.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中显示
hssfCellStyleNum.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
Font headFontCellNum = workbook.createFont();
headFontCellNum.setFontName("宋体");
headFontCellNum.setFontHeightInPoints((short) 10);// 字体大小
hssfCellStyleNum.setFont(headFontCellNum);
//设置表头字体
Font headFont = workbook.createFont();
headFont.setFontName("宋体");
headFont.setBold(true);
headFont.setFontHeightInPoints((short) 15);// 字体大小
hssfCellStyleHead.setFont(headFont);
HSSFCellStyle hssfCellStyleTwoHead = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyleTwoHead.setAlignment(HSSFCellStyle.ALIGN_LEFT);
hssfCellStyleTwoHead.setWrapText(true);
Font headFontTwo = workbook.createFont();
headFontTwo.setFontName("楷体");
headFontTwo.setBold(true);
headFontTwo.setFontHeightInPoints((short) 13);// 字体大小
hssfCellStyleTwoHead.setFont(headFontTwo);
hssfCellRemarkStyle.setFont(headFontTwo);
HSSFCellStyle hssfCellStyleThreeHead = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyleThreeHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
hssfCellStyleThreeHead.setWrapText(true);
Font headFontThree = workbook.createFont();
headFontThree.setFontName("仿宋");
headFontThree.setBold(true);
headFontThree.setFontHeightInPoints((short) 11);// 字体大小
hssfCellStyleThreeHead.setFont(headFontThree);
CellRangeAddress callRangeAddress0 = new CellRangeAddress(0,0,0,5);//起始行,结束行,起始列,结束列 //标题
sheet.addMergedRegion(callRangeAddress0);
CellRangeAddress callRangeAddress1 = new CellRangeAddress(1,1,0,5);//起始行,结束行,起始列,结束列 //标题
sheet.addMergedRegion(callRangeAddress1);
//创建第一行
org.apache.poi.ss.usermodel.Row row0 = sheet.createRow(0);
//创建单元格
Cell cell06 = row0.createCell(0);
row0.setHeight((short) 500);
//设置第一行第一格的值
String title= "titleName"; //创建表单的表单名称
cell06.setCellValue(title);
//设置单元格的文本居中显示
cell06.setCellStyle(hssfCellStyleHead);
cell06.setCellValue(title);
//设置单元格的文本居中显示
cell06.setCellStyle(hssfCellStyleHead);
//创建第一行
org.apache.poi.ss.usermodel.Row row1 = sheet.createRow(1);
//创建单元格
Cell cell07 = row1.createCell(0);
row1.setHeight((short) 400);
//设置第一行第一格的值
String title2= "备注:xxxxxxxxxxxxx";
cell07.setCellValue(title2);
//设置单元格的文本居中显示
cell07.setCellStyle(hssfCellRemarkStyle);
cell07.setCellValue(title2);
//设置单元格的文本居中显示
cell07.setCellStyle(hssfCellRemarkStyle);
//列表头
HSSFCellStyle hssfCellStyleListHead = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyleListHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font headFontList = workbook.createFont();
headFontList.setFontName("宋体");
headFontList.setFontHeightInPoints((short) 10);// 字体大小
hssfCellStyleListHead.setFont(headFontList);
//创建第三行
org.apache.poi.ss.usermodel.Row row2 = sheet.createRow(2);
row2.setHeight((short) 400);
//创建单元格
Cell cell0 = row2.createCell(0);
//设置第一行第一格的值
cell0.setCellValue("name1");
cell0.setCellStyle(hssfCellStyleThreeHead);
Cell cell1 = row2.createCell(1);
cell1.setCellValue("name2");
cell1.setCellStyle(hssfCellStyleThreeHead);
Cell cell2 = row2.createCell(2);
cell2.setCellValue("name3");
cell2.setCellStyle(hssfCellStyleThreeHead);
Cell cell3 = row2.createCell(3) ;
cell3.setCellValue("name4");
cell3.setCellStyle(hssfCellStyleThreeHead);
Cell cell4 = row2.createCell(4);
cell4.setCellValue("name5");
cell4.setCellStyle(hssfCellStyleThreeHead);
Cell cell5 = row2.createCell(5);
cell5.setCellValue("name6");
cell5.setCellStyle(hssfCellStyleThreeHead);
long time = new Date().getTime();
response.setHeader("Content-Disposition",
"attchement;filename=" + new String(time+".xls"));
workbook.write(out);
System.out.println("创建表单成功!");
out.flush();
out.close();
}catch (Exception e){
e.getStackTrace();
logger.error(e.getMessage());
}
}
2.导出数据文件(Excel)
public void excel(HttpServletRequest request, HttpServletResponse response) throws IOException {
try {
//获取报表信息,这里根据实际获取数据
if(weightTimeBegin == null || weightTimeBegin == ""){
weightTimeBegin = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
}
List<Map<String, Object>> dataReports = processedExceptionFourService.getDataReport(weightTimeBegin);
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
OutputStream out = response.getOutputStream();
//报头用于提供一个推荐的文件名,并强制浏览器显示保存对话框
//attachment表示以附件方式下载。如果要在页面中打开,则改为 inline
response.setHeader("Content-Disposition", "attachment; filename=TestExcel1.xls ");
Workbook workbook = new HSSFWorkbook();
//创建工作表
Sheet sheet = workbook.createSheet(weightTimeBegin+"sheetName");
//设置列宽
sheet.setColumnWidth(0, 256*18);
sheet.setColumnWidth(1, 256*18);
sheet.setColumnWidth(2, 256*18);
sheet.setColumnWidth(3, 256*20);
sheet.setColumnWidth(4, 256*20);
sheet.setColumnWidth(5, 256*20);
//sheet.autoSizeColumn(3);
//设置单元格样式
HSSFCellStyle hssfCellStyle = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中显示
Font headFontCell = workbook.createFont();
headFontCell.setFontName("宋体");
headFontCell.setFontHeightInPoints((short) 10);// 字体大小
hssfCellStyle.setFont(headFontCell);
HSSFCellStyle hssfCellStyleHead = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyleHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
hssfCellStyleHead.setWrapText(true);
hssfCellStyleHead.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);// 上下居中
HSSFCellStyle hssfCellStyleNum = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyleNum.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中显示
hssfCellStyleNum.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
Font headFontCellNum = workbook.createFont();
headFontCellNum.setFontName("宋体");
headFontCellNum.setFontHeightInPoints((short) 10);// 字体大小
hssfCellStyleNum.setFont(headFontCellNum);
//设置表头字体
Font headFont = workbook.createFont();
headFont.setFontName("宋体");
headFont.setBold(true);
headFont.setFontHeightInPoints((short) 15);// 字体大小
hssfCellStyleHead.setFont(headFont);
HSSFCellStyle hssfCellStyleTwoHead = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyleTwoHead.setAlignment(HSSFCellStyle.ALIGN_LEFT);
hssfCellStyleTwoHead.setWrapText(true);
Font headFontTwo = workbook.createFont();
headFontTwo.setFontName("楷体");
headFontTwo.setBold(true);
headFontTwo.setFontHeightInPoints((short) 13);// 字体大小
hssfCellStyleTwoHead.setFont(headFontTwo);
HSSFCellStyle hssfCellStyleThreeHead = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyleThreeHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
hssfCellStyleThreeHead.setWrapText(true);
Font headFontThree = workbook.createFont();
headFontThree.setFontName("仿宋");
headFontThree.setBold(true);
headFontThree.setFontHeightInPoints((short) 11);// 字体大小
hssfCellStyleThreeHead.setFont(headFontThree);
CellRangeAddress callRangeAddress0 = new CellRangeAddress(0,0,0,5);//起始行,结束行,起始列,结束列 //标题
sheet.addMergedRegion(callRangeAddress0);
//创建第一行
Row row0 = sheet.createRow(0);
//创建单元格
Cell cell06 = row0.createCell(0);
row0.setHeight((short) 500);
//设置第一行第一格的值
String title= weightTimeBegin+" titleName";
cell06.setCellValue(title);
//设置单元格的文本居中显示
cell06.setCellStyle(hssfCellStyleHead);
cell06.setCellValue(title);
//设置单元格的文本居中显示
cell06.setCellStyle(hssfCellStyleHead);
//列表头
HSSFCellStyle hssfCellStyleListHead = (HSSFCellStyle) workbook.createCellStyle();
hssfCellStyleListHead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font headFontList = workbook.createFont();
headFontList.setFontName("宋体");
headFontList.setFontHeightInPoints((short) 10);// 字体大小
hssfCellStyleListHead.setFont(headFontList);
//创建第三行
Row row2 = sheet.createRow(1);
row2.setHeight((short) 400);
//创建单元格
Cell cell0 = row2.createCell(0);
//设置第一行第一格的值
cell0.setCellValue("name1");
cell0.setCellStyle(hssfCellStyleThreeHead);
Cell cell1 = row2.createCell(1);
cell1.setCellValue("name2");
cell1.setCellStyle(hssfCellStyleThreeHead);
Cell cell2 = row2.createCell(2);
cell2.setCellValue("name3");
cell2.setCellStyle(hssfCellStyleThreeHead);
Cell cell3 = row2.createCell(3) ;
cell3.setCellValue("name4");
cell3.setCellStyle(hssfCellStyleThreeHead);
Cell cell4 = row2.createCell(4);
cell4.setCellValue("name5");
cell4.setCellStyle(hssfCellStyleThreeHead);
Cell cell5 = row2.createCell(5);
cell5.setCellValue("name6");
cell5.setCellStyle(hssfCellStyleThreeHead);
for (int i = 0; i < dataReports.size();i++){
Row rows1 = sheet.createRow(2+i);
rows1.setHeight((short) 300);
Cell cells = rows1.createCell(0);
//这里field为map中的key
cells.setCellValue(dataReports.get(i).get("field1").toString());
cells.setCellStyle(hssfCellStyle);
Cell cell1s = rows1.createCell(1);
cell1s.setCellStyle(hssfCellStyle);
cell1s.setCellValue(dataReports.get(i).get("field2").toString());
Cell cell2s = rows1.createCell(2);
cell2s.setCellStyle(hssfCellStyle);
cell2s.setCellValue(dataReports.get(i).get( "field3").toString());
Cell cell3s = rows1.createCell(3);
cell3s.setCellStyle(hssfCellStyle);
cell3s.setCellValue(dataReports.get(i).get("field4").toString());
Cell cell4s = rows1.createCell(4);
cell4s.setCellStyle(hssfCellStyle);
cell4s.setCellValue(dataReports.get(i).get("field5").toString());
Cell cell5s = rows1.createCell(5);
cell5s.setCellStyle(hssfCellStyle);
cell5s.setCellValue(dataReports.get(i).get("field6").toString());
}
long time = new Date().getTime();
response.setHeader("Content-Disposition",
"attchement;filename=" + new String(time+".xls"));
workbook.write(out);
System.out.println("数据写入成功!");
out.flush();
out.close();
}catch (Exception e){
e.getStackTrace();
logger.error(e.getMessage());
}
}