Click or drag to resize
SheetOpenTable Method (String)
Opens the specified range of the cells and returns a Table object.

Namespace: Aceoffix.ExcelWriter
Assembly: Aceoffix (in Aceoffix.dll) Version: 5.0.0.1
Syntax
Copy
public Table OpenTable(
	string RangeAddress
)

Parameters

RangeAddress
Type: SystemString

The A1-style notation is defined in Microsoft Excel. For example: A1:F8, it means a range of cells A1 through F8. The A1 cell is the cell whose row index is 1 and column index is 1. The F8 cell is the cell whose row index is 8 and column index is 6.

Note Note

Note: There is a limit that the maximum number of columns per worksheet is 676 and the maximum number of rows is 65,536 in Excel2003 and earlier versions. Please do not exceed the maximum number of columns when input the parameter of RangeAddress.

Return Value

Type: Table
Returns a Table object.
Remarks

In Excel worksheet, developer can define any range of cells containing one contiguous block of cells as a Table object. You should use Table object if you want to operate a range of cells.

If the number of data rows which are filled is out of range specified by RangeAddress, the Table will automatically expand the number of rows and apply the format of range specified by RangeAddress to new rows. If you do not want the Table to expand the number of rows, you should call the OpenTable(String, Boolean) method.

Examples

Code example 1:The following code example shows how to use the OpenTable method to fill data into an empty table which only has grid lines.

Copy
Aceoffix.ExcelWriter.Workbook wb = new Aceoffix.ExcelWriter.Workbook();
Aceoffix.ExcelWriter.Sheet sheet1 = wb.OpenSheet("sheet1");
Aceoffix.ExcelWriter.Table table1 = sheet1.OpenTable("B5:F10");

// Output data into the table.
System.Random rd = new Random(System.DateTime.Now.Millisecond);
for (int j = 0; j < 4; j++)
{
    for (int i = 0; i < table1.DataFields.Count; i++)
    {
        table1.DataFields[i].Value = rd.Next(1000).ToString(); // You can set the data from database.
    }
    table1.NextRow();
}
table1.Close(); // Required.

AceoffixCtrl1.ServerPage = "aceoffix-runtime/server.aspx";
AceoffixCtrl1.Bind(wb);
AceoffixCtrl1.OpenDocument("doc/test.xls", Aceoffix.OpenModeType.xlsReadOnly, "Tom");

Code example 2: Draws a table on an empty worksheet and fill data into the table. The code example also shows how to alert user attention to the out-of-range data.

Copy
Aceoffix.ExcelWriter.Workbook wb = new Aceoffix.ExcelWriter.Workbook();
Aceoffix.ExcelWriter.Sheet sheet1 = wb.OpenSheet("sheet1");
Aceoffix.ExcelWriter.Table table1 = sheet1.OpenTable("B5:F10");

// Draw a table with full grid lines and set the color of the table.
table1.RowHeight = 20;
table1.ColumnWidth = 14.25;
table1.Border.BorderType = Aceoffix.ExcelWriter.XlBorderType.xlFullGrid;
table1.Border.LineStyle = Aceoffix.ExcelWriter.XlBorderLineStyle.xlContinuous;
table1.Border.Weight = Aceoffix.ExcelWriter.XlBorderWeight.xlThin;
table1.Border.LineColor = System.Drawing.Color.Gray;
table1.BackColor = System.Drawing.Color.FromArgb(230, 230, 230);

// Draw the head of the table.
sheet1.OpenTable("B5:F5").BackColor = System.Drawing.Color.CadetBlue;
sheet1.OpenTable("B5:F5").ForeColor = System.Drawing.Color.White;
table1.DataFields[0].Value = "Head Title1";
table1.DataFields[1].Value = "Head Title2";
table1.DataFields[2].Value = "Head Title3";
table1.DataFields[3].Value = "Head Title4";
table1.DataFields[4].Value = "Head Title5";
table1.NextRow();

// Output data into the table.
System.Random rd = new Random(System.DateTime.Now.Millisecond);
for (int j = 0; j < 4; j++)
{
    for (int i = 0; i < table1.DataFields.Count; i++)
    {
        int iValue = rd.Next(1000);
        table1.DataFields[i].Value = iValue.ToString(); // You can set the data from the database.
        if (iValue > 800) // If the value is greater than 800, then the cell will display alert color to warn user.
        {
            table1.DataFields[i].BackColor = System.Drawing.Color.Red;
            table1.DataFields[i].ForeColor = System.Drawing.Color.Yellow;
        }
    }
    table1.NextRow();
}

// Draw the bottom of the table and calculate the total.
sheet1.OpenTable("B10:F10").BackColor = System.Drawing.Color.LightYellow;
table1.DataFields[0].Value = "Total";
// You can get the total by your code. But using the formula of Excel will be more easier in most cases.
table1.DataFields[4].Formula = "sum(F6:F9)";
table1.NextRow();
table1.Close(); // Required.

// Merge cells specified by C10:E10 into one cell in a table.
sheet1.OpenTable("C10:E10").Merge(); 

AceoffixCtrl1.ServerPage = "aceoffix-runtime/server.aspx";
AceoffixCtrl1.Bind(wb);
AceoffixCtrl1.OpenDocument("doc/test.xls", Aceoffix.OpenModeType.xlsReadOnly, "Tom");
See Also