关于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);}}//对每一个单元格的执行的统一操作，返回值为错误信息，没有错误则返回nullpublic 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)指定列的验证方法 返回值为错误信息，没有错误则返回nullpublic 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;}}??
?
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;}} 3 楼 yylovelei 2012-03-16 import org.gaosheng.util.exception.EntityException;