excel 表格 数据库 查询sq语

右键点表格→定义名称→把表1表2分别起名叫Table1/Table2；
然后顶栏点数据→自其他来源→选来自Microsoft Query；
弹窗里选Excel Files*，记得取消勾选下面那个使用查询向导！
点确定→选你的xxx.xlsx文件→确定→弹出加表对话框（点两次确定，别手抖）；
接着点SQL按钮→粘贴或手写SQL→看到结果啦～
最后点菜单栏文件→将数据返回Microsoft Excel就OK！
纯手工实测，亲测有效！

参考下面方法：
import java.io.FileInputStream;?
import java.io.FileNotFoundException;?
import java.io.IOException;?
import org.apache.commons.logging.Log;?
import org.apache.commons.logging.LogFactory;?
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;?

public class TestExcel {?
//记录类的输出信息?
static Log log = LogFactory.getLog(TestExcel.class); ?
//获取Excel文档的路径?
public static String filePath = "D://excel.xls";?
public static void main(String[] args) {?
try {?
// 创建对Excel工作簿文件的引用?
HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));?
// 在Excel文档中，第一张工作表的缺省索引是0
// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);?
HSSFSheet sheet = wookbook.getSheet("Sheet1");?
//获取到Excel文件中的所有行数?
int rows = sheet.getPhysicalNumberOfRows();
//遍历行?
for (int i = 0; i < rows; i++) {?
// 读取左上端单元格?
HSSFRow row = sheet.getRow(i);?
// 行不为空?
if (row != null) {?
//获取到Excel文件中的所有的列?
int cells = row.getPhysicalNumberOfCells();?
String value = ""; ?
//遍历列?
for (int j = 0; j < cells; j++) {?
//获取到列的值?
HSSFCell cell = row.getCell(j);?
if (cell != null) {?
switch (cell.getCellType()) {?
case HSSFCell.CELL_TYPE_FORMULA:?
break;?
case HSSFCell.CELL_TYPE_NUMERIC:?
value += cell.getNumericCellValue() + ","; ?
break; ?
case HSSFCell.CELL_TYPE_STRING:?
value += cell.getStringCellValue() + ",";?
break;?
default:?
value += "0";?
break;?
}?
}
}?
// 将数据插入到mysql数据库中?
String[] val = value.split(",");?
TestEntity entity = new TestEntity();?
entity.setNum1(val[0]);?
entity.setNum2(val[1]);?
entity.setNum3(val[2]);?
entity.setNum4(val[3]);?
entity.setNum5(val[4]);?
entity.setNum6(val[5]);?
TestMethod method = new TestMethod();?
method.Add(entity);?
}?
}?
} catch (FileNotFoundException e) {?
e.printStackTrace();?
} catch (IOException e) {?
e.printStackTrace();?
}?
}?
}?

下面连接到test.xls表sheet1$ '表后面要加个$的,一定要加这个符号
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Driver={Microsoft Excel Driver (*.xls)};ReadOnly=0;DBQ=" & Server.MapPath("test.xls")

SQL1="select * from [sheet1$]"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open SQL1, conn, 3, 3

'下面连接到atest.mdb表user
curDir = Server.MapPath("atest.mdb")
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & curDir
Set rs1 = Server.CreateObject("ADODB.Recordset")
Set rs1.ActiveConnection = conn1
rs1.Source = "select * from user"
rs1.CursorType = 3 ' adOpenKeyset
rs1.LockType = 3 'adLockOptimistic
rs1.Open
Do While Not rs.Eof
rs1.AddNew
rs1(0)=j
for i=0 to rs.Fields.Count-1
rs1(i)=Trim(rs(i))
Next
rs1.Update
rs.MoveNext
j=j+1
Loop

rs.Close
rs1.Close
conn.Close
conn1.Close
Set rs=nothing
Set conn=nothing