B
baga
Hi, I am trying to load xls file, but it works VERY slow (~50 rows per
second), maybe it is possible to do it faster?
public class ExcelLoader
{
public static List<DataTable> Load(string filename)
{
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(filename,
0, true, 5, "", "", true, Excel.XlPlatform.xlWindows,
"\t", false, false, 0, true);
List<DataTable> tables = new List<DataTable>();
foreach (Excel.Worksheet worksheet in workbook.Worksheets)
{
DataTable dt = LoadDataTable(worksheet);
tables.Add(dt);
}
app.Quit();
return tables;
}
private static DataTable LoadDataTable(Excel.Worksheet worksheet)
{
Excel.Range usedrange = worksheet.UsedRange;
int numrows = usedrange.Rows.Count;
int numcols = usedrange.Columns.Count;
string name = (string)worksheet.Name;
DataTable dt = new DataTable(name);
Program.TraceLine("Loading worksheet " + name);
for (int i = 0; i < numcols; i++)
{
Program.TraceLine("Creating column " + i.ToString());
dt.Columns.Add();
}
int rowid = 0;
foreach (Excel.Range row in worksheet.Rows)
{
Program.TraceLine("Creating row " + rowid.ToString());
DataRow dr = dt.NewRow();
int cellid = 0;
foreach (Excel.Range cell in row.Cells)
{
if (cell.Value != null)
dr[cellid] = cell.Value;
if (++cellid > numcols)
break;
}
dt.Rows.Add(dr);
if (++rowid > numrows)
break;
}
Program.TraceLine("Worksheet loaded");
return dt;
}
}
second), maybe it is possible to do it faster?
public class ExcelLoader
{
public static List<DataTable> Load(string filename)
{
Excel.Application app = new Excel.Application();
Excel.Workbook workbook = app.Workbooks.Open(filename,
0, true, 5, "", "", true, Excel.XlPlatform.xlWindows,
"\t", false, false, 0, true);
List<DataTable> tables = new List<DataTable>();
foreach (Excel.Worksheet worksheet in workbook.Worksheets)
{
DataTable dt = LoadDataTable(worksheet);
tables.Add(dt);
}
app.Quit();
return tables;
}
private static DataTable LoadDataTable(Excel.Worksheet worksheet)
{
Excel.Range usedrange = worksheet.UsedRange;
int numrows = usedrange.Rows.Count;
int numcols = usedrange.Columns.Count;
string name = (string)worksheet.Name;
DataTable dt = new DataTable(name);
Program.TraceLine("Loading worksheet " + name);
for (int i = 0; i < numcols; i++)
{
Program.TraceLine("Creating column " + i.ToString());
dt.Columns.Add();
}
int rowid = 0;
foreach (Excel.Range row in worksheet.Rows)
{
Program.TraceLine("Creating row " + rowid.ToString());
DataRow dr = dt.NewRow();
int cellid = 0;
foreach (Excel.Range cell in row.Cells)
{
if (cell.Value != null)
dr[cellid] = cell.Value;
if (++cellid > numcols)
break;
}
dt.Rows.Add(dr);
if (++rowid > numrows)
break;
}
Program.TraceLine("Worksheet loaded");
return dt;
}
}