ClosedXML reading empty cell and increasing count

Question

I am reading excel file with ClosedXML, I have data in 1 single Column A1.A2.A3... all A columns,

When I open excel and delete A2.A3 and keep only single value in A1 and save excel,

after that when I upload file to file control it uploads file and prints 3 even though I have single data in A1.

it reads data and insert into array as [ "123482", "", "" ] <- it is considering deleted rows as empty data and I am not able to skip these empty data any help?

Sample Data in Excel: _ <= Blank Line | Voucher | | -------- | | 123456 | | _ // This is an empty line in excel with using DELETE Key on Keyboard | | _ // This is an empty line in excel with using DELETE Key on Keyboard | | _ // This is an empty line in excel with using DELETE Key on Keyboard | | _ // This is an empty line in excel with using DELETE Key on Keyboard |

Sample Code READING EXCEL FILE:

public DataTable readExcelFile(System.IO.Stream inputExcelFile)
    {
        // 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(inputExcelFile))
        {
            // 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())
            {
                // 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;
                    continue; // go to next row
                }
                else {
                    // adding empty rows in sheet
                    dt.Rows.Add();
                    int 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();
                        i++;             
                    }
                }

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

CALLING FUNCTION WHICH HAS DATATABLE FROM ABOVE READING:

// converting Array into JSon String
excelSerialArrayToJson(excelReaderdt);

DATA TABLE ROW TO ARRAY

private void excelSerialArrayToJson(DataTable excelInputdt)
    {
        // initializing serials Array Length to DataTable Row Length
        serials = new String[excelInputdt.Rows.Count];
        // For each row, print the values of each column.
        // 2 rows  - ROW 0 -- Row 1
        for (int i = 0; i < excelInputdt.Rows.Count; i++)
        {
            // 1 column
            for (int j = 0; j < excelInputdt.Columns.Count; j++)
            {
                // initializing serials Array
                serials[i] = excelInputdt.Rows[i][j].ToString();
            }
        }

My Desired output should be

Output I want Serials[] = ("123456") - COUNT = 1
Output I am Getting| = Serials[] = ("123456","","","") - COUNT = 4

Array Count should be 1 as it has only 1 entry which is 123456

ClosedXML reading empty cell and increasing count

Answers (1)

Related Questions