ASPForums.Net RSS Feed additions to the content that appears on ASPForums.Net(c) 2019 All rights reserved.Import Excel data with Range and Formula using ClosedXML in ASP.Net<p>Hi <a class="username" rel="Pradeep23"> Pradeep23</a>,</p> <p>I have created sample please check the below below code. I have used ClosedXml library. You can download the dll from the below article.</p> <h1 class="header"><a title="Read and Import Excel data to DataTable using ClosedXml in ASP.Net with C# and VB.Net'a" href="">Read and Import Excel data to DataTable using ClosedXml in ASP.Net with C# and VB.Net</a></h1> <p><span style="text-decoration: underline;">Code</span></p> <pre class="brush: csharp">protected void Page_Load(object sender, EventArgs e) { DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[] { new DataColumn(&#34;Number&#34;, typeof(int)), new DataColumn(&#34;String&#34;, typeof(string)), new DataColumn(&#34;DateTime&#34;, typeof(DateTime)), new DataColumn(&#34;Formula&#34;, typeof(string)) }); string file = Server.MapPath(&#34;~/Test.xlsx&#34;); 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 &amp;&amp; !cell.HasFormula) { dt.Rows[dt.Rows.Count - 1][0] = cell.Value.ToString(); } else if (cell.DataType == ClosedXML.Excel.XLCellValues.Text &amp;&amp; !cell.HasFormula) { dt.Rows[dt.Rows.Count - 1][1] = cell.Value.ToString(); } else if (cell.DataType == ClosedXML.Excel.XLCellValues.DateTime &amp;&amp; !cell.HasFormula) { dt.Rows[dt.Rows.Count - 1][2] = cell.Value.ToString(); } else if (cell.HasFormula &amp;&amp; (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(); }</pre> <p><span style="text-decoration: underline;">Screenshot</span></p> <p><span style="text-decoration: underline;">Input</span></p> <table border="1"> <tbody> <tr> <td width="64">1</td> <td width="67">02/Jan/16</td> <td width="70">abc</td> </tr> <tr> <td>=15/5</td> <td>xyz</td> <td>02/Feb/16</td> </tr> </tbody> </table> <p><span style="text-decoration: underline;">Output</span></p> <p><img src="" alt="" width="333" height="114" /></p>, 01 Feb 2016 09:15:13 GMT