Imports System.Data.OleDb Imports System.Data.SqlClientPublic Class UpLoadExcel Implements IDisposable Private _UpFile As HtmlInputFile Private _FilePath As String Private _ErrMassages As New System.Text.StringBuilder Private _DataBaseTableName As String Private _objTable As DataTable Private _Columns As Collection ' Private _dbConn As SqlClient.SqlConnection#Region "类初始化" Public Sub New(ByRef file As HtmlInputFile) _objTable = New DataTable _UpFile = file _Columns = New Collection LoadExcel() End Sub Overloads Sub Dispose() Implements IDisposable.Dispose Dispose(True) GC.SuppressFinalize(Me) End Sub Protected Overridable Overloads Sub Dispose(ByVal disposing As Boolean) _objTable = Nothing _Columns = Nothing If _FilePath <> "" Then Try System.IO.File.Delete(_FilePath) Catch ex As Exception End Try End If End Sub Protected Overrides Sub Finalize() Dispose() End Sub #End Region Public ReadOnly Property DataTable() Get Return _objTable End Get End Property Public Sub AddColumn(ByVal xlsColumnName As String, ByVal DBTableColumnName As String, ByVal DataType As System.Type, Optional ByVal isNull As Boolean = True) Dim column As UpColumn column = New UpColumn(xlsColumnName, DBTableColumnName, DataType, isNull) _Columns.Add(column, DBTableColumnName) End Sub Public Function GetErrMassages() Return _ErrMassages.ToString End Function#Region "读入Excel文件到DataTable" Private Function LoadExcel() If CheckFileType(_UpFile) Then SaveFileToServer() Else Throw New Exception("没有指定要上载的文件,或文件不是Excel文件") End If LoadExcelToDataTable() End Function Private Function CheckFileType(ByRef upFile As HtmlInputFile) As Boolean If (upFile.PostedFile Is Nothing) Then Return False End If Dim pos = upFile.PostedFile.FileName.LastIndexOf(".") If (pos < 0) Then Return False End If Dim strExtName As String = upFile.PostedFile.FileName.Substring(pos) If strExtName.ToLower <> ".xls" Then Return False End If Return True End Function Private Sub SaveFileToServer() Try Dim tempDir As String = System.Environment.GetEnvironmentVariable("Temp") & "\" Me._FilePath = tempDir & System.Guid.NewGuid.ToString() & ".xls" Me._UpFile.PostedFile.SaveAs(_FilePath) Catch ex As Exception Throw New Exception("上转文件到服务器出错:" & ex.Message) End Try End Sub Private Sub LoadExcelToDataTable() Dim connString As String Dim xlsSQL As String connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _FilePath & ";Extended Properties='Excel 8.0;'" xlsSQL = "SELECT * FROM [Sheet1$]" Dim xlsconn As New OleDbConnection(connString) Dim xlsCmd As New OleDbCommand(xlsSQL, xlsconn) Dim xlsAdapter As New OleDbDataAdapter(xlsCmd) Try xlsAdapter.Fill(Me._objTable) Catch ex As Exception Throw New Exception("读入Excel文件出错:" & ex.Message) End Try End Sub #End Region
#Region "保存数据到数据库" ''' <summary> ''' "保存数据到数据库" ''' </summary> Public Function Save(ByVal DBTableName As String, ByRef conn As SqlClient.SqlConnection) As Integer If (DBTableName.Trim Is String.Empty) OrElse (conn Is Nothing) Then Throw New Exception("数据库表名为空,或连接对像为Nothing!") End If If _Columns.Count = 0 Then Throw New Exception("没有加入字段列表!") End If Me._DataBaseTableName = DBTableName Dim exStatic As Integer Try conn.Open() exStatic = SaveToDataBase(conn) conn.Close() Catch ex As Exception Throw New Exception(ex.Message) End Try Return exStatic End Function Private Function SaveToDataBase(ByVal conn As SqlClient.SqlConnection) As Integer Dim xlsRow As DataRow Dim xlsColumn As DataColumn Dim RowNumber As Integer = 1 Dim rowError As Integer rowError = 0 Dim cmd As SqlCommand cmd = GetCommand() cmd.Connection = conn Dim tra As SqlClient.SqlTransaction tra = conn.BeginTransaction cmd.Transaction = tra For Each xlsRow In Me._objTable.Rows If CheckExcelColumnData(xlsRow, RowNumber) Then Try SaveToDataBase(xlsRow, cmd) Catch ex As Exception rowError += 1 _ErrMassages.Append("第 ").Append(RowNumber).Append(" 行保存出错:").Append(ex.Message) End Try Else rowError += 1 End If RowNumber += 1 Next If rowError = 0 Then tra.Commit() Else tra.Rollback() End If Return rowError End Function Private Function CheckExcelColumnData(ByRef xlsRow As DataRow, ByVal RowNumber As Integer) As Boolean Dim dbColumn As UpColumn Dim errNumber As Integer = 0 For Each dbColumn In Me._Columns Try If IsDBNull(xlsRow.Item(dbColumn.xlsColumnName)) And (Not dbColumn.IsNull) Then _ErrMassages.Append("第 ").Append(RowNumber).Append(" 行的 ").Append(dbColumn.xlsColumnName).Append(" 为空!").Append(vbCrLf) errNumber += 1 Else System.Convert.ChangeType(xlsRow.Item(dbColumn.xlsColumnName), dbColumn.DataType.GetTypeCode(dbColumn.DataType)) End If Catch ex As Exception errNumber += 1 _ErrMassages.Append("第 ").Append(RowNumber).Append(" 行的 ").Append(dbColumn.xlsColumnName).Append(" 为空,或数据类型不对!").Append(vbCrLf) End Try Next If errNumber > 0 Then Return False Else Return True End If End Function Private Function SaveToDatabase(ByVal row As DataRow, ByVal cmd As SqlClient.SqlCommand) As Integer Dim column As UpColumn Dim QueryRow As Integer = 0 For Each column In Me._Columns cmd.Parameters("@" & column.TableColumnName).value = row(column.xlsColumnName) Next cmd.ExecuteNonQuery() End Function Private Function GetCommand() As SqlCommand Dim column As UpColumn Dim Insert, ColumnName, Values, Paramerters As String Dim cmd As New SqlClient.SqlCommand Insert = "INSERT INTO " & _DataBaseTableName & " ( " Values = ") VALUES (" Dim InitValues As System.DBNull For Each column In Me._Columns ColumnName = ColumnName & "," & column.TableColumnName Paramerters = Paramerters & ",@" & column.TableColumnName cmd.Parameters.Add("@" & column.TableColumnName, InitValues) Next cmd.CommandText = Insert & subLeftString(ColumnName) & Values & subLeftString(Paramerters) & ")" Return cmd End Function Private Function subLeftString(ByRef str As String) Return str.Substring(1, str.Length - 1) End Function #End Region '====================== Private Class UpColumn '数据列类 Private _xlsColumnName As String Private _TableColumnName As String Private _DataType As System.Type Private _isNull As Boolean Public Sub New(ByVal xlsColumnName As String, ByVal DBTableColumnName As String, ByVal DataType As System.Type, Optional ByVal isNull As Boolean = True) If xlsColumnName.Trim = "" Then Throw New Exception("不能指定Excel中的列名为空") End If If DBTableColumnName.Trim = "" Then Throw New Exception("不能指定DBDataTable中的列名为空") End If If DataType Is Nothing Then Throw New Exception("指定的DBDataTable列的类型不能为空") End If _xlsColumnName = xlsColumnName _TableColumnName = DBTableColumnName _DataType = DataType _isNull = isNull End Sub Public ReadOnly Property xlsColumnName() Get Return _xlsColumnName End Get End Property Public ReadOnly Property TableColumnName() Get Return _TableColumnName End Get End Property Public Function DataType() As System.Type Return _DataType End Function Public ReadOnly Property IsNull() Get Return _isNull End Get End Property End Class End Class
using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls;namespace WebTest1 { /// <summary> /// WebForm1 的摘要说明。 /// </summary> public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.Button button1; protected System.Web.UI.WebControls.DataGrid datagrid1;
private void Page_Load(object sender, System.EventArgs e) { // 在此处放置用户代码以初始化页面 } #region Web 窗体设计器生成的代码 override protected void OnInit(EventArgs e) { // // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。 // InitializeComponent(); base.OnInit(e); }
/// <summary> /// 设计器支持所需的方法 - 不要使用代码编辑器修改 /// 此方法的内容。 /// </summary> private void InitializeComponent() { this.button1.Click += new System.EventHandler(this.button1_Click); this.Load += new System.EventHandler(this.Page_Load); } #endregion private void button1_Click(object sender, System.EventArgs e) { ExelInputDataClass EIDC=new ExelInputDataClass(); datagrid1.DataSource=EIDC.returnDataToDataView(); datagrid1.DataBind(); //Response.Write("<script>window.alter('OK');</script>"); } } }类定义 using System; using System.Data; using System.Data.SqlClient; using System.Collections; using System.Configuration;namespace WebTest1 { /// <summary> /// /// </summary> public class ExelInputDataClass {
不能用,我是给别人写程序(ASP.NET C#),让别人用的,别人不懂SQLServer的.我只需要让他们操作即可
{
string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'D:/ExportToExcel/excel/test.xls';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection (mystring);
OleDbDataAdapter myDa =new OleDbDataAdapter("select * from [Sheet1$]",cnnxls);
DataSet myDs =new DataSet();
myDa.Fill(myDs);
if(myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
string CnnString="Provider=SQLOLEDB;database=testnews;server=(local);uid=sa;pwd=";
OleDbConnection conn =new OleDbConnection(CnnString);
conn.Open ();
OleDbCommand myCmd =null;
for(int i=0; i<myDs.Tables[0].Rows.Count; i++)
{
strSql="insert into news(title,body) values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "', '";
strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "')";
try
{
myCmd=new OleDbCommand(strSql,conn);
myCmd.ExecuteNonQuery();
Label8.Text = "<script language=javascript>alert('数据导入成功.');</script>";
}
catch
{
Label8.Text = "<script language=javascript>alert('数据导入失败.');</script>";
}
}
conn.Close();
} }
}
{
string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = 'D:/ExportToExcel/excel/test.xls';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection (mystring);
OleDbDataAdapter myDa =new OleDbDataAdapter("select * from [Sheet1$]",cnnxls);
DataSet myDs =new DataSet();
myDa.Fill(myDs);
if(myDs.Tables[0].Rows.Count > 0)
{
string strSql = "";
string CnnString="Provider=SQLOLEDB;database=testnews;server=(local);uid=sa;pwd=";
OleDbConnection conn =new OleDbConnection(CnnString);
conn.Open ();
OleDbCommand myCmd =null;
for(int i=0; i<myDs.Tables[0].Rows.Count; i++)
{
strSql="insert into news(title,body) values ('";
strSql += myDs.Tables[0].Rows[i].ItemArray[1].ToString() + "', '";
strSql += myDs.Tables[0].Rows[i].ItemArray[2].ToString() + "')";
try
{
myCmd=new OleDbCommand(strSql,conn);
myCmd.ExecuteNonQuery();
Label8.Text = "<script language=javascript>alert('数据导入成功.');</script>";
}
catch
{
Label8.Text = "<script language=javascript>alert('数据导入失败.');</script>";
}
}
conn.Close();
} }
}
{
this.SetStyle("Table");
string file="";
openFileDialog1.Filter = "Excel数据文件|*.xls";
openFileDialog1.RestoreDirectory = true;
if(this.openFileDialog1.ShowDialog()==DialogResult.OK)
{
file=this.openFileDialog1.FileName;
//
string conStr=@" Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+file+";Password=;User ID=Admin;Extended Properties=Excel 8.0" ; string cmdText="select * from [Sheet1$]"; OleDbDataAdapter a=new OleDbDataAdapter(cmdText,conStr); DataSet ds=new DataSet(); a.Fill(ds);
this.data=ds.Tables[0];
//string cols="学号,姓名,班级名称,院系名称";
this.dataGrid1.DataSource=this.data;
//
this.label1.Visible=true;
this.label2.Text="共有"+this.data.Rows.Count.ToString()+"条记录;";
}
}
Imports System.Data.SqlClientPublic Class UpLoadExcel
Implements IDisposable Private _UpFile As HtmlInputFile
Private _FilePath As String
Private _ErrMassages As New System.Text.StringBuilder
Private _DataBaseTableName As String
Private _objTable As DataTable
Private _Columns As Collection
' Private _dbConn As SqlClient.SqlConnection#Region "类初始化"
Public Sub New(ByRef file As HtmlInputFile)
_objTable = New DataTable
_UpFile = file
_Columns = New Collection
LoadExcel()
End Sub Overloads Sub Dispose() Implements IDisposable.Dispose
Dispose(True)
GC.SuppressFinalize(Me)
End Sub Protected Overridable Overloads Sub Dispose(ByVal disposing As Boolean)
_objTable = Nothing
_Columns = Nothing
If _FilePath <> "" Then
Try
System.IO.File.Delete(_FilePath)
Catch ex As Exception
End Try
End If
End Sub Protected Overrides Sub Finalize()
Dispose()
End Sub
#End Region Public ReadOnly Property DataTable()
Get
Return _objTable
End Get
End Property Public Sub AddColumn(ByVal xlsColumnName As String, ByVal DBTableColumnName As String, ByVal DataType As System.Type, Optional ByVal isNull As Boolean = True)
Dim column As UpColumn
column = New UpColumn(xlsColumnName, DBTableColumnName, DataType, isNull)
_Columns.Add(column, DBTableColumnName)
End Sub Public Function GetErrMassages()
Return _ErrMassages.ToString
End Function#Region "读入Excel文件到DataTable"
Private Function LoadExcel()
If CheckFileType(_UpFile) Then
SaveFileToServer()
Else
Throw New Exception("没有指定要上载的文件,或文件不是Excel文件")
End If
LoadExcelToDataTable()
End Function Private Function CheckFileType(ByRef upFile As HtmlInputFile) As Boolean
If (upFile.PostedFile Is Nothing) Then
Return False
End If
Dim pos = upFile.PostedFile.FileName.LastIndexOf(".") If (pos < 0) Then
Return False
End If
Dim strExtName As String = upFile.PostedFile.FileName.Substring(pos)
If strExtName.ToLower <> ".xls" Then
Return False
End If
Return True
End Function Private Sub SaveFileToServer()
Try
Dim tempDir As String = System.Environment.GetEnvironmentVariable("Temp") & "\"
Me._FilePath = tempDir & System.Guid.NewGuid.ToString() & ".xls"
Me._UpFile.PostedFile.SaveAs(_FilePath)
Catch ex As Exception
Throw New Exception("上转文件到服务器出错:" & ex.Message)
End Try
End Sub Private Sub LoadExcelToDataTable()
Dim connString As String
Dim xlsSQL As String
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _FilePath & ";Extended Properties='Excel 8.0;'"
xlsSQL = "SELECT * FROM [Sheet1$]"
Dim xlsconn As New OleDbConnection(connString)
Dim xlsCmd As New OleDbCommand(xlsSQL, xlsconn)
Dim xlsAdapter As New OleDbDataAdapter(xlsCmd)
Try
xlsAdapter.Fill(Me._objTable)
Catch ex As Exception
Throw New Exception("读入Excel文件出错:" & ex.Message)
End Try
End Sub
#End Region
''' <summary>
''' "保存数据到数据库"
''' </summary>
Public Function Save(ByVal DBTableName As String, ByRef conn As SqlClient.SqlConnection) As Integer If (DBTableName.Trim Is String.Empty) OrElse (conn Is Nothing) Then
Throw New Exception("数据库表名为空,或连接对像为Nothing!")
End If
If _Columns.Count = 0 Then
Throw New Exception("没有加入字段列表!")
End If Me._DataBaseTableName = DBTableName
Dim exStatic As Integer
Try
conn.Open()
exStatic = SaveToDataBase(conn)
conn.Close()
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
Return exStatic
End Function Private Function SaveToDataBase(ByVal conn As SqlClient.SqlConnection) As Integer
Dim xlsRow As DataRow
Dim xlsColumn As DataColumn
Dim RowNumber As Integer = 1
Dim rowError As Integer
rowError = 0
Dim cmd As SqlCommand
cmd = GetCommand()
cmd.Connection = conn
Dim tra As SqlClient.SqlTransaction
tra = conn.BeginTransaction
cmd.Transaction = tra
For Each xlsRow In Me._objTable.Rows
If CheckExcelColumnData(xlsRow, RowNumber) Then
Try
SaveToDataBase(xlsRow, cmd)
Catch ex As Exception
rowError += 1
_ErrMassages.Append("第 ").Append(RowNumber).Append(" 行保存出错:").Append(ex.Message)
End Try
Else
rowError += 1
End If
RowNumber += 1
Next
If rowError = 0 Then
tra.Commit()
Else
tra.Rollback()
End If
Return rowError
End Function Private Function CheckExcelColumnData(ByRef xlsRow As DataRow, ByVal RowNumber As Integer) As Boolean
Dim dbColumn As UpColumn
Dim errNumber As Integer = 0
For Each dbColumn In Me._Columns
Try
If IsDBNull(xlsRow.Item(dbColumn.xlsColumnName)) And (Not dbColumn.IsNull) Then
_ErrMassages.Append("第 ").Append(RowNumber).Append(" 行的 ").Append(dbColumn.xlsColumnName).Append(" 为空!").Append(vbCrLf)
errNumber += 1
Else
System.Convert.ChangeType(xlsRow.Item(dbColumn.xlsColumnName), dbColumn.DataType.GetTypeCode(dbColumn.DataType))
End If
Catch ex As Exception
errNumber += 1
_ErrMassages.Append("第 ").Append(RowNumber).Append(" 行的 ").Append(dbColumn.xlsColumnName).Append(" 为空,或数据类型不对!").Append(vbCrLf)
End Try
Next
If errNumber > 0 Then
Return False
Else
Return True
End If
End Function Private Function SaveToDatabase(ByVal row As DataRow, ByVal cmd As SqlClient.SqlCommand) As Integer
Dim column As UpColumn
Dim QueryRow As Integer = 0
For Each column In Me._Columns
cmd.Parameters("@" & column.TableColumnName).value = row(column.xlsColumnName)
Next
cmd.ExecuteNonQuery()
End Function Private Function GetCommand() As SqlCommand
Dim column As UpColumn
Dim Insert, ColumnName, Values, Paramerters As String
Dim cmd As New SqlClient.SqlCommand
Insert = "INSERT INTO " & _DataBaseTableName & " ( "
Values = ") VALUES ("
Dim InitValues As System.DBNull
For Each column In Me._Columns
ColumnName = ColumnName & "," & column.TableColumnName
Paramerters = Paramerters & ",@" & column.TableColumnName
cmd.Parameters.Add("@" & column.TableColumnName, InitValues)
Next
cmd.CommandText = Insert & subLeftString(ColumnName) & Values & subLeftString(Paramerters) & ")"
Return cmd
End Function
Private Function subLeftString(ByRef str As String)
Return str.Substring(1, str.Length - 1)
End Function
#End Region
'======================
Private Class UpColumn '数据列类
Private _xlsColumnName As String
Private _TableColumnName As String
Private _DataType As System.Type
Private _isNull As Boolean
Public Sub New(ByVal xlsColumnName As String, ByVal DBTableColumnName As String, ByVal DataType As System.Type, Optional ByVal isNull As Boolean = True)
If xlsColumnName.Trim = "" Then
Throw New Exception("不能指定Excel中的列名为空")
End If
If DBTableColumnName.Trim = "" Then
Throw New Exception("不能指定DBDataTable中的列名为空")
End If
If DataType Is Nothing Then
Throw New Exception("指定的DBDataTable列的类型不能为空")
End If
_xlsColumnName = xlsColumnName
_TableColumnName = DBTableColumnName
_DataType = DataType
_isNull = isNull
End Sub
Public ReadOnly Property xlsColumnName()
Get
Return _xlsColumnName
End Get
End Property
Public ReadOnly Property TableColumnName()
Get
Return _TableColumnName
End Get
End Property
Public Function DataType() As System.Type
Return _DataType
End Function
Public ReadOnly Property IsNull()
Get
Return _isNull
End Get
End Property
End Class
End Class
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;namespace WebTest1
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button button1;
protected System.Web.UI.WebControls.DataGrid datagrid1;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
} #region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.button1.Click += new System.EventHandler(this.button1_Click);
this.Load += new System.EventHandler(this.Page_Load); }
#endregion private void button1_Click(object sender, System.EventArgs e)
{
ExelInputDataClass EIDC=new ExelInputDataClass();
datagrid1.DataSource=EIDC.returnDataToDataView();
datagrid1.DataBind();
//Response.Write("<script>window.alter('OK');</script>");
}
}
}类定义
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;namespace WebTest1
{
/// <summary>
///
/// </summary>
public class ExelInputDataClass
{
public ExelInputDataClass()
{
}
public ICollection returnDataToDataView()
{
try
{
string str_Conn=ConfigurationSettings.AppSettings["strConSqlserver"];
SqlConnection objConn=new SqlConnection(str_Conn);
SqlCommand objComm=new SqlCommand();
objComm.Connection=objConn;
objComm.CommandType=CommandType.StoredProcedure;
objComm.CommandText="procedureTest1";
SqlDataAdapter objAdapter=new SqlDataAdapter(objComm);
DataSet ds=new DataSet();
objAdapter.Fill(ds,"ExcelData");
DataView dv=ds.Tables["ExcelData"].DefaultView;
return dv;
}
catch
{
return null;
}
}
}
}