ViewExcel

package com.sf.novatar.tpl.util;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.CellRangeAddress;import org.apache.poi.ss.usermodel.ClientAnchor;import org.apache.poi.ss.usermodel.CreationHelper;import org.apache.poi.ss.usermodel.Drawing;import org.apache.poi.ss.usermodel.Picture;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.servlet.view.document.AbstractExcelView;@SuppressWarnings("deprecation")public class ViewExcel extends AbstractExcelView {@SuppressWarnings("unchecked")@Overrideprotected void buildExcelDocument(Map<String, Object> model,HSSFWorkbook workbook, HttpServletRequest request,HttpServletResponse response) throws Exception {// sheet存在导出文件的机构代码，导入时需要使用此数据项String orgCode = (String) model.get("orgCode");HSSFSheet sheet = workbook.createSheet(orgCode);sheet.setDefaultColumnWidth(12);String[] cellsTitle = (String[]) model.get("cellsTitle");HSSFCell cell = null;HSSFCellStyle cellStyle = workbook.createCellStyle();HSSFFont font = workbook.createFont();font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);cellStyle.setFont(font);cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框int row = 0; //开始行int column = 0; //开始列int columns = 0; //合并列数//报表头部Map<Integer, String> header = (Map<Integer, String>) model.get("header");if (header != null) {for(Map.Entry<Integer, String> entry: header.entrySet()) {columns = entry.getKey().intValue(); //合并单元格列数String cotent = entry.getValue(); //单元格内容cell = getCell(sheet, row, 0);cell.setCellType(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle); //设置单元格样式setText(cell, cotent); //设置第row行0列单元格for (int i = 1; i < columns; i++) { //从第2列开始cell = getCell(sheet, row, i);cell.setCellStyle(cellStyle); //设置单元格样式setText(cell, ""); //设置第row行1列到column列单元格}sheet.addMergedRegion(new CellRangeAddress(row, row, 0, columns - 1)); //合并单元格}row = 1; //余下的从第1行开始}columns = 0;String[] columnTitles = null;String columnTitle = "";//报表合并标题Map<Integer, String[]> titles = (Map<Integer, String[]>) model.get("titles");if (titles != null) {for (String content : cellsTitle) {columnTitles = titles.get(column);if (columnTitles != null) {columns = column + Integer.parseInt(columnTitles[0]) - 1;columnTitle = columnTitles[1];}if (columnTitles != null || (column <= columns && column != 0)) {cell = getCell(sheet, row, column);cell.setCellType(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle);setText(cell, columnTitle);sheet.addMergedRegion(new CellRangeAddress(row, row, column, columns));cell = getCell(sheet, row + 1, column);cell.setCellType(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle);setText(cell, content);} else {cell = getCell(sheet, row, column);cell.setCellType(HSSFCell.ENCODING_UTF_16);cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置单元格内容上下居中cell.setCellStyle(cellStyle);setText(cell, content);cell = getCell(sheet, row + 1, column);cell.setCellStyle(cellStyle);setText(cell, "");sheet.addMergedRegion(new CellRangeAddress(row, row + 1, column, column));}column += 1;}row += 2;} else {for (String content : cellsTitle) {cell = getCell(sheet, row, column);cell.setCellType(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle);setText(cell, content);column += 1;}row += 1;}cellStyle = workbook.createCellStyle();cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框ArrayList<String[]> excelData = (ArrayList<String[]>) model.get("data");if (excelData != null) {// 记录行for (String[] contents : excelData) {column = 0;for (String content : contents) {cell = getCell(sheet, row, column);cell.setCellType(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle);setText(cell, content);column += 1;}row += 1;}}Map<String[], List<String[]>> rowData = (Map<String[], List<String[]>>) model.get("rowData");if (rowData != null && rowData.size() > 0) {for(Map.Entry<String[], List<String[]>> entry: rowData.entrySet()) {String[] keys = entry.getKey();List<String[]> valueList = entry.getValue();int size = valueList.size();for (int i = 0; i < keys.length; i++) {cell = getCell(sheet, row, i);cell.setCellType(HSSFCell.ENCODING_UTF_16);cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置单元格内容上下居中cell.setCellStyle(cellStyle);setText(cell, keys[i]);if (valueList.size() > 1) {for (int j = 1; j < valueList.size(); j++) {cell = getCell(sheet, row + j, i);cell.setCellStyle(cellStyle);setText(cell, "");}sheet.addMergedRegion(new CellRangeAddress(row, row + valueList.size() - 1, i, i));}}for (int i = 0; i < valueList.size(); i++) {String[] values = valueList.get(i);for (int j = 0; j < values.length; j++) {cell = getCell(sheet, row + i, keys.length + j);cell.setCellType(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle);//setText(cell, values[i]);setText(cell, values[j]);}}row += size;}}columns = 0;//报表底部Map<Integer, List<String>> footer = (Map<Integer, List<String>>) model.get("footer");if (footer != null) {for(Map.Entry<Integer, List<String>> entry: footer.entrySet()) {columns = entry.getKey().intValue(); //合并单元格列数List<String> totals = entry.getValue();cell = getCell(sheet, row, 0);cell.setCellType(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle);setText(cell, "合计：");for (int k = 1; k < columns; k++) {cell = getCell(sheet, row, k);cell.setCellStyle(cellStyle);setText(cell, "");}sheet.addMergedRegion(new CellRangeAddress(row, row, 0, columns - 1));for (String total : totals) {cell = getCell(sheet, row, columns++);cell.setCellType(HSSFCell.ENCODING_UTF_16);cell.setCellStyle(cellStyle);setText(cell, total);}for (int i = columns; i < cellsTitle.length; i++) {cell = getCell(sheet, row, i);cell.setCellStyle(cellStyle);setText(cell, "");}}} // 图片字节加入到Excelif(null!=model.get("insertImg")){  Map<String, Object> imgAttribute=(Map<String, Object>) model.get("insertImg");          int pictureIdx = workbook.addPicture((byte[])imgAttribute.get("imgBytes"), XSSFWorkbook.PICTURE_TYPE_PNG);          CreationHelper helper = workbook.getCreationHelper();          //获取excel 第几个sheet          Sheet sheet0 = workbook.getSheetAt(((Integer)imgAttribute.get("sheetAt")));          Drawing drawing = sheet0.createDrawingPatriarch();          ClientAnchor anchor = helper.createClientAnchor();          //设置图片初始位置          anchor.setCol1(((Integer)imgAttribute.get("col1")));          anchor.setRow1(((Integer)imgAttribute.get("row1")));          Picture pict = drawing.createPicture(anchor, pictureIdx);          pict.resize();        }String filename = model.get("fileName") + ".xls";// 设置下载时客户端Excel的名称filename = ExcelNameEncode.encodeFilename(filename, request);// 处理中文文件名response.setContentType("application/vnd.ms-excel;charset=UTF-8");// response.setContentType("application/msexcel;charset=GB2312");response.setHeader("Content-disposition", "attachment;filename="+ filename);OutputStream ouputStream = response.getOutputStream();workbook.write(ouputStream);ouputStream.flush();ouputStream.close();}}