java导入excel文件导入数据(后端springboot+前端angular)
前端（angular）
html层
TS层
value: number = 0;
showImport:boolean=false;
isDisplay:boolean = false;
importUrl:string = this.config.getContextPath() + '/importData/importExcel';
点击导入按钮触发点击事件时会将 this.showImport = true; 来显示html页面的导入页面
// 上传文件回调
onUpload(event) {var r = event.originalEvent.body;if ('0' === r.code) {this.value = 100;this.showImport = false;this.lastQryCriteria = new ImportDataQueryCriteria();this.lastQryCriteria.count = true;this.paginator.changePage(0);this.messageService.add({severity: 'success', summary: '提示', detail: '导入成功'});this.onQuery();//导入之后刷新页面的方法}else {this.value = 0;this.messageService.add({severity: 'info', summary: '提示', detail: r.message});}this.isDisplay = false;
}
// 上传后
onBeforeUpload(event) {this.isDisplay = true;
}
后端（springboot）
注意配置文件要加上接收文件大小
servlet:
multipart:
enabled: true #是否启用http上传处理
max-request-size: 100MB #最大请求文件的大小
max-file-size: 20MB #设置单个文件最大长度
file-size-threshold: 20MB #当文件达到多少时进行磁盘写入
Controller层
/**
* 导入数据
* @param request
* @return
*/
@RequestMapping(value = "/importExcel")
public RestResult
service层
/**
* 导入数据
*/
void importExcel(HttpServletRequest request)throws Exception;
ServiceImpl层
举个例子：
public static final String CREDIT_CODE = "信用代码"; 来进行要导入excel表格表头的对比
/**
* 导入数据
*/
@Override
@Transactional
public void importExcel(HttpServletRequest request) throws Exception {
//获取当前登录人主键和当前时间
long userId = InvocationInfoProxy.getUserId();
Date nowDate = new Date();
//获取文件集合
List
//获取文件信息
MultipartFile file = list_f.get(0).getFileMultipart();
//检查文件类型
ExcelUtil.checkFile(file);
// 获得Workbook工作薄对象
Workbook workbook = ExcelUtil.getWorkBook(file);
//遍历sheet工作表
for (int sheetNum = 0; sheetNum < 1 ; sheetNum++) {
// 获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if (Validator.isNull(sheet)) {
continue;
}
// 获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
// 获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
//获得当前行的开始列
int firstCellNum = sheet.getRow(firstRowNum).getFirstCellNum();
//获得当前行的列数
int lastCellNum = sheet.getRow(firstRowNum).getLastCellNum();
//标题行
Row titleRow=sheet.getRow(firstRowNum);
//错误提示信息
StringBuffer error = ExcelUtil.getErrorStringBuffer(sheet);
//导入信息保存
List
ImportDataEntity importDataEntity = null;
for (int rowNum = firstRowNum+1; rowNum <= lastRowNum; rowNum++) {
// 获得当前行
Row row = sheet.getRow(rowNum);
if (Validator.isNull(row)) {
continue;
}
//导入信息
importDataEntity = new ImportDataEntity();
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
String cellValue = ExcelUtil.getCellValue(row,cellNum);
String titleCellValue = ExcelUtil.getCellValue(titleRow,cellNum);
if(Validator.equals(CREDIT_CODE, titleCellValue)) {
if(Validator.isNull(cellValue)) {
throw new BusinessException(error+"第"+(rowNum+1)+"xxx为空");
} else if(cellValue.length() > 32) {
throw new BusinessException(error+"第"+(rowNum+1)+"xxx长度不能超过32");
}
//查询是否重复（唯一字段）
List
if(Validator.isNotNull(list_c) && list_c.size()>0) {
throw new BusinessException(error+"第"+(rowNum+1)+"行,xxx:"+cellValue+",已存在");
}
importDataEntity.setCreditCode(cellValue);
continue;
}
if(Validator.equals(ENTERPRISE_NAME, titleCellValue)) {
if(Validator.isNull(cellValue)) {
throw new BusinessException(error+"第"+(rowNum+1)+"xxx不能为空");
} else if(cellValue.length() > 100) {
throw new BusinessException(error+"第"+(rowNum+1)+"xxx不能超过100");
}
importDataEntity.setEnterpriseName(cellValue);
continue;
}
if(Validator.equals(BUSINESS_SCOPE, titleCellValue)) {
importDataEntity.setBusinessScope(cellValue);
continue;
}
if(Validator.equals(LEGAL_PERSON, titleCellValue)) {
importDataEntity.setLegalPerson(cellValue);
continue;
}
if(Validator.equals(ENTERPRISE_ADDRESS, titleCellValue)) {
importDataEntity.setEnterpriseAddress(cellValue);
continue;
}
if(Validator.equals(LEGAL_PERSON_CARD, titleCellValue)) {
importDataEntity.setLegalPersonCard(cellValue);
continue;
}
if(Validator.equals(LEGAL_PERSON_CARD_ID, titleCellValue)) {
importDataEntity.setLegalPersonCardId(cellValue);
continue;
}
if(Validator.equals(ENTERPRISE_TYPE, titleCellValue)) {
importDataEntity.setEnterpriseType(cellValue);
continue;
}
if(Validator.equals(ENTERPRISE_PHONE, titleCellValue)) {
importDataEntity.setEnterprisePhone(cellValue);
continue;
}
if(Validator.equals(REGISTERED_CAPITAL, titleCellValue)) {
importDataEntity.setRegisteredCapital(cellValue);
continue;
}
if(Validator.equals(START_DATE, titleCellValue)) {
DateFormat fmt =new SimpleDateFormat("yyyy-MM-dd");
Pattern pattern = Pattern.compile("[0-9]*");
Matcher isNum = pattern.matcher(cellValue);
if( !isNum.matches() ){
Date date = fmt.parse(cellValue);
importDataEntity.setStartDate(date);
} else {
Date date = fmt.parse(DateUtils.formatDate(HSSFDateUtil.getJavaDate(Double.parseDouble(cellValue)), DateUtils.DATE_FMT_YYYY_MM_DD));
importDataEntity.setStartDate(date);
}
continue;
}
if(Validator.equals(END_DATE, titleCellValue)) {
if(cellValue.equals("长期")) {
DateFormat fmt =new SimpleDateFormat("yyyy-MM-dd");
Date date1=fmt.parse("9999-01-01");
importDataEntity.setEndDate(date1);
}else {
DateFormat fmt =new SimpleDateFormat("yyyy-MM-dd");
Pattern pattern = Pattern.compile("[0-9]*");
Matcher isNum = pattern.matcher(cellValue);
if( !isNum.matches() ){
Date date = fmt.parse(cellValue);
importDataEntity.setEndDate(date);
} else {
Date date = fmt.parse(DateUtils.formatDate(HSSFDateUtil.getJavaDate(Double.parseDouble(cellValue)), DateUtils.DATE_FMT_YYYY_MM_DD));
importDataEntity.setEndDate(date);
}
}
continue;
}
if(Validator.equals(BUSINESS_TYPE, titleCellValue)) {
importDataEntity.setBusinessType(cellValue);
continue;
}
if(Validator.equals(BUSINESS_CODE, titleCellValue)) {
importDataEntity.setBusinessCode(cellValue);
continue;
}
}
importDataEntity.setMerchantDataId(IDGenerator.get32UUID());
importDataEntity.setCreateDate(nowDate);
importDataEntity.setModifyDate(nowDate);
importDataEntity.setCreator(userId);
importDataEntity.setModifier(userId);
list_ide.add(importDataEntity);
//保存导入信息
if(rowNum%100 == 0 || rowNum == lastRowNum) {
importDataService.saveImportDataList(list_ide);
list_ide = new ArrayList
}
}
}
}
/**
* 添加导入的数据
*/
@Override
public int saveImportDataList(List
return importDataMapper.insertAllImportData(list_id);
}
mapper层
int insertAllImportData(@Param("list_id")List
sql语句例子：
oracle:
insert all
into 表名
MERCHANT_DATA_ID,
CREDIT_CODE,
#{item.merchantDataId,jdbcType=VARCHAR},
#{item.creditCode,jdbcType=VARCHAR},
select 1 from dual
MySQL
insert into 表名
(
MERCHANT_DATA_ID,
CREDIT_CODE
)
values
(
#{item.merchantDataId,jdbcType=VARCHAR},
#{item.creditCode,jdbcType=VARCHAR}
)
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！