SSH框架中关于EXCEL表格导入到MySQL数据库

首先要导包，对于excel2003要导jxl.jar包，excel2007要导poi.jar包

关于导入excel2003和excel2007除了在导包上面有区别以外，在处理表格中的数字和字符串也有一定的差异

下面是我对SSH框架关于excel表格导入到MySQL数据库的详细编码过程

一.关于strut.xml的配置

    <struts>
        <constant name="struts.action.extension" value="do"></constant>  
        <!-- 使struts标签都是simple布局，即自己布局 -->
        <constant name="struts.ui.theme" value="simple" />
         <action name="boat" class="boatAction">
            <result name="ShowBoat">/general/ShowBoat.jsp</result>
            <result name="err">/general/err.jsp</result>
             <!-- 避免重复提交 -->
               <interceptor-ref name="token">
                   <param name="includeMethods">ShowBoat</param>
               </interceptor-ref>
               <!-- 文件上传 -->
               <interceptor-ref name="fileUpload">
                <param name="maximumSize">1024*1024</param>
                <param name="allowedExtensions">xls,xlsx</param>
            </interceptor-ref>
               <interceptor-ref name="defaultStack"></interceptor-ref>
         </action>
     </struts>

二.在action中，对于excel2007如果在数据库中的数据类型是varchar类型而在excel表格中写的全部是数字，那么必须把这些数字转换为字符串才行，如果是日期的话还要做进一步的转换！

package com.action;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;

import com.pojo.BoatTable;
import com.service.BoatService;

public class BoatAction {
    private BoatService boatService;
    private File file;
    //文件名和文件类型必须为 文件名+FileName和 文件名+ContentType
    private String fileFileName;
    private String fileContentTypt;
    public File getFile() {
        return file;
    }

    public void setFile(File file) {
        this.file = file;
    }

    public String getFileFileName() {
        return fileFileName;
    }

    public void setFileFileName(String fileFileName) {
        this.fileFileName = fileFileName;
    }

    public String getFileContentTypt() {
        return fileContentTypt;
    }

    public void setFileContentTypt(String fileContentTypt) {
        this.fileContentTypt = fileContentTypt;
    }

    public BoatService getBoatService() {
        return boatService;
    }

    public void setBoatService(BoatService boatService) {
        this.boatService = boatService;
    }

    
    /**
     * 导入船舶信息
     * 上传
     * @return
     * @throws IOException 
     */
    public String importBoatInfo() throws IOException{
        HttpServletRequest request = ServletActionContext.getRequest();
        HttpSession session = request.getSession();
        String path = ServletActionContext.getServletContext().getRealPath(
        "/general");
        InputStream in = new FileInputStream(file);
        OutputStream out = new FileOutputStream(path+"/"+this.getFileFileName());
        byte[] b = new byte[1024];
        int size = in.read(b);
        while (size>0){
            out.write(b,0,size);
            size = in.read(b);
        }  
        out.close();
        in.close();
        session.setAttribute("fileName", this.getFileFileName());
        return null;
    }
    //导入数据库
    public String intoDB() throws IOException{
        HttpServletRequest request = ServletActionContext.getRequest();
        HttpSession session = request.getSession();
        String path = ServletActionContext.getServletContext().getRealPath(
        "/general");
        String fileName = null;
        while(fileName == null){
            fileName = (String) session.getAttribute("fileName");
        }
        File f = new File(path + "/" + fileName);
        try {
            //用jxl解析excel2003
            if(this.getFileFileName()!=null && this.getFileFileName().indexOf(".xlsx")==-1){
                Workbook book = Workbook.getWorkbook(f);//  
                Sheet sheet = book.getSheet(0); // 获得第一个工作表对象
                for (int i = 1; i < sheet.getRows(); i++) {
                    BoatTable boat = new BoatTable();
                    boat.setBenglishname(sheet.getCell(0, i).getContents());
                    boat.setBchinesename(sheet.getCell(1, i).getContents());
                    boat.setBregist(sheet.getCell(2, i).getContents());
                    boat.setBboat(sheet.getCell(3, i).getContents());
                    boat.setBrecongnize(sheet.getCell(4, i).getContents());
                    boat.setBmaterial(sheet.getCell(5, i).getContents());
                    boat.setBlength(Double.parseDouble(sheet.getCell(6, i).getContents()));
                    boat.setBwidth(Double.parseDouble(sheet.getCell(7, i).getContents()));
                    boat.setBdeep(Double.parseDouble(sheet.getCell(8, i).getContents()));
                    boat.setBsum(Integer.parseInt(sheet.getCell(9, i).getContents()));
                    boat.setBsulttle(Integer.parseInt(sheet.getCell(10, i).getContents()));
                    boat.setBgross(Integer.parseInt(sheet.getCell(11, i).getContents()));
                    boat.setBpower(Integer.parseInt(sheet.getCell(12, i).getContents()));
                    boat.setBlog(sheet.getCell(13, i).getContents());
                    boat.setBremark(sheet.getCell(14, i).getContents());
                    boat.setBcheck(sheet.getCell(15, i).getContents());
                    boat.setBwater(sheet.getCell(16, i).getContents());
                    boat.setBoperat(sheet.getCell(17, i).getContents());
                    boat.setBrange(sheet.getCell(18, i).getContents());
                    boat.setBkind(sheet.getCell(19, i).getContents());
                    boat.setBowner(sheet.getCell(20, i).getContents());
                    //添加到数据库
                    boatService.addOneBoat(boat);
                }
            }else{ //用poi解析excel2007
                // 构建1个工作簿对象
                XSSFWorkbook xwb = null;
                try {
                    xwb = new XSSFWorkbook(new FileInputStream(f.getPath()));
                } catch (Exception e) {
                    e.printStackTrace();
                }
                //得到第一张工作表
                XSSFSheet xSheet = xwb.getSheetAt(0);
                //循环工作表的每一行
                for (int rowNum = 1; rowNum <= xSheet.getLastRowNum(); rowNum++) {
                    XSSFRow xRow = xSheet.getRow(rowNum);
                    if (xRow == null) {
                        continue;
                    }
                    BoatTable boat = new BoatTable();
                    boat.setBenglishname(xRow.getCell(0).getStringCellValue());
                    boat.setBchinesename(xRow.getCell(1).getStringCellValue());
                    boat.setBregist(xRow.getCell(2).getStringCellValue());
                    if(xRow.getCell(3).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
                        boat.setBboat(Double.valueOf(xRow.getCell(3).getNumericCellValue()).toString());
                    }else{
                        boat.setBboat(xRow.getCell(3).getStringCellValue());
                    }
                    boat.setBrecongnize(xRow.getCell(4).getStringCellValue());
                    boat.setBmaterial(xRow.getCell(5).getStringCellValue());
                    boat.setBlength(xRow.getCell(6).getNumericCellValue());
                    boat.setBwidth(xRow.getCell(7).getNumericCellValue());
                    boat.setBdeep(xRow.getCell(8).getNumericCellValue());
                    boat.setBsum((int)xRow.getCell(9).getNumericCellValue());
                    boat.setBsulttle((int)xRow.getCell(10).getNumericCellValue());
                    boat.setBgross((int)xRow.getCell(11).getNumericCellValue());
                    boat.setBpower((int)xRow.getCell(12).getNumericCellValue());
                    boat.setBlog(xRow.getCell(13).getStringCellValue());
                    boat.setBremark(xRow.getCell(14).getStringCellValue());
                    //如果单元格里面为数字，用String取得话会报异常，故有可能为数字的都要判断一下
                    if(xRow.getCell(15).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
                        boat.setBcheck(Double.valueOf(xRow.getCell(15).getNumericCellValue()).toString());
                    }else{
                        boat.setBcheck(xRow.getCell(15).getStringCellValue());
                    }
                    if(xRow.getCell(16).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
                        boat.setBwater(Double.valueOf(xRow.getCell(16).getNumericCellValue()).toString());
                    }else{
                        boat.setBwater(xRow.getCell(16).getStringCellValue());
                    }
                    if(xRow.getCell(17).getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
                        boat.setBoperat(Double.valueOf(xRow.getCell(17).getNumericCellValue()).toString());
                    }else{
                        boat.setBoperat(xRow.getCell(17).getStringCellValue());
                    }
                    boat.setBrange(xRow.getCell(18).getStringCellValue());
                    boat.setBkind(xRow.getCell(19).getStringCellValue());
                    boat.setBowner(xRow.getCell(20).getStringCellValue());
                    //添加到数据库
                    boatService.addOneBoat(boat);
                }
                
            }
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } 
        f.delete();
        session.removeAttribute("fileName");
        return null;
    }
    
    
}

三.在jsp页面中

<head>
    <script type="text/javascript" src="js/jquery-1.7.2.min.js"></script>
</head>

 

<!-- 导入船舶信息 -->
        <tr>
            <td align="left" colspan="14" height="30" style="font-size:14px;">
                <iframe id="uploadFrameID" name="uploadFrame" height="0" width="0" frameborder="0" scrolling="yes"></iframe>
                <form action="boat!importBoatInfo.do" method="post" name="form2" enctype="multipart/form-data" target="uploadFrame">
                    <s:file name="file" label="文件" id="fileName"></s:file>
                    <input type="button" value="导入数据" id="go" onclick="goto()">
                </form>
                <div id="bar" style="display:none;"><img src="./images/27.gif"/></div>
            </td>
        </tr>

        //导入
    
    
    function goto()
    {
        var fileNamem = document.getElementById("fileName").value;
        if(fileNamem == ""){
            alert("请先选中要导入的文件！");
        }else{
            form2.submit();
            $("#bar").css("display","block");
            $("#go").attr("disabled",true);
            $.ajax({
                   url:'boat!intoDB.do'
                   ,type:'POST'
                   ,data:{x:Math.random()}
                   ,success:function(data){
                       $("#bar").css("display","none");
                    //设置按钮可用
                    $("#go").attr("disabled",false);
                    alert("数据导入成功！");
                    location="boat!getAllBoat.do";
                   }
                   ,error:function(){
                       alert("数据导入失败，请检查格式是否正确！");
                       $("#bar").css("display","none");
                   }
            });
        }
    }