How to change this method to return a string array

Question

I want an array or list that contains all the values of all the cells in a spreadsheet. There are a set number of columns but any number of rows. This is a method I got from msdn.com and I want to know if I could modify it to return all values in the spreadsheet, or if there was a way to get all the cells that had values, then make a string builder to iterate through every address. Really, I'm trying to get my head around this SDK and am hoping you guys can give me some insights. Here is the method:

public static string GetCellValue(string fileName, 
string sheetName, 
string addressName)

{ string value = null;

// Open the spreadsheet document for read-only access.
using (SpreadsheetDocument document = 
    SpreadsheetDocument.Open(fileName, false))
{
    // Retrieve a reference to the workbook part.
    WorkbookPart wbPart = document.WorkbookPart;

    // Find the sheet with the supplied name, and then use that 
    // Sheet object to retrieve a reference to the first worksheet.
    Sheet theSheet = wbPart.Workbook.Descendants<Sheet>().
      Where(s => s.Name == sheetName).FirstOrDefault();

    // Throw an exception if there is no sheet.
    if (theSheet == null)
    {
        throw new ArgumentException("sheetName");
    }

    // Retrieve a reference to the worksheet part.
    WorksheetPart wsPart = 
        (WorksheetPart)(wbPart.GetPartById(theSheet.Id));

    // Use its Worksheet property to get a reference to the cell 
    // whose address matches the address you supplied.
    Cell theCell = wsPart.Worksheet.Descendants<Cell>().
      Where(c => c.CellReference == addressName).FirstOrDefault();

    // If the cell does not exist, return an empty string.
    if (theCell != null)
    {
        value = theCell.InnerText;

        // If the cell represents an integer number, you are done. 
        // For dates, this code returns the serialized value that 
        // represents the date. The code handles strings and 
        // Booleans individually. For shared strings, the code 
        // looks up the corresponding value in the shared string 
        // table. For Booleans, the code converts the value into 
        // the words TRUE or FALSE.
        if (theCell.DataType != null)
        {
            switch (theCell.DataType.Value)
            {
                case CellValues.SharedString:

                    // For shared strings, look up the value in the
                    // shared strings table.
                    var stringTable = 
                        wbPart.GetPartsOfType<SharedStringTablePart>()
                        .FirstOrDefault();

                    // If the shared string table is missing, something 
                    // is wrong. Return the index that is in
                    // the cell. Otherwise, look up the correct text in 
                    // the table.
                    if (stringTable != null)
                    {
                        value = 
                            stringTable.SharedStringTable
                            .ElementAt(int.Parse(value)).InnerText;
                    }
                    break;

                case CellValues.Boolean:
                    switch (value)
                    {
                        case "0":
                            value = "FALSE";
                            break;
                        default:
                            value = "TRUE";
                            break;
                    }
                    break;
            }
        }
    }
}
return value;

}

I really appreciate any help.

Jeff B · Accepted Answer · 2014-04-30T18:07:38.500

The following chunk of code grabs all the cells and filters it down to just the one we're trying to find...

Cell theCell = wsPart.Worksheet.Descendants<Cell>().
  Where(c => c.CellReference == addressName).FirstOrDefault();

If you remove the Where(...), that should give you all the cells...

var cells = wsPart.Worksheet.Descendants<Cell>();

Now that you have that, you can take the chunk of code inside the if (theCell != null) { ... } block, make it method that takes a Cell (I called it ProcessCell here, it'd need to be a static function that returns a string), and call it inside a foreach loop, like this:

var results = new List<String>();
foreach cell in cells
{
    results.Add(ProcessCell(cell));
}

and then just return your results!

return results;

Technically I didn't answer your question since the return type is a List<String> instead of an array, but I think you can figure that part out if it's really necessary ;)

How to change this method to return a string array

1 Answers1