Spreadsheetgear - get string representation of a range of cells

Question

I recently started using SpreadsheetGear to parse an Excel file. I needed to go through each row and display the string representation of the first 6 columns. What I did was this:

    Debug.Print(ssgSheet.Cells(i,0).Text)
    Debug.Print(ssgSheet.Cells(i,1).Text)
    Debug.Print(ssgSheet.Cells(i,2).Text)
    Debug.Print(ssgSheet.Cells(i,3).Text)
    Debug.Print(ssgSheet.Cells(i,4).Text)
    Debug.Print(ssgSheet.Cells(i,5).Text)

Where 'i' is the current row of the worksheet. This isn't very pretty, but for now it's not a big deal if I use the 6-liner version for debugging. I also tried this:

    Debug.Print(ssgSheet.Cells(i, 0, i, 5).Text)

but I only get a blank line.

I was just wondering if there is a simple way built into SpreadsheetGear to get a row or range of cells, and just have it returned as some kind of delimited string? I couldn't find an appropriate function to do this on the SpreadsheetGear API site but maybe I missed it.

Answer 1

Range to DataTable

I don't know of any built-in function in SpreadsheetGear to get a delimited string from a range of cells. However, there is a built-in function to get a DataTable from a range of cells.

DataTable dt = ssgSheet.Cells[i, 0, i, 5].GetDataTable(SpreadsheetGear.Data.GetDataFlags.None);

(You may want to set the flags differently depending on whether you have column names.)

Once you have a DataTable, you can get a delimited string using code from this question or from this question.

Save Range in CSV format to Stream

Another option is to save the range in CSV format to a Stream and then get a delimited string from the Stream.

using (System.IO.MemoryStream mstream = new System.IO.MemoryStream())
{
  ssgSheet.Cells[i, 0, i, 5].SaveToStream(mstream, SpreadsheetGear.FileFormat.CSV);
  mstream.Position = 0;
  System.IO.StreamReader reader = new System.IO.StreamReader(mstream);
  Debug.Print(reader.ReadToEnd());
}