1、控制层
/**
* @Author shenWB
* @Date 11:06
* @Param [response, sheetName]
* @Return void
* 导出Ecaxl
*/
// @RequestMapping(value = "/exportExcel")
@PostMapping("/exportExcel")
public void exportExcel(HttpServletResponse response,Integer typeNumber) throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
creatSheet(wb,typeNumber);
response.setContentType("application/binary;charset=UTF-8");
ServletOutputStream out=response.getOutputStream();
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("111.xls", "UTF-8"));
wb.write(out);
out.flush();
out.close();
}
/**
* @Author shenWB
* @Date 11:08
* @Param [wb, sheetName]
* @Return void
* 获取导出数据 这里不再多说自己根据自己业务来
*/
public void creatSheet(HSSFWorkbook wb,Integer typeNumber){
List<TodayCount> todayCount = todayCountService.getTodayCountList(typeNumber);
ExcalDownloadUtil.creatSheet(wb,todayCount);
}
2、导出excal工具类
/**
* @ClassName ExcalDownloadUtil
* @Description TODO
* @Author shenWB
* @Date 2019/5/31 9:19
* @Version 1.0
**/
public class ExcalDownloadUtil {
public static HSSFWorkbook creatSheet(HSSFWorkbook wb, List<TodayCount> todayCount){
//建立新的sheet对象（excel的表单）
HSSFSheet sheet = wb.createSheet();//新建sheet页
//========
HSSFCellStyle jz = wb.createCellStyle();//新建单元格样式
jz.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
jz.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
jz.setWrapText(true);//自动换行
//========
// 设置列宽
for(int i = 0 ; i<=21;i++){
if (i==0){
sheet.setColumnWidth((short) i, (short) 4500);
}else{
sheet.setColumnWidth((short) i, (short) 3400);
}
}
HSSFRow row0=sheet.createRow(0);//第一行
row0.setHeight((short) ((short) 30*20));//设置行高
HSSFCell cellTitle=row0.createCell(0); //创建单元格
HSSFCellStyle styleTitle = creatStyle(wb, "黑体", 20, HSSFCellStyle.ALIGN_CENTER,false,true);//设置单元格样式
cellTitle.setCellStyle(styleTitle);
cellTitle.setCellValue("今日各单位清单数量情况汇总表");//设置单元格内容
sheet.addMergedRegion(new CellRangeAddress(0,0,0,21));//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
//第二行
// poi做多行合并，一定需要先绘制单元格，然后写入数据，最后合并，不然各种坑啊,切记啊政务小组的伙伴们
HSSFRow row1 =sheet.createRow(1);
row1.setHeight((short) 300);//设置行高
HSSFCell cell1_0 = row1.createCell(0);//列
cell1_0.setCellValue("区化");
cell1_0.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_1 = row1.createCell(1);
cell1_1.setCellValue("单位名称");
cell1_1.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_2 = row1.createCell(2);
cell1_2.setCellValue("总目录数量");
cell1_2.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_3 = row1.createCell(3);
sheet.addMergedRegion(new CellRangeAddress(1,1,2,3));
cell1_3.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_4 = row1.createCell(4);
cell1_4.setCellValue("目录认领数量");
cell1_4.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_5 = row1.createCell(5);
sheet.addMergedRegion(new CellRangeAddress(1,1,4,5));
cell1_5.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_6 = row1.createCell(6);
cell1_6.setCellValue("实施清单编制数量");
cell1_6.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_7 = row1.createCell(7);
sheet.addMergedRegion(new CellRangeAddress(1,1,6,7));
cell1_7.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_8 = row1.createCell(8);
cell1_8.setCellValue("实施清单审核数量");
cell1_8.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_9 = row1.createCell(9);
sheet.addMergedRegion(new CellRangeAddress(1,1,8,9));
cell1_9.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_10 = row1.createCell(10);
cell1_10.setCellValue("实施清单发布数量");
cell1_10.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_11 = row1.createCell(11);
sheet.addMergedRegion(new CellRangeAddress(1,1,10,11));
cell1_11.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_12 = row1.createCell(12);
cell1_12.setCellValue("办理项数量");
cell1_12.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_13 = row1.createCell(13);
sheet.addMergedRegion(new CellRangeAddress(1,1,12,13));
cell1_13.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_14 = row1.createCell(14);
cell1_14.setCellValue("事项情形化数量");
cell1_14.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_15 = row1.createCell(15);
sheet.addMergedRegion(new CellRangeAddress(1,1,14,15));
cell1_15.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_16 = row1.createCell(16);
cell1_16.setCellValue("高频事项数量");
cell1_16.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_17 = row1.createCell(17);
sheet.addMergedRegion(new CellRangeAddress(1,1,16,17));
cell1_17.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_18 = row1.createCell(18);
cell1_18.setCellValue("最多跑一次事项数量");
cell1_18.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_19 = row1.createCell(19);
sheet.addMergedRegion(new CellRangeAddress(1,1,18,19));
cell1_19.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_20 = row1.createCell(20);
cell1_20.setCellValue("不见面审批事项数量");
cell1_20.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell1_21 = row1.createCell(21);
sheet.addMergedRegion(new CellRangeAddress(1,1,20,21));
cell1_21.setCellStyle(cellStyle(wb));//背景色
HSSFRow row2 =sheet.createRow(2);//第三行
row2.setHeight((short) 500);//设置行高
HSSFCell cell2_0 = row2.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(1,2,0,0));
HSSFCellStyle cellStyle2 = wb.createCellStyle();//新建单元格样式
//边框
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell2_0.setCellStyle(cellStyle2);//背景色
HSSFCell cell2_1 = row2.createCell(1);
sheet.addMergedRegion(new CellRangeAddress(1,2,1,1));
HSSFCellStyle cellStyle1 = wb.createCellStyle();//新建单元格样式
//边框
cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cell2_1.setCellStyle(cellStyle1);//背景色
HSSFCell cell2_2 = row2.createCell(2);
cell2_2.setCellValue("行政权力类数量");
cell2_2.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_3 = row2.createCell(3);
cell2_3.setCellValue("公共服务数量");
cell2_3.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_4 = row2.createCell(4);
cell2_4.setCellValue("行政权力类数量");
cell2_4.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_5 = row2.createCell(5);
cell2_5.setCellValue("公共服务数量");
cell2_5.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_6 = row2.createCell(6);
cell2_6.setCellValue("行政权力类数量");
cell2_6.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_7 = row2.createCell(7);
cell2_7.setCellValue("公共服务数量");
cell2_7.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_8 = row2.createCell(8);
cell2_8.setCellValue("行政权力类数量");
cell2_8.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_9 = row2.createCell(9);
cell2_9.setCellValue("公共服务数量");
cell2_9.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_10 = row2.createCell(10);
cell2_10.setCellValue("行政权力类数量");
cell2_10.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_11 = row2.createCell(11);
cell2_11.setCellValue("公共服务数量");
cell2_11.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_12 = row2.createCell(12);
cell2_12.setCellValue("行政权力类数量");
cell2_12.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_13 = row2.createCell(13);
cell2_13.setCellValue("公共服务数量");
cell2_13.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_14 = row2.createCell(14);
cell2_14.setCellValue("行政权力类数量");
cell2_14.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_15 = row2.createCell(15);
cell2_15.setCellValue("公共服务数量");
cell2_15.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_16 = row2.createCell(16);
cell2_16.setCellValue("行政权力类数量");
cell2_16.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_17 = row2.createCell(17);
cell2_17.setCellValue("公共服务数量");
cell2_17.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_18 = row2.createCell(18);
cell2_18.setCellValue("行政权力类数量");
cell2_18.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_19 = row2.createCell(19);
cell2_19.setCellValue("公共服务数量");
cell2_19.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_20 = row2.createCell(20);
cell2_20.setCellValue("行政权力类数量");
cell2_20.setCellStyle(cellStyle(wb));//背景色
HSSFCell cell2_21 = row2.createCell(21);
cell2_21.setCellValue("公共服务数量");
cell2_21.setCellStyle(cellStyle(wb));//背景色
//第四行
// HSSFCellStyle styleContent = creatStyle(wb, "宋体", 12, HSSFCellStyle.ALIGN_CENTER,true,false);
int rowNumber = 3;
for (TodayCount tc : todayCount) {
if(tc != null) {
HSSFRow row3 = sheet.createRow(rowNumber);
HSSFCell cell3_0 = row3.createCell(0);
cell3_0.setCellValue(tc.getXzqh());
cell3_0.setCellStyle(jz);
//
HSSFCell cell3_1 = row3.createCell(1);
cell3_1.setCellValue(tc.getDeptName());
cell3_1.setCellStyle(jz);
//
HSSFCell cell3_2 = row3.createCell(2);
if(!"".equals(tc.getCountNuber()) && tc.getCountNuber() !=null)
cell3_2.setCellValue(tc.getCountNuber());// 总目录;
cell3_2.setCellStyle(jz);
//
HSSFCell cell3_3 = row3.createCell(3);
cell3_3.setCellStyle(jz);
HSSFCell cell3_4 = row3.createCell(4);
if(!"".equals(tc.getGcRl()) && tc.getGcRl() !=null)
cell3_4.setCellValue(tc.getGcRl());//已认领
cell3_4.setCellStyle(jz);
//
HSSFCell cell3_5 = row3.createCell(5);
cell3_5.setCellStyle(jz);
//
HSSFCell cell3_6 = row3.createCell(6);
if(!"".equals(tc.getGcBz()) && tc.getGcBz() !=null)
cell3_6.setCellValue(tc.getGcBz());// 编制
cell3_6.setCellStyle(jz);
//
HSSFCell cell3_7 = row3.createCell(7);
cell3_7.setCellStyle(jz);
//
HSSFCell cell3_8 = row3.createCell(8);
if(!"".equals(tc.getLgbSh()) && tc.getLgbSh() !=null)
cell3_8.setCellValue(tc.getLgbSh());// 已审核;
cell3_8.setCellStyle(jz);
//
HSSFCell cell3_9 = row3.createCell(9);
cell3_9.setCellStyle(jz);
//
HSSFCell cell3_10 = row3.createCell(10);
if(!"".equals(tc.getLgbFb()) && tc.getLgbFb() !=null)
cell3_10.setCellValue(tc.getLgbFb());// 已发布;
cell3_10.setCellStyle(jz);
//
HSSFCell cell3_11 = row3.createCell(11);
cell3_11.setCellStyle(jz);
//
HSSFCell cell3_12 = row3.createCell(12);
if(!"".equals(tc.getLabBl()) && tc.getLabBl() !=null)
cell3_12.setCellValue(tc.getLabBl());// 办理项;
cell3_12.setCellStyle(jz);
//
HSSFCell cell3_13 = row3.createCell(13);
cell3_13.setCellStyle(jz);
HSSFCell cell3_14 = row3.createCell(14);// 情形化;
if(!"".equals(tc.getZxYzx()) && tc.getZxYzx() !=null)
cell3_14.setCellValue(tc.getZxYzx());
cell3_14.setCellStyle(jz);
//
HSSFCell cell3_15 = row3.createCell(15);
cell3_15.setCellStyle(jz);
//
HSSFCell cell3_16 = row3.createCell(16);
cell3_16.setCellStyle(jz);
//
HSSFCell cell3_17 = row3.createCell(17);
cell3_17.setCellStyle(jz);
//
HSSFCell cell3_18 = row3.createCell(18);
cell3_18.setCellStyle(jz);
//
HSSFCell cell3_19 = row3.createCell(19);
cell3_19.setCellStyle(jz);
HSSFCell cell3_20 = row3.createCell(20);
cell3_20.setCellStyle(jz);
HSSFCell cell3_21 = row3.createCell(21);
cell3_21.setCellStyle(jz);
rowNumber++;
}
}
//最后一行
// HSSFRow rowLast =sheet.createRow(rowNumber);
// rowLast.setHeight((short) ((short) 22*20));
// HSSFCell cellLast = rowLast.createCell(0);
// HSSFCellStyle styleLast = creatStyle(wb, "宋体", 12, HSSFCellStyle.ALIGN_CENTER,false,false);
// cellLast.setCellStyle(styleContent1);
// cellLast.setCellValue("备注");
// sheet.addMergedRegion(new CellRangeAddress(rowNumber,rowNumber,0,18));
return wb;
}
/**
* @Author shenWB
* @Date 15:49
* @Param [wb, fontName, fontHeightInPoints, alignment, border, boldweight]
* @Return org.apache.poi.hssf.usermodel.HSSFCellStyle
* 样式工具类，可设置字体，边框，等
*/
public static HSSFCellStyle creatStyle(HSSFWorkbook wb, String fontName, int fontHeightInPoints,short alignment,boolean border,boolean boldweight){
//设置内容字体
HSSFFont fontContent = wb.createFont();
if (boldweight){
// 字体加粗
fontContent.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
fontContent.setFontName(fontName);
fontContent.setFontHeightInPoints((short) fontHeightInPoints);
//创建内容样式
HSSFCellStyle styleContent = wb.createCellStyle();
styleContent.setFont(fontContent);
styleContent.setWrapText(true);
styleContent.setAlignment(alignment);
styleContent.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
if (border){
styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleContent.setBottomBorderColor(HSSFColor.BLACK.index);
styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleContent.setLeftBorderColor(HSSFColor.BLACK.index);
styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleContent.setRightBorderColor(HSSFColor.BLACK.index);
styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleContent.setTopBorderColor(HSSFColor.BLACK.index);
}
return styleContent;
}
/**
* @Author shenWB
* @Date 16:04
* @Param [wb]
* @Return org.apache.poi.hssf.usermodel.HSSFCellStyle
* 设置单元格样式，边框，居中
*/
public static HSSFCellStyle cellStyle(HSSFWorkbook wb){
HSSFCellStyle cellStyle = wb.createCellStyle();//新建单元格样式
// cellStyle.setWrapText(true);//自动换行
//设置背景颜色
cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());// 设置背景色(short) 13
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
return cellStyle;
}
}
3、导出效果
