这是我自己发布的vb.net的一段程序，你可以参考。非常好用，反过来就行了上有太多的使用打开隐藏文档的方式将DATASET或者DATATABLE导出到EXCEL. 这样做有几个问题: 1.EXCEL进程很难杀死,甚至出现无响应 2.响应速度慢 3.必须要安装OFFICE才能使用 当然,也有它的优点,那就是格式化能力强,能够做出格式化的EXCEL报表.但是比起常常出错,反映速度,以及特定环境看来,优点也不是很明显 在此贡献给大家一个不同的导出EXCEL方式 优点是: 1.速度快 2.不容易出错 3.无需安装OFFICE 但是也有确定,就是导出的报表象EXCEL一样,没有格式化. 因为此种方法就是将EXCEL当成数据库来创建的. 以下为详细的程序,需要研究的朋友只需要建立一个WIN窗体,将以下代码覆盖原来的全部代码就OK了.不过请自己在BIN目录下放一个数据库,或者将程序里面的连接代码改一下: Imports System.Data.OleDb Imports System.Data Imports Scripting Public Class Form1 Inherits System.Windows.Forms.Form Private dataset As New dataset Private mytable As DataTable Private dbpath As String = Application.StartupPath & "\YJ_Manage.mdb" '这里请自己改 Private dbconnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath Private dataadapter As OleDbDataAdapter Private dataconnection As OleDbConnection Private dataset As dataset #Region " Windows 窗体设计器生成的代码 " Public Sub New() MyBase.New() '该调用是 Windows 窗体设计器所必需的。 InitializeComponent() '在 InitializeComponent() 调用之后添加任何初始化 End Sub '窗体重写 dispose 以清理组件列表。 Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub 'Windows 窗体设计器所必需的 Private components As System.ComponentModel.IContainer '注意: 以下过程是 Windows 窗体设计器所必需的 '可以使用 Windows 窗体设计器修改此过程。 '不要使用代码编辑器修改它。 Friend WithEvents btnExport As System.Windows.Forms.Button Friend WithEvents SaveDl As System.Windows.Forms.SaveFileDialog <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.btnExport = New System.Windows.Forms.Button Me.SaveDl = New System.Windows.Forms.SaveFileDialog Me.SuspendLayout() ' 'btnExport ' Me.btnExport.Location = New System.Drawing.Point(104, 128) Me.btnExport.Name = "btnExport" Me.btnExport.Size = New System.Drawing.Size(80, 32) Me.btnExport.TabIndex = 0 Me.btnExport.Text = "Button1" ' 'Form1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14) Me.ClientSize = New System.Drawing.Size(292, 273) Me.Controls.Add(Me.btnExport) Me.Name = "Form1" Me.Text = "Form1" Me.ResumeLayout(False) End Sub #End Region Private Function getdatafromdb(ByVal sqlstr As String) As dataset Try dataconnection = New OleDbConnection dataconnection.ConnectionString = dbconnstr dataadapter = New OleDbDataAdapter(sqlstr, dataconnection) dataset = New DataSet dataset.Clear() dataadapter.Fill(dataset) dataconnection.Close() Catch ex As Exception MessageBox.Show(Err.Description, "数据错误", MessageBoxButtons.OK, MessageBoxIcon.Warning) Exit Function End Try If dataset.Tables(0).Rows.Count > 0 Then Return dataset Else Return Nothing End If End Function Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click Dim MyOleDbCn As New OleDbConnection Dim MyOleDbCmd As New OleDbCommand Dim intRowsCnt, intColsCnt As Integer Dim strSql As String, strFlName As String Dim Fso As New FileSystemObject dataset = getdatafromdb("select * from Employee_userinfo_main") mytable = dataset1.Tables(0) SaveDl.Title = "保持为" SaveDl.Filter = "xls工作薄|*.xls" If SaveDl.ShowDialog = DialogResult.OK Then If SaveDl.FileName <> "" Then strFlName = SaveDl.FileName() Else Exit Sub End If Else Exit Sub End If Try Me.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor MyOleDbCn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;" & _ "Data Source=" & strFlName & ";" & _ "Extended ProPerties=""Excel 8.0;HDR=Yes;""" MyOleDbCn.Open() MyOleDbCmd.Connection = MyOleDbCn MyOleDbCmd.CommandType = CommandType.Text '第一行插入列标题 strSql = "CREATE TABLE sheet1(" For intColsCnt = 0 To MyTable.Columns.Count - 1 If intColsCnt <> MyTable.Columns.Count - 1 Then strSql = strSql & MyTable.Columns(intColsCnt).Caption & " text," Else strSql = strSql & MyTable.Columns(intColsCnt).Caption & " text)" End If Next MyOleDbCmd.CommandText = strSql MyOleDbCmd.ExecuteNonQuery() '插入各行 For intRowsCnt = 0 To MyTable.Rows.Count - 1 strSql = "INSERT INTO sheet1 VALUES('" For intColsCnt = 0 To MyTable.Columns.Count - 1 If intColsCnt <> MyTable.Columns.Count - 1 Then strSql = strSql & MyTable.Rows(intRowsCnt).Item(intColsCnt) & "','" Else strSql = strSql & MyTable.Rows(intRowsCnt).Item(intColsCnt) & "')" End If Next MyOleDbCmd.CommandText = strSql MyOleDbCmd.ExecuteNonQuery() Next MessageBox.Show("数据已经成功导入EXCEL文件" & strFlName, "数据导出", MessageBoxButtons.OK, MessageBoxIcon.Information) Catch ErrCode As Exception MsgBox("错误信息：" & ErrCode.Message & vbCrLf & vbCrLf & _ "引发事件：" & ErrCode.TargetSite.ToString, MsgBoxStyle.OKOnly + MsgBoxStyle.Information, "错误来源：" & ErrCode.Source) Exit Sub Finally MyOleDbCmd.Dispose() MyOleDbCn.Close() MyOleDbCn.Dispose() Me.Cursor.Current = System.Windows.Forms.Cursors.Default End Try End Sub End Class 当然,将EXCEL导入到access,mssql等数据库,datagrid等容器也是用相反类似的方法,将EXCEL作为数据库来使用.
这个我刚做过,告诉你一个超级简单又超级方便的方法 dim app as excel.application app = new excel.application app.visable = true dim wb = excel.workbooks '打开你的表格文件 wb = app.workbooks.open("c:\b.xls") dim st as excel.worksheet 'st = wb.worksheets.add() st = wb.worksheets(1) '把A1中的值读到strTemp中去 dim strTemp as string strTemp = st.cell(1,1).value '释放相关内存 app.quite() st = nothing wb = nothing app = nothing
//Reads from an Excel file using ODBC private void ReadFromXL(string ExcelFilePath) { System.Data.Odbc.OdbcConnection ExcelConnection = new System.Data.Odbc.OdbcConnection(); System.Data.Odbc.OdbcDataReader ExcelDataReader; System.Data.Odbc.OdbcCommand ExcelCommand = new System.Data.Odbc.OdbcCommand(); string strSQL; ExcelConnection.ConnectionString = @"Driver={Microsoft Excel Driver (*.xls)};DBQ=" + ExcelFilePath + ";ReadOnly=0;"; ExcelCommand.Connection = ExcelConnection; ExcelCommand.CommandType = System.Data.CommandType.Text; //select data from the Customers sheet. Note that sheet name has to be in [] and suffixed with a $ sign ExcelCommand.CommandText = "SELECT FirstName, LastName FROM [Customers$]"; ExcelConnection.Open(); ExcelDataReader = ExcelCommand.ExecuteReader();
//Loop thru the records and display the results in a text box /*you could also change the code so that you can populate another database see the comments below for this */ while (ExcelDataReader.Read()) {
//build SQL that can be used for inserting the records into another database strSQL = "INSERT INTO myTable(FirstName, LastName) VALUES('" + ExcelDataReader.GetString(0) + "','" + ExcelDataReader.GetString(1) + "')";
/* The three lines below show the pseudo-code for inserting the records in a database * SQLCommand and SQLConnection are variables that have to declared * Connection string for SQLConnection will depend on the database * Open SQLConnection before the entering the while loop and don't forget to Dispose it after the loop */ //SQLCommand.CommandText = strSQL; //SQLCommand.Connection = SQLConnection; //SQLCommand.ExecuteNonQuery(); textBox1.Text += strSQL + System.Environment.NewLine; }
再来另外一个,可能有些复杂,但是考虑的很全面; ExcelReader.cs 类,看懂了以后,只管在用户的操作窗体,进行实例化,调用即可!! using System; using System.Data; using System.Data.OleDb; namespace utilities { /// <summary> /// Summary description for ExcelReader. /// </summary> public class ExcelReader : IDisposable { #region Variables private int[] _PKCol; private string _strExcelFilename; private bool _blnMixedData=true; private bool _blnHeaders=false; private string _strSheetName; private string _strSheetRange; private bool _blnKeepConnectionOpen=false; private OleDbConnection _oleConn; private OleDbCommand _oleCmdSelect; private OleDbCommand _oleCmdUpdate; #endregion #region properties public int[] PKCols { get {return _PKCol;} set {_PKCol=value;} } public string ColName(int intCol) { string sColName=""; if (intCol<26) sColName= Convert.ToString(Convert.ToChar((Convert.ToByte((char) 'A')+intCol)) ); else { int intFirst = ((int) intCol / 26); int intSecond = ((int) intCol % 26); sColName= Convert.ToString(Convert.ToByte((char) 'A')+intFirst); sColName += Convert.ToString(Convert.ToByte((char) 'A')+intSecond); } return sColName; } public int ColNumber(string strCol) { strCol = strCol.ToUpper(); int intColNumber=0; if (strCol.Length>1) { intColNumber = Convert.ToInt16(Convert.ToByte(strCol[1])-65); intColNumber += Convert.ToInt16(Convert.ToByte(strCol[1])-64)*26; } else intColNumber = Convert.ToInt16(Convert.ToByte(strCol[0])-65); return intColNumber; }
public String[] GetExcelSheetNames() {
System.Data.DataTable dt = null; try { if (_oleConn ==null) Open();
// Get the data table containing the schema dt = _oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null) { return null; } String[] excelSheets = new String[dt.Rows.Count]; int i = 0; // Add the sheet name to the string array. foreach(DataRow row in dt.Rows) { string strSheetTableName = row["TABLE_NAME"].ToString(); excelSheets[i] = strSheetTableName.Substring(0,strSheetTableName.Length-1); i++; } return excelSheets; } catch(Exception) { return null; } finally { // Clean up. if(this.KeepConnectionOpen==false) { this.Close(); } if(dt != null) { dt.Dispose(); dt=null; } } }
public string ExcelFilename { get { return _strExcelFilename;} set { _strExcelFilename=value;} } public string SheetName { get { return _strSheetName;} set { _strSheetName=value;} } public string SheetRange { get {return _strSheetRange;} set { if (value.IndexOf(":")==-1) throw new Exception("Invalid range length"); _strSheetRange=value;} }
public bool KeepConnectionOpen { get { return _blnKeepConnectionOpen;} set {_blnKeepConnectionOpen=value;} } public bool Headers { get { return _blnHeaders;} set { _blnHeaders=value;} } public bool MixedData { get {return _blnMixedData;} set {_blnMixedData=value;} } #endregion #region Methods #region Excel Connection private string ExcelConnectionOptions() { string strOpts=""; if (this.MixedData ==true) strOpts += "Imex=2;"; if (this.Headers==true) strOpts += "HDR=Yes;"; else strOpts += "HDR=No;"; return strOpts; }
private string ExcelConnection() { return @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + _strExcelFilename + ";" + @"Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 8.0;"+ ExcelConnectionOptions() + Convert.ToChar(34).ToString(); } #endregion #region Open / Close public void Open() { try { if (_oleConn !=null) { if (_oleConn.State==ConnectionState.Open) { _oleConn.Close(); } _oleConn=null; } if (System.IO.File.Exists(_strExcelFilename)==false) { throw new Exception("Excel file " + _strExcelFilename + "could not be found."); } _oleConn = new OleDbConnection(ExcelConnection()); _oleConn.Open(); } catch (Exception ex) { throw ex; } } public void Close() { if (_oleConn !=null) { if (_oleConn.State != ConnectionState.Closed) _oleConn.Close(); _oleConn.Dispose(); _oleConn=null; } } #endregion
System.Data.OleDb.OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\1.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""");
conn.Open();
System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM [1$]";
System.Data.OleDb.OleDbDataReader rdr = cmd.ExecuteReader();
DataSet dataSet11 = new DataSet();
......
我说个比较本的方法，以前做过，但是倒入的oracle的数据
先读出来，按一定格式放道dataset中
在插入数据库
用 chaobeyond(踮起脚尖就更靠近阳光)的办法可以不编程可以使用DTS
这样做有几个问题:
1.EXCEL进程很难杀死,甚至出现无响应
2.响应速度慢
3.必须要安装OFFICE才能使用
当然,也有它的优点,那就是格式化能力强,能够做出格式化的EXCEL报表.但是比起常常出错,反映速度,以及特定环境看来,优点也不是很明显
在此贡献给大家一个不同的导出EXCEL方式
优点是:
1.速度快
2.不容易出错
3.无需安装OFFICE
但是也有确定,就是导出的报表象EXCEL一样,没有格式化.
因为此种方法就是将EXCEL当成数据库来创建的.
以下为详细的程序,需要研究的朋友只需要建立一个WIN窗体,将以下代码覆盖原来的全部代码就OK了.不过请自己在BIN目录下放一个数据库,或者将程序里面的连接代码改一下:
Imports System.Data.OleDb
Imports System.Data
Imports Scripting
Public Class Form1
Inherits System.Windows.Forms.Form Private dataset As New dataset
Private mytable As DataTable
Private dbpath As String = Application.StartupPath & "\YJ_Manage.mdb" '这里请自己改
Private dbconnstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath Private dataadapter As OleDbDataAdapter
Private dataconnection As OleDbConnection
Private dataset As dataset #Region " Windows 窗体设计器生成的代码 " Public Sub New()
MyBase.New() '该调用是 Windows 窗体设计器所必需的。
InitializeComponent() '在 InitializeComponent() 调用之后添加任何初始化 End Sub '窗体重写 dispose 以清理组件列表。
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub 'Windows 窗体设计器所必需的
Private components As System.ComponentModel.IContainer '注意: 以下过程是 Windows 窗体设计器所必需的
'可以使用 Windows 窗体设计器修改此过程。
'不要使用代码编辑器修改它。
Friend WithEvents btnExport As System.Windows.Forms.Button
Friend WithEvents SaveDl As System.Windows.Forms.SaveFileDialog
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.btnExport = New System.Windows.Forms.Button
Me.SaveDl = New System.Windows.Forms.SaveFileDialog
Me.SuspendLayout()
'
'btnExport
'
Me.btnExport.Location = New System.Drawing.Point(104, 128)
Me.btnExport.Name = "btnExport"
Me.btnExport.Size = New System.Drawing.Size(80, 32)
Me.btnExport.TabIndex = 0
Me.btnExport.Text = "Button1"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(6, 14)
Me.ClientSize = New System.Drawing.Size(292, 273)
Me.Controls.Add(Me.btnExport)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False) End Sub #End Region Private Function getdatafromdb(ByVal sqlstr As String) As dataset
Try
dataconnection = New OleDbConnection
dataconnection.ConnectionString = dbconnstr
dataadapter = New OleDbDataAdapter(sqlstr, dataconnection)
dataset = New DataSet
dataset.Clear()
dataadapter.Fill(dataset)
dataconnection.Close()
Catch ex As Exception
MessageBox.Show(Err.Description, "数据错误", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Exit Function
End Try
If dataset.Tables(0).Rows.Count > 0 Then
Return dataset
Else
Return Nothing
End If
End Function Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
Dim MyOleDbCn As New OleDbConnection
Dim MyOleDbCmd As New OleDbCommand
Dim intRowsCnt, intColsCnt As Integer
Dim strSql As String, strFlName As String
Dim Fso As New FileSystemObject dataset = getdatafromdb("select * from Employee_userinfo_main")
mytable = dataset1.Tables(0)
SaveDl.Title = "保持为"
SaveDl.Filter = "xls工作薄|*.xls" If SaveDl.ShowDialog = DialogResult.OK Then
If SaveDl.FileName <> "" Then
strFlName = SaveDl.FileName()
Else
Exit Sub
End If
Else
Exit Sub
End If Try
Me.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor MyOleDbCn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;" & _
"Data Source=" & strFlName & ";" & _
"Extended ProPerties=""Excel 8.0;HDR=Yes;"""
MyOleDbCn.Open()
MyOleDbCmd.Connection = MyOleDbCn
MyOleDbCmd.CommandType = CommandType.Text '第一行插入列标题
strSql = "CREATE TABLE sheet1("
For intColsCnt = 0 To MyTable.Columns.Count - 1
If intColsCnt <> MyTable.Columns.Count - 1 Then
strSql = strSql & MyTable.Columns(intColsCnt).Caption & " text,"
Else
strSql = strSql & MyTable.Columns(intColsCnt).Caption & " text)"
End If
Next
MyOleDbCmd.CommandText = strSql
MyOleDbCmd.ExecuteNonQuery() '插入各行
For intRowsCnt = 0 To MyTable.Rows.Count - 1
strSql = "INSERT INTO sheet1 VALUES('"
For intColsCnt = 0 To MyTable.Columns.Count - 1
If intColsCnt <> MyTable.Columns.Count - 1 Then
strSql = strSql & MyTable.Rows(intRowsCnt).Item(intColsCnt) & "','"
Else
strSql = strSql & MyTable.Rows(intRowsCnt).Item(intColsCnt) & "')"
End If
Next
MyOleDbCmd.CommandText = strSql
MyOleDbCmd.ExecuteNonQuery()
Next
MessageBox.Show("数据已经成功导入EXCEL文件" & strFlName, "数据导出", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ErrCode As Exception
MsgBox("错误信息：" & ErrCode.Message & vbCrLf & vbCrLf & _
"引发事件：" & ErrCode.TargetSite.ToString, MsgBoxStyle.OKOnly + MsgBoxStyle.Information, "错误来源：" & ErrCode.Source)
Exit Sub
Finally
MyOleDbCmd.Dispose()
MyOleDbCn.Close()
MyOleDbCn.Dispose()
Me.Cursor.Current = System.Windows.Forms.Cursors.Default
End Try
End Sub End Class 当然,将EXCEL导入到access,mssql等数据库,datagrid等容器也是用相反类似的方法,将EXCEL作为数据库来使用.
但是得注意相对应字段的长短得一致，曾经因为这个耽误了半天功夫
insert into test(id,FName)
select 编号，姓名 from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="d:Book1.xls";User ID=Administrator; Password=;Extended properties=Excel 8.0')...[sheet1$]
然后在C#中调用执行这个存储过程就可以了
SELECT * into 表
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="Excel文件";User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$] 建议先将Excel导入DataSet (网上很多资料，楼上也有说明)，再在DataTab中进行处理。比如：
System.Data.DataTable table=ds.Tables[0] ; //定义Table对象，
//使用嵌套迭代
foreach(DataRow row in table.Rows)
{
rowIndex++; //行索引
colIndex=0; //列索引
foreach(DataColumn col in table.Columns)
{
colIndex++;
－－－－－－－－－－－－
//执行插入操作
－－－－－－－－－－－－
}
}
我是想在程序中让用户选择一个Excel文件，然后导入到数据库
当然这个Excel文件必须和数据库中的一个表相对应
请问有何好的方法
如果用存储过程
具体的方法该如何写，谢谢
dim app as excel.application
app = new excel.application
app.visable = true
dim wb = excel.workbooks
'打开你的表格文件
wb = app.workbooks.open("c:\b.xls")
dim st as excel.worksheet
'st = wb.worksheets.add()
st = wb.worksheets(1)
'把A1中的值读到strTemp中去
dim strTemp as string
strTemp = st.cell(1,1).value
'释放相关内存
app.quite()
st = nothing
wb = nothing
app = nothing
(1)都说用dts实现简单,请楼上这种观点的朋友,推荐一下这方面的资料或者你的思路(好像会存在空记录的问题)!!
(2)用Oledb从excel读到dataset,再用sqlclient从dataset插入到sql数据库(我目前采用的方法,用事务处理会更好)
(3)ExecuteReader的同时,就insert到sql数据库需要考虑的问题:客户端的excel版本问题,excel表格式(第一行或者第二行是表头,字段名等),还要检查excel中的sheet格式和数据类型是否和sql数据库中已有的表兼容!!
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + "文件路径" + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbDataAdapter myCommand = null;
DataSet ds = null;
string strExcel = "select * from [" + "sheet名称"+ "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, sheetname);
conn.Close();
return ds;有了dataset,其他就好办了
private void ReadFromXL(string ExcelFilePath)
{
System.Data.Odbc.OdbcConnection ExcelConnection = new System.Data.Odbc.OdbcConnection();
System.Data.Odbc.OdbcDataReader ExcelDataReader;
System.Data.Odbc.OdbcCommand ExcelCommand = new System.Data.Odbc.OdbcCommand(); string strSQL; ExcelConnection.ConnectionString = @"Driver={Microsoft Excel Driver (*.xls)};DBQ=" + ExcelFilePath + ";ReadOnly=0;";
ExcelCommand.Connection = ExcelConnection;
ExcelCommand.CommandType = System.Data.CommandType.Text; //select data from the Customers sheet. Note that sheet name has to be in [] and suffixed with a $ sign
ExcelCommand.CommandText = "SELECT FirstName, LastName FROM [Customers$]"; ExcelConnection.Open();
ExcelDataReader = ExcelCommand.ExecuteReader();
//Loop thru the records and display the results in a text box
/*you could also change the code so that you can populate another database
see the comments below for this */
while (ExcelDataReader.Read())
{
textBox1.Text += ExcelDataReader.GetString(0) + " | " + ExcelDataReader.GetString(1) + System.Environment.NewLine;
//build SQL that can be used for inserting the records into another database
strSQL = "INSERT INTO myTable(FirstName, LastName) VALUES('" + ExcelDataReader.GetString(0) + "','" + ExcelDataReader.GetString(1) + "')";
/* The three lines below show the pseudo-code for inserting the records in a database
* SQLCommand and SQLConnection are variables that have to declared
* Connection string for SQLConnection will depend on the database
* Open SQLConnection before the entering the while loop and don't forget to Dispose it after the loop
*/
//SQLCommand.CommandText = strSQL;
//SQLCommand.Connection = SQLConnection;
//SQLCommand.ExecuteNonQuery(); textBox1.Text += strSQL + System.Environment.NewLine;
}
ExcelConnection.Dispose();
}
using System;
using System.Data;
using System.Data.OleDb; namespace utilities
{ /// <summary>
/// Summary description for ExcelReader.
/// </summary>
public class ExcelReader : IDisposable
{
#region Variables
private int[] _PKCol;
private string _strExcelFilename;
private bool _blnMixedData=true;
private bool _blnHeaders=false;
private string _strSheetName;
private string _strSheetRange;
private bool _blnKeepConnectionOpen=false;
private OleDbConnection _oleConn;
private OleDbCommand _oleCmdSelect;
private OleDbCommand _oleCmdUpdate;
#endregion #region properties public int[] PKCols
{
get {return _PKCol;}
set {_PKCol=value;}
} public string ColName(int intCol)
{
string sColName="";
if (intCol<26)
sColName= Convert.ToString(Convert.ToChar((Convert.ToByte((char) 'A')+intCol)) );
else
{
int intFirst = ((int) intCol / 26);
int intSecond = ((int) intCol % 26);
sColName= Convert.ToString(Convert.ToByte((char) 'A')+intFirst);
sColName += Convert.ToString(Convert.ToByte((char) 'A')+intSecond);
}
return sColName;
} public int ColNumber(string strCol)
{
strCol = strCol.ToUpper();
int intColNumber=0;
if (strCol.Length>1)
{
intColNumber = Convert.ToInt16(Convert.ToByte(strCol[1])-65);
intColNumber += Convert.ToInt16(Convert.ToByte(strCol[1])-64)*26;
}
else
intColNumber = Convert.ToInt16(Convert.ToByte(strCol[0])-65);
return intColNumber;
}
public String[] GetExcelSheetNames()
{
System.Data.DataTable dt = null; try
{
if (_oleConn ==null) Open();
// Get the data table containing the schema
dt = _oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null)
{
return null;
} String[] excelSheets = new String[dt.Rows.Count];
int i = 0; // Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
excelSheets[i] = strSheetTableName.Substring(0,strSheetTableName.Length-1);
i++;
}
return excelSheets;
}
catch(Exception)
{
return null;
}
finally
{
// Clean up.
if(this.KeepConnectionOpen==false)
{
this.Close();
}
if(dt != null)
{
dt.Dispose();
dt=null;
}
}
}
public string ExcelFilename
{
get { return _strExcelFilename;}
set { _strExcelFilename=value;}
} public string SheetName
{
get { return _strSheetName;}
set { _strSheetName=value;}
} public string SheetRange
{
get {return _strSheetRange;}
set
{
if (value.IndexOf(":")==-1) throw new Exception("Invalid range length");
_strSheetRange=value;}
}
public bool KeepConnectionOpen
{
get { return _blnKeepConnectionOpen;}
set {_blnKeepConnectionOpen=value;}
} public bool Headers
{
get { return _blnHeaders;}
set { _blnHeaders=value;}
} public bool MixedData
{
get {return _blnMixedData;}
set {_blnMixedData=value;}
}
#endregion #region Methods #region Excel Connection
private string ExcelConnectionOptions()
{
string strOpts="";
if (this.MixedData ==true)
strOpts += "Imex=2;";
if (this.Headers==true)
strOpts += "HDR=Yes;";
else
strOpts += "HDR=No;";
return strOpts;
}
private string ExcelConnection()
{
return
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=" + _strExcelFilename + ";" +
@"Extended Properties=" + Convert.ToChar(34).ToString() +
@"Excel 8.0;"+ ExcelConnectionOptions() + Convert.ToChar(34).ToString();
}
#endregion
#region Open / Close
public void Open()
{
try
{
if (_oleConn !=null)
{
if (_oleConn.State==ConnectionState.Open)
{
_oleConn.Close();
}
_oleConn=null;
} if (System.IO.File.Exists(_strExcelFilename)==false)
{
throw new Exception("Excel file " + _strExcelFilename + "could not be found.");
}
_oleConn = new OleDbConnection(ExcelConnection());
_oleConn.Open();
}
catch (Exception ex)
{
throw ex;
}
} public void Close()
{
if (_oleConn !=null)
{
if (_oleConn.State != ConnectionState.Closed)
_oleConn.Close();
_oleConn.Dispose();
_oleConn=null;
}
}
#endregion