SpringMvc 使用poi导入导出Excel

转载自http://webook-java.iteye.com/blog/1699621

下面2个博客也不错，简洁明了

http://www.cnblogs.com/littlecharacter/p/5580676.html

http://blog.csdn.net/onepersontz/article/details/49891405

Util类  
package com.common.util;  
  
public class ExportUtil  
{  
    private XSSFWorkbook wb = null;  
  
    private XSSFSheet sheet = null;  
  
    /** 
     * @param wb 
     * @param sheet 
     */  
    public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet)  
    {  
        this.wb = wb;  
        this.sheet = sheet;  
    }  
  
    /** 
     * 合并单元格后给合并后的单元格加边框 
     *  
     * @param region 
     * @param cs 
     */  
    public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs)  
    {  
  
        int toprowNum = region.getFirstRow();  
        for (int i = toprowNum; i <= region.getLastRow(); i++)  
        {  
            XSSFRow row = sheet.getRow(i);  
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)  
            {  
                XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,  
                                                // (short) j);  
                cell.setCellStyle(cs);  
            }  
        }  
    }  
  
    /** 
     * 设置表头的单元格样式 
     *  
     * @return 
     */  
    public XSSFCellStyle getHeadStyle()  
    {  
        // 创建单元格样式  
        XSSFCellStyle cellStyle = wb.createCellStyle();  
        // 设置单元格的背景颜色为淡蓝色  
        cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);  
        cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);  
        // 设置单元格居中对齐  
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
        // 设置单元格垂直居中对齐  
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
        // 创建单元格内容显示不下时自动换行  
        cellStyle.setWrapText(true);  
        // 设置单元格字体样式  
        XSSFFont font = wb.createFont();  
        // 设置字体加粗  
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  
        font.setFontName("宋体");  
        font.setFontHeight((short) 200);  
        cellStyle.setFont(font);  
        // 设置单元格边框为细线条  
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
        return cellStyle;  
    }  
  
    /** 
     * 设置表体的单元格样式 
     *  
     * @return 
     */  
    public XSSFCellStyle getBodyStyle()  
    {  
        // 创建单元格样式  
        XSSFCellStyle cellStyle = wb.createCellStyle();  
        // 设置单元格居中对齐  
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
        // 设置单元格垂直居中对齐  
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);  
        // 创建单元格内容显示不下时自动换行  
        cellStyle.setWrapText(true);  
        // 设置单元格字体样式  
        XSSFFont font = wb.createFont();  
        // 设置字体加粗  
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);  
        font.setFontName("宋体");  
        font.setFontHeight((short) 200);  
        cellStyle.setFont(font);  
        // 设置单元格边框为细线条  
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);  
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);  
        return cellStyle;  
    }  
}  
service类  
public interface ITestExportExcelService  
{  
    public void exportExcel(String hql,String [] titles,ServletOutputStream outputStream);  
}  
@Service  
public class TestExportExcelServiceImpl implements ITestExportExcelService  
{  
    @Resource  
    private ITestExportExcelDao dao;  
      
    public void exportExcel(String hql, String[] titles, ServletOutputStream outputStream)  
    {  
        List<Goods> list = dao.exportExcel(hql);  
        // 创建一个workbook 对应一个excel应用文件  
        XSSFWorkbook workBook = new XSSFWorkbook();  
        // 在workbook中添加一个sheet,对应Excel文件中的sheet  
        XSSFSheet sheet = workBook.createSheet("导出excel例子");  
        ExportUtil exportUtil = new ExportUtil(workBook, sheet);  
        XSSFCellStyle headStyle = exportUtil.getHeadStyle();  
        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();  
        // 构建表头  
        XSSFRow headRow = sheet.createRow(0);  
        XSSFCell cell = null;  
        for (int i = 0; i < titles.length; i++)  
        {  
            cell = headRow.createCell(i);  
            cell.setCellStyle(headStyle);  
            cell.setCellValue(titles[i]);  
        }  
        // 构建表体数据  
        if (list != null && list.size() > 0)  
        {  
            for (int j = 0; j < list.size(); j++)  
            {  
                XSSFRow bodyRow = sheet.createRow(j + 1);  
                Goods goods = list.get(j);  
  
                cell = bodyRow.createCell(0);  
                cell.setCellStyle(bodyStyle);  
                cell.setCellValue(goods.getGoodsName());  
  
                cell = bodyRow.createCell(1);  
                cell.setCellStyle(bodyStyle);  
                cell.setCellValue(goods.getGoodsCost());  
  
                cell = bodyRow.createCell(2);  
                cell.setCellStyle(bodyStyle);  
                cell.setCellValue(goods.getGoodsUnit());  
            }  
        }  
        try  
        {  
            workBook.write(outputStream);  
            outputStream.flush();  
            outputStream.close();  
        }  
        catch (IOException e)  
        {  
            e.printStackTrace();  
        }  
        finally  
        {  
            try  
            {  
                outputStream.close();  
            }  
            catch (IOException e)  
            {  
                e.printStackTrace();  
            }  
        }  
  
    }  
  
}  
dao类  
public interface ITestExportExcelDao  
{  
    public List<Goods> exportExcel(String hql);  
}  
@Repository  
public class TestExportExcelDaoImpl implements ITestExportExcelDao  
{  
    @Resource  
    private SessionFactory factory;  
      
    /** 
     * 以goods表为例导出测试 
     */  
    @SuppressWarnings("unchecked")  
    public List<Goods> exportExcel(String hql)  
    {  
        Session session = factory.getCurrentSession();  
        List<Goods> list = session.createQuery(hql).list();  
        return list;  
    }  
  
}  
控制层Controller  
@Controller  
@RequestMapping("/testexportexcel/*")  
public class TestExportExcelController  
{  
    @Resource  
    private ITestExportExcelService service;  
  
    @RequestMapping  
    public String exportExcel(HttpServletResponse response)  
    {  
        response.setContentType("application/binary;charset=ISO8859_1");  
        try  
        {  
            ServletOutputStream outputStream = response.getOutputStream();  
            String fileName = new String(("导出excel例子").getBytes(), "ISO8859_1");  
            response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");// 组装附件名称和格式  
            String hql = "from Goods";  
            String[] titles = { "商品名", "商品单价", "商品单位" };  
            service.exportExcel(hql, titles, outputStream);  
        }  
        catch (IOException e)  
        {  
            e.printStackTrace();  
        }  
        return null;  
    }  
  
    @RequestMapping  
    public String upload(HttpServletRequest request, HttpServletResponse response)  
    {  
        MultipartHttpServletRequest mulRequest = (MultipartHttpServletRequest) request;  
        MultipartFile file = mulRequest.getFile("excel");  
        String filename = file.getOriginalFilename();  
        if (filename == null || "".equals(filename))  
        {  
            return null;  
        }  
        try  
        {  
            InputStream input = file.getInputStream();  
            XSSFWorkbook workBook = new XSSFWorkbook(input);  
            XSSFSheet sheet = workBook.getSheetAt(0);  
            if (sheet != null)  
            {  
                for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++)  
                {  
                    XSSFRow row = sheet.getRow(i);  
                    for (int j = 0; j < row.getPhysicalNumberOfCells(); j++)  
                    {  
                        XSSFCell cell = row.getCell(j);  
                        String cellStr = cell.toString();  
                        System.out.print("【"+cellStr+"】 ");  
                    }  
                    System.out.println();  
                }  
  
            }  
        }  
        catch (Exception e)  
        {  
            e.printStackTrace();  
        }  
        return "/test/uploadExcel.jsp";  
    }  
  
}