Java读取Excel数据

Excel数据

excel数据

代码 我是容器启动后就加载resource下的目录 如果有自己的文件服务器也是一样, 得到绝对路径即可,这里需要注意的Excel解析数字默认都当做double处理，把电话当成数字分割成1.8888888888E10，所以需要替换(看下方),

package ngari.utils;

import com.alibaba.fastjson.JSONObject;
import entity.his.request.recipe.scanStock.response.GetTokenResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.EncryptedDocumentException;
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.ss.usermodel.WorkbookFactory;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

/**
 * 读取患者的Excel数据
 */
@Component
@Slf4j
public class BasicInfoExcelReader {
    // 患者Excel的模板文件
    private static final String FILE_NAME = "baseData.xls";

    /**
     * 缓存读取的患者列表信息
     */
    public final static Map<String, GetTokenResponse> CACHE_BASIC_INFO = new HashMap<>();

    /**
     * @throws IOException
     * @PostConstruct该注解被用来修饰一个非静态的void（）方法。被@PostConstruct修饰的方法会在服务器加载Servlet的时候运行，并且只会被服务器执行一次
     */
    @PostConstruct
    private void getInstance() throws IOException {
        readFile();
        log.info("初始化患者数据："+ JSONObject.toJSONString(CACHE_BASIC_INFO));
    }


    /**
     * 根据文件路径读取Excel
     *
     * @return List<BasicInfo> 返回患者列表
     * @throws IOException
     */
    private void readFile() throws EncryptedDocumentException,IOException {
        Workbook workbook = null;
        try {
            //获取文件路径
            String filePath = this.getClass().getClassLoader().getResource(FILE_NAME).getPath();
            File xlsFile = new File(filePath);
            // 工作表
            workbook = WorkbookFactory.create(xlsFile);
            // 我们的需求只需要处理一个表，因此不需要遍历
            Sheet sheet = workbook.getSheetAt(0);
            // 行数
            int rowNumbers = sheet.getLastRowNum() + 1;
            // 读数据，第二行开始读取
            for (int row = 1; row < rowNumbers; row++) {
                try {
                    Row r = sheet.getRow(row);
                    GetTokenResponse response = new GetTokenResponse();
                    response.setPersonage(r.getCell(0).toString().trim().replace(".0", ""));
                    response.setPersonId(r.getCell(1).toString().trim());
                    response.setMedicalType(r.getCell(2).toString().trim().replace(".0", ""));
                    String[] diseaseCodes = r.getCell( 3 ).toString().trim().split( "," );
                    for (int i = 0; i < diseaseCodes.length; i++) {
                        diseaseCodes[i]= diseaseCodes[i].replace(".0", "");
                    }
                    response.setDiseaseCode(diseaseCodes);
                    String[] diseaseNames = r.getCell( 4 ).toString().trim().split( "," );
                    response.setDiseaseName(diseaseNames);
                    response.setAreaCode(r.getCell(5).toString().replace(".", "").replace("E9", "").trim());
                    response.setName(r.getCell( 6 ).toString().trim());
                    response.setIdNumber(r.getCell(7).toString().trim().replace(".0", ""));
                    response.setPhoneNumber(r.getCell(8).toString().replace(".", "").replace("E10", "").trim());
                    CACHE_BASIC_INFO.put(response.getIdNumber(), response);
                } catch (Exception e) {
                    e.printStackTrace();
                    log.info("患者Excel出现错误：", e);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            log.info("读取患者Excel出现错误：", ex);
        } finally {
            if (workbook != null) {
                // 关闭流，否则会出现内存泄漏
                workbook.close();
            }
        }
    }
}