如何从Excel中导入使用的Microsoft.Office.Interop.Excel一个DataSet？ [英] How do I import from Excel to a DataSet using Microsoft.Office.Interop.Excel?
问题描述
我试图使用Microsoft.Office.Interop.Excel命名空间打开一个Excel文件（XSL或CSV，但可悲的不可以 XSLX），并将其导入到数据集。我没有在工作表或列名的控制，所以我需要允许变更。
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.
我已经试过这在以往的 OLEDB方法，并有很多与它的问题（越野车，Excel文件的架构的缓慢，需要事先了解），所以我想避免再次这样做。我想要做的是使用的Microsoft.Office.Interop.Excel直接导入工作簿到一个DataSet，或通过工作表循环，并加载每一个到一个DataTable。
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.
不管你信不信，我已经很难找到资源这一点。 发现大多是在计算器上一些搜索人试图做反向（数据集=> Excel）中，或OLEDB技术。谷歌一直没有更多的帮助。
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.
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;
}
我很好无论是在一次导入整本书，或者通过一张在一个时间循环。我能做到这一点与Interop.Excel？
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?
推荐答案
有关使用 Excel数据阅读器（previously什么托管这里）在codePLEX一个开源项目？它的作品真的很好，我从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.
指定的链接中给出的样本code：
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();
更新
周围的一些搜索后，我碰到这篇文章就来了：更快的MS Excel的使用阅读办公室互操作程序集的。本文只使用办公互操作程序集来读取一个给定的Excel工作表的数据。该人士$ C $ c是项目也在那儿。我想这篇文章可以在你想达到什么样的一个起点。看看是否有帮助。
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
更新2
在code以下需要一个 Excel工作簿并读取找到的所有值，每个 Excel工作表内在 Excel工作簿。
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;
}
}
在上面的code，值[I，J] 是您需要添加到集中的值。 I 表示行，而Ĵ表示列。
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.
这篇关于如何从Excel中导入使用的Microsoft.Office.Interop.Excel一个DataSet？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
