CODEWITHSUNDEEP
c#excelspreadsheetlight
techno
techno

Reputation: 6508

Get Names of all Columns in Excel sheet in SpreadSheetLight

I'm trying to populate a combobox with the names of the columns in a spreadsheet. I'm using the spreadsheetlight library. I can set the cell value using the following code where A refers to column name and 1 refers to row name. (Am I right?)

But how can I get the the name of all columns in all sheets?

SLDocument sl = new SLDocument();    
sl.SetCellValue("A1", true);

Upvotes: 0

Views: 3772

Answers (1)

Chawin
Chawin

Reputation: 1466

First, get the last column index using SLWorksheetStatistics:

SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
int endColumnIndex = stats.EndColumnIndex;

Then iterate through the columns:

var headers = new List<string>();
for (int i = 1; i <= endColumnIndex; i++){
    headers.Add(sl.GetCellValueAsString(1, i));
}

The following will print the values "foo" and "bar" from the column list:

var fileName = "test.xlsx";
var sl = new SLDocument(fileName);

foreach (var sheetName in sl.GetWorksheetNames())
{
    SLDocument sheet = new SLDocument(fileName, sheetName);
    sheet.SetCellValue("A1", "foo");
    sheet.SetCellValue("B1", "bar");

    SLWorksheetStatistics stats = sheet.GetWorksheetStatistics();
    int endColumnIndex = stats.EndColumnIndex;

    var headers = new List<string>();
    for (int i = 1; i <= endColumnIndex; i++)
    {
        headers.Add(sheet.GetCellValueAsString(1, i));
    }

    foreach (var column in headers)
    {
        Console.WriteLine(column);
    }

    Console.ReadKey();
}

Upvotes: 1

Related Questions