CODEWITHSUNDEEP
c#loopsforeachexceldatareader
Krol Noir
Krol Noir

Reputation: 35

How can I iterate over an excel file

How can I iterate over an excel file?

I currently have a class using ExcelDataReader

Class => https://paste.lamlam.io/nomehogupi.cs#14fXzlopygZ27adDcXEDtQHT0tWTxoYR

I have a excel file with 5 columns

This is my current code, but it is not exporting the result that I expect ...

TextWriter stream = new StreamWriter("excel Path");
//foreach string in List<string>
foreach(var item in ComboList) {
 var rows = ExcelHelper.CellValueCollection(item.Key);

 foreach(var row in rows) {
  stream.WriteLine(item.Key + "|" + row);
  break;
 }
}

stream.Close();

My result:

Column1|Row1
Column1|Row2
Column1|Row3
...
Column2|Row1
Column2|Row2
Column2|Row3
...

Expected:

Column1|Row1|Column2|Row1...
Column1|Row2|Column2|Row2...
Column1|Row3|Column2|Row3...

Thanks

Upvotes: 1

Views: 2105

Answers (3)

shA.t
shA.t

Reputation: 16968

If I understand what you want truly! I think you need to add a method like RowValueCollection to your ExcelHelper as below:

public static IEnumerable<string[]> RowValueCollection()
{
    var result = Data.Tables[0].Rows.OfType<DataRow>()
        .Select(dr => dr.ItemArray.Select(ia => ia.ToString()).ToArray());
    return result;
}

And then use it like this:

var rowValues = ExcelHelper.RowValueCollection();
foreach (var row in rowValues)
{
    stream.WriteLine(string.Join("|", row));
}

HTH ;)

Upvotes: 0

Krol Noir
Krol Noir

Reputation: 35

This is the answer, I just needed to get the dataSet and iterate over it, very easy

var data = ExcelHelper.DataSet();

foreach (DataRow dr in data.Tables[0].Rows)
{
    Console.WriteLine(dr["Column1"] + "|" + dr["Column2"]);
}

Upvotes: 1

Shoeless
Shoeless

Reputation: 19

The first problem is that you are asking to write two items and only two items on a single line.

Would it help if you made the stream.writeline() statement into a .write() statement and then, after the inner loop performed a .writeline() which would terminate the line?

Apologies for not commenting but not enough Respect points to do so. - Malc

Upvotes: 0

Related Questions