How do I import from Excel to a DataSet using Microsoft.Office.Interop.Excel?(如何使用 Microsoft.Office.Interop.Excel 从 Excel 导入数据集?)
问题描述
What I want to do
I'm trying to use the Microsoft.Office.Interop.Excel namespace to open an Excel file (XSL or CSV, but sadly not XSLX) and import it into a DataSet. I don't have control over the worksheet or column names, so I need to allow for changes to them.
What I've tried
I've tried the OLEDB method of this in the past, and had a lot of problems with it (buggy, slow, and required prior knowledge of the Excel file's schema), so I want to avoid doing that again. What I'd like to do is use Microsoft.Office.Interop.Excel to import the workbook directly to a DataSet, or loop through the worksheets and load each one into a DataTable.
Believe it or not, I've had trouble finding resources for this. A few searches on StackOverflow have found mostly people trying to do the reverse (DataSet => Excel), or the OLEDB technique. Google hasn't been much more helpful.
What I've got so far
I'm fine with either importing the entire book at once, or looping through one sheet at a time. Can I do this with Interop.Excel?
What about using Excel Data Reader (previously hosted here) an open source project on codeplex? Its works really well for me to export data from excel sheets.
The sample code given on the link specified:
UPDATE
After some search around, I came across this article: Faster MS Excel Reading using Office Interop Assemblies. The article only uses Office Interop Assemblies
to read data from a given Excel Sheet. The source code is of the project is there too. I guess this article can be a starting point on what you trying to achieve. See if that helps
UPDATE 2
The code below takes an excel workbook
and reads all values found, for each excel worksheet
inside the excel workbook
.
In the above code, values[i, j]
is the value that you need to be added to the dataset
. i
denotes the row, whereas, j
denotes the column.
这篇关于如何使用 Microsoft.Office.Interop.Excel 从 Excel 导入数据集?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!