java之导入excel

2018-07-25 13:05:30来源：博客园阅读 ()

接口:

  1 /**
  2      * 
  3      * Description:  导入excel表 
  4      * @param map
  5      * @param request
  6      * @param session
  7      * @return
  8      * @author shiqianyu
  9      * @throws Exception 
 10      * @Data 2018年3月12日 下午2:40:52
 11      */
 12     @RequestMapping("/importExcelData")
 13     public ModelAndView importExcelData(ModelMap map,HttpServletRequest request,HttpSession session,@RequestParam(value = "file", required = true)MultipartFile multfile) throws Exception{  
 14          //获取页面信息
 15         Map<String, String> pageCrawlValue = StringUtil.getArrayValue(request.getParameterMap());
 16         pageCrawlValue.put("CRAWLRESULTPATH", pageCrawlValue.get("CRAWLRESULTPATH")+File.separator+pageCrawlValue.get("PAGECRAWLNAME"));
 17         PageCrawlTaskManage pageCrawlTaskManage = new PageCrawlTaskManage();
 18         pageCrawlTaskManage.setRegx(pageCrawlValue.get("PageRegx"));
 19         pageCrawlTaskManage.setPage_prdfix(pageCrawlValue.get("PAGE_PRDFIX"));
 20         pageCrawlTaskManage.setCtCycle(pageCrawlValue.get("CTCYCLE"));
 21         pageCrawlTaskManage.setCrawlResultPath(pageCrawlValue.get("CRAWLRESULTPATH"));
 22         pageCrawlTaskManage.setProxyDeployId(pageCrawlValue.get("PROXYID"));
 23         pageCrawlTaskManage.setCrawlIntervalTime(Integer.valueOf(pageCrawlValue.get("CRAWL_INTERVAL_TIME")));
 24         pageCrawlTaskManage.setPageCrawlName(pageCrawlValue.get("PAGECRAWLNAME"));
 25         pageCrawlTaskManage.setIsDrill(pageCrawlValue.get("isDrill"));
 26         pageCrawlTaskManage.setCtCycleUnit(pageCrawlValue.get("CTCYCLEUNIT"));
 27         pageCrawlTaskManage.setDataSourceId(pageCrawlValue.get("DATASOURCEID"));
 28         pageCrawlTaskManage.setPage_start(pageCrawlValue.get("PAGE_START"));
 29         pageCrawlTaskManage.setPage_end(pageCrawlValue.get("PAGE_END"));
 30         pageCrawlTaskManage.setDescribe(pageCrawlValue.get("describe"));
 31         pageCrawlTaskManage.setThreadCount(pageCrawlValue.get("CTTHREADCOUNT"));
 32         pageCrawlTaskManage.setPage_suffix(pageCrawlValue.get("PAGE_SUFFIX"));
 33         
 34         int startRow = Integer.parseInt(pageCrawlValue.get("startRow"))-1;//导入excel 起始行号 -3
 35         int endRow = Integer.parseInt(pageCrawlValue.get("endRow"))-1; //导入excel 结束行号 -3
 36         int column = Integer.parseInt(pageCrawlValue.get("column"))-1; //导入 excel指定的列号 -1
 37         
 38         //获取页码后缀
 39         List<String> suffixs = new ArrayList<String>();
 40         
 41         
 42         //检查文件
 43         ExcelData.checkFile(multfile);
 44          //获得Workbook工作薄对象
 45         Workbook workbook = ExcelData.getWorkBook(multfile);
 46         //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
 47         List<String[]> list = new ArrayList<String[]>();
 48         if(workbook != null){
 49             for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
 50                 //获得当前sheet工作表
 51                 Sheet sheet = workbook.getSheetAt(sheetNum);
 52                 if(sheet == null){
 53                     continue; 
 54                 }
 55                 //获得当前sheet的开始行
 56                 int firstRowNum  = startRow;
 57                 //获得当前sheet的结束行
 58                 int lastRowNum = endRow;
 59                 //循环行
 60                 for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){ //firstRowNum+1;
 61                     //获得当前行
 62                     Row row = sheet.getRow(rowNum);
 63                     if(row == null){
 64                         continue;
 65                     }
 66                     //获得当前行的开始列
 67                     int firstCellNum = row.getFirstCellNum();
 68                     //获得当前行的列数
 69                     int lastCellNum = row.getLastCellNum();
 70                     String[] cells = new String[row.getLastCellNum()];
 71                     //循环当前行
 72                     for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
 73                         if(cellNum==column){
 74                             Cell cell = row.getCell(cellNum);
 75                             cells[cellNum] = ExcelData.getCellValue(cell);
 76                         }
 77                         
 78                     }
 79                     list.add(cells);
 80                 }
 81             }
 82         }
 83         
 84        
 85         for(int i= 0;i<list.size();i++){//startRow
 86             String[] arr=null;
 87             arr = list.get(i);//[张飞, As255, c2, d2, f2]
 88             if(arr!=null){
 89                 //遍历列
 90                 for(int j=0;j<arr.length;j++){
 91                     if(j==column){
 92                         suffixs.add(arr[j]);//As255
 93                     }
 94                 }
 95             }
 96         }
 97          
 98         map.put("pageCrawlTaskManage", pageCrawlTaskManage);
 99         map.put("times",suffixs); 
100         return new ModelAndView("crawl/CrawlTaskToExcel");
101     }

View Code

工具类:

  1 package com.dimensoft.splider.util;
  2 
  3 import java.io.IOException;
  4 import java.io.InputStream;
  5 import java.text.DecimalFormat;
  6 import java.text.SimpleDateFormat;
  7 import java.util.ArrayList;
  8 import java.util.Date;
  9 import java.util.List;
 10 
 11 import org.apache.log4j.Logger;
 12 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
 13 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 14 import org.apache.poi.ss.usermodel.Cell;
 15 import org.apache.poi.ss.usermodel.CellStyle;
 16 import org.apache.poi.ss.usermodel.Row;
 17 import org.apache.poi.ss.usermodel.Sheet;
 18 import org.apache.poi.ss.usermodel.Workbook;
 19 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 20 import org.springframework.web.multipart.MultipartFile;
 21  
 22 
 23 /**
 24  * 解析excel 上传数据
 25  * @author shiqianyu
 26  *
 27  */
 28 public class ExcelData {
 29     
 30     private static final Logger log = Logger.getLogger(ExcelData.class);
 31     
 32     public static List<String[]> getExcelData(MultipartFile file) throws IOException{
 33         checkFile(file);
 34          //获得Workbook工作薄对象
 35         Workbook workbook = getWorkBook(file);
 36         //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
 37         List<String[]> list = new ArrayList<String[]>();
 38         if(workbook != null){
 39             for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
 40                 //获得当前sheet工作表
 41                 Sheet sheet = workbook.getSheetAt(sheetNum);
 42                 if(sheet == null){
 43                     continue; 
 44                 }
 45                 //获得当前sheet的开始行
 46                 int firstRowNum  = sheet.getFirstRowNum();
 47                 //获得当前sheet的结束行
 48                 int lastRowNum = sheet.getLastRowNum();
 49                 //循环除了第一行的所有行
 50                 for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
 51                     //获得当前行
 52                     Row row = sheet.getRow(rowNum);
 53                     if(row == null){
 54                         continue;
 55                     }
 56                     //获得当前行的开始列
 57                     int firstCellNum = row.getFirstCellNum();
 58                     //获得当前行的列数
 59                     int lastCellNum = row.getLastCellNum();
 60                     String[] cells = new String[row.getLastCellNum()];
 61                     //循环当前行
 62                     for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
 63                         Cell cell = row.getCell(cellNum);
 64                         cells[cellNum] = getCellValue(cell);
 65                     }
 66                     list.add(cells);
 67                 }
 68             }
 69         }
 70         return list;
 71     }
 72 
 73     
 74     /**
 75      * 检查文件
 76      * @param file
 77      * @throws IOException
 78      */
 79      public static  void checkFile(MultipartFile file) throws IOException{
 80          //判断文件是否存在
 81          if(null == file){
 82              log.error("文件不存在！");
 83          }
 84          //获得文件名
 85          String fileName = file.getOriginalFilename();
 86          //判断文件是否是excel文件
 87          if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
 88              log.error(fileName + "不是excel文件");
 89          }
 90      }
 91      
 92      
 93      public static  Workbook getWorkBook(MultipartFile file) {
 94          //获得文件名
 95          String fileName = file.getOriginalFilename();
 96          //创建Workbook工作薄对象，表示整个excel
 97          Workbook workbook = null;
 98          try {
 99              //获取excel文件的io流
100              InputStream is = file.getInputStream();
101              //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
102              if(fileName.endsWith("xls")){
103                  //2003
104                  workbook = new HSSFWorkbook(is);
105              }else if(fileName.endsWith("xlsx")){
106                  //2007 及2007以上
107                  workbook = new XSSFWorkbook(is);
108              }
109          } catch (IOException e) {
110              log.error(e.getMessage());
111          }
112          return workbook;
113      }
114      
115      public static String getCellValue(Cell cell){
116          String cellValue = "";
117          if(cell == null){
118              return cellValue;
119          }
120      //判断数据的类型
121          switch (cell.getCellType()){
122              case Cell.CELL_TYPE_NUMERIC: //数字
123                  cellValue = stringDateProcess(cell);
124                  break;
125              case Cell.CELL_TYPE_STRING: //字符串
126                  cellValue = String.valueOf(cell.getStringCellValue());
127                  break;
128              case Cell.CELL_TYPE_BOOLEAN: //Boolean
129                  cellValue = String.valueOf(cell.getBooleanCellValue());
130                  break;
131              case Cell.CELL_TYPE_FORMULA: //公式
132                  cellValue = String.valueOf(cell.getCellFormula());
133                  break;
134              case Cell.CELL_TYPE_BLANK: //空值
135                  cellValue = "";
136                  break;
137              case Cell.CELL_TYPE_ERROR: //故障
138                  cellValue = "非法字符";
139                  break;
140              default:
141                  cellValue = "未知类型";
142                  break;
143          }
144          return cellValue;
145      }
146      
147      /**
148       * 时间格式处理
149       * @return
150       * @author Liu Xin Nan
151       * @data 2017年11月27日
152       */
153      public static String stringDateProcess(Cell cell){
154          String result = new String();  
155          if (true) {// 处理日期格式、时间格式  
156              SimpleDateFormat sdf = null;  
157              if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {  
158                  sdf = new SimpleDateFormat("HH:mm");  
159              } else {// 日期  
160                  sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");  
161              }  
162              Date date = cell.getDateCellValue();  
163              result = sdf.format(date);  
164          } else if (cell.getCellStyle().getDataFormat() == 58) {  
165              // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
166              SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");  
167              double value = cell.getNumericCellValue();  
168              Date date = org.apache.poi.ss.usermodel.DateUtil  
169                      .getJavaDate(value);  
170              result = sdf.format(date);  
171          } else {  
172              double value = cell.getNumericCellValue();  
173              CellStyle style = cell.getCellStyle();  
174              DecimalFormat format = new DecimalFormat();  
175              String temp = style.getDataFormatString();  
176              // 单元格设置成常规  
177              if (temp.equals("General")) {  
178                  format.applyPattern("#");  
179              }  
180              result = format.format(value);  
181          }  
182          
183          return result;
184      }
185 }