所需jar包:
commons-fileupload-1.31.jar
poi-3.17.jar
poi-ooxml-3.17.jar
package com.fh.util;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import com.qlyl.db.Sql;
import com.qlyl.util.DataStore;
import com.qlyl.util.GlobalNames;
public class ObjectExcelReadCNV {
public static List<PageData> readExcelLis(int startrow, int startcol, int sheetnum, MultipartFile file) {
if(file.getOriginalFilename().endsWith(".xlsx")) {
return readExcelLisForXlsx(startrow,startcol,sheetnum,file);
}else {
return readExcelLisForXls(startrow,startcol,sheetnum,file);
}
}
private static List<PageData> readExcelLisForXlsx(int startrow, int startcol, int sheetnum, MultipartFile file) {
List<PageData> varList = new ArrayList<PageData>();
try {
CommonsMultipartFile cFile = (CommonsMultipartFile) file;
DiskFileItem fileItem = (DiskFileItem) cFile.getFileItem();
InputStream inputStream = fileItem.getInputStream();
FileInputStream fi = (FileInputStream) inputStream;
XSSFWorkbook wb = new XSSFWorkbook(fi);
XSSFSheet sheet = wb.getSheetAt(sheetnum); // sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; // 取得最后一行的行号
int sampleid = 1;
int custname = 2;
int sex = 3;
int age = 4;
int idnumber = 5;
int telephone = 6;
int source_agent = 7;
int sumitunit = 8;
int specimenkind = 9;
int samplestatus = 10;
int clinicaldiagnosis = 11;
int specialcase = 12;
int collecttime = 13;
int receivedate = 14;
int administrativeoffice = 15;
int sumitname = 16;
int patientid = 17;
int inhosptialnum = 18;
for (int i = 0; i < 1; i++) { // 取第一行标题,根据标题决定字段序列
XSSFRow row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
for (int j = startcol; j < cellNum; j++) {
XSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
} else {
cellValue = String.valueOf((long) cell.getNumericCellValue());
}
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
if (cellValue.equalsIgnoreCase("样本编号") || cellValue.equalsIgnoreCase("样品编号")) {
sampleid = j;
}
if (cellValue.equalsIgnoreCase("姓名")) {
custname = j;
}
if (cellValue.equalsIgnoreCase("性别")) {
sex = j;
}
if (cellValue.equalsIgnoreCase("年龄")) {
age = j;
}
if (cellValue.equalsIgnoreCase("身份证号")) {
idnumber = j;
}
if (cellValue.equalsIgnoreCase("手机号")) {
telephone = j;
}
if (cellValue.equalsIgnoreCase("样本来源")) {
source_agent = j;
}
if (cellValue.equalsIgnoreCase("采样单位")) {
sumitunit = j;
}
if (cellValue.equalsIgnoreCase("标本种类")) {
specimenkind = j;
}
if (cellValue.equalsIgnoreCase("标本状态")) {
samplestatus = j;
}
if (cellValue.equalsIgnoreCase("临床诊断")) {
clinicaldiagnosis = j;
}
if (cellValue.equalsIgnoreCase("特殊情况")) {
specialcase = j;
}
if (cellValue.equalsIgnoreCase("采样时间")) {
collecttime = j;
}
if (cellValue.equalsIgnoreCase("接收时间")) {
receivedate = j;
}
if (cellValue.equalsIgnoreCase("科别")) {
administrativeoffice = j;
}
if (cellValue.equalsIgnoreCase("申请医生")) {
sumitname = j;
}
if (cellValue.equalsIgnoreCase("病人ID")) {
patientid = j;
}
if (cellValue.equalsIgnoreCase("门诊/住院号")) {
inhosptialnum = j;
}
}
}
for (int i = startrow; i < rowNum; i++) { // 行循环开始
PageData varpd = new PageData();
boolean isExist = true;
XSSFRow row = sheet.getRow(i); // 行
int cellNum = row.getLastCellNum(); // 每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { // 列循环开始
XSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
} else {
cellValue = String.valueOf((long) cell.getNumericCellValue());
}
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
if (j == sampleid) {
if (StringUtils.isEmpty(cellValue)) {
isExist = false;
break;
}
varpd.put("sampleid", cellValue.trim());
varpd.put("is_bloodagain", GlobalNames.IS_NO);
}
if (j == custname) {
if (StringUtils.isEmpty(cellValue)) {
isExist = false;
break;
}
varpd.put("custname", cellValue.trim());
}
if (j == sex) {
varpd.put("sex", cellValue.trim());
}
if (j == age) {
varpd.put("age", cellValue.trim());
}
if (j == idnumber) {
varpd.put("idnumber", cellValue.trim());
}
if (j == telephone) {
varpd.put("telephone", cellValue.trim());
}
if (j == source_agent) {
varpd.put("source_agent", cellValue.trim());
}
if (j == sumitunit) {
varpd.put("sumitunit", cellValue.trim());
}
if (j == specimenkind) {
Sql sql = new Sql();
sql.setSql("select t.codename from sys_code t where codeid = ? and codekey = ?");
sql.setString(1, cellValue.trim());
sql.setString(2, "标本种类表");
DataStore executeQuery = sql.executeQuery();
if(executeQuery.size() > 0) {
String codename = executeQuery.getString(0, "codename").trim();
varpd.put("specimenkind", codename);
}else {
varpd.put("specimenkind", cellValue.trim());
}
}
if (j == samplestatus) {
varpd.put("samplestatus", cellValue.trim());
}
if (j == clinicaldiagnosis) {
varpd.put("clinicaldiagnosis", cellValue.trim());
}
if (j == specialcase) {
varpd.put("specialcase", cellValue.trim());
}
if (j == collecttime) {
varpd.put("collecttime", cellValue.trim());
}
if (j == receivedate) {
varpd.put("receivedate", cellValue.trim());
}
if (j == administrativeoffice) {
Sql sql = new Sql();
sql.setSql("select t.codename from sys_code t where codeid = ? and codekey = ?");
sql.setString(1, cellValue.trim());
sql.setString(2, "病人科别表");
DataStore executeQuery = sql.executeQuery();
if(executeQuery.size() > 0) {
String codename = executeQuery.getString(0, "codename").trim();
varpd.put("administrativeoffice", codename);
}else {
varpd.put("administrativeoffice", cellValue.trim());
}
}
if (j == sumitname) {
varpd.put("sumitname", cellValue.trim());
}
if (j == patientid) {
varpd.put("patientid", cellValue.trim());
}
if (j == inhosptialnum) {
varpd.put("inhosptialnum", cellValue.trim());
}
}
if (isExist) {
varList.add(varpd);
}
}
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
return varList;
}
private static List<PageData> readExcelLisForXls(int startrow, int startcol, int sheetnum, MultipartFile file) {
List<PageData> varList = new ArrayList<PageData>();
try {
CommonsMultipartFile cFile = (CommonsMultipartFile) file;
DiskFileItem fileItem = (DiskFileItem) cFile.getFileItem();
InputStream inputStream = fileItem.getInputStream();
FileInputStream fi = (FileInputStream) inputStream;
HSSFWorkbook wb = new HSSFWorkbook(fi);
HSSFSheet sheet = wb.getSheetAt(sheetnum); // sheet 从0开始
int rowNum = sheet.getLastRowNum() + 1; // 取得最后一行的行号
int sampleid = 1;
int custname = 2;
int sex = 3;
int age = 4;
int idnumber = 5;
int telephone = 6;
int source_agent = 7;
int sumitunit = 8;
int specimenkind = 9;
int samplestatus = 10;
int clinicaldiagnosis = 11;
int specialcase = 12;
int collecttime = 13;
int receivedate = 14;
int administrativeoffice = 15;
int sumitname = 16;
int patientid = 17;
int inhosptialnum = 18;
for (int i = 0; i < 1; i++) { // 取第一行标题,根据标题决定字段序列
HSSFRow row = sheet.getRow(i); // 行
int cellNum = row.getLastCellNum();
for (int j = startcol; j < cellNum; j++) {
@SuppressWarnings("deprecation")
HSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
} else {
cellValue = String.valueOf((long) cell.getNumericCellValue());
}
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
if (cellValue.equalsIgnoreCase("样本编号") || cellValue.equalsIgnoreCase("样品编号")) {
sampleid = j;
}
if (cellValue.equalsIgnoreCase("姓名")) {
custname = j;
}
if (cellValue.equalsIgnoreCase("性别")) {
sex = j;
}
if (cellValue.equalsIgnoreCase("年龄")) {
age = j;
}
if (cellValue.equalsIgnoreCase("身份证号")) {
idnumber = j;
}
if (cellValue.equalsIgnoreCase("手机号")) {
telephone = j;
}
if (cellValue.equalsIgnoreCase("样本来源")) {
source_agent = j;
}
if (cellValue.equalsIgnoreCase("采样单位")) {
sumitunit = j;
}
if (cellValue.equalsIgnoreCase("标本种类")) {
specimenkind = j;
}
if (cellValue.equalsIgnoreCase("标本状态")) {
samplestatus = j;
}
if (cellValue.equalsIgnoreCase("临床诊断")) {
clinicaldiagnosis = j;
}
if (cellValue.equalsIgnoreCase("特殊情况")) {
specialcase = j;
}
if (cellValue.equalsIgnoreCase("采样时间")) {
collecttime = j;
}
if (cellValue.equalsIgnoreCase("接收时间")) {
receivedate = j;
}
if (cellValue.equalsIgnoreCase("科别")) {
administrativeoffice = j;
}
if (cellValue.equalsIgnoreCase("申请医生")) {
sumitname = j;
}
if (cellValue.equalsIgnoreCase("病人ID")) {
patientid = j;
}
if (cellValue.equalsIgnoreCase("门诊/住院号")) {
inhosptialnum = j;
}
}
}
for (int i = startrow; i < rowNum; i++) { // 行循环开始
PageData varpd = new PageData();
boolean isExist = true;
HSSFRow row = sheet.getRow(i); // 行
int cellNum = row.getLastCellNum(); // 每行的最后一个单元格位置
for (int j = startcol; j < cellNum; j++) { // 列循环开始
@SuppressWarnings("deprecation")
HSSFCell cell = row.getCell(Short.parseShort(j + ""));
String cellValue = null;
if (null != cell) {
switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellValue = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
} else {
cellValue = String.valueOf((long) cell.getNumericCellValue());
}
break;
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getNumericCellValue() + "";
break;
case 3:
cellValue = "";
break;
case 4:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case 5:
cellValue = String.valueOf(cell.getErrorCellValue());
break;
}
} else {
cellValue = "";
}
if (j == sampleid) {
if (StringUtils.isEmpty(cellValue)) {
isExist = false;
break;
}
varpd.put("sampleid", cellValue.trim());
varpd.put("is_bloodagain", GlobalNames.IS_NO);
}
if (j == custname) {
if (StringUtils.isEmpty(cellValue)) {
isExist = false;
break;
}
varpd.put("custname", cellValue.trim());
}
if (j == sex) {
varpd.put("sex", cellValue.trim());
}
if (j == age) {
varpd.put("age", cellValue.trim());
}
if (j == idnumber) {
varpd.put("idnumber", cellValue.trim());
}
if (j == telephone) {
varpd.put("telephone", cellValue.trim());
}
if (j == source_agent) {
varpd.put("source_agent", cellValue.trim());
}
if (j == sumitunit) {
varpd.put("sumitunit", cellValue.trim());
}
if (j == specimenkind) {
Sql sql = new Sql();
sql.setSql("select t.codename from sys_code t where codeid = ? and codekey = ?");
sql.setString(1, cellValue.trim());
sql.setString(2, "标本种类表");
DataStore executeQuery = sql.executeQuery();
if(executeQuery.size() > 0) {
String codename = executeQuery.getString(0, "codename").trim();
varpd.put("specimenkind", codename);
}else {
varpd.put("specimenkind", cellValue.trim());
}
}
if (j == samplestatus) {
varpd.put("samplestatus", cellValue.trim());
}
if (j == clinicaldiagnosis) {
varpd.put("clinicaldiagnosis", cellValue.trim());
}
if (j == specialcase) {
varpd.put("specialcase", cellValue.trim());
}
if (j == collecttime) {
varpd.put("collecttime", cellValue.trim());
}
if (j == receivedate) {
varpd.put("receivedate", cellValue.trim());
}
if (j == administrativeoffice) {
Sql sql = new Sql();
sql.setSql("select t.codename from sys_code t where codeid = ? and codekey = ?");
sql.setString(1, cellValue.trim());
sql.setString(2, "病人科别表");
DataStore executeQuery = sql.executeQuery();
if(executeQuery.size() > 0) {
String codename = executeQuery.getString(0, "codename").trim();
varpd.put("administrativeoffice", codename);
}else {
varpd.put("administrativeoffice", cellValue.trim());
}
}
if (j == sumitname) {
varpd.put("sumitname", cellValue.trim());
}
if (j == patientid) {
varpd.put("patientid", cellValue.trim());
}
if (j == inhosptialnum) {
varpd.put("inhosptialnum", cellValue.trim());
}
}
if (isExist) {
varList.add(varpd);
}
}
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
return varList;
}
}