如何将Excel工作表保存到SQL数据库或数据集中 [英] How to save the excel sheet into the sql database or dataset
问题描述
我正在使用vb.net作为开发语言，并使用sql2005作为后端.
我必须在我的应用程序中提供来自excel的导入工具.
Excel工作表中有大量数据需要复制到数据库中
我使用过sqlbulkcopy，但遇到了问题.
我也曾使用OLedbConnection直接连接到excel工作表，但也遇到了问题.
现在我正在使用第三方ExcelReader
我的代码是:
Hi,
I am using vb.net as the development language and sql2005 as backend.
I have to provide import facility in my application from excel.
There is loads of data in the excel sheet which need to be copied in the database
I have used sqlbulkcopy but had problems with it.
I have also used the direct connection to excel sheet using OLedbConnection but had problems with it too.
now I am using the third party ExcelReader
my code is:
Dim sSheet As String = [" + SheetCombo.Text + "$];
Dim stream1 As FileStream = File.Open(Filename, FileMode.Open, FileAccess.Read, FileShare.None)
Dim excelReader As IExcelDataReader
If stream1.Name.EndsWith(".xlsx") = True Then
'' Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream1)
ElseIf stream1.Name.EndsWith(".xls") = True Then
'' Reading from a binary Excel file (''97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream1)
Else
excelReader = Nothing
MsgBox("Please Select a .xlsx or .xls file")
End If
excelReader.IsFirstRowAsColumnNames = True
Dim result As DataSet = excelReader.AsDataSet()
Me.DataGridView1.DataSource = result.Tables(0)
我正在从作为组合框的SheetCombo中选择工作表名称.
I am selecting the sheetnames from a SheetCombo which is a combobox.
Problem:Import was done but the date has 5 digits
有谁可以帮忙吗?
我按照_beauw_的指示 开始进行CSV转换 ，然后继续前进，我猜是朝着正确的方向前进，但现在 我正面临另一个问题
我保存了csv文件，它具有实际excel工作表的所有条目，但是当我使用以下语句时
Can any one help please.
I started with CSV conversion as _beauw_ told and went forward I guess in the right direction but now I am facing another problem
I saved the csv file and it had all the entries of the actual excel sheet but when I am using the below statement
Dim objCmdSelect As New OleDbCommand("SELECT * FROM tempcsformat.csv", objConn)
Dim objAdapter1 As New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataset1, "test")
objConn.Close()
我的csv文件中的日期字段遇到问题.
实际上，日期字段具有混合的日期格式，主要是dd/MM/yyyy和MM/dd/yyyy.
我想在整个日期字段中使用通用的dd/MM/yyyy格式.
我可以将它们读为String，但应该有其他解决方案.
I am facing problems with the date fields in my csv file.
Actually the date fields have mixed date format means dd/MM/yyyy and MM/dd/yyyy mainly.
I want to have a common dd/MM/yyyy format to entire date field.
I can read them as String but there should be some other solution for it.
If I use the above code to save the csv file records in database the dates which are not of currentculture means the current datetime format of the system are not considered as valid entries and all those dates that are not of current culture are not saved in the database..
非常感谢bluesatish的帮助和关注.
我看到了可以与csv文件一起使用的格式功能，但是我不确定要在哪里使用它，这意味着我已经在csv文件的select语句中使用了它.
Thanks alot bluesatish for your help and interest.
I saw this format function that can be used with csv file but I am not quiet sure where to use it,means I have used it in my select Statement of the csv file.
Dim objCmdSelect As New OleDbCommand("SELECT CId,SrNo,Format(Date,dd/MM/yyyy),Name," & aftnam & ",Format(DOB,dd/MM/yyyy),Age," & aftdob & " FROM tempcsformat.csv", objConn)
Dim objAdapter1 As New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataset1, "test")
objConn.Close()
但是我收到的错误值没有传递给一个或多个参数.
是否有人对我要去哪里有任何建议.
but I am getting an error value not passed for one or more parameter''s.
Do anyone have any suggestions about where I am going wrong.
推荐答案
; Dim stream1 as FileStream = File.Open(Filename，FileMode.Open，FileAccess.Read，FileShare.None) 将excelReader昏暗为IExcelDataReader 如果stream1.Name.EndsWith(.xlsx")= True，则 ''从OpenXml Excel文件读取(2007格式; * .xlsx) excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream1) ElseIf stream1.Name.EndsWith(.xls")= True然后 ''从二进制Excel文件中读取(''97 -2003格式; * .xls) excelReader = ExcelReaderFactory.CreateBinaryReader(stream1) 别的 excelReader =没什么 MsgBox(请选择一个.xlsx或.xls文件") 万一 excelReader.IsFirstRowAsColumnNames = True 昏暗的结果为DataSet = excelReader.AsDataSet() Me.DataGridView1.DataSource = result.Tables(0)
; Dim stream1 As FileStream = File.Open(Filename, FileMode.Open, FileAccess.Read, FileShare.None) Dim excelReader As IExcelDataReader If stream1.Name.EndsWith(".xlsx") = True Then '' Reading from a OpenXml Excel file (2007 format; *.xlsx) excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream1) ElseIf stream1.Name.EndsWith(".xls") = True Then '' Reading from a binary Excel file (''97-2003 format; *.xls) excelReader = ExcelReaderFactory.CreateBinaryReader(stream1) Else excelReader = Nothing MsgBox("Please Select a .xlsx or .xls file") End If excelReader.IsFirstRowAsColumnNames = True Dim result As DataSet = excelReader.AsDataSet() Me.DataGridView1.DataSource = result.Tables(0)
我正在从作为组合框的SheetCombo中选择工作表名称.
I am selecting the sheetnames from a SheetCombo which is a combobox.
Problem:Import was done but the date has 5 digits
有谁可以帮忙吗?
我按照_beauw_的指示 开始进行CSV转换 ，然后继续前进，我猜是朝着正确的方向前进，但现在 我正面临另一个问题
我保存了csv文件，它具有实际excel工作表的所有条目，但是当我使用以下语句时
Can any one help please.
I started with CSV conversion as _beauw_ told and went forward I guess in the right direction but now I am facing another problem
I saved the csv file and it had all the entries of the actual excel sheet but when I am using the below statement
Dim objCmdSelect As New OleDbCommand("SELECT * FROM tempcsformat.csv", objConn)
Dim objAdapter1 As New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataset1, "test")
objConn.Close()
我的csv文件中的日期字段遇到问题.
实际上，日期字段具有混合的日期格式，主要是dd/MM/yyyy和MM/dd/yyyy.
我想在整个日期字段中使用通用的dd/MM/yyyy格式.
我可以将它们读为String，但应该有其他解决方案.
I am facing problems with the date fields in my csv file.
Actually the date fields have mixed date format means dd/MM/yyyy and MM/dd/yyyy mainly.
I want to have a common dd/MM/yyyy format to entire date field.
I can read them as String but there should be some other solution for it.
If I use the above code to save the csv file records in database the dates which are not of currentculture means the current datetime format of the system are not considered as valid entries and all those dates that are not of current culture are not saved in the database..
非常感谢bluesatish的帮助和关注.
我看到了可以与csv文件一起使用的格式功能，但是我不确定要在哪里使用它，这意味着我已经在csv文件的select语句中使用了它.
Thanks alot bluesatish for your help and interest.
I saw this format function that can be used with csv file but I am not quiet sure where to use it,means I have used it in my select Statement of the csv file.
Dim objCmdSelect As New OleDbCommand("SELECT CId,SrNo,Format(Date,dd/MM/yyyy),Name," & aftnam & ",Format(DOB,dd/MM/yyyy),Age," & aftdob & " FROM tempcsformat.csv", objConn)
Dim objAdapter1 As New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataset1, "test")
objConn.Close()
但是我收到的错误值没有传递给一个或多个参数.
有人对我要去哪里有什么建议.
but I am getting an error value not passed for one or more parameter''s.
Do anyone have any suggestions about where I am going wrong.
http://codehill.com/2009/01/reading-excel-2003-and-2007-files-using-oledb /
看看.
http://codehill.com/2009/01/reading-excel-2003-and-2007-files-using-oledb/
have a look .
vinay，
使用SqlBulkCopy批量插入SQL Server
最近，我受雇于一家公司的一个项目，负责每天更新包含大量数据的SQL Server 2008数据库.起初，这项任务令人望而生畏，因为文件超过了400,000条记录，而且每天有几条记录需要处理.我首先尝试了LINQ to SQL，但由于数据量大，至少可以说插入慢.然后我想起了SqlBulkCopy类. SqlBulkCopy使您可以有效地将来自其他来源的数据批量加载到SQL Server表中. SqlBulkCopy类可用于仅将数据写入SQL Server表.但是，数据源不限于SQL Server.只要可以将数据加载到DataTable实例或使用IDataReader实例读取，就可以使用任何数据源.对于此示例，该文件将包含大约1000条记录，但是此代码可以处理大量数据.
首先，让我们在SQL Server中创建一个表来保存数据.将以下T-SQL复制到SQL Server中以创建表:
Hi vinay,
Bulk Insert into SQL Server using SqlBulkCopy
I was recently tasked with a project at a company to update an SQL Server 2008 database with large amounts of data each day. The task at first seemed daunting due to the files exceeding well over 400,000 records and there were several that needed processing daily. I first tried LINQ to SQL, but with the amount of data, the inserts were slow performing to say the least. Then I remembered the SqlBulkCopy class. SqlBulkCopy lets you efficiently bulk load a SQL Server table with data from another source. The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance. For this example the file will contain roughly 1000 records, but this code can handle large amounts of data.
To begin with let’s create a table in SQL Server that will hold the data. Copy the following T-SQL into SQL Server to create your table:
CREATE TABLE [dbo].[Censis](
[Suburb] [varchar](200) NULL,
[NotStated] [int] NULL,
[NotApplicable] [int] NULL,
[Fishing] [int] NULL,
[Mining] [int] NULL,
[Manufacturing] [int] NULL,
[Electricity] [int] NULL,
[Construction] [int] NULL
) ON [PRIMARY]
GO
上表将保存可在澳大利亚免费下载的Censis数据.
下一步要做的是创建一个控制台应用程序，该程序将批量加载数据.打开Visual Studio 2008，然后选择文件>新> Windows>控制台应用程序.
在继续之前，为了解释代码，我必须向后工作，并解释批量加载数据的最终方法. SqlBulkCopy有一个称为WriteToServer的方法.此方法的重载之一采用DataTable作为参数.因为DataTable包含行和列，所以这似乎是我面临的任务的合理选择.
回到示例，我们现在知道我们需要创建一个DataTable，其中包含来自文本文件的信息.以下代码演示了如何执行此操作:
The table above will hold Censis data that is freely available to download in Australia.
The next item to do is create a console application that will bulk load the data. Open Visual Studio 2008 and choose File > New > Windows > Console Application.
Before moving on, to explain the code I have to work backwards and explain the final method that bulk loads data. SqlBulkCopy has a method called WriteToServer. One of the overloads of this method takes a DataTable as the parameter. Because a DataTable contains rows and columns, this seemed like a logical choice for the task I was facing.
Jumping back to the example we now know we need to create a DataTable that contains the information from the text file. The code below demonstrates how to do this:
C#
DataTable dt = new DataTable();
string line = null;
int i = 0;
using (StreamReader sr = File.OpenText(@"c:\temp\table1.csv"))
{
while ((line = sr.ReadLine()) != null)
{
string[] data = line.Split(',');
if (data.Length > 0)
{
if (i == 0)
{
foreach (var item in data)
{
dt.Columns.Add(new DataColumn());
}
i++;
}
DataRow row = dt.NewRow();
row.ItemArray = data;
dt.Rows.Add(row);
}
}
}
VB.NET
Dim dt As New DataTable()
Dim line As String = Nothing
Dim i As Integer = 0
Using sr As StreamReader = File.OpenText("c:\temp\table1.csv")
line = sr.ReadLine()
Do While line IsNot Nothing
Dim data() As String = line.Split(","c)
If data.Length > 0 Then
If i = 0 Then
For Each item In data
dt.Columns.Add(New DataColumn())
Next item
i += 1
End If
Dim row As DataRow = dt.NewRow()
row.ItemArray = data
dt.Rows.Add(row)
End If
line = sr.ReadLine()
Loop
End Using
在上面的代码中，我创建了一个DataTable，它将存储来自csv文件的所有信息. csv文件位于C:\ Temp目录中.我正在使用StreamReader对象打开文件并读取文件中的每一行.然后将每一行拆分为一个字符串数组.该字符串数组将作为ItemArray值分配给每个DataRow.这将设置通过数组的行的值.
读取文件后，下一步是使用SqlBulkCopy类将数据插入SQL Server.以下代码演示了如何执行此操作:
In the code above, I created a DataTable that will store all the information from the csv file. The csv file resides in the C:\Temp directory. I am using a StreamReader object to open the file and read each line in the file. Each line is then split up into a string array. That string array will be assigned to each DataRow as the ItemArray value. This sets the values for the row through the array.
When the file has been read, the next thing to do is use the SqlBulkCopy class to insert the data into SQL Server. The following code demonstrates how to do this:
C#
using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConsoleApplication3.Properties.Settings.daasConnectionString"].ConnectionString))
{
cn.Open();
using (SqlBulkCopy copy = new SqlBulkCopy(cn))
{
copy.ColumnMappings.Add(0, 0);
copy.ColumnMappings.Add(1, 1);
copy.ColumnMappings.Add(2, 2);
copy.ColumnMappings.Add(3, 3);
copy.ColumnMappings.Add(4, 4);
copy.DestinationTableName = "Censis";
copy.WriteToServer(dt);
}
}
VB.NET
Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConsoleApplication3.Properties.Settings.daasConnectionString").ConnectionString)
cn.Open()
Using copy As New SqlBulkCopy(cn)
copy.ColumnMappings.Add(0, 0)
copy.ColumnMappings.Add(1, 1)
copy.ColumnMappings.Add(2, 2)
copy.ColumnMappings.Add(3, 3)
copy.ColumnMappings.Add(4, 4)
copy.DestinationTableName = "Censis"
copy.WriteToServer(dt)
End Using
End Using
SqlBulkCopy使用ADO.NET连接到数据库以批量加载数据.我创建了一个SqlConnection对象，该对象引用用于创建SqlBulkCopy对象. DestinationTableName属性引用数据库中要在其中加载数据的表. SqlBulkCopy的一个方便功能是SqlBulkCopyColumnMappingCollection.列映射定义数据源中的列与目标中的列之间的关系.如果数据源文件中的列不需要插入数据库中，则非常方便.可以通过索引来设置列映射，例如上面的示例，或者可以通过列的名称来设置列映射.当您使用不包含列名的文件时，使用索引非常方便.确保您的datatable和sqltable列都应使用相同的顺序.最后，通过运行WriteToServer方法将数据发送到数据库.
我建议您使用sqibulkcopy()方法来执行此操作，这比任何其他插入方法都非常快.
问候，
忧郁的
SqlBulkCopy uses ADO.NET to connect to a database to bulk load the data. I have created an SqlConnection object, and that object reference is used to create the SqlBulkCopy object. The DestinationTableName property references a table in the database where the data is to be loaded. A handy feature of SqlBulkCopy is the SqlBulkCopyColumnMappingCollection. Column mappings define the relationships between columns in the data source and columns in the destination. This is handy if the data source file has columns that don’t need to be inserted into the database. Column mappings can be set by an index, such as the example above, or they can be set by the name of the column. Using the index is handy when you’re working with files that contain no column names. Make sure both of your datatable and sqltable columns should be in a same order. Finally the data is sent to the database by running the WriteToServer method.
I suggest you to use sqibulkcopy() method to do this, its a very fastest than anyother insertion method.
Regards,
Bluesathish
这篇关于如何将Excel工作表保存到SQL数据库或数据集中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
