如何将EXCEL数据导入access数据库中的表中去 我的ACCESS数据库中有一个table1的表,表结构:id name pas我的excel中的数据也是分为跟表table1相同的结构我想用jsp把excel中的数据批量导入到access数据库的table1表中请问该如何去做,谢谢 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 /** * 根据formatExcel文件中的格式读取dataExcel中的数据 * * @param formatExcel excel模板的指针 * @param dataExcel excle格式存放的数据 * @return */ public Map readImportdata(HSSFWorkbook formatExcel, HSSFWorkbook dataExcel) { int sheetNumber = 0; int rowNumber = 0; int cellNumber = 0; int allSheet = 0; boolean needSkipFirstRow = true; List resultList = new ArrayList(); /** * resultMap 内含有如下内容: * flag String true:正常执行,可以取结果集 false:程序执行流程错误,但没有异常 * msg String 如果程序出错,此内容保存的是出错信息。 * resultList List 如果程序正常执行,这里保存的是结果集 */ Map resultMap = new HashMap(); String flag = "true"; StringBuffer msg = new StringBuffer(""); resultMap.put("flag", flag); resultMap.put("msg", msg.toString()); if (formatExcel == null) return resultMap; if (dataExcel == null) return resultMap; initialize(); getImportColumns(formatExcel); allSheet = dataExcel.getNumberOfSheets(); for (sheetNumber = 0; sheetNumber < allSheet; sheetNumber++) {//读取所有的sheet页 HSSFSheet dataSheet = dataExcel.getSheetAt(sheetNumber); int allrows = dataSheet.getLastRowNum(); Iterator rowIt = dataSheet.rowIterator(); //如果没有内容,返回,判断是否需要跳过第一行 if (rowIt.hasNext()) { if (needSkipFirstRow) { rowIt.next(); rowNumber++; } } //循环读数据 while (rowIt.hasNext()) { HSSFRow dataRow = (HSSFRow) rowIt.next(); // HSSFRow dataRow = (HSSFRow)dataSheet.getRow(currentrow); int allcells = dataRow.getLastCellNum(); short currentCol = 0; // Iterator cellIt = dataRow.cellIterator(); int columnsize = importColumns.size(); cellNumber = 0; Map oneRow = new HashMap(); for (currentCol = 0; currentCol < allcells; currentCol++) { // while(cellIt.hasNext() && currentCol <columnsize){ // HSSFCell dataCell = (HSSFCell)cellIt.next(); HSSFCell dataCell = dataRow.getCell(currentCol); // currentCol = dataCell.getCellNum(); cellNumber = currentCol; Object colType = importColumns.get(currentCol); if (colType instanceof Map) { //进行字段的校验和转换 Map tempcolType = (Map) colType; String cellContent = getStringFromCell(dataCell); String allownull = (String) tempcolType.get("allownull"); String dbname = (String) tempcolType.get("dbname"); String format = (String) tempcolType.get("format"); Integer maxlength = (Integer) tempcolType.get("maxlength"); String dictionary = (String) tempcolType.get("dictionary"); System.out.println(dbname + " = " + cellContent); if (dbname == null) dbname = ""; //进行非空检验 if (allownull == null) allownull = "true"; if (allownull.equals("false")) { if (cellContent == null || cellContent.trim().equals("")) { flag = "false"; msg.append( " 在第" + (sheetNumber + 1) + "页第" + (rowNumber + 1) + "行第" + (cellNumber + 1) + "单元格[" + cellContent + "]不能为空。\n"); oneRow = null; break; } } //进行maxlength校验 if (maxlength == null) { } else { if (cellContent.trim().length() > maxlength.intValue()) { flag = "false"; msg.append( " 在第" + (sheetNumber + 1) + "页第" + (rowNumber + 1) + "行第" + (cellNumber + 1) + "单元内容[" + cellContent + "]太长。\n"); oneRow = null; break; } } //进行format校验 if (format == null || format.trim().equals("")) { } else { if (!checkFormat(cellContent, format)) { flag = "false"; msg.append( " 在第" + (sheetNumber + 1) + "页第" + (rowNumber + 1) + "行第" + (cellNumber + 1) + "单元内容[" + cellContent + "]格式不对。\n"); oneRow = null; break; } } //进行dictionary处理 if (dictionary == null || dictionary.trim().equals("")) { oneRow.put(dbname, cellContent); } else { Object newvalue = fromContentToCode(cellContent, dictionary); oneRow.put(dbname, newvalue); } } else { //对这个cell不做处理 } // currentCol ++; cellNumber++; } if (oneRow != null && oneRow.size() > 0) resultList.add(oneRow); rowNumber++; } } resultMap.put("flag", flag); resultMap.put("msg", msg.toString()); resultMap.put("resultList", resultList); return resultMap; } /** * 根据formatExcel文件中的格式读取dataExcel中的数据 * * @param formatExcel excel模板的指针 * @param dataExcel excle格式存放的数据 * @return */请问我调用这个函数时该怎么把d\;test.xls中的数据加进去,谢谢 readImportdata(test.xls,test.xls)把test.xls放在该页的同一目录下 请问是这样调用这个函数吗?Map newMap = new HashMap(); newMap = readImportdata(test.xls,test.xls);还有该如何返回这个函数的值呢?我想一行一行的导入到access数据库的表中,我是菜鸟不知道该怎么用Map,谢谢 还有返回的resultList是list类型,该怎么弄? 先上传excle文件,然后用jxl或poi解析,然后insert到数据库http://blog.csdn.net/lcllcl987/archive/2005/01/12/250129.aspx 还有返回的resultList是list类型,该怎么弄?===============循环读出resultList,存入数据库 搞jsp的进来帮忙下,谢谢! Struts2 与Spring 整合时出错,在线等待解答,谢谢 C标签分页。技术性问题。 对象传递 如何在jsp页中将字段值作为初值赋给文本域? 初学问题:用JSP能做论坛吗?(有什么框架) 求代码 痛苦啊,按照书上的方法,在将数据存入数据库前,将数据转为“iso-8859-1”字符集的字符,为什么存入后还是乱码 我想用两个星期的时间学习html(jsp学习旅途中),哪位朋友能提供一些学习方法或心得感激不尽 前台jsp向后台Action传递String类型中文出现乱码,求解决 请教一个JSP问题,急急急 jsf找不到spring bean问题
* 根据formatExcel文件中的格式读取dataExcel中的数据
*
* @param formatExcel excel模板的指针
* @param dataExcel excle格式存放的数据
* @return
*/
public Map readImportdata(HSSFWorkbook formatExcel, HSSFWorkbook dataExcel) {
int sheetNumber = 0;
int rowNumber = 0;
int cellNumber = 0;
int allSheet = 0;
boolean needSkipFirstRow = true;
List resultList = new ArrayList();
/**
* resultMap 内含有如下内容:
* flag String true:正常执行,可以取结果集 false:程序执行流程错误,但没有异常
* msg String 如果程序出错,此内容保存的是出错信息。
* resultList List 如果程序正常执行,这里保存的是结果集
*/
Map resultMap = new HashMap();
String flag = "true";
StringBuffer msg = new StringBuffer("");
resultMap.put("flag", flag);
resultMap.put("msg", msg.toString());
if (formatExcel == null) return resultMap;
if (dataExcel == null) return resultMap;
initialize();
getImportColumns(formatExcel);
allSheet = dataExcel.getNumberOfSheets();
for (sheetNumber = 0; sheetNumber < allSheet; sheetNumber++) {//读取所有的sheet页
HSSFSheet dataSheet = dataExcel.getSheetAt(sheetNumber);
int allrows = dataSheet.getLastRowNum();
Iterator rowIt = dataSheet.rowIterator();
//如果没有内容,返回,判断是否需要跳过第一行
if (rowIt.hasNext()) {
if (needSkipFirstRow) {
rowIt.next();
rowNumber++;
}
}
//循环读数据
while (rowIt.hasNext()) {
HSSFRow dataRow = (HSSFRow) rowIt.next();
// HSSFRow dataRow = (HSSFRow)dataSheet.getRow(currentrow);
int allcells = dataRow.getLastCellNum();
short currentCol = 0;
// Iterator cellIt = dataRow.cellIterator();
int columnsize = importColumns.size();
cellNumber = 0;
Map oneRow = new HashMap();
for (currentCol = 0; currentCol < allcells; currentCol++) {
// while(cellIt.hasNext() && currentCol <columnsize){
// HSSFCell dataCell = (HSSFCell)cellIt.next();
HSSFCell dataCell = dataRow.getCell(currentCol);
// currentCol = dataCell.getCellNum();
cellNumber = currentCol;
Object colType = importColumns.get(currentCol);
if (colType instanceof Map) {
//进行字段的校验和转换
Map tempcolType = (Map) colType;
String cellContent = getStringFromCell(dataCell);
String allownull = (String) tempcolType.get("allownull");
String dbname = (String) tempcolType.get("dbname");
String format = (String) tempcolType.get("format");
Integer maxlength = (Integer) tempcolType.get("maxlength");
String dictionary = (String) tempcolType.get("dictionary");
System.out.println(dbname + " = " + cellContent);
if (dbname == null) dbname = "";
//进行非空检验
if (allownull == null) allownull = "true";
if (allownull.equals("false")) {
if (cellContent == null || cellContent.trim().equals("")) {
flag = "false";
" 在第" + (sheetNumber + 1) + "页第" + (rowNumber + 1) + "行第" + (cellNumber + 1) + "单元格[" +
cellContent +
"]不能为空。\n");
oneRow = null;
break;
}
}
//进行maxlength校验
if (maxlength == null) {
} else {
if (cellContent.trim().length() > maxlength.intValue()) {
flag = "false";
msg.append(
" 在第" + (sheetNumber + 1) + "页第" + (rowNumber + 1) + "行第" + (cellNumber + 1) + "单元内容[" +
cellContent +
"]太长。\n");
oneRow = null;
break;
}
}
//进行format校验
if (format == null || format.trim().equals("")) {
} else {
if (!checkFormat(cellContent, format)) {
flag = "false";
msg.append(
" 在第" + (sheetNumber + 1) + "页第" + (rowNumber + 1) + "行第" + (cellNumber + 1) + "单元内容[" +
cellContent +
"]格式不对。\n");
oneRow = null;
break;
}
}
//进行dictionary处理
if (dictionary == null || dictionary.trim().equals("")) {
oneRow.put(dbname, cellContent);
} else {
Object newvalue = fromContentToCode(cellContent, dictionary);
oneRow.put(dbname, newvalue);
}
} else {
//对这个cell不做处理
}
// currentCol ++;
cellNumber++;
}
if (oneRow != null && oneRow.size() > 0) resultList.add(oneRow);
rowNumber++;
}
}
resultMap.put("flag", flag);
resultMap.put("msg", msg.toString());
resultMap.put("resultList", resultList);
return resultMap;
}
* 根据formatExcel文件中的格式读取dataExcel中的数据
*
* @param formatExcel excel模板的指针
* @param dataExcel excle格式存放的数据
* @return
*/
请问我调用这个函数时该怎么把d\;test.xls中的数据加进去,谢谢
把test.xls放在该页的同一目录下
Map newMap = new HashMap();
newMap = readImportdata(test.xls,test.xls);
还有该如何返回这个函数的值呢?
我想一行一行的导入到access数据库的表中,我是菜鸟不知道该怎么用Map,谢谢
http://blog.csdn.net/lcllcl987/archive/2005/01/12/250129.aspx
===============
循环读出resultList,存入数据库