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 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);
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);
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
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
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");
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 }
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); }
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());
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;