EXCL模板报盘数据导入

最新推荐文章于 2023-11-02 09:12:10 发布

持.之.以.恒

最新推荐文章于 2023-11-02 09:12:10 发布

阅读量1.3k

点赞数 1

分类专栏： java

本文链接：https://blog.csdn.net/zl834205311/article/details/51371942

版权

java 专栏收录该内容

38 篇文章 0 订阅

订阅专栏

jsp页面

<body>
<div>
<a href="javascript:void(0)" style="background-color: #ff6600;margin-left: 0px;" οnclick="importData()">导入</a>

</div>
</body>	

<script type="text/javascript">
function importData() {

                       
			$.ajaxFileUpload({
				url: "${base!}/w/t/wt_importdata.html?Id=${dto?if_exists.Id!''}",            
				secureuri:false,
				fileElementId: 'textfield',
				dataType: 'json',
				success: function (data, status){
					if(data.hasError == "true"){
						var errors = [];
						for(var key in data){
							if(key != "hasError"){
								errors.push("第" + key + data[key]);
							}
						}
						//alert("上传失败!");
						mbox({
							title : "系统提示",
							message : errors.join("<br>")
							
						}); 
					}else if(data.status == "2"){
						///alert("上传失败!");
						 mbox({
							title : "系统提示",
							message : data.message
						
						}); 
					}else if(data.status == "4"){
						//alert("上传成功!");
						 mbox({
							title : "系统提示",
							message : data.message
							
						}); 
					}
					refreshPage();
				},
				error: function (data, status, e){
					
					alert("上传失败!");
					
				}
			});
	}

</script>

ExcelBean，处理excl表格数据：

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.regex.Pattern;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.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.poifs.filesystem.POIFSFileSystem;

/**
 * @description <h3>regex</h3>
 * <p>int 校验整型数据</p>
 * <p>number 校验数字</p>
 * <p>date,dateformat,date/date,dateformat,string 校验日期</p>
 * <p>code 校验选择项</p>
 * <p>cardid 校验身份证号</p>
 * <p>自定义校验规则或为空不做校验</p>
 * <h3>properties</h3>
 * <p>bean属性property数组</p>
 * <h3>proLength</h3>
 * <p>bean属性值最大长度数组</p>
 * @author 
 */
//EXCEL中数据都转为String类型处理
public final class ExcelBean<T> {

	private String[] properties;//字段名称
	private int[] proLength;//字段长度
	private int sheetno; //sheetno 当前处理的sheet页
	private int rowno; //rowno 起始行
	private int headerrowno; //标题行
	private int i; //i 起始列
	private Class<T> clazz;//bean的Class
	
	private String[] regepx;//正则校验
	private boolean[] notnull;
	private AbstractSpecialProperties specialProperties;
	
	/**
	 * @description 需要特殊处理的属性
	 * <p>需要自定义类并继承AbstractSpecialProperties抽象类，并实现特殊处理属性的方法dealSpecialMethod</p>
	 * @param specialProperties
	 * @void
	 * @author 
	 * @date 2015-11-13下午5:08:57
	 */
	public void setSpecialProperties(AbstractSpecialProperties specialProperties) {
		this.specialProperties = specialProperties;
	}
	
	public boolean[] getNotnull() {
		return notnull;
	}
	/**
	 * @description 设置是否校验可为空数组
	 * @param notnull
	 * @void
	 * @author 
	 * @date 
	 */
	public void setNotnull(boolean[] notnull) {
		this.notnull = notnull;
	}
	private int maxRownum = 0;//导入的最大条数限制
	//保存导入的数据
	private List<T> datas;
	public List<T> getDatas() {
		return datas;
	}
	public void setDatas(List<T> datas) {
		this.datas = datas;
	}
	/**
	 * @description 如果有错误，errors不为空，
	 * <p>key为ij，第几行第几列</p>
	 * <p>value为第i行第j列，+ header[j-1] + '数据错误' </p>
	 */
	private Map<String, String> errors;
	public int getHeaderrowno() {
		return headerrowno;
	}
	public void setHeaderrowno(int headerrowno) {
		this.headerrowno = headerrowno;
	}
	public String[] getProperties() {
		return properties;
	}
	public void setProperties(String[] properties) {
		this.properties = properties;
	}
	public int[] getProLength() {
		return proLength;
	}
	public void setProLength(int[] proLength) {
		this.proLength = proLength;
	}
	public int getSheetno() {
		return sheetno;
	}
	public void setSheetno(int sheetno) {
		this.sheetno = sheetno;
	}
	public int getRowno() {
		return rowno;
	}
	public void setRowno(int rowno) {
		this.rowno = rowno;
	}
	public int getI() {
		return i;
	}
	public void setI(int i) {
		this.i = i;
	}
	public Class<T> getClazz() {
		return clazz;
	}
	public void setClazz(Class<T> clazz) {
		this.clazz = clazz;
	}
	public String[] getRegepx() {
		return regepx;
	}
	public void setRegepx(String[] regepx) {
		this.regepx = regepx;
	}
	public Map<String, String> getErrors() {
		return errors;
	}
	public void setErrors(Map<String, String> errors) {
		this.errors = errors;
	}
	public int getMaxRownum() {
		return maxRownum;
	}
	/**
	 * @description 设置可导入的最大条数
	 * @param maxRownum
	 * @void
	 * @author
	 * @date 
	 */
	public void setMaxRownum(int maxRownum) {
		this.maxRownum = maxRownum;
	}
	/**
	 * @description 有错误返回true，没有错误返回false
	 * @return
	 * @boolean
	 * @author 
	 * @date 
	 */
	public boolean hasErrors(){
		return !errors.isEmpty();
	}
	/**
	 * @description properties，proLength，regepx必须存在，长度必须相等
	 * @param properties 属性数组不可为空
	 * @param proLength 属性长度不可为空
	 * @param sheetno 数据页
	 * @param rowno 数据起始行
	 * @param headerrowno 标题行
	 * @param i 起始列
	 * @param clazz
	 * @param regepx 正则匹配不可为空
	 */
	public ExcelBean(String[] properties, int[] proLength, String[] regexp, int sheetno,
			int rowno, int i, int headerrowno, Class<T> clazz){
		this.properties = properties;
		this.proLength = proLength;
		this.sheetno = sheetno;
		this.rowno = rowno;
		this.i = i;
		this.clazz = clazz;
		this.regepx = regexp;
		this.headerrowno = headerrowno;
		//按行列排序
		this.errors = new TreeMap<String, String>(new Comparator<String>() {
			@Override
			public int compare(String o1, String o2) {
				if(o1 != null && o2 != null){
					if(o1.contains("行") && o1.contains("列") && o2.contains("行") && o2.contains("列")){
						int temp1 = Integer.valueOf(o1.substring(0, o1.indexOf("行")));
						int temp2 = Integer.valueOf(o2.substring(0, o2.indexOf("行")));
						if(temp1 == temp2){
							temp1 = Integer.valueOf(o1.substring(o1.indexOf("行") + 1, o1.indexOf("列")));
							temp2 = Integer.valueOf(o2.substring(o2.indexOf("行") + 1, o2.indexOf("列")));
							return temp1 - temp2;
						}
						return temp1 - temp2;
					}
				}
				return o1.compareTo(o2);
			}
		});
	}
	/**
	 * @description 处理报盘模板Excel中的数据，并作校验
	 * @param source
	 * @return
	 * @List<T>
	 * @author 
	 * @throws IOException 
	 * @date 
	 */
	public List<T> impExcel(InputStream source){
		List<T> objs = new ArrayList<T>();
		boolean iterateFlag = true;
		int currRow = rowno;
		try {
			HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(
					new BufferedInputStream(source)));
			String[] header = getExcelHeader(wb, sheetno, headerrowno, properties.length, i);
			if(!hasErrors()){
				HSSFSheet sheet = wb.getSheetAt(sheetno);
				if(sheet == null){
					errors.put("00", "导入文件sheet页错误");
					iterateFlag = false;
				}
				while (iterateFlag) {
					if(maxRownum != 0 && currRow >= maxRownum + rowno){
						//超过最大行数限制，多余数据忽略
						break;
					}
					HSSFRow row = sheet.getRow(currRow);
					
					if(row == null){//已到最后一行
						break;
					}
					if(checkLastRow(row, this.properties.length)){//有空行即退出循环。如果是最后一行返回true
						break;
					}else{
						T t = clazz.newInstance();
						HSSFCell cell;
						for (int i = 0; i < header.length; i++) {
							cell = row.getCell(i + this.i);
							
							if(cell == null){
								if(this.notnull != null){
									if(this.notnull[i]){//不可为空
										errors.put((currRow + 1) + "行" + (i + this.i + 1) + "列" , header[i] + "信息不能为空，请按要求填写。");
									}
								}
								continue;
							}
							String value = getStringValueFromCell(cell);
							if(StringUtils.isBlank(value)){//值为空，不作处理。
								continue;
							}
							//特殊处理属性的类存在
							if(this.specialProperties != null){
								//特殊属性不为空并且当前属性属于特殊属性
								if(this.specialProperties.getSpecialPropertiesList() != null && this.specialProperties.getSpecialPropertiesList().contains(this.properties[i])){
									try {
										value = this.specialProperties.dealSpecialMethod(properties[i], value);
										BeanUtils.copyProperty(t, properties[i], value);
									} catch (Exception e) {
										e.printStackTrace();
										errors.put((currRow + 1) + "行" + (i + this.i + 1) + "列", header[i] + "信息错误，请按要求填写。");
									}
									//不做其他校验
									continue;
								}
							}
							if("code".equals(regepx[i])){
								if(value.indexOf(" ") == -1){//代码项不含空格，提示错误
									errors.put((currRow + 1) + "行" + (i + this.i + 1) + "列", header[i] + "信息错误，应为选择项，请按要求选择。");
									continue;
								}else{
									value = value.substring(0, value.indexOf(" "));
								}
								
								if(!Pattern.matches("^[a-zA-Z0-9_\\-]*$", value)){//代码项中只允许有字符和数字、下划线、横线
									errors.put((currRow + 1) + "行" + (i + this.i + 1) + "列" , header[i] + "信息错误，请按要求选择。");
									continue;
								}
							}else if("int".equals(regepx[i])){
								if(!Pattern.matches("^[1-9][0-9]*$", value)){//整数校验
									errors.put((currRow + 1) + "行" + (i + this.i + 1) + "列" , header[i] + "信息错误，请输入整数。");
									continue;
								}
							}else if("number".equals(regepx[i])){//校验数字
								try {
									Double.valueOf(value);
								} catch (Exception e) {
									errors.put((currRow + 1) + "行" + (i + this.i + 1) + "列" , header[i] + "信息应为数字，请按要求填写。");
									continue;
								}
							}else if("cardid".equals(regepx[i])){//校验身份证号
								if(!Pattern.matches("^(([0-9]{17}[Xx]{1})|([1-9]{1}[0-9]{17}))$", value)){
									errors.put((currRow + 1) + "行" + (i + this.i + 1) + "列" , header[i] + "信息为身份证号，现填的内容为" + value +",长度为" + value.length() + "。请按要求填写。");
									continue;
								}
							}else if(regepx[i] != null && regepx[i].startsWith("date,")){//date已英文逗号隔开date,format日期格式化
								try {
									String[] datesReg = regepx[i].split(",");
									SimpleDateFormat format = new SimpleDateFormat(datesReg[1]);
									Date date = format.parse(value.trim());
									if(datesReg.length == 3){//保存成字符串格式
										SimpleDateFormat dateFormat = new SimpleDateFormat(datesReg[2]);
										BeanUtils.copyProperty(t, properties[i], dateFormat.format(date));
									}else{
										BeanUtils.copyProperty(t, properties[i], date);
									}
								} catch (Exception e) {
									errors.put((currRow + 1) + "行" + (i + this.i + 1) + "列" , header[i] + "信息错误，请按要求填写。");
								}
								continue;//时间赋值之后跳到下一个CELL
							}else if(regepx[i] != null){//自定义校验
								if(!Pattern.matches(regepx[i], value)){
									errors.put((currRow + 1) + "行" + (i + this.i + 1) + "列" , header[i] + "信息格式不正确，请按要求填写。");
									continue;
								}
							}else{
								//按普通字符串填写
							}
							//校验长度是否超范围
							if(value.length() > proLength[i]){
								errors.put((currRow + 1) + "行" + (i + this.i + 1) + "列" , header[i] + "信息超过要求长度，请按要求填写。");
								continue;
							}else{
								BeanUtils.copyProperty(t, properties[i], value);
							}
						}
						objs.add(t);
					}
					currRow++;
				}
			}
			//关闭文件流
			source.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(this.hasErrors()){//校验出现错误，返回空。不进行导入。
			return null;
		}
		this.setDatas(objs);//导入的数据存放于datas中，便于保存
		return objs;
	}
	
	private String[] getExcelHeader(HSSFWorkbook workbook, int sheerno, int headerrow, int length, int i){
		String[] header = new String[length];
		try {
			HSSFWorkbook wb = workbook;
			if(sheetno > wb.getNumberOfSheets()){
				return null;
			}
			HSSFSheet sheet = wb.getSheetAt(sheetno);
			if (sheet == null) {
				errors.put("header00", "导入文件sheet页错误！");
				throw new Exception("导入文件sheet页错误！");
			}
			HSSFRow row = sheet.getRow(headerrowno);
			if (row != null) {
				for (int j = 0; j < length; j++) {
					HSSFCell cell = (HSSFCell) row.getCell(j + i);
					if(cell == null ){
						continue;
					}
					String value;
					if(getObjFormCell(cell) instanceof java.lang.String) {
						value = String.valueOf(getObjFormCell(cell));
					}else {
						continue;
					}
					if(value!=null){
						value=value.replaceAll(" ", "");
						value=value.replaceAll("\\n", "");
						value=value.replaceAll("\\*", "");
					}
					if (j == 0 && value != null && "end".equalsIgnoreCase(value)) {
						break;
					}
					if(value != "" || value != null){
						if(header[j]==null){
							header[j] = value;
						}else {
							if(header[j]!=""){
								header[j] += value;
							}else if(i > 0){
								header[j] = header[j-1]+value;
							}
						}
					}
				}
			}
		} catch (Exception ex) {
			errors.put("header01", "获取模板头部信息时错误！");
			throw new RuntimeException(ex);
		}
		return header;
	}
	
	private static Object getObjFormCell(HSSFCell cell) {
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC :
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					return cell.getDateCellValue();
				}
				return (Object) new Double(cell.getNumericCellValue());
				
			case HSSFCell.CELL_TYPE_STRING : 
				return cell.getStringCellValue();
				
			default:
				return (Object) cell.getStringCellValue();
		}
	}
	/**
	 * @description 获取excel表格中的字符串数据
	 * @param cell
	 * @return
	 * @String
	 * @author 
	 * @date 
	 */
	private static String getStringValueFromCell(HSSFCell cell){
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC :
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					return date == null ? null : date.toString();
				}
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);//修改为文本数据
				return cell.getStringCellValue();
			case HSSFCell.CELL_TYPE_STRING : 
				return cell.getStringCellValue();
			default:
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);//修改为文本数据
				return cell.getStringCellValue();
		}
	}
	/**
	 * @description 
	 * @param row
	 * @param cellNum 列数
	 * @return
	 * @boolean
	 * @author 
	 * @date 
	 */
	private boolean checkLastRow(HSSFRow row, int cellNum){
		boolean isLast = true;
		if(row == null){
			return isLast;
		}else{//row不为空
			for (int i = 0; i < cellNum; i++) {
				HSSFCell cell = row.getCell(i);
				if(cell != null){
					//强制cell为String类型，以免取值发生错误
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					String value = getStringValueFromCell(cell);
					if(StringUtils.isNotBlank(value)){//有一个不为空既不是最后一行
						isLast = false;
						break;
					}
				}
			}
		}
		return isLast;
	}
}

定义实体和dao、dto ：

限制EXCL导入数据条数上限：

@Repository
public class testWebDao{

	public int countNum(String Id){
		Bricks bricks = new Bricks(this);
		bricks.eq("Id", Id);
		return countBricks(bricks).intValue();
	}
	
}

实体dto,testWebDto.java

public class testWebDto {


        private String Id;
	private String name;
	private String class;
	private String age;
	private String sex;
    .............

}

service 类，ImportExcelMngImpl .java ,调用excleBean：

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class WgbgfImportExcelMngImpl implements WgbgfImportExcelMng<testWebDto> {
	
	@Autowired
	private testWebDao testWebDao;


	@Override
	public ExcelBean<<span style="font-family: Arial, Helvetica, sans-serif;">test</span>WebDto> tempSaveInJavaBean(InputStream is,<span style="font-family: Arial, Helvetica, sans-serif;">test</span><span style="font-family: Arial, Helvetica, sans-serif;">WebDto t) {</span>
	
		
		int maxNum = 99;
		int currentNum = testWebDao.countNum(t.getId());
		if(currentNum >= maxNum){
			return null;
		}
		
		int[] proLength = new int[]{ 18, 10, 3, 3, 50};
		String[] properties = new String[]{"id", "name", "age", "class", "sex"};
		String[] regexp = new String[]{"cardid", null, "code",  "^[0-9\\-]{0,50}$",
									  "number", "date,yyyyMMdd,yyyy-MM-dd", "date,yyyyMMdd,yyyy-MM-dd", null};
		boolean[] notnull = new boolean[]{true, true, true, false, true};
		ExcelBean<testWebDto> excelBean = new ExcelBean<testWebDto>(properties, proLength, regexp, 1, 3, 0, 1, testWebDto.class);
		excelBean.setNotnull(notnull);
		excelBean.setMaxRownum(maxNum - currentNum);
		excelBean.impExcel(is);
		return excelBean;
	}


}

action 这里用的是SpringMVC注解模式：

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartFile;




@Controller
@RequestMapping("/t/")

public class WtAct {


	@Autowired
	private ImportExcelMng<testWebDto> importExcelMng;

       @ResponseBody
	@RequestMapping("wt_importdata.html")
	public void importData(MultipartHttpServletRequest request, String Id, ModelMap model, HttpServletResponse response){
		
		
		testWebDto dto = new testWebDto();
		dto.setId(Id);
		Iterator<String> fileIter = request.getFileNames();
		String message="";
		if (!fileIter.hasNext()) {
			message="模板文件错误，请查看！";
			JSONUtil.writejson(response, "{'status' : '2', 'message' : '" + message + "'}");
			return;
		}
		CommonsMultipartFile file = (CommonsMultipartFile) request.getFile("textfield");
		String fileName = file.getOriginalFilename();
		if (!fileName.endsWith(".xls")) {
			message="模板文件错误，请查看！";
			JSONUtil.writejson(response, "{'status' : '2', 'message' : '" + message + "'}");
			return;
		} else if(StringUtils.isBlank(Id)){
			message="请先填写并保存基本补贴信息！";
			JSONUtil.writejson(response, "{'status' : '2', 'message' : '" + message + "'}");
			return;
		} else {
			try {
				ExcelBean<testWebDto> excelBean = this.importExcelMng.tempSaveInJavaBean(file.getInputStream(), dto);
				if(excelBean == null){//花名册已达到最大人员数量限制
					message = "花名册人员数量已达到最大值。";
				}else if(excelBean.hasErrors()){//报盘文件存在错误信息
					excelBean.getErrors().put("hasError", "true");
					JSONUtil.write(response, excelBean.getErrors());
					return;
				}else{//通过校验，数据保存至临时表
					importExcelMng.saveData(excelBean.getDatas(), dto);
					message = importExcelMng.importCheckData(dto);//调用过程校验报盘表数据  /** saveData和importCheckData分别是保存和校验数据方法，此处不详细叙述
				}
			} catch (Exception e) {
				e.printStackTrace();
				message = "获取模板文件信息出错";
			}
		}
		
		JSONUtil.writejson(response, "{'status' : '4', 'message' : '" + message + "'}");
	}
	



}