导出Excel java

2016-01-18 16:47 qqForever 阅读(203) 评论(0) 编辑收藏举报

1.导出excel的工具类模板

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

2.导出调用方法

  1 public void exportXls(V_itemList item, HttpServletRequest request, HttpServletResponse response) {
  2         HSSFWorkbook wb_item = new HSSFWorkbook();
  3         HSSFSheet sheet_item = wb_item.createSheet();
  4         ExportExcelUtil exportExcel = new ExportExcelUtil(wb_item, sheet_item);
  5         //列数
  6         int number = 13 ;
  7         // 给工作表列定义列宽(实际应用自己更改列数)
  8         for (int i = 0; i < number; i++) {
  9             sheet_item.setColumnWidth(i,5000);
 10         }
 11         // 创建单元格样式
 12         HSSFCellStyle cellStyle = wb_item.createCellStyle();
 13         // 指定单元格居中对齐
 14         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
 15         // 指定单元格垂直居中对齐
 16         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
 17         // 指定当单元格内容显示不下时自动换行
 18         cellStyle.setWrapText(true);
 19         // 设置单元格字体
 20         HSSFFont font = wb_item.createFont();
 21         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
 22         font.setFontName("宋体");
 23         font.setFontHeight((short) 200);
 24         cellStyle.setFont(font);
 25         // 创建报表头部
 26         exportExcel.createNormalHead("美容项目列表", number);
 27         // 设置第二行
 28         Date now = new Date(); 
 29         DateFormat d1 = DateFormat.getDateTimeInstance();
 30         String nowDate = d1.format(now); 
 31         String excelWhere=getItemExcelWhere(item, request);
 32         if (StringUtil.isEmpty(excelWhere)) {
 33             excelWhere="无";
 34         }
 35         exportExcel.createNormalTwoRow(nowDate,excelWhere, number);
 36         HSSFRow row2 = sheet_item.createRow(2);
 37         
 38         HSSFCell cell0 = row2.createCell(0);
 39         cell0.setCellStyle(cellStyle);
 40         cell0.setCellValue(new HSSFRichTextString("项目编号"));
 41         
 42         
 43         HSSFCell cell1 = row2.createCell(1);
 44         cell1.setCellStyle(cellStyle);
 45         cell1.setCellValue(new HSSFRichTextString("项目名称"));
 46         
 47         HSSFCell cell2 = row2.createCell(2);
 48         cell2.setCellStyle(cellStyle);
 49         cell2.setCellValue(new HSSFRichTextString("项目类型"));
 50         
 51       
 92         String sql;
 93         sql="from V_itemList t";
 94         String sqlWhere = getItemSqlWhere(item,request);
 95         if (!sqlWhere.isEmpty()) {
 96             sql+=" where "+sqlWhere;
 97         }
 98         List<V_itemList> itemList= systemService.findByQueryString(sql);
 99         // 循环创建中间的单元格的各项的值
100                 for (int i =0; i < itemList.size(); i++) {
101                     V_itemList dc =  itemList.get(i);
102                     HSSFRow row = sheet_item.createRow((short) i + 3);
103                     exportExcel.cteateCell(wb_item, row, (short) 0,HSSFCellStyle.ALIGN_CENTER_SELECTION, String .valueOf(dc.getNo()));
104                     exportExcel.cteateCell(wb_item, row, (short) 1,HSSFCellStyle.ALIGN_CENTER_SELECTION, String .valueOf(dc.getName()));
105                     exportExcel.cteateCell(wb_item, row, (short) 2,HSSFCellStyle.ALIGN_CENTER_SELECTION, String .valueOf(dc.getTypename()));
106                   
131     }
132                         
133         exportExcel.outputExcel("d:\\itemList.xls");
134         download("d:\\itemList.xls", response); 
135     }

3.弹出下载框

 1 /**
 2      * 弹出下载框
 3      * @param path
 4      * @param response
 5      */
 6     private void download(String path, HttpServletResponse response) {  
 7         try {  
 8             // path是指欲下载的文件的路径。  
 9             File file = new File(path);  
10             // 取得文件名。  
11             String filename = file.getName();  
12             // 以流的形式下载文件。  
13             InputStream fis = new BufferedInputStream(new FileInputStream(path));  
14             byte[] buffer = new byte[fis.available()];  
15             fis.read(buffer);  
16             fis.close();  
17             // 清空response  
18             response.reset();  
19             // 设置response的Header  
20             response.addHeader("Content-Disposition", "attachment;filename="  
21                     + new String(filename.getBytes()));  
22             response.addHeader("Content-Length", "" + file.length());  
23             OutputStream toClient = new BufferedOutputStream(  
24                     response.getOutputStream());  
25             response.setContentType("application/vnd.ms-excel;charset=gb2312");  
26             toClient.write(buffer);  
27             toClient.flush();  
28             toClient.close();  
29         } catch (IOException ex) {  
30             ex.printStackTrace();  
31         }  
32     }

会员力量，点亮园子希望

刷新页面返回顶部

qqForever

导出Excel java

About