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;        }    }}