Java 通过Poi解析数据到EXCEL

爱被打了一巴掌 2021-10-13 15:30 369阅读 0赞

> 傻瓜式操作步骤：你要是再看不懂，实在是无语了
> 
> 1.前台界面FILE控件：
> 
>        //主要是这一行  
>       <input type="file" id="excelFile" name="excelFile" style="width:0px;" size="60" οnchange="copyFilepathvalue();" 
> 
> 2.请求地址后，进入到Controller层，进入到方法
> 
>   MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;  
>   MultipartFile byteFile = multipartRequest.getFile("excelFile"); //就是前台界面控件的Name  
>   String originalFileName=byteFile.getOriginalFilename();  
>   InputStream is = byteFile.getInputStream(); //解析流  
>   templateAdapter = theChiefDispatch.getTemplateAdaptation("templateAdapter"); //不是重点，无需多看  
>   templateAdapter.twice\_resolve(is,flowProcessTpl); //进入方法了  
> 
>  int columnStart=0;//列开始  
>  int rowStart=4;//行开始  
>  int columnSurplus = 1;//剩余不需要解析的列行  
>  int rowSurplus = 1;//剩余不需要解析的行数
> 
> public FlowProcessTpl twice\_resolve(InputStream is) throws ExcelNotResolveException \{  
>    Workbook wb;  
>     try \{  
>     int len = is.available();  
>     byte\[\] excelFile = new byte\[len\];  
>     is.read(excelFile);  
>     ByteArrayInputStream bin= new ByteArrayInputStream(excelFile);  
>     wb = WorkbookFactory.create(bin);  
>     // 获取第一张工作表Sheet   
>      Sheet sheet = wb.getSheetAt(0);   
>      String title = sheet.getRow(0).getCell(0).getStringCellValue();  
>       
>      flowProcess.setTpl(excelFile);  //将这个EXCEL存放到数据库的BLOB中  实体属性为 byte\[\]类型的  
>      flowProcess.setReserve1(title);  //设置表头  
>      Row rowName = sheet.getRow(rowStart-3);//保存列信息
> 
>     解析列  
>        for(int colNum = columnStart + 3;colNum < rowName.getLastCellNum();colNum++)\{  
>         Cell cell = rowName.getCell(colNum);  
>                    
>         if(colNum==rowName.getLastCellNum()-1)\{  
>          flowProcess.setAnaly\_name(getValue(cell));  
>         \}        
>        Row rowScale = sheet.getRow(rowStart-2);  
>        int index = 0 ;  
>        for(int colNum = columnStart + 3;colNum < rowScale.getLastCellNum();colNum++)\{  
>         Cell cell = rowScale.getCell(colNum);  
>                
>         if(colNum!=rowScale.getLastCellNum()-1)\{  
>          ColInfo colInfo = list.get(index++);  
>          if(StringUtils.isNotBlank(getValue(cell)))\{  
>           colInfo.setColScale(getValue(cell));  
>          \}  
>         \}  
>           
>        \}  
>          
>        //保存描述信息  
>        Row rowDscribe = sheet.getRow(rowStart-1);  
>        index = 0;  
>        for(int colNum = columnStart + 3;colNum < rowDscribe.getLastCellNum();colNum++)\{  
>         Cell cell = rowDscribe.getCell(colNum);  
>         ColInfo colInfo=new ColInfo();  
>         if(cell == null)\{  
>          continue;  
>         \}  
>         if(colNum==rowScale.getLastCellNum()-1)\{  
>          flowProcess.setAnaly\_descri(getValue(cell));  
>         \}else\{  
>          colInfo = list.get(index++);  
>          colInfo.setDescribe(getValue(cell));  
>         \}  
>         if(colNum!=rowScale.getLastCellNum()-1)\{  
>          colInfoService.saveColInfo(colInfo);  
>         \}  
>          \}  
>          
>        Integer i = 0;  
>       // 循环行Row    
>       for(int rowNum = rowStart; rowNum <= sheet.getLastRowNum()-rowSurplus; rowNum++)\{   
>         Row row = sheet.getRow( rowNum);    
>         //分别要导入的三列  
>         Cell taskNameCell = row.getCell( columnStart);  
>         Cell explainCell = row.getCell( columnStart + 1 );  
>         Cell unitCell = row.getCell( columnStart + 2 );  
>          i++;  
>         \}  
>        is.close();  
>         \}   catch (Exception e)\{  
>     if(flowProcess.getId() != null )\{  
>      flowProcessTplService.removeFlowProcessTpl(new String\[\]\{flowProcess.getId().toString()\});  //删除不正确的模板数据  
>     \}  
>     ExcelNotResolveException ee=new ExcelNotResolveException("不支持的excel文件模板,请重新上传！");  
>     e.printStackTrace();  
>     if(e instanceof ExcelNotResolveException) ee= (ExcelNotResolveException)e;  
>     throw ee;  
>    \}   
>   \}
> 
> 下面为要导入模板的样式
> 
>  
> 
>  ![SouthEast][]
> 
> ![SouthEast 1][]
> 
> 为导入成功后，读取数据
> 
>

[SouthEast]: /images/20211013/4cd52fc960a446a0a55eefb1ec051924.png
[SouthEast 1]: /images/20211013/59ff3ec0f2d34243aafab3f26c56ce5f.png