POIexcel导出案例

Service导出方法代码为：

  1 /**
  2      * 导出excel表头和数据处理并下载
  3      * @param path
  4      * @param paramMap
  5      */
  6     public void downloadExcelReport(String path, Map<String,String> paramMap,HttpServletResponse response) {
  7         //获取paramMap的value，根据这些参数查询出对应的报表数据集合reportList
  8         String  pDateTime =  paramMap.get("pDateTime");
  9         String  channelCode=  paramMap.get("channelCode");
 10         String  operater=  paramMap.get("operater");
 11         //获取数据集合这里不做具体阐述
 12         List<ReportQueryEntity> reportList = this.execute(pDateTime, channelCode,operater);
 13         //中国银联来款汇总表大表头（标题）
 14         String bigHeaderTilte="中国XX来款汇总表";
 15         //工作薄对象
 16         SXSSFWorkbook wb= new SXSSFWorkbook(500);//内存中保留 500 条数据，以免内存溢出，其余写入 硬盘
 17         //工作表对象
 18         Sheet sheet = wb.createSheet("Export"); //在webbook中添加一个sheet,对应Excel文件中的sheet
 19         
 20         //样式列表
 21         ExportExcelNewUtils utils= new ExportExcelNewUtils();
 22         Map<String, CellStyle> styles = utils.createStyles(wb);
 23         //当前行号
 24          int rownum = 0;
 25         // Create title
 26         if (StringUtils.isNotBlank(bigHeaderTilte)) {
 27             Row titleRow = sheet.createRow(0);//标题行
 28             titleRow.setHeightInPoints(30);// 设置标题的高度
 29             Cell titleCell = titleRow.createCell(0);//创建标题的单元格
 30             titleCell.setCellStyle(styles.get("title"));//设置单元格样式
 31             titleCell.setCellValue(bigHeaderTilte);//塞值
 32             //单元格合并：下标从0开始sheet.addMergedRegion(new CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) 
 33             sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
 34                     titleRow.getRowNum(), titleRow.getRowNum(), 8));
 35             
 36         }
 37         /**新增行：显示清算机构，清算币种*/
 38         sheet.addMergedRegion(new CellRangeAddress(1,1, 1, 5));//清算机构值:合并单元格
 39         Row settHeadRow = sheet.createRow(1);
 40         settHeadRow.setHeightInPoints(16);
 41         Cell settCellFirst = settHeadRow.createCell(0);
 42         settCellFirst.setCellStyle(styles.get("data"));
 43         settCellFirst.setCellValue("清算机构：");
 44         //清算机构值
 45         Cell settCellSec = settHeadRow.createCell(1);
 46         settCellSec.setCellStyle(styles.get("data"));
 47         if(CollectionUtils.isNotEmpty(reportList)){
 48             settCellSec.setCellValue(reportList.get((reportList.size()-1)).getData9());
 49         }
 50         //创建空单元格：为防止单元格合并后边框消失，每个单元格都必须创建，否则只有创建的那个单元格有边框，不创建的单元格没边框
 51         for (int j = 2; j <= 5; j++) {
 52             Cell emptyCel = settHeadRow.createCell(j);
 53             emptyCel.setCellStyle(styles.get("data")); //style为带边框的样式 上面有定义
 54             emptyCel.setCellValue("");
 55         }
 56         Cell settCellThird = settHeadRow.createCell(6);
 57         settCellThird.setCellStyle(styles.get("data"));
 58         settCellThird.setCellValue("清算币种：");
 59         Cell settCellFourth = settHeadRow.createCell(7);
 60         settCellFourth.setCellStyle(styles.get("data"));
 61         settCellFourth.setCellValue("人民币");
 62         /**新增行：显示清算日期，生成日期*/
 63         sheet.addMergedRegion(new CellRangeAddress(2,2, 1, 5));//清算日期值：合并单元格
 64         Row dateHeadRow = sheet.createRow(2);
 65         dateHeadRow.setHeightInPoints(16);
 66         Cell dateCellFirst = dateHeadRow.createCell(0);
 67         dateCellFirst.setCellStyle(styles.get("data"));
 68         dateCellFirst.setCellValue("清算日期：：");
 69         Cell cellSec = dateHeadRow.createCell(1);
 70         cellSec.setCellStyle(styles.get("data"));
 71         if(CollectionUtils.isNotEmpty(reportList)){
 72             cellSec.setCellValue(reportList.get((reportList.size()-1)).getData10());
 73         }
 74         //创建空单元格：为防止单元格合并后边框消失，每个单元格都必须创建，否则只有创建的那个单元格有边框，不创建的单元格没边框
 75         for (int j = 2; j <= 5; j++) {
 76             Cell emptyCel = dateHeadRow.createCell(j);
 77             emptyCel.setCellStyle(styles.get("data")); //style为带边框的样式 上面有定义
 78             emptyCel.setCellValue("");
 79         }
 80         Cell cellThird = dateHeadRow.createCell(6);
 81         cellThird.setCellStyle(styles.get("data"));
 82         cellThird.setCellValue("生成日期：");
 83         Cell cellFourth = dateHeadRow.createCell(7);
 84         cellFourth.setCellStyle(styles.get("data"));
 85         if(CollectionUtils.isNotEmpty(reportList)){
 86             cellFourth.setCellValue(reportList.get((reportList.size()-1)).getData11());
 87         }
 88         //交易范围 单独一行
 89         sheet.addMergedRegion(new CellRangeAddress(3,3, 0, 7));
 90         Row otherHeadRow = sheet.createRow(3);
 91         otherHeadRow.setHeightInPoints(16);
 92         Cell otherCellFirst = otherHeadRow.createCell(0);
 93         otherCellFirst.setCellStyle(styles.get("data"));
 94         otherCellFirst.setCellValue("交易范围：");
 95         //创建空单元格：为防止单元格合并后边框消失，每个单元格都必须创建，否则只有创建的那个单元格有边框，不创建的单元格没边框
 96         for (int j = 1; j <= 7; j++) {
 97             Cell emptyCel = otherHeadRow.createCell(j);
 98             emptyCel.setCellStyle(styles.get("data")); //style为带边框的样式 上面有定义
 99             emptyCel.setCellValue("");
100         }
101         //跨两行标题
102         String[] header_2={"交易类型","交易笔数"};
103         //跨列标题，跨列标题对应列数
104         String[] header_cate = {"交易金额","费用","资金清算差额"};
105         int[] cate_num ={2,2,2};
106         //小标题（不跨行不跨列）
107         String[] header_1 = {"借","贷","借","贷","借","贷"};
108 
109         for(int i=0; i<header_2.length; i++){
110             sheet.addMergedRegion(new CellRangeAddress(4, 5, i, i));//合并4-5行
111         }
112         //合并列
113         int sum1 = 0;
114         int sum2 = 0;
115         for(int i=0; i<header_cate.length; i++){
116             sum1 += cate_num[i];
117             sheet.addMergedRegion(new CellRangeAddress(4, 4, 2+sum2, 2+sum1-1));
118             sum2 += cate_num[i];
119         }
120         //创建第5行并塞值
121         Row row = sheet.createRow(4);
122         for(int i=0; i<header_2.length; i++){
123             final Cell cell = row.createCell(i);//第1,2列（index下标：0,1）
124             cell.setCellStyle(styles.get("data"));    
125             cell.setCellValue(header_2[i]);
126             int colWidth = sheet.getColumnWidth(i) * 2;
127             sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
128         }
129         int sum = 0;
130         for(int i=0; i<header_cate.length; i++){
131             final Cell cell = row.createCell(2+sum);//第3,5,7列（下标：2,4,6）
132             cell.setCellStyle(styles.get("data"));
133             cell.setCellValue(header_cate[i]);
134             sum += cate_num[i];
135         }
136         //创建空单元格(第4,6,8列(下标：3,5,7))：为防止单元格合并后边框消失，每个单元格都必须创建，否则只有创建的那个单元格有边框，不创建的单元格没边框
137         for (int j = 3; j <= 7; j=j+2) {
138             Cell emptyCel = row.createCell(j);
139             emptyCel.setCellStyle(styles.get("data")); //style为带边框的样式 上面有定义
140             emptyCel.setCellValue("");
141         }
142         //创建第6行并塞值
143         row = sheet.createRow(5);
144         //创建空单元格(第1,2列(下标：0,1))：为防止单元格合并后边框消失，每个单元格都必须创建，否则只有创建的那个单元格有边框，不创建的单元格没边框
145         for (int j = 0; j <= 1; j++) {
146             Cell emptyCel = row.createCell(j);
147             emptyCel.setCellStyle(styles.get("data")); //style为带边框的样式 上面有定义
148             emptyCel.setCellValue("");
149         }
150         for(int i=0; i<header_1.length; i++){
151             final Cell cell = row.createCell(i+2);    //第3-8列（下标：2-7）
152             cell.setCellStyle(styles.get("data"));
153             cell.setCellValue(header_1[i]);
154             int colWidth = sheet.getColumnWidth(i+2) * 2;
155             sheet.setColumnWidth(i+2, colWidth < 3000 ? 3000 : colWidth);
156         }
157         
158         
159         List<List<String>> dataList = new ArrayList<>();
160         for (int i = 0; i < reportList.size(); i++) {
161             List<String> list = new ArrayList<>();
162             list.add(reportList.get(i).getData1());
163             list.add(reportList.get(i).getData2());
164             list.add(reportList.get(i).getData3());
165             list.add(reportList.get(i).getData4());
166             list.add(reportList.get(i).getData5());
167             list.add(reportList.get(i).getData6());
168             list.add(reportList.get(i).getData7());
169             list.add(reportList.get(i).getData8());
170             dataList.add(list);
171         }
172         
173         
174         //遍历报表数据,每行每行塞入excel表格
175         for (int i = 0; i < dataList.size(); i++) {
176             //新增excel行
177             Row rowData = sheet.createRow(6+i);
178             for (int j = 0; j < dataList.get(i).size(); j++) {
179 //                utils.addCell(row, j, dataList.get(i).get(j));
180                 utils.addCellNew(rowData, j, dataList.get(i).get(j), 0, Class.class, styles, wb);
181             }
182         }
183         
184         //报表名称
185         String reportName = "中国XX来款汇总表"+pDateTime;
186         //下载
187         try {        
188             response.setContentType("application/octet-stream;charset=UTF-8");
189             response.setHeader("Content-Disposition",
190                     "attachment; filename=" + java.net.URLEncoder.encode(reportName+ ".xls", "UTF-8"));
191             OutputStream out = response.getOutputStream();
192             wb.write(out);//写出文件
193             
194             out.flush();
195             out.close();
196         } catch (Exception e) {
197             e.printStackTrace();
198         }
199     }

其中：第一：ExportExcelNewUtils代码为：

 1 /**
 2  * 
 3  */
 4 package kklazy.utils;
 5 
 6 import java.util.Date;
 7 import java.util.Map;
 8 
 9 import org.apache.poi.ss.usermodel.Cell;
10 import org.apache.poi.ss.usermodel.CellStyle;
11 import org.apache.poi.ss.usermodel.DataFormat;
12 import org.apache.poi.ss.usermodel.Row;
13 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
14 import org.slf4j.LoggerFactory;
15 
16 /**
17  * 
18  * 导出excel工具类
19  *
20  */
21 public class ExportExcelNewUtils extends  ExportExcelUtils{
22     private static org.slf4j.Logger log = LoggerFactory.getLogger(ExportExcelUtils.class);
23     
24     /**
25      * 
26      */
27     public ExportExcelNewUtils() {
28         
29     }
30     
31     /**
32      * 添加一个单元格
33      * 
34      * @param row
35      *            添加的行
36      * @param column
37      *            添加列号
38      * @param val
39      *            添加值
40      * @param align
41      *            对齐方式（1：靠左；2：居中；3：靠右）
42      * @return 单元格对象
43      */
44     public Cell addCellNew(Row row, int column, Object val, int align,
45             Class<?> fieldType,Map<String, CellStyle> styles,SXSSFWorkbook wb) {
46         Cell cell = row.createCell(column);
47         /*CellStyle style = styles.get("data"
48                 + (align >= 1 && align <= 3 ? align : ""));*/
49         CellStyle style = styles.get("header");
50         try {
51             if (val == null) {
52                 cell.setCellValue("");
53             } else if (val instanceof String) {
54                 cell.setCellValue((String) val);
55             } else if (val instanceof Integer) {
56                 cell.setCellValue((Integer) val);
57             } else if (val instanceof Long) {
58                 cell.setCellValue((Long) val);
59             } else if (val instanceof Double) {
60                 cell.setCellValue((Double) val);
61             } else if (val instanceof Float) {
62                 cell.setCellValue((Float) val);
63             } else if (val instanceof Date) {
64                 DataFormat format = wb.createDataFormat();
65                 style.setDataFormat(format.getFormat("yyyy-MM-dd"));
66                 cell.setCellValue((Date) val);
67             } else {
68                 if (fieldType != Class.class) {
69                     cell.setCellValue((String) fieldType.getMethod("setValue",
70                             Object.class).invoke(null, val));
71                 } else {
72                     cell.setCellValue((String) Class
73                             .forName(
74                                     this.getClass()
75                                             .getName()
76                                             .replaceAll(
77                                                     this.getClass()
78                                                             .getSimpleName(),
79                                                     "fieldtype."
80                                                             + val.getClass()
81                                                                     .getSimpleName()
82                                                             + "Type"))
83                             .getMethod("setValue", Object.class)
84                             .invoke(null, val));
85                 }
86             }
87         } catch (Exception ex) {
88             log.info("Set cell value [" + row.getRowNum() + "," + column
89                     + "] error: " + ex.toString());
90             cell.setCellValue(val.toString());
91         }
92         cell.setCellStyle(style);
93         return cell;
94     }
95     
96 }

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

 1 /**
 2      * 创建表格样式
 3      *
 4      * @param wb
 5      *            工作薄对象
 6      * @return 样式列表
 7      */
 8     public Map<String, CellStyle> createStyles(Workbook wb) {
 9         Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
10 
11         CellStyle style = wb.createCellStyle();//设置样式
12         style.setAlignment(CellStyle.ALIGN_CENTER);
13         style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
14         Font titleFont = wb.createFont();// 设置字体
15         titleFont.setFontName("Arial");//设置字体名字 
16         titleFont.setFontHeightInPoints((short) 16);//设置字体大小
17         titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);//设置字体加粗
18         style.setFont(titleFont);
19         styles.put("title", style);
20 
21         style = wb.createCellStyle();
22         //style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
23         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直对齐的样式为居中对齐; 
24         style.setBorderRight(CellStyle.BORDER_THIN);//设置右边框; 
25         //style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
26         style.setBorderLeft(CellStyle.BORDER_THIN);//设置左边框; 
27         //style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
28         style.setBorderTop(CellStyle.BORDER_THIN); //设置顶边框; 
29         //style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
30         style.setBorderBottom(CellStyle.BORDER_THIN);//设置底边框; 
31         //style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
32         Font dataFont = wb.createFont();
33         dataFont.setFontName("Arial");
34         dataFont.setFontHeightInPoints((short) 10);
35         style.setFont(dataFont);
36         styles.put("data", style);
37 
38         style = wb.createCellStyle();
39         style.cloneStyleFrom(styles.get("data"));//克隆样式
40         style.setAlignment(CellStyle.ALIGN_LEFT);//设置水平对齐的样式为居中对齐;
41         styles.put("data1", style);
42 
43         style = wb.createCellStyle();
44         style.cloneStyleFrom(styles.get("data"));
45         style.setAlignment(CellStyle.ALIGN_CENTER);
46         styles.put("data2", style);
47 
48         style = wb.createCellStyle();
49         style.cloneStyleFrom(styles.get("data"));
50         style.setAlignment(CellStyle.ALIGN_RIGHT);
51         styles.put("data3", style);
52 
53         style = wb.createCellStyle();
54         style.cloneStyleFrom(styles.get("data"));
55         style.setWrapText(true);
56         style.setAlignment(CellStyle.ALIGN_CENTER);
57         style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
58         style.setFillPattern(CellStyle.SOLID_FOREGROUND);
59         Font headerFont = wb.createFont();
60         headerFont.setFontName("Arial");
61         headerFont.setFontHeightInPoints((short) 10);
62         headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
63         headerFont.setColor(IndexedColors.BLACK.getIndex());
64         style.setFont(headerFont);
65         styles.put("header", style);
66 
67         return styles;
68     }

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

最后：导出excel效果如下：

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