Change column names or properties of an Excel worksheet via Linq

Question

I'm wondering how to raise the column names or properties of a worksheet into UPPERCASE without knowing how many there are or what their contents is?

My current code is as follows:

var excel = new ExcelQueryFactory(fileLocation);
var dataSheet = from c in excel.Worksheet(0) select c;

score 1 · Answer 1 · answered Aug 09 '14 at 20:37

Assuming the column names are on the first row of the worksheet, I recommend using the WorksheetNoHeader() method that LinqToExcel provides. You can then retrieve the first row and loop through all column names.

Here's a code example of what that could look like:

var excel = new ExcelQueryFactory(fileLocation);
var firstRow = excel.WorksheetNoHeader().First();

var columnNames = new List<string>();
foreach (var cell in firstRow)
  columnNames.Add(cell.ToString());

score 1 · Answer 2 · answered Aug 10 '14 at 07:50

You can use the open Office library.

Below code will give you the column text in upper case (considering 1st row has column names)

List<string> columnNames = new List<string>();
var worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

foreach (var r in sheetData.Elements<Row>())
{
    var columnIndex = 0;
    if (r.RowIndex == "1") // Column Header
    {
        foreach (var c in r.Elements<Cell>())
        {
            columnNames.Add(GetCellValue(spreadsheetDocument.WorkbookPart, c).ToUpper());
        }
    }

score 0 · Answer 3 · answered Aug 09 '14 at 17:59

The obvious way to do this would be to isolate the appropriate cells (the ones that stand for the column names of your table) from the variable dataSheet (you could do this by looping, hardcoding or (ideally) by more efficient means, if you know of one). Then use the String#toUpper() method to raise each one of the String values into uppercase & inject them back into their respective cells.

Change column names or properties of an Excel worksheet via Linq

3 Answers3