如何使用Microsoft.Office.Interop.Excel从Excel导入到DataSet？ [英] How do I import from Excel to a DataSet using Microsoft.Office.Interop.Excel?
问题描述
我想做什么
我试图使用 Microsoft.Office.Interop.Excel 命名空间以打开Excel文件（XSL或CSV，但可悲的是不是 XSLX），并将其导入到DataSet中。我没有控制工作表或列名称，所以我需要允许更改它们。
我尝试过的
我尝试过 OLEDB方法，并且有很多问题（错误，缓慢，并且需要Excel文件的架构的先前知识），所以我想避免再次这样做。我想做的是使用Microsoft.Office.Interop.Excel将工作簿直接导入到DataSet中，或循环遍历工作表，并将其加载到DataTable中。
相信与否，我找不到资源。 StackOverflow上的几个搜索已经发现大多数人试图反向（DataSet => Excel）或OLEDB技术。 Google没有太多的帮助。
我到目前为止
public void Load（string filename，Excel.XlFileFormat format = Excel.XlFileFormat.xlCSV）
{
app = new Excel.Application（）;
book = app.Workbooks.Open（文件名：filename，格式：格式）;
DataSet ds = new DataSet（）;
foreach（Excel.Worksheet sheet in book.Sheets）
{
DataTable dt = new DataTable（sheet.Name）;
ds.Tables.Add（dt）;
// ???从表格中填充dt
this.Data = ds;
}
我可以一次导入整本书，或循环一张一张。我可以用Interop.Excel吗？
使用 Excel数据读取器（以前托管的 here ）打开源代码项目它的作品非常适合我从Excel表格导出数据。
指定链接上给出的示例代码：
FileStream流= File.Open（filePath，FileMode.Open，FileAccess.Read）;
// 1。从二进制Excel文件（'97 -2003格式; * .xls）读取
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader（stream）;
// ...
// 2。从OpenXml Excel文件读取（2007格式; * .xlsx）
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader（stream）;
// ...
// 3。 DataSet - 每个电子表格的结果将在结果中创建。表$ $ $ $ $ $ $ $ $ DataSet result = excelReader.AsDataSet（）;
// ...
// 4。 DataSet - 从第一行创建列名
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet（）;
// 5。数据读取器方法
while（excelReader.Read（））
{
//excelReader.GetInt32(0）;
}
// 6。免费资源（IExcelDataReader是IDisposable）
excelReader.Close（）;
更新
经过一番搜索，我发现了这篇文章：更快的MS Excel阅读使用Office Interop组件。该文章仅使用 Office Interop Assemblies 从指定的Excel工作表中读取数据。项目的源代码也在那里。我猜这个文章可以成为你想要实现的一个起点。看看是否有帮助
更新2
下面的代码需要一个 excel工作簿并读取 excel工作簿内的每个 excel工作表的所有找到的值$ c>
private static void TestExcel（）
{
ApplicationClass app = new ApplicationClass（） ;
工作簿book = null;
范围范围= null;
try
{
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
string execPath = Path.GetDirectoryName（Assembly.GetExecutingAssembly（）。CodeBase）;
book = app.Workbooks.Open（@C：\data.xls，Missing.Value，Missing.Value，Missing.Value
，Missing.Value，Missing.Value ，Missing.Value，Missing.Value
，Missing.Value，Missing.Value，Missing.Value，Missing.Value
，Missing.Value，Missing.Value，Missing.Value）;
foreach（book.Worksheets中的工作表）
{
Console.WriteLine（@Sheet for Sheet+ sheet.Index）;
//获取一个范围使用
range = sheet.get_Range（A1，Missing.Value）;
//获取右边的值的结尾（将停在第一个空单元格）
range = range.get_End（XlDirection.xlToRight）;
//将底部的值结束，查找最后一列（将在第一个空单元格停止）
range = range.get_End（XlDirection.xlDown）;
//获取底部的地址，右侧单元格
string downAddress = range.get_Address（
false，false，XlReferenceStyle.xlA1，
Type.Missing， Type.Missing）;
//获取范围，然后从a1
range = sheet.get_Range（A1，downAddress）;
object [，] values =（object [，]）range.Value2;
//查看值
Console.Write（\t）;
Console.WriteLine（）; （int i = 1; i< = values.GetLength（0）; i ++）
{
for（int j = 1; j <= values.GetLength（1） ; j ++）
{
Console.Write（{0} \t，values [i，j]）;
}
Console.WriteLine（）;
}
}
}
catch（异常e）
{
Console.WriteLine（e）;
}
finally
{
range = null;
if（book！= null）
book.Close（false，Missing.Value，Missing.Value）;
book = null;
if（app！= null）
app.Quit（）;
app = null;
}
}
在上面的代码中，值[i，j] 是您需要添加到数据集中的值。 i 表示行，而 j 表示列。
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
public void Load(string filename, Excel.XlFileFormat format = Excel.XlFileFormat.xlCSV)
{
app = new Excel.Application();
book = app.Workbooks.Open(Filename: filename, Format: format);
DataSet ds = new DataSet();
foreach (Excel.Worksheet sheet in book.Sheets)
{
DataTable dt = new DataTable(sheet.Name);
ds.Tables.Add(dt);
//??? Fill dt from sheet
}
this.Data = ds;
}
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:
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
while (excelReader.Read())
{
//excelReader.GetInt32(0);
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
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.
private static void TestExcel()
{
ApplicationClass app = new ApplicationClass();
Workbook book = null;
Range range = null;
try
{
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
string execPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase);
book = app.Workbooks.Open(@"C:\data.xls", Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value);
foreach (Worksheet sheet in book.Worksheets)
{
Console.WriteLine(@"Values for Sheet "+sheet.Index);
// get a range to work with
range = sheet.get_Range("A1", Missing.Value);
// get the end of values to the right (will stop at the first empty cell)
range = range.get_End(XlDirection.xlToRight);
// get the end of values toward the bottom, looking in the last column (will stop at first empty cell)
range = range.get_End(XlDirection.xlDown);
// get the address of the bottom, right cell
string downAddress = range.get_Address(
false, false, XlReferenceStyle.xlA1,
Type.Missing, Type.Missing);
// Get the range, then values from a1
range = sheet.get_Range("A1", downAddress);
object[,] values = (object[,]) range.Value2;
// View the values
Console.Write("\t");
Console.WriteLine();
for (int i = 1; i <= values.GetLength(0); i++)
{
for (int j = 1; j <= values.GetLength(1); j++)
{
Console.Write("{0}\t", values[i, j]);
}
Console.WriteLine();
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
range = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
}
}
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导入到DataSet？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
