How to read data of an Excel file using C#?

Question

How to read an Excel file using C#? I open an Excel file for reading and copy it to clipboard to search email format, but I don't know how to do it.

FileInfo finfo;
Excel.ApplicationClass ExcelObj = new Excel.ApplicationClass();
ExcelObj.Visible = false;

Excel.Workbook theWorkbook;
Excel.Worksheet worksheet;

if (listView1.Items.Count > 0)
{
    foreach (ListViewItem s in listView1.Items)
    {
        finfo = new FileInfo(s.Text);
        if (finfo.Extension == ".xls" || finfo.Extension == ".xlsx" || finfo.Extension == ".xlt" || finfo.Extension == ".xlsm" || finfo.Extension == ".csv")
        {
            theWorkbook = ExcelObj.Workbooks.Open(s.Text, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, false);

            for (int count = 1; count <= theWorkbook.Sheets.Count; count++)
            {
                worksheet = (Excel.Worksheet)theWorkbook.Worksheets.get_Item(count);
                worksheet.Activate();
                worksheet.Visible = false;
                worksheet.UsedRange.Cells.Select();
            }
        }
    }
}

Answer 1

Use OLEDB Connection to communicate with excel files. it gives better result

using System.Data.OleDb;



                string physicalPath = "Your Excel file physical path";
                OleDbCommand cmd = new OleDbCommand();
                OleDbDataAdapter da = new OleDbDataAdapter();
                DataSet ds = new DataSet();
                String strNewPath = physicalPath;
                String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                String query = "SELECT * FROM [Sheet1$]"; // You can use any different queries to get the data from the excel sheet
                OleDbConnection conn = new OleDbConnection(connString);
                if (conn.State == ConnectionState.Closed) conn.Open();
                try
                {
                    cmd = new OleDbCommand(query, conn);
                    da = new OleDbDataAdapter(cmd);
                    da.Fill(ds);

                }
                catch
                {
                    // Exception Msg 

                }
                finally
                {
                    da.Dispose();
                    conn.Close();
                }

The Output data will be stored in dataset, using the dataset object you can easily access the datas. Hope this may helpful

Answer 2

Use Open XML.

Here is some code to process a spreadsheet with a specific tab or sheet name and dump it to something like CSV. (I chose a pipe instead of comma).

I wish it was easier to get the value from a cell, but I think this is what we are stuck with. You can see that I reference the MSDN documents where I got most of this code. That is what Microsoft recommends.

    /// <summary>
    /// Got code from: https://msdn.microsoft.com/en-us/library/office/gg575571.aspx
    /// </summary>
    [Test]
    public void WriteOutExcelFile()
    {
        var fileName = "ExcelFiles\\File_With_Many_Tabs.xlsx";
        var sheetName = "Submission Form"; // Existing tab name.
        using (var document = SpreadsheetDocument.Open(fileName, isEditable: false))
        {
            var workbookPart = document.WorkbookPart;
            var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);
            var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
            var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

            foreach (var row in sheetData.Elements<Row>())
            {
                foreach (var cell in row.Elements<Cell>())
                {
                    Console.Write("|" + GetCellValue(cell, workbookPart));
                }
                Console.Write("\n");
            }
        }
    }

    /// <summary>
    /// Got code from: https://msdn.microsoft.com/en-us/library/office/hh298534.aspx
    /// </summary>
    /// <param name="cell"></param>
    /// <param name="workbookPart"></param>
    /// <returns></returns>
    private string GetCellValue(Cell cell, WorkbookPart workbookPart)
    {
        if (cell == null)
        {
            return null;
        }

        var value = cell.CellFormula != null
            ? cell.CellValue.InnerText 
            : cell.InnerText.Trim();

        // If the cell represents an integer number, you are done. 
        // For dates, this code returns the serialized value that 
        // represents the date. The code handles strings and 
        // Booleans individually. For shared strings, the code 
        // looks up the corresponding value in the shared string 
        // table. For Booleans, the code converts the value into 
        // the words TRUE or FALSE.
        if (cell.DataType == null)
        {
            return value;
        }
        switch (cell.DataType.Value)
        {
            case CellValues.SharedString:

                // For shared strings, look up the value in the
                // shared strings table.
                var stringTable =
                    workbookPart.GetPartsOfType<SharedStringTablePart>()
                        .FirstOrDefault();

                // If the shared string table is missing, something 
                // is wrong. Return the index that is in
                // the cell. Otherwise, look up the correct text in 
                // the table.
                if (stringTable != null)
                {
                    value =
                        stringTable.SharedStringTable
                            .ElementAt(int.Parse(value)).InnerText;
                }
                break;

            case CellValues.Boolean:
                switch (value)
                {
                    case "0":
                        value = "FALSE";
                        break;
                    default:
                        value = "TRUE";
                        break;
                }
                break;
        }
        return value;
    }

Answer 3

You can use Microsoft.Office.Interop.Excel assembly to process excel files.

1. Right click on your project and go to Add reference. Add the Microsoft.Office.Interop.Excel assembly.
2. Include using Microsoft.Office.Interop.Excel; to make use of assembly.

Here is the sample code:

    using Microsoft.Office.Interop.Excel;

    //create the Application object we can use in the member functions.
    Microsoft.Office.Interop.Excel.Application _excelApp = new Microsoft.Office.Interop.Excel.Application();
    _excelApp.Visible = true;

    string fileName = "C:\\sampleExcelFile.xlsx";

    //open the workbook
    Workbook workbook = _excelApp.Workbooks.Open(fileName,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing);

    //select the first sheet        
    Worksheet worksheet = (Worksheet)workbook.Worksheets[1];

    //find the used range in worksheet
    Range excelRange = worksheet.UsedRange;

    //get an object array of all of the cells in the worksheet (their values)
    object[,] valueArray = (object[,])excelRange.get_Value(
                XlRangeValueDataType.xlRangeValueDefault);

    //access the cells
    for (int row = 1;  row <= worksheet.UsedRange.Rows.Count; ++row)
    {
        for (int col = 1; col <= worksheet.UsedRange.Columns.Count; ++col)
        {
            //access each cell
            Debug.Print(valueArray[row, col].ToString());
        }
    }

    //clean up stuffs
    workbook.Close(false, Type.Missing, Type.Missing);
    Marshal.ReleaseComObject(workbook);

    _excelApp.Quit();
    Marshal.FinalReleaseComObject(_excelApp);

Answer 4

public void excelRead(string sheetName)
        {
            Excel.Application appExl = new Excel.Application();
            Excel.Workbook workbook = null;
            try
            {
                string methodName = "";


                Excel.Worksheet NwSheet;
                Excel.Range ShtRange;

                //Opening Excel file(myData.xlsx)
                appExl = new Excel.Application();


                workbook = appExl.Workbooks.Open(sheetName, Missing.Value, ReadOnly: false);
                NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
                ShtRange = NwSheet.UsedRange; //gives the used cells in sheet


                int rCnt1 = 0;
                int cCnt1 = 0;

                for (rCnt1 = 1; rCnt1 <= ShtRange.Rows.Count; rCnt1++)
                {
                    for (cCnt1 = 1; cCnt1 <= ShtRange.Columns.Count; cCnt1++)
                    {
                        if (Convert.ToString(NwSheet.Cells[rCnt1, cCnt1].Value2) == "Y")
                        {

                            methodName = NwSheet.Cells[rCnt1, cCnt1 - 2].Value2;
                            Type metdType = this.GetType();
                            MethodInfo mthInfo = metdType.GetMethod(methodName);

                            if (Convert.ToString(NwSheet.Cells[rCnt1, cCnt1 - 2].Value2) == "fn_AddNum" || Convert.ToString(NwSheet.Cells[rCnt1, cCnt1 - 2].Value2) == "fn_SubNum")
                            {
                                StaticVariable.intParam1 = Convert.ToInt32(NwSheet.Cells[rCnt1, cCnt1 + 3].Value2);
                                StaticVariable.intParam2 = Convert.ToInt32(NwSheet.Cells[rCnt1, cCnt1 + 4].Value2);
                                object[] mParam1 = new object[] { StaticVariable.intParam1, StaticVariable.intParam2 };
                                object result = mthInfo.Invoke(this, mParam1);
                                StaticVariable.intOutParam1 = Convert.ToInt32(result);
                                NwSheet.Cells[rCnt1, cCnt1 + 5].Value2 = Convert.ToString(StaticVariable.intOutParam1) != "" ? Convert.ToString(StaticVariable.intOutParam1) : String.Empty;
                            }

                            else
                            {
                                object[] mParam = new object[] { };
                                mthInfo.Invoke(this, mParam);

                                NwSheet.Cells[rCnt1, cCnt1 + 5].Value2 = StaticVariable.outParam1 != "" ? StaticVariable.outParam1 : String.Empty;
                                NwSheet.Cells[rCnt1, cCnt1 + 6].Value2 = StaticVariable.outParam2 != "" ? StaticVariable.outParam2 : String.Empty;
                            }
                            NwSheet.Cells[rCnt1, cCnt1 + 1].Value2 = StaticVariable.resultOut;
                            NwSheet.Cells[rCnt1, cCnt1 + 2].Value2 = StaticVariable.resultDescription;
                        }

                        else if (Convert.ToString(NwSheet.Cells[rCnt1, cCnt1].Value2) == "N")
                        {
                            MessageBox.Show("Result is No");
                        }
                        else if (Convert.ToString(NwSheet.Cells[rCnt1, cCnt1].Value2) == "EOF")
                        {
                            MessageBox.Show("End of File");
                        }

                    }
                }

                workbook.Save();
                workbook.Close(true, Missing.Value, Missing.Value);
                appExl.Quit();
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ShtRange);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(NwSheet);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(workbook);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(appExl);
            }
            catch (Exception)
            {
                workbook.Close(true, Missing.Value, Missing.Value);
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
                System.Runtime.InteropServices.Marshal.CleanupUnusedObjectsInCurrentContext();
            }
        }

//code for reading excel data in datatable
public void testExcel(string sheetName)
        {
            try
            {
                MessageBox.Show(sheetName);

                foreach(Process p in Process.GetProcessesByName("EXCEL"))
                {
                    p.Kill();
                }
                //string fileName = "E:\\inputSheet";
                Excel.Application oXL;
                Workbook oWB;
                Worksheet oSheet;
                Range oRng;


                //  creat a Application object
                oXL = new Excel.Application();




                //   get   WorkBook  object
                oWB = oXL.Workbooks.Open(sheetName);


                //   get   WorkSheet object
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[1];
                System.Data.DataTable dt = new System.Data.DataTable();
                //DataSet ds = new DataSet();
                //ds.Tables.Add(dt);
                DataRow dr;


                StringBuilder sb = new StringBuilder();
                int jValue = oSheet.UsedRange.Cells.Columns.Count;
                int iValue = oSheet.UsedRange.Cells.Rows.Count;


                //  get data columns
                for (int j = 1; j <= jValue; j++)
                {
                    oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];
                    string strValue = oRng.Text.ToString();
                    dt.Columns.Add(strValue, System.Type.GetType("System.String"));
                }


                //string colString = sb.ToString().Trim();
                //string[] colArray = colString.Split(':');


                //  get data in cell
                for (int i = 2; i <= iValue; i++)
                {
                    dr = dt.NewRow();
                    for (int j = 1; j <= jValue; j++)
                    {
                        oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
                        string strValue = oRng.Text.ToString();
                        dr[j - 1] = strValue;


                    }
                    dt.Rows.Add(dr);
                }
                if(StaticVariable.dtExcel != null)
                {
                    StaticVariable.dtExcel.Clear();
                    StaticVariable.dtExcel = dt.Copy();
                }
                else
                StaticVariable.dtExcel = dt.Copy();

                oWB.Close(true, Missing.Value, Missing.Value);
                oXL.Quit();
                MessageBox.Show(sheetName);

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {

            }
        }

//code for class initialize
 public static void startTesting(TestContext context)
        {

            Playback.Initialize();
            ReadExcel myClassObj = new ReadExcel();
            string sheetName="";
            StreamReader sr = new StreamReader(@"E:\SaveSheetName.txt");
            sheetName = sr.ReadLine();
            sr.Close();
            myClassObj.excelRead(sheetName);
            myClassObj.testExcel(sheetName);
        }

//code for test initalize
public  void runValidatonTest()
        {

            DataTable dtFinal = StaticVariable.dtExcel.Copy();
            for (int i = 0; i < dtFinal.Rows.Count; i++)
            {
                if (TestContext.TestName == dtFinal.Rows[i][2].ToString() && dtFinal.Rows[i][3].ToString() == "Y" && dtFinal.Rows[i][4].ToString() == "TRUE")
                {
                    MessageBox.Show(TestContext.TestName);
                    MessageBox.Show(dtFinal.Rows[i][2].ToString());
                    StaticVariable.runValidateResult = "true";
                    break;
                }
            }
            //StaticVariable.dtExcel = dtFinal.Copy();
        }

Answer 5

First of all, it's important to know what you mean by "open an Excel file for reading and copy it to clipboard..."

This is very important because there are many ways you could do that depending just on what you intend to do. Let me explain:

1. If you want to read a set of data and copy that in the clipboard and you know the data format (e.g. column names), I suggest you use an OleDbConnection to open the file, this way you can treat the xls file content as a Database Table, so you can read data with SQL instruction and treat the data as you want.

2. If you want to do operations on the data with the Excel object model then open it in the way you began.

3. Some time it's possible to treat an xls file as a kind of csv file, there are tools like File Helpers which permit you to treat and open an xls file in a simple way by mapping a structure on an arbitrary object.

Another important point is in which Excel version the file is.

I have, unfortunately I say, a strong experience working with Office automation in all ways, even if bounded in concepts like Application Automation, Data Management and Plugins, and generally I suggest only as the last resort, to using Excel automation or Office automation to read data; just if there aren't better ways to accomplish that task.

Working with automation could be heavy in performance, in terms of resource cost, could involve in other issues related for example to security and more, and last but not at least, working with COM interop it's not so "free".. So my suggestion is think and analyze the situation within your needs and then take the better way.

Answer 6

Why don't you create OleDbConnection? There are a lot of available resources in the Internet. Here is an example

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+filename+";Extended Properties=Excel 8.0");
con.Open();
try
{
     //Create Dataset and fill with imformation from the Excel Spreadsheet for easier reference
     DataSet myDataSet = new DataSet();
     OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM ["+listname+"$]" , con);
     myCommand.Fill(myDataSet);
     con.Close();
     richTextBox1.AppendText("\nDataSet Filled");

     //Travers through each row in the dataset
     foreach (DataRow myDataRow in myDataSet.Tables[0].Rows)
     {
          //Stores info in Datarow into an array
          Object[] cells = myDataRow.ItemArray;
          //Traverse through each array and put into object cellContent as type Object
          //Using Object as for some reason the Dataset reads some blank value which
          //causes a hissy fit when trying to read. By using object I can convert to
          //String at a later point.
          foreach (object cellContent in cells)
          {
               //Convert object cellContect into String to read whilst replacing Line Breaks with a defined character
               string cellText = cellContent.ToString();
               cellText = cellText.Replace("\n", "|");
               //Read the string and put into Array of characters chars
               richTextBox1.AppendText("\n"+cellText);
          }
     }
     //Thread.Sleep(15000);
}
catch (Exception ex)
{
     MessageBox.Show(ex.ToString());
     //Thread.Sleep(15000);
}
finally
{
     con.Close();
}