百度搜索圈T社区 免费行业视频教程
www.aiquanti.com package com.asima;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
*
* @author asima
* @data 2006-10-18
*/
public class XlsToAccess
{
HSSFSheet globalSheet = null;
/*读取一个指定单元格内容*/
public String readCellValue(String pos)
{
int xpos;
short ypos;
int cellType; /*取得此单元格的类型 0-Numeric,1-String;
3-null*/
String result; /*返回取得的单元格的值*/
ypos = (short) (pos.toUpperCase().charAt(0) - 65);
xpos = Integer.parseInt(pos.substring(1, pos.length())) - 1;
HSSFRow row = null; /* 定义excel中的行 */
HSSFCell cell = null; /* 定义excel中的单元格 */
/* 根据xPos和yPos取得单元格 */
row = globalSheet.getRow(xpos);
cell = row.getCell(ypos);
/** **************此处如果是空需要修改********************************** */
cellType = cell.getCellType();
switch (cellType)
{
case 0: /* 0-Numeric */
result = String.valueOf(cell.getNumericCellValue());
break;
case 1: /* 1-String */
result = cell.getStringCellValue();
break;
case 3: /* 3-null */
result = \"\";
break;
default:
result = \"\";
break;
}
return result;
}
/*读取excel文件并把内容插入到access表中*/
public void insertIntoTable() throws Exception
{
// 创建对Excel工作簿文件的引用
HSSFWorkbook workbook =
new HSSFWorkbook(new FileInputStream(\"D:/temp/test.xls\"));
// 获得一个sheet
globalSheet = workbook.getSheetAt(0);
String value1 = readCellValue(\"c1\");
String value2 = readCellValue(\"c2\");
String value3 = readCellValue(\"c3\");
String value4 = readCellValue(\"c4\");
System.out.println(value1);
System.out.println(value2);
/* 插入数据库 */
Class.forName(\"sun.jdbc.odbc.JdbcOdbcDriver\");
String url = \"jdbc:odbc:asima\";
Connection conn = DriverManager.getConnection(url);
PreparedStatement stmt =
conn.prepareStatement(\"insert into custom values(?,?,?,?)\");
// 定义查询的SQL语句
stmt.setString(1, value1);
stmt.setString(2, value2);
stmt.setString(3, value3);
stmt.setString(4, value4);
stmt.executeUpdate
下载jdom-2.0.6.jar和操作excel的jxl-2.6.jar
/**
* 将execl转成xml
*
* @param excelPath
*被转换的excel文件路径
* @param xmlPath
*转换成xml的路径
*/
public static void execlConvertXml(String excelPath, String xmlPath) {
Workbook readwb = null;
try {
readwb = Workbook.getWorkbook(new File(excelPath));
Element data = new Element(\"data\");// 创建根节点
Document doc = new Document(data);// 根节点添加到文档中;
// 循环每个sheet
for (int m = 0; m < readwb.getNumberOfSheets(); m++) {
Sheet sheet = readwb.getSheet(m);
int rsColumns = sheet.getColumns();// 获取Sheet表中所包含的总列数
int rsRows = sheet.getRows();// 获取Sheet表中所包含的总行数
Cell[] firstCells = sheet.getRow(0);// 获取每个sheet中的第一行标题
// 循环每行,从1行开始1开始,第0行为列名
for (int i = 1; i < rsRows; i++) {
// 创建行节点;
Element row = new Element(\"dataDetail\");
insertHead(row);// 每一行添加相同的信息,可忽略
// 循环当前行的各单元格
for (int j = 1; j < rsColumns; j++) {
Cell cell = sheet.getCell(j, i);// 取出每个单元格
if (cell.getContents() == \"\") {
continue;
}
Element column = new Element(
firstCells[j].getContents());// 创建单元格节点
column.setText(cell.getContents());
row.addContent(column);
}
data.addContent(row);
}
}
// 将标签内容格式化
Format format = Format.getPrettyFormat();
XMLOutputter XMLOut = new XMLOutputter(format);
XMLOut.output(doc, new FileOutputStream(xmlPath));
} catch (Exception e) {
e.printStackTrace();
} finally {
readwb.close();
System.out.println(\"run over\");
}
}