個(gè)人中心
退出
實(shí)戰(zhàn)：第十七章：xlsx文件導(dǎo)入數(shù)據(jù)入庫(kù)
直接上代碼，先是頁(yè)面，這里用jsp頁(yè)面，導(dǎo)入的頁(yè)面，后面的js發(fā)送請(qǐng)求通過(guò)后臺(tái)controller跳轉(zhuǎn)到這個(gè)頁(yè)面
<%@ 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>客戶(hù)管理導(dǎo)入</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%">
導(dǎo)入結(jié)果：
<br>
<div style="font-size:14px;" id="result">還沒(méi)有導(dǎo)入文件！</div>
</div>
<br>
<button class="datagrid-op layui-btn layui-btn-primary ds-btn2 " type="button" onclick="uploadBtn()" title="導(dǎo)入" id="daochu">導(dǎo)入</button>
<button class="datagrid-op layui-btn layui-btn-primary ds-btn2 " type="button" onclick="colseAll()" title="導(dǎo)入" id="guanbi">關(guān)閉</button>
</div>
</body>
<script type="text/javascript">
//導(dǎo)入
function uploadBtn() {
debugger;
var file = $("#file").val();
if(file == ''){
alert("請(qǐng)選擇excel,再上傳");
}else if(file.lastIndexOf(".xls")<0){
alert("只能上傳Excel文件");
}else {
$("#result").html("正在導(dǎo)入請(qǐng)稍候...") ;
//獲取form 表單內(nèi)容
var form = document.getElementById("batchUpload");
//獲取form表單中第一個(gè) input type是file的的對(duì)象
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, //禁止設(shè)置請(qǐng)求類(lèi)型
processData: false, //禁止jquery對(duì)DAta數(shù)據(jù)的處理,默認(rèn)會(huì)處理
//禁止的原因是,FormData已經(jīng)幫我們做了處理
success: function (data) {
$("#result").html(data.message) ;
}
});
}
}
/**
* 關(guān)閉
*/
function colseAll() {
var index=parent.layer.getFrameIndex(window.name); //獲取當(dāng)前窗口的name
parent.layer.close(index);
}
</script>
</html>
然后是js，主要是點(diǎn)擊導(dǎo)入按鈕去指定的導(dǎo)入頁(yè)面
//導(dǎo)入按鈕
layui.dataGridButton.exportCustomerinfo = function(elem, datagrid, rowPkMetaKey, funParams){
layer.open({
type: 2,
title: '導(dǎo)入文件',
shadeClose: true, //點(diǎn)擊遮罩關(guān)閉層
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;
/**
* 客戶(hù)信息導(dǎo)入
* @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("=======導(dǎo)入異常:"+e.getMessage());
}
}
/**
* 去導(dǎo)入頁(yè)面
* @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("數(shù)據(jù)導(dǎo)入失敗?。?！");
}
}
} catch (IOException e) {
log.error(e.getMessage());
throw new RuntimeException("數(shù)據(jù)導(dǎo)入失敗?。?！錯(cuò)誤信息：" + e.getMessage());
} catch (InvalidFormatException e) {
log.error(e.getMessage());
throw new RuntimeException("數(shù)據(jù)導(dǎo)入失?。。?！錯(cuò)誤信息：" + e.getMessage());
}
}
/**
* 執(zhí)行導(dǎo)入操作
* @param sheet
* @param i
* @return
*/
private boolean executeImport(Sheet sheet,int i, HttpServletRequest request) {
Row row = sheet.getRow(i);
//客戶(hù)名稱(chēng)
String customerName = getCellValue(row.getCell(1));
//別名
String alias = getCellValue(row.getCell(2));
//責(zé)任人
String zrr = getCellValue(row.getCell(3));
//客戶(hù)類(lèi)型
String khlx = getCellValue(row.getCell(4));
//客戶(hù)級(jí)別
String khjb = getCellValue(row.getCell(5));
//地點(diǎn)
String site = getCellValue(row.getCell(6));
//客戶(hù)地址
String khdz = getCellValue(row.getCell(7));
//郵政編碼
String khym = getCellValue(row.getCell(8));
//傳真
String cz = getCellValue(row.getCell(9));
//總回款金額
String zhkje = getCellValue(row.getCell(10));
//總意向項(xiàng)目金額
String zyxxmje = getCellValue(row.getCell(11));
//姓名
String xm = getCellValue(row.getCell(12));
//職務(wù)
String zw = getCellValue(row.getCell(13));
//手機(jī)
String iphone = getCellValue(row.getCell(14));
//固話(huà)
String phone = getCellValue(row.getCell(15));
//郵箱
String email = getCellValue(row.getCell(16));
//微信
String wechat = getCellValue(row.getCell(17));
//部門(mén)
String ngr = getCellValue(row.getCell(18));
//備注
String bz = getCellValue(row.getCell(19));
if(i != 1){
//設(shè)置客戶(hù)集合對(duì)象
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(!"客戶(hù)名稱(chēng)".equals(row.getCell(1).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("客戶(hù)名稱(chēng)不存在與之匹配的列名");
}
if(!"別名".equals(row.getCell(2).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("別名不存在與之匹配的列名");
}
if(!"責(zé)任人".equals(row.getCell(3).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("責(zé)任人不存在與之匹配的列名");
}
if(!"客戶(hù)類(lèi)型".equals(row.getCell(4).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("客戶(hù)類(lèi)型不存在與之匹配的列名");
}
if(!"客戶(hù)級(jí)別".equals(row.getCell(5).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("客戶(hù)級(jí)別不存在與之匹配的列名");
}
if(!"地點(diǎn)".equals(row.getCell(6).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("地點(diǎn)不存在與之匹配的列名");
}
if(!"客戶(hù)地址".equals(row.getCell(7).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("客戶(hù)地址不存在與之匹配的列名");
}
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(!"總意向項(xiàng)目金額".equals(row.getCell(11).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("總意向項(xiàng)目金額不存在與之匹配的列名");
}
if(!"姓名".equals(row.getCell(12).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("姓名不存在與之匹配的列名");
}
if(!"職務(wù)".equals(row.getCell(13).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("職務(wù)不存在與之匹配的列名");
}
if(!"手機(jī)".equals(row.getCell(14).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("手機(jī)不存在與之匹配的列名");
}
if(!"固話(huà)".equals(row.getCell(15).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("固話(huà)不存在與之匹配的列名");
}
if(!"郵箱".equals(row.getCell(16).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("郵箱不存在與之匹配的列名");
}
if(!"微信".equals(row.getCell(17).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("微信不存在與之匹配的列名");
}
if(!"部門(mén)".equals(row.getCell(18).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("部門(mén)不存在與之匹配的列名");
}
if(!"備注".equals(row.getCell(19).getStringCellValue().replaceAll(" ", ""))){
throw new RuntimeException("備注不存在與之匹配的列名");
}
}
return false;
}
/**
* 設(shè)置客戶(hù)信息集合
* @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("客戶(hù)信息");
gInfosEntity.setCreateUserId(user.getId());
gInfosEntity.setCreateUName(user.getUserName());
gInfosEntity.setBt(customerName);
gInfosEntity.setUserName(customerName);
//查詢(xún)改客戶(hù)是否存在，如果存在則更新，否則添加
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");
//入庫(kù)保存
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;
}
}
/**
* 保存導(dǎo)入的數(shù)據(jù)入庫(kù)
* @param sheet
* @param customerInfo
* @param customerInfoSubEntity
* @param i
* @return
*/
private boolean storeImportData(Sheet sheet,CustomerInfoEntity customerInfo, CustomerInfoSubEntity customerInfoSubEntity,int i,GInfosEntity gInfosEntity) {
//判斷庫(kù)里是否存在該客戶(hù)信息
String khmc = getCellValue(sheet.getRow(i).getCell(1));
if(isEmpty(khmc)){
boolean save = entityDao.save(customerInfo);
if(!save){
log.info("====保存失敗=========客戶(hù)名稱(chēng)為：" + 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("====更新失敗=========客戶(hù)名稱(chēng)為：" + 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;
}
/**
* 判斷是否客戶(hù)信息為空
* @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);
}
/**
* 設(shè)置客戶(hù)信息子表
* @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) {
//客戶(hù)信息子表
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("-");
}
/**
* 是否為數(shù)值
* @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;
}
/**
* 設(shè)置客戶(hù)信息
* @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","客戶(hù)級(jí)別");
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類(lèi)型
* @param cell
* @return
*/
public static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//把數(shù)字當(dāng)成String來(lái)讀，避免出現(xiàn)1讀成1.0的情況
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判斷數(shù)據(jù)的類(lèi)型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //數(shù)字
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 = "未知類(lèi)型";
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();
}
/**
* 使用線(xiàn)程池執(zhí)行業(yè)務(wù)方法并加入視圖
* @param service 要調(diào)用的service
* @param method 被調(diào)用的方法
* @param param 方法參數(shù)
*/
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 要調(diào)用的service
* @param method 被調(diào)用的方法
* @param param 方法參數(shù)
*/
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 *** 執(zhí)行 ((( %s ))) 方法,耗時(shí) <<< %s 秒 >>> 參數(shù)",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());
}
}
}
實(shí)體類(lèi)就不發(fā)了哈，業(yè)務(wù)層嵌套的有點(diǎn)多，其中WorkbookFactory和Sheet類(lèi)是通用的org.apache.poi.ss.usermodel;
分類(lèi)導(dǎo)航