ssh开发时利用excel表格导入数据

最近在在一个web的项目开发时，需要用到excel表格导入数据库，做完之后发现需要注意的点有很多，所以就写下来分享一下自己的新的体会

 

 package com.oracle.match.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;

import com.opensymphony.xwork2.ActionSupport;
import com.oracle.match.po.Introduce;
import com.oracle.match.po.Match1;
import com.oracle.match.service.IntroduceService;
import com.oracle.match.service.MatchService;

public class FilematchAction extends ActionSupport{
    /**
     *
     */
    private static final long serialVersionUID = -2807461111381509765L;
    private File excel;
    private String excelFileName;
    private String result;
    public String getResult() {
        return result;
    }

    public void setResult(String result) {
        this.result = result;
    }

    public File getExcel() {
        return excel;
    }

    public void setExcel(File excel) {
        this.excel = excel;
    }

    public String getExcelFileName() {
        return excelFileName;
    }

    public void setExcelFileName(String excelFileName) {
        this.excelFileName = excelFileName;
    }

    private MatchService matchService;
    private IntroduceService introduceService;
    public IntroduceService getIntroduceService() {
        return introduceService;
    }

    public void setIntroduceService(IntroduceService introduceService) {
        this.introduceService = introduceService;
    }

    private Introduce introduce;
    public Introduce getIntroduce() {
        return introduce;
    }

    public void setIntroduce(Introduce introduce) {
        this.introduce = introduce;
    }

    public MatchService getMatchService() {
        return matchService;
    }

    public void setMatchService(MatchService matchService) {
        this.matchService = matchService;
    }

    public String Fileupload(){
        String realPath = ServletActionContext.getServletContext().getRealPath("/files");
        System.out.println(excelFileName);
        File savefile = new File(new File(realPath),excelFileName);
        if(!savefile.getParentFile().exists()) savefile.getParentFile().mkdirs();
        try {
            FileUtils.copyFile(excel, savefile);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        getDataFromExcel(realPath+"\\"+excelFileName);
        if(excel.exists())
            excel.delete();
        return "SUCCESS";
    }
    
    
       /**
     *     
     * @param cell 一个单元格的对象
     * @return 返回该单元格相应的类型的值
     */
    public static Object getRightTypeCell(Cell cell){
    
        Object object = null;
        switch(cell.getCellType())
        {
            case Cell.CELL_TYPE_STRING :
            {
                object=cell.getStringCellValue();
                break;
            }
            case Cell.CELL_TYPE_NUMERIC :
            {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                object=cell.getNumericCellValue();
                break;
            }
                
            case Cell.CELL_TYPE_FORMULA :
            {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                object=cell.getNumericCellValue();
                break;
            }
            
            case Cell.CELL_TYPE_BLANK :
            {
                cell.setCellType(Cell.CELL_TYPE_BLANK);
                object=cell.getStringCellValue();
                break;
            }
        }
        return object;
    }
    
    /**
     * 读取出filePath中的所有数据信息
     * @param filePath excel文件的绝对路径
     *
     */
    
    public  void getDataFromExcel(String filePath)
    {
        List<Map<String,Integer>> list = new ArrayList<Map<String, Integer>>();
        FileInputStream fis =null;
        Workbook workbook = null;
        int flag = 0;
        
        try
        {
            //获取一个绝对地址的流
              fis = new FileInputStream(filePath);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
       
        try
        {
            //2003版本的excel，用.xls结尾
            workbook = new HSSFWorkbook(fis);//得到工作簿
             
        }
        catch (Exception ex)
        {
            try
            {
                //2007版本的excel，用.xlsx结尾
                fis = new FileInputStream(filePath);
                workbook = new XSSFWorkbook(fis);//得到工作簿
            } catch (IOException e)
            {
                e.printStackTrace();
            }
        }
        
        //得到一个工作表
        Sheet sheet = workbook.getSheetAt(0);
        
        //获得表头
        Row rowHead = sheet.getRow(0);
        
      //根据不同的data放置不同的表头
        Map<Object,Integer> headMap = new HashMap<Object, Integer>();
        try
        {
            //----------------这里根据你的表格有多少列
            while (flag < 6)
            {
                Cell cell = rowHead.getCell(flag);
                if (getRightTypeCell(cell).toString().equals("赛事名"))
                    headMap.put("mname", flag);
                
                if (getRightTypeCell(cell).toString().equals("类别编号"))
                    headMap.put("cid", flag);
                
                
                if (getRightTypeCell(cell).toString().equals("赛事介绍"))
                    headMap.put("intro", flag);
                
                if (getRightTypeCell(cell).toString().equals("人数"))
                    headMap.put("num", flag);
                
                if (getRightTypeCell(cell).toString().equals("开赛时间"))
                    headMap.put("btime", flag);
                
                if (getRightTypeCell(cell).toString().equals("比赛场地"))
                    headMap.put("matchcd", flag);
                
                flag++;
            }
        } catch (Exception e)
        {
            e.printStackTrace();
            System.out.println("表头不合规范，请修改后重新导入");
        }
        
        
        //获得数据的总行数
        int totalRowNum = sheet.getLastRowNum();
        System.out.println(totalRowNum);
        
        
        
        String mname ="";//这堆其实没什么用，我忘删了
        String cid ="";
        Double cid1=0.0;
        int Cid=0;
        String intro="";
        String num="";
        Double num1 =0.0;
        int Num=0;
        String btime ="";
        String matchcd ="";
       // String pass1 ="";
        Double temp=0.0;
        
        if(0 == totalRowNum)
        {
            System.out.println("Excel内没有数据！");
        }
        
        Cell cell_1 = null,cell_2 = null,cell_3 = null,cell_4 = null,cell_5 = null,cell_6 = null;
        
       //获得所有数据
        for(int i = 1 ; i <= totalRowNum ; i++)
        {
            //获得第i行对象
            Row row = sheet.getRow(i);
            
            try
            {
                cell_1 = row.getCell(headMap.get("mname"));
                cell_2 =row.getCell(headMap.get("cid"));
                cell_3 = row.getCell(headMap.get("intro"));
                cell_4 = row.getCell(headMap.get("num"));
                cell_5 = row.getCell(headMap.get("btime"));
                cell_6 = row.getCell(headMap.get("matchcd"));
            } catch (Exception e)
            {
                e.printStackTrace();
                System.out.println("获取单元格错误");
            }
            
            try
            {
                
                mname = cell_1.toString();
                cid =  cell_2.toString();
                if(cid!=null&&cid.length()>0)
                {
                cid1=Double.valueOf(cid);
                for(;cid1!=0;cid1--){
                    Cid++;
                }
                }
                intro =  cell_3.toString();
                num =  cell_4.toString();
                if(num!=null&&num.length()>0)
                {
                num1=Double.valueOf(num);
                for(;num1!=0;num1--){
                    Num++;
                }
                }
                btime = cell_5.toString();
                matchcd = cell_6.toString();
                
            } catch (ClassCastException e)
            {
                e.printStackTrace();
                System.out.println("数据不全是数字或全部是文字!");
            }
            Match1 match = new Match1();
            
            if(mname!=null&&mname.length()>0)
            {
            match.setMname(mname);
            match.setCid(Cid);
            getMatchService().save(match);
            Introduce introduce=new Introduce();
            List<Match1> Mid=matchService.queryByMname(mname);
            int ID = 0;
            for(Match1 id:Mid)
            {
            ID=Integer.valueOf(id.getMid()).intValue();
            }
            introduce.setMid(ID);
            introduce.setBtime(btime);
            introduce.setIntro(intro);
            introduce.setMatchcd(matchcd);
            introduce.setNum(Num);
            introduceService.save(introduce);
            
            }
        }
        result="导入完毕！";
        
    }

}

上面就是导入的代码，不过要注意，表格里所有的数字在经过程序堆出来的时候都会变成double型，比如4会变成4.0所以数据在读出来的时候需要自己转化一下，字存到数据库里防止存入数据出错，例如我上面的代码就是先转化成String类型的在转化成double在转化成int型，这种方法比较笨，不过实用  num =  cell_4.toString();
             

  if(num!=null&&num.length()>0)
                {
                num1=Double.valueOf(num);
                for(;num1!=0;num1--){
                    Num++;
                }代码

上面的代码写完之后，这个action类还要在struct.xml里配置才可以调用实用，代码如下：

<action name="filematchAction_*" method="{1}" class="com.oracle.match.action.FilematchAction">
        <result name="SUCCESS">admin/match/query.jsp</result>
        <interceptor-ref name="token">
                   <param name="excludeMethods">Fileupload</param>
               </interceptor-ref>
               <!-- 文件上传 -->
               <interceptor-ref name="fileUpload">
                <param name="maximumSize">10M</param>
                <param name="allowedExtensions">xls,xlsx</param>
            </interceptor-ref>
               <interceptor-ref name="defaultStack"></interceptor-ref>
        
        </action>

这样配置完之后，就可以在前台编写jsp代码调用了，代码如下：

<form action="filematchAction_Fileupload.action" method="post"
        enctype="multipart/form-data">
        <s:file label="文件" id="fileName" name="excel"></s:file>
        <input type="submit" value="导入数据" id="go" οnclick="goto1()">${result}
    </form>

这样就可以excel表格导入数据了，希望可以帮到你们