1.6.3.1 Worksheet Basic Operation

The basic worksheet operations include adding worksheet to workbook, activating a worksheet, getting and setting worksheet properties, deleting worksheet, etc. Some practical examples are provided below.

Add New Worksheet

Add a worksheet to a workbook using the AddLayer method. 

// Access the workbook named "Book1"
WorksheetPage wksPage("Book1");

// Add a new sheet to the workbook
int index = wksPage.AddLayer("New Sheet"); 

// Access the new worksheet
Worksheet wksNew = wksPage.Layers(index);

Activate a Worksheet

Workbook is an Origin object that contains worksheets. To make a worksheet in workbook to be activated, the function set_active_layer can be used. 

// Access a worksheet by full name
Worksheet wks("[Book1]Sheet1");

// Set this worksheet to be active
set_active_layer(wks);

Delete Worksheet

Use the Destroy method to delete a worksheet. 

Worksheet wks = Project.ActiveLayer();
if( wks ) // If the active layer is a worksheet
    wks.Destroy(); // Delete the worksheet

Access Worksheets in Workbook

There are two ways to access a worksheet by its name. You can pass the layer's full name to the constructor or to the Attach method. The layer's full name contains the page name in square brackets followed by the layer name. 

// Assume wksPage is a valid WorksheetPage holding the sheet we want to access.
string strFullName = okutil_make_book_sheet_string(wksPage.GetName(), "Sheet1");

// If book and sheet name are known, the string can be constructed manually.
string strFullName = okutil_make_book_sheet_string("Book5", "Sheet1");

With the full layer name we can now access the worksheet. 

// Construct a new Worksheet instance and attach it to the named sheet.
Worksheet wks1(strFullName);

// Attach an existing Worksheet instance to the named sheet.
wks2.Attach(strFullName);

A workbook contains a collection of worksheets. You can loop through all the worksheets in a specified workbook using the foreach statement. 

WorksheetPage wksPage("Book1");
foreach(Layer wks in wksPage.Layers)
    out_str(wks.GetName());

You can also access a specified worksheet by its name or index. 

//assume there are at least two worksheets on the page Book1,
//and they are named Sheet1 and Sheet2 separately.
WorksheetPage wksPage("Book1");
Worksheet wksFirst = wksPage.Layers(0); //by index
Worksheet wksSecond = wksPage.Layers("Sheet2"); //by name

Reorder Worksheets

The Reorder method allows you to change the position of a worksheet in a workbook. 

// This example assumes the active workbook contains two sheets

// Get the active page from the active layer
WorksheetPage wksPage;
Worksheet wks = Project.ActiveLayer();
if( wks )
    wksPage = wks.GetPage();

// Move the 2nd worksheet to the 1st position
if( wksPage.Reorder(1, 0) )
    out_str("Reorder sheets successfully");

Copy Worksheet

The AddLayer method is used to copy a layer from one page to another, and can be used with GraphPage, WorksheetPage or MatrixPage.

The following example shows how to drag all worksheets from the active folder to merge into the active workbook. 

WorksheetPage wksPageDest = Project.Pages();
if( !wksPageDest ) // no active window or active window is not a worksheet
	return; 

bool bKeepSourceLayer = false; // delete source layer after copying
Folder fld = Project.ActiveFolder(); 
foreach(PageBase pb in fld.Pages)
{
    WorksheetPage wbSource(pb);
    if(!wbSource)
        continue;//not a workbook
    
    if(wbSource.GetName() == wksPageDest.GetName())
        continue;//skip our destination book
    
    // copy worksheet to destination book and delete it from source book
    foreach(Layer lay in wbSource.Layers)
    {
        Worksheet wks = lay;
        wksPageDest.AddLayer(wks, 0, bKeepSourceLayer);
    }
    wbSource.Destroy();// destroy the empty workbook
}

Format a Worksheet

A worksheet can be formatted programmatically using a theme tree. The example below demonstrates obtaining and saving an existing theme tree: 

// get format tree from worksheet
Worksheet wks = Project.ActiveLayer();

Tree tr;	
tr = wks.GetFormat(FPB_ALL, FOB_ALL, TRUE, TRUE);	
out_tree(tr); // Output tree to Script window

Or, you may construct a theme tree as in the following three steps. First, create a worksheet and insert some data: 

// Create worksheet
Worksheet wks;
wks.Create("Origin");	
wks.SetCell(0, 0, "abc"); // Put text to (0, 0) cell

// Establish data range to apply formatting:
DataRange dr;
int r1 = 0, c1 = 0, r2 = 4, c2 = 1;	
dr.Add("Range1", wks, r1, c1, r2, c2);

Second, construct the tree using the range information and provide values for desired properties: 

Tree tr;

// Fill color
tr.Root.CommonStyle.Fill.FillColor.nVal = SYSCOLOR_LTCYAN; 

// Alignment of text in cell, 2 for center
tr.Root.CommonStyle.Alignment.Horizontal.nVal = 2;

// The font size of text
tr.Root.CommonStyle.Font.Size.nVal = 11;

// The color of text
tr.Root.CommonStyle.Color.nVal = SYSCOLOR_BLUE;

Third, apply the formatting to the data range: 

// Apply the format to the specified data range
if( 0 == dr.UpdateThemeIDs(tr.Root) ) // Returns 0 for no error
{
	bool bRet = dr.ApplyFormat(tr, true, true);
}

Merge Cells

We can use Origin C code to merge Worksheet cells with the specified range. The selected range can be data area or column label area. If you want to merge label cells, just change bLabels to true in the following code. 

Worksheet wks;
wks.Create("Origin");

//Define a Grid and attach it to the worksheet
Grid gg;
gg.Attach(wks); 

// to merge the first two rows in two columns
ORANGE rng;
rng.r1 = 0;
rng.c1 = 0;
rng.r2 = 1;
rng.c2 = 1;

bool bLabels = false;
bool bRet = gg.MergeCells(rng, bLabels);

if( bRet )
	printf("Successfully merged cells in %s!\n", wks.GetName());
else
	printf("Failed to merge cells in %s!\n", wks.GetName());

Read Only Cells

If you don't want the contents in a cell of worksheet to be changed, you can set the cells to be read-only by using theme tree.

The example below shows how to set the data cells in worksheet to be read-only, and then change the second data cell in column 1 to be editable. 

// Create a worksheet by using default template (Origin)
// so to make sure that Long Name, Units, and Comments rows are shown
Worksheet wks;
wks.Create("Origin");

Tree tr;
tr = wks.GetFormat(FPB_ALL, FOB_ALL, true, true);  // Get theme tree of worksheet

// Start to get the specific tree node from the theme tree
// to set the read-only format for the data cells
string strName = "ogData";  // Use to get the node with the desired format
TreeNode trGrid, trNameStyles;
trGrid = tr.Root.Grid;  // Get Grid node
if(!trGrid.IsValid())
	return;

// Read-only format is under some child node of this node
trNameStyles = trGrid.NameStyles;  
if(!trNameStyles.IsValid())
	return;

TreeNode trNameStyle;
bool bRet = false;
// Loop all children nodes to find out the desired tree node
foreach(trNameStyle in trNameStyles.Children)
{
	// Find the node with "ogData"
	if(0 == trNameStyle.Name.strVal.Compare(strName))  
	{
		bRet = true;
		break;
	}
}
if(!bRet)
	return;

trNameStyle.Style.ReadOnly.nVal = 1;  // Set all data cells to be read-only

// Start to get/create the specific tree node from the theme tree
// to cancel the read-only format for the specified data cell
TreeNode trRangeStyles;
trRangeStyles = trGrid.RangeStyles;  // Get RangeStyles node from Grid node
TreeNode trRangeStyle;
if(!trRangeStyles.IsValid())  // If RangeStyles node does not exist yet
{
	// Create RangeStyles node
	trRangeStyles = trGrid.AddNode("RangeStyles");
	// And create a sub node named RangeStyle1
	trRangeStyle = trRangeStyles.AddNode("RangeStyle1");
}
else  // If RangeStyles node already exist
{
	// Find how many children nodes
	int tagNum = trRangeStyles.Children.Count();
	// And create a sub node name RangeStyle#, # = tagNum+1
	trRangeStyle = trRangeStyles.AddNode("RangeStyle"+(tagNum+1));
}
// Define the range for setting, here range is just one cell
// Left cell of the range, start from 1
trRangeStyle.Left.nVal = 1;  
// Top cell of the range, start from 5, including label rows
// there are 4 label rows, then 5 means the first data cell
trRangeStyle.Top.nVal = 5;  
// Just one cell, so right of the range is the same with left
trRangeStyle.Right.nVal = 1;
// Just one cell, so bottom of the range is the same with top
trRangeStyle.Bottom.nVal = 5;
trRangeStyle.Style.ReadOnly.nVal = 0;  // Set read-only to 0 to cancel it

// Apply the setting format to worksheet
if(0 == wks.UpdateThemeIDs(tr.Root))
{
	bool bb = wks.ApplyFormat(tr, true, true);
	if(bb)
	{
		printf("Cell 1 in column 1 is editable.\n");
	}
}

It is also able to set the Read-Only format for the cells in label rows. We can just make some simple changes on the code above. For example, we are going to make the Comments row to be read-only except the one in column 2, then the corresponding changes are like below. 

/* Comment out the line below in the above code
string strName = "ogData";
*/
// This line is for the Data, just change it for Comments, as following
string strName = "ogComment";

/* Comment out the following 4 lines in the above code
trRangeStyle.Left.nVal = 1; 
trRangeStyle.Top.nVal = 5;
trRangeStyle.Right.nVal = 1;
trRangeStyle.Bottom.nVal = 5;
*/
// These 4 lines are used to set for the second data 
// cell (assume 3 label rows displayed in worksheet)
// Now we need to set for the Comments cell, 
// assume the Comments row is the third row, 
// and is for column 2, but not column 1 anymore, then
trRangeStyle.Left.nVal = 
trRangeStyle.Right.nVal = 2;  // Column 2
// Comments row (the third row displayed in worksheet)
trRangeStyle.Top.nVal = 
trRangeStyle.Bottom.nVal = 3;
English | Deutsch | 日本語