报表的导出

首先是Controller

/** * 导出 * @return */@RequestMapping(value = "/Export", method = RequestMethod.GET)@ResponseBodypublic void export(ModelMap model, HttpServletRequest request, HttpServletResponse response, VehicleInfo vehicleInfo) {    vehicleInfo = toTrim(vehicleInfo);    String userCode = (String) request.getSession().getAttribute(SessionSecurityConstants.KEY_USER_NAME);    String fileName = "私车公用.xlsx";    //获取数据    List<VehicleInfo> vehicleInfoList = vehicleInfoService.searchAllVehicleInfos(vehicleInfo, userCode);    try {        fileName = new String(fileName.getBytes("GBK"), "ISO-8859-1");        ExportVehicleInfoTemplate exportTemplate = new ExportVehicleInfoTemplate();        exportTemplate.doExport(response, fileName, vehicleInfoList);    } catch (UnsupportedEncodingException e) {        LOGGER.error("UnsupportedEncodingException", e);    } catch (IOException e) {        LOGGER.error("IOException", e);    }}

然后是报表模板，首先是两个公用的

第一个

import javax.servlet.http.HttpServletResponse;import java.util.List;/** * 将数据导出到excel接口定义 * @author WangXuzheng * */public interface ExcelExportTemplate<T> {   /**    * 将数据导出为excel    * @param fileName 文件输出流    * @param response 参数    */   void doExport(HttpServletResponse response, String fileName, List<T> ts) throws Exception;   /**    * 要创建的excel文件的sheet名称    * @return    */   String[] getSheetNames();      /**    * 要创建的excel表格中的表头内容.    * list中存放的是多个sheet的表头内容    * @return    */   String[][] getTitles();      /**    * 要创建的excel表格的每个sheet的表头    * @return    */   String[] getCaptions();      /**    * 控制文件在内存中最多占用多少条    * @return    */   int getRowAccessWindowSize();}

第二个

import java.io.IOException;import java.io.OutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang.StringUtils;import org.apache.commons.lang.Validate;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFWorkbook;/** * @author WangXuzheng * */public abstract class AbstractExcelExportTemplate<T> implements ExcelExportTemplate<T> {   /**    * 默认表格宽度    */   private static final int DEFAULT_COLUMN_WIDTH = 7000;   /**    * excel文件对象    */   protected Workbook workbook;   /**    * excel sheet列表    */   protected List<Sheet> sheets = new ArrayList<Sheet>();   /**    * 标题栏    */   protected String[][] titles;   protected CellStyle captionRowSytle;   /**    * 默认标题行样式    */   protected CellStyle titleRowStyle;   /**    * 默认内容行样式    */   protected CellStyle bodyRowStyle;   /**    * 各个sheet是否包含抬头,key:sheet坐标，value:包含true,否则false    */   protected Map<Integer, Boolean> hasCaptionMap = new HashMap<Integer, Boolean>();   /**    * 默认单元格宽度    */   protected int columnWidth = DEFAULT_COLUMN_WIDTH;   /**    * 参数列表    */   protected T parameters;   /*    * (non-Javadoc)    *     * @see    * com.haier.openplatform.excel.ExcelExportService#doExport(java.io.OutputStream    * )    */   @Override   public void doExport(HttpServletResponse response, String fileName, List<T> ts) throws IOException {      String[] sheetNames = this.getSheetNames();      Validate.notEmpty(sheetNames);      this.workbook = new SXSSFWorkbook(getRowAccessWindowSize());      this.titles = this.getTitles();      this.captionRowSytle = crateCaptionCellStyle();      this.titleRowStyle = crateTitleCellStyle();      this.bodyRowStyle = crateBodyCellStyle();      this.afterCreateWorkBook();      for (int i = 0; i < sheetNames.length; i++) {         Sheet sheet = workbook.createSheet(sheetNames[i]);         this.sheets.add(sheet);         afterBuildSheet(i);         buildCaption(i);         buildTitle(i);         afterBuildTitle(i);         buildBody(i, ts);         afterBuildBody(i);      }      response.setContentType("application/vnd.ms-excel");      response.setHeader("Content-disposition", "attachment;filename="            + fileName);      OutputStream ouputStream = response.getOutputStream();      workbook.write(ouputStream);      ouputStream.flush();      ouputStream.close();   }   /**    * 创建单元格    *     * @param row    * @param index    * @param cellValue    * @param cellStyle    */   protected void createStyledCell(Row row, int index, String cellValue, CellStyle cellStyle) {      Cell cell = row.createCell(index);      cell.setCellValue(cellValue);      cell.setCellStyle(cellStyle);   }   /**    * 在创建完毕HSSFWorkBook对象和样式对象后作的处理操作，通常用来对默认的样式进行重新定义    */   protected void afterCreateWorkBook() {   }   /**    * 获取excel抬头样式    *     * @return    */   protected CellStyle crateCaptionCellStyle() {      Font font = workbook.createFont();      font.setColor(Font.COLOR_NORMAL);      CellStyle cellStyle = workbook.createCellStyle();      cellStyle.setWrapText(false);      font.setFontHeight((short) 250);      cellStyle.setFont(font);      cellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.index);      cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);      cellStyle.setAlignment(CellStyle.ALIGN_CENTER);      return cellStyle;   }   /**    * 获取excel表头样式    *     * @return    */   protected CellStyle crateTitleCellStyle() {      Font font = workbook.createFont();      font.setColor(Font.COLOR_NORMAL);      CellStyle cellStyle = workbook.createCellStyle();      cellStyle.setWrapText(false);      font.setFontHeight((short) 250);      cellStyle.setFont(font);      cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);      cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);      short border = 1;      setCellBorder(cellStyle, border, border, border, border);      cellStyle.setAlignment(CellStyle.ALIGN_LEFT);      return cellStyle;   }   /**    * 设置单元格的border    *     * @param cellStyle    * @param top    * @param bottom    * @param left    * @param right    */   protected void setCellBorder(CellStyle cellStyle, short top, short bottom, short left, short right) {      cellStyle.setBorderBottom(bottom);      cellStyle.setBorderLeft(left);      cellStyle.setBorderRight(right);      cellStyle.setBorderTop(top);   }   /**    * 获取excel内容样式    *     * @return    */   protected CellStyle crateBodyCellStyle() {      Font font = workbook.createFont();      // font.setColor(HSSFColor.BLUE_GREY.index);      CellStyle cellStyle = workbook.createCellStyle();      cellStyle.setWrapText(false);      cellStyle.setFont(font);      cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);      cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);      cellStyle.setAlignment(CellStyle.ALIGN_LEFT);      short border = 1;      setCellBorder(cellStyle, border, border, border, border);      return cellStyle;   }   /**    * 获取第n个excel sheet    *     * @param sheetIndex    * @return    */   protected Sheet getSheet(int sheetIndex) {      return this.sheets.get(sheetIndex);   }   /**    * 创建sheet完毕后做的操作    *     * @param sheetIndex    */   protected void afterBuildSheet(int sheetIndex) {   }   /**    * 在sheet的第一行插入标题    *     * @param sheetIndex    */   protected void buildCaption(int sheetIndex) {      Sheet sheet = getSheet(sheetIndex);      String[] captions = this.getCaptions();      hasCaptionMap.put(sheetIndex, false);      if (captions != null && captions.length >= sheetIndex + 1) {         String caption = captions[sheetIndex];         if (StringUtils.isNotBlank(caption)) {            Row row = sheet.createRow(0);            int lastColumn = calculateLastColumn(sheetIndex);            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, lastColumn);            sheet.addMergedRegion(cellRangeAddress);            createStyledCell(row, 0, caption, this.captionRowSytle);            hasCaptionMap.put(sheetIndex, true);         }      }   }   /**    * 计算最后一列数据数据的    *     * @param sheetIndex    * @return    */   protected int calculateLastColumn(int sheetIndex) {      if (this.titles != null && sheetIndex <= this.titles.length - 1            && this.titles[sheetIndex] != null) {         return this.titles[sheetIndex].length - 1;      } else {         return 1;      }   }   /**    * 创建sheet中数据的标题    *     * @param sheetIndex    */   protected void buildTitle(int sheetIndex) {      if (this.titles.length < sheetIndex + 1) {         return;      }      String[] ts = this.titles[sheetIndex];      if (ts == null) {         return;      }      Sheet sheet = this.getSheet(sheetIndex);      int titleStartIndex = this.getTitleStartIndex(sheetIndex);      Row rowTitle = sheet.createRow(titleStartIndex);      for (int i = 0; i < ts.length; i++) {         sheet.setColumnWidth(i, columnWidth);         createStyledCell(rowTitle, i, ts[i], this.titleRowStyle);      }   }   /**    * 获取各个sheet内容部分起始行index,默认为从第一行开始    *     * @param sheetIndex    *            sheet的index    * @return    */   protected int getBodyStartIndex(int sheetIndex) {      int captionRow = getTitleStartIndex(sheetIndex);      int titleRow = 0;      if (this.titles != null && this.titles.length >= sheetIndex + 1) {         if (titles[sheetIndex] != null && titles[sheetIndex].length > 0) {            titleRow = 1;         }      }      return captionRow + titleRow;   }   protected int getTitleStartIndex(int sheetIndex) {      return this.hasCaptionMap.get(sheetIndex) ? 1 : 0;   }   /**    * 创建sheet中数据的标题之后做的操作    *     * @param sheetIndex    */   protected void afterBuildTitle(int sheetIndex) {   }   /**    * 创建sheet中数据的内容    *     * @param sheetIndex    */   protected abstract void buildBody(int sheetIndex, List<T> ts);   /**    * 创建sheet中数据的内容之后做的操作    *     * @param sheetIndex    */   protected void afterBuildBody(int sheetIndex) {   }   @Override   public String[] getCaptions() {      return new String[] {};   }   @Override   public int getRowAccessWindowSize() {      return 200;   }   protected CellStyle getStringCellStyle() {      Font font = workbook.createFont();      // font.setColor(HSSFColor.BLUE_GREY.index);      CellStyle cellStyle = workbook.createCellStyle();      cellStyle.setWrapText(false);      cellStyle.setFont(font);      cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);      cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);      cellStyle.setAlignment(CellStyle.ALIGN_LEFT);      cellStyle.setDataFormat((short) 49);      short border = 1;      setCellBorder(cellStyle, border, border, border, border);      return cellStyle;   }}

然后是根据自己的数据写自己的模板

第三个

import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;/** * @Author: zhangshulong * @Description: 私车公用导出表格模板 * @Date: Created in 19:24  2016/11/21 */public class ExportVehicleInfoTemplate extends AbstractExcelExportTemplate<VehicleInfo> {    @Override    public String[] getSheetNames() {        return new String[] {"私车公用"};    }    @Override    public String[][] getTitles() {        return new String[][] {{"流程号", "状态", "申请人", "使用人", "车牌号", "所属部门", "所属公司", "申请使用开始时间", "驾驶证编码", "行驶证编码", "保险金额", "创建人", "创建时间", "维护人", "维护时间","是否冻结"}};    }    @Override    protected void buildBody(int sheetIndex, List<VehicleInfo> vehicleInfoList) {        Date date = new Date();        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");        Sheet sheet = getSheet(sheetIndex);        int startIndex = this.getBodyStartIndex(sheetIndex);        sheet.setDefaultColumnStyle(0, getStringCellStyle());        sheet.setDefaultColumnStyle(1, getStringCellStyle());        sheet.setDefaultColumnStyle(2, getStringCellStyle());        sheet.setDefaultColumnStyle(3, getStringCellStyle());        sheet.setDefaultColumnStyle(4, getStringCellStyle());        sheet.setDefaultColumnStyle(5, getStringCellStyle());        sheet.setDefaultColumnStyle(6, getStringCellStyle());        sheet.setDefaultColumnStyle(7, getStringCellStyle());        sheet.setDefaultColumnStyle(8, getStringCellStyle());        sheet.setDefaultColumnStyle(9, getStringCellStyle());        sheet.setDefaultColumnStyle(10, getStringCellStyle());        sheet.setDefaultColumnStyle(11, getStringCellStyle());        sheet.setDefaultColumnStyle(12, getStringCellStyle());        sheet.setDefaultColumnStyle(13, getStringCellStyle());        sheet.setDefaultColumnStyle(14, getStringCellStyle());        sheet.setDefaultColumnStyle(15, getStringCellStyle());        for (int i = 0; i < vehicleInfoList.size(); i++) {            Row row = sheet.createRow(i + startIndex);            row.setHeight((short) 300);            int index = 0;            createStyledCell(row, index++, vehicleInfoList.get(i).getDocument(), getStringCellStyle()); //流程单号            createStyledCell(row, index++, vehicleInfoList.get(i).getApprovalState(), getStringCellStyle()); //当前审批状态            createStyledCell(row, index++, vehicleInfoList.get(i).getUserCode(), getStringCellStyle()); //申请人            createStyledCell(row, index++, vehicleInfoList.get(i).getVehicleUserName(), getStringCellStyle()); //使用人            createStyledCell(row, index++, vehicleInfoList.get(i).getPlateNumber(), getStringCellStyle()); //车牌号            createStyledCell(row, index++, vehicleInfoList.get(i).getDepartmentName(), getStringCellStyle()); //所属部门            createStyledCell(row, index++, vehicleInfoList.get(i).getCompanyName(), getStringCellStyle()); //所属公司            if (null != vehicleInfoList.get(i).getApplyTime()) {                createStyledCell(row, index++, formatter.format(vehicleInfoList.get(i).getApplyTime()), getStringCellStyle()); //保险金额            } else {                createStyledCell(row, index++, "", getStringCellStyle());            }            createStyledCell(row, index++, vehicleInfoList.get(i).getDrivingLicence(), getStringCellStyle()); //驾驶证编码            createStyledCell(row, index++, vehicleInfoList.get(i).getVehicleTravelLicence(), getStringCellStyle()); //行驶证编码            if (null != vehicleInfoList.get(i).getAssuranceAmount()) {                createStyledCell(row, index++, String.valueOf(vehicleInfoList.get(i).getAssuranceAmount()), getStringCellStyle()); //保险金额            } else {                createStyledCell(row, index++, "", getStringCellStyle());            }            if (null != vehicleInfoList.get(i).getCreateBy()) { //创建人                createStyledCell(row, index++, vehicleInfoList.get(i).getCreateBy(), getStringCellStyle());            } else {                createStyledCell(row, index++, "", getStringCellStyle());            }            if (null != vehicleInfoList.get(i).getCreateDate()) { //创建时间                createStyledCell(row, index++, formatter.format(vehicleInfoList.get(i).getCreateDate()), getStringCellStyle());            } else {                createStyledCell(row, index++, "", getStringCellStyle());            }            if (null != vehicleInfoList.get(i).getUpdateBy()) {                createStyledCell(row, index++, vehicleInfoList.get(i).getUpdateBy(), getStringCellStyle()); //维护人            } else {                createStyledCell(row, index++, "", getStringCellStyle());            }            if (null != vehicleInfoList.get(i).getUpdateDate()) {                createStyledCell(row, index++, formatter.format(vehicleInfoList.get(i).getUpdateDate()), getStringCellStyle()); //维护时间            } else {                createStyledCell(row, index++, "", getStringCellStyle());            }            if ("0".equals(vehicleInfoList.get(i).getRunningStatus())) {                createStyledCell(row, index++, "冻结", getStringCellStyle());            } else {                createStyledCell(row, index++, "正常", getStringCellStyle());            }        }    }}