jsp上传excel文件并导入数据库

1，excel文件的上传

需要借助jar包：commons-fileupload-1.2.1.jar以及commons-io-1.3.2.jar

前端的html文件

 

    <form id="file_form" action="UpdFile" enctype="multipart/form-data"
        method="post">
        <input type="file" name="file" id="file_input" /> 
        <input type="submit" value="文件上传" id='upFile-btn'>
    </form>

 

2，利用ajax提交文件

为了在本页面提交文件，利用到jquery.js以及jqurey-form.js

前端验证以及提交的javascript代码如下：

<script type="text/javascript">
        $(function() {

            $("#file_form").submit(
                    function() {
                        //首先验证文件格式
                        var fileName = $('#file_input').val();
                        if (fileName === '') {
                            alert('请选择文件');
                            return false;
                        }
                        var fileType = (fileName.substring(fileName
                                .lastIndexOf(".") + 1, fileName.length))
                                .toLowerCase();
                        if (fileType !== 'xls' && fileType !== 'xlsx') {
                            alert('文件格式不正确，excel文件！');
                            return false;
                        }

                        $("#file_form").ajaxSubmit({
                            dataType : "json",
                            success : function(data, textStatus) {
                                if (data['result'] === 'OK') {
                                    console.log('上传文件成功');
                                } else {
                                    console.log('文件格式错误');
                                }
                                return false;
                            }
                        });
                        return false;
                    });

        });
    </script>

3，后台利用poi文件对excel文件进行读取（导入数据库的过程暂略）

使用到的相关jar包有：

poi-3.5-beta5.jar;　　poi-contrib-3.5-beta5.jar;　　poi-ooxml-3.5-beta5.jar　　poi-scratchpad-3.5-beta5.jar

如果是97-03版本，后缀为xls的excel文件，以上jar就已足够，如果还需要读取07版本及以后，后缀为xlsx的excel，还需要借助于jar包：

xbean.jar

dom4j-1.6.1.jar

对应网站后台的servlet处理文件：

package com.bobo.servlet;

import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Date;
import java.util.List;
import java.util.Random;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

import com.bobo.helper.ExcelHelper;

public class UpdFile extends HttpServlet {

    /**
     * The doGet method of the servlet. <br>
     * 
     * This method is called when a form has its tag value method equals to get.
     * 
     * @param request
     *            the request send by the client to the server
     * @param response
     *            the response send by the server to the client
     * @throws ServletException
     *             if an error occurred
     * @throws IOException
     *             if an error occurred
     */
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        process(request, response);
    }

    /**
     * The doPost method of the servlet. <br>
     * 
     * This method is called when a form has its tag value method equals to
     * post.
     * 
     * @param request
     *            the request send by the client to the server
     * @param response
     *            the response send by the server to the client
     * @throws ServletException
     *             if an error occurred
     * @throws IOException
     *             if an error occurred
     */
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        process(request, response);
    }

    private void process(HttpServletRequest request,
            HttpServletResponse response) throws IOException {
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/json");
        PrintWriter out = response.getWriter();
        FileItemFactory factory = new DiskFileItemFactory();
        ServletFileUpload upload = new ServletFileUpload(factory);
        upload.setHeaderEncoding(request.getCharacterEncoding());
        ExcelHelper helper = new ExcelHelper();
        try {
            List<FileItem> list = upload.parseRequest(request);
            for (int i = 0; i < list.size(); i++) {
                FileItem item = list.get(i);
                if (item.getName().endsWith(".xls")||item.getName().endsWith(".xlsx")) {
                    // 说明是文件,不过这里最好限制一下
                    //helper.importXls(item.getInputStream());
                    helper.importXlsx(item.getInputStream());
                    out.write("{\"result\":\"OK\"}");
                } else {
                    // 说明文件格式不符合要求
                    out.write("{\"result\":\"Invalid\"}");
                }
            }
            out.flush();
            out.close();

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}

4，其中helper.importXlsx(item.getInputStream())的方法如下：

    // 读取单元格的值
    private String getValue(Cell cell) {
        String result = "";

        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_BOOLEAN:
            result = cell.getBooleanCellValue() + "";
            break;
        case Cell.CELL_TYPE_STRING:
            result = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            result = cell.getCellFormula();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            // 可能是普通数字，也可能是日期
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                result = DateUtil.getJavaDate(cell.getNumericCellValue())
                        .toString();
            } else {
                result = cell.getNumericCellValue() + "";
            }
            break;
        }
        return result;
    }

    /***
     * 这种方法支持03，和07版本的excel读取
     * 但是对于合并的单元格，除了第一行第一列之外，其他部分读取的值为空
     * @param is
     */
    public void importXlsx(InputStream is) {
        try {
            Workbook wb = WorkbookFactory.create(is);
            // OPCPackage pkg = OPCPackage.open(is);
            // XSSFWorkbook wb = new XSSFWorkbook(pkg);
            for (int i = 0, len = wb.getNumberOfSheets(); i < len; i++) {
                Sheet sheet = wb.getSheetAt(i);
                for (int j = 0; j <= sheet.getLastRowNum(); j++) {
                    if (sheet == null) {
                        return;
                    }
                    Row row = sheet.getRow(j);
                    if(row==null){
                        return;
                    }
                    // 读取每一个单元格
                    for (int k = 0; k < row.getLastCellNum(); k++) {
                        Cell cell = row.getCell(k);
                        if (cell == null) {
                            return;
                        }
                        System.out.print(getValue(cell));

                    }
                    System.out.println();
                }
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

1）该方法可以读取xls文件，也可以读取xlsx文件

2）对于合并的单元格，对于xls文件有对应的方法解决，xlsx文件，还没有发现相关的解决方案，只能除了第一行第一列之外，其他都为空字符串这种方法读取了

处理合并单元格部分的代码：

/**
     * 判断是否是合并的单元格，如果是的话，返回合并区域，否则返回空（仅适用于）
     * 
     * @param sheet
     * @param cellRow
     * @param cellColumn
     * @return
     */
    private CellRangeAddress isMerged(Sheet sheet, Cell cell) {

        CellRangeAddress result = null;
        CellRangeAddress cra = null;
        int cellRow = cell.getRowIndex();
        int cellColumn = cell.getColumnIndex();
        int mergedNum = sheet.getNumMergedRegions();
        for (int i = 0; i < mergedNum; i++) {
            // 如果是xlsx的格式，怎么办？
            cra = ((HSSFSheet) sheet).getMergedRegion(i);
            if (cellRow >= cra.getFirstRow() && cellRow <= cra.getLastRow()
                    && cellColumn >= cra.getFirstColumn()
                    && cellColumn <= cra.getLastColumn()) {
                result = cra;
            }
        }
        return result;
    }

    private String getCellValue(Sheet sheet, Cell cell) {
        String result = "";
        // 判断是否是合并的单元格
        CellRangeAddress cra = null;
        if ((cra = isMerged(sheet, cell)) != null) {
            Cell fcell = sheet.getRow(cra.getFirstRow()).getCell(
                    cra.getFirstColumn());
            result = getValue(fcell);
        } else {
            result = getValue(cell);
        }
        return result;
    }

 

转载于:https://www.cnblogs.com/bobodeboke/p/4959103.html