How to read by row and by column in excel

Core principles of easyExcel

When writing xlsx files with a large amount of data, POI provides us with the SXSSFWorkBook class to process. The processing mechanism of this class is to flush this part of data when the number of data in memory reaches a limit, and then process the remaining data in turn, which can meet the needs in most scenarios. When reading a file with a large amount of data, WorkBook processing is not enough, because POI first reads the cell s in the file into memory and generates a tree structure (for each sheet in Excel, TreeMap is used to store the rows in the sheet). If the amount of data is large, it will also generate java.lang.OutOfMemoryError: Java heap space error. POI officially recommends "XSSF and SAX (event API)" to solve this problem. There are three keys to solve OOM after analyzing POI

• Read data conversion process
• The design idea and related roles of easyexcel to analyze data.

According to the information given on the official website above, we need to have a model to receive the data of each line. In this example, we use the CommonUser object, on which data verification can also be added. We also need a listener CommonUserListener to parse each line, which can process the data of each line, and then read and write the database operation.

A small demo (mybatis plus framework used)

controler

@RestController @RequestMapping("info/commonuser") public class CommonUserController { @Autowired private CommonUserService commonUserService; /** * excel Import (read by line) * <p> * 1. Create entity object reference {@ link CommonUser} corresponding to excel * <p> * 2. Because excel is read line by line by default, you need to create a callback listener for Excel line by line, refer to {@ link CommonUserListener} * <p> * 3. Read directly */ @PostMapping("upload") @ResponseBody public String upload(MultipartFile file) throws IOException { EasyExcel.read(file.getInputStream(), CommonUser.class, new CommonUserListener(commonUserService)) .sheet() .doRead(); return "success"; } /** * File download (if it fails, an Excel with partial data will be returned) * <p> * 1. Create entity object reference {@ link CommonUser} corresponding to excel * <p> * 2. Set parameters returned * <p> * 3. Write directly. Note here that when you finish, the OutputStream will be automatically closed. Of course, it's not a big problem for you to close the stream outside */ @GetMapping("download") public void download(HttpServletResponse response) throws IOException { // Please use the browser or postman directly response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // here URLEncoder.encode It can prevent Chinese miscoding. Of course, it has nothing to do with easyexc String fileName = URLEncoder.encode("User table", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); EasyExcel.write(response.getOutputStream(), CommonUser.class).sheet("Template").doWrite(data()); } /** * excel export * json is returned when the file is downloaded and fails (an Excel with partial data will be returned if it fails by default) * * @since 2.1.1 */ @GetMapping("downloadFailedUsingJson") public void downloadFailedUsingJson(HttpServletResponse response) throws IOException { // Please use the browser or postman directly try { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding("utf-8"); // here URLEncoder.encode It can prevent Chinese miscoding, of course, it has nothing to do with easyexc String fileName = URLEncoder.encode("test", "UTF-8"); response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx"); // You need to set not to close the flow here EasyExcel.write(response.getOutputStream(), CommonUser.class).autoCloseStream(Boolean.FALSE).sheet("Template") .doWrite(data()); } catch (Exception e) { // Reset response response.reset(); response.setContentType("application/json"); response.setCharacterEncoding("utf-8"); Map<String, String> map = new HashMap<String, String>(); map.put("status", "failure"); map.put("message", "Download file failed" + e.getMessage()); response.getWriter().println(new Gson().toJson(map)); } } private List<CommonUser> data() { List<CommonUser> list = commonUserService.list(); return list; } }

CommonUserService (not related to reading excel, business needs)

/** * User Service * * @author hfl [email protected] * @date 2020-05-16 08:42:50 */ public interface CommonUserService extends IService<CommonUser> { }

CommonUserServiceImpl

@Service("commonUserService") public class CommonUserServiceImpl extends ServiceImpl<CommonUserMapper, CommonUser> implements CommonUserService { private final static Logger logger = LoggerFactory.getLogger(CommonUserServiceImpl.class); }

CommonUserListener (responsible for getting the data of each row, and then db saving as required)

public class CommonUserListener extends AnalysisEventListener<CommonUser> { private static final Logger LOGGER = LoggerFactory.getLogger(TestController.class); /** * Every five databases can be stored. In practice, 3000 databases can be used, and then the list can be cleaned to facilitate memory recycling * mybatis-plus Default 1000 */ private static final int BATCH_COUNT = 1000; List<CommonUser> list = new ArrayList<>(); /** * Suppose this is a DAO. Of course, if there is business logic, this can also be a service. Of course, it's no use not storing this object. */ private CommonUserService commonUserService; public CommonUserListener(CommonUserService commonUserService) { // This is the demo, so any new one. Actual use if spring is reached, please use the following parametered constructor this.commonUserService = commonUserService; } /** * Every five databases can be stored. In practice, 3000 databases can be used, and then the list can be cleaned to facilitate memory recycling */ @Override public void invoke(CommonUser data, AnalysisContext context) { LOGGER.info("Parse to a piece of data:{}", new Gson().toJson(data)); list.add(data); // Reach BATCH_COUNT, need to store the database once to prevent tens of thousands of data in memory, easy OOM if (list.size() >= BATCH_COUNT) { saveData(); // Storage complete cleanup list list.clear(); } } @Override public void doAfterAllAnalysed(AnalysisContext context) { // Data should also be saved here to ensure that the last legacy data is also stored in the database saveData(); LOGGER.info("All data analysis completed!"); } /** * Plus storage database */ private void saveData() { LOGGER.info("{}Data, start to store database!", list.size()); commonUserService.saveBatch(list); LOGGER.info("Storage database succeeded!"); } }

CommonUserMapper (for database operations)

/** * User table * * @author hfl * @date 2020-05-16 08:42:50 */ @Mapper public interface CommonUserMapper extends BaseMapper<CommonUser> { }

Solid object

@Data @TableName("common_user") public class CommonUser extends BaseEntity { /** * User ID */ @TableId private String userId; /** * user name */ @ExcelProperty("String title") private String userName; /** * Real name */ private String userRealname; /** * password */ private String userPassword; /** * salt */ private String userSalt; /** * phone number */ private String userMobile; /** * Gender */ private String userSex; /** * Avatar url */ private String userAvatar; /** * mail box */ private String userEmail; /** * Account status (0-normal, 1-frozen) */ private Integer userStatus; /** * Extended fields */ private String userEx; @Override public String toString() { return "CommonUser{" + "userId='" + userId + '\'' + ", userName='" + userName + '\'' + ", userRealname='" + userRealname + '\'' + ", userPassword='" + userPassword + '\'' + ", userSalt='" + userSalt + '\'' + ", userMobile='" + userMobile + '\'' + ", userSex='" + userSex + '\'' + ", userAvatar='" + userAvatar + '\'' + ", userEmail='" + userEmail + '\'' + ", userStatus=" + userStatus + ", userEx='" + userEx + '\'' + '}'; } }

Database table structure:

CREATE TABLE `common_user` ( `user_id` varchar(32) NOT NULL COMMENT 'user ID', `user_name` varchar(50) DEFAULT NULL COMMENT 'user name', `user_realname` varchar(50) DEFAULT NULL COMMENT 'Real name', `user_password` varchar(50) DEFAULT NULL COMMENT 'password', `user_salt` varchar(50) DEFAULT NULL COMMENT 'salt', `user_mobile` varchar(20) DEFAULT NULL COMMENT 'phone number', `user_sex` varchar(20) DEFAULT NULL COMMENT 'Gender', `user_avatar` varchar(255) DEFAULT NULL COMMENT 'head portrait url', `user_email` varchar(50) DEFAULT NULL COMMENT 'mail box', `user_status` tinyint(1) DEFAULT NULL COMMENT 'Account status(0-normal,1-frozen)', `user_ex` text COMMENT 'Extended fields', `creater` varchar(32) DEFAULT NULL COMMENT 'creator', `modifier` varchar(32) DEFAULT NULL COMMENT 'Modified by', `create_time` datetime DEFAULT NULL COMMENT 'Creation time', `update_time` datetime DEFAULT NULL COMMENT 'Update time', `is_deleted` tinyint(1) DEFAULT NULL COMMENT '0: Not deleted, 1: deleted', PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User table';

Start project: Use postman to export the local database data to excel: Enter in browser: localhost:7000/info/commonuser/downloadFailedUsingJson

You can see that the database is exported: the title header corresponds to @ ExcelProperty in the entity: only the string Title header is written here, and other fields will be displayed in English by default.

Read by line

Delete all the data in the database just now: try importing the Excel data just now

Use post test: You can see that the control has shown success, Look at the database: it shows that it has been read in line and written to the database

The above imported excel data is read by line, but my requirements are as follows: Column form, many sheet s are different, how to deal with it? So I think that easyExcel can't be realized. I just use the poi's own cell to read by myself.

Idea: each template excel corresponds to an entity, and the location (row number and column number) of each cell is corresponding to the entity by annotation. In this way, I parse the cell, take out the value and location of each cell, and assign them to the attributes of the corresponding entity.

Small demo of parsing cell

Because easyExcel references poi by default, you don't need to refer to maven package, just write it directly:

@Test public void AllExcelRead() throws IOException, InvalidFormatException { //1. Specify the name of the EXCEL document to read String filename="C:\\Users\\69032\\Desktop\\vechicleService.xlsx"; //2. Create input stream FileInputStream input=new FileInputStream(filename); //3. Create a workbook object from the workbook factory class Workbook workbook= WorkbookFactory.create(input); //4. Get sheet (by sheet name or sheet index) String sheetName = "Vehicle information filing application"; Sheet sheet=workbook.getSheet(sheetName); // Sheet sheet=workbook.getSheet("worksheet 1"); // Sheet sheet = workbook.getSheetAt(0); //5. Get row Row row=sheet.getRow(53); //6. Get cell Cell cell=row.getCell(2); //7. Read cell contents System.out.println(cell.getStringCellValue()); }

The operation results are as follows: Find the data of excel cell perfectly:

Actual combat:

Define comments on attributes, and specify attributes of a cell:

RecordTemplate

@Target() @Retention(RetentionPolicy.RUNTIME) @Documented public @interface RecordTemplate { //Line No int rowNo(); //Column number int columnNo(); //Required or not FillCommentEnum comment(); //Cell name String name(); //data type }

Required enumeration: FillCommentEnum

/** * Title: FillCommentEnum * Description: Cell fill enumeration * * @author hfl * @version V1.0 * @date 2020-05-29 */ public enum FillCommentEnum { FILL(0, "Required"), EMPTY(1, "Not required"); private int code; private String description; FillCommentEnum(int code, String description) { this.code = code; this.description = description; } public int getCode() { return code; } public void setCode(int code) { this.code = code; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } }

Next, I need to define entity and template correspondence.

@Data @TableName("vehicle_static_info") public class VehicleStaticInfo extends BaseEntity { /** * Vehicle ID (primary key) */ @TableId private String vehicleId; /** * Maximum reference torque */ private String engineMaxTorque; /** * License plate number ((GB17691-2005 required, GB17691-2018 optional)) * This field has been removed from the database and extracted into the vehicle public information table */ @RecordTemplate(rowNo = 3, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "license plate") @TableField(exist = false) private String vehicleLicense; /** * License plate color (license plate color) */ @RecordTemplate(rowNo = 4, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "License plate color") private String licenseColor; /** * Body structure (see instruction 3 for coding) */ @RecordTemplate(rowNo = 5, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Car body structure") private Integer vehicleStructure; /** * Vehicle color */ @RecordTemplate(rowNo = 6, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Vehicle color") private String vehicleColor; /** * Approved load */ @RecordTemplate(rowNo = 7, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Approved load") private Double vehicleLoad; /** * Vehicle dimension mm (length) (32960, 17691, etc.) */ @RecordTemplate(rowNo = 8, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Vehicle size mm(Long)") private Double vehicleLong; /** * Vehicle dimension mm (width) (Dictionary of new energy, etc. in the Sixth National Congress of the people's Republic of China) */ @RecordTemplate(rowNo = 9, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Vehicle size mm(Width)") private Double vehicleWide; /** * Vehicle dimension mm (height) */ @RecordTemplate(rowNo = 10, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Vehicle size mm(High)") private Double vehicleHigh; /** * Total mass */ @RecordTemplate(rowNo = 11, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Total mass") private Double grossVehicleMass; /** * Vehicle type ((code see Note 1)) */ @RecordTemplate(rowNo = 12, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Vehicle type (code see right side 1)") private Integer vehicleType; /** * Industry type ((code see note 8 http://www.stats.gov.cn/tjsj/tjbz/hyflbz/201905/P020190716349644060705.pdf )) */ @RecordTemplate(rowNo = 13, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Industry type (code see right 8)") private String industryType; /** * Vehicle model ((GB17691-2005 required, GB17691-2018 optional)) */ @RecordTemplate(rowNo = 14, columnNo = 2, comment = FillCommentEnum.FILL, name = "Vehicle model( GB17691-2005 Required, GB17691-2018 Optional)") @NotEmpty(message = "Vehicle model cannot be empty", groups = ) private String vehicleModel; /** * Purchase time */ @RecordTemplate(rowNo = 15, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Purchase time") private String buyingDate; /** * Frame number VIN((required)) */ @RecordTemplate(rowNo = 16, columnNo = 2, comment = FillCommentEnum.FILL, name = "Frame No VIN(Required)") @NotEmpty(message = "Frame No VIN Cannot be empty", groups = ) @TableField(exist = false) private String vehicleFrameNo; /** * Driving license No */ @RecordTemplate(rowNo = 17, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Driving license No") private String drivingLicenseNo; /** * Engine model ((GB17691-2005 required, GB17691-2018 optional)) */ @RecordTemplate(rowNo = 18, columnNo = 2, comment = FillCommentEnum.FILL, name = "Engine model( GB17691-2005 Required, GB17691-2018 Optional)") @NotEmpty(message = "Engine model cannot be empty", groups = ) private String engineModel; /** * Engine number */ @RecordTemplate(rowNo = 19, columnNo = 2, comment = FillCommentEnum.FILL, name = "Engine number") @NotEmpty(message = "Engine number cannot be empty", groups = ) private String engineNo; /** * Vehicle registration */ @RecordTemplate(rowNo = 20, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Vehicle registration") private String vehiclePlace; /** * Technical level of vehicle (see Note 2 for coding) */ @RecordTemplate(rowNo = 21, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Technical level of vehicle (see right side 2 for coding)") private Integer technicalLevel; /** * date of production */ @RecordTemplate(rowNo = 22, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "date of production") private String productionDate; /** * Rating date */ @RecordTemplate(rowNo = 23, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Rating date") private String gradeAssessmentDate; /** * Secondary maintenance date */ @RecordTemplate(rowNo = 24, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Secondary maintenance date") private String twoMaintenanceDate; /** * Secondary maintenance status (see instruction 5 for coding) */ @RecordTemplate(rowNo = 25, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Secondary maintenance status (see right 5 for coding)") private Integer twoMaintenanceStatus; /** * Annual review status (see note 4 for code) */ @RecordTemplate(rowNo = 26, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Annual review status (see 4 on the right for the code)") private Integer yearEvaluationStatus; /** * Validity of annual inspection */ @RecordTemplate(rowNo = 27, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Validity of annual inspection") private String yearinspectionPeriod; /** * Insurance period */ @RecordTemplate(rowNo = 28, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Insurance period") private String insurancePeriod; /** * Maintenance validity */ @RecordTemplate(rowNo = 29, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Maintenance validity") private String maintenancePeriod; /** * Name of affiliated unit */ @RecordTemplate(rowNo = 30, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Name of affiliated unit") private String enterpriseName; /** * Vehicle contact */ @RecordTemplate(rowNo = 31, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Vehicle contact") private String contactsName; /** * Vehicle contact number */ @RecordTemplate(rowNo = 32, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Vehicle contact number") private String contactPhone; /** * Vehicle sim card number */ @RecordTemplate(rowNo = 33, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "vehicle sim Card number") private String terminalSim; /** * Vehicle registration time */ @RecordTemplate(rowNo = 34, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Vehicle registration time") private String registerDate; /** * Organization ID */ @RecordTemplate(rowNo = 35, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Organization ID") private String organizationId; /** * Vehicle type of Environmental Protection Bureau (code see instruction 6) */ @RecordTemplate(rowNo = 36, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "EPA vehicle type (code see right 6)") private Integer epaVehicleType; /** * Transport Bureau vehicle type ((code see note 7)) */ @RecordTemplate(rowNo = 37, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Vehicle type of Transport Bureau (see 7 on the right for the code)") private Integer transVehicleType; /** * All sim cards bound */ @RecordTemplate(rowNo = 38, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "All bound sim card") private String terminalAllSim; /** * Owner address */ @RecordTemplate(rowNo = 39, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Owner address") private String ownerAddress; /** * License plate model */ @RecordTemplate(rowNo = 40, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "License plate model") private String licenseModel; /** * administrative division */ @RecordTemplate(rowNo = 41, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "administrative division") private String administrativeArea; /** * Administrative address */ @RecordTemplate(rowNo = 42, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Administrative address") private String administrativeAddress; /** * Total passengers */ @RecordTemplate(rowNo = 43, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Total passengers") private Integer totalnumberGuest; /** * curb weight */ @RecordTemplate(rowNo = 44, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "curb weight ") private Double curbWeight; /** * Maximum total mass of train */ @RecordTemplate(rowNo = 45, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Maximum total mass of train") private Double maximumTotalMassOfTrain; /** * Access Certificate No */ @RecordTemplate(rowNo = 46, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Access Certificate No") private String netNumber; /** * Date of initial registration */ @RecordTemplate(rowNo = 47, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Date of initial registration") private String initialRegistrationDate; /** * Date of annual inspection */ @RecordTemplate(rowNo = 48, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Date of annual inspection") private String annualInspectionDate; /** * Mandatory retirement date */ @RecordTemplate(rowNo = 49, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Mandatory retirement date") private String mandatoryScrapDate; /** * Abbreviation of affiliated enterprise */ @RecordTemplate(rowNo = 50, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Abbreviation of affiliated enterprise") private String enterpriseShortName; /** * Vehicle SN */ @RecordTemplate(rowNo = 51, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "vehicle SN") private String vehicleSn; /** * Model of safety chip (required if the vehicle terminal contains safety chip)) */ @RecordTemplate(rowNo = 52, columnNo = 2, comment = FillCommentEnum.EMPTY, name = "Model of safety chip (required if the vehicle terminal contains safety chip)") private String chipType; /** * Vehicle terminal model (required) */ @RecordTemplate(rowNo = 53, columnNo = 2, comment = FillCommentEnum.FILL, name = "Vehicle terminal model (required)") @NotEmpty(message = "Vehicle terminal model cannot be empty", groups = ) private String tboxType; /** * Activation mode ((code see note 9, required) 0 - no activation required, 1 - activation required) */ @RecordTemplate(rowNo = 54, columnNo = 2, comment = FillCommentEnum.FILL, name = "Activation mode (code see right 9, required)") @NotEmpty(message = "Activation mode cannot be empty", groups = ) private Integer vehRegisterMode; /** * Emission level ((see note 10 for code, required) (1-g6, 2-g5, 3-g4, 4-g3, 5-g2, 6-exhaust aftertreatment system modified vehicle)) */ @RecordTemplate(rowNo = 55, columnNo = 2, comment = FillCommentEnum.FILL, name = "Emission level (code see right 10, required)") @NotEmpty(message = "Leveling cannot be empty", groups = ) private Integer emissionlLevelType; /** * * Vehicle manufacturer */ @RecordTemplate(rowNo = 56, columnNo = 2, comment = FillCommentEnum.FILL, name = "Vehicle manufacturer") @NotEmpty(message = "Vehicle manufacturer cannot be empty", groups = ) private String vehicleFirm; /** * Security chip number (not required for filing) */ private String chipCode; /** * remarks */ private String remark; /** * Vehicle filing results (0: draft; 1: to be reviewed; 2: not filed; 3: approved; 4: failed) * @return */ @TableField(exist = false) private Integer recordResult;

Parsing tool class (most important)

Idea: traverse the attribute annotated with @ ExcelTemplate on entity class, and if there is one that explains the cause cell and its corresponding, pass the row number and column number of the attribute to the method of parsing cell data, return the cell value, and then assign a value to the class through the java file reflection mechanism, and then all the values in excel will be obtained. When parsing cells, exceptions can be thrown in advance according to whether they are required or not.

Methods related to cell parsing:

/** * Get sheet object */ public static Sheet getSheetByStream(InputStream inputStream, String sheetName) { Workbook workbook = null; try { workbook = WorkbookFactory.create(inputStream); } catch (Exception e) { throw new ServiceException("excel Wrong document"); } Sheet sheet = null; if (StringUtils.isBlank(sheetName)) { //Take the first one sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName.trim()); } return sheet; } /** * Read the value of a single cell * * @param sheet * @param name * @param rowNo * @param columnNo */ public static String readCell(Sheet sheet, int rowNo, int columnNo, FillCommentEnum comment, String name) { //5. Get row Row row = sheet.getRow(rowNo); //6. Get cell Cell cell = row.getCell(columnNo); //7. Read cell contents String stringCellValue = getCellValueByType(cell, name); if (comment.getCode() == 0 && StringUtils.isBlank(stringCellValue)) { throw new ServiceException(name + "Cannot be empty"); } logger.info(stringCellValue); return stringCellValue; } public static String getCellValueByType(Cell cell,String name){ String cellValue = ""; if(cell.getCellTypeEnum() == CellType.NUMERIC){ if (HSSFDateUtil.isCellDateFormatted(cell)) { cellValue = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd"); } else { NumberFormat nf = NumberFormat.getInstance(); cellValue = String.valueOf(nf.format(cell.getNumericCellValue())).replace(",", ""); } logger.info(cellValue); }else if(cell.getCellTypeEnum() == CellType.STRING){ cellValue = String.valueOf(cell.getStringCellValue()); }else if(cell.getCellTypeEnum() == CellType.BOOLEAN){ cellValue = String.valueOf(cell.getBooleanCellValue()); }else if(cell.getCellTypeEnum() == CellType.ERROR){ cellValue = "Error type"; throw new ServiceException("Cell"+name+": "+cellValue); } return cellValue; }

Related methods reflected to object assignment:

/** * Analysis of excel record data to object * * @return * @throws ClassNotFoundException * @throws IllegalAccessException * @throws InstantiationException */ public static Object parseExcelToModel(String className, Sheet sheet) throws ClassNotFoundException, IllegalAccessException, InstantiationException { Class<?> clazz = Class.forName(className); Field[] declaredFields = clazz.getDeclaredFields(); Object o = clazz.newInstance(); //Get the flow of excel, and the front end passes in for (Field field: declaredFields) { field.setAccessible(true); if (field.isAnnotationPresent(RecordTemplate.class)) { RecordTemplate annotation = field.getAnnotation(RecordTemplate.class); Class<?> type = field.getType(); logger.info(type.getName()); //Cell value String value = ReadExcellCellUtils.readCell(sheet, annotation.rowNo(), annotation.columnNo(), annotation.comment(), annotation.name()); //Give cell values to object properties by reflection setFieldValue(o, field, type, value); } } return o; } /** * Give cell values to object properties by reflection * @param o * @param field * @param type * @param value * @throws IllegalAccessException */ private static void setFieldValue(Object o, Field field, Class<?> type, String value) throws IllegalAccessException { Object targetValue = null; if (StringUtils.isEmpty(value)) { return; } if (type.equals(Integer.class)) { targetValue = Integer.parseInt(value); } else if (type.equals(Double.class)) { targetValue = Double.parseDouble(value); } else if (type.equals(Float.class)) { targetValue = Float.parseFloat(value); } else if (type.equals(Boolean.class)) { targetValue = Boolean.getBoolean(value); }else{ targetValue = value; } field.set(o, targetValue); } /** * Parsing data to model * * @param className Class name * @param inputStream Input stream * @param sheetName sheetname Can be null, take the first page when null * @return Mapping objects */ public static Object parseExcelToModel(String className, InputStream inputStream, String sheetName) { Sheet sheetByStream = getSheetByStream(inputStream, sheetName); try { return parseExcelToModel(className, sheetByStream); } catch (Exception e) { e.printStackTrace(); throw new ServiceException("Parse data to model fail"); } }

How to use:

Just provide these 3 values. For example: Test: Using postman request: excel data are obtained:

Since then, according to the example of cell, this kind of Excel is suitable for irregular excel, and the number of lines is determined, especially for the reading of template excel.

Summary: This paper mainly implements the example of easyExcel according to the number of rows, and the example of general irregular excel reading according to columns (exactly according to cells). If there is any problem or error, please leave a message to discuss.

WeChat official account No. Search: bloom every day Push related articles from time to time, looking forward to growing with you!!

finish

This paper is a platform of operation tools such as blog group sending one article and multiple sending OpenWrite release

Add new comment

0 comments