java根据列名读取excel内容

所需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;
	}
}

发布了6 篇原创文章 · 获赞 0 · 访问量 96
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览