当前位置:Gxlcms > 数据库问题 > Excel导入文件到数据库

Excel导入文件到数据库

时间:2021-07-01 10:21:17 帮助过:43人阅读

  

<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
<exclusions>
<exclusion>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
<exclusions>
<exclusion>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
</exclusion>
</exclusions>
</dependency>

  

public HashMap<String, Boolean> excelComplaintinfoUpload(MultipartFile files) throws IOException, MyException {
HashMap<Object, Object> resultMap = new HashMap<>();
InputStream inputStream = files.getInputStream();
String fileName = files.getOriginalFilename();
Workbook book=null;
if(fileName.endsWith("xlsx")){
book = new XSSFWorkbook(inputStream);
}else if(fileName.endsWith("XLSX")){
book = new HSSFWorkbook(inputStream);
}else{
resultMap.put("success", false);
resultMap.put("msg", "文件格式有误!");
}
if (book!=null){
//第一个工作簿
Sheet sheet = book.getSheetAt(0);
List<ComplaintInfo> complaintInfos= convert(sheet);
int i=0;
if (i>0){
return ReturnResult.json(i);
}

}
return null;
}

private List<ComplaintInfo> convert(Sheet sheet){
List<ComplaintInfo> complaintInfoList = new ArrayList<>();
for (int i = 1; i <= sheet.getLastRowNum() ; i++) {
Row row = sheet.getRow(i);

ComplaintInfo complaintInfo = new ComplaintInfo();

Iterator<Cell> iterator = row.cellIterator();

while (iterator.hasNext()){
Cell cell = iterator.next();
int columnIndex = cell.getColumnIndex();

if (cell.getColumnIndex() == 0){
cell.setCellType(CellType.STRING);
String stringCellValue = cell.getStringCellValue();
//投诉人
complaintInfo.setComplaintName(stringCellValue);
}
if(cell.getColumnIndex() == 1){
//第二列,类型设置为string,然后赋值给name
cell.setCellType(CellType.STRING);
String stringCellValue = cell.getStringCellValue();

complaintInfo.setComplaintCardid(stringCellValue);
}
if(cell.getColumnIndex() == 2){
//第二列,类型设置为string,
cell.setCellType(CellType.STRING);

complaintInfo.setTyshCode(cell.getStringCellValue());
}

if(cell.getColumnIndex() == 3){
cell.setCellType(CellType.STRING);
//投诉人电话
complaintInfo.setComplaintPhone(cell.getStringCellValue());

}

if(cell.getColumnIndex() == 4){
cell.setCellType(CellType.STRING);
//投诉标题
complaintInfo.setComplainTitle(cell.getStringCellValue());

}

if(cell.getColumnIndex() == 5){
cell.setCellType(CellType.STRING);
//投诉描述

complaintInfo.setComplaintDesc(cell.getStringCellValue());
}
if(cell.getColumnIndex() == 6){
cell.setCellType(CellType.STRING);
//来源渠道
complaintInfo.setChannel(cell.getStringCellValue());

}


this.complaintInfoMapper.insertSelective(complaintInfo);
}
complaintInfoList.add(complaintInfo);
}
return complaintInfoList;
}

Excel导入文件到数据库

标签:第一个   asn   while   book   tle   type   tty   object   name   

人气教程排行