How to read data from an excel and push into an array in asp.net?

Question

I am new to c# programming and would like to know how we can read data from an excel cell by cell. In the below code, I am getting an array of data from Column A of excel as pValue1= a;b;c;d%;e%;f%; Now, I want to push only the values with % at the end into a different array if the header of column A=ID. Also, I want to enclose each item in pValue1 with single quotes.

Input:

ID	Name
a	roy
b	may
c	Jly
d%	nav
e%	nov
f%	lio

Expected output: pValue1= 'a';'b';'c' pValue3= d%e%f%

         try {
                Utils.ExcelFile excelFile = new Utils.ExcelFile(excelFilename);
                DataTable excelData = excelFile.GetDataFromExcel();

                // Column headers
                param1 = 0 < excelData.Columns.Count ? excelData.Columns[0].ColumnName :string.Empty;
                param2 = 1 < excelData.Columns.Count ? excelData.Columns[1].ColumnName :string.Empty;

                ArrayList pValueArray1 = new ArrayList();
                ArrayList pValueArray2 = new ArrayList();

                if (pValueArray1.Count > 0) pValue1 = string.Join(";", pValueArray1.ToArray()) + ";";
                if (pValueArray2.Count > 0) pValue2 = string.Join(";", pValueArray2.ToArray()) + ";";
               
            }

Tim Schmelter · Accepted Answer · 2021-02-25 09:08:48Z

Not sure if i understood your issue. I guess you have already loaded the excel into the DataTable and you now just want to split the Id-column into two separate lists. You can use LINQ:

var percentLookup = excelData.AsEnumerable()
    .ToLookup(row => row.Field<string>("Id").EndsWith("%"));
List<string> pValue1List = percentLookup[false]
    .Select(row => row.Field<string>("Id"))
    .ToList();
List<string> pValue2List = percentLookup[true]
    .Select(row => row.Field<string>("Id"))
    .ToList();

The lookup contains two groups, the rows where the id-column has a percent at the end and the rest. So you can create the two lists easily with it.

Since you are new to C# programming it might be better to use a plain loop:

List<string> pValue1List = new List<string>();
List<string> pValue2List = new List<string>();
foreach (DataRow row in excelData.Rows)
{
    string id = row.Field<string>("Id");
    if(id.EndsWith("%"))
    {
        pValue2List.Add(id);
    }
    else
    {
        pValue1List.Add(id);
    }
}

If you need a String[] instead of a List<string> use ToArray instead of ToList and in the second approach fill the lists but use i.e. pValue1List.ToArray at the end.

In general: you should stop using ArrayList, that's 20 years old and since more than 10 years obsolete. Use a strongly typed List<T>, here a List<string>.

Collectives™ on Stack Overflow

How to read data from an excel and push into an array in asp.net?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related