AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

实战：第十七章：xlsx文件导入数据入库

2023-01-16 215
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 实战：第十七章：xlsx文件导入数据入库

直接上代码，先是页面，这里用jsp页面，导入的页面，后面的js发送请求通过后台controller跳转到这个页面

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<%
  String path = request.getContextPath();
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en" class="html_bg">
<head>
  <title>客户管理导入</title>
  <meta name="renderer" content="webkit">
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <link rel="stylesheet" href="<%=path%>/resource/css/layui-list.css" media="all">
  <link rel="stylesheet" href="<%=path%>/external/layui/css/layui.css"  media="all">
  <link rel="stylesheet" href="<%=path%>/resource/css/layui-dreamweb.css">
  <link rel="stylesheet" href="<%=path%>/resource/css/layui-list-${portal_skin}.css" media="all">
  <link rel="stylesheet" href="<%=path%>/external/menu-font/iconfont.css">
  <link rel="stylesheet" href="<%=path%>/resource/css/layui-card.css"  media="all">
  <link rel="stylesheet" href="<%=path%>/resource/css/layui-card-blue.css" media="all">
  <script type="text/javascript" src="<%=path%>/external/jquery-1.12.4.min.js" charset="utf-8"></script>
  <script type="text/javascript" src="<%=path%>/external/layui/layui.js" charset="utf-8"></script>
  <script type="text/javascript" src="<%=path%>/external/layui/layui.all.js?v=v1.016.000.20200529_snapshot"></script>
  <script type="text/javascript" src="<%=path%>/resource/js/file-code-choose.js"></script>
  <script type="text/javascript" src="<%=path%>/resource/js/public.js?v=${version}" charset="utf-8"></script>
  <script type="text/javascript" src="<%=path%>/resource/js/xform.js?v=v1.016.000.20200529_snapshot"></script>
</head>
<body style="height: 80%">
<div class="new_window" style="width: 80%; margin: 20px auto;">
  <form enctype="multipart/form-data" id="batchUpload">
    <input type="file" name="file" style="border: 1px solid #969696" id="file">
  </form>
  <br>
  <div style="font-size:14px;height: 250px;overflow: auto;border:1px solid #999999;padding:3px 5px; width: 80%">
    导入结果：
    <br>
    <div style="font-size:14px;" id="result">还没有导入文件！</div>
  </div>
  <br>
  <button class="datagrid-op layui-btn layui-btn-primary ds-btn2 " type="button" onclick="uploadBtn()" title="导入" id="daochu">导入</button>
  <button class="datagrid-op layui-btn layui-btn-primary ds-btn2 " type="button" onclick="colseAll()" title="导入" id="guanbi">关闭</button>
</div>
</body>
<script type="text/javascript">
    //导入
    function uploadBtn() {
debugger;
        var file = $("#file").val();
        if(file == ''){
            alert("请选择excel,再上传");
        }else if(file.lastIndexOf(".xls")<0){
            alert("只能上传Excel文件");
        }else {
            $("#result").html("正在导入请稍候...") ;
            //获取form 表单内容
            var form = document.getElementById("batchUpload");
            //获取form表单中第一个 input type是file的的对象
            var filename=$('input[type=file]')[0].files[0];
            var fm = new FormData(form);
            fm.append('file', filename);
            var url = "<%=path%>/ctrl/customerinfo/toLead";
            $.ajax({
                url: url,
                type: "post",
                data: fm,
                contentType: false, //禁止设置请求类型
                processData: false, //禁止jquery对DAta数据的处理,默认会处理
                //禁止的原因是,FormData已经帮我们做了处理
                success: function (data) {
                    $("#result").html(data.message) ;
                }
            });
        }
    }
    /**
   * 关闭
     */
    function colseAll() {
        var index=parent.layer.getFrameIndex(window.name); //获取当前窗口的name
        parent.layer.close(index);
    }
</script>
</html>

然后是js，主要是点击导入按钮去指定的导入页面

//导入按钮
layui.dataGridButton.exportCustomerinfo = function(elem, datagrid, rowPkMetaKey, funParams){
    layer.open({
        type: 2,
        title: '导入文件',
        shadeClose: true, //点击遮罩关闭层
        resize:false,
        area : ['605px' , '380px'],
        content: DSF.getURLRoot() + "ctrl/customerinfo/customer",
        end : function() {
            layui.dataGridUtil.reloadDataGrid();
        }
    });
}

Controller

package cn.p2022.module_200801181621stvy8De68zNVAglWxUP.controller;
import cn.bean.MessageBean;
import cn.web.controller.base.BaseController;
import cn.p1000.util.SuperKit;
import cn.p2022.module_200801181621stvy8De68zNVAglWxUP.service.CustomerInfoService;
import cn.p2022.module_200801181621stvy8De68zNVAglWxUP.utils.ThreadTool;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;
import javax.servlet.http.HttpServletRequest;
import java.io.InputStream;
import java.lang.reflect.Method;
@RestController
@RequestMapping("/customerinfo")
public class CustomerinfoController extends BaseController {
    @Autowired
    private CustomerInfoService customerInfoService;
    /**
     *  客户信息导入
     * @return
     */
    @RequestMapping("/toLead")
    public MessageBean toLead(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
        try {
            InputStream is = file.getInputStream();
            Method method = customerInfoService.getClass().getMethod("toLead", InputStream.class,HttpServletRequest.class);
            ThreadTool.runMethod(customerInfoService, method, is,request);
            return SuperKit.success();
        } catch (Exception e) {
            e.printStackTrace();
            return SuperKit.error("=======导入异常:"+e.getMessage());
        }
    }
    /**
     *  去导入页面
     * @return
     */
    @RequestMapping("/customer")
    public ModelAndView customer(){
        return view("p2022/200806141848M6V0Vs9aCjkvNCIZdCG/customerInfo");
    }
}

service

package cn.p2022.module_200801181621stvy8De68zNVAglWxUP.service;
import javax.servlet.http.HttpServletRequest;
import java.io.InputStream;
public interface CustomerInfoService {
    void toLead(InputStream is, HttpServletRequest request);
}

serviceimpl

package cn.p2022.module_200801181621stvy8De68zNVAglWxUP.service.impl;
import cn.persistence.dao.EntityDao;
import cn.p1000.module.common.entity.GInfosEntity;
import cn.p1000.module.userinfo.entity.User;
import cn.p1000.util.SuperKit;
import cn.p2022.module_200801181621stvy8De68zNVAglWxUP.entity.CustomerInfoEntity;
import cn.p2022.module_200801181621stvy8De68zNVAglWxUP.entity.CustomerInfoSubEntity;
import cn.p2022.module_200801181621stvy8De68zNVAglWxUP.service.CustomerInfoService;
import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import xsf.data.DBManager;
import xsf.data.DataRow;
import xsf.data.DataTable;
import xsf.data.Sql;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
@Service
public class CustomerInfoServiceImpl implements CustomerInfoService {
    @Autowired
    EntityDao entityDao;
    private static Logger log = Logger.getLogger(CustomerInfoServiceImpl.class);
    @Override
    public void toLead(InputStream file, HttpServletRequest request) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(file);
            Sheet sheet = wb.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rows; i++) {
                if (executeImport(sheet,  i,request)){
                    throw new RuntimeException("数据导入失败！！！");
                }
            }
        } catch (IOException e) {
            log.error(e.getMessage());
            throw new RuntimeException("数据导入失败！！！错误信息：" + e.getMessage());
        } catch (InvalidFormatException e) {
            log.error(e.getMessage());
            throw new RuntimeException("数据导入失败！！！错误信息：" + e.getMessage());
        }
    }
    /**
     * 执行导入操作
     * @param sheet
     * @param i
     * @return
     */
    private boolean executeImport(Sheet sheet,int i, HttpServletRequest request) {
        Row row = sheet.getRow(i);
        //客户名称
        String customerName = getCellValue(row.getCell(1));
        //别名
        String alias = getCellValue(row.getCell(2));
        //责任人
        String zrr = getCellValue(row.getCell(3));
        //客户类型
        String khlx = getCellValue(row.getCell(4));
        //客户级别
        String khjb = getCellValue(row.getCell(5));
        //地点
        String site = getCellValue(row.getCell(6));
        //客户地址
        String khdz = getCellValue(row.getCell(7));
        //邮政编码
        String khym = getCellValue(row.getCell(8));
        //传真
        String cz = getCellValue(row.getCell(9));
        //总回款金额
        String zhkje = getCellValue(row.getCell(10));
        //总意向项目金额
        String zyxxmje = getCellValue(row.getCell(11));
        //姓名
        String xm = getCellValue(row.getCell(12));
        //职务
        String zw = getCellValue(row.getCell(13));
        //手机
        String iphone = getCellValue(row.getCell(14));
        //固话
        String phone = getCellValue(row.getCell(15));
        //邮箱
        String email = getCellValue(row.getCell(16));
        //微信
        String wechat = getCellValue(row.getCell(17));
        //部门
        String ngr = getCellValue(row.getCell(18));
        //备注
        String bz = getCellValue(row.getCell(19));
        if(i != 1){
            //设置客户集合对象
            setCustomerInfoList(request,sheet,i,customerName, alias, zrr, khlx, khjb, site, khdz, khym, cz, zhkje, zyxxmje, xm, zw, iphone, phone, email, wechat, bz, ngr);
        }else {
            if(!"客户名称".equals(row.getCell(1).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("客户名称不存在与之匹配的列名");
            }
            if(!"别名".equals(row.getCell(2).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("别名不存在与之匹配的列名");
            }
            if(!"责任人".equals(row.getCell(3).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("责任人不存在与之匹配的列名");
            }
            if(!"客户类型".equals(row.getCell(4).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("客户类型不存在与之匹配的列名");
            }
            if(!"客户级别".equals(row.getCell(5).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("客户级别不存在与之匹配的列名");
            }
            if(!"地点".equals(row.getCell(6).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("地点不存在与之匹配的列名");
            }
            if(!"客户地址".equals(row.getCell(7).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("客户地址不存在与之匹配的列名");
            }
            if(!"邮政编码".equals(row.getCell(8).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("邮政编码不存在与之匹配的列名");
            }
            if(!"传真".equals(row.getCell(9).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("传真不存在与之匹配的列名");
            }
            if(!"总回款金额".equals(row.getCell(10).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("总回款金额不存在与之匹配的列名");
            }
            if(!"总意向项目金额".equals(row.getCell(11).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("总意向项目金额不存在与之匹配的列名");
            }
            if(!"姓名".equals(row.getCell(12).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("姓名不存在与之匹配的列名");
            }
            if(!"职务".equals(row.getCell(13).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("职务不存在与之匹配的列名");
            }
            if(!"手机".equals(row.getCell(14).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("手机不存在与之匹配的列名");
            }
            if(!"固话".equals(row.getCell(15).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("固话不存在与之匹配的列名");
            }
            if(!"邮箱".equals(row.getCell(16).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("邮箱不存在与之匹配的列名");
            }
            if(!"微信".equals(row.getCell(17).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("微信不存在与之匹配的列名");
            }
            if(!"部门".equals(row.getCell(18).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("部门不存在与之匹配的列名");
            }
            if(!"备注".equals(row.getCell(19).getStringCellValue().replaceAll(" ", ""))){
                throw new RuntimeException("备注不存在与之匹配的列名");
            }
        }
        return false;
    }
    /**
     * 设置客户信息集合
     * @param request
     * @param sheet
     * @param i
     * @param customerName
     * @param alias
     * @param zrr
     * @param khlx
     * @param khjb
     * @param site
     * @param khdz
     * @param khym
     * @param cz
     * @param zhkje
     * @param zyxxmje
     * @param xm
     * @param zw
     * @param iphone
     * @param phone
     * @param email
     * @param wechat
     * @param bz
     * @param ngr
     * @return
     */
    private boolean setCustomerInfoList(HttpServletRequest request,Sheet sheet,int i,String customerName, String alias, String zrr, String khlx,
                                        String khjb, String site, String khdz, String khym, String cz, String zhkje, String zyxxmje, String xm, String zw, String iphone, String phone, String email, String wechat, String bz, String ngr) {
        CustomerInfoEntity customerInfo = new CustomerInfoEntity();
        customerInfo.setBt(customerName);
        List<CustomerInfoEntity> customerInfoEntities = entityDao.find(customerInfo);
        User user = SuperKit.getLoginedUser(request);
        GInfosEntity gInfosEntity = new GInfosEntity();
        gInfosEntity.setModuleId("200806141848M6V0Vs9aCjkvNCIZdCG");
        gInfosEntity.setModuleName("客户信息");
        gInfosEntity.setCreateUserId(user.getId());
        gInfosEntity.setCreateUName(user.getUserName());
        gInfosEntity.setBt(customerName);
        gInfosEntity.setUserName(customerName);
        //查询改客户是否存在，如果存在则更新，否则添加
        boolean empty = CollectionUtils.isEmpty(customerInfoEntities);
        if(empty){
            String uuid = SuperKit.getUUID();
            setCustomerInfo(customerInfo, customerName, alias, zrr, khlx, khjb, site, khdz, khym, cz, zhkje, zyxxmje);
            CustomerInfoSubEntity customerInfoSubEntity = setCustomerInfoSub(xm, zw, iphone, phone, email, wechat, bz, ngr);
            customerInfo.setId(uuid);
            customerInfo.setIsImport("1");
            customerInfo.setRowstate("1");
            customerInfo.setModuleId("200806141848M6V0Vs9aCjkvNCIZdCG");
            customerInfo.setCreateUserid(user.getId());
            customerInfoSubEntity.setFID(uuid);
            customerInfoSubEntity.setIsImport("1");
            //入库保存
            return storeImportData(sheet,customerInfo,customerInfoSubEntity, i,gInfosEntity);
        }else {
            for (CustomerInfoEntity customerInfoEntity : customerInfoEntities) {
                setCustomerInfo(customerInfoEntity, customerName, alias, zrr, khlx, khjb, site, khdz, khym, cz, zhkje, zyxxmje);
                CustomerInfoSubEntity customerInfoSub = setCustomerInfoSub(xm, zw, iphone, phone, email, wechat, bz, ngr);
                customerInfoSub.setFID(customerInfoEntity.getId());
                customerInfoSub.setIsImport("1");
                if(storeImportData(sheet,customerInfo,customerInfoSub, i,gInfosEntity)){
                    return true;
                }
            }
            return false;
        }
    }
    /**
     * 保存导入的数据入库
     * @param sheet
     * @param customerInfo
     * @param customerInfoSubEntity
     * @param i
     * @return
     */
    private boolean storeImportData(Sheet sheet,CustomerInfoEntity customerInfo, CustomerInfoSubEntity customerInfoSubEntity,int i,GInfosEntity gInfosEntity) {
        //判断库里是否存在该客户信息
        String khmc = getCellValue(sheet.getRow(i).getCell(1));
        if(isEmpty(khmc)){
            boolean save = entityDao.save(customerInfo);
            if(!save){
                log.info("====保存失败=========客户名称为：" + customerInfo.getBt());
                return true;
            }
            save = entityDao.save(customerInfoSubEntity);
            if(!save){
                log.info("=====保存失败========姓名为：" + customerInfoSubEntity.getXm());
                return true;
            }
            gInfosEntity.setCreateTime(new Date());
            save = entityDao.save(gInfosEntity);
            if(!save){
                log.info("=========日志记录插入失败！！！============");
                return true;
            }
        }else {
            boolean update = entityDao.update(customerInfo);
            if(!update){
                log.info("====更新失败=========客户名称为：" + customerInfo.getBt());
                return true;
            }
            update = entityDao.update(customerInfoSubEntity);
            if(!update){
                log.info("=====更新失败========姓名为：" + customerInfoSubEntity.getXm());
                return true;
            }
            gInfosEntity.setLastUpdateTime(new Date());
            update = entityDao.update(gInfosEntity);
            if(!update){
                log.info("=====日志记录插入失败！！！========");
                return true;
            }
        }
        return false;
    }
    /**
     * 判断是否客户信息为空
     * @param bt
     * @return
     */
    private boolean isEmpty(String bt) {
        CustomerInfoEntity customerInfo = new CustomerInfoEntity();
        customerInfo.setBt(bt);
        List<CustomerInfoEntity> customerInfoEntities = entityDao.find(customerInfo);
        return CollectionUtils.isEmpty(customerInfoEntities);
    }
    /**
     * 设置客户信息子表
     * @param xm
     * @param zw
     * @param iphone
     * @param phone
     * @param email
     * @param wechat
     * @param bz
     * @param ngr
     * @return
     */
    private CustomerInfoSubEntity setCustomerInfoSub(String xm, String zw, String iphone, String phone, String email, String wechat, String bz, String ngr) {
        //客户信息子表
        CustomerInfoSubEntity customerInfoSubEntity = new CustomerInfoSubEntity();
        if(xm.length() <= 50){
            customerInfoSubEntity.setXm(xm);
        }
        if(zw.length() <= 50){
            customerInfoSubEntity.setZw(zw);
        }
        if(isNumber(iphone) && iphone.length() <= 11){
            customerInfoSubEntity.setIphone(iphone);
        }
        if(phone.length() <= 50){
            customerInfoSubEntity.setPhone(phone);
        }
        if(email.length() <= 50){
            customerInfoSubEntity.setEmail(email);
        }
        if(wechat.length() <= 50){
            customerInfoSubEntity.setWechat(wechat);
        }
        if(bz.length() <= 500){
            customerInfoSubEntity.setBz(bz);
        }
        if(ngr.length() <= 50){
            customerInfoSubEntity.setNgr(ngr);
        }
        customerInfoSubEntity.setModuleId("200806182459Nr0LSMyQDkVlH12YYu7");
        return customerInfoSubEntity;
    }
    /**
     * 是否包含-
     * @param string
     * @return
     */
    private static boolean isConSpeCharacters(String string) {
        return string.contains("-");
    }
    /**
     * 是否为数值
     * @param str
     * @return
     */
    private static boolean isNumber(String str){
        String reg = "^[0-9]+(.[0-9]+)?$";
        boolean matches = str.matches(reg);
        boolean specialChar = isConSpeCharacters(str);
        return matches && !specialChar;
    }
    /**
     * 设置客户信息
     * @param customerInfo
     * @param customerName
     * @param alias
     * @param zrr
     * @param khlx
     * @param khjb
     * @param site
     * @param khdz
     * @param khym
     * @param cz
     * @param zhkje
     * @param zyxxmje
     */
    private void setCustomerInfo(CustomerInfoEntity customerInfo, String customerName, String alias, String zrr, String khlx, String khjb, String site, String khdz, String khym, String cz, String zhkje, String zyxxmje) {
        customerInfo.setBt(customerName);
        customerInfo.setBm(alias);
        Sql sql = new Sql("SELECT ID FROM G_USERINFO WHERE USER_NAME = ?");
        sql.addParameter("USER_NAME",zrr);
        DataTable dataTable = DBManager.getDataTable(sql);
        if (dataTable != null && dataTable.getRowCount() > 0) {
            for (DataRow rowData : dataTable) {
                String value = rowData.getString("ID");
                customerInfo.setZrr(value);
                customerInfo.setZrrSchema(value);
            }
        }
        customerInfo.setZrrText(zrr);
        sql = new Sql("SELECT CODE_VALUE FROM G_CODE WHERE CODE_NAME = ?");
        sql.addParameter("CODE_NAME",khlx);
        dataTable = DBManager.getDataTable(sql);
        if (dataTable != null && dataTable.getRowCount() > 0) {
            for (DataRow rowData : dataTable) {
                String value = rowData.getString("CODE_VALUE");
                customerInfo.setKhlx(value);
            }
        }
        customerInfo.setKhlxText(khlx);
        sql = new Sql("SELECT * FROM g_code a,(SELECT * FROM G_CODE WHERE class = ?) b WHERE a.`fID` = b.id AND a.code_name = ?");
        sql.addParameter("class","客户级别");
        sql.addParameter("code_name",khjb);
        dataTable = DBManager.getDataTable(sql);
        if (dataTable != null && dataTable.getRowCount() > 0) {
            for (DataRow rowData : dataTable) {
                String value = rowData.getString("code_value");
                customerInfo.setKhjb(value);
            }
        }
        customerInfo.setKhjbText(khjb);
        sql = new Sql("SELECT * FROM g_code WHERE code_name = ? AND class = ?");
        sql.addParameter("code_name",site);
        sql.addParameter("class","省份代码");
        dataTable = DBManager.getDataTable(sql);
        if (dataTable != null && dataTable.getRowCount() > 0) {
            for (DataRow rowData : dataTable) {
                String value = rowData.getString("code_value");
                customerInfo.setSite(value);
            }
        }
        customerInfo.setSiteText(site);
        customerInfo.setKhdz(khdz);
        customerInfo.setKhym(khym);
        customerInfo.setCz(cz);
        customerInfo.setZhkje(zhkje);
        customerInfo.setZyxxmje(zyxxmje);
    }
    /**
     * 判断Excel类型
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue().replaceAll(" ", ""));
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                try {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    cellValue = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}

ThreadTool

package cn.p2022.module_200801181621stvy8De68zNVAglWxUP.utils;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import java.lang.reflect.Method;
import java.util.concurrent.ExecutionException;
public class ThreadTool {
    static ThreadPoolTaskExecutor threadPoolTaskExecutor;
    static {
        threadPoolTaskExecutor = new ThreadPoolTaskExecutor();
        threadPoolTaskExecutor.setCorePoolSize(5);
        threadPoolTaskExecutor.setMaxPoolSize(10);
        threadPoolTaskExecutor.setQueueCapacity(1000);
        threadPoolTaskExecutor.initialize();
    }
    /**
     * 使用线程池执行业务方法并加入视图
     * @param service 要调用的service
     * @param method 被调用的方法
     * @param param 方法参数
     */
    public static void runMethod(Object service, Method method, Object... param) throws ExecutionException, InterruptedException {
        threadPoolTaskExecutor.submit(new RunInThreadPool(service,method,param)).get();
    }
}

RunInThreadPool

package cn.p2022.module_200801181621stvy8De68zNVAglWxUP.utils;
import org.apache.log4j.Logger;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
public class RunInThreadPool implements Runnable {
    Object service;
    Method method;
    Object[] param;
    private static Logger log = Logger.getLogger(RunInThreadPool.class);
    /**
     * @param service        要调用的service
     * @param method         被调用的方法
     * @param param          方法参数
     */
    public RunInThreadPool(Object service, Method method, Object... param) {
        this.service = service;
        this.method = method;
        this.param = param;
    }
    @Override
    public void run() {
        try {
            Long start = System.currentTimeMillis();
            method.invoke(service, param);
            Long end = System.currentTimeMillis();
            log.info(String.format("%s *** 执行 ((( %s ))) 方法,耗时 <<< %s 秒 >>> 参数",service.getClass(),method.getName(),(end - start),param));
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            log.error(e.getMessage());
        } catch (InvocationTargetException e) {
            e.printStackTrace();
            log.error(e.getMessage());
        }
    }
}

实体类就不发了哈，业务层嵌套的有点多，其中WorkbookFactory和Sheet类是通用的org.apache.poi.ss.usermodel;

文章标签：
Java
JavaScript
我是廖志伟
目录
相关文章
艾派森_
|
6月前
|
数据采集 机器学习/深度学习 数据可视化
【办公自动化】使用Python批量处理Excel文件并转为csv文件
【办公自动化】使用Python批量处理Excel文件并转为csv文件
艾派森_
177 0
jianz123
|
4月前
|
关系型数据库 MySQL 数据库
使用Python读取xlsx表格数据并导入到MySQL数据库中时遇到的问题24
【7月更文挑战第24天】使用Python读取xlsx表格数据并导入到MySQL数据库中
jianz123
62 7
mingupup
|
3月前
|
开发框架 算法 .NET
C#使用MiniExcel导入导出数据到Excel/CSV文件
C#使用MiniExcel导入导出数据到Excel/CSV文件
mingupup
78 0
游客ywxcp53cgkqwi
Dynamo导出明细表至Excel
Dynamo导出明细表至Excel
游客ywxcp53cgkqwi
809 0
Dynamo导出明细表至Excel
gamonxfdgmihc
|
存储 数据采集 文件存储
kettle开发篇-批量读取EXCEL文件
笔记
gamonxfdgmihc
917 0
kettle开发篇-批量读取EXCEL文件
低代码布道师
|
JSON NoSQL 小程序
小程序中实现excel数据的批量导入
小程序中实现excel数据的批量导入
低代码布道师
363 0
小程序中实现excel数据的批量导入
条码打印
|
关系型数据库 数据库 Oracle
如何从Excel表格导入数据批量生成二维码
目前二维码应用渐趋广泛，二维码具有储存量大、保密性高、追踪性高、抗损性强、备援性大、成本便宜等特性，这些特性特别适用于表单、安全保密、追踪、证照、存货盘点、资料备援等方面。那么我们怎么用条码打印软件从现有Excel表格中导入数据批量生成我们所需要的二维码呢? 首先我们打开条码打印软件，点击“新建”...
条码打印
2740 0
go-Jack
|
BI
【机房收费系统——导入Excel】
【机房收费系统——导入Excel】
go-Jack
114 0
长沙红胖子Qt
|
数据库
c实用技巧：纯c对于excel数据表的处理
c实用技巧：纯c对于excel数据表的处理
长沙红胖子Qt
204 0
c实用技巧：纯c对于excel数据表的处理
游客tmj4hgt7bfvas
|
BI
Kettle使用模板文件生成Excle报表
Kettle使用模板文件生成Excle报表
游客tmj4hgt7bfvas
491 0