java POI导出Excel

1.需要的jar包

 1 <properties>
 2     <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 3      <poi.version>3.10-beta2</poi.version> 
 4   </properties>
 5 <!-- 加入POI核心依赖 -->
 6  <dependencies>
 7        <dependency>
 8               <groupId>org.apache.poi</groupId>
 9               <artifactId>poi</artifactId>
10               <version>${poi.version}</version>
11        </dependency>
12        <!--为POI支持office open xml -->
13        <dependency>
14               <groupId>org.apache.poi</groupId>
15               <artifactId>poi-ooxml</artifactId>
16               <version>${poi.version}</version>
17        </dependency>
18        <dependency>
19               <groupId>org.apache.poi</groupId>
20               <artifactId>poi-scratchpad</artifactId>
21               <version>${poi.version}</version>
22        </dependency>
23        <dependency>
24            <groupId>net.sourceforge.jexcelapi</groupId>
25            <artifactId>jxl</artifactId>
26            <version>2.6.12</version>
27        </dependency>
28  </dependencies>

2.POI导出Excel工具类

  1 package com.jk.util;
  2  
  3 import java.io.IOException;
  4 import java.io.OutputStream;
  5 import java.util.ArrayList;
  6 import java.util.List;
  7 
  8 import javax.servlet.http.HttpServletResponse;
  9 
 10 import org.apache.poi.hssf.usermodel.HSSFCell;
 11 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
 12 import org.apache.poi.hssf.usermodel.HSSFFont;
 13 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
 14 import org.apache.poi.hssf.usermodel.HSSFRow;
 15 import org.apache.poi.hssf.usermodel.HSSFSheet;
 16 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 17 import org.apache.poi.hssf.util.CellRangeAddress;
 18 import org.apache.poi.hssf.util.HSSFColor;
 19 
 20 
 21  
 22 /**
 23  * 导出Excel公共方法
 24  * @version 1.0    
 25  * 
 26  * @author wangcp
 27  *
 28  */ 
 29 public class ExportExcel { 
 30      
 31     //显示的导出表的标题 
 32     private String title; 
 33     //导出表的列名 
 34     private String[] rowName ; 
 35      
 36     private List<Object[]>  dataList = new ArrayList<Object[]>();
 37    
 38        HttpServletResponse response; 
 39      
 40     //构造方法，传入要导出的数据 
 41     public  ExportExcel(String title,String[] rowName,List<Object[]>  dataList,HttpServletResponse  response){ 
 42         this.dataList = dataList; 
 43         this.rowName = rowName; 
 44         this.title = title; 
 45         this.response = response;
 46     } 
 47              
 48     /*
 49      * 导出数据
 50      * */ 
 51     public void export() throws Exception{ 
 52         try{ 
 53           //HSSFWorkbook是 03版的Excel,XSSFWorkbook是 版的Excel
 54             HSSFWorkbook workbook = new HSSFWorkbook();                     // 创建工作簿对象 
 55             HSSFSheet sheet = workbook.createSheet(title);                  // 创建工作表 
 56  
 57             // 产生表格标题行（大标题） 
 58             HSSFRow rowm = sheet.createRow(0); 
 59             HSSFCell cellTiltle = rowm.createCell(0); 
 60             
 61             //设置单元格样式
 62             //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】 
 63             HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象 
 64             HSSFCellStyle style = this.getStyle(workbook);                  //单元格样式对象 
 65               
 66             //合并单元格 0,1,0,(rowName.length-1) 代表：起始行，结束行，起始列，结束列
 67             sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));   
 68             //设置单元格样式
 69             cellTiltle.setCellStyle(columnTopStyle); 
 70             //设置单元格内容
 71             cellTiltle.setCellValue(title); 
 72  
 73             // 定义所需列数 
 74             int columnNum = rowName.length; 
 75             HSSFRow rowRowName = sheet.createRow(2);                // 在索引2的位置创建行(最顶端的行开始的第二行) 
 76              
 77             // 将列头设置到sheet的单元格中 
 78             for(int n=0;n<columnNum;n++){ 
 79                 HSSFCell  cellRowName = rowRowName.createCell(n);               //创建列头对应个数的单元格 
 80                 cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);             //设置列头单元格的数据类型 
 81                 HSSFRichTextString text = new HSSFRichTextString(rowName[n]); 
 82                 cellRowName.setCellValue(text);                                 //设置列头单元格的值 
 83                 cellRowName.setCellStyle(columnTopStyle);                       //设置列头单元格样式 
 84             } 
 85              
 86             //将查询出的数据设置到sheet对应的单元格中 
 87             for(int i=0;i<dataList.size();i++){ 
 88                  
 89                 Object[] obj = dataList.get(i);//遍历每个对象 
 90                 HSSFRow row = sheet.createRow(i+3);//创建所需的行数 
 91                  
 92                 for(int j=0; j<obj.length; j++){ 
 93                     HSSFCell  cell = null;   //设置单元格的数据类型 
 94 //                    if(j == 0){ 
 95 //                        cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC); 
 96 //                        cell.setCellValue(i+1);  
 97 //                    }else{ 
 98                         cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING); 
 99                         if(!"".equals(obj[j]) && obj[j] != null){ 
100                             cell.setCellValue(obj[j].toString());                       //设置单元格的值 
101                         }else {
102                               cell.setCellValue("");
103                         }
104 //                    } 
105                     cell.setCellStyle(style);                                   //设置单元格样式 
106                 } 
107             } 
108             //让列宽随着导出的列长自动适应 
109             for (int colNum = 0; colNum < columnNum; colNum++) { 
110                 int columnWidth = sheet.getColumnWidth(colNum) / 256; 
111                 for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { 
112                     HSSFRow currentRow; 
113                     //当前行未被使用过 
114                     if (sheet.getRow(rowNum) == null) { 
115                         currentRow = sheet.createRow(rowNum); 
116                     } else { 
117                         currentRow = sheet.getRow(rowNum); 
118                     } 
119                     if (currentRow.getCell(colNum) != null) { 
120                         HSSFCell currentCell = currentRow.getCell(colNum); 
121                         if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING ) { 
122                               int length = 0;
123                               if (null != currentCell.getStringCellValue() && !"".equals(currentCell.getStringCellValue())) {
124                                      length = currentCell.getStringCellValue().getBytes().length; 
125                                                  }
126                             if (columnWidth < length) { 
127                                 columnWidth = length; 
128                             } 
129                         } 
130                     } 
131                 } 
132                 if(colNum == 0){ 
133                     sheet.setColumnWidth(colNum, (columnWidth-2) * 256); 
134                 }else{ 
135                     sheet.setColumnWidth(colNum, (columnWidth+4) * 256); 
136                 } 
137             } 
138              
139             if(workbook !=null){ 
140                 try 
141                 { 
142                     String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls"; 
143                     String headStr = "attachment; filename=\"" + fileName + "\""; 
144                     response.setContentType("APPLICATION/OCTET-STREAM"); 
145                     response.setHeader("Content-Disposition", headStr); 
146                     OutputStream out = response.getOutputStream(); 
147                     workbook.write(out); 
148                 } 
149                 catch (IOException e) 
150                 { 
151                     e.printStackTrace(); 
152                 } 
153             } 
154  
155         }catch(Exception e){ 
156             e.printStackTrace(); 
157         } 
158          
159     } 
160      
161     /* 
162      * 列头单元格样式
163      */     
164     public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { 
165          
166           // 设置字体 
167           HSSFFont font = workbook.createFont(); 
168           //设置字体大小 
169           font.setFontHeightInPoints((short)11); 
170           //字体加粗 
171           font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
172           //设置字体名字  
173           font.setFontName("Courier New"); 
174           //设置样式;  
175           HSSFCellStyle style = workbook.createCellStyle(); 
176           //设置底边框;  
177           style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
178           //设置底边框颜色;   
179           style.setBottomBorderColor(HSSFColor.BLACK.index); 
180           //设置左边框;    
181           style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
182           //设置左边框颜色;  
183           style.setLeftBorderColor(HSSFColor.BLACK.index); 
184           //设置右边框;  
185           style.setBorderRight(HSSFCellStyle.BORDER_THIN); 
186           //设置右边框颜色;  
187           style.setRightBorderColor(HSSFColor.BLACK.index); 
188           //设置顶边框;  
189           style.setBorderTop(HSSFCellStyle.BORDER_THIN); 
190           //设置顶边框颜色;   
191           style.setTopBorderColor(HSSFColor.BLACK.index); 
192           //在样式用应用设置的字体;   
193           style.setFont(font); 
194           //设置自动换行;  
195           style.setWrapText(false); 
196           //设置水平对齐的样式为居中对齐;   
197           style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
198           //设置垂直对齐的样式为居中对齐;  
199           style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
200            
201           return style; 
202            
203     } 
204      
205     /*  
206      * 列数据信息单元格样式
207      */   
208     public HSSFCellStyle getStyle(HSSFWorkbook workbook) { 
209           // 设置字体 
210           HSSFFont font = workbook.createFont(); 
211           //设置字体大小 
212           //font.setFontHeightInPoints((short)10); 
213           //字体加粗 
214           //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
215           //设置字体名字  
216           font.setFontName("Courier New"); 
217           //设置样式;  
218           HSSFCellStyle style = workbook.createCellStyle(); 
219           //设置底边框;  
220           style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
221           //设置底边框颜色;   
222           style.setBottomBorderColor(HSSFColor.BLACK.index); 
223           //设置左边框;    
224           style.setBorderLeft(HSSFCellStyle.BORDER_THIN); 
225           //设置左边框颜色;   
226           style.setLeftBorderColor(HSSFColor.BLACK.index); 
227           //设置右边框;  
228           style.setBorderRight(HSSFCellStyle.BORDER_THIN); 
229           //设置右边框颜色;  
230           style.setRightBorderColor(HSSFColor.BLACK.index); 
231           //设置顶边框;  
232           style.setBorderTop(HSSFCellStyle.BORDER_THIN); 
233           //设置顶边框颜色;   
234           style.setTopBorderColor(HSSFColor.BLACK.index); 
235           //在样式用应用设置的字体;   
236           style.setFont(font); 
237           //设置自动换行;  
238           style.setWrapText(false); 
239           //设置水平对齐的样式为居中对齐;   
240           style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
241           //设置垂直对齐的样式为居中对齐;  
242           style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
243           
244           return style; 
245      
246     } 
247 }

3.控制层调用

 1 /**
 2      * <pre>expAccountList(POI导出excel)  
 3      * 修改备注： 用流的形式写出去
 4      */
 5     @RequestMapping("/expAccountList")
 6     public void expAccountList(Account account, HttpServletResponse response) {
 7           
 8            //title 设置导出的sheet页的名称   以及上边的大标题
 9            String title = "账号信息"; 
10            String[] rowsName = new String[]{"序号","用户名","联系电话","状态","注册来源","注册时间","用户类型","业务员工号"};
11            List<Object[]>  dataList = new ArrayList<Object[]>();
12            //1.查询账号信息
13            try {
14                  
15                   List<Account> list = accountService.queryUserList();
16                   Object[] obj = null; 
17                   for (Account account2 : list) {
18                          obj = new Object[rowsName.length];
19                          obj[0] = account2.getId();
20                          obj[1] = account2.getUsername();
21                          obj[2] = account2.getTelphone();
22                          obj[3] = account2.getStatus();
23                          obj[4] = account2.getRegistersource();
24                          obj[5] = account2.getRegisterdate();
25                          obj[6] = account2.getUsertype();
26                          obj[7] = account2.getDbqkms();
27                          dataList.add(obj);
28                   }
29                   ExportExcel ex = new ExportExcel(title, rowsName, dataList,response); 
30                   ex.export(); 
31                    
32            } catch (Exception e) {
33                   e.printStackTrace();
34            }
35           
36     }

posted @ 2017-08-29 19:19 lill 阅读(103) 评论(0) 收藏举报

刷新页面返回顶部

lill

java POI导出Excel

公告