java 读取exe java 读取excel写入数据库

1.导入jar包

<!-- poi -->
         <dependency>
             <groupId>org.apache.poi</groupId>
             <artifactId>poi</artifactId>
             <version>3.11</version>
         </dependency>        <dependency>
             <groupId>org.apache.poi</groupId>
             <artifactId>poi-examples</artifactId>
             <version>3.17</version>
         </dependency>        <dependency>
             <groupId>org.apache.poi</groupId>
             <artifactId>poi-excelant</artifactId>
             <version>3.17</version>
         </dependency>

2.配置下载模板

在controller中写下载模板接口：

@Controller
 @RequestMapping(value = "/excelModel")
 public class PoiExpExcel {
     
     @RequestMapping(value = "/importExcelModel", method = RequestMethod.GET, produces = "application/json; charset=utf-8")
     @ResponseBody
     public ModelAndView exportExcel()throws Exception{ 
         ModelAndView mv = new ModelAndView();  
         Map<String,Object> dataMap = new HashMap<String,Object>();  
         List<String> titles = new ArrayList<String>();  
         titles.add("油站编号");  
         titles.add("所属省份");  
         titles.add("所属城市");
         titles.add("所属片区");  
         titles.add("非油品编码");  
         titles.add("油站名"); 
         titles.add("油站类型"); 
         titles.add("具体位置"); 
         titles.add("电话（含区号）"); 
         titles.add("营业时间"); 
         titles.add("营业/关停"); 
         titles.add("是否有充值业务"); 
         titles.add("是否有便利店"); 
         titles.add("经度"); 
         titles.add("纬度"); 
         titles.add("92#"); 
         titles.add("95#"); 
         titles.add("98#"); 
         titles.add("0#"); 
         dataMap.put("titles", titles);  
         ObjectExcelView erv = new ObjectExcelView();  
         mv = new ModelAndView(erv,dataMap);  
            return mv;    }  }

在接口中使用了工具类ObjectExcelView：

public class ObjectExcelView extends AbstractExcelView{
     
     @Override  
     protected void buildExcelDocument(Map<String, Object> model,  
             HSSFWorkbook workbook, HttpServletRequest request,  
             HttpServletResponse response) throws Exception {  
         // TODO Auto-generated method stub  
         Date date = new Date();
         String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
         HSSFSheet sheet;  
         HSSFCell cell;  
         response.setContentType("application/octet-stream");  
         response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");  
         sheet = workbook.createSheet("sheet1"); 
         
         List<String> titles = (List<String>) model.get("titles");  
         int len = titles.size();  
         HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式  
         headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
         headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
         HSSFFont headerFont = workbook.createFont();    //标题字体  
         headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
         headerFont.setFontHeightInPoints((short)11);  
         headerStyle.setFont(headerFont);  
         short width = 20,height=25*20;  
         sheet.setDefaultColumnWidth(width);  
         HSSFRow row=sheet.createRow(0);
         for(int i=0; i<len; i++){ //设置标题  
             String title = titles.get(i);
                 if(title.equals("模板使用教程")) {
                 HSSFCell createCell = row.createCell(i);
                 //createCell.setCellValue("模板使用说明: 在油站类型列  1-中石油，2-中石化，3-其它加油站"+"\n");
                 createCell.setCellValue("模板使用说明: 在油站类型列  1-中石油，2-中石化，3-其它加油站");
                 HSSFCellStyle style=workbook.createCellStyle();
                 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
                 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
                 style.setWrapText(true);//自动换行
                 style.setIndention((short)3);//缩进
                 HSSFFont font = workbook.createFont();
                 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);                font.setColor(Font.COLOR_RED);//设置字体颜色
                 style.setFont(font);
                 createCell.setCellStyle(style);
                 //设置合并单元格，第一个参数：起始行号；第二个参数：结束行号；第三个参数：起始列号；第四个参数：结束列号；
                 CellRangeAddress region=new CellRangeAddress(0, 3, i, i+1);
                 sheet.addMergedRegion(region);
                  
             }else {
                 cell = getCell(sheet, 0, i);  
                 cell.setCellStyle(headerStyle);  
                 setText(cell,title);
             }
          /*if(title.equals("车辆类型")) {
              CellRangeAddressList regions = new CellRangeAddressList(1, 65535,i, i);
              DVConstraint constraint =DVConstraint.createExplicitListConstraint(new String[] { "轿车","货运", "客车","工程车","卡车","SUV","MVP","油品运输车","燃气运输车"});
              HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);
               sheet.addValidationData(dataValidate);    
              }
          if(title.equals("油品类型")) {
              CellRangeAddressList regions = new CellRangeAddressList(1, 65535,i, i);
              DVConstraint constraint =DVConstraint.createExplicitListConstraint(new String[] {"92#", "95#","98#", "5#","0#","-10#","-20#"});
              HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);
               sheet.addValidationData(dataValidate);    
              }
          if(title.equals("车辆等级")) {
              CellRangeAddressList regions = new CellRangeAddressList(1, 65535,i, i);
              DVConstraint constraint =DVConstraint.createExplicitListConstraint(new String[] { "黑卡","钻石卡", "白金卡","黄金卡","白银卡","普通卡"});
              HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);
               sheet.addValidationData(dataValidate);    
              }*/
               
         }  
         sheet.getRow(0).setHeight(height);    
         HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式  
         contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
         
         List<PageData> varList = (List<PageData>) model.get("varList");  
     if(varList!=null) {
         int varCount = varList.size();  
         for(int i=0; i<varCount; i++){  
             PageData vpd = varList.get(i);  
             for(int j=0;j<len;j++){  
                 String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";  
                 cell = getCell(sheet, i+1, j);  
                 cell.setCellStyle(contentStyle);  
                 setText(cell,varstr);  
             }  
               
         }  }
           
     }  }

3.配置工具类ObjectExcelView引用的PageData工具类：

@SuppressWarnings("all")
 public class PageData extends HashMap implements Map{    private static final long serialVersionUID = 1L;
    Map map = null;
     HttpServletRequest request;    public PageData(HttpServletRequest request){
         this.request = request;
         Map properties = request.getParameterMap();        Map returnMap = new HashMap();
         Iterator entries = properties.entrySet().iterator();
         Map.Entry entry;
         String name = "";
         String value = "";
         while (entries.hasNext()) {
             entry = (Map.Entry) entries.next();
             name = (String) entry.getKey();
             System.out.println(name);
             Object valueObj = entry.getValue();
             if(null == valueObj){
                 value = "";
             }else if(valueObj instanceof String[]){
                 String[] values = (String[])valueObj;
                 for(int i=0;i<values.length;i++){
                     value = values[i] + ",";
                 }
                 value = value.substring(0, value.length()-1);
             }else{
                 value = valueObj.toString();
             }
             returnMap.put(name, value);
         }
         map = returnMap;
     }    public PageData() {
         map = new HashMap();
     }    @Override
     public Object get(Object key) {
         Object obj = null;
         if(map.get(key) instanceof Object[]) {
             Object[] arr = (Object[])map.get(key);
             obj = request == null ? arr:(request.getParameter((String)key) == null ? arr:arr[0]);
         } else {
             obj = map.get(key);
         }
         return obj;
     }    public String getString(Object key) {
         return (String)get(key);
     }    @Override
     public Object put(Object key, Object value) {
         return map.put(key, value);
     }    @Override
     public Object remove(Object key) {
         return map.remove(key);
     }    public void clear() {
         map.clear();
     }    public boolean containsKey(Object key) {
         return map.containsKey(key);
     }    public boolean containsValue(Object value) {
         return map.containsValue(value);
     }    public Set entrySet() {
         return map.entrySet();
     }    public boolean isEmpty() {
         return map.isEmpty();
     }    public Set keySet() {
         return map.keySet();
     }    @SuppressWarnings("unchecked")
     public void putAll(Map t) {
         map.putAll(t);
     }    public int size() {
         return map.size();
     }    public Collection values() {
         return map.values();
     }
 }

 

4.配置工具类ReadExcel读取excel中的内容：

@Component
 public class ReadExcel {
   //总行数
     private int totalRows = 0;  
     //总条数
     private int totalCells = 0; 
     //错误信息接收器
     private String errorMsg;
     //构造方法
     public ReadExcel(){}
     //获取总行数
     public int getTotalRows()  { return totalRows;} 
     //获取总列数
     public int getTotalCells() {  return totalCells;} 
     //获取错误信息
     public String getErrorInfo() { return errorMsg; }  
 //    @Value("${PICTURE_ADDR}")
 //    public String PICTURE_ADDR;
     
   /**
    * 验证EXCEL文件
    * @param filePath
    * @return
    */
   public boolean validateExcel(String filePath){
         if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))){  
             errorMsg = "文件名不是excel格式";  
             return false;  
         }  
         return true;
   }
     
   /**
    * 读EXCEL文件，获取客户信息集合
    * @param fielName
    * @return
    */
  
   public List<OilStation> getExcelInfo(String fileName,MultipartFile Mfile){
       
       //把spring文件上传的MultipartFile转换成CommonsMultipartFile类型
        CommonsMultipartFile cf= (CommonsMultipartFile)Mfile; //获取本地存储路径
         File file = new  File("D:\\fileupload");
        //创建一个目录 （它的路径名由当前 File 对象指定，包括任一必须的父路径。）
        if (!file.exists()) file.mkdirs();
        //新建一个文件
        File file1 = new File("D:\\fileupload" + new Date().getTime() + ".xlsx"); 
        //将上传的文件写入新建的文件中
        try {
            cf.getFileItem().write(file1); 
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        //初始化客户信息的集合    
        List<OilStation> oilStationList=new ArrayList<OilStation>();
        //初始化输入流
        InputStream is = null;  
        try{
           //验证文件名是否合格
           if(!validateExcel(fileName)){
               return null;
           }
           //根据文件名判断文件是2003版本还是2007版本
           boolean isExcel2003 = true; 
           if(WDWUtil.isExcel2007(fileName)){
               isExcel2003 = false;  
           }
           //根据新建的文件实例化输入流
           is = new FileInputStream(file1);
           //根据excel里面的内容读取客户信息
           oilStationList = (List<OilStation>) getExcelInfo(is, isExcel2003); 
           is.close();
       }catch(Exception e){
           e.printStackTrace();
       } finally{
           if(is !=null)
           {
               try{
                   is.close();
               }catch(IOException e){
                   is = null;    
                   e.printStackTrace();  
               }
           }
       }
       return oilStationList;
   }
   /**
    * 根据excel里面的内容读取客户信息
    * @param is 输入流
    * @param isExcel2003 excel是2003还是2007版本
    * @return
    * @throws IOException
    */
   public  List<OilStation> getExcelInfo(InputStream is,boolean isExcel2003){
        List<OilStation> oilStationList=null;
        try{
            /** 根据版本选择创建Workbook的方式 */
            Workbook wb = null;
            //当excel是2003时
            if(isExcel2003){
                wb = new HSSFWorkbook(is); 
            }
            else{//当excel是2007时
                wb = new XSSFWorkbook(is); 
            }
            //读取Excel里面客户的信息
            oilStationList=(List<OilStation>) readExcelValue(wb);
        }
        catch (IOException e)  {  
            e.printStackTrace();  
        }  
        return oilStationList;
   }
   /**
    * 读取Excel里面客户的信息
    * @param wb
    * @return
    */
   private List<OilStation> readExcelValue(Workbook wb){ 
       //得到第一个shell  
        Sheet sheet=wb.getSheetAt(0);
        
       //得到Excel的行数
        this.totalRows=sheet.getPhysicalNumberOfRows();
        
       //得到Excel的列数(前提是有行数)
        if(totalRows>=1 && sheet.getRow(0) != null){
             this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
        }
        
        List<OilStation> oilStationList=new ArrayList<OilStation>();
        OilStation oilStation;
       //循环Excel行数,从第二行开始。标题不入库
        for(int r=1;r<totalRows;r++){
            Row row = sheet.getRow(r);
            if (row == null) continue;
            oilStation = new OilStation();
            
            //循环Excel的列
            for(int c = 0; c <this.totalCells; c++){    
                Cell cell = row.getCell(c);
                //System.out.println("c======:"+c+"cell:"+cell);
                if(null != cell) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                }
              if(cell!=null && !cell.equals("")) {
                    if(c==0){
                        oilStation.setStationCode(cell.getStringCellValue());//
                    }else if(c==1){
                        oilStation.setProvince(cell.getStringCellValue());//
                    }else if(c==2){
                        oilStation.setCity(cell.getStringCellValue());//
                    }else if(c==3){
                        oilStation.setArea(cell.getStringCellValue());
                    }else if(c==4){
                       oilStation.setNotOilCode(cell.getStringCellValue());
                    }else if(c==5){
                        oilStation.setName(cell.getStringCellValue());
                    }else if(c==6){
                        if(cell.getStringCellValue() != null && cell.getStringCellValue() != ""
                                && !cell.getStringCellValue().equals("")) {
                            oilStation.setStationType(Integer.valueOf(cell.getStringCellValue()));
                        }
                    }else if(c==7){
                        oilStation.setAddress(cell.getStringCellValue());
                    }else if(c==8){
                        oilStation.setTelephone(cell.getStringCellValue());
                    }else if(c==9){//营业时间
                        if(StringUtil.isNotEmpty(cell.getStringCellValue())) {
                            oilStation.setBusinessHours(cell.getStringCellValue());
                        }
                    }else if(c==10){//营业状态
                        String s = cell.getStringCellValue();
                        if(s.contains("营业")) {
                            oilStation.setWorkStatus(1);
                        }else {
                            oilStation.setWorkStatus(2);
                        }
                        
                    }else if(c==11){
                        String s = cell.getStringCellValue();
                        if(s.contains("是")) {
                            oilStation.setAddMoney(1);
                        }else {
                            oilStation.setAddMoney(2);
                        }
                    }else if(c==12){
                        String s = cell.getStringCellValue();
                        if(s.contains("是")) {
                            oilStation.setMarket(1);
                        }else {
                            oilStation.setMarket(2);
                        }
                    }else if(c==13){
                        oilStation.setLongitude(cell.getStringCellValue());
                    }else if(c==14){
                        oilStation.setDimension(cell.getStringCellValue());
                    }else if(c==15) {
                        String s = cell.getStringCellValue();
                        oilStation.setSecond(Double.parseDouble("".equals(s.toString())?"0.00":s.toString()));
                    }else if(c==16) {
                        String s = cell.getStringCellValue();
                        oilStation.setFive(Double.parseDouble("".equals(s.toString())?"0.00":s.toString()));
                    }else if(c==17) {
                        String s = cell.getStringCellValue();
                        oilStation.setEight(Double.parseDouble("".equals(s.toString())?"0.00":s.toString()));
                    }else if(c==18) {
                        String s = cell.getStringCellValue();
                        oilStation.setZero(Double.parseDouble("".equals(s.toString())?"0.00":s.toString()));
                    }else if(c==19) {
                        String s = cell.getStringCellValue();
                        if(s.contains("是")) {
                            oilStation.setCameraNumber("1");
                        }else {
                            oilStation.setCameraNumber("2");
                        }
                        
                    }else if(c==20) {
                            oilStation.setRemark(cell.getStringCellValue());
                    }
                
            }}
            //添加客户
            oilStationList.add(oilStation);
        }
        return oilStationList;
   }}

5.配置工具类ReadExcel引用的WDWUtil工具类，来判断excel的版本：

public class WDWUtil {
    // @描述：是否是2003的excel，返回true是2003 
     public static boolean isExcel2003(String filePath)  {  
          return filePath.matches("^.+\\.(?i)(xls)$");  
      }  
    
     //@描述：是否是2007的excel，返回true是2007 
     public static boolean isExcel2007(String filePath)  {  
          return filePath.matches("^.+\\.(?i)(xlsx)$");  
      }  
 }

6.配置读取excel文件内容的接口，并添加到数据库中：

@RequestMapping(value = "/batchAddOilStation", method = RequestMethod.POST, produces = "application/json; charset=utf-8")
       public Map<String, Object> batchAddOilStation(@RequestParam(value = "filename") MultipartFile file,HttpServletRequest request) throws FileNotFoundException {
       Map<String, Object> map = new HashMap<>();
       try {
               
              String name = file.getOriginalFilename();
               ReadExcel read =new ReadExcel();
               List<OilStation> excelInfo = read.getExcelInfo(name, file);
                boolean flag = oilStationService.batchOwners(excelInfo);
                map.put("code", 200);
                map.put("msg", "");
                map.put("count", 1);
                map.put("data", flag);
       } catch (CodeException ce) {
           LOGGER.error(ce.getCode() + ":" + ce.getMessage());
       }
       return map;
   }

7.在jsp中需要配置：

<form>
    <div class="row cl">
                     <label class="form-label col-xs-4 col-sm-2">导入车辆模板：</label>
                     <div class="formControls col-xs-8 col-sm-10">
                         <span class="btn-upload form-group">
                          <input class="input-text upload-url" type="text" name="uploadfile"
                             id="uploadfile" readonly="" nullmsg="请添加附件！">
                             <a href=" " class="btn btn-primary radius upload-btn">
                             <i class="Hui-iconfont" style="width: 150px"></i> 浏览文件</a> 
                             <input type="file" multiple="" name="filename" id="filename" class="input-file">
                         </span>
                         <label class="btn btn-primary radius " οnclick="importExcel();" style="width: 150px">下载导入模板</label>
                     </div>
                 </div></form>

8.在js中：

function importExcel(){    
             window.location.href = "/upload/uploadExcelModel/"; 
         } submitHandler: function(form) {
             var formData = new FormData($( "#form-add" )[0])
             $.ajax({
                 type: "post",
                 url: contextPath + "/owners/add",
                 data: formData,
                 cache: false,  
                 contentType: false,  
                 processData: false,
                 dataType: "json",
                 success: function(data) {
                     },
                 error: function(XMLHttpRequest, textStatus, errorThrown) {
                     //alert(errorThrown);
                     return false;
                 } 
             });
             return false;
         }