POIexcel导出案例

Service导出方法代码为：

/**
     * 导出excel表头和数据处理并下载
     * @param path
     * @param paramMap
     */
    public void downloadExcelReport(String path, Map<String,String> paramMap,HttpServletResponse response) {
        //获取paramMap的value，根据这些参数查询出对应的报表数据集合reportList
        String  pDateTime =  paramMap.get("pDateTime");
        String  channelCode=  paramMap.get("channelCode");
        String  operater=  paramMap.get("operater");
        //获取数据集合这里不做具体阐述
        List<ReportQueryEntity> reportList = this.execute(pDateTime, channelCode,operater);
        //中国银联来款汇总表大表头（标题）
        String bigHeaderTilte="中国XX来款汇总表";
        //工作薄对象
        SXSSFWorkbook wb= new SXSSFWorkbook(500);//内存中保留 500 条数据，以免内存溢出，其余写入 硬盘
        //工作表对象
        Sheet sheet = wb.createSheet("Export"); //在webbook中添加一个sheet,对应Excel文件中的sheet
        
        //样式列表
        ExportExcelNewUtils utils= new ExportExcelNewUtils();
        Map<String, CellStyle> styles = utils.createStyles(wb);
        //当前行号
         int rownum = 0;
        // Create title
        if (StringUtils.isNotBlank(bigHeaderTilte)) {
            Row titleRow = sheet.createRow(0);//标题行
            titleRow.setHeightInPoints(30);// 设置标题的高度
            Cell titleCell = titleRow.createCell(0);//创建标题的单元格
            titleCell.setCellStyle(styles.get("title"));//设置单元格样式
            titleCell.setCellValue(bigHeaderTilte);//塞值
            //单元格合并：下标从0开始sheet.addMergedRegion(new CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) 
            sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
                    titleRow.getRowNum(), titleRow.getRowNum(), 8));
            
        }
        /**新增行：显示清算机构，清算币种*/
        sheet.addMergedRegion(new CellRangeAddress(1,1, 1, 5));//清算机构值:合并单元格
        Row settHeadRow = sheet.createRow(1);
        settHeadRow.setHeightInPoints(16);
        Cell settCellFirst = settHeadRow.createCell(0);
        settCellFirst.setCellStyle(styles.get("data"));
        settCellFirst.setCellValue("清算机构：");
        //清算机构值
        Cell settCellSec = settHeadRow.createCell(1);
        settCellSec.setCellStyle(styles.get("data"));
        if(CollectionUtils.isNotEmpty(reportList)){
            settCellSec.setCellValue(reportList.get((reportList.size()-1)).getData9());
        }
        //创建空单元格：为防止单元格合并后边框消失，每个单元格都必须创建，否则只有创建的那个单元格有边框，不创建的单元格没边框
        for (int j = 2; j <= 5; j++) {
            Cell emptyCel = settHeadRow.createCell(j);
            emptyCel.setCellStyle(styles.get("data")); //style为带边框的样式 上面有定义
            emptyCel.setCellValue("");
        }
        Cell settCellThird = settHeadRow.createCell(6);
        settCellThird.setCellStyle(styles.get("data"));
        settCellThird.setCellValue("清算币种：");
        Cell settCellFourth = settHeadRow.createCell(7);
        settCellFourth.setCellStyle(styles.get("data"));
        settCellFourth.setCellValue("人民币");
        /**新增行：显示清算日期，生成日期*/
        sheet.addMergedRegion(new CellRangeAddress(2,2, 1, 5));//清算日期值：合并单元格
        Row dateHeadRow = sheet.createRow(2);
        dateHeadRow.setHeightInPoints(16);
        Cell dateCellFirst = dateHeadRow.createCell(0);
        dateCellFirst.setCellStyle(styles.get("data"));
        dateCellFirst.setCellValue("清算日期：：");
        Cell cellSec = dateHeadRow.createCell(1);
        cellSec.setCellStyle(styles.get("data"));
        if(CollectionUtils.isNotEmpty(reportList)){
            cellSec.setCellValue(reportList.get((reportList.size()-1)).getData10());
        }
        //创建空单元格：为防止单元格合并后边框消失，每个单元格都必须创建，否则只有创建的那个单元格有边框，不创建的单元格没边框
        for (int j = 2; j <= 5; j++) {
            Cell emptyCel = dateHeadRow.createCell(j);
            emptyCel.setCellStyle(styles.get("data")); //style为带边框的样式 上面有定义
            emptyCel.setCellValue("");
        }
        Cell cellThird = dateHeadRow.createCell(6);
        cellThird.setCellStyle(styles.get("data"));
        cellThird.setCellValue("生成日期：");
        Cell cellFourth = dateHeadRow.createCell(7);
        cellFourth.setCellStyle(styles.get("data"));
        if(CollectionUtils.isNotEmpty(reportList)){
            cellFourth.setCellValue(reportList.get((reportList.size()-1)).getData11());
        }
        //交易范围 单独一行
        sheet.addMergedRegion(new CellRangeAddress(3,3, 0, 7));
        Row otherHeadRow = sheet.createRow(3);
        otherHeadRow.setHeightInPoints(16);
        Cell otherCellFirst = otherHeadRow.createCell(0);
        otherCellFirst.setCellStyle(styles.get("data"));
        otherCellFirst.setCellValue("交易范围：");
        //创建空单元格：为防止单元格合并后边框消失，每个单元格都必须创建，否则只有创建的那个单元格有边框，不创建的单元格没边框
        for (int j = 1; j <= 7; j++) {
            Cell emptyCel = otherHeadRow.createCell(j);
            emptyCel.setCellStyle(styles.get("data")); //style为带边框的样式 上面有定义
            emptyCel.setCellValue("");
        }
        //跨两行标题
        String[] header_2={"交易类型","交易笔数"};
        //跨列标题，跨列标题对应列数
        String[] header_cate = {"交易金额","费用","资金清算差额"};
        int[] cate_num ={2,2,2};
        //小标题（不跨行不跨列）
        String[] header_1 = {"借","贷","借","贷","借","贷"};

        for(int i=0; i<header_2.length; i++){
            sheet.addMergedRegion(new CellRangeAddress(4, 5, i, i));//合并4-5行
        }
        //合并列
        int sum1 = 0;
        int sum2 = 0;
        for(int i=0; i<header_cate.length; i++){
            sum1 += cate_num[i];
            sheet.addMergedRegion(new CellRangeAddress(4, 4, 2+sum2, 2+sum1-1));
            sum2 += cate_num[i];
        }
        //创建第5行并塞值
        Row row = sheet.createRow(4);
        for(int i=0; i<header_2.length; i++){
            final Cell cell = row.createCell(i);//第1,2列（index下标：0,1）
            cell.setCellStyle(styles.get("data"));    
            cell.setCellValue(header_2[i]);
            int colWidth = sheet.getColumnWidth(i) * 2;
            sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
        }
        int sum = 0;
        for(int i=0; i<header_cate.length; i++){
            final Cell cell = row.createCell(2+sum);//第3,5,7列（下标：2,4,6）
            cell.setCellStyle(styles.get("data"));
            cell.setCellValue(header_cate[i]);
            sum += cate_num[i];
        }
        //创建空单元格(第4,6,8列(下标：3,5,7))：为防止单元格合并后边框消失，每个单元格都必须创建，否则只有创建的那个单元格有边框，不创建的单元格没边框
        for (int j = 3; j <= 7; j=j+2) {
            Cell emptyCel = row.createCell(j);
            emptyCel.setCellStyle(styles.get("data")); //style为带边框的样式 上面有定义
            emptyCel.setCellValue("");
        }
        //创建第6行并塞值
        row = sheet.createRow(5);
        //创建空单元格(第1,2列(下标：0,1))：为防止单元格合并后边框消失，每个单元格都必须创建，否则只有创建的那个单元格有边框，不创建的单元格没边框
        for (int j = 0; j <= 1; j++) {
            Cell emptyCel = row.createCell(j);
            emptyCel.setCellStyle(styles.get("data")); //style为带边框的样式 上面有定义
            emptyCel.setCellValue("");
        }
        for(int i=0; i<header_1.length; i++){
            final Cell cell = row.createCell(i+2);    //第3-8列（下标：2-7）
            cell.setCellStyle(styles.get("data"));
            cell.setCellValue(header_1[i]);
            int colWidth = sheet.getColumnWidth(i+2) * 2;
            sheet.setColumnWidth(i+2, colWidth < 3000 ? 3000 : colWidth);
        }
        
        
        List<List<String>> dataList = new ArrayList<>();
        for (int i = 0; i < reportList.size(); i++) {
            List<String> list = new ArrayList<>();
            list.add(reportList.get(i).getData1());
            list.add(reportList.get(i).getData2());
            list.add(reportList.get(i).getData3());
            list.add(reportList.get(i).getData4());
            list.add(reportList.get(i).getData5());
            list.add(reportList.get(i).getData6());
            list.add(reportList.get(i).getData7());
            list.add(reportList.get(i).getData8());
            dataList.add(list);
        }
        
        
        //遍历报表数据,每行每行塞入excel表格
        for (int i = 0; i < dataList.size(); i++) {
            //新增excel行
            Row rowData = sheet.createRow(6+i);
            for (int j = 0; j < dataList.get(i).size(); j++) {
//                utils.addCell(row, j, dataList.get(i).get(j));
                utils.addCellNew(rowData, j, dataList.get(i).get(j), 0, Class.class, styles, wb);
            }
        }
        
        //报表名称
        String reportName = "中国XX来款汇总表"+pDateTime;
        //下载
        try {        
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + java.net.URLEncoder.encode(reportName+ ".xls", "UTF-8"));
            OutputStream out = response.getOutputStream();
            wb.write(out);//写出文件
            
            out.flush();
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

其中：第一：ExportExcelNewUtils代码为：

/**
 * 
 */
package kklazy.utils;

import java.util.Date;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.LoggerFactory;

/**
 * 
 * 导出excel工具类
 *
 */
public class ExportExcelNewUtils extends  ExportExcelUtils{
    private static org.slf4j.Logger log = LoggerFactory.getLogger(ExportExcelUtils.class);
    
    /**
     * 
     */
    public ExportExcelNewUtils() {
        
    }
    
    /**
     * 添加一个单元格
     * 
     * @param row
     *            添加的行
     * @param column
     *            添加列号
     * @param val
     *            添加值
     * @param align
     *            对齐方式（1：靠左；2：居中；3：靠右）
     * @return 单元格对象
     */
    public Cell addCellNew(Row row, int column, Object val, int align,
            Class<?> fieldType,Map<String, CellStyle> styles,SXSSFWorkbook wb) {
        Cell cell = row.createCell(column);
        /*CellStyle style = styles.get("data"
                + (align >= 1 && align <= 3 ? align : ""));*/
        CellStyle style = styles.get("header");
        try {
            if (val == null) {
                cell.setCellValue("");
            } else if (val instanceof String) {
                cell.setCellValue((String) val);
            } else if (val instanceof Integer) {
                cell.setCellValue((Integer) val);
            } else if (val instanceof Long) {
                cell.setCellValue((Long) val);
            } else if (val instanceof Double) {
                cell.setCellValue((Double) val);
            } else if (val instanceof Float) {
                cell.setCellValue((Float) val);
            } else if (val instanceof Date) {
                DataFormat format = wb.createDataFormat();
                style.setDataFormat(format.getFormat("yyyy-MM-dd"));
                cell.setCellValue((Date) val);
            } else {
                if (fieldType != Class.class) {
                    cell.setCellValue((String) fieldType.getMethod("setValue",
                            Object.class).invoke(null, val));
                } else {
                    cell.setCellValue((String) Class
                            .forName(
                                    this.getClass()
                                            .getName()
                                            .replaceAll(
                                                    this.getClass()
                                                            .getSimpleName(),
                                                    "fieldtype."
                                                            + val.getClass()
                                                                    .getSimpleName()
                                                            + "Type"))
                            .getMethod("setValue", Object.class)
                            .invoke(null, val));
                }
            }
        } catch (Exception ex) {
            log.info("Set cell value [" + row.getRowNum() + "," + column
                    + "] error: " + ex.toString());
            cell.setCellValue(val.toString());
        }
        cell.setCellStyle(style);
        return cell;
    }
    
}

第二:ExportExcelUtils.createStyles()创建表格样式代码为：

/**
     * 创建表格样式
     *
     * @param wb
     *            工作薄对象
     * @return 样式列表
     */
    public Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

        CellStyle style = wb.createCellStyle();//设置样式
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        Font titleFont = wb.createFont();// 设置字体
        titleFont.setFontName("Arial");//设置字体名字 
        titleFont.setFontHeightInPoints((short) 16);//设置字体大小
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);//设置字体加粗
        style.setFont(titleFont);
        styles.put("title", style);

        style = wb.createCellStyle();
        //style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐; 
        style.setBorderRight(CellStyle.BORDER_THIN);//设置右边框; 
        //style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);//设置左边框; 
        //style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN); //设置顶边框; 
        //style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);//设置底边框; 
        //style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));//克隆样式
        style.setAlignment(CellStyle.ALIGN_LEFT);//设置水平对齐的样式为居中对齐;
        styles.put("data1", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        styles.put("data2", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        styles.put("data3", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setWrapText(true);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        return styles;
    }

第三：为防止合并单元格，但合并后边框消失：涉及到的单元格都需要被create，否则 没有被创建的单元格的边框不显示：具体见代码

最后：导出excel效果如下：

我的老腰呀，今天暂时告一段落，over。