首先页面请求进行导出服务层进行处理：
@ResponseBody
@RequestMapping(value="/exportCollegeDetailExcel.action")
public String exportCollegeDetailExcel(String school_year,String batch_id,String stu_type,String college,String origin_id) {
List pageList = newstudentsCollegeStatisticsManager.collegeStatisticsDeatil_new(school_year,batch_id,stu_type,origin_id,college);
try {
String[] keys = new String[] {"college_name", "major_name", "should_report_num","should_report_man_num","should_report_woman_num", "reported_num","reported_man_num","reported_woman_num", "unreported_num","unreported_man_num","unreported_woman_num", "reported_percentage"};
String[] headers = new String[] {"院系", "学生类型", "录取人数","录取男生人数","录取女生人数", "报道人数","男生报道人数","女生报道人数", "未报到人数","未报到男生人数","未报到女生人数", "报到率"};
HttpServletResponse response = getResponse();
ExportExcel export = new ExportExcel();
ExportExcel.prepareResponseHeader(getRequest(), response, "院系报到统计");
// export.exportExcel("院系报到统计", headers, pageList, getResponse().getOutputStream(), keys);
export.exportCollegeExcel("院系报到统计", headers, pageList, getResponse().getOutputStream(), keys);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}然后进行poi进行表格的处理：
//专门为院系专业报到统计定制
public void exportCollegeMajorExcel(String sheetTitle, String[] headers,
Collection<T> dataset, OutputStream out, Object... keyStrings) {
exportCollegeMajorExcel(sheetTitle, headers, dataset, out, "yyyy-MM-dd", keyStrings);
}接下来就是数据表头的设计处理：
我们以一下为例的表格：
此处的表头需要进行相应的处理合并。
@SuppressWarnings("deprecation")
public void exportCollegeMajorClassesExcel(String title, String[] headers,
Collection<T> dataset, OutputStream out, String pattern,
Object... keyStrings) {
index = 0;
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet;
int init = 65535; // 单个excel最大数据量
// 定义注释的大小和位置,详见文档
// HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
// 0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
// comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
// comment.setAuthor("leno");
// 产生表格标题行
int indexTotal = 0;
if(!Utility.isEmpty(dataset)) {
indexTotal = dataset.size() / init
+ (dataset.size() % init > 0 ? 1 : 0);
for (int k = 0; k < indexTotal; k++) {
index = 0;
List<T> dataList = new ArrayList<T>();
if(dataset.size() > init) {
if(k != indexTotal - 1) {
dataList = ((List)dataset).subList(k*init, (k+1)*init);
} else {
dataList = ((List)dataset).subList(k*init, dataset.size());
}
} else {
dataList = (List)dataset;
}
sheet = workbook.createSheet(title+k);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
if (headers != null && headers.length > 0) {
// 生成一个样式
try {
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor((short) 20);
font.setFontHeightInPoints((short) 12);
style.setWrapText(true);
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// HSSFRow row = sheet.createRow(index);
//进行标题行的设计
//这里的表头使用HSSFRichTextString
HSSFRow rowTitle=sheet.createRow(0);
HSSFCell cellNum=rowTitle.createCell((short)0);
HSSFRichTextString text1 = new HSSFRichTextString("院系");
cellNum.setCellValue(text1);
cellNum.setCellStyle(style);
HSSFCell cellName=rowTitle.createCell((short)1);
HSSFRichTextString text2 = new HSSFRichTextString("专业");
cellName.setCellValue(text2);
cellName.setCellStyle(style);
HSSFCell cellClass=rowTitle.createCell((short)2);
HSSFRichTextString text25 = new HSSFRichTextString("班级");
cellClass.setCellValue(text25);
cellClass.setCellStyle(style);
HSSFCell cellMsg=rowTitle.createCell((short)3);
HSSFRichTextString text3 = new HSSFRichTextString("录取情况");
cellMsg.setCellValue(text3);
cellMsg.setCellStyle(style);
HSSFCell cellDept=rowTitle.createCell((short)6);
HSSFRichTextString text4 = new HSSFRichTextString("报道情况");
cellDept.setCellValue(text4);
cellDept.setCellStyle(style);
HSSFCell cellnotArrivied=rowTitle.createCell((short)9);
HSSFRichTextString text5= new HSSFRichTextString("未报到情况");
cellnotArrivied.setCellValue(text5);
cellnotArrivied.setCellStyle(style);
HSSFCell cellpersent=rowTitle.createCell((short)12);
HSSFRichTextString text6= new HSSFRichTextString("报到率");
cellpersent.setCellValue(text6);
cellpersent.setCellStyle(style);
//第二行两列数据
HSSFRow rowAT=sheet.createRow(1);
HSSFCell cellS=rowAT.createCell((short)0);
HSSFRichTextString text20= new HSSFRichTextString("");
cellS.setCellValue(text20);
cellS.setCellStyle(style);
HSSFCell cellSs=rowAT.createCell((short)1);
HSSFRichTextString text21= new HSSFRichTextString("");
cellSs.setCellValue(text21);
cellSs.setCellStyle(style);
HSSFCell cellclass=rowAT.createCell((short)2);
HSSFRichTextString text26= new HSSFRichTextString("");
cellclass.setCellValue(text26);
cellclass.setCellStyle(style);
HSSFCell cellSss=rowAT.createCell((short)3);
HSSFRichTextString text7= new HSSFRichTextString("录取人数");
cellSss.setCellValue(text7);
cellSss.setCellStyle(style);
HSSFCell cellCard=rowAT.createCell((short)4);
HSSFRichTextString text8= new HSSFRichTextString("男生人数");
cellCard.setCellValue(text8);
cellCard.setCellStyle(style);
HSSFCell cellD=rowAT.createCell((short)5);
HSSFRichTextString text9= new HSSFRichTextString("女生人数");
cellD.setCellValue(text9);
cellD.setCellStyle(style);
HSSFCell cella=rowAT.createCell((short)6);
HSSFRichTextString text10= new HSSFRichTextString("报到人数");
cella.setCellValue(text10);
cella.setCellStyle(style);
HSSFCell cellb=rowAT.createCell((short)7);
HSSFRichTextString text11= new HSSFRichTextString("男生人数");
cellb.setCellValue(text11);
cellb.setCellStyle(style);
HSSFCell cellc=rowAT.createCell((short)8);
HSSFRichTextString text12= new HSSFRichTextString("女生人数");
cellc.setCellValue(text12);
cellc.setCellStyle(style);
HSSFCell celld=rowAT.createCell(9);
HSSFRichTextString text13= new HSSFRichTextString("未报到人数");
celld.setCellValue(text13);
celld.setCellStyle(style);
HSSFCell celle=rowAT.createCell((short)10);
HSSFRichTextString text14= new HSSFRichTextString("男生人数");
celle.setCellValue(text14);
celle.setCellStyle(style);
HSSFCell cellf=rowAT.createCell((short)11);
HSSFRichTextString text15= new HSSFRichTextString("女生人数");
cellf.setCellValue(text15);
cellf.setCellStyle(style);
//这里的CellRangAddress(a,b,c,d)其中是从a行b行，从c列到d列的合并单元格
CellRangeAddress region1=new CellRangeAddress ((short)0,(short)1,(short)0,(short)0);
CellRangeAddress region2=new CellRangeAddress ((short)0,(short)1,(short)1,(short)1);
CellRangeAddress region7=new CellRangeAddress ((short)0,(short)1,(short)2,(short)2);
CellRangeAddress region3=new CellRangeAddress ((short)0,(short)0,(short)3,(short)5);
CellRangeAddress region4=new CellRangeAddress ((short)0,(short)0,(short)6,(short)8);
CellRangeAddress region5=new CellRangeAddress ((short)0,(short)0,(short)9,(short)11);
CellRangeAddress region6=new CellRangeAddress ((short)0,(short)1,(short)12,(short)12);
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
sheet.addMergedRegion(region3);
sheet.addMergedRegion(region4);
sheet.addMergedRegion(region5);
sheet.addMergedRegion(region6);
sheet.addMergedRegion(region7);
//注意此为标题的行数，一定要写。
index=2;
} catch (Exception e) {
e.printStackTrace();
}
}
// 遍历集合数据，产生数据行，此处是循环显示数据行的
putDateset2Rows(dataList, pattern, workbook, sheet, patriarch,
keyStrings);
}
} else {
sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
if (headers != null && headers.length > 0) {
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor((short) 20);
font.setFontHeightInPoints((short) 12);
style.setWrapText(true);
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
HSSFRow row = sheet.createRow(index);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//index++;
}
// 遍历集合数据，产生数据行
putDateset2Rows(dataset, pattern, workbook, sheet, patriarch,
keyStrings);
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
}
}
导出效果如下：
‘
’
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
