Import Excel data with Range and Formula using ClosedXML in ASP.Net

Last Reply on Feb 01, 2016 09:15 AM By dharmendr

Posted on Feb 01, 2016 06:46 AM

I want to Import an excel Sheet based on the Range.

The Range cells contain data like string,Double and also Lookups and formuals.

How to check if it is string or date or Lookup or Formauls and read them.

 

 

You are viewing reply posted by: dharmendr on Feb 01, 2016 09:15 AM.
Posted on Feb 01, 2016 09:15 AM Modified on one year ago

Hi Pradeep23,

I have created sample please check the below below code. I have used ClosedXml library. You can download the dll from the below article.

Read and Import Excel data to DataTable using ClosedXml in ASP.Net with C# and VB.Net

Code

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { 
                new DataColumn("Number", typeof(int)), 
                new DataColumn("String", typeof(string)), 
                new DataColumn("DateTime", typeof(DateTime)),
                new DataColumn("Formula", typeof(string)) 
            });
    string file = Server.MapPath("~/Test.xlsx");
    using (XLWorkbook workBook = new XLWorkbook(file))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);

        bool firstRow = false;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    if (cell.DataType == ClosedXML.Excel.XLCellValues.Number && !cell.HasFormula)
                    {
                        dt.Rows[dt.Rows.Count - 1][0] = cell.Value.ToString();
                    }
                    else if (cell.DataType == ClosedXML.Excel.XLCellValues.Text && !cell.HasFormula)
                    {
                        dt.Rows[dt.Rows.Count - 1][1] = cell.Value.ToString();
                    }
                    else if (cell.DataType == ClosedXML.Excel.XLCellValues.DateTime && !cell.HasFormula)
                    {
                        dt.Rows[dt.Rows.Count - 1][2] = cell.Value.ToString();
                    }
                    else if (cell.HasFormula && (cell.DataType == ClosedXML.Excel.XLCellValues.Number
                        || cell.DataType == ClosedXML.Excel.XLCellValues.Text || cell.DataType == ClosedXML.Excel.XLCellValues.DateTime))
                    {
                        dt.Rows[dt.Rows.Count - 1][3] = cell.Value.ToString();
                    }
                    i++;
                }
            }
        }
    }
    gvDetails.DataSource = dt;
    gvDetails.DataBind();
}

Screenshot

Input

1 02/Jan/16 abc
=15/5 xyz 02/Feb/16

Output