版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/luo4105/article/details/51833999
这个是将公司的一个工具栏改成的，以前工具类只能导出单层的excel
实现效果
写了一个方法，注解都在代码里
代码语言：javascript复制/** 带分类标题导出Excel的方法
*
* @param title
* excel中的sheet名称
* @param header_2
* 两列的头的标题
* @param header_cate
* 分类列
* @param cate_num
* 分类列行数
* @param header_1
* 分类列行数
* @param columns
* 列名
* @param result
* 结果集
* @param out
* 输出流
* @param pattern
* 时间格式
*/
@SuppressWarnings("deprecation")
public void exportoExcel1(String title, String[] header_2, String[] header_cate, int[] cate_num, String[] header_1, String[] columns, Collection<T> result, OutputStream out,
String pattern) throws Exception {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置单元格背景色，设置单元格背景色以下两句必须同时设置
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.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);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 以下可以用于设置导出的数据的样式
// 产生表格标题行
// 表头的样式
HSSFCellStyle titleStyle = workbook.createCellStyle();// 创建样式对象
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);// 水平居中
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
// 设置字体
HSSFFont titleFont = workbook.createFont(); // 创建字体对象
titleFont.setFontHeightInPoints((short) 15); // 设置字体大小
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 设置粗体
// titleFont.setFontName("黑体"); // 设置为黑体字
titleStyle.setFont(titleFont);
//第一行
CellRangeAddress cra=new CellRangeAddress(0, (short) 0, 0, (short) (columns.length - 1));//参数:起始行号，终止行号， 起始列号，终止列号
sheet.addMergedRegion(cra);
Row row = sheet.createRow(0);
Cell row0=row.createCell(0);
row0.setCellValue(title);
row0.setCellStyle(titleStyle);
for(int i=0; i<header_2.length; i ){
cra=new CellRangeAddress(1, 2, i, i);
sheet.addMergedRegion(cra);
}
int sum1 = 0;
int sum2 = 0;
for(int i=0; i<header_cate.length; i ){
sum1 = cate_num[i];
cra=new CellRangeAddress(1, 1, 1 sum2, sum1); //
sheet.addMergedRegion(cra);
sum2 = cate_num[i];
}
row = sheet.createRow(1);
for(int i=0; i<header_2.length; i ){
final Cell cell = row.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(header_2[i]);
}
int sum = 0;
for(int i=0; i<header_cate.length; i ){
final Cell cell = row.createCell(1 sum); //
cell.setCellStyle(style);
cell.setCellValue(header_cate[i]);
sum = cate_num[i];
}
row = sheet.createRow(2);
for(int i=0; i<header_1.length; i ){
final Cell cell = row.createCell(i 1); //
cell.setCellStyle(style);
cell.setCellValue(header_1[i]);
}
// 遍历集合数据，产生数据行
if (result != null) {
int index = 3;
for (T t : result) {
row = sheet.createRow(index);
index ;
for (short i = 0; i < columns.length; i ) {
Cell cell = row.createCell(i);
String fieldName = columns[i];
String getMethodName = "get" fieldName.substring(0, 1).toUpperCase() fieldName.substring(1);
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Class[] {});
String textValue = null;
if (value == null) {
textValue = "";
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else if (value instanceof byte[]) {
// 有图片时，设置行高为60px;
row.setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, (short) (35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6,
index);
anchor.setAnchorType(2);
patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
} else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
if (textValue != null) {
Pattern p = Pattern.compile("^//d (//.//d )?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
}
}
}
workbook.write(out);
out.flush();
out.close();
}实现方法
代码语言：javascript复制@RequestMapping(InterfaceConsts.EXPORT_MONTHTESTRSTLIST)
@ResponseBody
public void export_MonthTestrstList(HttpServletRequest request, WqTestrstDForm form,
String jsonpCallback, HttpServletResponse response) throws Exception{
Map<String, Object> params = new HashMap<String, Object>();
params = MapUtil.toMap(form);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream out = response.getOutputStream();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM");
String title = sdf.format(new Date(form.getTimeStart().replaceAll("-", "/"))) wqPrjBService.getWqPrjBBySmid(Integer.valueOf(form.getSmid())).getPrjnm() "水质监测月报表";
String[] header_2={"检测项目"};
int[] cate_num={5,5,4};
String[] header_cate={"源水","出厂水","末梢水"};
String[] headers = { "原水检验次数", "原水最大值", "原水最小值", "原水平均值", "出厂水检验次数", "出厂水最大值", "出厂水最小值", "出厂水平均值", "出厂水标准值", "出厂水检验次数", "管网末梢水最大值", "管网末梢水最小值", "管网末梢水平均值", "管网末梢水标准值"};
String[] columns = { "itmnm", "count01", "tstv01Max", "tstv01Min", "tstv01Avg", "count02", "tstv02Max", "tstv02Min", "tstv02Avg", "ostnv", "count03", "tstv03Max", "tstv03Min", "tstv03Avg", "estnv"};
List<WqTestrstDMonth> houseList = wqTestrstDService.getAllMonthTestrstList(params);//我的数据源
ExcelUitl<WqTestrstDMonth> excelUtil = new ExcelUitl<WqTestrstDMonth>();
String pattern = "yyyy-MM-dd HH:mm:dd";
response.setHeader("Content-Disposition", "attachment;filename=" new String((title ".xls").getBytes(), "iso-8859-1"));
excelUtil.exportoExcel1(title, header_2, header_cate, cate_num, headers, columns, houseList, out, pattern);
}达成效果
