Read Excel rows from specified row number using CLosedXML in ASP.Net

telldurg...

SOLVED CLOSED ACTIVE ANSWERED
User: telldurges
Posted: on May 16, 2020 01:19 AM
Forum: ASP.Net Thirdparty controls
Answer: 1
Views: 29930

hello sir

How to read dynamic rows and there cell from excel using CLosedXml using asp.net

i am facing an issue while reading data from excel using closedXml. i have a requirement suppose i have an excel if the excel rows will start from first row then no issue is there but suppose in excel i want to read from 4th row or any starting row that row will read and read that rows cell also not first rows cell in my code i am able to access from 3'rd row but cell is reading from first row. in my case i want to give dynamic rows like row will read from 3rd row and there 3rd row cell ignore 1 and 2nd row data even in first or second rows have data.

Please give me solution

                            foreach (IXLRow row in rows)
                            {
                                if (FirstRow == Rnumber)
                                {                                    
                                        AddQuery += "IF OBJECT_ID('dbo." + map.FileName + "', 'U') IS NULL ";
                                        AddQuery += "BEGIN ";
                                        AddQuery += "CREATE TABLE [dbo].[" + map.FileName + "](";
                                        // AddQuery += "id int identity , ";
                                        try
                                        {
                                        //foreach (IXLCell cell in row.Cells())
                                        foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
                                        {
                                                if (cell.Value.ToString() != "")
                                                {
                                                    dt.Columns.Add(cell.Value.ToString());
                                                    //AddQuery += "[" + cell.Value.ToString() + "]" + " VARCHAR(MAX),";
                                                    AddQuery += "[" + RemoveSpecialCharacters(cell.Value.ToString()) + "]" + " VARCHAR(MAX),";
                                                    var DtMsrcFld = obj.InsertMainSrcField(map.FileName, RemoveSpecialCharacters(cell.Value.ToString()), cell.Value.ToString());
                                                }
                                            }
                                            AddQuery = AddQuery.TrimEnd(',');
                                            AddQuery += ")";
                                            AddQuery += " END";
                                        //RowNumber++;
                                        //firstRow = false;
                                        FirstRow++;
                                    }
                                           
                                        catch (Exception e)
                                        {
                                        FirstRow++;
                                         errrornum = 1;
                                        var response = e.Message;
                                        }
                                  
                                    }
                                    else
                                    {
                                    if (errrornum==1)
                                    {
                                        break;
                                    }
                                        dt.Rows.Add();
                                        int i = 0;
                                        try
                                        {
                                        //foreach (IXLCell cell in row.Cells())
                                        foreach (IXLCell cell in row.Cells(row.FirstCellUsed().Address.ColumnNumber, row.LastCellUsed().Address.ColumnNumber))
                                        {
                                            i = (int)GetColumnIndexFromName(GetColumnName(cell.Address.ToString()));
                                                dt.Rows[dt.Rows.Count - 1][i - 1] = cell.Value.ToString();
                                            }
                                        }
                                        catch (Exception ee)
                                        {
                                            ee.ToString();
                                        }
                                    }                                
                            }

Rnumber is coming from textbox that is row number from which row i am going to read data.

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on May 16, 2020 02:31 AM
Report

Using this article i have created the example.

Code

DataTable dt;
using (XLWorkbook workBook = new XLWorkbook(FileUpload1.PostedFile.InputStream))
{
    IXLWorksheet workSheet = workBook.Worksheet(1);
    dt = new DataTable();
    int rowIndex = 1;
    int rowToReadFrom = 3;
    bool headerRow = true;
    foreach (IXLRow row in workSheet.Rows())
    {
        if (rowIndex >= rowToReadFrom)
        {
            if (headerRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                headerRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }

        rowIndex++;
    }
}

VB.Net

Using workBook As XLWorkbook = New XLWorkbook(FileUpload1.PostedFile.InputStream)
    Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
    dt = New DataTable()
    Dim rowIndex As Integer = 1
    Dim rowToReadFrom As Integer = 3
    Dim headerRow As Boolean = True
    For Each row As IXLRow In workSheet.Rows()
        If rowIndex >= rowToReadFrom Then
            If headerRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                headerRow = False
            Else
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If
        End If
        rowIndex += 1
    Next
End Using

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Read Excel rows from specified row number using CLosedXML in ASP.Net

Read Excel File data in ASP.Net

Company

Explore

Follow us

Contact Us