java 获取excel文件，并向其写入数据

刺骨的言语ヽ痛彻心扉 2023-02-15 15:56 61阅读 0赞

public void templateToExcel(ServletOutputStream output, HttpSession session) throws Exception { 
    
            InputStream is=null;
            FileInputStream fis=null;
            Workbook wb=null;
            try { 
                String classpath = this.getClass().getResource("/").getPath().replaceFirst("/", "");
                String filePath = classpath+"META-INF/resources/uploadFiles/员工工资批量导入模板.xlsx";
                File excel = new File(filePath);
                if (excel.isFile() && excel.exists()) {    //判断文件是否存在
                    String[] split = excel.getName().split("\\.");  //.是特殊字符，需要转义！！！！！
                    //根据文件后缀（xls/xlsx）进行判断
                    if ( "xls".equals(split[1])){ 
                        fis = new FileInputStream(excel);   //文件流对象
                        wb = new HSSFWorkbook(fis);
                    }else if ("xlsx".equals(split[1])){ 
                        is = new FileInputStream(excel);
                        XSSFWorkbook xssfWorkbook= new XSSFWorkbook(is);
                        wb = xssfWorkbook;
                    }
                    // 获取工作簿
                    Sheet sheet = wb.getSheetAt(0);
    
                    List<ControlClerk> list =controlClerkMapper.getClerkList2();
                    if(list != null && list.size() > 0) { 
                        Cell cell = null; //列对象
                        Row nRow = null; // 行对象
                        for(int i = 0; i < list.size(); i++) { 
                            nRow = sheet.createRow(i + 2);
    // nRow.setRowStyle(style);
                            ControlClerk item = list.get(i);
    
                            int j = 1;
    
                            cell = nRow.createCell(j++);
                            cell.setCellValue(item.getUserId());
    
                            cell = nRow.createCell(j++);
                            cell.setCellValue(item.getUserName());
                        }
                    }
                } else { 
                    _log.info("===========文件不存在！=============");
                }
            } catch (Exception e) { 
                e.printStackTrace();
                _log.error("===========读取文件出错！==========="+e.getMessage(),e);
            }finally{ 
                if(is!=null){ 
                    try { 
                        is.close();
                    } catch (IOException e) { 
                        e.printStackTrace();
                    }
                }
                if(fis!=null){ 
                    try { 
                        fis.close();
                    } catch (IOException e) { 
                        e.printStackTrace();
                    }
                }
            }
            wb.write(output);
            output.close();
        }