excel的导入导出

换工作一周了，之前一段时间在家休息，所以并没有记录博客，而这一住也很忙，今天记录一点简单一些的东西吧，关于excel的。

刚来就开始干活了，刚好在工作中有excl导入导出的功能需求，以前也做过，这次在这里记录一下吧，下次就不重复写了。

这是上传excel加解析 xls和xlsx是新旧两种excel

核心步骤：
//获取文件流
 InputStream is = file.getInputStream();
//将文件流转换为workbook，这是专门针对excel的工具类
 if (hz.toLowerCase().equals(".xls")) {
            wb = new HSSFWorkbook(is);// Excel 2003
        } else {
            wb = new XSSFWorkbook(is);// Excel 2007
        }
  }
  //获得sheet
  sheet = wb.getSheetAt(0);
  //获得某行
  Row row = sheet.getRow(i)
  //获得某行的某列
  row.getCell(i) 

/**
     * 导入excel
     * @param file
     * @return
     * @throws Exception
     */
    public  FileDTO importExcel(MultipartFile file) throws IOException  ,RuntimeException  {

        Workbook wb = null;
        Sheet sheet = null;
        fileName = file.getOriginalFilename();
        InputStream is = file.getInputStream();

        // 判断Excel版本
        logger.info("进入了上传方法,上传的文件名称是：" + fileName.toLowerCase());
        // 比较后缀
        String hz = fileName.substring(fileName.lastIndexOf("."),
                fileName.length());
        if (hz.toLowerCase().equals(".xls")) {
            wb = new HSSFWorkbook(is);// Excel 2003
        } else {
            wb = new XSSFWorkbook(is);// Excel 2007
        }
        sheet = wb.getSheetAt(0);
        int rowCount=sheet.getLastRowNum();
        logger.info("导入表中有{}条数据",rowCount );
        FileDTO handle=null;
        try {
            handle = this.excel2List(sheet)!=null ?  this.excel2List(sheet):new FileDTO();
            handle.setFilaName(fileName);
            handle.setRowCount(rowCount);
        } catch (RuntimeException e) {
            throw e;
        }
        return handle;
    }




protected FileDTO excel2List(Sheet sheet) {  
        FileDTO result =new FileDTO();
        //返回的结果
        List<RefundTmpImport> list = new ArrayList<>();
        //错误信息
        Set<String> setErrMes=new HashSet<>();
        RefundTmpImport refund = null;
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            try {
                Row row = sheet.getRow(i);
                refund = new RefundTmpImport();
                // 判空
                if (row == null) {
                    this.throwRuntimeException(i, null);
                }
                refund.setOrderNumber(row.getCell(0) != null ? row.getCell(0)
                        .toString() : null);
                //验证金额
                refund.setRefundAmt((Double)this.checkType(row.getCell(1), i, 2, DataType.D_DOUBLE4));
                //验证日期
                refund.setRefundTime(this.checkDateAndCell2object(row.getCell(2), i, 3, DateFormat.F_DATE));

                list.add(refund);
            } catch (ParseException e) {
                e.printStackTrace();
            } catch (RuntimeException e) {
                setErrMes.add(e.getMessage());
                e.printStackTrace();
            }
        }
        result.setErrorMes(setErrMes);
        result.setData(list);
        return result;
    }

导出这边只到xlsx

/**
     * 导出excel
     * @throws IOException 
     */
    public void exportExcel(HttpServletRequest request, HttpServletResponse response  ,List list) {

        HttpSession session = request.getSession();
        session.setAttribute("state", null);
        // 生成提示信息，
        response.setContentType("application/vnd.ms-excel");
        OutputStream fOut = null;
        try {
            // 进行转码，使其支持中文文件名
            //TODO  修改导出名称
            String codedFileName = java.net.URLEncoder.encode("test", "UTF-8");
            response.setHeader("content-disposition", "attachment;filename="
                    + codedFileName + ".xlsx");
            // 产生工作簿对象
            XSSFWorkbook workbook = new XSSFWorkbook();
            // 产生工作表对象
            XSSFSheet sheet = workbook.createSheet();
            // TODO　做导出的实现类
            fOut = response.getOutputStream();
            this.list2Excel(list, sheet,fOut,workbook);

        } catch (IOException e) {
            //jsonView.setFlag(0);
            logger.error(e.getMessage(), e);
        } finally {
            try {
                fOut.flush();
                fOut.close();
            } catch (IOException e) {
                //jsonView.setFlag(0);
                logger.error(e.getMessage(), e);
            }
            session.setAttribute("state", "open");
        }
        System.out.println("文件生成...");
    }
//转出对象
protected void list2Excel(List list, XSSFSheet sheet,OutputStream fOut,XSSFWorkbook workbook ) throws IOException {
        int i = 0;
        for (Object o : list) {
            ExportExcelDTO dto = (ExportExcelDTO) o;
            Row row = sheet.createRow((int) i);// 创建一行

            Cell cell0 = row.createCell((int) 0);// 创建一列
            cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell0.setCellValue(dto.getOrderNumber());
            i++;
        }
        workbook.write(fOut);

    }