jsp使用webuploader上传excel mysql导入导出 apache-POI

文章目录

• jsp使用webuploader上传excel mysql导入导出 apache-poi
• • 目标
  • 效果
  • 大致思路
  • 代码
  • • util
    • • excelUtil
      • DBUtil
    • servlet
    • • DownloadServlet
      • JDBCServlet
      • Log4jServlet
      • uploadServlet
    • service and dao
    • • ExcelService
      • ExcelServiceImpl
      • ExcelDao
      • ExcelDaoImpl
    • jsp
    • • add.jsp
      • data.jsp
      • list.jsp

jsp使用webuploader上传excel mysql导入导出 apache-poi

关键词：jsp，webuploader，excel上传导入导出，mysql

目标

• 让excel上传到服务器并让插入数据到数据库
• 如果需要下载则将数据库中的数据导出成excel并成功让用户下载

效果

大致思路

前端实现:layui,webuploader,jsp
后端实现:apache.poi,mysql,servlet,fastjson,commons-io
日志组件:slf4j,log4j

前端使用layui来实现，用webuploader作上传组件上传excel，用apache-poi操作excel导入和导出，编写了一个工具类ExcelUtil

最开始从add.jsp开始,需要上传就到uploadservlet中,操作是将excel传到服务器并且存入数据库中(我加了一个id字段)

list.jsp即查看数据库中有多少个excel表格,数据是从data.jsp中传入的

如果需要下载则通过downloadservlet生成excel并下载

现在的我后知后觉,应该把那些顶部的abcde…设成字段,傻了傻了

代码 util excelUtil

excel工具类

package top.sehnsucht.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import top.sehnsucht.dao.ExcelDao;
import top.sehnsucht.dao.impl.ExcelDaoImpl;
import top.sehnsucht.service.ExcelService;
import top.sehnsucht.service.impl.ExcelServiceImpl;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;



public class ExcelUtil {
    
    public static void createExcelFile(String name, String path) throws Exception {
        ExcelDao dao = new ExcelDaoImpl();
        ArrayList> allList = dao.dataToExcel(name);

        File excelFile = new File(path);
        if (!excelFile.exists()) {
            excelFile.mkdirs();
        }

        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet();
        CellStyle cellStyle = wb.createCellStyle();
        // 字体
        Font font = wb.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 20);
        cellStyle.setFont(font);
        for (int i = 0; i < allList.size(); i++) {
            // 3、创建行对象
            Row row = sheet.createRow(i);
            for (int j = 0; j < allList.get(i).size(); j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(allList.get(i).get(j));
                cell.setCellStyle(cellStyle);
            }
        }

        OutputStream os = new FileOutputStream(path + "\" + name + ".xls");
        wb.write(os);
        os.close();
    }

    
    public static void readExcelFile(String name, String filePath) throws IOException {
        ExcelDao dao = new ExcelDaoImpl();
        ExcelService service = new ExcelServiceImpl(dao);

        File xlsFile = new File(filePath);

        // 工作表
        Workbook workbook = WorkbookFactory.create(xlsFile);
        // 表个数。
        int numberOfSheets = workbook.getNumberOfSheets();
        // 遍历表。
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);

            // 行数。
            int rowNumbers = sheet.getLastRowNum() + 1;

            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }

            int cells = temp.getPhysicalNumberOfCells();

            ArrayList list = new ArrayList();
            // 读数据。
            for (int row = 0; row < rowNumbers; row++) {
                if (row == 0) {
                    Row r = sheet.getRow(row);
                    for (int col = 0; col < cells; col++) {
                        if (r.getCell(col) == null) {
                            list.add("null");
                        } else {
                            list.add(r.getCell(col).toString());
                        }
                    }
                    System.out.println(list);
                    service.createExcel(name,list);
                    continue;
                }
                ArrayList data = new ArrayList();
                Row r = sheet.getRow(row);
                for (int col = 0; col < cells; col++) {
                    if (r.getCell(col) == null) {
                        data.add("null");
                    } else {
                        data.add(r.getCell(col).toString());
                    }
                }
                service.addExcel(name,list,data);
            }
        }
    }
}

DBUtil

package top.sehnsucht.utils;

import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.log4j.Logger;


@Slf4j
public class DBUtil {
    // 定义数据库连接参数
    public static final String DRIVER_CLASS_NAME = "com.mysql.cj.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/excel";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "818181";


    // 注册数据库驱动
    static {
        try {
            Class.forName(DRIVER_CLASS_NAME);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    // 关闭连接
    public static void closeConn(Connection conn) {
        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("关闭连接失败！");
                e.printStackTrace();
            }
        }
    }

    //测试
    public static void main(String[] args) throws SQLException {
        System.out.println(DBUtil.getConn());
    }
}

servlet DownloadServlet

package top.sehnsucht;

import org.apache.log4j.Logger;
import top.sehnsucht.dao.ExcelDao;
import top.sehnsucht.dao.impl.ExcelDaoImpl;
import top.sehnsucht.service.ExcelService;
import top.sehnsucht.service.impl.ExcelServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;



public class DownloadServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Logger logger = Logger.getLogger(this.getClass());
        String path = req.getSession().getServletContext().getRealPath("/") + "\excel";
        String name = req.getParameter("name");
        logger.info(path);
        logger.info(name);
        ExcelDao dao = new ExcelDaoImpl();
        ExcelService service = new ExcelServiceImpl(dao);
        service.DataToExcel(name,path);


        resp.setContentType("application/vnd.ms-excel");
        req.getRequestDispatcher("/excel/" + name + ".xls").forward(req,resp);

        req.getRequestDispatcher("/list").forward(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

JDBCServlet

package top.sehnsucht;

import org.apache.log4j.Logger;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;


public class JDBCServlet extends HttpServlet {
    private Logger logger = Logger.getLogger(this.getClass());

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String strBackUrl = "http://" + req.getServerName() + ":" + req.getServerPort() + "/list.jsp";
        resp.setStatus(HttpServletResponse.SC_MOVED_TEMPORARILY);
        resp.setHeader("Location", strBackUrl);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

Log4jServlet

package top.sehnsucht;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.PropertyConfigurator;

import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import java.io.File;




public class Log4JInitServlet extends HttpServlet {

    @Override
    public void init(ServletConfig config) throws ServletException {
        System.out.println("Log4JInitServlet 正在初始化 log4j日志设置信息");
        String log4jLocation = config.getInitParameter("log4j-properties-location");

        ServletContext sc = config.getServletContext();

        String str= sc.getInitParameter("test");
        System.out.println("str:"+str);

        if (log4jLocation == null) {
            System.err.println("*** 没有 log4j-properties-location 初始化的文件, 所以使用 BasicConfigurator初始化");
            BasicConfigurator.configure();
        } else {
            String webAppPath = sc.getRealPath("/");
            String log4jProp = webAppPath + log4jLocation;
            File yoMamaYesThisSaysYoMama = new File(log4jProp);
            if (yoMamaYesThisSaysYoMama.exists()) {
                System.out.println("使用: " + log4jProp+"初始化日志设置信息");
                PropertyConfigurator.configure(log4jProp);
            } else {
                System.err.println("*** " + log4jProp + " 文件没有找到， 所以使用 BasicConfigurator初始化");
                BasicConfigurator.configure();
            }
        }
        super.init(config);
    }
}

uploadServlet

package top.sehnsucht;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.log4j.Logger;
import top.sehnsucht.utils.ExcelUtil;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;



public class UploadServlet extends HttpServlet {
    private Logger logger=Logger.getLogger(this.getClass());
    private Lock lock = new ReentrantLock();
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        lock.lock();
        try {
            req.setCharacterEncoding("utf-8");
            resp.setCharacterEncoding("utf-8");
            resp.setContentType("text/html; charset=UTF-8");
            logger.info("单次请求" + req);

            //判断文件是否可以上传
            boolean isMultipart = ServletFileUpload.isMultipartContent(req);
            //可以上传,有问题就报异常
            if (isMultipart) {
                //创建一个FileItem工厂，通过工厂创建文件上传核心组件ServletFileUpload对象
                ServletFileUpload upload = new ServletFileUpload(new DiskFileItemFactory());
                try {
                    //通过核心上传组件解析request请求，获取表单的所有表单项，表单的每一个表单项对应一个FileItem
                    List items = upload.parseRequest(req);
                    Iterator it = items.iterator();
                    for (FileItem item : items) {

                        if (item.isFormField()) {
                            logger.error("其他类型");
                        } else {
                            for (String s : item.getName().split("\.")) {
                                logger.error(s);
                            }

                            if (!item.getName().split("\.")[item.getName().split("\.").length - 1].equals("xls")) {
                                continue;
                            }
                            String filename = item.getName();
                            String path = req.getSession().getServletContext().getRealPath("/") + "\1";
                            File file = new File(path);
                            if (!file.exists()) {
                                file.mkdirs();
                            }
                            file = new File(path, filename);
                            item.write(file);
                            logger.debug(filename + "上传成功");
                            ExcelUtil.readExcelFile(filename.split("\.")[0],path + "\" + filename);
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            lock.unlock();
            logger.debug("解锁");
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req, resp);
    }
}

service and dao ExcelService

package top.sehnsucht.service;

import java.util.ArrayList;
import java.util.Map;

public interface ExcelService {
    void createExcel(String name, ArrayList list);

    void addExcel(String name, ArrayList list, ArrayList data);

    ArrayList> showTable();

    void DataToExcel(String name,String path);
}

ExcelServiceImpl

package top.sehnsucht.service.impl;

import top.sehnsucht.dao.ExcelDao;
import top.sehnsucht.service.ExcelService;
import top.sehnsucht.utils.ExcelUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;



public class ExcelServiceImpl implements ExcelService {
    private ExcelDao dao;

    public ExcelServiceImpl(ExcelDao dao) {
        this.dao = dao;
    }

    @Override
    public void createExcel(String name, ArrayList list) {
        try {
            dao.createExcel(name, list);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void addExcel(String name, ArrayList list, ArrayList data) {
        try {
            dao.addExcel(name, list, data);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public ArrayList> showTable() {
        try {
            return dao.showTable();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void DataToExcel(String name,String path) {
        try {
            ExcelUtil.createExcelFile(name,path);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

ExcelDao

package top.sehnsucht.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;

public interface ExcelDao {
    void createExcel(String name,ArrayList list) throws SQLException;

    void addExcel(String name, ArrayList list, ArrayList data) throws Exception;

    ArrayList> showTable() throws Exception;

    ArrayList> dataToExcel(String name) throws Exception;
}

ExcelDaoImpl

package top.sehnsucht.dao.impl;

import org.apache.log4j.Logger;
import top.sehnsucht.dao.ExcelDao;
import top.sehnsucht.utils.DBUtil;

import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;



public class ExcelDaoImpl implements ExcelDao {
    private Logger logger = Logger.getLogger(this.getClass());

    @Override
    public void createExcel(String name, ArrayList list) throws SQLException {
        Connection conn;
        Statement stmt;
        conn = DBUtil.getConn();
        stmt = conn.createStatement();
        String sql = "create table " + name + "(id  int not null auto_increment primary key";
        for (String s : list) {
            sql += " , " + s + " varchar(100) ";
        }
        sql += " ) default charset = utf8;";
        stmt.executeUpdate(sql);
        logger.debug("创建表完成");
        stmt.close();
        DBUtil.closeConn(conn);
    }

    @Override
    public void addExcel(String name, ArrayList list, ArrayList data) throws Exception {
        Connection conn;
        PreparedStatement ps = null;
        conn = DBUtil.getConn();

        String sql = "insert into " + name + " ( ";
        for (int j = 0; j < list.size(); j++) {
            if (j == 0) {
                sql += list.get(j);
            } else {
                sql += ", " + list.get(j);
            }
        }
        sql += " ) values ( ";
        for (int j = 0; j < list.size(); j++) {
            if (j == 0) {
                sql += " ? ";
            } else {
                sql += " , ? ";
            }
        }
        sql += ");";
        ps = conn.prepareStatement(sql);
        for (int j = 0; j < data.size(); j++) {
            ps.setString(j + 1, data.get(j));
        }

        int count = ps.executeUpdate();
        if (count <= 0) {
            logger.error("插入失败！");
        }
        DBUtil.closeConn(conn);
        ps.close();
    }

    @Override
    public ArrayList> showTable() throws Exception {
        Connection conn;
        PreparedStatement ps;
        ResultSet rs;
        ArrayList> arrs = new ArrayList<>();

        conn = DBUtil.getConn();
        String sql = "show tables;";

        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery(sql);
        while (rs.next()) {
            Map map = new HashMap();
            String username = rs.getString(1);
            map.put("name", username);
            arrs.add(map);
        }

        DBUtil.closeConn(conn);
        ps.close();

        return arrs;
    }

    @Override
    public ArrayList> dataToExcel(String name) throws Exception {
        Connection conn;
        PreparedStatement ps;
        ResultSet rs;
        ArrayList> list = new ArrayList>();
        conn = DBUtil.getConn();
        String sql = "select * from " + name;

        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery(sql);
        ResultSetmetaData rsmetaData = rs.getmetaData();
        int count = rsmetaData.getColumnCount();
        ArrayList header = new ArrayList<>();
        for (int i = 1; i <= count; i++) {
            System.out.println(rsmetaData.getColumnName(i));
            header.add(rsmetaData.getColumnName(i));
        }
        list.add(header);
        while (rs.next()) {
            ArrayList some = new ArrayList();
            for (int i = 1; i <= count; i++) {
                if ("null".equals(rs.getString(i))) {
                    some.add("");
                } else {
                    some.add(rs.getString(i));
                }
            }
            list.add(some);
        }
        DBUtil.closeConn(conn);
        ps.close();

        return list;
    }
}

jsp add.jsp

<%--
Created by IntelliJ IDEA.
User: windows
Date: 2021/10/29
Time: 10:08
To change this template use File | Settings | File Templates.
    --%>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
        
        
        

    


    




    

        

            

    

选择excel
查看excel表格

data.jsp

<%@ page import="com.alibaba.fastjson.JSON" %>
<%@ page import="top.sehnsucht.vo.Result" %>
<%@ page import="org.apache.log4j.Logger" %>
<%@ page import="top.sehnsucht.dao.ExcelDao" %>
<%@ page import="top.sehnsucht.dao.impl.ExcelDaoImpl" %>
<%@ page import="top.sehnsucht.service.ExcelService" %>
<%@ page import="top.sehnsucht.service.impl.ExcelServiceImpl" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%
    Logger logger = Logger.getLogger(this.getClass());

    response.setHeader("Content-Type", "application/json;charset=UTF-8");//注意加上这一句

    Result result = new Result();
    result.setCode(0);
    result.setCount(1L);

    //清空session
    request.getSession().getServletContext().setAttribute("allPicUrls", "");
    request.getSession().getServletContext().setAttribute("allFilename", "");

    ExcelDao dao = new ExcelDaoImpl();
    ExcelService service = new ExcelServiceImpl(dao);

    String id = request.getParameter("id");

    String json = JSON.toJSONString(service.showTable());

    System.out.println(json);

    out.println("{"code":" + result.getCode() + ","msg":"","count":" + result.getCount() + ","data":" + json + "}");

%>

list.jsp

<%--
  Created by IntelliJ IDEA.
  User: windows
  Date: 2021/10/29
  Time: 10:08
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>