使用org.apache.poi导出Excel表格

  1 public HSSFWorkbook MakeExcel(List<TransactionLogVO> logList) {
  2 
  3        // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  4         /*response.reset();  
  5         response.setHeader("Content-Disposition", "attachment;fileName="+ fileName);// 指定下载的文件名       
  6         OutputStream output = response.getOutputStream();  
  7         BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);  */
  8         //        String path = this.getClass().getClassLoader().getResource("WEB-INF/tqjsxx").getPath();
  9         // 定义单元格报头  
 10         String worksheetTitle = "交易日志信息";
 11 
 12         HSSFWorkbook wb = new HSSFWorkbook();
 13 
 14         // 创建单元格样式  
 15         HSSFCellStyle cellStyleTitle = wb.createCellStyle();
 16         // 指定单元格居中对齐  
 17         cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 18         // 指定单元格垂直居中对齐  
 19         cellStyleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
 20         // 指定当单元格内容显示不下时不换行  
 21         cellStyleTitle.setWrapText(false);
 22         // ------------------------------------------------------------------  
 23         HSSFCellStyle cellStyle = wb.createCellStyle();
 24         // 指定单元格居中对齐  
 25         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 26         // 指定单元格垂直居中对齐  
 27         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
 28         // 指定当单元格内容显示不下时换行  
 29         cellStyle.setWrapText(true);
 30 
 31         // ------------------------------------------------------------------  
 32         // 设置单元格字体  
 33         HSSFFont font = wb.createFont();
 34         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
 35         font.setFontName("宋体");
 36         font.setFontHeight((short) 200);
 37         cellStyleTitle.setFont(font);
 38 
 39         // 工作表名  
 40         String id = "序号";
 41         String channelCode = "渠道代码";
 42         String channelName = "渠道名称";
 43         String cityName = "所属城市";
 44         String functionCode = "接口功能";
 45         String logTime = "日志时间";
 46         String reqMsgId = "消息ID";
 47         String reqTime = "请求时间";
 48         String reqMsg = "请求报文";
 49         String respTime = "响应时间";
 50         String resultCode = "处理代码";
 51         String respMsg = "响应报文";
 52 
 53         HSSFSheet sheet = wb.createSheet();
 54         ExportExcel exportExcel = new ExportExcel(wb, sheet);
 55         // 创建报表头部  
 56         exportExcel.createNormalHead(worksheetTitle, 11);
 57         // 定义第一行  
 58         HSSFRow row1 = sheet.createRow(1);
 59         HSSFCell cell1 = row1.createCell(0);
 60 
 61         //第一行第1列         
 62         cell1.setCellStyle(cellStyleTitle);
 63         cell1.setCellValue(new HSSFRichTextString(id));
 64         sheet.setColumnWidth(0, 7 * 256);
 65         //第一行第2列  
 66         cell1 = row1.createCell(1);
 67         cell1.setCellStyle(cellStyleTitle);
 68         cell1.setCellValue(new HSSFRichTextString(channelCode));
 69         //第一行第3列  
 70         cell1 = row1.createCell(2);
 71         cell1.setCellStyle(cellStyleTitle);
 72         cell1.setCellValue(new HSSFRichTextString(channelName));
 73 
 74         //第一行第4列  
 75         cell1 = row1.createCell(3);
 76         cell1.setCellStyle(cellStyleTitle);
 77         cell1.setCellValue(new HSSFRichTextString(cityName));
 78 
 79         //第一行第5列  
 80         cell1 = row1.createCell(4);
 81         cell1.setCellStyle(cellStyleTitle);
 82         cell1.setCellValue(new HSSFRichTextString(functionCode));
 83         sheet.setColumnWidth(4, 20 * 256);
 84         //第一行第6列  
 85         cell1 = row1.createCell(5);
 86         cell1.setCellStyle(cellStyleTitle);
 87         cell1.setCellValue(new HSSFRichTextString(logTime));
 88         sheet.setColumnWidth(5, 20 * 256);
 89         //第一行第7列  
 90         cell1 = row1.createCell(6);
 91         cell1.setCellStyle(cellStyleTitle);
 92         cell1.setCellValue(new HSSFRichTextString(reqMsgId));
 93         sheet.setColumnWidth(6, 20 * 256);
 94         //第一行第8列  
 95         cell1 = row1.createCell(7);
 96         cell1.setCellStyle(cellStyleTitle);
 97         cell1.setCellValue(new HSSFRichTextString(reqTime));
 98         sheet.setColumnWidth(7, 20 * 256);
 99         //第一行第9列  
100         cell1 = row1.createCell(8);
101         cell1.setCellStyle(cellStyleTitle);
102         cell1.setCellValue(new HSSFRichTextString(reqMsg));
103         sheet.setColumnWidth(8, 40 * 256);
104         //第一行第10列  
105         cell1 = row1.createCell(9);
106         cell1.setCellStyle(cellStyleTitle);
107         cell1.setCellValue(new HSSFRichTextString(respTime));
108         sheet.setColumnWidth(9, 20 * 256);
109         //第一行第11列  
110         cell1 = row1.createCell(10);
111         cell1.setCellStyle(cellStyleTitle);
112         cell1.setCellValue(new HSSFRichTextString(resultCode));
113         sheet.setColumnWidth(10, 20 * 256);
114         //第一行第12列  
115         cell1 = row1.createCell(11);
116         cell1.setCellStyle(cellStyleTitle);
117         cell1.setCellValue(new HSSFRichTextString(respMsg));
118         sheet.setColumnWidth(11, 40 * 256);
119 
120         //定义第二行开始后的实体内容  
121         HSSFRow row = sheet.createRow(2);
122         HSSFCell cell = row.createCell(1);
123         for (int i = 0; i < logList.size(); i++) {
124             TransactionLogVO transactionLog = JSON.parseObject(JSONObject.toJSONString(logList.get(i)), TransactionLogVO.class);
125 
126             row = sheet.createRow(i + 2);
127 
128             cell = row.createCell(0);
129             cell.setCellStyle(cellStyle);
130             cell.setCellValue(new HSSFRichTextString(transactionLog.getId() + ""));
131 
132             cell = row.createCell(1);
133             cell.setCellStyle(cellStyle);
134             cell.setCellValue(new HSSFRichTextString(transactionLog.getChannelCode()));
135 
136             cell = row.createCell(2);
137             cell.setCellStyle(cellStyle);
138             cell.setCellValue(new HSSFRichTextString(transactionLog.getChannelName()));
139 
140             cell = row.createCell(3);
141             cell.setCellStyle(cellStyle);
142             cell.setCellValue(new HSSFRichTextString(transactionLog.getCityName()));
143 
144             cell = row.createCell(4);
145             cell.setCellStyle(cellStyle);
146             cell.setCellValue(new HSSFRichTextString(transactionLog.getFunctionCode()));
147 
148             cell = row.createCell(5);
149             cell.setCellStyle(cellStyle);
150             //cell.setCellValue(new HSSFRichTextString(sdf.format(transactionLog.getLogTime())));
151             cell.setCellValue(new HSSFRichTextString(timeStamp2Date(transactionLog.getLogTime())));
152 
153             cell = row.createCell(6);
154             cell.setCellStyle(cellStyle);
155             cell.setCellValue(new HSSFRichTextString(transactionLog.getReqMsgId()));
156 
157             cell = row.createCell(7);
158             cell.setCellStyle(cellStyle);
159             //cell.setCellValue(new HSSFRichTextString(sdf.format(transactionLog.getReqTime())));
160             cell.setCellValue(new HSSFRichTextString(timeStamp2Date(transactionLog.getReqTime())));
161 
162             cell = row.createCell(8);
163             cell.setCellStyle(cellStyle);
164             cell.setCellValue(new HSSFRichTextString(transactionLog.getReqMsg()));
165 
166             cell = row.createCell(9);
167             cell.setCellStyle(cellStyle);
168             //cell.setCellValue(new HSSFRichTextString(sdf.format(transactionLog.getRespTime())));
169             cell.setCellValue(new HSSFRichTextString(timeStamp2Date(transactionLog.getRespTime())));
170 
171             cell = row.createCell(10);
172             cell.setCellStyle(cellStyle);
173             cell.setCellValue(new HSSFRichTextString(transactionLog.getResultCode()));
174 
175             cell = row.createCell(11);
176             cell.setCellStyle(cellStyle);
177             cell.setCellValue(new HSSFRichTextString(transactionLog.getRespMsg()));
178 
179         }
180         return wb;
181     }



导出表格工具类

  1 public class ExportExcel {  
  2   
  3     private HSSFWorkbook wb = null;  
  4     private HSSFSheet sheet = null;  
  5   
  6     /** 
  7      * @param wb 
  8      * @param sheet  
  9      */  
 10     public ExportExcel(HSSFWorkbook wb, HSSFSheet sheet) {  
 11         // super();  
 12         this.wb = wb;  
 13         this.sheet = sheet;  
 14     }  
 15   
 16     /** 
 17      * 创建通用EXCEL头部 
 18      *  
 19      * @param headString 
 20      *            头部显示的字符 
 21      * @param colSum 
 22      *            该报表的列数 
 23      */  
 24     public void createNormalHead(String headString, int colSum) {  
 25         HSSFRow row = sheet.createRow(0);  
 26         // 设置第一行  
 27         HSSFCell cell = row.createCell(0);  
 28         // row.setHeight((short) 1000);  
 29   
 30         // 定义单元格为字符串类型  
 31         cell.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理  
 32         cell.setCellValue(new HSSFRichTextString(headString));  
 33   
 34         // 指定合并区域  
 35         /** 
 36          * public Region(int rowFrom, short colFrom, int rowTo, short colTo) 
 37          */  
 38         sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, (short) 0, (short) colSum));  
 39   
 40         // 定义单元格格式，添加单元格表样式，并添加到工作簿  
 41         HSSFCellStyle cellStyle = wb.createCellStyle();  
 42         // 设置单元格水平对齐类型  
 43         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐  
 44         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐  
 45         cellStyle.setWrapText(true);// 指定单元格自动换行  
 46   
 47         // 设置单元格字体  
 48         HSSFFont font = wb.createFont();  
 49         // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
 50         // font.setFontName("宋体");  
 51         // font.setFontHeight((short) 600);  
 52         // cellStyle.setFont(font);  
 53         cell.setCellStyle(cellStyle);  
 54     }  
 55   
 56     /** 
 57      * 创建通用报表第二行 
 58      *  
 59      * @param params 
 60      *            统计条件数组 
 61      * @param colSum 
 62      *            需要合并到的列索引 
 63      */  
 64     public void createNormalTwoRow(String[] params, int colSum) {  
 65         // 创建第二行  
 66         HSSFRow row1 = sheet.createRow(1);  
 67   
 68         row1.setHeight((short) 400);  
 69   
 70         HSSFCell cell2 = row1.createCell(0);  
 71   
 72         cell2.setCellType(HSSFCell.ENCODING_UTF_16);  
 73         cell2.setCellValue(new HSSFRichTextString("时间：" + params[0] + "至"  
 74                 + params[1]));  
 75   
 76         // 指定合并区域  
 77         /** 
 78          * public Region(int rowFrom, short colFrom, int rowTo, short colTo) 
 79          */  
 80         sheet.addMergedRegion(new CellRangeAddress(1, 1, (short) 0, (short) colSum));  
 81   
 82         HSSFCellStyle cellStyle = wb.createCellStyle();  
 83         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐  
 84         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐  
 85         cellStyle.setWrapText(true);// 指定单元格自动换行  
 86   
 87         // 设置单元格字体  
 88         HSSFFont font = wb.createFont();  
 89         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
 90         font.setFontName("宋体");  
 91         font.setFontHeight((short) 250);  
 92         cellStyle.setFont(font);  
 93   
 94         cell2.setCellStyle(cellStyle);  
 95     }  
 96   
 97     /** 
 98      * 设置报表标题 
 99      *  
100      * @param columHeader 
101      *            标题字符串数组 
102      */  
103     public void createColumHeader(String[] columHeader) {  
104   
105         // 设置列头 在第三行  
106         HSSFRow row2 = sheet.createRow(2);  
107   
108         // 指定行高  
109         row2.setHeight((short) 600);  
110   
111         HSSFCellStyle cellStyle = wb.createCellStyle();  
112         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐  
113         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐  
114         cellStyle.setWrapText(true);// 指定单元格自动换行  
115   
116         // 单元格字体  
117         HSSFFont font = wb.createFont();  
118         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
119         font.setFontName("宋体");  
120         font.setFontHeight((short) 250);  
121         cellStyle.setFont(font);  
122   
123         // 设置单元格背景色  
124         cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);  
125         cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
126   
127         HSSFCell cell3 = null;  
128   
129         for (int i = 0; i < columHeader.length; i++) {  
130             cell3 = row2.createCell(i);  
131             cell3.setCellType(HSSFCell.ENCODING_UTF_16);  
132             cell3.setCellStyle(cellStyle);  
133             cell3.setCellValue(new HSSFRichTextString(columHeader[i]));  
134         }  
135     }  
136   
137     /** 
138      * 创建内容单元格 
139      *  
140      * @param wb 
141      *            HSSFWorkbook 
142      * @param row 
143      *            HSSFRow 
144      * @param col 
145      *            short型的列索引 
146      * @param align 
147      *            对齐方式 
148      * @param val 
149      *            列值 
150      */  
151     public void cteateCell(HSSFWorkbook wb, HSSFRow row, int col, short align,  
152             String val) {  
153         HSSFCell cell = row.createCell(col);  
154         cell.setCellType(HSSFCell.ENCODING_UTF_16);  
155         cell.setCellValue(new HSSFRichTextString(val));  
156         HSSFCellStyle cellstyle = wb.createCellStyle();  
157         cellstyle.setAlignment(align);  
158         cell.setCellStyle(cellstyle);  
159     }  
160   
161     /** 
162      * 创建合计行 
163      *  
164      * @param colSum 
165      *            需要合并到的列索引 
166      * @param cellValue 
167      */   
168     public void createLastSumRow(int colSum, String[] cellValue) {  
169   
170         HSSFCellStyle cellStyle = wb.createCellStyle();  
171         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐  
172         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐  
173         cellStyle.setWrapText(true);// 指定单元格自动换行  
174   
175         // 单元格字体  
176         HSSFFont font = wb.createFont();  
177         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
178         font.setFontName("宋体");  
179         font.setFontHeight((short) 250);  
180         cellStyle.setFont(font);  
181         // 获取工作表最后一行  
182         HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));  
183         HSSFCell sumCell = lastRow.createCell(0);  
184   
185         sumCell.setCellValue(new HSSFRichTextString("合计"));  
186         sumCell.setCellStyle(cellStyle);  
187         // 合并 最后一行的第零列-最后一行的第一列  
188         sheet.addMergedRegion(new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(),  
189                 (short) 0, (short) colSum));// 指定合并区域  
190   
191         for (int i = 2; i < (cellValue.length + 2); i++) {  
192             // 定义最后一行的第三列  
193             sumCell = lastRow.createCell(i);  
194             sumCell.setCellStyle(cellStyle);  
195             // 定义数组 从0开始。  
196             sumCell.setCellValue(new HSSFRichTextString(cellValue[i - 2]));  
197         }  
198     }  
199   
200     /** 
201      * 输入EXCEL文件 
202      *  
203      * @param fileName 
204      *            文件名 
205      */  
206     public void outputExcel(String fileName) {  
207         FileOutputStream fos = null;  
208         try {  
209             fos = new FileOutputStream(new File(fileName));  
210             wb.write(fos);  
211             fos.close();  
212         } catch (FileNotFoundException e) {  
213             e.printStackTrace();  
214         } catch (IOException e) {  
215             e.printStackTrace();  
216         }  
217     }  
218   
219     /** 
220      * @return the sheet 
221      */  
222     public HSSFSheet getSheet() {  
223         return sheet;  
224     }  
225   
226     /** 
227      * @param sheet 
228      *            the sheet to set 
229      */  
230     public void setSheet(HSSFSheet sheet) {  
231         this.sheet = sheet;  
232     }  
233   
234     /** 
235      * @return the wb 
236      */  
237     public HSSFWorkbook getWb() {  
238         return wb;  
239     }  
240   
241     /** 
242      * @param wb 
243      *            the wb to set 
244      */  
245     public void setWb(HSSFWorkbook wb) {  
246         this.wb = wb;  
247     }  
248 }

调用方法

 1  @RequestMapping("/export")
 2     public void export(TransactionLogVO transactionlog, HttpServletRequest request, HttpServletResponse response) {
 3         ResultMessage<DataGridVO> queryRs = transactionLogServiceImpl.list(transactionlog);
 4         //System.out.println(queryRs.toString());
 5         //Map<?, ?> resultMap = JSON.parseObject(queryRs.toString());
 6         List<TransactionLogVO> resultL = new ArrayList<TransactionLogVO>();
 7         //if ((Integer) resultMap.get("errcode") == 0) {
 8         resultL = (List<TransactionLogVO>) queryRs.getObj().getRows();
 9         // }
10         if (resultL == null || resultL.size() == 0) {
11             resultL = new ArrayList<TransactionLogVO>();
12         }
13         System.out.println("resultL:" + resultL.toString());
14         HSSFWorkbook wb = MakeExcel(resultL);
15         String fileName = "交易日志.xls";
16         try {
17             fileName = new String(fileName.getBytes("utf-8"), "ISO8859-1");
18         } catch (UnsupportedEncodingException e) {
19             e.printStackTrace();
20         }
21         System.out.println("fileName:" + fileName);
22         response.reset();
23         response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);// 指定下载的文件名       
24         response.setContentType("application/vnd.ms-excel;charset=utf-8");
25         try {
26             OutputStream output = response.getOutputStream();
27             wb.write(output);
28             output.flush();
29             output.close();
30         } catch (IOException e) {
31             e.printStackTrace();
32         }
33     }

需要在pom文件加入POI依赖

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10.1</version>
</dependency>

posted @ 2018-05-10 11:22 宇枫阅读(2139) 评论(0) 编辑收藏举报

宇翊

使用org.apache.poi导出Excel表格