Loop through Excel worksheets and save text into a TXT file with C#

Question

I have most of the problem solved. I can loop through the worksheets, I can create the text file and write to it. The only problem I'm having is actually extracting the text or values from every sheet. Below is the code I have, but it only throws an object per worksheet and writes those words into my text file instead of the actual values.

System.Object[,]
System.Object[,]

What else am I missing? I should point out I'm a beginner programmer.

Here's the code I have so far:

using (StreamWriter sw = File.CreateText("ExtractedText.txt"))
{
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook thisWkBook = xlApp.Workbooks.Open(thisFile);

    foreach (Excel.Worksheet sheet in thisWkBook.Worksheets)
    {
        sw.WriteLine(sheet.UsedRange.Value);
    }
}

Any ideas? Thanks!

Fiacc · Accepted Answer · 2013-01-25T05:04:36.217

3

Something like this, not tested. See http://dontbreakthebuild.com/2011/01/30/excel-and-c-interop-with-net-4-how-to-read-data-from-excel/

using (StreamWriter sw = File.CreateText("ExtractedText.txt"))
{
    var excelApp = new Excel.Application();
    var workBook = excelApp.Workbooks.Open(thisFile);

    foreach (var sheet in workBook.Worksheets)
    {
      foreach (var row in sheet.UsedRange.Rows)
      {
        foreach (var cell in row.Columns)
        {
          sw.Write(cell.Value + " ");
        } 
        sw.WriteLine();
      }
    }
}

edited Jan 25 '13 at 05:04

answered Jan 24 '13 at 17:02

Fiacc

1,324
1
15
24

Ah! So I needed to loop through the rows and columns as well! Gotcha. I'm testing this right away, thanks! – sergeidave Jan 24 '13 at 17:09
The `.Value2` part didn't work. But starting from your suggestion, I found that you can loop through the cells with 'foreach' statements as follows: `foreach (Excel.Worksheet sheet in thisWkBook.Worksheets) { foreach (Excel.Range thisRow in sheet.UsedRange.Rows) { foreach (Excel.Range thisCell in thisRow.Columns) { sw.Write(thisCell.Value + " "); } sw.WriteLine(); }` Would you like to update your answer so I can select it as the final answer? – sergeidave Jan 24 '13 at 19:13

SysDragon · Answer 2 · 2013-01-24T17:11:18.577

1

You need to get the data fron the sheet, for example:

sw.WriteLine(sheet.Range["C5"].Value.ToString());

edited Jan 24 '13 at 17:11

answered Jan 24 '13 at 16:55

SysDragon

9,692
15
60
89

This points me to the right direction (it's actually `Range["C5"]`, with brackets). Thanks! – sergeidave Jan 24 '13 at 17:07

Loop through Excel worksheets and save text into a TXT file with C#

2 Answers2