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更加炫酷。