使用JXL从Excel中提取数据插入到数据库中

1. 首先表数据基本有一个表头，表头下是数据。这就要求我们必须知道表头的那一列对应哪个数据库字段，但是IBATIS插入都是List<T>结构

 

也就是说T类型已知，要知道表头的那一列对应那一个PO属性。

 

方法如下：

 

import java.util.HashMap;import java.util.Map;import jxl.Cell;import jxl.CellType;import jxl.Sheet;public class JxlExt {/** * 获得Excel Sheet指定列和PO属性对构成的集合 * @param sheet Excel Sheet * @param firstCol 起始列 * @param firstRow 起始行 * @param lastCol 终点列 * @param lastRow 终点行 * @param mTP sheet中的名称和PO对象对应的属性名称对集合,例如<"单位名称","orgName"> * @return Map<Integer, String>,Integer对应列标,String对应PO属性 */public Map<Integer, String> findCell(Sheet sheet, int firstCol,int firstRow, int lastCol, int lastRow, Map<String, String> mTP) throws Exception {Map<Integer, String> mColP = new HashMap<Integer, String>();int numCols = lastCol - firstCol;int numRows = lastRow - firstRow;for (int i = 0; i <= numRows; i++) {int curRow = firstRow + i;for (int j = 0; j <= numCols; j++) {int curCol = firstCol + j;if (curCol < sheet.getColumns() && curRow < sheet.getRows()) {Cell c = sheet.getCell(curCol, curRow);if (c.getType() != CellType.EMPTY) {if (mTP.get(c.getContents()) != null) {mColP.put(curCol, mTP.get(c.getContents()));}}}}}return mColP;}}

 

其次就是要搜索数据了！

 

import java.util.List;import java.util.Map;import jxl.Sheet;public interface IExcelDataManage<T> {/** * @param sheet Excel Sheet * @param firstRow 起始行 * @param lastRow 终点行 * @param mCP 指定列和PO属性对构成的集合 * @return 需要返回的对象集合 * @throws Exception */public abstract List<T> findData(Sheet sheet, int firstRow,int lastRow, Map<Integer, String> mCP) throws Exception;/** * 合并重复的集合数据 * @param srtKey 标识列--对应PO中的属性 * @param lstOrgs 传入集合 * @return 不重复集合 * @throws Exception */public abstract List<T> mergeData(String srtKey,List<T> lstOrgs) throws Exception;}

 

其中一个实现类为插入部门数据。

 

import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import jxl.Sheet;import org.apache.commons.beanutils.PropertyUtils;import com.lfpost.model.IExcelDataManage;import com.lfpost.po.Organization;public class ExcelOrgDataManage implements IExcelDataManage<Organization> { /* (non-Javadoc) * @see jxlTest.IExcelDataManage#findData(jxl.Sheet, int, int, java.util.Map) */public List<Organization> findData(Sheet sheet, int firstRow, int lastRow,Map<Integer, String> mCP) throws Exception {List<Organization> retLst = new ArrayList<Organization>();int numRows = lastRow - firstRow;for (int i = 0; i <= numRows; i++) {int curRow = firstRow + i;Organization org = new Organization();for (Map.Entry<Integer, String> tmp : mCP.entrySet()) {PropertyUtils.setProperty(org, tmp.getValue(), sheet.getCell(tmp.getKey(), curRow).getContents());}retLst.add(org);}return retLst;} /* (non-Javadoc) * @see jxlTest.IExcelDataManage#mergeData(java.lang.String, java.util.List) */public List<Organization> mergeData(String srtKey,List<Organization> lstOrgs) throws Exception {List<Organization> retLst = new ArrayList<Organization>();Map<String, Organization> mColP = new HashMap<String, Organization>();for (Organization org : lstOrgs) {String strMKey = (String) PropertyUtils.getProperty(org, srtKey);mColP.put(strMKey, org);}for (Map.Entry<String, Organization> tmp : mColP.entrySet()) {retLst.add(tmp.getValue());}return retLst;}}

写一个测试

public static void main(String[] args) throws Exception {Map<String, String> mColP = new HashMap<String, String>();mColP.put("单位", "orgName");mColP.put("部门性质", "orgType");Workbook book = Workbook.getWorkbook(new File("E:/test.xls"));// 获得第一个工作表对象Sheet sheet = book.getSheet(0);IExcelDataManage zh = new ZhSExcelManager();Map<Integer, String> m = zh.findCell(sheet, 0, 0, 48, 1, mColP);List<Organization> lorg = zh.findData(sheet, 2, 658, m);lorg = zh.mergeData("orgName", lorg);int i = 1;for (Iterator iterator = lorg.iterator(); iterator.hasNext();) {System.out.println(i++);Organization org = (Organization) iterator.next();System.out.println(org + org.getOrgName() + "_" + org.getOrgType());}book.close();book = null;}

 

数据提取出就可以使用批量插入方法了！