Importing Excel into a DataTable Quickly
Caling .Value2
is an expensive operation because it's a COM-interop call. I would instead read the entire range into an array and then loop through the array:
object[,] data = Range.Value2;
// Create new Column in DataTable
for (int cCnt = 1; cCnt <= Range.Columns.Count; cCnt++)
{
textBox3.Text = cCnt.ToString();
var Column = new DataColumn();
Column.DataType = System.Type.GetType("System.String");
Column.ColumnName = cCnt.ToString();
DT.Columns.Add(Column);
// Create row for Data Table
for (int rCnt = 1; rCnt <= Range.Rows.Count; rCnt++)
{
textBox2.Text = rCnt.ToString();
string CellVal = String.Empty;
try
{
cellVal = (string)(data[rCnt, cCnt]);
}
catch (Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
{
ConvertVal = (double)(data[rCnt, cCnt]);
cellVal = ConvertVal.ToString();
}
DataRow Row;
// Add to the DataTable
if (cCnt == 1)
{
Row = DT.NewRow();
Row[cCnt.ToString()] = cellVal;
DT.Rows.Add(Row);
}
else
{
Row = DT.Rows[rCnt + 1];
Row[cCnt.ToString()] = cellVal;
}
}
}
Best /Fastest way to read an Excel Sheet into a DataTable?
I have always used OLEDB
for this, something like...
Dim sSheetName As String
Dim sConnection As String
Dim dtTablesList As DataTable
Dim oleExcelCommand As OleDbCommand
Dim oleExcelReader As OleDbDataReader
Dim oleExcelConnection As OleDbConnection
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1"""
oleExcelConnection = New OleDbConnection(sConnection)
oleExcelConnection.Open()
dtTablesList = oleExcelConnection.GetSchema("Tables")
If dtTablesList.Rows.Count > 0 Then
sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString
End If
dtTablesList.Clear()
dtTablesList.Dispose()
If sSheetName <> "" Then
oleExcelCommand = oleExcelConnection.CreateCommand()
oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]"
oleExcelCommand.CommandType = CommandType.Text
oleExcelReader = oleExcelCommand.ExecuteReader
nOutputRow = 0
While oleExcelReader.Read
End While
oleExcelReader.Close()
End If
oleExcelConnection.Close()
The ACE.OLEDB
provider will read both .xls
and .xlsx
files and I have always found the speed quite good.
Get Excel data in DataTable
While the JET/Access driver can read Excel files as if they were databases, it has several problems, especially in the .NET Core era:
- It's Windows-only
- It needs to be installed. It can't be packaged with your application
- The installed version must match the bitness (32/64-bit) of any Office components. This in turn means that your application must match Office's bitness.
There are libraries that can read Excel files directly. One such option is ExcelDataReader, which opens a DbDataReader
over an Excel sheet. It can handle both the obsolete xls
format and the 16 year old xlsx
format (yes, the "new" xlsx format was introduce in 2006, 16 years ago).
The generated data reader can be used to read the data or load a DataTable the same as any other data reader.
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var table=new DataTable();
table.Load(reader);
...
}
}
ExcelDataReader has an extension that allows reading all sheets in a workbook into a DataSet, with one DataTable for each sheet.
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var dataset= reader.AsDataSet();
// The result of each spreadsheet is in dataset.Tables
}
}
Import from excel to data table vb.net
You are facing this issue because your column contains mixed data types, so the OLEDB provider will take the dominant data type and convert all other datatype values to NULL.
Try adding IMEX=1
to the extended properties in the connection String:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
- What is IMEX in the OLEDB connection string?
If it doesn't work, try adding a dummy row after the Header, in this row add a string value ex xyz
and in the code delete this row after reading the data.
How do I import from Excel to a DataSet using Microsoft.Office.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.
Related Topics
Comboboxes Are Linked for Some Reason
Open a Folder Using Process.Start
Index of Currently Selected Row in Datagridview
Creating Threads - Task.Factory.Startnew VS New Thread()
Linq Syntax - Selecting Multiple Columns
Invalid Cast from 'System.Int32' to 'System.Nullable'1[[System.Int32, Mscorlib]]
Is There a Format Code Shortcut for Visual Studio
Wpf Listview: Attaching a Double-Click (On an Item) Event
Error Message "Cs5001 Program Does Not Contain a Static 'Main' Method Suitable for an Entry Point"
Why Are There System.*.* Dlls in My Bin Release Directory
Datetime.Adddays() Not Working as Expected
Finding Property Differences Between Two C# Objects
Can a Tcp C# Client Receive and Send Continuously/Consecutively Without Sleep