Worksheets

xlsgen > overview > Worksheets

Worksheets are members of the workbook. Creating a new worksheet is a matter of calling the appropriate method from the workbook object :

Java code


XlsWorksheet wksht1 = wbk.AddWorksheet( "sheet1" );

// work with this worksheet
...

XlsWorksheet wksht2 = wbk.AddWorksheet( "sheet2" );

// work with this worksheet
...

VB code


Dim wksht1 As IXlsWorksheet
Set wksht1 = wbk.AddWorksheet( "sheet1" )

' work with this worksheet
...

Dim wksht2 As IXlsWorksheet
Set wksht2 = wbk.AddWorksheet( "sheet2" )

' work with this worksheet
...

C# code


IXlsWorksheet wksht1 = wbk.AddWorksheet( "sheet1" );

// work with this worksheet
...

IXlsWorksheet wksht2 = wbk.AddWorksheet( "sheet2" );

// work with this worksheet
...

C/C++ code


xlsgen::IXlsWorksheetPtr wksht1;
wksht1 = wbk->AddWorksheet( L"sheet1" );

// work with this worksheet
...

xlsgen::IXlsWorksheetPtr wksht2;
wksht2 = wbk->AddWorksheet( L"sheet2" );

// work with this worksheet
...

Caution : in the worksheet name, the following characters are forbidden : / \ : * [ ], and the name must be less than 32 characters long.

The programming interface of worksheets is described here. And worksheets are created from the IXlsWorkbook interface.

A worksheet exposes properties and methods which can be broken up in several parts :

simple cell content output, as described in cells.
object creation aimed to create a richer cell content, for instance merged cells, ranges.
formulas and conditional formatting
rows and columns
worksheet management
specific functions

The remainder of this page gets into details of worksheet management.

Reuse the content of an existing Excel file as is, including rich objects like charts, filters, pivot tables, VBA macros and so on
Import the content of worksheets and update their content
Add new worksheets to an Excel file, in addition to those imported from an existing Excel file
Add new worksheets before or after the current worksheet
Reorder the worksheets
Hide or delete one or more worksheets
Rename one or more worksheets
Duplicate one or more worksheets
Assign the default selected worksheet, and default selected cell range when an Excel workbook is opened
Define the page setup (print) options
Reuse existing ranges
Promote formatting reuse by extracting a style from an existing worksheet
Extract cell values to perform consistent updates of the data
Export the content of a worksheet as XML or CSV
Import a CSV file, with plenty of import options
Compute the dimensions of a worksheet
Autofit columns and rows
Freeze and unfreeze worksheet panes
Split and unsplit worksheet panes
Protect/Unprotect a worksheet
Remove VBA macros (workbook.DeleteMacros() removes all macros in the file)
Get the used cell range in a worksheet

Reuse the content of an existing Excel file

Reusing the content of an existing Excel file xlsgen implies the following steps :

use Excel to create a workbook and put everything you want to reuse. Note all this is done by hand and there is no limitation in what type of objects you create at this point. Just use Excel for what it's supposed to do, add data in cells, format those cells, add charts, add a VBA macro, ...Save the resulting file as usual, for instance let's name it MyResultingFile.xls.

start your code editor, it's now time to use xlsgen. Reusing an existing Excel file instead of creating one from scratch is obtained by using the Open(existingfilename, newfilename) method() rather than the New(newfilename) method. The Open() method call expects the fully qualified filepath of the Excel file to be passed as first parameter. Pass the name of the new Excel file as second parameter. The following code shows how it works :

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

...

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"c:\output\ResultingFile.xls" );

...

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"c:\\output\\ResultingFile.xls" );

  ...

  wbk->Close();
}

With this code, the resulting file ResultingFile.xls is an exact copy of the original file, nothing is changed or added. Any additional code will make changes to the resulting file, while preserving the original file intact.

The original file, and the resulting file

Please note that xlsgen does not prevent you to overwrite your original file, passing MyResultingFile.xls in both parameters of the Open() method in order to update your template file. Although it's generally considered a good habit to keep the template files untouched, its a scenario supported by xlsgen. Use it with caution since it may affect any automated processing chain that would use that template file somewhere else.

Now that we know how to import the content of an existing workbook, let's take a look at more features.

Import and update the content of worksheets

By adding code to the snippet above, we can update the content of existing worksheets, or create new regular worksheets and add content in those. One requirement is that you should always obtain a reference to that worksheet before you start changing the content. Below is an example that updates the content of an existing worksheet and adds content in a new worksheet. The assumption for that scenario to work is that the template file has two worksheets. You can check this by the way :

The original file, and the resulting file

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

' make sure that we have at least two worksheets
If wbk.WorksheetCount < 2 Then

  MsgBox "Error : the workbook has less than two worksheets."

Else

  ' update the content of the second worksheet
  Dim wksht As IXlsWorksheet
  Set wksht = wbk.WorksheetByIndex(2)

  wksht.Label(8, 1) = "Another Label"

  ' create a new worksheet and add some content
  Dim wksht_new As IXlsWorksheet
  Set wksht_new = wbk.AddWorksheet("Sheet3")

  wksht_new.Label(5, 3) = "Label in the new worksheet"

End If

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"c:\output\ResultingFile.xls" );

// make sure that we have at least two worksheets
if (wbk.get_WorksheetCount < 2)
{
  MessageBox.Show( "Error : the workbook has less than two worksheets.");
}
else
{ 

  // update the content of the second worksheet
  IXlsWorksheet wksht = wbk.get_WorksheetByIndex(2);

  wksht.set_Label(8,1, "Another Label");

  // create a new worksheet and add some content
  IXlsWorksheet wksht_new = wbk.AddWorksheet("Sheet3");

  wksht_new.set_Label(5,3, "Label in the new worksheet");

}

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"c:\\output\\ResultingFile.xls" );

  // make sure that we have at least two worksheets
  if (wbk->WorksheetCount < 2)
  {
    ::MessageBox(NULL, "the workbook has less than two worksheets.", "error", MB_OK);
  }
  else
  {

    // update the content of the second worksheet
    xlsgen::IXlsWorksheetPtr wksht;
    wksht = wbk->WorksheetByIndex[2];

    wksht->Label[8][1] = L"Another Label";

    // create a new worksheet and add some content
    xlsgen::IXlsWorksheetPtr wksht_new;
    wksht_new = wbk->AddWorksheet("Sheet3");

    wksht_new->Label[5][3] = L"Label in the new worksheet";

  }

  wbk->Close();
}

Reorder the worksheets

Changing the order of worksheets is a snap, just set the DisplayOrder worksheet property to the order in ascending order : the worksheet with index equals to 1 is displayed first, and so on. A sample code which changes the order of the second and third worksheet is as follows :

The original file, and the resulting file

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

Dim wksht_new As IXlsWorksheet
Set wksht_new = wbk.AddWorksheet("Sheet3")

wksht_new.DisplayOrder = 2

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"c:\output\ResultingFile.xls" );

// create a new worksheet and add some content
IXlsWorksheet wksht_new = wbk.AddWorksheet("Sheet3");

wksht_new.DisplayOrder = 2;

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"c:\\output\\ResultingFile.xls" );

  // create a new worksheet and add some content
  xlsgen::IXlsWorksheetPtr wksht_new;
  wksht_new = wbk->AddWorksheet("Sheet3");

  wksht_new->DisplayOrder = 2;

  wbk->Close();
}

You can reorder existing or new worksheets, according to your needs. Combined with the hide/delete method explained below, reordering is a powerful way to manage the requirement changes over time while keeping the same template files, i.e. capitalizing once for all.

Hide or delete worksheets

Hiding a worksheet is a regular Excel feature. Hiding and showing a worksheet is something you can change interactively in Excel with the Format / Sheet / Show-Hide menu option. xlsgen makes it easy to do this with code. Hiding a worksheet is a lossless operation, and the content of the impacted worksheet is still there, albeit not displayed. Formulas, or any cell reference to that particular worksheet will still work. Deleting a worksheet however is a more impacting change. Deleting a worksheet permanently removes its content. Make sure that no formulas or cell references target that worksheet. In case of doubt, hide the worksheet instead. The code below hides the first worksheet from the template, delete the second, and then creates a new worksheet :

The original file, and the resulting file

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

' hide the first worksheet
Dim wksht001 As IXlsWorksheet
Set wksht001 = wbk.WorksheetByIndex(1)

wksht001.Hidden = True

' permanently delete the second worksheet
Dim wksht002 As IXlsWorksheet
Set wksht002 = wbk.WorksheetByIndex(2)

wksht002.Delete

' create a new worksheet
Dim wksht_new As IXlsWorksheet
Set wksht_new = wbk.AddWorksheet("Sheet3")

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"c:\output\ResultingFile.xls" );

// hide the first worksheet
IXlsWorksheet wksht001 = wbk.get_WorksheetByIndex(1);
wksht001.Hidden = true;

// permanently delete the second worksheet
IXlsWorksheet wksht002 = wbk.get_WorksheetByIndex(2);
wksht002.Delete();

// create a new worksheet
IXlsWorksheet wksht_new = wbk.AddWorksheet("Sheet3");

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"c:\\output\\ResultingFile.xls" );

  // hide the first worksheet
  xlsgen::IXlsWorksheetPtr wksht001 = wbk->WorksheetByIndex[1];
  wksht001->Hidden = TRUE;

  // permanently delete the second worksheet
  xlsgen::IXlsWorksheetPtr wksht002 = wbk->WorksheetByIndex[2];
  wksht002->Delete();

  // create a new worksheet
  xlsgen::IXlsWorksheetPtr wksht_new = wbk->AddWorksheet("Sheet3");

  wbk->Close();
}

Please note that hiding or deleting a worksheet does not take effect immediately, this is the reason why the indexes used in the code above still reflect the original worksheets. Hiding or deleting worksheets takes effect in the Close statement of the generated workbook.

Duplicate the content of one or more worksheets

Besides adding and deleting worksheets, you can duplicate them. You can duplicate a worksheet of the current workbook, or a worksheet from another workbook.

Duplicating a worksheet from the current workbook

This scenario lets you create a new worksheet in the current workbook based on the content of another. Calling the method requires you provide the name of the new worksheet to be created, whose only requirement is that it should not conflict with existing worksheet names.

VB code

...
' duplicate content of worksheet 'wksht' in a new worksheet "new worksheet"
'  the worksheet is added at the tail of existing worksheets
'  (you can use the DisplayOrder property to reorder the worksheets according to your needs)
wksht.Duplicate("new worksheet")
...

Duplicating a worksheet from another workbook

Duplicating a worksheet from another workbook is the scenario which imports an external worksheet. For that to work, the method call only requires you to pass the worksheet to be imported.

VB code


Dim wbk As IXlsWorkbook
Set wbk = engine.Open("existingfile.xls", "")

Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByName("Sheet2")

...

Dim workbook As IXlsWorkbook
Set workbook = engine.New("newfile.xls")

Dim wkshtNew As IXlsWorksheet
Set wkshtNew = workbook.AddWorksheet("MySheet")

wkshtNew.DuplicateFrom wksht

...

wbk.Close

workbook.Close

Known limitation for duplicating content from external worksheets : some objects are not duplicated. This includes, at the time of writing, rich objects such as pivot tables. This limitation will be removed in future releases. This limitation does not exist when duplicating content from worksheets of the same workbook.

Rename worksheets

The ability to rename worksheets enables custom scenarios where for instance the worksheets themselves are known in the template file, but their final names are unknown and are dynamically computed, resulting in names that reflect the content of the current worksheet. The code below renames the first worksheet :

The original file, and the resulting file

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

' rename the first worksheet
Dim wksht001 As IXlsWorksheet
Set wksht001 = wbk.WorksheetByIndex(1)
wksht001.Name = "August 13 2004"

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"c:\output\ResultingFile.xls" );

// rename the first worksheet
IXlsWorksheet wksht001 = wbk.get_WorksheetByIndex(1);
wksht001.Name = "August 13 2004";

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"c:\\output\\ResultingFile.xls" );

  // rename the first worksheet
  xlsgen::IXlsWorksheetPtr wksht001 = wbk->WorksheetByIndex[1];
  wksht001->Name = "August 13 2004";

  wbk->Close();
}

Assign the default selected worksheet, and default selected cell range when an Excel workbook is opened

If you create a file from an existing file, then the resulting file will display in Excel the same default worksheet, unless the worksheet is hidden or deleted. It is possible to select which worksheet is selected when the workbook is opened.

By default, the worksheet will select cell[1,1] located in the top-left corner. It is also possible to change this. In the example below, the second worksheet is selected. The same code also selects a particular range of that worksheet.

The original file, and the resulting file

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

' select the second worksheet, and define the current cell range
Dim wksht002 As IXlsWorksheet
Set wksht002 = wbk.WorksheetByIndex(2)
wksht002.Selected = True
wksht002.Select( 11, 1, 11, 2 )

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"c:\output\ResultingFile.xls" );

// select the second worksheet, and define the current cell range
IXlsWorksheet wksht002 = wbk.get_WorksheetByIndex(2);
wksht002.Selected = true;
wksht002.Select( 11, 1, 11, 2 );

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"c:\\output\\ResultingFile.xls" );

  // select the second worksheet, and define the current cell range
  xlsgen::IXlsWorksheetPtr wksht002 = wbk->WorksheetByIndex[2];
  wksht002->Selected = TRUE;
  wksht002->Select( 11, 1, 11, 2 );

  wbk->Close();
}

Import

You can import a CSV file simply by passing a .CSV file as an argument to the Open() method, or use the flexible import options from the worksheet object. For a thorough explanation of how this works, see here.

Export

Saving the content of a worksheet inside a .xls file is only one option. Other options include the ability to save the content as XML (angle brackets, fixed schema) and as CSV (semi-colon separated values). For a thorough explanation of how this works, see here.

Computing the dimensions of a worksheet

There is a built-in dimension properties to query whenever you need to know the exact boundaries of a worksheet. Those properties are updated in real-time so you don't need to be at a particular stage only to query those. Needless to say, having those properties simply cuts the need for row/column iterations. See the IXlsWorksheetDimensions interface. Here is how to do this :

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

' get the second worksheet
Dim wksht002 As IXlsWorksheet
Set wksht002 = wbk.WorksheetByIndex(2)

' compute dimensions
Dim left
left = wksht002.Dimensions.FirstColumn

Dim right
right = wksht002.Dimensions.LastColumn

Dim top
top = wksht002.Dimensions.FirstRow

Dim bottom
bottom = wksht002.Dimensions.LastRow

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"" );

// get the second worksheet
IXlsWorksheet wksht002 = wbk.get_WorksheetByIndex(2);

// compute dimensions
int left   = wksht002.Dimensions.FirstColumn;
int right  = wksht002.Dimensions.LastColumn;
int top    = wksht002.Dimensions.FirstRow;
int bottom = wksht002.Dimensions.LastRow;

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"" );

  // get the second worksheet
  xlsgen::IXlsWorksheetPtr wksht002 = wbk->WorksheetByIndex[2];

  // compute dimensions
  int left   = wksht002->Dimensions->FirstColumn;
  int right  = wksht002->Dimensions->LastColumn;
  int top    = wksht002->Dimensions->FirstRow;
  int bottom = wksht002->Dimensions->LastRow;

  wbk->Close();
}

Freeze / Unfreeze panes

Freezing a worksheet pane

Freeing a pane allows to scroll the worksheet while keeping a part of it visible. In Excel, this feature is available from the Windows menu. You can freeze horizontally or vertically. In the example above, it's a horizontal freeze. See IXlsWorksheetPane interface. Here is how to achieve this :

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

' get the first worksheet
Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByIndex(1)

' freeze rows 1 to 3. Let rows 4 and downwards free to be scrolled
wksht.HorizontalPane.SplitLimit = 4
wksht.HorizontalPane.Freeze = True
' in order to unfreeze, set Freeze = False

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"" );

// get the first worksheet
IXlsWorksheet wksht = wbk.get_WorksheetByIndex(1);

// freeze rows 1 to 3. Let rows 4 and downwards free to be scrolled
wksht.HorizontalPane.SplitLimit = 4;
wksht.HorizontalPane.Freeze = 1; // true
//   in order to unfreeze, set Freeze = 0

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"" );

  // get the first worksheet
  xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

  // freeze rows 1 to 3. Let rows 4 and downwards free to be scrolled
  wksht->HorizontalPane->SplitLimit = 4;
  wksht->HorizontalPane->Freeze = TRUE;
  //   in order to unfreeze, set Freeze = FALSE

  wbk->Close();
}

Likewise you can unfreeze a frozen pane by passing the same argument than for a freeze.

The reference documentation for the freeze interface is here.

Split / Unsplit panes

Splitting worksheet panes in both directions

Whenever you need more than one view of the current worksheet, you can split it in pieces, horizontally and vertically. When a pane is split, it has his own cursor and thus parts of the worksheet can be visible in several panes at the same time, just as in the screen capture above.

Programmatically accessing the panes is very easy, thanks to the HorizontalPane and VerticalPane objects available at the worksheet object level, and then splitting is a matter of expliciting where the split should be made, optionally what is the first row or column that should be visible in the pane, and eventually set the Split property to true. See IXlsWorksheetPane interface.

Likewise, unsplitting in one direction or both is achieved by setting the Split property to false. Here is a sample code :

VB code

Dim engine As CoXlsEngine
Set engine = CreateObject("ExcelGenerator.ARsTDesign")

Dim wbk As IXlsWorkbook
Set wbk = engine.Open("c:\input\MyExistingFile.xls", "c:\output\ResultingFile.xls")

' get the first worksheet
Dim wksht As IXlsWorksheet
Set wksht = wbk.WorksheetByIndex(1)

' split vertically at column H
wksht.VerticalPane.SplitLimit = 8
wksht.VerticalPane.FirstVisibleItemAfterSplit = 8
wksht.VerticalPane.Split = True
'   in order to unsplit, set Split = False

wbk.Close

Set engine = Nothing

C# code

xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();

IXlsWorkbook wbk = excel.Open( @"c:\input\MyExistingFile.xls", @"" );

// get the first worksheet
IXlsWorksheet wksht = wbk.get_WorksheetByIndex(1);

// split vertically at column H
wksht.VerticalPane.SplitLimit = 8;
wksht.VerticalPane.FirstVisibleItemAfterSplit = 8;
wksht.VerticalPane.Split = 1; // true
//   in order to unsplit, set Split = 0

wbk.Close();
excel = null;

C/C++ code

{
  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->Open(L"c:\\input\\MyExistingFile.xls", L"" );

  // get the first worksheet
  xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[1];

  // split vertically at column H
  wksht->VerticalPane->SplitLimit = 8;
  wksht->VerticalPane->FirstVisibleItemAfterSplit = 8;
  wksht->VerticalPane->Split = TRUE;
  //   in order to unsplit, set Split = FALSE

  wbk->Close();
}

Protect/Unprotect a worksheet

You can protect a worksheet thanks to the lock properties. This topic is further explained here.