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

Pradeep23
 
on Feb 01, 2016 06:46 AM
Sample_966871.zip
16479 Views

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.

 

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Feb 01, 2016 09:15 AM
on Sep 13, 2017 06:51 AM

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