将表格数据读取到List<Map<String,Object>>集合中并进行校验
ParseExcelUtil
@RestController
@RequestMapping("/v1")
public class ParseExcelUtil {
@Autowired
private EnterpriseBaseMapper enterpriseBaseMapper;
@Autowired
private OutputValueMapper outputValueMapper;
@Autowired
private TaxMapper taxMapper;
@Autowired
private InvestMapper investMapper;
@Autowired
private ShareholderMapper shareholderMapper;
@Autowired
private InvestDetailMapper investDetailMapper;
public FileInputStream fis;
public HSSFWorkbook workBook;
public HSSFSheet sheet;
public ParseXMLUtil parseXmlUtil;
public StringBuffer errorString;
/**
* 当前实体类的code
**/
public String curEntityCode;
/**
* 表头map对象：key:entityCode, value:headMap(index,headTitle)
**/
public Map curEntityHeadMap;
/**
* 字段的必填：key:entityCode+headTitle, value:true(必填),false(不必填)
**/
public Map curEntityColRequired;
/**
* 存放每一行的数据
**/
public List listDatas;
@RequestMapping(value = "/excel", method = RequestMethod.POST)
public ResponseObj<String> importExcel(HttpServletRequest request) {
// File excelFile = new File("E:/ECProject/企业管理 - 副本.xls");
// File excelFile = new File("E:/ECProject/企业管理.xls");
// File excelFile = new File("E:/ECProject/产值指标管理.xls");
// File excelFile = new File("E:/ECProject/税务指标管理.xls");
// File excelFile = new File("E:/ECProject/招商指标管理.xls");
// File excelFile = new File("E:/ECProject/股东信息管理.xls");
// File excelFile = new File("E:/ECProject/招商信息细则管理.xls");
// File xmlFile = returnXmlFile(excelFile);
// parseExcelUtil(excelFile, xmlFile);
if (request instanceof MultipartHttpServletRequest) {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Set<Map.Entry<String, MultipartFile>> set = multipartRequest.getFileMap().entrySet();
for (Map.Entry<String, MultipartFile> multipartFileEntry : set) {
MultipartFile value = multipartFileEntry.getValue();
try {
FileInputStream fis = (FileInputStream) value.getInputStream();
File xmlFile = returnXmlFile(fis);
parseExcelUtil(fis, xmlFile);
} catch (IOException e) {
e.printStackTrace();
}
}
if (this.getErrorString().length() == 0) {//如果没有任何错误，就保存
return new ResponseObj<>("导入表格数据成功", RetCode.SUCCESS);
} else {
return new ResponseObj<>(errorString.toString(), RetCode.FAIL);
}
} else {
return new ResponseObj<>("导入表格数据失败", RetCode.FAIL);
}
}
public File returnXmlFile(/*File excelFile*/FileInputStream fis) {
File xmlFile = null;
try {
// fis = new FileInputStream(excelFile);
workBook = new HSSFWorkbook(fis);
sheet = workBook.getSheetAt(0);
String entityName = workBook.getSheetName(0);
if (entityName.equals(ParseConstans.ENTERPRISE_BASE)) {
URL url = ParseExcelUtil.class.getClassLoader().getResource("company.xml");
xmlFile = new File(url.getFile());
} else if (entityName.equals(ParseConstans.ENTERPRISE_OUTPUT_VALUE)) {
URL url = ParseExcelUtil.class.getClassLoader().getResource("output_value.xml");
xmlFile = new File(url.getFile());
} else if (entityName.equals(ParseConstans.ENTERPRISE_TAX)) {
URL url = ParseExcelUtil.class.getClassLoader().getResource("tax.xml");
xmlFile = new File(url.getFile());
} else if (entityName.equals(ParseConstans.ENTERPRISE_INVEST)) {
URL url = ParseExcelUtil.class.getClassLoader().getResource("invest.xml");
xmlFile = new File(url.getFile());
} else if (entityName.equals(ParseConstans.ENTERPRISE_SHAREHOLDER)) {
URL url = ParseExcelUtil.class.getClassLoader().getResource("shareholder.xml");
xmlFile = new File(url.getFile());
} else if (entityName.equals(ParseConstans.ENTERPRISE_INVEST_DETAIL)) {
URL url = ParseExcelUtil.class.getClassLoader().getResource("invest_detail.xml");
xmlFile = new File(url.getFile());
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return xmlFile;
}
public void parseExcelUtil(/*File excelFile*/FileInputStream fis, File xmlFile) {
// try {
// if (excelFile == null) {
// throw new FileNotFoundException();
// }
// fis = new FileInputStream(excelFile);
// workBook = new HSSFWorkbook(fis);
// parseXmlUtil = new ParseXMLUtil();
// parseXmlUtil.parseXMLUtil(xmlFile);
// errorString = new StringBuffer();
// readExcelData();
// } catch (FileNotFoundException e) {
// e.printStackTrace();
// } catch (IOException e) {
// e.printStackTrace();
// }
parseXmlUtil = new ParseXMLUtil();
parseXmlUtil.parseXMLUtil(xmlFile);
errorString = new StringBuffer();
readExcelData();
}
/**
* 开始从excel读取数据
**/
public void readExcelData() {
int sheetSize = workBook.getNumberOfSheets();
for (int i = 0; i < sheetSize; i++) {
sheet = workBook.getSheetAt(i);
String entityName = workBook.getSheetName(i); //excel表格名
readSheetData(sheet, entityName);
}
}
/**
* 读每个sheet页的数据
**/
public void readSheetData(HSSFSheet sheet, String entityName) {
int rowNumbers = sheet.getPhysicalNumberOfRows();
Map ent = (Map) parseXmlUtil.getEntityMap().get(entityName); //"code" -> "t_enterprise_base_info";"name" ->
// "企业管理"
this.setCurEntityCode((String) ent.get("code"));
if (rowNumbers == 0) {
System.out.println("================excel中数据为空！");
errorString.append(ParseConstans.ERROR_EXCEL_NULL);
}
List colList = (List) parseXmlUtil.getColumnListMap().get(entityName);
int xmlRowNum = colList.size();
HSSFRow excelRow = sheet.getRow(0);
int excelFirstRow = excelRow.getFirstCellNum();
int excelLastRow = excelRow.getLastCellNum();
// if(xmlRowNum != (excelLastRow-excelFirstRow)){
// System.out.println("==================xml列数与excel列数不相符，请检查");
// errorString.append(ParseConstans.ERROR_EXCEL_COLUMN_NOT_EQUAL);
// }
readSheetHeadData(sheet);
readSheetColumnData(sheet, entityName);
}
/**
* 读取sheet页中的表头信息
**/
public void readSheetHeadData(HSSFSheet sheet) {
Map headMap = new HashMap();
curEntityHeadMap = new HashMap();
curEntityColRequired = new HashMap();
HSSFRow excelheadRow = sheet.getRow(0);
int excelLastRow = excelheadRow.getLastCellNum();
String headTitle = "";
for (int i = 0; i < excelLastRow; i++) {
HSSFCell cell = excelheadRow.getCell(i);
headTitle = this.getStringCellValue(cell).trim();
if (headTitle.endsWith("*")) {
curEntityColRequired.put(this.getCurEntityCode() + "_" + headTitle, true);
} else {
curEntityColRequired.put(this.getCurEntityCode() + "_" + headTitle, false);
}
headMap.put(i, headTitle);
}
curEntityHeadMap.put(this.getCurEntityCode(), headMap);
}
/**
* 读取sheet页里面的数据
**/
public void readSheetColumnData(HSSFSheet sheet, String entityName) { //entityName=企业表
HSSFRow excelheadRow = sheet.getRow(0);
int excelLastcell = excelheadRow.getLastCellNum(); //excel总列数
int excelRowNum = sheet.getLastRowNum(); //excel总行数
Map headMap = (Map) this.getCurEntityHeadMap().get(this.getCurEntityCode());
Map xmlColMap = parseXmlUtil.getColumnMap();
listDatas = new ArrayList();
List<RowData> rowDatas = new ArrayList(); //存放不重复行数据的集合
List<RowData> rowDatasCopy = new ArrayList(); //存放不重复行数据的集合
for (int i = 1; i < excelRowNum + 1; i++) {//行循环
HSSFRow columnRow = sheet.getRow(i);
if (columnRow != null) {
RowData rowData = new RowData();
StringBuffer sb = new StringBuffer();
Map curRowCellMap = new HashMap();
for (int j = 0; j < excelLastcell; j++) { //列循环
int cout = headMap.get(j).toString().indexOf("*");
String headTitle = "";
if (cout == -1) {
headTitle = headMap.get(j).toString();
} else {
headTitle = headMap.get(j).toString().substring(0, cout);
}
Map curColMap = (Map) xmlColMap.get(entityName + "_" + headTitle);
if (curColMap != null && !"".equals(curColMap)) { //新增对头信息的判断
String curColCode = (String) curColMap.get("code");//对应数据库表中字段名
String curColType = (String) curColMap.get("type");//对应数据库表中字段类型
HSSFCell colCell = columnRow.getCell(j);
String value = this.getStringCellValue(colCell);
if (value != null) {
value = value.trim();
}
String xmlColType = (String) curColMap.get("type"); //类型
if (curColCode.equals("enterpriseId") || curColCode.equals("outputId") ||
curColCode.equals("taxId") || curColCode.equals("investId") ||
curColCode.equals("shareholderId") || curColCode.equals("investDetailId")) {
//数据库表id,excel表中没有这一列,不需要验证
value = UUID.randomUUID().toString(); //给value赋值
curRowCellMap.put(curColCode, value);
} else if (curColCode.equals("operateTime")) {
value = DateUtil.getStringDate(); //给value赋值
curRowCellMap.put(curColCode, value);
} else {
/**验证cell数据**/
validateCellData(i + 1, j + 1, colCell, entityName, headTitle, curColType, rowData, sb);
}
if (xmlColType.equals("int") || xmlColType.equals("Double") || xmlColType.equals("decimal")) {
curRowCellMap.put(curColCode, value); //将这一行的数据以code-value的形式存入map
} else if (xmlColType.equals("String") || xmlColType.equals("Date") || xmlColType.equals
("datetime")) {
// TODO: 2018/1/31 用于sql语句直接插入
// if (value != null) {
// value = "'" + value + "'";
// }
curRowCellMap.put(curColCode, value); //将这一行的数据以code-value的形式存入map
} else {
curRowCellMap.put(curColCode, value);
}
} else {
errorString.append("第" + 1 + "行,第" + j + "列头信息:" + headTitle + "不存在" + "<br>");
}
}
//逐条验证行：数据添加到集合rowDatas中时作一个校验比较，此步验证时间太长!
String splicingValue = sb.toString();
rowData.setSplicingValue(splicingValue); //rowData下一条数据
// validateRowData(rowDatas, rowDatasCopy, rowData, splicingValue, entityName);
rowDatas.add(rowData);
listDatas.add(curRowCellMap);
}
}
// TODO: 2018/1/29
//将所有数据加入到集合中一并验证excel表格中数据,并验证表格与数据库数据
validateRowData(rowDatas, entityName);
if (this.getErrorString().length() == 0) {//如果没有任何错误，就保存
saveExcelData(entityName, xmlColMap);
} else {
//清理所有的缓存clearMap();现在暂时未清理
// TODO: 2018/1/29 清理缓存
String[] strArr = errorString.toString().split("<br>");
for (String s : strArr) {
System.out.println(s);
}
}
}
private List list = new ArrayList(); //uscc集合
private List splingList = new ArrayList(); //uscc+month集合
private Map<String, String> map = new HashMap<>();
/**
* 验证单元格数据
**/
public void validateCellData(int curRow, int curCol, HSSFCell colCell, String entityName,
String headName, String curColType, RowData rowData, StringBuffer sb) {
List rulList = (List) parseXmlUtil.getColumnRulesMap().get(entityName + "_" + headName);
if (rulList != null && rulList.size() > 0) {
for (int i = 0; i < rulList.size(); i++) {
Map rulM = (Map) rulList.get(i);
String rulName = (String) rulM.get("name");
String rulMsg = (String) rulM.get("message");
String str = this.getStringCellValue(colCell);
String cellValue = "";
if (str != null && !"".equals(str)) {
cellValue = this.getStringCellValue(colCell).trim();
} else {
cellValue = str;
}
rowData.setRow(curRow);//所在行
/*if (rulName.equals(ParseConstans.RULE_NAME_UNIQUE)) { //需要验证唯一性的时候存入到map中
if (list.contains(cellValue)) {
errorString.append("第" + curRow + "行,第" + curCol + "列:" + rulMsg + "<br>");
} else {
list.add(cellValue);
}
} else */
if (rulName.equals(ParseConstans.RULE_NAME_NULLABLE)) {
if ("".equals(cellValue) || cellValue == null) {
errorString.append("第" + curRow + "行,第" + curCol + "列:" + rulMsg + "<br>");
}
} else if (rulName.equals(ParseConstans.RULE_MONTHFORMAT)) {
if (cellValue != null && !"".equals(cellValue)) {
String[] split = cellValue.split("-");
if (split[0].length() != 4 || split[1].length() != 2) {
errorString.append("第" + curRow + "行,第" + curCol + "列:" + rulMsg + "<br>");
}
}
} else if (rulName.equals(ParseConstans.RULE_USCCCHECKUNIQUE)) { //企业统一社会信用代码和年月不能同时相同
rowData.setUsccColumn(curCol);
sb.append(cellValue);
} else if (rulName.equals(ParseConstans.RULE_MONTHCHECKUNIQUE)) { //企业统一社会信用代码和年月不能同时相同
rowData.setMonthColumn(curCol);
sb.append(cellValue);
} else if (rulName.equals(ParseConstans.RULE_TYPECHECKUNIQUE)) {
rowData.setTypeColumn(curCol);
sb.append(cellValue);
} else {
//这里写其他的验证规则。。。
}
}
}
}
/**
* 验证excel每行数据方法一
* rowData下一条数据
**/
public void validateRowData(List<RowData> rowDatas, List<RowData> rowDatasCopy,
RowData rowData, String splicingValue, String entityName) {
rowDatas.clear();
rowDatas.addAll(rowDatasCopy);
if (rowDatas != null && rowDatas.size() > 0) {
for (RowData data : rowDatas) { //data上一条数据
if (splicingValue != null && !"".equals(splicingValue)) {
if (splicingValue.equals(data.getSplicingValue())) {
if (entityName.equals(ParseConstans.ENTERPRISE_INVEST_DETAIL)) {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() + "列、第" +
rowData.getMonthColumn() + "列、第" + rowData.getTypeColumn() + "列不能和第" +
data.getRow() + "行第" + data.getUsccColumn() +
"列、第" + data.getMonthColumn() + "列、第" + data.getTypeColumn() + "列完全相同" + "<br>");
} else if (entityName.equals(ParseConstans.ENTERPRISE_BASE)) {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() + "列不能和第" +
data.getRow() + "行第" + data.getUsccColumn() + "列完全相同" + "<br>");
} else {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() + "列、第" +
rowData.getMonthColumn() + "列不能和第" + data.getRow() + "行第" + data.getUsccColumn() +
"列、第" + data.getMonthColumn() + "列完全相同" + "<br>");
}
} else {
//集合去重并且按照自然顺序排列
rowDatasCopy.add(rowData);
List<RowData> newList = new ArrayList<>(new TreeSet<>(rowDatasCopy));
rowDatasCopy.clear();
rowDatasCopy.addAll(newList);
}
}
}
} else {
rowDatas.add(rowData);
rowDatasCopy.add(rowData);
}
}
/**
* 验证excel每行数据方法二
**/
public void validateRowData(List<RowData> rowDatas, String entityName) {
for (RowData rowData : rowDatas) {
String splicingValue = rowData.getSplicingValue();
int row = rowData.getRow();
if (splicingValue != null && !"".equals(splicingValue)) {
for (RowData data : rowDatas) {
if (row != data.getRow()) {
if (splicingValue.equals(data.getSplicingValue())) {
if (entityName.equals(ParseConstans.ENTERPRISE_INVEST_DETAIL)) {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() + "列、第" +
rowData.getMonthColumn() + "列、第" + rowData.getTypeColumn() + "列不能和第" +
data.getRow() + "行第" + data.getUsccColumn() +
"列、第" + data.getMonthColumn() + "列、第" + data.getTypeColumn() + "列完全相同" +
"<br>");
} else if (entityName.equals(ParseConstans.ENTERPRISE_BASE)) {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() +
"列不能和第" +
data.getRow() + "行第" + data.getUsccColumn() + "列完全相同" + "<br>");
} else {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() + "列、第" +
rowData.getMonthColumn() + "列不能和第" + data.getRow() + "行第" + data
.getUsccColumn() +
"列、第" + data.getMonthColumn() + "列完全相同" + "<br>");
}
}
}
}
}
}
//验证excel表格中数据与数据库中数据
Map map = new HashMap();
map = CommonUtil.convertFormData(map);
validateRowData1(rowDatas, entityName, map);
}
/**
* 验证excel和数据库数据
**/
public void validateRowData1(List<RowData> rowDatas, String entityName, Map map) {
if (rowDatas != null && rowDatas.size() > 0) {
if (entityName.equals(ParseConstans.ENTERPRISE_BASE)) {
List<EnterpriseBase> enterpriseBases = enterpriseBaseMapper.selectByOrg(map);
for (RowData rowData : rowDatas) {
String splicingValue = rowData.getSplicingValue();
for (EnterpriseBase base : enterpriseBases) {
if (splicingValue.equals(base.getEnterpriseUscc())) {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() +
"列与数据库中数据重复" + "<br>");
}
}
}
} else if (entityName.equals(ParseConstans.ENTERPRISE_OUTPUT_VALUE)) {
List<OutputValue> enterpriseBases = outputValueMapper.list(map);
for (RowData rowData : rowDatas) {
String splicingValue = rowData.getSplicingValue();
for (OutputValue base : enterpriseBases) {
String usccMonth = base.getEnterpriseUscc() + base.getYearmonth();
if (splicingValue.equals(usccMonth)) {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() + "列、第" +
rowData.getMonthColumn() + "列与数据库中数据重复" + "<br>");
}
}
}
} else if (entityName.equals(ParseConstans.ENTERPRISE_TAX)) {
List<Tax> enterpriseBases = taxMapper.list(map);
for (RowData rowData : rowDatas) {
String splicingValue = rowData.getSplicingValue();
for (Tax base : enterpriseBases) {
String usccMonth = base.getEnterpriseUscc() + base.getYearmonth();
if (splicingValue.equals(usccMonth)) {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() + "列、第" +
rowData.getMonthColumn() + "列与数据库中数据重复" + "<br>");
}
}
}
} else if (entityName.equals(ParseConstans.ENTERPRISE_INVEST)) {
List<Invest> enterpriseBases = investMapper.list(map);
for (RowData rowData : rowDatas) {
String splicingValue = rowData.getSplicingValue();
for (Invest base : enterpriseBases) {
String usccMonth = base.getEnterpriseUscc() + base.getYearmonth();
if (splicingValue.equals(usccMonth)) {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() + "列、第" +
rowData.getMonthColumn() + "列与数据库中数据重复" + "<br>");
}
}
}
} else if (entityName.equals(ParseConstans.ENTERPRISE_SHAREHOLDER)) {
List<ShareHolder> enterpriseBases = shareholderMapper.list(map);
} else if (entityName.equals(ParseConstans.ENTERPRISE_INVEST_DETAIL)) {
List<InvestDetail> enterpriseBases = investDetailMapper.list(map);
for (RowData rowData : rowDatas) {
String splicingValue = rowData.getSplicingValue();
for (InvestDetail base : enterpriseBases) {
String usccMonth = base.getEnterpriseUscc() + base.getYearmonth() + base.getInvestDetailType();
if (splicingValue.equals(usccMonth)) {
errorString.append("第" + rowData.getRow() + "行第" + rowData.getUsccColumn() + "列、第" +
rowData.getMonthColumn() + "列、第" + rowData.getTypeColumn() + "列与数据库中数据重复" + "<br>");
}
}
}
}
} else {
errorString.append("excel表格没有可以插入的数据");
}
}
/**
* 保存excel里面的数据
**/
public void saveExcelData(String entityName, Map<String, Object> colMap) {
// // TODO: 2018/1/27 sql语句插入
// StringBuffer sb = new StringBuffer();
// sb.append("INSERT INTO ").append(getCurEntityCode()); //表名
// String foot = "";
// String head = "";
//
// for (int i = 0; i < this.getListDatas().size(); i++) {
// Map<String, Object> map = (Map) this.getListDatas().get(i); //得到第 i 行的数据
// StringBuffer keys = new StringBuffer();
// StringBuffer values = new StringBuffer();
// keys.append("(");
// values.append("(");
// for (String key : map.keySet()) {
// Object value = map.get(key);
// if (value != null && !"".equals(value)) {
// keys.append(key).append(",");
// values.append(value).append(",");
// }
// }
// head = keys.deleteCharAt(keys.lastIndexOf(",")).append(")").toString();
// foot += values.deleteCharAt(values.lastIndexOf(",")).append("),").toString();
// }
// sb.append(head).append("VALUES").append(foot);
// sb.deleteCharAt(sb.lastIndexOf(",")).append(";");
// DBhepler dBhepler = new DBhepler();
// dBhepler.hh(sb.toString());
// TODO: 2018/1/29 批量插入
List users = new ArrayList();
for (int i = 0; i < this.getListDatas().size(); i++) {
Map excelCol = (Map) this.getListDatas().get(i); //得到第 i 行的数据
try {
if (entityName.equals(ParseConstans.ENTERPRISE_BASE)) {
EnterpriseBase user = new EnterpriseBase();
EnterpriseBase obj = (EnterpriseBase) BeanToMapUtil.mapToObject(excelCol, user.getClass());
users.add(obj);
} else if (entityName.equals(ParseConstans.ENTERPRISE_OUTPUT_VALUE)) {
OutputValue user = new OutputValue();
OutputValue obj = (OutputValue) BeanToMapUtil.mapToObject(excelCol, user.getClass());
users.add(obj);
} else if (entityName.equals(ParseConstans.ENTERPRISE_TAX)) {
Tax user = new Tax();
Tax obj = (Tax) BeanToMapUtil.mapToObject(excelCol, user.getClass());
users.add(obj);
} else if (entityName.equals(ParseConstans.ENTERPRISE_INVEST)) {
Invest user = new Invest();
Invest obj = (Invest) BeanToMapUtil.mapToObject(excelCol, user.getClass());
users.add(obj);
} else if (entityName.equals(ParseConstans.ENTERPRISE_SHAREHOLDER)) {
ShareHolder user = new ShareHolder();
ShareHolder obj = (ShareHolder) BeanToMapUtil.mapToObject(excelCol, user.getClass());
users.add(obj);
} else if (entityName.equals(ParseConstans.ENTERPRISE_INVEST_DETAIL)) {
InvestDetail user = new InvestDetail();
InvestDetail obj = (InvestDetail) BeanToMapUtil.mapToObject(excelCol, user.getClass());
users.add(obj);
}
} catch (IntrospectionException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
if (entityName.equals(ParseConstans.ENTERPRISE_BASE)) {
int i = enterpriseBaseMapper.insertByBatch(users);
if (i == users.size()) {
System.out.println("导入数据成功！");
}
} else if (entityName.equals(ParseConstans.ENTERPRISE_OUTPUT_VALUE)) {
int i = outputValueMapper.insertByBatch(users);
if (i == users.size()) {
System.out.println("导入数据成功！");
}
} else if (entityName.equals(ParseConstans.ENTERPRISE_TAX)) {
int i = taxMapper.insertByBatch(users);
if (i == users.size()) {
System.out.println("导入数据成功！");
}
} else if (entityName.equals(ParseConstans.ENTERPRISE_INVEST)) {
int i = investMapper.insertByBatch(users);
if (i == users.size()) {
System.out.println("导入数据成功！");
}
} else if (entityName.equals(ParseConstans.ENTERPRISE_SHAREHOLDER)) {
int i = shareholderMapper.insertByBatch(users);
if (i == users.size()) {
System.out.println("导入数据成功！");
}
} else if (entityName.equals(ParseConstans.ENTERPRISE_INVEST_DETAIL)) {
int i = investDetailMapper.insertByBatch(users);
if (i == users.size()) {
System.out.println("导入数据成功！");
}
}
// //单条插入数据库
// List<User> users= new ArrayList();
// for(int i = 0 ; i<this.getListDatas().size();i++){
// Map excelCol = (Map) this.getListDatas().get(i); //得到第 i 行的数据
// User user = new User();
// try {
// User obj = (User) BeanToMapUtil.convertMap(user.getClass(), excelCol);
// users.add(obj);
// } catch (IntrospectionException e) {
// e.printStackTrace();
// } catch (IllegalAccessException e) {
// e.printStackTrace();
// } catch (InstantiationException e) {
// e.printStackTrace();
// } catch (InvocationTargetException e) {
// e.printStackTrace();
// }
//
// }
// /**批量保存数据**/
// Dao dao = new Dao();
// for(int i = 0;i<users.size();i++){
// try{
// dao.saveUser(users.get(i));
//
// }catch(Exception e){
// e.printStackTrace();
// }
// }
}
/**
* 获得单元格字符串
*
* @throws
*/
public static String getStringCellValue(HSSFCell cell) {
if (cell == null) {
return null;
}
String result = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
java.text.SimpleDateFormat TIME_FORMATTER = new java.text.SimpleDateFormat(
"yyyy-MM-dd");
result = TIME_FORMATTER.format(cell.getDateCellValue());
} else {
double doubleValue = cell.getNumericCellValue();
result = "" + doubleValue;
}
break;
case HSSFCell.CELL_TYPE_STRING:
if (cell.getRichStringCellValue() == null) {
result = null;
} else {
result = cell.getRichStringCellValue().getString();
}
break;
case HSSFCell.CELL_TYPE_BLANK:
result = null;
break;
case HSSFCell.CELL_TYPE_FORMULA:
try {
result = String.valueOf(cell.getNumericCellValue());
} catch (Exception e) {
result = cell.getRichStringCellValue().getString();
}
break;
default:
result = "";
}
return result;
}
public String getCurEntityCode() {
return curEntityCode;
}
public void setCurEntityCode(String curEntityCode) {
this.curEntityCode = curEntityCode;
}
public Map getCurEntityHeadMap() {
return curEntityHeadMap;
}
public void setCurEntityHeadMap(Map curEntityHeadMap) {
this.curEntityHeadMap = curEntityHeadMap;
}
public ParseXMLUtil getParseXmlUtil() {
return parseXmlUtil;
}
public void setParseXmlUtil(ParseXMLUtil parseXmlUtil) {
this.parseXmlUtil = parseXmlUtil;
}
public Map getCurEntityColRequired() {
return curEntityColRequired;
}
public void setCurEntityColRequired(Map curEntityColRequired) {
this.curEntityColRequired = curEntityColRequired;
}
public List getListDatas() {
return listDatas;
}
public void setListDatas(List listDatas) {
this.listDatas = listDatas;
}
public StringBuffer getErrorString() {
return errorString;
}
public void setErrorString(StringBuffer errorString) {
this.errorString = errorString;
}
}
ParseXMLUtil
@Component
public class ParseXMLUtil {
/**entity map对象，key:name ,value:entity的属性map集**/
public Map entityMap ;
/**column map 对象，key:entityName_colName , value:column的属性map集 **/
public Map columnMap;
/**rule map 对象，key:entityName_colName_ruleName, value: rule 的map集：找到一行rule**/
public Map ruleMap ;
/**rules map 对象, key:entityName_colName, value: rules 的map集:找到该column下所有的rule**/
public Map columnRulesMap ;
/**entity--column map: key:entityName, value: column list:根据实体类名得到所有的列**/
public Map columnListMap ;
/**column list**/
public List columnList ;
public String entityName;
/**主方法**/
// public static void main(String[] args) {
// File file = new File("src/company.xml");
//// new ParseXMLUtil(file);
// parseXMLUtil(file);
// }
/**开始解析xml文件**/
public void parseXMLUtil(File xmlFilePath){
FileInputStream in = null;
try {
if(xmlFilePath == null){
throw new FileNotFoundException();
}
SAXReader reader = new SAXReader();
in = new FileInputStream(xmlFilePath);
Document doc = reader.read(in);
Element root = doc.getRootElement();
Iterator itEntity = root.elements("entity").iterator();
while(itEntity.hasNext()){ //现在是用的全局变量，只能有一个entity节点！！
Element entity = (Element) itEntity.next();
parseEntity(entity);
}
/**测试entityMap 是否正确**/
Map enMap = (Map) this.getEntityMap().get(entityName); //entityName:entity节点下的name
Set<?> set = enMap.keySet();
Iterator it = set.iterator();
while(it.hasNext()){
String uu = (String) it.next();
System.out.println("entity properties:"+uu+" = "+enMap.get(uu));
}
/* *//**测试column list是否正确**//*
List colList = (List) this.getColumnListMap().get("用户表");
System.out.println("column size:"+colList.size());
*//**测试columnMap是否正确**//*
Map colMap = (Map) this.getColumnMap().get("用户表_员工号");
Set<?> coListSet = colMap.keySet();
Iterator coListIt = coListSet.iterator();
while(coListIt.hasNext()){
String coListKey = (String) coListIt.next();
System.out.println("column properties: "+coListKey+" = "+colMap.get(coListKey));
}
*//**测试ruleMap是否正确**//*
if(this.getColumnRulesMap() != null){
List rulesValidList = (List) this.getColumnRulesMap().get("用户表_员工号");
for(int i=0;i<rulesValidList.size(); i++){
Map colRuleMap = (Map) rulesValidList.get(i);
String ruleName = (String) colRuleMap.get("name");
Map ruleMa = (Map) this.getRuleMap().get("用户表_员工号_"+ruleName); //eg: 用户表_用户名_nullable
String mess = (String) ruleMa.get("message");
System.out.println("Validate Rules"+i+" : "+mess);
}
}*/
}catch(Exception e){
e.printStackTrace();
}
}
/**开始解析entity**/
@SuppressWarnings("unchecked")
public void parseEntity(Element entity){
if(entity != null){
/**对数据进行初始化设置**/
columnListMap = new HashMap();
columnMap = new HashMap();
entityMap = new HashMap();
ruleMap = new HashMap();
columnRulesMap = new HashMap();
columnList = new ArrayList();
setEntityMap(entity);
// String entityName = entity.attributeValue("name");
entityName = entity.attributeValue("name");
Iterator itColumn = entity.elements("column").iterator();
while(itColumn.hasNext()){
Element column = (Element) itColumn.next();
setColumnMap(entityName,column);
}
columnListMap.put(entityName, columnList);
}
}
/**将entity放入entityMap中**/
@SuppressWarnings("unchecked")
public void setEntityMap(Element entity){
Map ent = new HashMap();
String name = entity.attributeValue("name");
String code = entity.attributeValue("code");
ent.put("name", name);
ent.put("code", code);
entityMap.put(name, ent);
}
/**将column放入columnMap中**/
@SuppressWarnings("unchecked")
public void setColumnMap(String entityName,Element column){
if(column != null){
Map col = new HashMap();
String name = column.attributeValue("name");
String code = column.attributeValue("code");
String type = column.attributeValue("type");
col.put("name", name);
col.put("code", code);
col.put("type", type);
String columnMapKey = entityName+"_"+name; //eg: 用户表_用户名
columnMap.put(columnMapKey, col);
columnList.add(col);
Iterator ruleIt = column.elements("rules").iterator(); //获得rules
while(ruleIt.hasNext()){
Element rules = (Element)ruleIt.next();
Iterator rule = rules.elements("rule").iterator(); //获得 rule
while(rule.hasNext()){
Element ruleValid = (Element) rule.next(); //获得每一行rule
setRuleMap(entityName,name,ruleValid);
}
}
}
}
/**将 rule 验证规则放入ruleMap中**/
@SuppressWarnings("unchecked")
public void setRuleMap(String entityName,String columnName,Element ruleValid){
if(ruleValid != null){
String ruleName = ruleValid.attributeValue("name");
String ruleMsg = ruleValid.attributeValue("message");
Map ruleValidMap = new HashMap();
ruleValidMap.put("name", ruleName);
ruleValidMap.put("message", ruleMsg);
String ruleStrKey = entityName+"_"+columnName+"_"+ruleName;
String colStrKey = entityName+"_"+columnName;
if(this.getColumnRulesMap().containsKey(colStrKey)){
List valids = (List) this.getColumnRulesMap().get(colStrKey);
valids.add(ruleValidMap);
}else{
List valids = new ArrayList();
valids.add(ruleValidMap);
this.columnRulesMap.put(colStrKey, valids); //将每个column下的所有rules存入该map中
}
ruleMap.put(ruleStrKey, ruleValidMap); //将每个column下的一条rule存入该map中
}
}
/**所有的get set 方法**/
public Map getEntityMap() {
return entityMap;
}
public void setEntityMap(Map entityMap) {
this.entityMap = entityMap;
}
public Map getColumnMap() {
return columnMap;
}
public void setColumnMap(Map columnMap) {
this.columnMap = columnMap;
}
public Map getRuleMap() {
return ruleMap;
}
public void setRuleMap(Map ruleMap) {
this.ruleMap = ruleMap;
}
public Map getColumnRulesMap() {
return columnRulesMap;
}
public void setColumnRulesMap(Map columnRulesMap) {
this.columnRulesMap = columnRulesMap;
}
public Map getColumnListMap() {
return columnListMap;
}
public void setColumnListMap(Map columnListMap) {
this.columnListMap = columnListMap;
}
}
ParseConstans
public class ParseConstans {
/**xml中验证规则的名称name**/
public static String RULE_NAME_NULLABLE = "nullable";
public static String RULE_NAME_UNIQUE = "checkUnique";
public static String RULE_NAME_MEANWHILE = "meanwhile";
public static String RULE_USCCCHECKUNIQUE = "usccCheckUnique";
public static String RULE_MONTHCHECKUNIQUE = "monthCheckUnique";
public static String RULE_TYPECHECKUNIQUE = "typeCheckUnique";
public static String RULE_MONTHFORMAT = "monthFormat"; //时间格式
/**excel 中的模板数据错误**/
public static String ERROR_EXCEL_NULL="excel中数据为空!<br>";
public static String ERROR_EXCEL_COLUMN_NOT_EQUAL="xml列数与excel列数不相符，请检查!<br>";
public static String ERROR_EXCEL_DATA_TYPE = "数据类型错误";
/**xml对应的excel表格名**/
public static String ENTERPRISE_BASE = "企业管理";
public static String ENTERPRISE_OUTPUT_VALUE = "产值指标管理";
public static String ENTERPRISE_TAX = "税务指标管理";
public static String ENTERPRISE_INVEST = "招商指标管理";
public static String ENTERPRISE_SHAREHOLDER = "股东信息管理";
public static String ENTERPRISE_INVEST_DETAIL = "招商信息细则管理";
}
BeanToMapUtil
public class BeanToMapUtil {
/**
* 将一个 Map 对象转化为一个 JavaBean
*
* @param type 要转化的类型
* @param map 包含属性值的 map
* @return 转化出来的 JavaBean 对象
* @throws IntrospectionException 如果分析类属性失败
* @throws IllegalAccessException 如果实例化 JavaBean 失败
* @throws InstantiationException 如果实例化 JavaBean 失败
* @throws InvocationTargetException 如果调用属性的 setter 方法失败
*/
@SuppressWarnings("rawtypes")
public static Object convertMap(Class type, Map map)
throws IntrospectionException, IllegalAccessException,
InstantiationException, InvocationTargetException {
BeanInfo beanInfo = Introspector.getBeanInfo(type); // 获取类属性
Object obj = type.newInstance(); // 创建 JavaBean 对象
// 给 JavaBean 对象的属性赋值
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
for (int i = 0; i < propertyDescriptors.length; i++) {
PropertyDescriptor descriptor = propertyDescriptors[i];
String propertyName = descriptor.getName();
if (map.containsKey(propertyName)) {
// 下面一句可以 try 起来，这样当一个属性赋值失败的时候就不会影响其他属性赋值。
Object value = map.get(propertyName);
Object[] args = new Object[1];
if (propertyName.equals("establishDate")) {
String replace = ((String) value).replace("'", "");
Date date = DateUtil.stringToDate(replace);
args[0] = date;
} else if (propertyName.equals("trialTime")) {
} else if (propertyName.equals("investTime")) {
} else if (propertyName.equals("productTime")) {
} else if (propertyName.equals("datetime")) {
} /*else {
args[0] = value;
}*/
String name = descriptor.getPropertyType().getName();
if (name.equals("java.lang.String")) {
if (value != null && !"".equals(value)) {
String replace = ((String) value).replace("'", "");
args[0] = replace;
} else {
args[0] = value;
}
} else if (name.equals("java.lang.Integer") || name.equals("java.math.BigDecimal")) {
args[0] = value;
} /*else {
args[0] = value;
}*/
descriptor.getWriteMethod().invoke(obj, args);
}
}
return obj;
}
/**
* 将一个 JavaBean 对象转化为一个 Map
*
* @param bean 要转化的JavaBean 对象
* @return 转化出来的 Map 对象
* @throws IntrospectionException 如果分析类属性失败
* @throws IllegalAccessException 如果实例化 JavaBean 失败
* @throws InvocationTargetException 如果调用属性的 setter 方法失败
*/
@SuppressWarnings({"rawtypes", "unchecked"})
public static Map convertBean(Object bean)
throws IntrospectionException, IllegalAccessException, InvocationTargetException {
Class type = bean.getClass();
Map returnMap = new HashMap();
BeanInfo beanInfo = Introspector.getBeanInfo(type);
PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
for (int i = 0; i < propertyDescriptors.length; i++) {
PropertyDescriptor descriptor = propertyDescriptors[i];
String propertyName = descriptor.getName();
if (!propertyName.equals("class")) {
Method readMethod = descriptor.getReadMethod();
Object result = readMethod.invoke(bean, new Object[0]);
if (result != null) {
returnMap.put(propertyName, result);
} else {
returnMap.put(propertyName, "");
}
}
}
return returnMap;
}
public static Object mapToObject(Map<String, Object> map, Class<?> beanClass) throws Exception {
if (map == null)
return null;
Object obj = beanClass.newInstance();
Field[] fields = obj.getClass().getDeclaredFields();
for (Field field : fields) {
int mod = field.getModifiers();
if (Modifier.isStatic(mod) || Modifier.isFinal(mod)) {
continue;
}
String name = field.getName();
Object value = map.get(name);
if (value != null && !"".equals(value)) {
if (name.equals("establishDate") || name.equals("trialTime") ||
name.equals("investTime") || name.equals("productTime")) {
String replace = ((String) value).replace("'", "");
Date date = DateUtil.stringToDate(replace);
field.setAccessible(true);
field.set(obj, date);
} else if (name.equals("operateTime")) {
String replace = ((String) value).replace("'", "");
Date date = DateUtil.stringTooDate(replace);
field.setAccessible(true);
field.set(obj, date);
}
String name1 = field.getType().getName();
if (name1.equals("java.lang.Integer")) {
if (value != null && !"".equals(value)) {
Integer i = Integer.valueOf((String) value);
field.setAccessible(true);
field.set(obj, i);
}
} else if (name1.equals("java.lang.String")) {
field.setAccessible(true);
field.set(obj, value);
} else if (name1.equals("java.math.BigDecimal")) {
if (value != null && !"".equals(value)) {
Double d = Double.valueOf((String) value);
BigDecimal b = BigDecimal.valueOf(d);
field.setAccessible(true);
field.set(obj, b);
}
}
}
}
return obj;
}
}
RowData
public class RowData implements Comparable<RowData>{
private int row;//所在行
private int usccColumn; //USCC所在列
private int monthColumn; //年月所在列
private int typeColumn; //类型所在列
private String splicingValue; //Uscc+年月拼接数据
public int getRow() {
return row;
}
public void setRow(int row) {
this.row = row;
}
public int getUsccColumn() {
return usccColumn;
}
public void setUsccColumn(int usccColumn) {
this.usccColumn = usccColumn;
}
public int getMonthColumn() {
return monthColumn;
}
public void setMonthColumn(int monthColumn) {
this.monthColumn = monthColumn;
}
public int getTypeColumn() {
return typeColumn;
}
public void setTypeColumn(int typeColumn) {
this.typeColumn = typeColumn;
}
public String getSplicingValue() {
return splicingValue;
}
public void setSplicingValue(String splicingValue) {
this.splicingValue = splicingValue;
}
@Override
public int compareTo(RowData o) {
return this.getRow()-o.getRow();//按照对象的row属性进行排序
}
}
company.xml
<?xml version="1.0" encoding="UTF-8"?>
<excel>
<entity name="企业管理" code="t_enterprise_base_info">
<column name="ID" code="enterpriseId" type="String">
<rules>
<rule name="nullable" message="ID不允许为空"></rule>
</rules>
</column>
<column name="企业名称" code="enterpriseName" type="String">
<rules>
<rule name="nullable" message="企业名称不允许为空"></rule>
</rules>
</column>
<column name="企业统一社会信用代码" code="enterpriseUscc" type="String">
<rules>
<rule name="nullable" message="企业统一社会信用代码不允许为空"></rule>
<!--checkUnique对于验证行数据无效，使用usccCheckUnique规则验证-->
<!--<rule name="checkUnique" message="企业统一社会信用代码不允许重复"></rule>-->
<rule name="usccCheckUnique" message="企业统一社会信用代码不允许重复"></rule>
</rules>
</column>
<column name="股东情况" code="gdInfo" type="String">
</column>
<column name="海关编码" code="hgCode" type="String">
</column>
<column name="成立日期" code="establishDate" type="Date">
</column>
<column name="注册资本" code="registCapital" type="decimal">
</column>
<column name="公司地址" code="enterpriseAddress" type="String">
</column>
<column name="经营范围" code="enterpriseScope" type="String">
</column>
<column name="法人代表" code="legalName" type="String">
</column>
<column name="法人代表联系方式" code="contactPhone" type="String">
</column>
<column name="是否系招商引资企业" code="isInvest" type="int">
</column>
<column name="园区分类" code="parkType" type="int">
</column>
<column name="厂房位置" code="workshopPos" type="String">
</column>
<column name="厂房面积" code="workshopArea" type="decimal">
</column>
<column name="行业分类" code="industryClass" type="int">
</column>
<column name="产业类别" code="industryCategory" type="int">
</column>
<column name="试产时间" code="trialTime" type="Date">
</column>
<column name="投产时间" code="investTime" type="Date">
</column>
<column name="达产时间" code="productTime" type="Date">
</column>
<column name="企业实际经营情况" code="busiInfo" type="int">
</column>
<column name="企业类型" code="enterpriseType" type="String">
</column>
<column name="备注" code="remark" type="String">
</column>
<column name="状态" code="status" type="int">
<rules>
<rule name="nullable" message="状态不允许为空"></rule>
</rules>
</column>
<column name="操作人" code="operator" type="String">
<rules>
<rule name="nullable" message="操作人不允许为空"></rule>
</rules>
</column>
<column name="操作时间" code="operateTime" type="datetime">
<rules>
<rule name="nullable" message="操作时间不允许为空"></rule>
<!-- <rule name="checkUnique" message="员工号已经存在"></rule> -->
</rules>
</column>
<!--<entity name="企业管理" code="t_enterprise_base_info_copy">-->
<!--<column name="ID" code="ENTERPRISE_ID" type="String">-->
<!--<rules>-->
<!--<rule name="nullable" message="ID不允许为空"></rule>-->
<!--</rules>-->
<!--</column>-->
<!--<column name="企业名称" code="ENTERPRISE_NAME" type="String">-->
<!--<rules>-->
<!--<rule name="nullable" message="企业名称不允许为空"></rule>-->
<!--</rules>-->
<!--</column>-->
<!--<column name="企业统一社会信用代码" code="ENTERPRISE_USCC" type="String">-->
<!--<rules>-->
<!--<rule name="nullable" message="企业统一社会信用代码不允许为空"></rule>-->
<!--<!–checkUnique对于验证行数据无效，使用usccCheckUnique规则验证–>-->
<!--<!–<rule name="checkUnique" message="企业统一社会信用代码不允许重复"></rule>–>-->
<!--<rule name="usccCheckUnique" message="企业统一社会信用代码不允许重复"></rule>-->
<!--</rules>-->
<!--</column>-->
<!--<column name="股东情况" code="GD_INFO" type="String">-->
<!--</column>-->
<!--<column name="海关编码" code="HG_CODE" type="String">-->
<!--</column>-->
<!--<column name="成立日期" code="ESTABLISH_DATE" type="Date">-->
<!--</column>-->
<!--<column name="注册资本" code="REGIST_CAPITAL" type="decimal">-->
<!--</column>-->
<!--<column name="公司地址" code="ENTERPRISE_ADDRESS" type="String">-->
<!--</column>-->
<!--<column name="经营范围" code="ENTERPRISE_SCOPE" type="String">-->
<!--</column>-->
<!--<column name="法人代表" code="LEGAL_NAME" type="String">-->
<!--</column>-->
<!--<column name="法人代表联系方式" code="CONTACT_PHONE" type="String">-->
<!--</column>-->
<!--<column name="是否系招商引资企业" code="IS_INVEST" type="int">-->
<!--</column>-->
<!--<column name="园区分类" code="PARK_TYPE" type="int">-->
<!--</column>-->
<!--<column name="厂房位置" code="WORKSHOP_POS" type="String">-->
<!--</column>-->
<!--<column name="厂房面积" code="WORKSHOP_AREA" type="decimal">-->
<!--</column>-->
<!--<column name="行业分类" code="INDUSTRY_CLASS" type="int">-->
<!--</column>-->
<!--<column name="产业类别" code="INDUSTRY_CATEGORY" type="String">-->
<!--</column>-->
<!--<column name="试产时间" code="TRIAL_TIME" type="Date">-->
<!--</column>-->
<!--<column name="投产时间" code="INVEST_TIME" type="Date">-->
<!--</column>-->
<!--<column name="达产时间" code="PRODUCT_TIME" type="Date">-->
<!--</column>-->
<!--<column name="企业实际经营情况" code="BUSI_INFO" type="int">-->
<!--</column>-->
<!--<column name="企业类型" code="ENTERPRISE_TYPE" type="String">-->
<!--</column>-->
<!--<column name="备注" code="REMARK" type="String">-->
<!--</column>-->
<!--<column name="状态" code="STATUS" type="int">-->
<!--<rules>-->
<!--<rule name="nullable" message="状态不允许为空"></rule>-->
<!--</rules>-->
<!--</column>-->
<!--<column name="操作人" code="OPERATOR" type="String">-->
<!--<rules>-->
<!--<rule name="nullable" message="操作人不允许为空"></rule>-->
<!--</rules>-->
<!--</column>-->
<!--<column name="操作时间" code="OPERATE_TIME" type="String">-->
<!--<rules>-->
<!--<rule name="nullable" message="操作时间不允许为空"></rule>-->
<!--<!– <rule name="checkUnique" message="员工号已经存在"></rule> –>-->
<!--</rules>-->
<!--</column>-->
</entity>
</excel>
网友评论