SpringMVC 使用poi导入导出Excel

Util类:

  1 public class ExportUtil  
  2 {  
  3     private XSSFWorkbook wb = null;  
  4   
  5     private XSSFSheet sheet = null;  
  6   
  7     /** 
  8      * @param wb 
  9      * @param sheet 
 10      */  
 11     public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet)  
 12     {  
 13         this.wb = wb;  
 14         this.sheet = sheet;  
 15     }  
 16   
 17     /** 
 18      * 合并单元格后给合并后的单元格加边框 
 19      *  
 20      * @param region 
 21      * @param cs 
 22      */  
 23     public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs)  
 24     {  
 25   
 26         int toprowNum = region.getFirstRow();  
 27         for (int i = toprowNum; i <= region.getLastRow(); i++)  
 28         {  
 29             XSSFRow row = sheet.getRow(i);  
 30             for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)  
 31             {  
 32                 XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,  
 33                                                 // (short) j);  
 34                 cell.setCellStyle(cs);  
 35             }  
 36         }  
 37     }  
 38   
 39     /** 
 40      * 设置表头的单元格样式 
 41      *  
 42      * @return 
 43      */  
 44     public XSSFCellStyle getHeadStyle()  
 45     {  
 46         // 创建单元格样式  
 47         XSSFCellStyle cellStyle = wb.createCellStyle();  
 48         // 设置单元格的背景颜色为淡蓝色  
 49         cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);  
 50         cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);  
 51         // 设置单元格居中对齐  
 52         cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
 53         // 设置单元格垂直居中对齐  
 54         cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
 55         // 创建单元格内容显示不下时自动换行  
 56         cellStyle.setWrapText(true);  
 57         // 设置单元格字体样式  
 58         XSSFFont font = wb.createFont();  
 59         // 设置字体加粗  
 60         font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  
 61         font.setFontName("宋体");  
 62         font.setFontHeight((short) 200);  
 63         cellStyle.setFont(font);  
 64         // 设置单元格边框为细线条  
 65         cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
 66         cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
 67         cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
 68         cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
 69         return cellStyle;  
 70     }  
 71   
 72     /** 
 73      * 设置表体的单元格样式 
 74      *  
 75      * @return 
 76      */  
 77     public XSSFCellStyle getBodyStyle()  
 78     {  
 79         // 创建单元格样式  
 80         XSSFCellStyle cellStyle = wb.createCellStyle();  
 81         // 设置单元格居中对齐  
 82         cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
 83         // 设置单元格垂直居中对齐  
 84         cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
 85         // 创建单元格内容显示不下时自动换行  
 86         cellStyle.setWrapText(true);  
 87         // 设置单元格字体样式  
 88         XSSFFont font = wb.createFont();  
 89         // 设置字体加粗  
 90         font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  
 91         font.setFontName("宋体");  
 92         font.setFontHeight((short) 200);  
 93         cellStyle.setFont(font);  
 94         // 设置单元格边框为细线条  
 95         cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
 96         cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
 97         cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
 98         cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
 99         return cellStyle;  
100     }  
101 }
View Code

service类 :

 1 public interface ITestExportExcelService  
 2 {  
 3     public void exportExcel(String hql,String [] titles,ServletOutputStream outputStream);  
 4 }  
 5 @Service  
 6 public class TestExportExcelServiceImpl implements ITestExportExcelService  
 7 {  
 8     @Resource  
 9     private ITestExportExcelDao dao;  
10       
11     public void exportExcel(String hql, String[] titles, ServletOutputStream outputStream)  
12     {  
13         List<Goods> list = dao.exportExcel(hql);  
14         // 创建一个workbook 对应一个excel应用文件  
15         XSSFWorkbook workBook = new XSSFWorkbook();  
16         // 在workbook中添加一个sheet,对应Excel文件中的sheet  
17         XSSFSheet sheet = workBook.createSheet("导出excel例子");  
18         ExportUtil exportUtil = new ExportUtil(workBook, sheet);  
19         XSSFCellStyle headStyle = exportUtil.getHeadStyle();  
20         XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();  
21         // 构建表头  
22         XSSFRow headRow = sheet.createRow(0);  
23         XSSFCell cell = null;  
24         for (int i = 0; i < titles.length; i++)  
25         {  
26             cell = headRow.createCell(i);  
27             cell.setCellStyle(headStyle);  
28             cell.setCellValue(titles[i]);  
29         }  
30         // 构建表体数据  
31         if (list != null && list.size() > 0)  
32         {  
33             for (int j = 0; j < list.size(); j++)  
34             {  
35                 XSSFRow bodyRow = sheet.createRow(j + 1);  
36                 Goods goods = list.get(j);  
37   
38                 cell = bodyRow.createCell(0);  
39                 cell.setCellStyle(bodyStyle);  
40                 cell.setCellValue(goods.getGoodsName());  
41   
42                 cell = bodyRow.createCell(1);  
43                 cell.setCellStyle(bodyStyle);  
44                 cell.setCellValue(goods.getGoodsCost());  
45   
46                 cell = bodyRow.createCell(2);  
47                 cell.setCellStyle(bodyStyle);  
48                 cell.setCellValue(goods.getGoodsUnit());  
49             }  
50         }  
51         try  
52         {  
53             workBook.write(outputStream);  
54             outputStream.flush();  
55             outputStream.close();  
56         }  
57         catch (IOException e)  
58         {  
59             e.printStackTrace();  
60         }  
61         finally  
62         {  
63             try  
64             {  
65                 outputStream.close();  
66             }  
67             catch (IOException e)  
68             {  
69                 e.printStackTrace();  
70             }  
71         }  
72   
73     }  
74   
75 }
View Code

dao类:

 1 public interface ITestExportExcelDao  
 2 {  
 3     public List<Goods> exportExcel(String hql);  
 4 }  
 5 @Repository  
 6 public class TestExportExcelDaoImpl implements ITestExportExcelDao  
 7 {  
 8     @Resource  
 9     private SessionFactory factory;  
10       
11     /** 
12      * 以goods表为例导出测试 
13      */  
14     @SuppressWarnings("unchecked")  
15     public List<Goods> exportExcel(String hql)  
16     {  
17         Session session = factory.getCurrentSession();  
18         List<Goods> list = session.createQuery(hql).list();  
19         return list;  
20     }  
21   
22 }
View Code

controller类:

 1 @Controller  
 2 @RequestMapping("/testexportexcel/*")  
 3 public class TestExportExcelController  
 4 {  
 5     @Resource  
 6     private ITestExportExcelService service;  
 7   
 8     @RequestMapping  
 9     public String exportExcel(HttpServletResponse response)  
10     {  
11         response.setContentType("application/binary;charset=ISO8859_1");  
12         try  
13         {  
14             ServletOutputStream outputStream = response.getOutputStream();  
15             String fileName = new String(("导出excel例子").getBytes(), "ISO8859_1");  
16             response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");// 组装附件名称和格式  
17             String hql = "from Goods";  
18             String[] titles = { "商品名", "商品单价", "商品单位" };  
19             service.exportExcel(hql, titles, outputStream);  
20         }  
21         catch (IOException e)  
22         {  
23             e.printStackTrace();  
24         }  
25         return null;  
26     }  
27   
28     @RequestMapping  
29     public String upload(HttpServletRequest request, HttpServletResponse response)  
30     {  
31         MultipartHttpServletRequest mulRequest = (MultipartHttpServletRequest) request;  
32         MultipartFile file = mulRequest.getFile("excel");  
33         String filename = file.getOriginalFilename();  
34         if (filename == null || "".equals(filename))  
35         {  
36             return null;  
37         }  
38         try  
39         {  
40             InputStream input = file.getInputStream();  
41             XSSFWorkbook workBook = new XSSFWorkbook(input);  
42             XSSFSheet sheet = workBook.getSheetAt(0);  
43             if (sheet != null)  
44             {  
45                 for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++)  
46                 {  
47                     XSSFRow row = sheet.getRow(i);  
48                     for (int j = 0; j < row.getPhysicalNumberOfCells(); j++)  
49                     {  
50                         XSSFCell cell = row.getCell(j);  
51                         String cellStr = cell.toString();  
52                         System.out.print("【"+cellStr+"】 ");  
53                     }  
54                     System.out.println();  
55                 }  
56   
57             }  
58         }  
59         catch (Exception e)  
60         {  
61             e.printStackTrace();  
62         }  
63         return "/test/uploadExcel.jsp";  
64     }  
65   
66 }
View Code

 

posted @ 2015-08-21 12:02  Tobey唐  阅读(1268)  评论(0)  编辑  收藏  举报