共计 14147 个字符，预计需要花费 36 分钟才能阅读完成。
按钮：
<button class="layui-btn layui-btn-sm" id="upload_importCustomer"> 导入 </button>upload 控件渲染：
upload.render({
elem: '#upload_importCustomer'
,url: ctx+'/customer/customer/importData/'
,accept: 'file' // 普通文件
,data:{importType:1}
,done: function(response){layer.msg('导入完成，若无数据，请到【导入记录】页面查看, 或 5 秒后刷新');
setTimeout(function () {active.reload();
},3000);
}
,error: function(){layer.msg('导入失败，请到【导入记录】页面查看！');
active.reload();}
});java:
/**
* 导入客户
*/
@RequestMapping(value = "importData")
@ResponseBody
public Object importData(@RequestParam("importType") Integer importType,HttpServletRequest request, HttpServletResponse response){BaseResp resp = new BaseResp();
try {MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultiValueMap<String, MultipartFile> map = multipartRequest.getMultiFileMap();
StringBuffer sb = new StringBuffer();
ExcelReadUtil excel = null;
if (map != null) {Iterator iter = map.keySet().iterator();
while (iter.hasNext()) {String str = (String) iter.next();
List<MultipartFile> fileList = map.get(str);
for (MultipartFile mpf : fileList) {excel = new ExcelReadUtil();
excel.importExcel(mpf.getInputStream());
String errMsg = "";
Map<String,Object> checkMap = CustomerImportUtils.checkCustomerImport(excel,errMsg);
if ("false".equals(checkMap.get("check"))) {sb.append(mpf.getOriginalFilename()+ "文件,"+checkMap.get("msg")+"未填写");
} else {// List<AccountCustomerImportPo> records = CustomerImportUtils.getAccountCustomerFormExcel(excel);
List<CustomerImportRecordPo> records = (List<CustomerImportRecordPo>)checkMap.get("list");
System.out.println("records.size="+records.size());
ImportRecordThread thread = new ImportRecordThread(records, mpf.getOriginalFilename(),importType,getUser());
thread.start();}
}
}
}
if (sb.length() > 0) {resp.setCode(BaseResp.RC_NOT);
resp.setSuccess(false);
resp.setMsg(sb.toString());
}
} catch (Exception e) {e.printStackTrace();
logger.warn("controller Exception:", e);
resp.setCode(BaseResp.RC_NOT);
resp.setSuccess(false);
resp.setMsg("导入客户失败！请检查文件格式和必填栏目");
}
return resp;
} 重点代码是：
List<MultipartFile> fileList = map.get(str);
获取文件。
处理文件：
excel = new ExcelReadUtil();
excel.importExcel(mpf.getInputStream());ExcelReadUtil.java:
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
public class ExcelReadUtil {
private XSSFWorkbook workbook;// 工作簿
/**
* 读取文件路径字符串。* <p>
* 详细描述。* </p>
*
* @param strfile
*/
public void importExcel(String strfile) {
try {
// 获取工作薄 workbook
workbook = new XSSFWorkbook(new FileInputStream(strfile));
} catch (Exception e) {e.printStackTrace();
}
}
/**
* 读取文件。* <p>
* 详细描述。* </p>
*
* @param file
*/
public void importExcel(File file) {
try {workbook = new XSSFWorkbook(new FileInputStream(file));
} catch (Exception e) {e.printStackTrace();
}
}
/**
* 读取文件流。* <p>
* 详细描述。* </p>
*
* @param filestream
*/
public void importExcel(InputStream filestream) {
try {workbook = new XSSFWorkbook(filestream);
} catch (Exception e) {e.printStackTrace();
}
}
/**
* 获取想要第几张工作表第几行的数据 importExcel 导入。* <p>
* 详细描述。* </p>
*
* @param sheetNumber
* @return
*/
@SuppressWarnings("deprecation")
public List<Vector<Object>> readSet(int sheetNumber, int rowIndex) {List<Vector<Object>> result = new ArrayList<Vector<Object>>();
try {
// 获得指定的 sheet
XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
// 获得 sheet 总行数
int rowCount = sheet.getLastRowNum();
if (rowCount < 1) {return result;}
// HashMap<Integer, Object> map=new HashMap<Integer, Object>();
// 遍历行 row
for (; rowIndex <= rowCount; rowIndex++) {
// 获得行对象
XSSFRow row = sheet.getRow(rowIndex);
if (null != row) {// List<Object> rowData = new ArrayList<Object>();
Vector<Object> vector = new Vector<Object>();
// 获得本行中单元格的个数
int cellCount = row.getLastCellNum();
// 遍历列 cell
for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {XSSFCell cell = row.getCell(cellIndex);
// 获得指定单元格中的数据
Object cellStr = this.getCellString(cell);
// map.put(arg0, arg1)
vector.add(cellStr);
}
result.add(vector);
}
}
} catch (Exception e) {System.out.println("readSet error:"+e.getMessage());
result = null;
}
return result;
}
/**
* 获取想要第几张工作表的数据 importExcel 导入。* <p>
* 详细描述。* </p>
*
* @param sheetNumber
* @return
*/
@SuppressWarnings({"unchecked", "deprecation"})
public List readSet(int sheetNumber) {List<List> result = new ArrayList<List>();
// 获得指定的 sheet
XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
// 获得 sheet 总行数
int rowCount = sheet.getLastRowNum();
if (rowCount < 1) {return result;}
// HashMap<Integer, Object> map=new HashMap<Integer, Object>();
// 遍历行 row
for (int rowIndex = 0; rowIndex <= rowCount; rowIndex++) {
// 获得行对象
XSSFRow row = sheet.getRow(rowIndex);
if (null != row) {// List<Object> rowData = new ArrayList<Object>();
Vector<Object> vector = new Vector<Object>();
// 获得本行中单元格的个数
int cellCount = row.getLastCellNum();
// 遍历列 cell
for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {XSSFCell cell = row.getCell(cellIndex);
// 获得指定单元格中的数据
Object cellStr = this.getCellString(cell);
// map.put(arg0, arg1)
vector.add(cellStr);
}
result.add(vector);
}
}
return result;
}
/**
* 从第几张工作表第几行的数据 importExcel 导入。* <p>
* 详细描述。* </p>
*
* @param sheetNumber
* @param rowIndex
* @return
*/
@SuppressWarnings({"unchecked", "deprecation"})
public List readRow(int sheetNumber, int rowIndex) {List result = new ArrayList();
// 获得指定的 sheet
XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
// 获得 sheet 总行数
int rowCount = sheet.getLastRowNum();
if (rowCount < 1) {return result;}
// 遍历行 row
// for (int rowIndex = rows+2; rowIndex <= rowCount; rowIndex++) {
// 获得行对象
XSSFRow row = sheet.getRow(rowIndex);
if (null != row) {// Vector<Object> vector=new Vector<Object>();
// 获得本行中单元格的个数
int cellCount = row.getLastCellNum();
// 遍历列 cell
for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {XSSFCell cell = row.getCell(cellIndex);
// 获得指定单元格中的数据
Object cellStr = this.getCellString(cell);
// vector.add(cellStr);
result.add(cellStr);
}
}
// }
return result;
}
/**
* 获取指定工作表的总。* <p>
* 详细描述。* </p>
*
* @param sheetNumber
* @return
*/
public int getRowIndex(int sheetNumber) {XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
// 获得 sheet 总行数
int rowCount = sheet.getLastRowNum();
if (rowCount < 1) {return 0;}
return rowCount;
}
/**
* 从第几张工作表第几行读到第几行。* <p>
* 详细描述。* </p>
*
* @param sheetNumber
* @param rows
* @param getrows
* @return
*/
@SuppressWarnings({"unchecked", "deprecation"})
public List readCell(int sheetNumber, int rows, int getrows) {List<List> result = new ArrayList<List>();
// 获得指定的 sheet
XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
// 获得 sheet 总行数
int rowCount = getrows;
if (rowCount < 1) {return result;}
// HashMap<Integer, Object> map=new HashMap<Integer, Object>();
// 遍历行 row
for (int rowIndex = rows + 2; rowIndex <= rowCount; rowIndex++) {
// 获得行对象
XSSFRow row = sheet.getRow(rowIndex);
if (null != row) {// List<Object> rowData = new ArrayList<Object>();
Vector<Object> vector = new Vector<Object>();
// 获得本行中单元格的个数
int cellCount = row.getLastCellNum();
// 遍历列 cell
for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {XSSFCell cell = row.getCell(cellIndex);
// 获得指定单元格中的数据
Object cellStr = this.getCellString(cell);
// map.put(arg0, arg1)
vector.add(cellStr);
}
result.add(vector);
}
}
return result;
}
/**
* 读取第几张工作表的第几列。* <p>
* 详细描述。* </p>
*
* @param sheetNumber
* @param cells
* @return
*/
@SuppressWarnings("unchecked")
public List readColum(int sheetNumber, int cells) {List<List> result = new ArrayList<List>();
// 获得指定的 sheet
XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
// 获得 sheet 总行数
int rowCount = sheet.getLastRowNum();
if (rowCount < 1) {return result;}
// HashMap<Integer, Object> map=new HashMap<Integer, Object>();
// 遍历行 row
for (int rowIndex = 2; rowIndex <= rowCount; rowIndex++) {
// 获得行对象
XSSFRow row = sheet.getRow(rowIndex);
if (null != row) {// List<Object> rowData = new ArrayList<Object>();
Vector<Object> vector = new Vector<Object>();
// 获得本行中单元格的个数
XSSFCell cell = row.getCell(cells);
Object cellStr = this.getCellString(cell);
vector.add(cellStr);
result.add(vector);
}
}
return result;
}
/**
* 获取一个 cell 的数据类型
*
* @param cell
* @return
*/
private Object getCellString(XSSFCell cell) {
Object result = null;
if (cell != null) {
// 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
int cellType = cell.getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = cell.getRichStringCellValue().getString();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {result = cell.getDateCellValue();
} else
result = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = null;
break;
case HSSFCell.CELL_TYPE_ERROR:
result = null;
break;
default:
break;
}
}
return result;
}
/**
* 删除整行
*
* @param sheetNum
* @param rowNum
*/
public void deleteRow(int sheetNum, int rowNum) {XSSFSheet sheet = workbook.getSheetAt(sheetNum);
int lastRowNum = sheet.getLastRowNum();
if (rowNum >= 0 && rowNum < lastRowNum)
sheet.shiftRows(rowNum + 1, lastRowNum, -1);// 将行号为 rowIndex+ 1 一直到行号为 lastRowNum 的单元格全部上移一行，以便删除 rowIndex 行
if (rowNum == lastRowNum) {XSSFRow removingRow = sheet.getRow(rowNum);
if (removingRow != null)
sheet.removeRow(removingRow);
}
}
/**
* 在行最后添加一个单元格
*
* @param sheetNum
* @param rowNum
*/
public void addCell(int sheetNum, int rowNum, String content) {XSSFSheet sheet = workbook.getSheetAt(sheetNum);
XSSFRow row = sheet.getRow(rowNum);
XSSFCell cell = row.createCell(row.getLastCellNum());
XSSFCellStyle style = row.getCell(0).getCellStyle();
cell.setCellStyle(style);
cell.setCellValue(content);
}
/**
* 新增或者修改特定单元格
*
* @param sheetNum
* @param rowNum
* @param content
*/
public void addModifyCell(int sheetNum, int rowNum, int columnNum, String content) {XSSFSheet sheet = workbook.getSheetAt(sheetNum);
XSSFRow row = sheet.getRow(rowNum);
XSSFCell cell = row.getCell(columnNum);
if (row.getCell(columnNum) == null) {cell = row.createCell(columnNum);
XSSFCellStyle style = row.getCell(0).getCellStyle();
cell.setCellStyle(style);
}
cell.setCellValue(content);
}
public XSSFWorkbook getWorkbook() {return workbook;}
public void setWorkbook(XSSFWorkbook workbook) {this.workbook = workbook;}
}接下来是解析 excel 数据，转成 list:
Map<String,Object> checkMap = CustomerImportUtils.checkCustomerImport(excel,errMsg);CustomerImportUtils.java:
public class CustomerImportUtils {private static ImportException setCheckMap(Map<String,Object> checkMap,int rowIndex,int colIndex){checkMap.put("check","false");
char c = 'A';
c = (char)(c+colIndex);
checkMap.put("msg","【"+(rowIndex+2)+"行"+c+"列】");
return new ImportException();}
public static Map<String,Object> checkCustomerImport(ExcelReadUtil excel,String errMsg){Map<String,Object> checkMap = new HashMap<String,Object>();
int dataStartRow = 1;
int colIndex = 0; // 列序号
List<Vector<Object>> list = excel.readSet(0, dataStartRow);
if(list==null){checkMap.put("check","false");
checkMap.put("msg","表格中存在表达式，请检查！");
}
boolean result = true;
List<CustomerImportRecordPo> customerImportRecordPoList = new ArrayList<CustomerImportRecordPo>();
if (list != null && list.size() > 0) {for (int i = 0, index = dataStartRow; i < list.size(); i++) {CustomerImportRecordPo customerImportRecordPo = new CustomerImportRecordPo();
try{Vector<Object> vector = list.get(i);
//cjianquan 2019/11/18 这里是页签
if(vector!=null && vector.size()==1 && "客户信息".equals(vector.get(0))){continue;}
try{if (checkVectorNotNull(vector)) { // 校验行数据不为 null
int size = 1;
if (vector.size() >= size) { // 至少包含两列（至少包含两列必填项）colIndex = 0;
String region = cellDataDeal(vector.get(colIndex), ""); // 区域
if (StringUtils.isBlank(region)) {
result = false;
throw setCheckMap(checkMap,i,colIndex);
}
customerImportRecordPo.setRegion(region);
++colIndex;
String name = cellDataDeal(vector.get(colIndex), ""); // 公司名称
if (StringUtils.isBlank(name)) {
result = false;
throw setCheckMap(checkMap,i,colIndex);
}
name = name.replaceAll("","").replaceAll("\\(","（").replaceAll("\\)","）");
customerImportRecordPo.setName(name);
++colIndex;
String contacts = cellDataDeal(vector.get(colIndex), ""); // 联系人
if (StringUtils.isBlank(contacts)) {
result = false;
throw setCheckMap(checkMap,i,colIndex);
}
customerImportRecordPo.setContacts(contacts);
++colIndex;
String mobile = cellDataDeal(vector.get(colIndex), ""); // 手机号
if (StringUtils.isBlank(mobile)) {
result = false;
throw setCheckMap(checkMap,i,colIndex);
}
customerImportRecordPo.setMobile(mobile);
++colIndex;
String issueTime = cellDataDeal(vector.get(colIndex), ""); // 企业注册日期 *
customerImportRecordPo.setIssueTime(issueTime);
++colIndex;
String taxpayerType = cellDataDeal(vector.get(colIndex), ""); // 纳税人类型
customerImportRecordPo.setTaxpayerType(taxpayerType);
++colIndex;
String telephone = cellDataDeal(vector.get(colIndex), ""); // 固话号码
customerImportRecordPo.setTelephone(telephone);
++colIndex;
String address = cellDataDeal(vector.get(colIndex), ""); // 详细地址
customerImportRecordPo.setAddress(address);
++colIndex;
String industry = cellDataDeal(vector.get(colIndex), ""); // 所属行业
customerImportRecordPo.setIndustry(industry);
++colIndex;
String email = cellDataDeal(vector.get(colIndex), ""); // 电子邮箱
customerImportRecordPo.setEmail(email);
++colIndex;
String website = cellDataDeal(vector.get(colIndex), ""); // 客户网址
customerImportRecordPo.setWebsite(website);
++colIndex;
String remark = cellDataDeal(vector.get(colIndex), ""); // 备注描述
customerImportRecordPo.setRemark(remark);
++colIndex;
customerImportRecordPoList.add(customerImportRecordPo);
} else {result = false;}
}
}catch (ArrayIndexOutOfBoundsException e){
//cjianquan 2019/11/18 因为很多为空，不算入列长
e.printStackTrace();
customerImportRecordPoList.add(customerImportRecordPo);
continue;
}
}catch (ImportException e){customerImportRecordPoList.add(customerImportRecordPo);
}
if(!result){break;}
}
}
checkMap.put("list",customerImportRecordPoList);
return checkMap;
}
/**
* 处理单元格数据，最终转换成字符串类型
*
* @param cellData
* @return
*/
private static String cellDataDeal(Object cellData, String type) {
String str = "";
if (cellData == null) {} else if (cellData instanceof String) {str = (String) cellData;
} else if (cellData instanceof Double) {double tmp = (Double) cellData;
if (type.equals("integer")) {long tmp2 = (long) tmp;
str = String.valueOf(tmp2);
} else if (type.equals("double")) {str = String.valueOf(tmp);
} else {long tmp2 = (long) tmp;
str = String.valueOf(tmp2);
}
// long tmp2 = (long) tmp;
} else if (cellData instanceof Integer) {int tmp = (Integer) cellData;
str = String.valueOf(tmp);
} else if (cellData instanceof Float) {float tmp = (Float) cellData;
str = String.valueOf(tmp);
} else if (cellData instanceof Long) {long tmp = (Long) cellData;
str = String.valueOf(tmp);
} else if (cellData instanceof Date) {SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
str = sdf.format(cellData);
} else {str = (String) cellData;
}
return str;
}
/**
* 校验行数据不为 null
*
* @param vector
* @return
*/
private static boolean checkVectorNotNull(Vector<Object> vector) {
int j = 0;
int size = vector.size();
for (int i = 0; i < size; i++) {if (vector.get(i) != null) {return true;} else {j++;}
}
if (j == size) {return false;} else {return true;}
}ImportException 是自定义异常类：
public class ImportException extends Exception {
private static final long serialVersionUID = -6357149550353160810L;
private int code;
private String msg;
public ImportException(){super();
}
public ImportException(int code){super("ImportException code="+code);
}
public ImportException(int code, String msg){super("ImportException code="+code+",msg="+msg);
}
}
正文完
