SpringMvc 使用poi导入导出Excel 

  1 Util类  
  2 package com.common.util;  
  3   
  4 public class ExportUtil  
  5 {  
  6     private XSSFWorkbook wb = null;  
  7   
  8     private XSSFSheet sheet = null;  
  9   
 10     /** 
 11      * @param wb 
 12      * @param sheet 
 13      */  
 14     public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet)  
 15     {  
 16         this.wb = wb;  
 17         this.sheet = sheet;  
 18     }  
 19   
 20     /** 
 21      * 合并单元格后给合并后的单元格加边框 
 22      *  
 23      * @param region 
 24      * @param cs 
 25      */  
 26     public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs)  
 27     {  
 28   
 29         int toprowNum = region.getFirstRow();  
 30         for (int i = toprowNum; i <= region.getLastRow(); i++)  
 31         {  
 32             XSSFRow row = sheet.getRow(i);  
 33             for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)  
 34             {  
 35                 XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,  
 36                                                 // (short) j);  
 37                 cell.setCellStyle(cs);  
 38             }  
 39         }  
 40     }  
 41   
 42     /** 
 43      * 设置表头的单元格样式 
 44      *  
 45      * @return 
 46      */  
 47     public XSSFCellStyle getHeadStyle()  
 48     {  
 49         // 创建单元格样式  
 50         XSSFCellStyle cellStyle = wb.createCellStyle();  
 51         // 设置单元格的背景颜色为淡蓝色  
 52         cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);  
 53         cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);  
 54         // 设置单元格居中对齐  
 55         cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
 56         // 设置单元格垂直居中对齐  
 57         cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
 58         // 创建单元格内容显示不下时自动换行  
 59         cellStyle.setWrapText(true);  
 60         // 设置单元格字体样式  
 61         XSSFFont font = wb.createFont();  
 62         // 设置字体加粗  
 63         font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  
 64         font.setFontName("宋体");  
 65         font.setFontHeight((short) 200);  
 66         cellStyle.setFont(font);  
 67         // 设置单元格边框为细线条  
 68         cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
 69         cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
 70         cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
 71         cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
 72         return cellStyle;  
 73     }  
 74   
 75     /** 
 76      * 设置表体的单元格样式 
 77      *  
 78      * @return 
 79      */  
 80     public XSSFCellStyle getBodyStyle()  
 81     {  
 82         // 创建单元格样式  
 83         XSSFCellStyle cellStyle = wb.createCellStyle();  
 84         // 设置单元格居中对齐  
 85         cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
 86         // 设置单元格垂直居中对齐  
 87         cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
 88         // 创建单元格内容显示不下时自动换行  
 89         cellStyle.setWrapText(true);  
 90         // 设置单元格字体样式  
 91         XSSFFont font = wb.createFont();  
 92         // 设置字体加粗  
 93         font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  
 94         font.setFontName("宋体");  
 95         font.setFontHeight((short) 200);  
 96         cellStyle.setFont(font);  
 97         // 设置单元格边框为细线条  
 98         cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
 99         cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
100         cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
101         cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
102         return cellStyle;  
103     }  
104 }  
105 service类  
106 public interface ITestExportExcelService  
107 {  
108     public void exportExcel(String hql,String [] titles,ServletOutputStream outputStream);  
109 }  
110 @Service  
111 public class TestExportExcelServiceImpl implements ITestExportExcelService  
112 {  
113     @Resource  
114     private ITestExportExcelDao dao;  
115       
116     public void exportExcel(String hql, String[] titles, ServletOutputStream outputStream)  
117     {  
118         List<Goods> list = dao.exportExcel(hql);  
119         // 创建一个workbook 对应一个excel应用文件  
120         XSSFWorkbook workBook = new XSSFWorkbook();  
121         // 在workbook中添加一个sheet,对应Excel文件中的sheet  
122         XSSFSheet sheet = workBook.createSheet("导出excel例子");  
123         ExportUtil exportUtil = new ExportUtil(workBook, sheet);  
124         XSSFCellStyle headStyle = exportUtil.getHeadStyle();  
125         XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();  
126         // 构建表头  
127         XSSFRow headRow = sheet.createRow(0);  
128         XSSFCell cell = null;  
129         for (int i = 0; i < titles.length; i++)  
130         {  
131             cell = headRow.createCell(i);  
132             cell.setCellStyle(headStyle);  
133             cell.setCellValue(titles[i]);  
134         }  
135         // 构建表体数据  
136         if (list != null && list.size() > 0)  
137         {  
138             for (int j = 0; j < list.size(); j++)  
139             {  
140                 XSSFRow bodyRow = sheet.createRow(j + 1);  
141                 Goods goods = list.get(j);  
142   
143                 cell = bodyRow.createCell(0);  
144                 cell.setCellStyle(bodyStyle);  
145                 cell.setCellValue(goods.getGoodsName());  
146   
147                 cell = bodyRow.createCell(1);  
148                 cell.setCellStyle(bodyStyle);  
149                 cell.setCellValue(goods.getGoodsCost());  
150   
151                 cell = bodyRow.createCell(2);  
152                 cell.setCellStyle(bodyStyle);  
153                 cell.setCellValue(goods.getGoodsUnit());  
154             }  
155         }  
156         try  
157         {  
158             workBook.write(outputStream);  
159             outputStream.flush();  
160             outputStream.close();  
161         }  
162         catch (IOException e)  
163         {  
164             e.printStackTrace();  
165         }  
166         finally  
167         {  
168             try  
169             {  
170                 outputStream.close();  
171             }  
172             catch (IOException e)  
173             {  
174                 e.printStackTrace();  
175             }  
176         }  
177   
178     }  
179   
180 }  
181 dao类  
182 public interface ITestExportExcelDao  
183 {  
184     public List<Goods> exportExcel(String hql);  
185 }  
186 @Repository  
187 public class TestExportExcelDaoImpl implements ITestExportExcelDao  
188 {  
189     @Resource  
190     private SessionFactory factory;  
191       
192     /** 
193      * 以goods表为例导出测试 
194      */  
195     @SuppressWarnings("unchecked")  
196     public List<Goods> exportExcel(String hql)  
197     {  
198         Session session = factory.getCurrentSession();  
199         List<Goods> list = session.createQuery(hql).list();  
200         return list;  
201     }  
202   
203 }  
204 控制层Controller  
205 @Controller  
206 @RequestMapping("/testexportexcel/*")  
207 public class TestExportExcelController  
208 {  
209     @Resource  
210     private ITestExportExcelService service;  
211   
212     @RequestMapping  
213     public String exportExcel(HttpServletResponse response)  
214     {  
215         response.setContentType("application/binary;charset=ISO8859_1");  
216         try  
217         {  
218             ServletOutputStream outputStream = response.getOutputStream();  
219             String fileName = new String(("导出excel例子").getBytes(), "ISO8859_1");  
220             response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");// 组装附件名称和格式  
221             String hql = "from Goods";  
222             String[] titles = { "商品名", "商品单价", "商品单位" };  
223             service.exportExcel(hql, titles, outputStream);  
224         }  
225         catch (IOException e)  
226         {  
227             e.printStackTrace();  
228         }  
229         return null;  
230     }  
231   
232     @RequestMapping  
233     public String upload(HttpServletRequest request, HttpServletResponse response)  
234     {  
235         MultipartHttpServletRequest mulRequest = (MultipartHttpServletRequest) request;  
236         MultipartFile file = mulRequest.getFile("excel");  
237         String filename = file.getOriginalFilename();  
238         if (filename == null || "".equals(filename))  
239         {  
240             return null;  
241         }  
242         try  
243         {  
244             InputStream input = file.getInputStream();  
245             XSSFWorkbook workBook = new XSSFWorkbook(input);  
246             XSSFSheet sheet = workBook.getSheetAt(0);  
247             if (sheet != null)  
248             {  
249                 for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++)  
250                 {  
251                     XSSFRow row = sheet.getRow(i);  
252                     for (int j = 0; j < row.getPhysicalNumberOfCells(); j++)  
253                     {  
254                         XSSFCell cell = row.getCell(j);  
255                         String cellStr = cell.toString();  
256                         System.out.print("【"+cellStr+"】 ");  
257                     }  
258                     System.out.println();  
259                 }  
260   
261             }  
262         }  
263         catch (Exception e)  
264         {  
265             e.printStackTrace();  
266         }  
267         return "/test/uploadExcel.jsp";  
268     }  
269   
270 }

 

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>

 

posted @ 2016-08-18 15:14  atzuge  阅读(17643)  评论(0)  编辑  收藏  举报