Reading Row Data from a spreadsheet

Question

Using the tools SpreadSheetLight I cant find how to read the rows of a spreadsheet file. Specificly Sheet 1.

Two problems Im having is

There is no way that I can see to get the Rows

There is no way that I can see to get the Column Index Here is my code

public void ParseExcelFile(FileInfo file) 
{
    using (SLDocument sl = new SLDocument())
    {
        FileStream fs = new FileStream(file.FullName, FileMode.Open);

        MemoryStream msFirstPass = new MemoryStream();
        SLDocument sheet1 = new SLDocument(fs, "Sheet1");


        // There is no way that I can see to get the Rows
        foreach(var row in sheet1.Rows)
        {
            foreach(SLCell c in row)
            {
                // There is no way that I can see to get the Column Index
                switch(c.Column )
                {
                    case 1:
                        //Handle data if cell is Column 1
                        break;
                    case 2:
                        //Handle data if cell is Column 2
                        break;
                    case 3:
                        //Handle data if cell is Column 3
                        break;
                }
            }
        }



    }

}//func

Same problem. Could you please provide some info here as you have already accepted the answer? — Sandy, Oct 30 '16 at 19:42

score 4 · Answer 1 · answered Jul 03 '17 at 13:26

The code below would read each row (assuming file is the file path to the Excel file):

using (SLDocument sl = new SLDocument())
{
    FileStream fs = new FileStream(file, FileMode.Open);
    SLDocument sheet = new SLDocument(fs, "Table 1");

    SLWorksheetStatistics stats = sheet.GetWorksheetStatistics();
    for (int j = 1; j < stats.EndRowIndex; j++)
    {
        // Get the first column of the row (SLS is a 1-based index)
        var value = sheet.GetCellValueAsString(j, 0);

    }
}

score 1 · Accepted Answer · answered May 07 '14 at 04:46

1

Its going to be hard for people to answer this as SpreadSheetLight doesn't appear to have publicly available code documentation. I have two suggestions based on a couple of assumptions:

Does the SLDocument.Row.SLCell class have any index property? If so you could get your required information from there.
You could replace your foreach's with for's to track the row and column.

answered May 07 '14 at 04:46

S.Richmond

11,412
6
39
57

Actually, there's quite a bit of info here: http://spreadsheetlight.com/sample-code/ – B. Clay Shannon-B. Crow Raven Apr 06 '16 at 18:31
2

... api [help file in chm format](http://spreadsheetlight.com/downloads/SpreadsheetLight.chm) also available – mcalex Feb 17 '17 at 02:54

score 0 · Answer 3 · answered May 24 '23 at 13:15

string fileName = @"Test.xlsx";

using FileStream fs = new FileStream(fileName, FileMode.Open);
using SLDocument sl = new SLDocument(fs);

var worksheetNames = sl.GetWorksheetNames();
foreach (var worksheetName in worksheetNames)
{
    sl.SelectWorksheet(worksheetName);
    SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
    for (var i = 1; i <= stats.NumberOfRows; i++)
    {
        for (var j = 1; j <= stats.NumberOfColumns; j++)
        {
            var value = sl.GetCellValueAsString(i, j);
            Console.WriteLine(value);
        }
    }
}

Reading Row Data from a spreadsheet

3 Answers3