关于excel的导入验证

目前公司接的项目都有很多功能的数据都要依靠excel导入，因此对excel中数据的验证必不可少。

先来看一下目前存在的问题：

一:在每处导入的程序中都会包括excel读取、数据验证、错误数据的导出或错误的输出，每次都是拷贝、粘帖、修改，本来不同的地方只有验证部分，但由于格式、验证的不同，在修改过程中还要对excel的读取、错误的导出进行修改、调试，造成工作效率的降低和时间的浪费。

二:由于人员更替频繁，每个人的风格都不一样，对于错误的显示和输出都不一样，客户看到的结果是每处导入的地方返回的错误结果也不一样，有的只是提醒一句成功、失败，有的则会把错误的记录导出excel供客户下载修改。客户对此也有很多抱怨。

解决思路:

在excel导入中我们关心的(也是唯一不同的)是数据的验证、保存，对于读取、错误记录导出并不关心，那就该把这两部分分离出来，这样的好处有:1.导入时不再关心excel的读取和错误信息的导出，编码及调试时不再为这部分付出时间和精力，加快开发效率。2.降低耦合度，目前对excel的操作使用的是jxl，如果以后改为poi那只需要需改excel操作的实现即可。3.统一，所有的导入使用相同的excel操作实现，如果excel读取操作有bug则只需修改一处(写此代码的起因就是一个同事出现的一个bug引起的)，而且对错误记录的输出也有统一的输出。

解决办法:

限于本人的表达能力，要想讲清楚太费时间和篇幅了，在这里就直接上代码了

首先是抽象类 ImportDataMultiSheet，包括excel读取、错误记录导出的实现，支持多sheet及合并单元格的处理

 

import java.io.File;
import java.io.IOException;
import java.lang.reflect.Method;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.TimeZone;

import org.gaosheng.util.exception.EntityException;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * jxl导入excel类，继承类实现验证方法
 * 
 * @author gaosheng
 * 
 */
public abstract class ImportDataMultiSheet {

	private int startRow = 1;
	private int startColumn = 0;
	private int minRows = 1;
	private int minColumns = 1;
	private int maxRows = -1;
	private int maxColumns = -1;
	private Map<Integer, Method> methodMap;
	private Map<Integer, List<String>> holdColumns;
	private List<String>[] titiles ;
	private List<MeregRange> meregRangeList ;
	private Cell curCell;
	private Cell[] curRowCells;
	private int successcount = 0;
	
	private String[] columnMethods = null;
	private int[] needHoldColumns = null;
	private File importExcel;
	private File errorExcel;
	private boolean hasError = false;
	private List<String> errors = new ArrayList<String>();

	/**
	 * 启动导入
	 * 
	 * @return boolean
	 * @throws SecurityException
	 * @throws NoSuchMethodException
	 * @throws EntityException
	 */
	public boolean execute() throws SecurityException, NoSuchMethodException,
			EntityException {
		setMethodMap();
		setHoldColumns();
		Workbook work = null;
		try {
			work = Workbook.getWorkbook(importExcel);
		} catch (Exception e) {
			throw new EntityException("Excel表格读取异常！批量导入失败！<br/>");
		}

		//数据总行数
		int totalRows = 0;
		Sheet sheet = null;
		WritableWorkbook writew = null;
		WritableSheet writes = null;
		
		int sheet_num = work.getNumberOfSheets();
		// 全局验证
		if (!this.validGlobal(work.getSheets())) {
			throw new EntityException("导入文件格式错误");
		}
		try {
			for (int sheet_index = 0; sheet_index < sheet_num ;sheet_index++) {
				sheet = work.getSheet(sheet_index);
				meregRangeList = new ArrayList<MeregRange>();
				int columns = sheet.getColumns();
				int rows = sheet.getRows();
				totalRows += rows;

				for (Range range : sheet.getMergedCells()) {
					Cell topleft = range.getTopLeft();
					Cell bottomRight = range.getBottomRight();
					meregRangeList.add(new MeregRange(topleft.getRow(),topleft.getColumn(),bottomRight.getRow(),bottomRight.getColumn(),getCellValue(topleft)));
				}
				
				writew = Workbook.createWorkbook(errorExcel);
				writes = writew.createSheet("ErrorReport", 0);
				Label label;
				WritableCellFormat wcf;
				titiles = new List[startRow];
				List<String>	list = null;
				for (int i = 0; i < startRow; i++) {
					list = new ArrayList<String>();
					for (int j = 0; j < columns; j++) {
						label = new Label(j, i, getCellValue(sheet.getCell(j, i)));
						writes.addCell(label);
						list.add(getValue(sheet.getCell(j, i)));
					}
					titiles[i] = list;
				}
				label = new Label(columns, startRow - 1, "错误详细");
				WritableFont wf0 = new WritableFont(WritableFont.TIMES, 12);
				wcf = new WritableCellFormat(wf0);
				label.setCellFormat(wcf);
				writes.addCell(label);

				int wi = startRow;
				// -------------------------
				StringBuffer info_temp = null;
				String result = null;
				Method method = null;
				for (int i = startRow; i < rows; i++) {
					curRowCells = sheet.getRow(i);
					if (curRowCells == null || curRowCells.length < minColumns) {
						continue;
					}
					boolean[] wj = new boolean[columns];
					info_temp = new StringBuffer();
					for (int j = startColumn; j < columns; j++) {
						curCell = sheet.getCell(j, i);
						
//						System.out.print(String.format("%-30.30s", this.getValue(curCell))+"  ");
						result = everyCell();
						if (result != null) {
							method = methodMap.get(j);
							if (method == null) {
								continue;
							}
							result = (String) method.invoke(this, null);
						}
						if (result != null) {
							info_temp.append(result);
							info_temp.append(" ");
							wj[j] = true;
						}
						if (holdColumns.get(j) != null) {
							holdColumns.get(j).add(this.getValue(curCell));
						}
						if (info_temp.length() > 0) {
							errors.add("sheet "+sheet.getName()+" 中第 " + (i + 1) + " 行 :"
									+ info_temp.toString());
						}
					}
//					System.out.println();
					if (info_temp.length() > 1) {
						for (int ii = startColumn; ii < columns; ii++) {
							Cell c_temp = sheet.getCell(ii, i);
							label = new Label(ii, wi, c_temp.getContents().trim());
							wcf = new WritableCellFormat();
							if (wj[ii])
								wcf.setBackground(Colour.RED);
							label.setCellFormat(wcf);
							writes.addCell(label);
						}
						label = new Label(columns, wi, info_temp.toString());
						WritableFont wf = new WritableFont(WritableFont.TIMES,
								12);
						wf.setColour(Colour.RED);
						wcf = new WritableCellFormat(wf);
						label.setCellFormat(wcf);
						writes.addCell(label);
						wi++;
					} else {
						this.save();
						successcount ++;
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			this.hasError = true;
			errors.add("sheet "+sheet.getName()+" 第"+this.curCell.getRow() +" 行 第 "+ this.curCell.getColumn()+" 列 :"+this.getCurCell().getContents()+" 遇到错误");
			return false;
		} finally {
			try {
				writew.write();
				writew.close();
				work.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		if (successcount < totalRows - sheet_num*startRow) {
			this.hasError = true;
		}
		return true;
	}

	/**
	 * 全局验证，验证对行数和列数的要求
	 * 
	 * @return
	 */
	public boolean validGlobal(Sheet[] sheets) {
		for (int i = 0; i < sheets.length; i++) {
			if (minRows != -1 && sheets[i].getRows() < minRows) {
				return false;
			} else if (minColumns != -1 && sheets[i].getColumns() < minColumns) {
				return false;
			} else if (maxRows != -1 && sheets[i].getRows() > maxRows) {
				return false;
			} else if (maxColumns != -1 && sheets[i].getColumns() > maxColumns) {
				return false;
			}
		}
		return true;
	}

	/**
	 * 一行数据验证成功后保存
	 * @return boolean
	 */
	public abstract boolean save();
	
	/**
	 * 对每一个单元格进行的操作
	 * @return boolean
	 */
	public abstract String everyCell();
	
	/**
	 * 初始化存储验证列方法的Map
	 * 
	 * @throws SecurityException
	 * @throws NoSuchMethodException
	 */
	@SuppressWarnings("unchecked")
	private void setMethodMap() throws SecurityException, NoSuchMethodException {
		methodMap = new HashMap<Integer, Method>();
		if (columnMethods == null) {
			Method[] methods = this.getClass().getMethods();
			for (int i = 0; i < methods.length; i++) {
				if (methods[i].getName().startsWith("validColumn_")) {
					String column = methods[i].getName().substring(
							methods[i].getName().indexOf("_") + 1);
					try {
						methodMap.put(Integer.parseInt(column), methods[i]);
					} catch (Exception e) {
						throw new NumberFormatException("默认列明必须为数字");
					}
				}
			}
		} else {
			Class<ImportDataMultiSheet> class1 = (Class<ImportDataMultiSheet>) this.getClass();
			for (int i = 0; i < columnMethods.length; i++) {
				methodMap.put(i, class1.getMethod(columnMethods[i], null));
			}
		}
	}

	/**
	 * 初始化存储保留列的Map，保留列用于验证某些列值时需引用其他列的情况
	 */
	private void setHoldColumns() {
		holdColumns = new HashMap<Integer, List<String>>();
		if (needHoldColumns == null) {
			return;
		}
		for (int i = 0; i < needHoldColumns.length; i++) {
			holdColumns.put(needHoldColumns[i], new ArrayList<String>());
		}
	}

	/**
	 * 获得给定单元格的实际值，对于时间会返回 'yyyy-MM-dd HH:mm:ss' 格式的字符串
	 * 
	 * @param cell
	 * @return String
	 */
	public static String getCellValue(Cell cell) {
		if (cell.getType().equals(CellType.NUMBER)) {
			return Double.toString(((NumberCell) cell).getValue());
		} else if (cell.getType().equals(CellType.DATE)) {
			TimeZone gmt = TimeZone.getTimeZone("GMT");
			DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",
					Locale.getDefault());
			dateFormat.setTimeZone(gmt);
			return dateFormat.format(((DateCell) cell).getDate());
		} else if (cell.getType().equals(CellType.EMPTY)) {
			return null;
		} else {
			return cell.getContents().trim();
		}
	}

	public String getValue(Cell cell){
		String value = getCellValue(cell);
		if (value == null || getCellValue(cell).equals("")) {
			for(MeregRange meregRange:meregRangeList){
				if (meregRange.isInRange(cell.getRow(), cell.getColumn())) {
					return meregRange.getValue();
				}
			}
			return value;
		}else {
			return value;
		}
	}
	
	/**
	 * 防止空指针
	 * 
	 * @param object
	 * @return String
	 */
	public String fixNull(Object object) {
		return object == null ? "" : object.toString();
	}

	public int getMinRows() {
		return minRows;
	}

	public void setMinRows(int minRows) {
		this.minRows = minRows;
	}

	public int getMinColumns() {
		return minColumns;
	}

	public void setMinColumns(int minColumns) {
		this.minColumns = minColumns;
	}

	public int getMaxRows() {
		return maxRows;
	}

	public void setMaxRows(int maxRows) {
		this.maxRows = maxRows;
	}

	public int getMaxColumns() {
		return maxColumns;
	}

	public void setMaxColumns(int maxColumns) {
		this.maxColumns = maxColumns;
	}

	public String[] getColumnMethods() {
		return columnMethods;
	}

	public void setColumnMethods(String[] columnMethods) {
		this.columnMethods = columnMethods;
	}

	public File getImportExcel() {
		return importExcel;
	}

	public void setImportExcel(File importExcel) {
		this.importExcel = importExcel;
	}

	public File getErrorExcel() {
		return errorExcel;
	}

	public void setErrorExcel(File errorExcel) {
		this.errorExcel = errorExcel;
	}

	public boolean isHasError() {
		return hasError;
	}

	public int[] getNeedHoldColumns() {
		return needHoldColumns;
	}

	public void setNeedHoldColumns(int[] needHoldColumns) {
		this.needHoldColumns = needHoldColumns;
	}

	public Map<Integer, List<String>> getHoldColumns() {
		return holdColumns;
	}

	public int getStartRow() {
		return startRow;
	}

	public void setStartRow(int startRow) {
		this.startRow = startRow;
	}

	public int getStartColumn() {
		return startColumn;
	}

	public void setStartColumn(int startColumn) {
		this.startColumn = startColumn;
	}

	public Cell getCurCell() {
		return curCell;
	}

	public List<String> getErrors() {
		return errors;
	}

	public Cell[] getCurRowCells() {
		return curRowCells;
	}

	public List<String>[] getTitiles() {
		return titiles;
	}

	public int getSuccesscount() {
		return successcount;
	}

}

 

 下面是一个实现类的范例:

 

 

import java.io.File;
import java.util.List;

import jxl.Cell;

import org.gaosheng.util.exception.EntityException;
import org.gaosheng.util.xls.ImportDataMultiSheet;

public class ImportDatemultiImp extends ImportDataMultiSheet {

	public static void main(String[] args) throws SecurityException, NoSuchMethodException, EntityException {
		File importFile = new File("F:/test.xls");
		File errorFile = new File("F:/error.xls");
		ImportDatemultiImp importDateImp = new ImportDatemultiImp();
		importDateImp.setImportExcel(importFile);
		importDateImp.setErrorExcel(errorFile);
		importDateImp.setStartRow(1);
		importDateImp.execute();
		importDateImp.getErrorExcel();
		for (String error : importDateImp.getErrors()) {
			System.out.println(error);
		}
	}

	//对每一个单元格的执行的统一操作，返回值为错误信息，没有错误则返回null
	public String everyCell() {
		Cell cell = this.getCurCell();
		List<String>	semList = this.getTitiles()[2];
		List<String> courseList = this.getTitiles()[3];
		if (cell.getRow() > 3 && cell.getColumn() > 3) {
			String cellvalue = this.getValue(cell);
			String course_name = courseList.get(cell.getColumn());
			String reg_no = this.getValue(this.getCurRowCells()[1]); 
			String stuname = this.getValue(this.getCurRowCells()[2]);
			if (cellvalue != null && !cellvalue.equals("") && course_name !=null && !course_name.equals("") && reg_no != null && !reg_no.equals("")) {

			}else {
				return "无效成绩";
			}
		}
		return null;
	}

	//定义每一列的验证，默认方法名是validColumn_+列索引，也可以用setColumnMethods(String[] columnMethods)指定列的验证方法 返回值为错误信息，没有错误则返回null
	public String validColumn_1(){
		if (!this.getCurCell().getContents().equals("name")) {
			return "姓名错误";
		}
		return null;
	}
	
	public String validColumn_2(){
		if (!this.getCurCell().getContents().equals("passwd")) {
			return "密码错误";
		}
		return null;
	}

	public String validColumn_3(){
	
		return null;
	}

	//验证成功后保存记录
	public boolean save() {
		return false;
	}
}

 

 

 

评论

7 楼 yylovelei 2012-03-19  

gaosheng08 写道

yylovelei 写道

gaosheng08 写道

yylovelei 写道

import org.gaosheng.util.exception.EntityException;

博主能把这个包贴出来吗？
谢谢了

这个是自定义的异常，随便实现就行

才看到你这个是jxl

对excel2007以上版本是不就不通用了？？

对博主后来所写的excel2007
http://gaosheng08.iteye.com/blog/624758

是不就用不了了？

恩，是的，2007以上的一直没有写。

唉，水平有限啊，这下是判断不了了。

6 楼 gaosheng08 2012-03-18  

yylovelei 写道

gaosheng08 写道

yylovelei 写道

import org.gaosheng.util.exception.EntityException;

博主能把这个包贴出来吗？
谢谢了

这个是自定义的异常，随便实现就行

才看到你这个是jxl

对excel2007以上版本是不就不通用了？？

对博主后来所写的excel2007
http://gaosheng08.iteye.com/blog/624758

是不就用不了了？

恩，是的，2007以上的一直没有写。

5 楼 yylovelei 2012-03-18  

gaosheng08 写道

yylovelei 写道

import org.gaosheng.util.exception.EntityException;

博主能把这个包贴出来吗？
谢谢了

这个是自定义的异常，随便实现就行

才看到你这个是jxl

对excel2007以上版本是不就不通用了？？

对博主后来所写的excel2007
http://gaosheng08.iteye.com/blog/624758

是不就用不了了？

4 楼 gaosheng08 2012-03-18  

yylovelei 写道

import org.gaosheng.util.exception.EntityException;

博主能把这个包贴出来吗？
谢谢了

这个是自定义的异常，随便实现就行

3 楼 yylovelei 2012-03-16  

import org.gaosheng.util.exception.EntityException;

博主能把这个包贴出来吗？
谢谢了

2 楼 gaosheng08 2011-08-03  

fengmengcy 写道

请问这个类是MergeRange 是什么类

忘了贴了，是合并单元格的范围类

package org.gaosheng.util.xls.valid;

public class MeregRange {
	private int topleft_row ;
	private int topleft_column ;
	private int bottomRight_row ;
	private int bottomRight_column;
	private String value ;
	
	public MeregRange() {
		super();
	}

	public MeregRange(int topleft_row, int topleft_column, int bottomRight_row,int bottomRight_column){
		this(topleft_row, topleft_column, bottomRight_row, bottomRight_column, null);
	}
	
	public MeregRange(int topleft_row, int topleft_column, int bottomRight_row,int bottomRight_column,String value) {
		super();
		this.topleft_row = topleft_row;
		this.topleft_column = topleft_column;
		this.bottomRight_row = bottomRight_row;
		this.bottomRight_column = bottomRight_column;
		this.value = value;
	}
	
	public boolean isInRange(int row,int column) {
		if (row >= topleft_row && column >= topleft_column && row <= bottomRight_row && column <= bottomRight_column) {
			return true;
		}
		return false;
	}
	
	public int getTopleft_row() {
		return topleft_row;
	}
	public void setTopleft_row(int topleft_row) {
		this.topleft_row = topleft_row;
	}
	public int getTopleft_column() {
		return topleft_column;
	}
	public void setTopleft_column(int topleft_column) {
		this.topleft_column = topleft_column;
	}
	public int getBottomRight_row() {
		return bottomRight_row;
	}
	public void setBottomRight_row(int bottomRight_row) {
		this.bottomRight_row = bottomRight_row;
	}
	public int getBottomRight_column() {
		return bottomRight_column;
	}
	public void setBottomRight_column(int bottomRight_column) {
		this.bottomRight_column = bottomRight_column;
	}

	public String getValue() {
		return value;
	}

	public void setValue(String value) {
		this.value = value;
	}
	
}

1 楼 fengmengcy 2011-08-03  

请问这个类是MergeRange 是什么类