Read Excel File in C#



Created: 12/11/2019 02:07:15 p.m.







Read Excel File in C# (Very basic example)


Tools:
Visual studio 2019
Microsoft Excel 2007
Interop v12

Advice:
If you have a huge file that you need to upload into your database, take a look at bulk upload in c# from excel in google.

Html:

<asp:Literal ID="ltlExcel" runat="server"></asp:Literal>


Code behind:

        protected void Button3_Click(object sender, EventArgs e)
        {
            string path = Server.MapPath("data.xlsx");
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path);
            Microsoft.Office.Interop.Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;

            string html = "";
            for(int i = 1; i < xlRange.Rows.Count; i++)
            {
                html += "<br/><div>";
                for (int y = 1; y < xlRange.Cells.Count; y++)
                {
                    if(xlRange.Cells[i][y] != null && xlRange.Cells[i, y].Value != null)
                        html += string.Format("{0}", xlRange.Cells[i,y].Value);
                }
                html += "<div>";
            }

            xlWorkbook.Close();
            xlApp.Quit();

            ltlExcel.Text = string.Format("<div>{0}</div>", html);
        }

Important!
Pay attention to initial values of the range, i started from 1, not 0, in both Columns and Cells. Otherwise you will face the following exception: System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A03EC'



Regards



Comments:


Go to Comments