SpringMVC 实现Excel导入



SpringMVC完成上传

 /**
  * @author jiangzeyun
  * @startTime  2014-09-06
  * @endTime    2014-09-08
  * @param request
  * @param response
  */
 @RequestMapping(method=RequestMethod.POST, value="/uploadExcel")
    public void uploadExcel(HttpServletRequest request, HttpServletResponse response) {
  
  MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
  MultipartFile multipartFile = multipartRequest.getFile("Filedata");
  String sourceName = multipartFile.getOriginalFilename(); // 原始文件名
  String fileType = sourceName.substring(sourceName.lastIndexOf("."));
  
  
  //System.out.println("上传的文件名为:"+sourceName+"类型为:"+fileType);
  String proId=request.getParameter("proId");
  String proName=request.getParameter("proName");
  String settleType=request.getParameter("settleType");
  //安置类型:1表示货币安置  2.房源安置
  
  
  //System.out.println("项目Id为:"+proId+"项目名称为:"+proName); 
  String base = request.getSession().getServletContext().getRealPath("/") + "attachments" + File.separator + "uploadedExcel";
  File file = new File(base);
  if(!file.exists()){
   file.mkdirs();
  }
  try{
   String path=base + File.separator + sourceName;
   
   multipartFile.transferTo(new File(path));
   //service.insert("insertAttachment", attach);
   //上传成功后读取Excel表格里面的数据
   System.out.println("路径是"+path);
   File read=new File(path);
   Map<String,CollectiveSettle> map=ImportExcel.readExcelFile(path);
   int flag=ParseMap.updateMap(map, proId, proName, settleType,service);
   if(1==flag){
    response.getWriter().print("{success:true}");
   }else{
    response.getWriter().print("{success:false}");
   }
   
   
  }catch (Exception e) {
   try {
    response.getWriter().print("{success:false}");
   } catch (IOException e1) {
    e1.printStackTrace();
   }
  }
 }



java解析Excel部分实现:

package com.ldacms.excelUtils;

import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ldacms.domain.proCol.CollectiveSettle;
import com.ldacms.domain.proCol.DelayTransition;
import com.ldacms.util.PrimaryUtils;

import org.apache.poi.ss.usermodel.Row;

public class ImportExcel {
 

 //判断一个字符串是否是整数
 
 public static boolean isDig(String number){
  try {
   
   Double.valueOf(number);
   return true;
   
  } catch (Exception e) {
   return false;
  }
  
  
 }
 

 //判断是否为整数jiangzeyun 14-10-31 add
 public static boolean isEqual2(String num){
  try {
   Double.valueOf(num);
   return true;
  } catch (Exception e) {
   return false;
  }
 }

 public static String readValue(Cell cell){
  String value="";
  if(cell==null){
   return "NULL";
  }
  
  int rowType=cell.getCellType();
  if( rowType== Cell.CELL_TYPE_STRING){
    value=cell.getStringCellValue();      
   
  }else if(rowType == Cell.CELL_TYPE_NUMERIC){
   if(HSSFDateUtil.isCellDateFormatted(cell)){
    
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    String date=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
    value=date.toString();
    
   }else{
    Number v=cell.getNumericCellValue();
    //如果是身份证(第三列)(因为身份证全是数字)则把小数部分去掉
    if(cell.getColumnIndex()==3){
     double va=Double.valueOf(v.toString());
     int val=(int)va;
     value=String.valueOf(val);
     
    }else{
     value=v.toString();
    }
    
   }
   
  }
 
  
  return value;
 }
 //读取单元格的数据,把一行行的数据保存到map中,用map来过滤重复身份证的数据
 public static Map<String,CollectiveSettle> readExcelFile(String fileName){
  //创建对Excel工作薄文件的引用
  
  HSSFWorkbook workbook=null;
  HashMap<String, CollectiveSettle> map=new HashMap<String, CollectiveSettle>();
  try {
   FileInputStream in=new FileInputStream(fileName);
   
   Workbook wb;
    if(fileName.endsWith(".xls")) { 
     //Excel2003
             wb = new HSSFWorkbook(in); 
        } else { 
         //Excel 2007
             wb = new XSSFWorkbook(in); 
      } 

   
   //创建对工作表的引用
   Sheet sheet= wb.getSheetAt(0);
   //遍历所有单元格,读取单元格
   int row_num=sheet.getLastRowNum();
   
   //项目名称
   String proName="";
   for(int i=1 ;i< row_num ;i++){
    Row row=sheet.getRow(i);
     //获得项目名称
              if(1==i){
               proName=readValue(row.getCell(0));
              }else if(i>2){
              //取得第0个单元格的值
               String zeroValue=readValue(row.getCell(0));
              if("NULL".equals(zeroValue)||!isEqual2(zeroValue)){
                break;
               }else{
                
            //得到每一行的单元格
     CollectiveSettle settle=new CollectiveSettle();
     //生成pmrowId和treeId
     settle.setPmrowId(PrimaryUtils.generateGuid());
     settle.setTreeId(settle.getPmrowId());
     
     settle.setProName(proName);
     settle.setMname(readValue(row.getCell(1)));
     //身份证
     settle.setMcardId(readValue(row.getCell(3)));
     //与户主的关系
     settle.setRelationship(readValue(row.getCell(4)));
     //pname;户主名称
       settle.setPname(readValue(row.getCell(5)));   
     //货币补偿
     String money=readValue(row.getCell(8));
     if(money.equals("NULL")||money==null||money==""){
      settle.setPayMoney(0.0);
     }else{
      settle.setPayMoney(Double.valueOf(money));
     }
     
     settle.setRemark(readValue(row.getCell(10)));
      
      
     map.put(readValue(row.getCell(3)), settle);
               }
     
              }
     
    }
    
    
   
   
   
   
  } catch (Exception e) {
   e.printStackTrace();
   
  }finally{
   
   
  }
  
  
  return map;
 }
 
 
 //读取单元格的数据,把一行行的数据保存到map中,用map来过滤重复身份证的数据
  public static Map<String,CollectiveSettle> readHouseExcelFile(String fileName){
   //创建对Excel工作薄文件的引用
   
   HSSFWorkbook workbook=null;
   HashMap<String, CollectiveSettle> map=new HashMap<String, CollectiveSettle>();
   try {
    FileInputStream in=new FileInputStream(fileName);
    
    Workbook wb;
     if(fileName.endsWith(".xls")) { 
      //Excel2003
              wb = new HSSFWorkbook(in); 
         } else { 
          //Excel 2007
              wb = new XSSFWorkbook(in); 
       } 

    
    //创建对工作表的引用
    Sheet sheet= wb.getSheetAt(0);
    //遍历所有单元格,读取单元格
    int row_num=sheet.getLastRowNum();
    
    //项目名称
    String proName="";
    for(int i=1 ;i< row_num ;i++){
     Row row=sheet.getRow(i);
      //获得项目名称
               if(1==i){
                proName=readValue(row.getCell(0));
               }else if(i>2){
               //取得第0个单元格的值
                String zeroValue=readValue(row.getCell(0));
               if("NULL".equals(zeroValue)){
                 break;
                }else{
                 
             //得到每一行的单元格
      CollectiveSettle settle=new CollectiveSettle();
      //生成pmrowId和treeId
      settle.setPmrowId(PrimaryUtils.generateGuid());
      settle.setTreeId(settle.getPmrowId());
      
      settle.setProName(proName);
      settle.setMname(readValue(row.getCell(1)));
      //身份证
      settle.setMcardId(readValue(row.getCell(3)));
      //与户主的关系
      settle.setRelationship(readValue(row.getCell(4)));
      //pname;户主名称
        settle.setPname(readValue(row.getCell(5)));   
      //选房号
        settle.setChooseHouse(readValue(row.getCell(9)));
      settle.setRemark(readValue(row.getCell(11)));
       
       
      map.put(readValue(row.getCell(3)), settle);
                }
      
               }
      
     }
     
     
    
    
    
    
   } catch (Exception e) {
    e.printStackTrace();
    
   }finally{
    
    
   }
   
   
   return map;
  }
  /*********************************************过度延期费用Excel表格读取***************************************************************/
  //读取单元格的数据,把一行行的数据保存到map中,用map来过滤重复身份证的数据
    public static Map<String,DelayTransition> readDelayTranExcelFile(String fileName){
     //创建对Excel工作薄文件的引用
     
     HSSFWorkbook workbook=null;
     HashMap<String, DelayTransition> map=new HashMap<String, DelayTransition>();
     try {
      FileInputStream in=new FileInputStream(fileName);
      
      Workbook wb;
       if(fileName.endsWith(".xls")) { 
        //Excel2003
                wb = new HSSFWorkbook(in); 
           } else { 
            //Excel 2007
                wb = new XSSFWorkbook(in); 
         } 

      
      //创建对工作表的引用
      Sheet sheet= wb.getSheetAt(1);
      //遍历所有单元格,读取单元格
      int row_num=sheet.getLastRowNum();
      //项目名称
      String proName="";
      System.out.println("总行数---"+row_num);
      for(int i=0 ;i< row_num ;i++){
       Row row=sheet.getRow(i);
        //获得项目名称
                 if(0==i){
                  proName=readValue(row.getCell(0));
                  System.out.println(proName);
                 }else if(i>3)
                 //取得第0个单元格的值
                  //String zeroValue=readValue(row.getCell(0));
                {
                  String zeroValue=readValue(row.getCell(0));
                  System.out.println("第一个数字式:"+zeroValue);
                  if(isDig(zeroValue)){
                   //得到每一行的单元格
          DelayTransition delayTran = new DelayTransition();
          //生成pmrowId和treeId
          delayTran.setPmrowId(PrimaryUtils.generateGuid());
          delayTran.setTreeId(delayTran.getPmrowId());
          delayTran.setProName(proName);
          delayTran.setPname(readValue(row.getCell(1)));
          delayTran.setMcardId(readValue(row.getCell(2)));
          //这里的问题主要是有些是用Excel中的计算出来的,导入时候出现问题
          //System.out.println("户主"+readValue(row.getCell(1))+"身份证号码"+readValue(row.getCell(2))+"补偿面积"+readValue(row.getCell(3)));
          System.out.println("第"+i+"行"+"第三列----------"+readValue(row.getCell(3)));
          delayTran.setCompeneArea(Double.valueOf(readValue(row.getCell(3))).doubleValue());
          
          delayTran.setDownDate(readValue(row.getCell(4)));
          
          //System.out.println(readValue(row.getCell(5)));
          //先将String转化成float,然后再强制转化成整型
          System.out.println("应安置总人"+readValue(row.getCell(5))+"货币安置人数"+readValue(row.getCell(6))+"已住房安置人数"+readValue(row.getCell(7))+"合计已安置人数"+readValue(row.getCell(8)));
          delayTran.setShouldPaceCount((int)Double.parseDouble(readValue(row.getCell(5))));
          delayTran.setMoneyPaceCount((int)Double.parseDouble(readValue(row.getCell(6))));
          delayTran.setHousePaceCount((int)Double.parseDouble(readValue(row.getCell(7))));
          
          
          int alreadyPaceCount = (int)Double.parseDouble(readValue(row.getCell(6)))+(int)Double.parseDouble(readValue(row.getCell(7)));
          
          delayTran.setAlreadyPaceCount(alreadyPaceCount);
          
          int notPaceCount = (int)Double.parseDouble(readValue(row.getCell(5)))-alreadyPaceCount;
          System.out.println(notPaceCount);
          delayTran.setNotPaceCount(notPaceCount);
          //这里需要将string转换成doubule类型
          double compeneArea = Double.valueOf(readValue(row.getCell(3))).doubleValue();
          int shouldPaceCount = (int)Double.parseDouble(readValue(row.getCell(5)));
          double area = (compeneArea/shouldPaceCount)*notPaceCount;
          delayTran.setArea(area);
          
          double monthPayMoney = 12*area;
          delayTran.setMonthPayMoney(monthPayMoney);
          
          double thisPayMoney = monthPayMoney*3;
          delayTran.setThisPayMoney(thisPayMoney);
          delayTran.setRemark(readValue(row.getCell(13)));
          //过滤身份证信息
          map.put(readValue(row.getCell(2)), delayTran);
                  }else{
                   break;
                  }
                  }
        
                 }
        
      
     } catch (Exception e) {
      e.printStackTrace();
      
     }finally{
      
      
     }
     
     
     return map;
    }
 public static void main(String[] args) {
  
 
 }
 
}

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页