excel读取数据偷懒相关（根据表字段中英文，批量输出实体类里的字段及注解）

背景：

标准562.xls第一个sheet页中，第二行开始为数据，第二列和第4列分别是中文，大写英文字段。

偷懒输出类似格式

/** 证件类型 */
@JSONField(name = "CARD_TYPE")
private String cardType;

代码记录

<dependency>
            <groupId>com.aspose</groupId>
            <artifactId>aspose-cell</artifactId>
            <version>21.1</version>
            <classifier>jdk17</classifier>
            <scope>system</scope>
            <systemPath>D:/.m2/repository/com/aspose/aspose-cell/21.1/aspose-cell-21.1-jdk17.jar</systemPath>
</dependency>

另一处代码忽略吧，是为了批量读取值域码表写数据库用的。

package com.neusoft.mrqe.utils;

import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.google.common.collect.Lists;
import com.neusoft.mrqe.mybatis.entity.CodomainDetail;
import org.apache.commons.lang.WordUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class AsposeUtil {
    private static final Logger logger = LoggerFactory.getLogger(AsposeUtil.class);
    public static final String file = "D:\\IdeaProjects\\dr2023\\mrqe\\doc\\疾病分类与代码国家临床版2.0(2022年修订版).xlsx";
    public static final String file_standard = "C:\\Users\\meepo\\Desktop\\标准562.xlsx";
    public static void main(String[] args) throws Exception {
//        List<CodomainDetail> r = impAnswerXls(file,new HashMap<>());
//        System.out.println(r);
        printByTable(file_standard);
    }

    private static void printByTable(String file_standard) throws Exception{
        //        // 加载 Excel 文件
        Workbook workbook = new Workbook(file_standard);
        // 获取第一个工作表
        Worksheet worksheet = workbook.getWorksheets().get(0);
        // 读取指定区域的数据
        com.aspose.cells.Range range = worksheet.getCells().createRange("A:D");
        Object[][] values = (Object[][]) range.getEntireColumn().getValue();//多行，每行2个值
        for(int row=1;row<35867;row++){
            if(values[row][3]==null){
                System.out.println("row="+row);
                break;
            }
            System.out.println("/** " + values[row][1] + " */");
            System.out.println("@JSONField(name = \"" + values[row][3] + "\")");
            System.out.println("private String " + toCamelCase(String.valueOf(values[row][3])) + ";");
            System.out.println();
        }

    }

    public static String toCamelCase(String s) {
        String[] words = s.split("_");
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < words.length; i++) {
            String word = words[i];
            if (i == 0) {
                sb.append(word.toLowerCase());
            } else {
                String firstLetter = word.substring(0, 1);
                String restLetters = word.substring(1);
                sb.append(firstLetter.toUpperCase()).append(restLetters.toLowerCase());
            }
        }
        return sb.toString();
    }


    /**
     *
     */
    public static List<CodomainDetail> impAnswerXls(String file,Map<String,String> answerMap) throws Exception {
//        // 加载 Excel 文件
//        Workbook workbook = new Workbook(file);
//        // 获取第一个工作表
//        Worksheet worksheet = workbook.getWorksheets().get(0);
//        // 读取指定区域的数据
//        com.aspose.cells.Range range = worksheet.getCells().createRange("A:B");
//        Object[][] values = (Object[][]) range.getEntireColumn().getValue();//多行，每行2个值
//        Map<String,Map<String,Integer>> ret = new HashMap<>();
//        String k = null;
//        String v = null;
//        CodomainDetail d = null;
        List<CodomainDetail> list = Lists.newArrayList();
//        for(int row=1;row<35867;row++){
//            k = String.valueOf(values[row][0]);
//            v = String.valueOf(values[row][1]);
//            d = new CodomainDetail();
//            d.setIsDel("0");
//            d.setDetailId(UUIDUtils.getUUID());
//            d.setSn(String.format("%05d", row));
//            d.setCodomainId("JBFLYDM");
//            d.setValue(k);
//            d.setMean(v);
//            d.setCreateBy("meepo");
//            d.setCreateDate("202307100000");
//            list.add(d);
//        }
        return list;
    }
}

另附一个单元测试调用

import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.google.common.collect.Lists;
import com.neusoft.mrqe.Application;
import com.neusoft.mrqe.datasource.DbContextHolder;
import com.neusoft.mrqe.mybatis.entity.MrqeDqScore;
import com.neusoft.mrqe.mybatis.service.CodomainDetailService;
import com.neusoft.mrqe.mybatis.service.MrqeDqScoreService;
import com.neusoft.mrqe.utils.AsposeUtil;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.HashMap;
import java.util.List;

/**
 * @author meepo
 * @date: 2023-07-10 17:01
 * @Description:
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class DBTest {

    @Autowired
    private CodomainDetailService codomainDetailService;

    @Autowired
    private MrqeDqScoreService dqScoreService;

//    @Test
    public void testGetUserById() {
        List list = null;
        try {
            list = AsposeUtil.impAnswerXls(AsposeUtil.file,new HashMap<>());
        } catch (Exception e) {
            e.printStackTrace();
        }
//        codomainDetailService.saveBatch(list,5000);
        System.out.println("导入完毕");
    }

    @Test
    public void testData(){
        LambdaQueryWrapper<MrqeDqScore> queryWrapper = Wrappers.lambdaQuery();
        queryWrapper.eq(MrqeDqScore::getPlanId,"764be528174f445a83184a6ccfad6192");
        queryWrapper.last("limit 1");
        DynamicDataSourceContextHolder.push("tag_db");
        MrqeDqScore o = dqScoreService.getOne(queryWrapper,true);
        List<MrqeDqScore> list = Lists.newArrayList();
        MrqeDqScore newObj = null;
        for(int i=0;i<900000;i++){
            newObj = new MrqeDqScore();
            BeanUtils.copyProperties(o, newObj);
            newObj.setUpdateBy(String.valueOf(i+100001));
            list.add(newObj);
        }

        dqScoreService.saveBatch(list,5000);
        DynamicDataSourceContextHolder.poll();
        System.out.println("导入完毕");
    }

}