How can I iterate over an excel file

Question

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

I have not used `ExcelDataReader` myself, but for most `XReader` classes you must use a `while` loop like `while (reader.Read()) { Console.WriteLine(reader["name"]);` }`. See https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader(v=vs.110).aspx or https://msdn.microsoft.com/en-us/library/system.io.streamreader(v=vs.110).aspx — JamesFaix, Aug 08 '18 at 19:53
this doesn't appear to be an issue with reading from the excel sheet, but rather an issue with how the op is displaying the output. currently the loop is setup to go by column then by row. You're going to need to do it the other way around to get the output you want. Or store the values for each row somewhere and display them later. — Hack, Aug 08 '18 at 19:57
what I'm looking for and practically this: string.format("{0}|{1}|{2}|{3}", colum1[row1], colum2[row1], colum3[row1], colum4[row1]); and so on, but I can not find a logic — Krol Noir, Aug 08 '18 at 20:32

score 1 · Answer 1 · answered Aug 08 '18 at 20:46

1

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"]);
}

answered Aug 08 '18 at 20:46

Krol Noir

35
7

this doesn't solve the op's problem, what about the 3rd and 4th and nth column? – Hack Aug 09 '18 at 11:19
dr["Column3"], dr["Column4"], dr["Column5"], dr["Column6"] – Krol Noir Aug 09 '18 at 11:34
lol didn't even realize you answered your own question. still think you could do better then this personally, could be made so you didn't have to manually enter the columns like that allowing for a more generic approach. – Hack Aug 09 '18 at 11:50

score 0 · Answer 2 · answered Aug 08 '18 at 19:57

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

score 0 · Accepted Answer · answered Aug 14 '18 at 10:58

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 ;)

How can I iterate over an excel file

3 Answers3