写了一个方法,注解都在代码里
- /** 带分类标题导出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();
- }
实现方法
- @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);
- }
达成效果