using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.IO; using Microsoft.Office.Interop.Excel; namespace TestAccess { class Program { static void Main(string[] args) {
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;"; strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;"; strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"; OleDbConnection objConnection = new OleDbConnection(strConnection); objConnection.Open(); OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection); DataSet ds = new DataSet(); myCommandd.Fill(ds, "[Sheet1$]"); System.Data.DataTable dt = ds.Tables["[Sheet1$]"]; Console.WriteLine(dt.Columns[0].ToString()); Console.WriteLine(dt.Columns[1].ToString()); DataRow drDisplay = dt.Rows[0]; int[] num = new int[dt.Columns.Count]; for (int j = 0; ; ) { for (int i = 0; i < dt.Columns.Count; i++) {
if (drDisplay[i] is DBNull) ; else num[i] += Convert.ToInt32(drDisplay[i]);
} if (++j >= dt.Rows.Count) break; drDisplay = dt.Rows[j]; } objConnection.Close(); object MissingValue = Type.Missing; Microsoft.Office.Interop.Excel.Application app = new Application(); Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue); Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; for (int i = 0; i < dt.Columns.Count; i++) { //注意下面是i+1,,excel小标默认从1开始 wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString(); }
{
string str=gv.rows[i].cell[0].text; // 获取gv 中的数据 rows[i] 是第i行
// cell[0] 是 第1 列
// 的到行数据后 就可以进行数据库的插入操作了。
......
}思路是这样。 不知道 08 会 有其他什么办法没有哦?
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace TestAccess
{
class Program
{
static void Main(string[] args)
{
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";
strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";
strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
OleDbConnection objConnection = new OleDbConnection(strConnection);
objConnection.Open();
OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);
DataSet ds = new DataSet();
myCommandd.Fill(ds, "[Sheet1$]");
System.Data.DataTable dt = ds.Tables["[Sheet1$]"];
Console.WriteLine(dt.Columns[0].ToString());
Console.WriteLine(dt.Columns[1].ToString());
DataRow drDisplay = dt.Rows[0];
int[] num = new int[dt.Columns.Count];
for (int j = 0; ; )
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (drDisplay[i] is DBNull) ;
else
num[i] += Convert.ToInt32(drDisplay[i]);
}
if (++j >= dt.Rows.Count) break;
drDisplay = dt.Rows[j];
}
objConnection.Close();
object MissingValue = Type.Missing;
Microsoft.Office.Interop.Excel.Application app = new Application();
Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue);
Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
for (int i = 0; i < dt.Columns.Count; i++)
{
//注意下面是i+1,,excel小标默认从1开始
wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString();
}
wbook.Save();
wbook.Close(true, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wsheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wsheet = null;
wbook = null;
app = null;
GC.Collect();
}
}
}
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/LCL_data/archive/2009/05/06/4154784.aspx
private DataTable XlsToDataTable(String strpath)
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strpath + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataTable dt = new DataTable();
myCommand.Fill(dt);
return dt;
} 或
private void button1_Click(object sender, EventArgs e)
{
string str="";
oleDbConnExcel = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Extended properties=Excel 5.0;Data Source=" + Application.StartupPath.Trim() +"\\a.xls");
oleDbConnExcel.Open();
strGetDataFromExcel = "SELECT * FROM [Sheet1$]";
oleDbCmdExcel = new OleDbCommand(strGetDataFromExcel, oleDbConnExcel);
oleDbDataReaderExcel = oleDbCmdExcel.ExecuteReader();
if (oleDbDataReaderExcel.HasRows == true)
{
oleDbConnAccess.Open();
for (; ; )
{
if (oleDbDataReaderExcel.Read())
{
str= "";
oleDbCmdAccess = new OleDbCommand(str, oleDbConnAccess);
oleDbCmdAccess.ExecuteNonQuery();
oleDbCmdAccess.Dispose();
}
else
break;
}
oleDbConnAccess.Close();
}
oleDbDataReaderExcel.Close();
oleDbCmdExcel.Dispose();
oleDbConnExcel.Close();
}
如果LZ用的OLEDB将EXCEL导入到GRIDVIEW的话
LZ要注意EXCEL格式的问题
EXCEL中有一个下拉项“格式”-->"样式"-->"修改",里面设置的时间啊,数学啊格式的有可能有变化
比如你在EXCEL中设置格式--小数保留到两位。。
那么EXCEL中编辑输入1,EXCEL显示的是1.00,那你用OLEDB的话你只能取得1非你可能想要的1.00
当然用EXCEL.DLL引用然取TEXT属性不存在这个问题不过用EXCEL引用太慢了。。
有一个字节流倒是很NB不知道怎么用。。?