Learn more about Xeed Workbooks for .NET

We conclude our overview of the changes in Workbooks for .NET v1.2.

Merging and unmerging cells

The ability to merge and unmerge cells was added. It can either be done by using the Add() and RemoveAt() methods on the Worksheet’s MergedCells property, or with the MergeCells() and UnmergeCells() methods on the CellRange to merge/unmerge

The MergedCells property on a Worksheet is a MergedCellCollection, which represents a collection of all the cell ranges that need to be merged.

The MergedCellCollection class has the following properties:

• Count: the number of CellRange in the current collection (read-only)
• Item: the CellRange object that represents all the Cells that will be merged at the given index (read-only)

The MergedCellCollection class has the following methods:

• Add: adds a CellRange element in the collection
• RemoveAt: Removes an element from the collection

The Add() method has 2 overloads available, to specify the start and end cells by either cell address or by using the row and column ids:

• Add(startingCellAddress, endingCellAddress, isCenter, isMergeAcross)
• Add(startingRowId, startingColumnId, endingRowId, endingColumnId, isCenter, isMergeAcross)

// Using Worksheet.MergedCells
using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];

	// Merge a range by adding it to the collection, using the cell address
	worksheet.MergedCells.Add("B5", "C8", true, false);

	// Merge a range (B10:C13) by adding it to the collection, using the cell IDs this time
	worksheet.MergedCells.Add(9, 1, 12, 2, true, false);

	// Unmerging the first range currently in the collection
	worksheet.MergedCells.RemoveAt(0);

	// save the modifications
	document.Save();
}

The MergeCells() method on the CellRange class has the following parameters:

• isCenter: centers the text horizontally and vertically; true by default.
• isAcross: indicates if the merge is split per Row, for example "A1 : B2" will be merged as "A1: B1" and "A2 : B2"; false by default.

// Using the CellRange directly
using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];

	// Merging
	CellRange range1 = worksheet.Cells[ "B5, C8" ];
	range1.MergeCells();

	// Unmerging
	CellRange range2 = worksheet.Cells[ "E5, F8" ];
	range2.UnmergeCells();

	// save the modifications
	document.Save();
}

Wrap the content of a cell

The IsTextWrapped property was added on the Alignment class, to specify if the content of a cell is wrapped or not.

// Wrapping the content of a cell
using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];
	worksheet.Cells[ "C12" ].Style.Alignment.IsTextWrapped = true;

	// save the modifications
	document.Save();
}

Modify more properties on Picture

The Picture class previously only had the Description, DrawingClientData and PictureLocks properties, and was expanded to include more properties.

The Picture class now also has the following properties:

• AbsolutePosition: the absolute position of the Picture in the Worksheet, this value must be positive and is of type Position.
Note that setting this value will clear the value of AnchorPosition, as only one or the other can be used.
• AnchorPosition: the Anchor's position in the Worksheet, this value is of type CellRange.
Note that setting this value will clear the value of AbsolutePosition, as only one or the other can be used.
• AnchorType: the Anchor's type; can be either an AbsoluteAnchor, a OneCellAnchor or a TwoCellAnchor. (read-only)
• BottomRightOffsets: offsets the bottom right corner of a TwoCellAnchor Picture.
• Edit: affects the behavior of a TwoCellAnchor Picture when adding and deleting Rows and Columns using Excel's user interface.
• Format: the file format of the uploaded media. (read-only)
• Height: the height of an AbsoluteAnchor or OneCellAnchor Picture.
• MeasureUnit: the unit used for measuring the Width and Height of the Picture.
• Name: the name of the Picture in the Worksheet (must be unique across the whole Workbook).
• TopLeftOffsets: offsets the top left corner of a TwoCellAnchor Picture.
• Width: the width of an AbsoluteAnchor or OneCellAnchor Picture.

Importing Data

Data can now be imported by using the ImportData() method on the Worksheet class.

The ImportData() method supports data of the following Types:

• Array
• 2D-Array
• ArrayList
• ICollection
• IDictionary
• DataTable
• DataView
• CSV path/stream

The ImportData() method has the following parameters:

• data: the data to import.
• options: the options when importing the data

The options can be of the following types:

• ImportOptions: import option properties when importing data in a Worksheet
base class for DataTableImportOptions, UserObjectImportOptions and CSVImportOptions.
• UserObjectImportOptions: import options for user objects when importing data in a Worksheet
derives from ImportOptions, useful when importing user object data, like a List, a Player[] or an Array
• DataTableImportOptions: import options for DataTables when importing data in a Worksheet
derives from ImportOptions, useful when importing a DataTable
• CSVImportOptions: import options for a CSV document or stream when importing data in a Worksheet
derives from ImportOptions, useful when importing CSV data

ImportOptions

using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];

	// Define a list of strings, the import options(vertical by default) and call the ImportData function.
	var stringData = new List() { "First", "Second", "Third", "Fourth" };
	var stringImportOptions = new ImportOptions() { DestinationTopLeftAddress = "B5" };
	worksheet.ImportData( stringData, stringImportOptions );

	// save the modifications
	document.Save();
}

UserObjectImportOptions

using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];

	// Define a list of user objects, the import options (vertical by default, specify PropertyNames and show propertyNames) and call the ImportData function.
	var userObjectData = new List()
	{
		new Player() { Name = "Tom Sawyer", Team = Team.Miami_Ducks, Number = 9 },
		new Player() { Name = "Mike Smith", Team = Team.Chicago_Hornets, Number = 18 },
		new Player() { Name = "Kelly Tomson", Team = Team.LosAngelese_Raiders, Number = 33 },
		new Player() { Name = "John Graham", Team = Team.NewYork_Bucs, Number = 7 },
	};

	var userObjectImportOptions = new UserObjectImportOptions() { DestinationTopLeftAddress = "H5", PropertyNames = new string[] { "Name", "Team" }, IsPropertyNamesShown = true };
	worksheet.ImportData( userObjectData, userObjectImportOptions );

	// save the modifications
	document.Save();
}

DataTableImportOptions

using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];

	// Define a dataTable, the import options(show specific ColumnNames) and call the ImportData function.
	var dataTable = new DataTable( "Employees" );
	dataTable.Columns.Add( "Name", typeof( string ) );
	dataTable.Columns.Add( "Position", typeof( string ) );
	dataTable.Columns.Add( "Experience", typeof( double ) );
	dataTable.Columns.Add( "Salary", typeof( int ) );
	dataTable.Rows.Add( "Jenny Melchuck", "Project Manager", 11.5d, 77000 );
	dataTable.Rows.Add( "Cindy Gartner", "Medical Assistant", 1.3d, 56000 );
	dataTable.Rows.Add( "Carl Jones", "Web Designer", 4d, 66000 );
	dataTable.Rows.Add( "Anna Karlweiss", "Account Executive", 7.8d, 51000 );
	dataTable.Rows.Add( "Julia Robertson", "Marketing Coordinator", 17.6d, 65000 );

	var dataTableImportOptions = new DataTableImportOptions() { DestinationTopLeftAddress = "B5", ColumnNames = new string[] { "Name", "Experience", "Position" }, IsColumnNamesShown = true };
	worksheet.ImportData( dataTable, dataTableImportOptions );

	// save the modifications
	document.Save();
}

CSVImportOptions

using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];

	// Define a path to a csv document, the import options(which separator to use) and call the ImportData function.
	var stringSCVData = "Book1.csv";
	var stringCSVImportOptions = new CSVImportOptions() { DestinationTopLeftAddress = "C5", Separator = "," };
	worksheet.ImportData( stringSCVData, stringCSVImportOptions );

	// save the modifications
	document.Save();
}

For more information, please refer to the documentation.