Spring学习笔记（三十三）：导入execl和导出execl并通过浏览器进行下载

execl导出并通过浏览器下载：
转自：http://www.cnblogs.com/bmbm/archive/2011/12/08/2342261.html
代码如下：

//第一步：获取导出数据
public static byte[] export(List<RechargeCard> list,HttpServletResponse response){
        // 创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet("充值卡");  
        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  

        HSSFCell cell = row.createCell(0);  
        cell.setCellValue("账号");  
        cell.setCellStyle(style);  
        cell = row.createCell( 1);  
        cell.setCellValue("密码");  
        cell.setCellStyle(style);  
        cell = row.createCell( 2);  
        cell.setCellValue("金额");  
        cell.setCellStyle(style);  
        cell = row.createCell( 3);  
        cell.setCellValue("批次");  
        cell.setCellStyle(style);  
        cell = row.createCell( 4);  
        cell.setCellValue("使用者");  
        cell.setCellStyle(style);  
        cell = row.createCell( 5);  
        cell.setCellValue("使用时间"); 
        cell.setCellStyle(style);  
        cell = row.createCell( 6);  
        cell.setCellValue("创建时间");   
        cell.setCellStyle(style);  
        cell = row.createCell( 7);  
        cell.setCellValue("截止时间");  
        cell.setCellStyle(style); 
        cell = row.createCell( 8);  
        cell.setCellValue("状态");  
        cell.setCellStyle(style); 

        // 写入实体数据  
        RechargeCard map = null;
        for (int i = 0; i < list.size(); i++)  
        {  
            map = list.get(i);
            row = sheet.createRow((int) i + 1);  
            // 创建单元格，并设置值  
            row.createCell( 0).setCellValue(map.getAmount());  
            row.createCell( 1).setCellValue(map.getPassword());  
            row.createCell( 2).setCellValue(map.getMoney()+"");  
            row.createCell( 3).setCellValue(map.getCode());  
            row.createCell( 4).setCellValue(map.getUsername());
            if(map.getUsedAt() != null)
                row.createCell( 5).setCellValue(map.getUsedAt());
            else
                row.createCell( 5).setCellValue("");
            row.createCell( 6).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(map.getCreatedAt()));  
            row.createCell( 7).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(map.getDeadline()));  
            row.createCell( 8).setCellValue(map.getStatus().equals("0")?"未使用":"已使用");  
            //cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format());  
        }  
        // 第六步，下载文件 
        ByteArrayOutputStream out = null;
        try  
        {  
            //方法一：指定下载路径
//          FileOutputStream fout = new FileOutputStream("/Users/jalon/Downloads/充值卡.xlsx");
//          wb.write(fout);  
            //方法二：直接通过浏览器进行默认下载
            out = new ByteArrayOutputStream();
            wb.write(out);
        }catch (Exception e) {
            e.printStackTrace();
            // TODO: handle exception
        }finally {
            if(out!=null){
                try {
                    out.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return out.toByteArray();
    }
    //第二步：在控制器的请求中，调用上面的方法
    //导出
    @RequestMapping("export")
    @ResponseBody
    public void export(HttpServletRequest request,HttpServletResponse response,HttpSession session,
            Model model) throws Exception{
        RechargeCard fc = new RechargeCard();
        fc.setRows(0);
        fc.setPageSize(999);
        List<RechargeCard> list = cardService.getListByMoreCondition(fc);
        byte[] bytes = ExeclUtil.export(list,response);
        response.setContentType("application/x-msdownload");
        response.setHeader("Content-Disposition", "attachment;filename=" + "123.xls");
        response.setContentLength(bytes.length);
        response.getOutputStream().write(bytes);
        response.getOutputStream().flush();
        response.getOutputStream().close();
    }
    //第三步：就是前端代码，点击一个链接，get请求即可

---

execl导入：
jar下载地址：http://www.mvnrepository.com/artifact/org.apache.poi/poi-ooxml/3.15

• 思路：先用MultipartFile上传文件，后台获取，转换成InputStream，创建Workbook（这里要判断File是2003版的xls还是2007版的xlsx，因为这两个的实现不同），然后循环获取行和列即可；
• 代码如下：

package com.prositech.v11j.utils;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import com.prositech.v11j.clothes.entity.Cloth;
import com.prositech.v11j.clothes.entity.ClothSize;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExeclUtil {

    private static int totalRows;
    private static int totalCells;

    //读取execl
    public static List<Cloth> readExecl(MultipartFile file){
        try{
            String name = file.getOriginalFilename();
            InputStream in = file.getInputStream();
            Workbook wb = null;
            if(name.contains("xlsx"))
                    wb = new XSSFWorkbook(in);
            else
                wb = new HSSFWorkbook(in);
            Sheet sheet=wb.getSheetAt(0);
            totalRows=sheet.getPhysicalNumberOfRows();
            if(totalRows>=1 && sheet.getRow(0) != null){
                totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
            }
            List<Cloth> list = new ArrayList<Cloth>();
            for(int i=1;i<totalRows;i++){
                //获取行
                Row row = sheet.getRow(i);
                if(row == null)
                    continue;
                Cloth cloth = new Cloth();
                ClothSize size = new ClothSize();
                cloth.setBrand(row.getCell((short) 0).getStringCellValue());
                cloth.setName(row.getCell((short) 1).getStringCellValue());
                cloth.setLinkurl(row.getCell((short) 2).getStringCellValue());
                size.setSizecode(row.getCell((short) 3).getStringCellValue());
                size.setJk((float) row.getCell((short)4).getNumericCellValue());
                size.setXw((float) row.getCell((short)5).getNumericCellValue());
                size.setYw((float) row.getCell((short)6).getNumericCellValue());
                size.setTw((float) row.getCell((short)7).getNumericCellValue());
                size.setXc((float) row.getCell((short)8).getNumericCellValue());
                size.setKc((float) row.getCell((short)9).getNumericCellValue());
                cloth.setClothSize(size);
                list.add(cloth);
            }
            return list;
        }catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            return null;
        }

    }
}