导入Excel表格批量增加
直接上代码
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
package com.ruoyi.juancloud.videocloudbroadcasting.util;
import com.github.pagehelper.util.StringUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;
import static org.apache.poi.ss.usermodel.CellType.STRING;
public class ReadExcelByPoi<T> {
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
public List<T> readExcelByPoi(T t, MultipartFile filePath) {
List<T> infoList = new ArrayList<>();
Class c = t.getClass();
Field[] fs = c.getDeclaredFields();
Map<String, List<String>> map = new HashMap<String, List<String>>();
try {
int index = Objects.requireNonNull(filePath.getOriginalFilename()).lastIndexOf(".");
String postfix = filePath.getOriginalFilename().substring(index + 1);
Workbook workbook = null;
if (XLS.equals(postfix)) {
workbook = new HSSFWorkbook(filePath.getInputStream());
} else if (XLSX.equals(postfix)) {
workbook = new XSSFWorkbook(filePath.getInputStream());
}
assert workbook != null;
Sheet sheet = workbook.getSheetAt(0);
int rows = sheet.getLastRowNum();
int columns = sheet.getRow(0).getLastCellNum();
for (int i = 1; i <= rows; i++) {
Row row = sheet.getRow(i);
if (null != row && row.getFirstCellNum() == -1) {
continue;
}
List<String> contentList = new ArrayList<String>();
for (int j = 0; j < columns; j++) {
assert row != null;
if (row.getCell(j) != null) {
row.getCell(j).setCellType(STRING);
contentList.add(row.getCell(j).getStringCellValue());
} else {
contentList.add("");
}
}
map.put("entityInfo" + i, contentList);
}
for (Map.Entry<String, List<String>> entry : map.entrySet()) {
t = (T) c.newInstance();
List<String> list = entry.getValue();
for (int i = 1; i < fs.length; i++) {
Field f = t.getClass().getDeclaredField(fs[i].getName());
f.setAccessible(true);
if (f.getType().getName().equals(String.class.getName())) {
if (StringUtil.isEmpty(list.get(i - 1))) {
f.set(t, null);
} else {
f.set(t, list.get(i - 1));
}
} else if (f.getType().getName().equals(Integer.class.getName())) {
if (StringUtil.isEmpty(list.get(i - 1))) {
f.set(t, null);
} else {
f.set(t, Integer.parseInt(list.get(i - 1)));
}
} else if (f.getType().getName().equals(Long.class.getName())) {
if (StringUtil.isEmpty(list.get(i - 1))) {
f.set(t, null);
} else {
f.set(t, Long.valueOf(list.get(i - 1)));
}
} else if (f.getType().getName().equals(BigDecimal.class.getName())) {
BigDecimal bigDecimal = new BigDecimal(list.get(i - 1));
if (StringUtil.isEmpty(list.get(i - 1))) {
f.set(t, bigDecimal);
} else {
f.set(t, bigDecimal);
}
}
}
infoList.add(t);
}
} catch (Exception e) {
e.printStackTrace();
}
return infoList;
}
}
控制层
@PostMapping("/importExcel")
public AjaxResult getPathToExcel(@RequestParam("uploadFile") MultipartFile file,
@RequestPart @Validated String groupId) {
ReadExcelByPoi<Device> readExcelByPoi = new ReadExcelByPoi<>();
List<Device> startContents = readExcelByPoi.readExcelByPoi(new Device(), file);
ArrayList<Device> list = new ArrayList<>();
for (Device device : startContents) {
Device dev = new Device();
dev.setDeviceCode(device.getDeviceCode());
List<Device> devices = deviceService.selectDeviceList(dev);
if (devices.size() > 0) {
return AjaxResult.error("设备id重复!重复id为:" + device.getDeviceCode());
}
list.add(device);
}
if (startContents.size() > 0) {
try {
tblFixChangeServiceImpl.getGroupDevice(list, Long.valueOf(groupId));
} catch (NumberFormatException e) {
e.printStackTrace();
}
}
return AjaxResult.success("添加成功!");
}