Problem

A range is a group of cells in an Excel workbook that can span multiple worksheets, which is defined by an underlying formula that specifies the worksheets and cells included in the range. The formula references the worksheets and cells by name, for example: Sheet1!A5:A6 or Sheet2!C14:C27.

ExcelWriter provides the option of creating named and unnamed ranges programmatically. Named ranges will remain in the output file, but unnamed ranges are only defined in code. Both Worksheet.CreateRange() and one of the overloaded methods for Worksheet.CreateNamedRange() require a formula string.

Here are a few useful tips for pulling together the formulas for [defining ranges|http://wiki.softartisans.com/display/EW8/Areas+and+Ranges] with ExcelWriter.

Solution

The formulas that express a range in Excel use sheet names and absolute or relative cell names to reference groups of cells.

Cell Names

Cell names can either be relative (A5) or absolute ($A$5). When the cell name is relative, the cell name can update. For instance, a formula with the relative cell name A5 will update to A6 if a row is inserted above row 5. When the absolute cell name is used, the name does not update. Both can be used in the formulas for ranges.

For example, the two statements below create the same range using the ExcelApplication object:
Range cond_range = wb.CreateRange("=Sheet1!C7:O7");
Range vRange = wb.Worksheets[0].CreateRange("=Sheet1!$C$7:$O$7");
For additional ease, the Cell.Name object can be used to return the relative name of the cell:
Workbook.Worksheets[0].Cells[0, 0].Name; //Returns "A1"

Worksheet Names In Excel

Formulas that express ranges also need to specify the worksheet that the cells belong to in order to avoid ambiguity. The general format to fully specify cells with a worksheet is to preface the cells with SheetName!. For example: Sheet1!C4:C5.

If the worksheet name contains a space, the entire worksheet name needs to be surrounded by single quotation marks: ‘Sheet 2’!A5:A7.

Worksheet Names with ExcelWriter

Just like in Excel, if a worksheet name has a space in it, make sure to surround the sheet name with ‘ ‘. Not doing so will result in a failed parser error message: Unable to parse formula: irrecoverable syntax error.

Worksheet name with space:
Workbook.Worksheets[0].Name = "Sheet 1";
Range rng = WB.CreateRange("='Sheet 1'!$C$7:$O$7");
Worksheet name without space:
Workbook.Worksheets[1].Name = "Sheet2";
Range rng2 = WB.CreateRange("=Sheet2!$C$7:$O$7");
Worksheet.Name can be used to set or return the name of the worksheet:
Workbook.Worksheets[0].Name = "Sheet 1";

Including separate groups of cells in a single range:

Separate groups of cells can be included in the same range. Each continuous group of cells needs to be prefaced by SheetName! and separated by commas. For example: Sheet1!C5:C6,Sheet1!D5:D6,Sheet2!E7:E10 is made of three separate groups of continuous cells that span two worksheets.

Below is some sample code that adds cells in specified columns to a range:

int[] cols = { 2, 3, 6, 12, 14 }; //0-index column numbers to include in the range
string range_formula = "=";
foreach (int col in cols)
{
range_formula = range_formula + "Sheet1!" + wb.Worksheets[0].Cells[row - 1, col].Name + ",";
}

Follow

Related posts: