excel导入与导出(markdown版本）

前段时间要做一个excel表格的上传下载功能，上网找了各种资料及源码，最后借用别人的代码及思路完成了符合自己需求的代码，总结一下excel的上传下载实质是通过第三方的类库比如poi，jxl等对excel单元格的设置与读写，通过类库的api，你可以发现各种各样的操作excel的方法，基本上只要你能在office的excel上进行的基本操作通过代码都能实现。

上传文件：

首先新建一个工具类，我命名为ImportExcelUtil，代码如下：

public class ImportExcelUtil {  

    private final static String excel2003L =".xls";    //2003- 版本的excel  
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel  

    /** 
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象 
     * @param in,fileName 
     * @return 
     * @throws IOException  
     */  
    public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{  
        List<List<Object>> list = null;  

        //创建Excel工作薄  
        Workbook work = this.getWorkbook(in,fileName);  
        if(null == work){  
            throw new Exception("创建Excel工作薄为空！");  
        }  
        Sheet sheet = null;  
        Row row = null;  
        Cell cell = null;  

        list = new ArrayList<List<Object>>();  
        //遍历Excel中所有的sheet  
        int s=work.getNumberOfSheets();  
        for (int i = 0; i < work.getNumberOfSheets(); i++) {  
            sheet = work.getSheetAt(i);  
            if(sheet==null){continue;}  
            int s2=sheet.getLastRowNum();  
            //遍历当前sheet中的所有行  
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {  
                row = sheet.getRow(j);  
                if(row==null||row.getFirstCellNum()==j){continue;}  

                //遍历所有的列  
                List<Object> li = new ArrayList<Object>();  
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {  
                    cell = row.getCell(y);  
                    li.add(this.getCellValue(cell));  
                }  
                list.add(li);  
            }  
        }  
        work.close();  
        return list;  
    }  

    /** 
     * 描述：根据文件后缀，自适应上传文件的版本  
     * @param inStr,fileName 
     * @return 
     * @throws Exception 
     */  
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
        Workbook wb = null;  
        String fileType = fileName.substring(fileName.lastIndexOf("."));  
        if(excel2003L.equals(fileType)){  
            wb = new HSSFWorkbook(inStr);  //2003-  
        }else if(excel2007U.equals(fileType)){  
            wb = new XSSFWorkbook(inStr);  //2007+  
        }else{  
            throw new Exception("解析的文件格式有误！");  
        }  
        return wb;  
    }  

    /** 
     * 描述：对表格中数值进行格式化 
     * @param cell 
     * @return 
     */  
    public  Object getCellValue(Cell cell){  
        Object value = null;  
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符  
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd");  //日期格式化  
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字  

        switch (cell.getCellType()) {  
        case Cell.CELL_TYPE_STRING:  
            value = cell.getRichStringCellValue().getString();  
            break;  
        case Cell.CELL_TYPE_NUMERIC:  
            if("General".equals(cell.getCellStyle().getDataFormatString())){  
                value = df.format(cell.getNumericCellValue());  
            }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){  
                value = sdf.format(cell.getDateCellValue());  
            }else{  
                value = df2.format(cell.getNumericCellValue());  
            }  
            break;  
        case Cell.CELL_TYPE_BOOLEAN:  
            value = cell.getBooleanCellValue();  
            break;  
        case Cell.CELL_TYPE_BLANK:  
            value = "";  
            break;  
        default:  
            break;  
        }  
        return value;  
    }  
}

这个工具类是支持2003和2007的，excel2003和2007所用到的包是不一样的，所以如何你的工程师maven构建的话需要在pom.xml上引入下面两个包：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->  
<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi</artifactId>  
    <version>3.14</version>  
</dependency>  
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->  
<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi-ooxml</artifactId>  
    <version>3.14</version>  
</dependency>  

工具类写好以后，接下来就可以在具体业务逻辑实现方法里调用了，我这边有个上传的controller

public class UploadExcelControl {  
    @Autowired  
    DetailInfoVoService detailInfoVoService;  
    /** 
     * 描述：通过传统方式form表单提交方式导入excel文件 
     * @param request 
     * @throws Exception 
     */  
    @RequestMapping(value="upload.do",method={RequestMethod.GET,RequestMethod.POST})  
    public  String  uploadExcel(HttpServletRequest request) throws Exception {  
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;    
        System.out.println("通过传统方式form表单提交方式导入excel文件！");  

        InputStream in =null;  
        List<List<Object>> listob = null;  
        MultipartFile file = multipartRequest.getFile("upfile");  
        if(file.isEmpty()){  
            throw new Exception("文件不存在！");  
        }  
        in = file.getInputStream();  
        listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename());  
        in.close();  
        //因为要导入的excel比较特殊，不是常规的一行类别，其他行都是数据，所以我需要根据具体模板来读取excel  
        for (int i = 0; i < listob.size(); i+=11) {  
            DetailInfoVo vo = new DetailInfoVo();  
            vo.setSiteId(String .valueOf(listob.get(i).get(1)));  
            vo.setSiteName(String.valueOf(listob.get(i).get(3)));  
            vo.setSitePhone(String.valueOf(listob.get(i).get(5)));  
            vo.setSiteAddress(String.valueOf(listob.get(i+1).get(1)));  
            vo.setIsOpened(String.valueOf(listob.get(i+1).get(5)));  
            vo.setSiteClass(String.valueOf(listob.get(i+2).get(1)).split("，")[0]);  
            vo.setSiteScale(String.valueOf(listob.get(i+2).get(1)).split("，")[1]);  
            vo.setSiteDetail(String.valueOf(listob.get(i+3).get(0)));  
            vo.setIsPartnership(String.valueOf(listob.get(i+4).get(1)));  
            vo.setReason(String.valueOf(listob.get(i+5).get(0)));  
            vo.setBossName(String.valueOf(listob.get(i+7).get(1)));  
            vo.setBossAge(String.valueOf(listob.get(i+7).get(3)));  
            vo.setBossJiguan(String.valueOf(listob.get(i+7).get(5)));  
            vo.setBossYear(String.valueOf(listob.get(i+8).get(1)));  
            vo.setBossPeople(String.valueOf(listob.get(i+8).get(3)));  
            vo.setOthers(String.valueOf(listob.get(i+9).get(1)));  
            vo.setEventLog(String.valueOf(listob.get(i+10).get(0)));  
            int resultCode=detailInfoVoService.insert(vo);  
            if(resultCode!=0){  
                System.out.println("=======");  
            }  
        }  
        return "result";  
    }  
}  

前台使用的是form表单提交的方式，首先在jsp页面定义一个form，action指向对应的controller

<form class="form-inline clearfix " method="POST" enctype="multipart/form-data" id="form1" action="../uploadExcel/upload.do">  
          <div class="row">  
            <div class="form-group col-xs-12">  
              <label class="fl">上传文件: </label>  
              <input type="file" class="form-control" id="upfile" name="upfile">  
            </div>  
       </div>  
</form>  

上传的内容就介绍完了，相比上传，下载更加容易。

导出excel:

新建一个工具类ExportExcelUtil：

public class ExportExcelUtil {  

    /** 
     * 描述：根据文件路径获取项目中的文件 
     * @param fileDir 文件路径 
     * @return 
     * @throws Exception 
     */  
    public  File getExcelDemoFile(String fileDir) throws Exception{  
        String classDir = null;  
        String fileBaseDir = null;  
        File file = null;  
        classDir = Thread.currentThread().getContextClassLoader().getResource("/").getPath();  
        fileBaseDir = classDir.substring(0, classDir.lastIndexOf("classes"));  

        file = new File(fileBaseDir+fileDir);  
        if(!file.exists()){  
            throw new Exception("模板文件不存在！");  
        }  
        return file;  
    }  

    public  Workbook writeNewExcel(File file,String sheetName,List<DetailInfoVo> lis) throws Exception{  
        Workbook wb = null;  
        Row row = null;   
        Cell cell = null;  

        FileInputStream fis = new FileInputStream(file);  
        wb = new ImportExcelUtil().getWorkbook(fis, file.getName());    //获取工作薄  
        Sheet sheet = wb.getSheet(sheetName);  

        //循环插入数据  
        int lastRow = sheet.getLastRowNum()+1;    //插入数据的数据ROW  
        CellStyle cs = setSimpleCellStyle(wb);    //Excel单元格样式  
        for (int i = 0; i < lis.size(); i++) {  
            row = sheet.createRow(lastRow+i); //创建新的ROW，用于数据插入  

            //按项目实际需求，在该处将对象数据插入到Excel中  
            DetailInfoVo vo  = lis.get(i);  
            if(null==vo){  
                break;  
            }  
            //Cell赋值开始  
            cell = row.createCell(0);  
            cell.setCellValue(vo.getSiteId());  
            cell.setCellStyle(cs);  

            cell = row.createCell(1);  
            cell.setCellValue(vo.getSiteName());  
            cell.setCellStyle(cs);  

            cell = row.createCell(2);  
            cell.setCellValue(vo.getSiteAddress());  
            cell.setCellStyle(cs);  

            cell = row.createCell(3);  
            cell.setCellValue(vo.getSiteClass());  
            cell.setCellStyle(cs);  

            cell = row.createCell(4);  
            cell.setCellValue(vo.getSiteScale());  
            cell.setCellStyle(cs);  

            cell = row.createCell(5);  
            cell.setCellValue(vo.getIsPartnership());  
            cell.setCellStyle(cs);  

            cell = row.createCell(6);  
            cell.setCellValue(vo.getSitePhone());  
            cell.setCellStyle(cs);  

            cell = row.createCell(7);  
            cell.setCellValue(vo.getIsOpened());  
            cell.setCellStyle(cs);  
        }  
        return wb;  
    }  

    /** 
     * 描述：设置简单的Cell样式 
     * @return 
     */  
    public  CellStyle setSimpleCellStyle(Workbook wb){  
        CellStyle cs = wb.createCellStyle();  

        cs.setBorderBottom(CellStyle.BORDER_THIN); //下边框  
        cs.setBorderLeft(CellStyle.BORDER_THIN);//左边框  
        cs.setBorderTop(CellStyle.BORDER_THIN);//上边框  
        cs.setBorderRight(CellStyle.BORDER_THIN);//右边框  

        cs.setAlignment(CellStyle.ALIGN_CENTER); // 居中  

        return cs;  
    }  

}  

具体的实现类如下

public class ExportExcelControl {  
    @Autowired  
    DetailInfoVoService detailInfoVoService;  
    /** 
     * 描述：导出Excel 
     * @param request 
     * @param response 
     * @throws Exception 
     */  
    @RequestMapping(value="export.do",method={RequestMethod.GET,RequestMethod.POST})  
    public  String  exportExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {  
        OutputStream os = null;    
        Workbook wb = null;    //工作薄  
        List<DetailInfoVo> list=detailInfoVoService.findList();  
        try {  
            List<DetailInfoVo> lo = new ArrayList<DetailInfoVo>();  
            for (int i = 0; i < list.size(); i++) {  
                //根据具体需求向excel即将导出的vo模型填充数据  
                DetailInfoVo vo = new DetailInfoVo();  
                vo.setSiteId(list.get(i).getSiteId());  
                vo.setSiteName(list.get(i).getSiteName());  
                vo.setSiteAddress(list.get(i).getSiteAddress());  
                vo.setSiteClass(list.get(i).getSiteClass());  
                vo.setSiteScale(list.get(i).getSiteScale());  
                vo.setIsPartnership(list.get(i).getIsPartnership());  
                vo.setSitePhone(list.get(i).getSitePhone());  
                vo.setIsOpened(list.get(i).getIsOpened());  
                lo.add(vo);  
            }  

            //导出Excel文件数据  
            ExportExcelUtil util = new ExportExcelUtil();  
            File file =util.getExcelDemoFile("/ExcelDemoFile/模板.xlsx");  
            String sheetName="sheet1";    
            wb = util.writeNewExcel(file, sheetName,lo);   

            String fileName="导出信息.xlsx";  
            response.setContentType("application/vnd.ms-excel");  
            response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));  
            os = response.getOutputStream();  
            wb.write(os);    
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        finally{  
            os.flush();  
            os.close();  
            wb.close();  
        }   
        return null;  
    }  


}  

更详细的代码我就不贴了，主要是上传下载这个思路大家要理清，原理搞清楚了啥都会搞了，比如加特技，通过poi提供的api让你的excel更加炫酷。