Springboot imported Excel data

Only applicable to the import of data without verification

Dependency jar package

org.apache.poi< br> poi
4.1.0


org.apache.poi
poi-ooxml
4.1.0

< br>
org.projectlombok
lombok
true

io.springfox
springfox-swagger-ui< br> 2.7.0


io.springfox
springfox-swagger2
2.7.0

Tools

import io.swagger.annotations.ApiModelProperty;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermod el.Cell;
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.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap ;
import java.util.List;
import java.util.Map;
@Component
public class ExcelUtil {
public static final String XLS = “.xls”;
public static final String XLSX = “.xlsx”;

public List readExcelFileToDTO(MultipartFile file, Class clazz) throws IOException {
return readExcelFileToDTO(file, clazz, 0 );
}

public List readExcelFileToDTO(MultipartFile file, Class clazz, Integer sheetId) throws IOException {
// Convert file to workbook type
W orkbook workbook = buildWorkbook(file);
//First table
return readSheetToDTO(workbook.getSheetAt(sheetId), clazz);
}

public List readSheetToDTO(Sheet sheet, Class clazz) throws IOException {
List result = new ArrayList<>();
List> sheetValue = changeSheetToMapList(sheet);
for (Map valueMap: sheetValue) {
T dto = buildDTOByClass(clazz, valueMap);
if (dto != null) {
to result.
}
}
}
Return result;
}

//Type conversion
Private Workbook buildWorkbook(MultipartFile file) throws IOException {
String filename get file name. Get IOException {
();
if (filename.endsWith(XLS)) {
return new HSSFWorkbook(file.getInputStream());
} else if (filename.endsWith(XLSX)) {
return XSSFWorkbook(file.getInputStream());
} else {
throw new IOException(“unknown file format: “+ filename);
}
}

private List> changeSheetToMapList(Sheet sheet) {
List> result = new ArrayList<>();
int rowNumber = sheet.getPhysicalNumberOfRows();
String[] titles = getSheetRowValues(sheet.getRow(0)); / / The first row as the header
for (int i = 1; i String[] values ​​= getSheetRowValues(sheet.getRow(i));
Map valueMap = new HashMap<>();
for (int j = 0; j valueMap.put(titles[j], values[j]);
}
Result.add(valueMap);
}
Return result;
}

Private T buildDTOByClass(Class clazz, Map valueMap) {
try {
T dto = clazz.newInstance();
for (Field field: clazz.getDeclaredFields()) {
ApiModelProperty desc = field.getAnnotation(ApiModelProperty.class);
String value = valueMap.get(desc.value());
(get() field) if (value != null) {
.getName().equalsIgnoreCase(“java.lang.Integer”)) {
Method method = clazz.getMethod(getSetMethodName(field.getName()), field.getType() ger Inte age Integer;
.parseInt(value.substring(0,value.indexOf(“.”)));
getName method.invoke(dto, age);
getMethod(Method azzMethod (Method azz)else{
field.getName()), field.getType());
br>
dto, value);
dto, value
dto, return
dtobr> } catch (Exception e) {
e.getStackTrace();
}
return null;
}

private String getSetMethodName(String name) {
String firstChar = name.substring(0, 1);
return “set” + firstChar.toUpperCase() + name.substring(1);
}

private String[] getSheetRowValues(Row row) {
if (row == null) {
return new String[]{};
} else {
int cellNumber = row.getLastCellNum();
List();
for (int i = 0; i cellValueList.add(getValueOnCell(row.getCell(i)));
> Return cellValueList.toArray(new String[0]);
}
}

private String getValueOnCell(Cell cell) {
return if (cell == null) {
return “”;
}
switch (cell.getCellTypeEnum()) {
case STRING: return cell.getStringCellValue();
// case NUMERIC: return String.” format(“%.2. , cell.getNumericCellValue());
case NUMERIC: return String.valueOf(cell.getNumeric CellValue());
case BOOLEAN: return cell.getBooleanCellValue()? “True”: “false”;
case FORMULA:
. get cell value(
) try Cell return {
} catch (Exception e) {
// Return String.format(“%.2f”, cell.getNumericCellValue());
Return String.valueOf(cell.getNumericCellValue());
br> default: return “”;
}
}

public static void main(String[] args) {
int i = Integer.parseInt(“12”);
System.out.println(i);
Integer integer = Integer.valueOf(“12”);
System.out.println(integer);
}
}

User Information

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@Data
public class UserDto {
@ApiModelProperty(value = “Number”)
private String id;

@ApiModelProperty(value = “User Name”)
private String name;
< br> @ApiModelProperty(value = “User age”)
private Integer age;
}

Access layer

import com.taikang.test. importExcel.bo.UserDto;
import com.taikang.test.importExcel.util.ExcelUtil;
import io.swagger.annotations.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;
import org. springframework.web.multipart.MultipartFile;

import java.util.List;
@RestController
@Api(tags = “Import Excel file”)
@Slf4j
public class InportExcelController {
@Autowired
private ExcelUtil excelUtil;
@PostMapping(value = “/uploadExcel”, consumes = “multipart/*”, headers = “content-type=multipart/form-data”)< br> @ApiOperation(value = “User information Excel import data”, notes = “User information Excel import data”, httpMethod = “POST”)
@ApiResponses(value = {
@ApiResponse(code = 200, message = “Uploaded successfully! “),
@ApiResponse(code = 500, message = “Upload failed!”)
})
public String uploadExcel(@ApiParam(value = “User information Excel import data”, required = true) MultipartFile file) throws Exception {
List dtoList = excelUtil.readExcelFileToDTO(file, UserDto.class);
log.info(“length:”+dtoList.size());
dtoList = excelUtil.readExcelFileToDTO(file, UserDto.class); TODO code added to the library by itself
return “Import successful”;
}
}
————————————————

Reprinted: Original link: https://blog.csdn.net/p393975269/article/details/100130728