大体上有两种方式: A 。 使用OleAdo.net或是ADO.net 将Excel看成数据源,然后操纵ADO.Net对象进行数据的检索,然后将检索的数据向SQl Insert就行了。 B 。 在程序中使用Excel COM对象,这样相当有直接操纵Excel,对Excel中数据的读取请找一下(论坛上很多)。 一段小的代码如下: using System; using System.Reflection; using System.Runtime.InteropServices; using Excel;namespace excel { /// <summary> /// Class1 的摘要说明。 /// </summary> class excel { public static int Main() { Application exc = new Application(); if (exc == null) { Console.WriteLine("ERROR: EXCEL couldnt be started!"); return 0; }
try { exc.Visible=false; Workbooks wbs=exc.Workbooks; Console.WriteLine("Current workbook counts:"+wbs.Count); Object mis=System.Reflection.Missing.Value; wbs.Add(mis); Console.WriteLine("Current workbook counts:"+wbs.Count); Workbook wbk=wbs.get_Item(1); if(wbk==null) { Console.WriteLine("Can not get the workbook!"); } else { Sheets wsts=exc.Worksheets; Console.WriteLine("Total sheets:"+wsts.Count); wsts.Add(mis,mis,1,mis); Worksheet wst=(Worksheet)wsts.get_Item(4); if(wst==null) { Console.WriteLine("Can not get worksheet"); } else { /// /// mode a input html /// String url="URL;file:///e://项目开发//球赛分析//a.html.html"; QueryTables qtbs=wst.QueryTables; qtbs.Add(url,wst.get_Range("A1","A1"),mis); QueryTable qtb=(QueryTable)qtbs.Item(1); qtb.Refresh(false);//=Excel
/* Object obj=wst.get_Range("C41",mis).Value; if(obj==null) { Console.WriteLine("Obj==Null"); } else { Console.WriteLine("Obj!=Null"); String tmp=(String)obj; String stmp=(String)tmp.Clone(); Console.WriteLine(stmp); if(stmp.Length==0) { Console.WriteLine("Length==0"); } else { Console.WriteLine("Length<>0"); Console.WriteLine("length of range is:"+wst.get_Range("C41",mis).Count); Console.WriteLine(stmp+"==Select all - Remove all:"+(stmp=="Select all - Remove all")); } } */
实在不行就先用Access导入Excel,再用Sql Server导入Access.
方法和属性,慢慢摸索就做的出来了。范例没带,但我记得做的时候看看几个属性和方法(有一个sheet属性..)就可以读取数据了,不难
A 。 使用OleAdo.net或是ADO.net 将Excel看成数据源,然后操纵ADO.Net对象进行数据的检索,然后将检索的数据向SQl Insert就行了。
B 。 在程序中使用Excel COM对象,这样相当有直接操纵Excel,对Excel中数据的读取请找一下(论坛上很多)。
一段小的代码如下:
using System;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel;namespace excel
{
/// <summary>
/// Class1 的摘要说明。
/// </summary>
class excel
{
public static int Main()
{
Application exc = new Application();
if (exc == null)
{
Console.WriteLine("ERROR: EXCEL couldnt be started!");
return 0;
}
try
{
exc.Visible=false;
Workbooks wbs=exc.Workbooks;
Console.WriteLine("Current workbook counts:"+wbs.Count);
Object mis=System.Reflection.Missing.Value;
wbs.Add(mis);
Console.WriteLine("Current workbook counts:"+wbs.Count);
Workbook wbk=wbs.get_Item(1);
if(wbk==null)
{
Console.WriteLine("Can not get the workbook!");
}
else
{
Sheets wsts=exc.Worksheets;
Console.WriteLine("Total sheets:"+wsts.Count);
wsts.Add(mis,mis,1,mis);
Worksheet wst=(Worksheet)wsts.get_Item(4);
if(wst==null)
{
Console.WriteLine("Can not get worksheet");
}
else
{
///
/// mode a input html
///
String url="URL;file:///e://项目开发//球赛分析//a.html.html";
QueryTables qtbs=wst.QueryTables;
qtbs.Add(url,wst.get_Range("A1","A1"),mis);
QueryTable qtb=(QueryTable)qtbs.Item(1);
qtb.Refresh(false);//=Excel
/*
Object obj=wst.get_Range("C41",mis).Value;
if(obj==null)
{
Console.WriteLine("Obj==Null");
}
else
{
Console.WriteLine("Obj!=Null");
String tmp=(String)obj;
String stmp=(String)tmp.Clone();
Console.WriteLine(stmp);
if(stmp.Length==0)
{
Console.WriteLine("Length==0");
}
else
{
Console.WriteLine("Length<>0");
Console.WriteLine("length of range is:"+wst.get_Range("C41",mis).Count);
Console.WriteLine(stmp+"==Select all - Remove all:"+(stmp=="Select all - Remove all"));
}
}
*/
String tmp="";int check=0;
for(int i=15;i<=60;i++)
{
char ch='C';check=0;
for(;ch<='N';ch++)
{
tmp=ch.ToString()+i.ToString();
Object obj=wst.get_Range(tmp,tmp).Value;
if(obj!=null)
{
Console.Write(obj+"\t");
check++;
}
}
if(check==0)break;
Console.WriteLine();
}
Console.WriteLine("-----------Another tables---------------");
///
///next changed
///
/*
wst=(Worksheet)wsts.get_Item(2);
url="URL;file:///e://项目开发//球赛分析//x.html.html";
wst.QueryTables.Add(url,wst.get_Range("A1","A1"),mis);
qtb=(QueryTable)wst.QueryTables.Item(1);
qtb.Refresh(false);
for(int i=15;i<=50;i++)
{
char ch='F';
Console.WriteLine(i.ToString());
for(;ch<='N';ch++)
{
tmp=ch.ToString()+i.ToString();
tmp=(String)wst.get_Range(tmp,tmp).Value;
//Console.WriteLine(tmp);
Console.Write(tmp+"\t");
}
if(tmp.Length==0) break;
Console.WriteLine();
}
*/
}
wbk.Close(false,"c:\\tmp.xls",mis);
exc.Workbooks.Close();
Console.WriteLine("Finish");
}
/*
Workbook wb=(Workbook)wbs.get_Item(1);
wb.OpenLinks("E:\\项目开发\\球赛分析\\test.xls",mis,mis);
Worksheets wst=(Worksheets)exc.Worksheets;
QueryTables qtb=((Worksheet)wst.get_Item(1)).QueryTables;
qtb.Item(1).Refresh(false);
*/
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
Console.Read();
exc.Quit();
Console.WriteLine("Exiting....");
}
/*
exc.Visible=true;
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
if (worksheet == null)
{
Console.WriteLine ("ERROR: worksheet == null");
} worksheet.QueryTables.Item(1).Refresh(False);
*/
//Console.Read();Console.Read();Console.Read();
return 0;
}
}
}