Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 857
sergeidave Avatar asked Oct 06 '22 06:10

sergeidave


2 Answers

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();
      }
    }
}
like image 63
Fiacc Avatar answered Oct 10 '22 02:10

Fiacc


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

sw.WriteLine(sheet.Range["C5"].Value.ToString());
like image 26
SysDragon Avatar answered Oct 10 '22 04:10

SysDragon