Excel數(shù)據(jù)

excel數(shù)據(jù)

代碼 我是容器啟動后就加載resource下的目錄 如果有自己的文件服務(wù)器也是一樣, 得到絕對路徑即可,這里需要注意的Excel解析數(shù)字默認(rèn)都當(dāng)做double處理，把電話當(dāng)成數(shù)字分割成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數(shù)據(jù)
 */
@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該注解被用來修飾一個非靜態(tài)的void（）方法。被@PostConstruct修飾的方法會在服務(wù)器加載Servlet的時候運(yùn)行，并且只會被服務(wù)器執(zhí)行一次
     */
    @PostConstruct
    private void getInstance() throws IOException {
        readFile();
        log.info("初始化患者數(shù)據(jù)："+ JSONObject.toJSONString(CACHE_BASIC_INFO));
    }


    /**
     * 根據(jù)文件路徑讀取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);
            // 行數(shù)
            int rowNumbers = sheet.getLastRowNum() + 1;
            // 讀數(shù)據(jù)，第二行開始讀取
            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出現(xiàn)錯誤：", e);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            log.info("讀取患者Excel出現(xiàn)錯誤：", ex);
        } finally {
            if (workbook != null) {
                // 關(guān)閉流，否則會出現(xiàn)內(nèi)存泄漏
                workbook.close();
            }
        }
    }
}