Leehbi Leehbi - 2 months ago 26
C# Question

Quickly read data from Excel

I have the code below reading data from an Excel worksheet and converting it to a pipe delimited text file. It works. The problem is it's quite slow as I have to read 1 cell at a time in order to add in the pipe.

I wondered if there was a better way to do this i.e. read the data into memory/array in one step and act on it there.

public string Process(string filename)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;
string str = "";
int rCnt = 0;
int cCnt = 0;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Use the 1st worksheet

StreamWriter sw = new StreamWriter(destpath);
range = xlWorkSheet.UsedRange;

for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
{
if ((rCnt % 1000) == 0)
{
txtProgress.Text = "Rows processed: "+ rCnt;
}
for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
{
str = str + ToStr((range.Cells[rCnt, cCnt] as Excel.Range).Value2) + "|";
}
sw.WriteLine(str);
str = "";

}

xlWorkBook.Close(true, null, null);
xlApp.Quit();
sw.Close();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Complete","Status");
return "Success";
}

public static string ToStr(object readField)
{
if ((readField != null))
{
if (readField.GetType() != typeof(System.DBNull))
{
return Convert.ToString(readField);
}
else
{
return "";
}
}
else
{
return "";
}
}

private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}

Answer

If you plan on only performing a read on the excel file content, I suggest you use the ExcelDataReader library Link, which extracts the worksheetData into a DataSet object.

    static void Main(string[] args)
    {
        IExcelDataReader reader = null;
        string FilePath = "PathToExcelFile";

        //Load file into a stream
        FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);

        //Must check file extension to adjust the reader to the excel file type
        if (System.IO.Path.GetExtension(FilePath).EndsWith(".xls"))
        {
            reader = ExcelReaderFactory.CreateBinaryReader(stream);
        }
        else if (System.IO.Path.GetExtension(FilePath).EndsWith(".xlsx"))
        {
            reader = ExcelReaderFactory.CreateBinaryReader(stream);
        }

        if (reader != null)
        {
            //Fill DataSet
            System.Data.DataSet result = reader.AsDataSet();
            try
            {
                //Loop through rows for the desired worksheet
                //In this case I use the table index "0" to pick the first worksheet in the workbook
                foreach (DataRow row in result.Tables[0].Rows)
                {
                    string FirstColumn = row[0].ToString();
                }
            }
            catch
            {

            }
        }
    }
Comments