导出报表工具类

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
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.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
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.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Title: PoiExportExcel Description: 导出Excel公共方法
 * 
 * @date 2018年3月6日
 */
@SuppressWarnings("deprecation")
public class PoiExportExcel {

   public static final String excel2003 = "xls";

   public static final String excel2007 = "xlsx";

   // 显示的导出表的标题
   private String title;

   // 导出表的列名
   private String[] rowName;

   private List<Object[]> dataList = new ArrayList<Object[]>();

   public PoiExportExcel() {
   }

   // 构造方法,传入要导出的数据
   public PoiExportExcel(String title, String[] rowName, List<Object[]> dataList) {
      this.dataList = dataList;
      this.rowName = rowName;
      this.title = title;
   }

   /*
    * 导出数据
    */
   public void exportNoTitleRow(HttpServletResponse response, String excelType) throws Exception {
      try {

         Workbook workbook = null;
         if (StringUtils.equalsIgnoreCase(excel2003, excelType)) {
            workbook = new HSSFWorkbook();
         } else if (StringUtils.equalsIgnoreCase(excel2007, excelType)) {
            if (CollectionUtils.isNotEmpty(dataList) && dataList.size() > 50000) {
               workbook = new SXSSFWorkbook();
            } else {
               workbook = new XSSFWorkbook();
            }
         }

         // HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
         HSSFSheet sheet = (HSSFSheet) workbook.createSheet(title); // 创建工作表

         int rowIndex = 0;

         // 产生表格标题行
         /*
          * HSSFRow rowm = sheet.createRow(0); HSSFCell cellTiltle = rowm.createCell(0);
          * 
          * // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】 HSSFCellStyle
          * columnTopStyle = this.getColumnTopStyle(workbook);// 获取列头样式对象 HSSFCellStyle
          * style = this.getStyle(workbook); // 单元格样式对象
          * 
          * sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
          * cellTiltle.setCellStyle(columnTopStyle); cellTiltle.setCellValue(title);
          */

         CellStyle columnTopStyle = this.getColumnTopStyle(workbook, excelType);
         CellStyle style = this.getStyle(workbook, excelType);

         // 定义所需列数
         int columnNum = rowName.length;
         Row rowRowName = sheet.createRow(rowIndex++); // 在索引2的位置创建行(最顶端的行开始的第二行)

         // 将列头设置到sheet的单元格中
         for (int n = 0; n < columnNum; n++) {
            Cell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
            cellRowName.setCellType(Cell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
            RichTextString text = new HSSFRichTextString(rowName[n]);
            cellRowName.setCellValue(text); // 设置列头单元格的值
            cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
         }

         // 将查询出的数据设置到sheet对应的单元格中
         for (int i = 0; i < dataList.size(); i++) {
            Object[] obj = dataList.get(i);// 遍历每个对象
            Row row = sheet.createRow(rowIndex++);// 创建所需的行数
            for (int j = 0; j < obj.length; j++) {
               Cell cell = null; // 设置单元格的数据类型
               if (j == 0) {
                  cell = row.createCell(j, Cell.CELL_TYPE_NUMERIC);
                  cell.setCellValue(i + 1);
               } else {
                  cell = row.createCell(j, Cell.CELL_TYPE_STRING);
                  String value = "";
                  if (obj[j] != null)
                     value = obj[j].toString();
                  cell.setCellValue(value);
               }
               cell.setCellStyle(style); // 设置单元格样式
            }
         }

         // 让列宽随着导出的列长自动适应
         for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
               Row currentRow;
               // 当前行未被使用过
               if (sheet.getRow(rowNum) == null) {
                  currentRow = sheet.createRow(rowNum);
               } else {
                  currentRow = sheet.getRow(rowNum);
               }
               if (currentRow.getCell(colNum) != null) {
                  Cell currentCell = currentRow.getCell(colNum);
                  if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
                     int length = currentCell.getStringCellValue().getBytes().length;
                     if (columnWidth < length) {
                        columnWidth = length;
                     }
                  }
               }
            }
            if (colNum == 0) {
               sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
            } else {
               if ((columnWidth + 4) > 255)
                  sheet.setColumnWidth(colNum, 255 * 256);
               else
                  sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
            }
         }

         if (workbook != null) {
            try {
               SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
               String fileName = "order-data-" + dateFormat.format(new Date()) + ".xls";
               String headStr = "attachment; filename=\"" + fileName + "\"";
               response.setContentType("APPLICATION/OCTET-STREAM");
               response.setHeader("Content-Disposition", headStr);
               OutputStream out = response.getOutputStream();
               workbook.write(out);
               out.flush();
               out.close();
            } catch (IOException e) {
               e.printStackTrace();
            }
         }

      } catch (Exception e) {
         e.printStackTrace();
      }

   }

   /*
    * 导出数据
    */
   public void export(OutputStream os, String excelType) throws Exception {
      try {

         Workbook workbook = null;// 创建工作簿对象
         if (StringUtils.equalsIgnoreCase(excel2003, excelType)) {
            workbook = new HSSFWorkbook();
         } else if (StringUtils.equalsIgnoreCase(excel2007, excelType)) {
            if (CollectionUtils.isNotEmpty(dataList) && dataList.size() > 50000) {
               workbook = new SXSSFWorkbook();
            } else {
               workbook = new XSSFWorkbook();
            }
         }

         // HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
         Sheet sheet = workbook.createSheet(title); // 创建工作表

         // 产生表格标题行
         Row rowm = sheet.createRow(0);
         Cell cellTiltle = rowm.createCell(0);

         // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
         CellStyle columnTopStyle = this.getColumnTopStyle(workbook, excelType);// 获取列头样式对象
         CellStyle style = this.getStyle(workbook, excelType); // 单元格样式对象

         sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
         cellTiltle.setCellStyle(columnTopStyle);
         cellTiltle.setCellValue(title);

         // 定义所需列数
         int columnNum = rowName.length;
         Row rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)

         // 将列头设置到sheet的单元格中
         for (int n = 0; n < columnNum; n++) {
            Cell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
            cellRowName.setCellType(Cell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
            RichTextString text = null;
            if (StringUtils.equalsIgnoreCase(excel2003, excelType)) {
               text = new HSSFRichTextString(rowName[n]);
            } else if (StringUtils.equalsIgnoreCase(excel2007, excelType)) {
               text = new XSSFRichTextString(rowName[n]);
            }

            cellRowName.setCellValue(text); // 设置列头单元格的值
            cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
         }

         // 将查询出的数据设置到sheet对应的单元格中
         for (int i = 0; i < dataList.size(); i++) {

            Object[] obj = dataList.get(i);// 遍历每个对象
            Row row = sheet.createRow(i + 3);// 创建所需的行数

            for (int j = 0; j < obj.length; j++) {
               Cell cell = null; // 设置单元格的数据类型
               if (j == 0) {
                  cell = row.createCell(j, Cell.CELL_TYPE_NUMERIC);
                  cell.setCellValue(i + 1);
               } else {
                  cell = row.createCell(j, Cell.CELL_TYPE_STRING);
                  if (!"".equals(obj[j]) && obj[j] != null) {
                     cell.setCellValue(obj[j].toString()); // 设置单元格的值
                  } else {
                     cell.setCellValue("");
                  }
               }
               cell.setCellStyle(style); // 设置单元格样式
            }
         }
         // 让列宽随着导出的列长自动适应
         for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
               Row currentRow;
               // 当前行未被使用过
               if (sheet.getRow(rowNum) == null) {
                  currentRow = sheet.createRow(rowNum);
               } else {
                  currentRow = sheet.getRow(rowNum);
               }
               if (currentRow.getCell(colNum) != null) {
                  Cell currentCell = currentRow.getCell(colNum);
                  if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
                     int length = currentCell.getStringCellValue().getBytes().length;
                     if (columnWidth < length) {
                        columnWidth = length;
                     }
                  }
               }
            }
            if (colNum == 0) {
               columnWidth = (columnWidth - 2) * 128;
            } else {
               columnWidth = (columnWidth + 4) * 256;
            }
            sheet.setColumnWidth(colNum, Math.min(columnWidth, 255 * 256));
         }

         if (workbook != null) {
            try {
               workbook.write(os);
               os.flush();
               os.close();
            } catch (IOException e) {
               e.printStackTrace();
            }
         }

      } catch (Exception e) {
         e.printStackTrace();
      }

   }

   /**
    * 结算单导出数据(结算收款单,结算付款单)
    * 
    * @param header
    *            数据项
    * @param rowName
    *            列名
    * @param orderList
    *            数据项列表
    * @param otherList
    * @param type
    *            结算单类型proceeds,settlement
    */
   public void exportSettlement(Map<String, String> header, String[] rowName, List<Object[]> orderList, List<Object[]> otherList, HttpServletResponse response, String type, String excelType) throws Exception {
      try {
         HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
         HSSFSheet sheet = workbook.createSheet(header.get("title")); // 创建工作表

         CellStyle columnTopStyle = this.getColumnTopStyle(workbook, excelType);// 获取列头样式对象
         CellStyle style = this.getStyle(workbook, excelType); // 单元格样式对象

         // 创建标题
         Row rowm = sheet.createRow(0);
         Cell cellTiltle = rowm.createCell(0);
         sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, rowName.length - 1));
         cellTiltle.setCellStyle(columnTopStyle);
         cellTiltle.setCellValue(header.get("title"));

         // 创建副标题
         if ("proceeds".equals(type)) {
            rowm = sheet.createRow(2);
            cellTiltle = rowm.createCell(0);
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 2));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue("收款单单号:" + header.get("settlementNo"));

            cellTiltle = rowm.createCell(3);
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 4));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue("客户:" + header.get("customer"));

            cellTiltle = rowm.createCell(5);
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 5, rowName.length - 1));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue("制单日期:" + header.get("createTime"));
         } else if ("settlement".equals(type)) {
            rowm = sheet.createRow(2);
            cellTiltle = rowm.createCell(0);
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 2));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue("付款单单号:" + header.get("settlementNo"));

            cellTiltle = rowm.createCell(3);
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 5));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue("收款人:" + header.get("payee"));

            cellTiltle = rowm.createCell(6);
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, rowName.length - 1));
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue("制单日期:" + header.get("createTime"));
         }

         // 定义所需列数
         int columnNum = rowName.length;
         Row rowRowName = sheet.createRow(4); // 在索引4的位置创建行(最顶端的行开始的第四行)

         // 将列头设置到sheet的单元格中
         for (int n = 0; n < columnNum; n++) {
            Cell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
            cellRowName.setCellType(Cell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
            RichTextString text = null;
            if (StringUtils.equalsIgnoreCase(excel2003, excelType)) {
               text = new HSSFRichTextString(rowName[n]);
            } else if (StringUtils.equalsIgnoreCase(excel2007, excelType)) {
               text = new XSSFRichTextString(rowName[n]);
            }
            cellRowName.setCellValue(text); // 设置列头单元格的值
            cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
         }

         // 遍历数据内容
         for (int i = 0; i < orderList.size(); i++) {
            Object[] obj = orderList.get(i);
            HSSFRow row = sheet.createRow(i + 5);
            for (int j = 0; j < obj.length; j++) {
               HSSFCell cell = null;
               cell = row.createCell(j, Cell.CELL_TYPE_STRING);
               if (!"".equals(obj[j]) && obj[j] != null) {
                  cell.setCellValue(obj[j].toString());
               } else {
                  cell.setCellValue("");
               }
               cell.setCellStyle(style);
            }
         }
         // 遍历其他内容
         for (int i = 0; i < otherList.size(); i++) {
            Object[] obj = otherList.get(i);// 遍历每个对象
            HSSFRow row = sheet.createRow(orderList.size() + i + 5);// 创建所需的行数
            for (int j = 0; j < obj.length; j++) {
               HSSFCell cell = null; // 设置单元格的数据类型
               cell = row.createCell(j, Cell.CELL_TYPE_STRING);
               if (!"".equals(obj[j]) && obj[j] != null) {
                  cell.setCellValue(obj[j].toString()); // 设置单元格的值
               } else {
                  cell.setCellValue("");
               }
               if (i != 0 && i != otherList.size() - 1) {
                  cell.setCellStyle(style); // 设置单元格样式
               } else {
                  cell.setCellStyle(columnTopStyle); // 设置单元格样式
               }
            }
         }
         // 让列宽随着导出的列长自动适应(行号从第4行开始遍历)
         for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 4; rowNum < sheet.getLastRowNum(); rowNum++) {
               HSSFRow currentRow;
               // 当前行未被使用过
               if (sheet.getRow(rowNum) == null) {
                  currentRow = sheet.createRow(rowNum);
               } else {
                  currentRow = sheet.getRow(rowNum);
               }
               if (currentRow.getCell(colNum) != null) {
                  HSSFCell currentCell = currentRow.getCell(colNum);
                  if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
                     int length = currentCell.getStringCellValue().getBytes().length;
                     if (columnWidth < length)
                        columnWidth = length;
                  }
               }
            }
            if (colNum == 0) {
               sheet.setColumnWidth(colNum, (columnWidth + 2) * 256);
            } else {
               sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
            }
         }
         if (workbook != null) {
            try {
               SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
               String fileName = dateFormat.format(new Date()) + ".xls";
               String headStr = "attachment; filename=\"" + fileName + "\"";
               response.setContentType("APPLICATION/OCTET-STREAM");
               response.setHeader("Content-Disposition", headStr);
               OutputStream out = response.getOutputStream();
               workbook.write(out);
               out.flush();
               out.close();
            } catch (IOException e) {
               e.printStackTrace();
            }
         }
      } catch (Exception e) {
         e.printStackTrace();
      }
   }

   /*
    * 列头单元格样式
    */
   public CellStyle getColumnTopStyle(Workbook workbook, String excelType) {

      // 设置字体
      Font font = workbook.createFont();
      // 设置字体大小
      font.setFontHeightInPoints((short) 11);
      // 字体加粗
      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
      // 设置字体名字
      font.setFontName("Courier New");
      // 设置样式;
      CellStyle style = workbook.createCellStyle();
      // // 设置底边框;
      // style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      // // 设置底边框颜色;
      // style.setBottomBorderColor(HSSFColor.BLACK.index);
      // // 设置左边框;
      // style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      // // 设置左边框颜色;
      // style.setLeftBorderColor(HSSFColor.BLACK.index);
      // // 设置右边框;
      // style.setBorderRight(HSSFCellStyle.BORDER_THIN);
      // // 设置右边框颜色;
      // style.setRightBorderColor(HSSFColor.BLACK.index);
      // // 设置顶边框;
      // style.setBorderTop(HSSFCellStyle.BORDER_THIN);
      // // 设置顶边框颜色;
      // style.setTopBorderColor(HSSFColor.BLACK.index);
      // 在样式用应用设置的字体;
      style.setFont(font);
      // 设置自动换行;
      style.setWrapText(false);
      if (StringUtils.equalsIgnoreCase(excel2003, excelType)) {
         // 设置水平对齐的样式为居中对齐;
         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
         // 设置垂直对齐的样式为居中对齐;
         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      } else if (StringUtils.equalsIgnoreCase(excel2007, excelType)) {
         // 设置水平对齐的样式为居中对齐;
         style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
         // 设置垂直对齐的样式为居中对齐;
         style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
      }

      return style;

   }

   /*
    * 列数据信息单元格样式
    */
   public CellStyle getStyle(Workbook workbook, String excelType) {
      // 设置字体
      Font font = workbook.createFont();
      // 设置字体大小
      // font.setFontHeightInPoints((short)10);
      // 字体加粗
      // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
      // 设置字体名字
      font.setFontName("Courier New");
      // 设置样式;
      CellStyle style = workbook.createCellStyle();
      // 设置底边框;
      // style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      // 设置底边框颜色;
      // style.setBottomBorderColor(HSSFColor.BLACK.index);
      // 设置左边框;
      // style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      // 设置左边框颜色;
      // style.setLeftBorderColor(HSSFColor.BLACK.index);
      // 设置右边框;
      // style.setBorderRight(HSSFCellStyle.BORDER_THIN);
      // 设置右边框颜色;
      // style.setRightBorderColor(HSSFColor.BLACK.index);
      // 设置顶边框;
      // style.setBorderTop(HSSFCellStyle.BORDER_THIN);
      // 设置顶边框颜色;
      // style.setTopBorderColor(HSSFColor.BLACK.index);
      // 在样式用应用设置的字体;
      style.setFont(font);
      // 设置自动换行;
      style.setWrapText(false);
      if (StringUtils.equalsIgnoreCase(excel2003, excelType)) {
         // 设置水平对齐的样式为居中对齐;
         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
         // 设置垂直对齐的样式为居中对齐;
         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      } else if (StringUtils.equalsIgnoreCase(excel2007, excelType)) {
         // 设置水平对齐的样式为居中对齐;
         style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
         // 设置垂直对齐的样式为居中对齐;
         style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
      }

      return style;

   }

   /*
    * 导出数据
    */
   public void export(HttpServletResponse response, String fileName, String flag, String excelType) throws Exception {
      try {
         HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
         HSSFSheet sheet = workbook.createSheet(title); // 创建工作表

         int rowIndex = 0;

         CellStyle columnTopStyle = this.getColumnTopStyle(workbook, excelType);
         CellStyle style = this.getStyle(workbook, excelType);

         // 定义所需列数
         int columnNum = rowName.length;
         Row rowRowName = sheet.createRow(rowIndex++); // 在索引2的位置创建行(最顶端的行开始的第二行)

         // 将列头设置到sheet的单元格中
         for (int n = 0; n < columnNum; n++) {
            Cell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
            cellRowName.setCellType(Cell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
            RichTextString text = new HSSFRichTextString(rowName[n]);
            cellRowName.setCellValue(text); // 设置列头单元格的值
            cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
            // 设置列自适应宽度
            sheet.setColumnWidth(n, text.getString().getBytes().length * 2 * 256);
         }

         // 将查询出的数据设置到sheet对应的单元格中
         for (int i = 0; i < dataList.size(); i++) {
            Object[] obj = dataList.get(i);// 遍历每个对象
            HSSFRow row = sheet.createRow(rowIndex++);// 创建所需的行数
            for (int j = 0; j < obj.length; j++) {
               HSSFCell cell = null; // 设置单元格的数据类型
               if (j == 0) {
                  cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                  cell.setCellValue(i + 1);
               } else {
                  cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                  String value = "";
                  if (obj[j] != null)
                     value = obj[j].toString();
                  cell.setCellValue(value);
               }
               cell.setCellStyle(style); // 设置单元格样式
            }
         }

         // HSSFRow rowTitle = sheet.createRow(rowIndex++);
         // HSSFCell cellTiltle = rowTitle.createCell(0);
         // sheet.addMergedRegion(new CellRangeAddress(rowIndex-1,
         // rowIndex-1, 0, rowName.length-1));
         // cellTiltle.setCellValue(title);

         if (workbook != null) {
            try {
               SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
               String fname = fileName + dateFormat.format(new Date()) + ".xls";
               String headStr = "attachment; filename=\"" + fname + "\"";
               response.setContentType("APPLICATION/OCTET-STREAM");
               response.setHeader("Content-Disposition", headStr);
               OutputStream out = response.getOutputStream();
               workbook.write(out);
               out.flush();
               out.close();
            } catch (IOException e) {
               e.printStackTrace();
            }
         }

      } catch (Exception e) {
         e.printStackTrace();
      }

   }

   /*
    * 导出数据
    */
   @SuppressWarnings("unchecked")
   public void businessExport(HttpServletResponse response, String excelType) throws Exception {
      try {
         HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
         HSSFSheet sheet = workbook.createSheet(title); // 创建工作表

         // 产生表格标题行
         HSSFRow rowm = sheet.createRow(0);
         HSSFCell cellTiltle = rowm.createCell(0);

         // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
         CellStyle columnTopStyle = this.getColumnTopStyle(workbook, excelType);// 获取列头样式对象
         CellStyle style = this.getStyle(workbook, excelType); // 单元格样式对象

         sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
         cellTiltle.setCellStyle(columnTopStyle);
         cellTiltle.setCellValue(title);

         // 定义所需列数
         int columnNum = rowName.length;
         Row rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)

         // 将列头设置到sheet的单元格中
         for (int n = 0; n < columnNum; n++) {
            Cell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
            cellRowName.setCellType(Cell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
            RichTextString text = null;
            if (StringUtils.equalsIgnoreCase(excel2003, excelType)) {
               text = new HSSFRichTextString(rowName[n]);
            } else if (StringUtils.equalsIgnoreCase(excel2007, excelType)) {
               text = new XSSFRichTextString(rowName[n]);
            }
            cellRowName.setCellValue(text); // 设置列头单元格的值
            cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
         }

         int mergeNum = 3;
         int createNum = 3;
         for (int i = 0; i < dataList.size(); i++) {
            Object[] obj = dataList.get(i);
            List<Object[]> detailRows = (List<Object[]>) obj[obj.length - 1];
            HSSFRow row = null;
            for (int k = 0; k < detailRows.size(); k++) {
               row = sheet.createRow(k + createNum);
               Object[] detailObj = (Object[]) detailRows.get(k);
               HSSFCell cell = null; // 设置单元格的数据类型
               for (int j = 0; j < detailObj.length; j++) {
                  cell = row.createCell(12 + j, Cell.CELL_TYPE_STRING);
                  if (detailObj[j] != null && !"".equals(detailObj[j])) {
                     cell.setCellValue(detailObj[j].toString()); // 设置单元格的值
                  } else {
                     cell.setCellValue("");
                  }
               }
               cell.setCellStyle(style); // 设置单元格样式
            }

            createNum = createNum + detailRows.size();
            mergeNum = mergeNum + detailRows.size();

            // 合并行
            for (int j = 0; j < obj.length - 1; j++) {
               HSSFCell cell = null; // 设置单元格的数据类型
               // 合并单元格 row行 col列
               if (detailRows.size() == 1) {
                  row = sheet.getRow(sheet.getLastRowNum());
               } else {
                  sheet.addMergedRegion(new CellRangeAddress(sheet.getLastRowNum() - detailRows.size() + 1, sheet.getLastRowNum(), j, j));
                  row = sheet.getRow(sheet.getLastRowNum() - detailRows.size() + 1);
               }
               if (j == 0) {
                  cell = row.createCell(j, Cell.CELL_TYPE_NUMERIC);
                  cell.setCellValue(i + 1);
               } else {
                  cell = row.createCell(j, Cell.CELL_TYPE_STRING);
                  if (obj[j] != null && !"".equals(obj[j])) {
                     cell.setCellValue(obj[j].toString()); // 设置单元格的值
                  } else {
                     cell.setCellValue("");
                  }
               }
               cell.setCellStyle(style); // 设置单元格样式
            }

         }

         // 让列宽随着导出的列长自动适应
         for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
               HSSFRow currentRow;
               // 当前行未被使用过
               if (sheet.getRow(rowNum) == null) {
                  currentRow = sheet.createRow(rowNum);
               } else {
                  currentRow = sheet.getRow(rowNum);
               }
               if (currentRow.getCell(colNum) != null) {
                  HSSFCell currentCell = currentRow.getCell(colNum);
                  if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
                     int length = currentCell.getStringCellValue().getBytes().length;
                     if (columnWidth < length) {
                        columnWidth = length;
                     }
                  }
               }
            }
            if (colNum == 0) {
               sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
            } else {
               sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
            }
         }

         if (workbook != null) {
            try {
               SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
               String fileName = "channelbusiness_" + dateFormat.format(new Date()) + ".xls";
               String headStr = "attachment; filename=\"" + fileName + "\"";
               response.setContentType("APPLICATION/OCTET-STREAM");
               response.setHeader("Content-Disposition", headStr);
               OutputStream out = response.getOutputStream();
               workbook.write(out);
               out.flush();
               out.close();
            } catch (IOException e) {
               e.printStackTrace();
            }
         }

      } catch (Exception e) {
         e.printStackTrace();
      }

   }

   /*
    * 导出数据
    */
   public void exportFlowReport(HttpServletResponse response, String fileName, String appendData, String excelType) throws Exception {
      try {
         HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
         HSSFSheet sheet = workbook.createSheet(title); // 创建工作表

         // 产生表格标题行
         HSSFRow rowm = sheet.createRow(0);
         HSSFCell cellTiltle = rowm.createCell(0);

         // sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
         CellStyle columnTopStyle = this.getColumnTopStyle(workbook, excelType);// 获取列头样式对象
         CellStyle style = this.getStyle(workbook, excelType); // 单元格样式对象

         sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
         cellTiltle.setCellStyle(columnTopStyle);
         cellTiltle.setCellValue(title);

         // 定义所需列数
         int columnNum = rowName.length;
         Row rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)

         // 将列头设置到sheet的单元格中
         for (int n = 0; n < columnNum; n++) {
            Cell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
            cellRowName.setCellType(Cell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
            HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
            cellRowName.setCellValue(text); // 设置列头单元格的值
            cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
         }

         // 将查询出的数据设置到sheet对应的单元格中
         for (int i = 0; i < dataList.size(); i++) {

            Object[] obj = dataList.get(i);// 遍历每个对象
            HSSFRow row = sheet.createRow(i + 3);// 创建所需的行数

            for (int j = 0; j < obj.length; j++) {
               HSSFCell cell = null; // 设置单元格的数据类型
               if (j == 0) {
                  cell = row.createCell(j, Cell.CELL_TYPE_NUMERIC);
                  cell.setCellValue(i + 1);
               } else {
                  cell = row.createCell(j, Cell.CELL_TYPE_STRING);
                  if (!"".equals(obj[j]) && obj[j] != null) {
                     cell.setCellValue(obj[j].toString()); // 设置单元格的值
                  } else {
                     cell.setCellValue("");
                  }
               }
               cell.setCellStyle(style); // 设置单元格样式
            }
         }

         // 追加一行
         int lastRow = dataList.size() + 3;
         HSSFRow appendRow = sheet.createRow(lastRow);
         HSSFCell appendCell = appendRow.createCell(0, Cell.CELL_TYPE_STRING);
         sheet.addMergedRegion(new CellRangeAddress(lastRow, lastRow + 1, 0, (rowName.length - 1)));
         appendCell.setCellValue(appendData);
         style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
         appendCell.setCellStyle(style); // 设置单元格样式

         // 让列宽随着导出的列长自动适应
         for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
               HSSFRow currentRow;
               // 当前行未被使用过
               if (sheet.getRow(rowNum) == null) {
                  currentRow = sheet.createRow(rowNum);
               } else {
                  currentRow = sheet.getRow(rowNum);
               }
               if (currentRow.getCell(colNum) != null) {
                  HSSFCell currentCell = currentRow.getCell(colNum);
                  if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
                     int length = currentCell.getStringCellValue().getBytes().length;
                     if (columnWidth < length) {
                        columnWidth = length;
                     }
                  }
               }
            }
            if (colNum == 0) {
               columnWidth = (columnWidth - 2) * 256;
            } else {
               columnWidth = (columnWidth + 4) * 256;
            }
            sheet.setColumnWidth(colNum, Math.min(columnWidth, 255 * 256));
         }

         if (workbook != null) {
            try {
               SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
               String fileName1 = fileName + dateFormat.format(new Date()) + ".xls";
               String headStr = "attachment; filename=\"" + fileName1 + "\"";
               response.setContentType("APPLICATION/OCTET-STREAM; charset=UTF-8");
               response.setHeader("Content-Disposition", headStr);
               OutputStream out = response.getOutputStream();
               workbook.write(out);
               out.flush();
               out.close();
            } catch (IOException e) {
               e.printStackTrace();
            }
         }

      } catch (Exception e) {
         e.printStackTrace();
      }

   }

}
public void exportFeedbackDataToExcel(Map<String, Object> condition, OutputStream os) {
   List<FeedbackReportVO> reportList = this.queryFeedbackReport(condition);
   int total = this.countFeedbackReport(condition);
   String[] rowsName = new String[] { "序号", "会员卡号", "注册手机号", "反馈类型", "反馈时间", "反馈描述","留下的联系手机号","会员OpenID" };
   List<Object[]> dataList = new ArrayList<Object[]>();
   Object[] objs = null;
   int idx = 1;
   if (CollectionUtils.isNotEmpty(reportList)) {
      for (FeedbackReportVO report : reportList) {
         objs = new Object[rowsName.length];
         objs[0] = idx;
         objs[1] = report.getCardNo();
         objs[2] = report.getRegisterMobile();
         objs[3] = report.getFeedbackType();
         objs[4] = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(report.getFeedbackTime());
         objs[5] = report.getFeedbackContent();
         objs[6] = report.getMobile();
         objs[7] = report.getOpenId();
         dataList.add(objs);
         idx++;
      }
   }
   int no = idx - 1;
   PoiExportExcel poiExportExcel = new PoiExportExcel("会员意见反馈记录条数总计(" + total + "),实际导出条数(" + no + ")", rowsName, dataList);

   try {
      poiExportExcel.export(os, PoiExportExcel.excel2007);
   } catch (Exception e) {
      e.printStackTrace();
   }  
}

举个例子
public void exportFeedbackDataToExcel(Map<String, Object> condition, OutputStream os) {
   List<FeedbackReportVO> reportList = this.queryFeedbackReport(condition);
   int total = this.countFeedbackReport(condition);
   String[] rowsName = new String[] { "序号", "会员卡号", "注册手机号", "反馈类型", "反馈时间", "反馈描述","留下的联系手机号","会员OpenID" };
   List<Object[]> dataList = new ArrayList<Object[]>();
   Object[] objs = null;
   int idx = 1;
   if (CollectionUtils.isNotEmpty(reportList)) {
      for (FeedbackReportVO report : reportList) {
         objs = new Object[rowsName.length];
         objs[0] = idx;
         objs[1] = report.getCardNo();
         objs[2] = report.getRegisterMobile();
         objs[3] = report.getFeedbackType();
         objs[4] = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(report.getFeedbackTime());
         objs[5] = report.getFeedbackContent();
         objs[6] = report.getMobile();
         objs[7] = report.getOpenId();
         dataList.add(objs);
         idx++;
      }
   }
   int no = idx - 1;
   PoiExportExcel poiExportExcel = new PoiExportExcel("会员意见反馈记录条数总计(" + total + "),实际导出条数(" + no + ")", rowsName, dataList);

   try {
      poiExportExcel.export(os, PoiExportExcel.excel2007);
   } catch (Exception e) {
      e.printStackTrace();
   }  
}

前端页面

<div class="columns pull-right col-md-2 " style="width: 140px; margin-right: 12px;">
   <button type="button" style="background-color: #7ED321; border-color: #7ED321;" class="btn btn-warning" onclick="exportFeedbackDataToExcel();">
         <i class="fa fa-file-excel-o"></i> 导出当前条件数据
   </button>
</div>

Js

function exportFeedbackDataToExcel() {
   window.top.layer.open({
      content : '确认导出条件反馈记录数据?',
      btn : [ '确认', '取消' ],
      closeBtn : false,
      shadeClose : true,
      yes : function(index, layer) {
         var url = basePath + 'feedback/exportfeedbackdatatoexcel.html?registerMobile=' + $('#registerMobile').val() + "&cardNo="
                     + $("#cardNo").val() + "&typeId=" + $("#typeId").val() + "&beginTime="
                     + $("#beginTime").val() + "&endTime=" + $("#endTime").val();
         window.location.href = url;
         window.parent.layer.close(index);
      }
   });
}

阅读更多 登录后自动展开
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页