POI，servlet，jsp 实现excel文件的导入，并存到oracle数据库中

最新推荐文章于 2023-06-12 16:25:09 发布

奋斗小码蚁

最新推荐文章于 2023-06-12 16:25:09 发布

阅读量1.9k

点赞数 1

分类专栏： javaEE 文章标签： poi excel servlet

本文链接：https://blog.csdn.net/mqchenrong/article/details/82851019

版权

javaEE 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

导包

在这里插入图片描述

实体类

package com.crh.bean;

import java.io.Serializable;

/**
 * @author Chrui
 * @date 2018/09/24__22:48
 */
public class ImportTitle implements Serializable {

    private String titles;
    private String terri;
    private String select1;
    private String score1;
    private String select2;
    private String score2;
    private String select3;
    private String score3;
    private String select4;
    private String score4;
//以下是省略getter/setter
}

 private List<ImportTitle> excelList = new ArrayList();
 private ImportTitle t = null;

后端代码一：读取xls文件

public void readXls(InputStream path) throws IOException {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(path);
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            System.out.println("总行数:" + ",总列数:" + hssfSheet.getLastRowNum());
            if (hssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum < hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    t = new ImportTitle();
                    HSSFCell title = hssfRow.getCell(0);
                    HSSFCell select1 = hssfRow.getCell(1);
                    HSSFCell score1 = hssfRow.getCell(2);
                    HSSFCell select2 = hssfRow.getCell(3);
                    HSSFCell score2 = hssfRow.getCell(4);
                    HSSFCell select3 = hssfRow.getCell(5);
                    HSSFCell score3 = hssfRow.getCell(6);
                    HSSFCell select4 = hssfRow.getCell(7);
                    HSSFCell score4 = hssfRow.getCell(8);
                    t.setTitles(getValue(title));
                    t.setSelect1(getValue(select1));
                    t.setSelect2(getValue(select2));
                    t.setSelect3(getValue(select3));
                    t.setSelect4(getValue(select4));
                    t.setScore1(getValue(score1));
                    t.setScore2(getValue(score2));
                    t.setScore3(getValue(score3));
                    t.setScore4(getValue(score4));

                    excelList.add(t);
                }
            }
        }
    }

后端代码二：servlet下使用读取文件

  /**
     * 导入文件
     */
    private void inportTitles(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        String territoryIds = null;
        Integer territoryId = 0;
        if (territoryIds != null && !territoryIds.equals("")) {
            territoryId = Integer.valueOf(territoryIds);
        }
        response.setContentType("text/html;charset=UTF-8");
        try {
            FileItemFactory factory = new DiskFileItemFactory();
            //文件上传核心工具类
            ServletFileUpload upload = new ServletFileUpload(factory);
            //单个文件大小限制
            upload.setFileSizeMax(10 * 1024 * 1024);
            //总文件大小限制
            upload.setSizeMax(50 * 1024 * 1024);
            //对中文编码处理
            upload.setHeaderEncoding("UTF-8");

            if (ServletFileUpload.isMultipartContent(request)) {
                List<FileItem> list = upload.parseRequest(request);
                //遍历
                for (FileItem item : list) {
                    String name = item.getFieldName(); // 获取name属性的值（必须要写的，当前端的表单有text类型的数据时候），就这样获取表单的元素
                    String value = item.getString("utf-8"); // 获取value属性的值
                    if (name.equals("territory")) {
                        territoryIds = value;
                    }
                    if (!item.isFormField()) {
                        //读取文件
                        readXls(item.getInputStream());
                    }
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        // 添加
     //处理添加进列表的数据
        }
    }

前端代码

<form action="${pageContext.request.contextPath}/admin/reportServlet?flag=inportTitles"
                      data-validator-option="{theme:'bootstrap', timely:2, stopOnError:true}" id="titleForm"
                      class="form-horizontal" role="form" enctype="multipart/form-data" method="post">
                    <div class="form-group">
                        <label class="control-label col-sm-3">领域：</label>
                        <div class="col-sm-5">
                            <select name="territory" id="territoryIdss" class="form-control">
                                <option selected value="0">请选择</option>
                                <c:forEach items="${territoryList}" var="terr">
                                    <option value="${terr.territoryId}">${terr.territoryName}</option>
                                </c:forEach>
                            </select>
                        </div>
                    </div>
                    <div class="form-group">
                        <label class="control-label col-sm-3">上传文件：</label>
                        <div class="col-sm-6">
                            <input id="articleImageFile" name="f1" type="file" class="form-control"
                                   style="width:100%; display: inline;"/>
                        </div>
                    </div>
                    <div class="form-inline">
                        <div class="col-sm-offset-9">
                            <input type="submit" class="btn btn-primary btn-sm" value="导入"/>
                        </div>
                    </div>

                </form>