Excel导入、导出

本篇是自己工作中梳理的总结，有不当之处请批评指正!!!

1 jar 包准备

2 引入ExcelUtil工具类

  public File getHSSFWorkbook(String sheetName, String[] title, String[][] values){
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet(sheetName);
    HSSFRow row = sheet.createRow(0);
    HSSFCellStyle style = wb.createCellStyle();
    HSSFCell cell = null;
    for (int i = 0; i < title.length; ++i) {
      cell = row.createCell(i);
      cell.setCellValue(title[i]);
      cell.setCellStyle(style);
    }
    for (i = 0; i < values.length; ++i) {
      row = sheet.createRow(i + 1);
      for (int j = 0; j < values[i].length; ++j)
      {
        row.createCell(j).setCellValue(values[i][j]);
      }
    }
    String export_path = PropKit.get("export_path"); System.out.println("export_path---" + export_path);
    deleteFile(export_path);
    String file_name = export_path + "/" + DateUtil.getCurrentDate();
    File f = new File(file_name);
    if (!(f.exists())) {
      f.mkdir();
    }
    file_name = file_name + "/" + System.currentTimeMillis() + ".xls"; System.out.println("file_name---" + file_name);
    File wfile = new File(file_name);
    try {
      FileOutputStream fos = new FileOutputStream(wfile);
      wb.write(fos);
    }
    catch (FileNotFoundException e) {
      e.printStackTrace();
    }
    catch (IOException e) {
      e.printStackTrace();
    }
    return wfile;
  }
  public void deleteFile(String path){
    try{
      File fileRoot = new File(path);
      if (fileRoot.exists()) {
        File[] fileAll = fileRoot.listFiles();
        for (File file : fileAll) {
          if ((!(file.isDirectory())) || 
            (!(file.getName().startsWith("2")))) continue;
          String nowDate = DateUtil.getCurrentDate();
          if (!(file.getName().equals(nowDate))) {
            new FileOperation().deleteDir(file.getPath());
          }
        }
      }
    }catch (Exception e){
      e.printStackTrace();
    }
  }
  public String getExcelContent2003(HSSFRow row, int index){
    String content = "";
    try {
      row.getCell(index).setCellType(CellType.STRING);
      content = row.getCell(index).getStringCellValue().trim();
    }catch (Exception e) {
      e.printStackTrace();
    }
    return content;
  }
  public String getExcelContent2007(XSSFRow row, int index){
    String content = "";
    try {
      row.getCell(index).setCellType(CellType.STRING);
      content = row.getCell(index).getStringCellValue().trim();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return content;
  } ```

  3 前端jsp
  <button type="button" class="btn btn-info" onclick="batchImportObject()">
      <span class="glyphicon glyphicon-import" aria-hidden="true"></span>导入
  </button>
  <button type="button" class="btn btn-info" onclick="exportObject()">
      <span class="glyphicon glyphicon-export" aria-hidden="true"></span>导出
  </button>
  
  function batchImportObject() {
      modelWindow.openFrameWindow("栏目统计数据导入",
	"/sys/cs/project/system/data/tongji/info/catinfo_import.html?cat_id="+ cat_id, 450, 300);
      }
   function exportObject() {
			window.open("/sys/cs/project/system/data/tongji/info/exportCatInfo/"+ cat_id);
      }

  4 java方法

      导出：
      public class ExportStatisticalInfo{
        public File exportStatisticalInfo(String cat_id){
          SqlSession s = IbatisSessionFactory.getInstance().openSession();
                try {
                  JSONObject catinfo = new CatCache().getCatJSONObject(cat_id);
                  String titles = "序号," + catinfo.getString("title_name");
                  for (int i = 0; i < catinfo.getIntValue("data_num"); ++i) {
                    titles = titles + "," + catinfo.getString(new StringBuilder("data_name").append(i + 1).toString());
                  }
                  List info_list = new StatisticalInfoDao().getAllStatisticalInfos(s, cat_id);
                  String[] title = titles.split(",");
                  System.out.println("note_list---" + JSON.toJSONString(info_list));

                  String sheetName = catinfo.getString("cat_fullname");
                  String[][] content = new String[info_list.size()][title.length];
                  int i = 0;
                  for (Map json : info_list) {
                    content[i][0] = (i + 1);
                    content[i][1] = json.get("title_data");
                    for (int j = 0; j < catinfo.getIntValue("data_num"); ++j) {
                      content[i][(j + 2)] = json.get(new StringBuilder("data").append(j + 1).toString());
                    }
                          ++i;
                    }

                        return new ExcelUtils().getHSSFWorkbook(sheetName, title, content);
                }catch (Exception e) {
                        e.printStackTrace();
                return null;
            } finally {
               s.close();
          }
        }
      }
      
      导入：
      public class ImportStatisticalInfo{
        public boolean importInfoData(String path, String cat_id, SettingLogsBean stl){
          boolean result = false;
          path = new UploadPicFunctions().getSavePath() + path;
          try {
            List reaultList = new ArrayList();
            if (path.toLowerCase().endsWith(".xls")) {
              result = readExcel2003(path, cat_id, stl);
                 break ; 
            } if (path.toLowerCase().endsWith(".xlsx"))
              result = readExcel2007(path, cat_id, stl);
          }catch (Exception e){
               e.printStackTrace();
          }
           return result;
        }

        public boolean readExcel2003(String path, String cat_id, SettingLogsBean stl){
                boolean rsflag = false;
                int index = 0;
                SqlSession s = IbatisSessionFactory.getInstance().openSession();

                CatBean catInfo = new CatCache().getCatBean(cat_id);
                if ((catInfo != null) && (!("".equals(catInfo)))) {
                  index = catInfo.getData_num();
                }
                StatisticalInfoDao statisticalInfoDao = new StatisticalInfoDao();
                try {
                  FileInputStream fis = new FileInputStream(path);
                  HSSFWorkbook workbook = new HSSFWorkbook(fis);
                  HSSFSheet sheet = workbook.getSheetAt(0);
                  ExcelUtils eUtil = new ExcelUtils();
                  Map cat_map = new HashMap();
                  cat_map.put("cat_id", cat_id);
                  cat_map.put("add_time", DateUtil.getCurrentDateTime());
                  cat_map.put("add_user", stl.getUser_id());
                  for (int i = 0; i < 20 - index; ++i) {
                         cat_map.put("data" + (index + 1 + i), "");
            }

            for (i = 1; i < sheet.getPhysicalNumberOfRows(); ++i) {
                    HSSFRow row = sheet.getRow(i);
                    String title_data = eUtil.getExcelContent2003(row, 1);
              if (!("".equals(title_data))) {
                for (int j = 0; j < index; ++j){
                  cat_map.put("data" + (j + 1), eUtil.getExcelContent2003(row, j + 2));
                }

                cat_map.put("title_data", title_data);
                cat_map.put("status", "0");
                cat_map.put("sort_id", "999");
                cat_map.put("memo", "");
                rsflag = statisticalInfoDao.insertStatisticalInfoMap(s, cat_map);
                if (!(rsflag)) {
                  break;
                }
              }
            }

                if (!(rsflag)) break label391;
                  PublicTableDAO.insertSettingLogs(s, "添加", "批量导入栏目信息", "", stl);
                  s.commit();
                }catch (Exception e){
                  e.printStackTrace();
                  s.rollback();
                }finally {
                  s.close();
                }
                return rsflag;
              }

              public boolean readExcel2007(String path, String cat_id, SettingLogsBean stl){
                boolean rsflag = false;
                int index = 0;
                SqlSession s = IbatisSessionFactory.getInstance().openSession();

                CatBean catInfo = new CatCache().getCatBean(cat_id);
                if ((catInfo != null) && (!("".equals(catInfo)))) {
                  index = catInfo.getData_num();
                }
                StatisticalInfoDao statisticalInfoDao = new StatisticalInfoDao();
                try {
                  FileInputStream fis = new FileInputStream(path);
                  XSSFWorkbook workbook = new XSSFWorkbook(fis);
                  XSSFSheet sheet = workbook.getSheetAt(0);
                  ExcelUtils eUtil = new ExcelUtils();
                  Map cat_map = new HashMap();
                  cat_map.put("cat_id", cat_id);
                  cat_map.put("add_time", DateUtil.getCurrentDateTime());
                  cat_map.put("add_user", stl.getUser_id());
                  for (int i = 0; i < 20 - index; ++i) {
                    cat_map.put("data" + (index + 1 + i), "");
                  }

                  for (i = 1; i < sheet.getPhysicalNumberOfRows(); ++i) {
                    XSSFRow row = sheet.getRow(i);
                    String title_data = eUtil.getExcelContent2007(row, 1);
                    if (!("".equals(title_data))) {
                      for (int j = 0; j < index; ++j){
                        cat_map.put("data" + (j + 1), eUtil.getExcelContent2007(row, j + 2));
                  }

                      cat_map.put("title_data", title_data);
                      cat_map.put("status", "0");
                      cat_map.put("sort_id", "999");
                      cat_map.put("memo", "");
                      rsflag = statisticalInfoDao.insertStatisticalInfoMap(s, cat_map);
                      if (!(rsflag)) {
                              break;
                      }
                    }
                  }

                  if (!(rsflag)) break label391;
                  PublicTableDAO.insertSettingLogs(s, "添加", "批量导入栏目信息", "", stl);
                        s.commit();
                  }catch (Exception e){
                        e.printStackTrace();
                        s.rollback();
                  }finally {
                        s.close();
                  }
                      return rsflag;
                  }
                }