0

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);
Matt Ellen
  • 11,268
  • 4
  • 68
  • 90
techno
  • 6,100
  • 16
  • 86
  • 192

1 Answers1

1

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();
}
Chawin
  • 1,438
  • 1
  • 21
  • 33
  • Thanks..Will check and get back. – techno Jul 03 '17 at 17:57
  • I tried your code to populate a combox,but instead of returning the column names.. the combobox returns the name of the file..I populated the comboxbox like this `foreach (var column in headers) { comboBox1.Items.Add(column); }` – techno Jul 04 '17 at 13:07
  • I've corrected `stats.EndowIndex` to `stats.EndColumnIndex` and added a solution which runs perfectly – Chawin Jul 04 '17 at 15:42
  • Your code works perfectly.. but what about files with more than 1 worksheets? – techno Jul 04 '17 at 17:25
  • @techno An SLDocument is a sheet, you'd need to have a `for` loop through each of the sheets and run the above code – Chawin Jul 05 '17 at 07:41
  • How can I loop through the sheets within a work book? Please advice? – techno Jul 05 '17 at 09:43
  • @tecno `foreach (var name in sl.GetWorksheetNames())`, but really that's a separate question, your initial question has been answered – Chawin Jul 05 '17 at 10:32
  • Yes.but a spreadsheet will have more than one sheets usually.How clan I create a document from the spreadsheet name ? – techno Jul 05 '17 at 11:51
  • @techo This should really be in another question which can then be answered, comments shouldn't be for extended discussions – Chawin Jul 05 '17 at 12:45
  • Since most excel documents have more than 1 sheets,your current answer is not useful to me...unless you add details about iterating through the sheets.The code above does not work with files with more than 1 sheets. – techno Jul 05 '17 at 12:46
  • @techno Again, that's not what you asked. Regardless, I've updated my answer, but I'd recommend updating your question with your new requirements – Chawin Jul 05 '17 at 13:48