ClosedXML Webform - foreach loop looping twice

Question

So my problem is when I am trying to read excel file it prints output correct in gridview once, but when I try to store the row[cells] - only into an String variable using serials += row[cells] it is looping through the foreach loop twice, and I can't figure out how to solve it!

WebForm1.aspx <- contains a form Fileupload button and gridview to uplad and read Excel File

WebForm1.aspx.cs

using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
public partial class WebForm1 : System.Web.UI.Page
{

    String serials = "";
    int sheetRowCount = 0;
    int rowCount = 0;
    int ColumnCount = 0;
    int i, rownum=0;
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void Button1_Click(object sender, EventArgs e)
    {

        // checking File Extension
        if (checkFileExtension())
        {

            // reading File if extension is true
            readExcelFile();

        }

    }

    private bool checkFileExtension()
    {
        String[] allowedExtensions = { "xls", "xlsx" };
        String ext = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName);
        bool isValidFile = false;
        for (int i = 0; i < allowedExtensions.Length; i++)
        {
            if (ext == "." + allowedExtensions[i])
            {
                isValidFile = true;
            }
        }

        if (!isValidFile)
        {
            error1.Text = "Allowed FileTypes are .xls/.xlsx";
            return isValidFile;
        }
        else
        {
            readExcelFile();
            return isValidFile;
        }
    }

    private void readExcelFile()
    {
        // to bind data table to gridview
        DataTable dt = new DataTable();

        // creating new workbook object and sending Uploaded File as input File
        using (XLWorkbook workbook = new XLWorkbook(FileUpload1.PostedFile.InputStream))
        {
            // creating excel worksheet object to access worksheet from workbook(Excel File)
            IXLWorksheet sheet = workbook.Worksheet(1);

            // initializing 1st row
            bool firstRow = true;
            foreach (IXLRow row in sheet.Rows())
            {
                sheetRowCount++;
                //System.Diagnostics.Debug.WriteLine("SHEET ROW COUNT: " + sheetRowCount);
                // printing 1st row as headers of Table DATA
                if (firstRow)
                {
                    // getting cells of 1st row
                    foreach (IXLCell cell in row.Cells())
                    {
                        // adding full 1st row cells into data table
                        dt.Columns.Add(cell.Value.ToString());
                    }
                    // making 1st row = false as 1st is finished
                    firstRow = false;
                }
                else
                {
                    rowCount++;
                    rownum++;
                  
                    // as it is 2nd row it will be printed as normal text
                    // adding empty rows in sheet
                    dt.Rows.Add();

                    //System.Diagnostics.Debug.WriteLine("Rows Count : " + rowCount);
                    // variable for couting row
                    i = 0;
                    // getting cells in rows
                    foreach (IXLCell cell in row.Cells())
                    {
                        // to stop Row indexer we use row count - 1 as it loops
                        // it always keep row to row - 1 to add value to corrent row
                        dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                        ColumnCount++;
                        //System.Diagnostics.Debug.WriteLine("Column Count: " + ColumnCount);
                        i++;
                    }

                }
                

            }
        }

        //System.Diagnostics.Debug.WriteLine("=====================================");
        // sheet Row Count
        //System.Diagnostics.Debug.WriteLine("SHEET ROW COUNT: " + sheetRowCount);
        sheetRowCount = 0;
        // Row Count
        ///System.Diagnostics.Debug.WriteLine("Rows Count : " + rowCount);
        rowCount = 0;

        //System.Diagnostics.Debug.WriteLine("Current Row: " + rownum);
        rownum = 0;

        //Column Count
        //System.Diagnostics.Debug.WriteLine("Current Column: " + i);
        i = 0;

        //DATA TABLE
        // setting data source to gridview
        GridView1.DataSource = dt;
        // binding data to gridview
        GridView1.DataBind();


        gettingAllSerialsIntoArrayToJSON(dt);
    }

    private void gettingAllSerialsIntoArrayToJSON(DataTable dt)
    {

        System.Diagnostics.Debug.WriteLine("after printing: " + dt.Rows.Count);
        // For each row, print the values of each column.
        // 2 rows  - ROW 0 -- Row 1
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            // 1 column
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                serials += i + "-" + j + " : " + dt.Rows[i][j].ToString() + "END LINE   ";
            }
        }

        error1.Text = serials;

    }
}

}

ClosedXML Webform - foreach loop looping twice

Answers (1)

Related Questions