spring mvc poi excel

Java代码  收藏代码
  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. }  
posted @ 2015-08-15 14:18  java高级技术汇  阅读(340)  评论(0编辑  收藏  举报