EasyXLS™ library allows you to read merge areas or to merge cells in Excel spreadsheet. Also, you can apply other formatting options like wrap text in cells or to shrink the text to fit the cell.
For better performances, the best option is to apply the wrap and shrink to fit properties to the style of the cell, row, or column by using ExcelStyle class. But the formatting can also be applied directly to the cell, row, or column.
Merging cells means combing two or more cells in one single cell. The text inside the first cell is spread over all the merged cells. The value from the merge area is kept in the cell from the left-upper corner of the area. The below example shows how to export an Excel file with merge cells.
// Create an instance of the class that exports Excel files having one sheet
ExcelDocument workbook = new ExcelDocument(1);
// Get the table of data for the worksheet
ExcelTable xlsTable =
((ExcelWorksheet)workbook.easy_getSheet("Sheet1")).easy_getExcelTable();
// Merge cells
xlsTable.easy_mergeCells("A1:C3");
// Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Merge cells.xlsx");
' Create an instance of the class that exports Excel files having one sheetDim workbook As New ExcelDocument(1)
' Get the table of data for the worksheetDim xlsFirstTab As ExcelWorksheet = workbook.easy_getSheet("Sheet1")
Dim xlsTable = xlsFirstTab.easy_getExcelTable()
' Merge cells
xlsTable.easy_mergeCells("A1:C3")
' Export Excel file
workbook.easy_WriteXLSXFile("C:\Samples\Merge cells.xlsx")
C++// Create an instance of the class that exports Excel files
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
NULL,
CLSCTX_ALL,
__uuidof(EasyXLS::IExcelDocument),
(void**) &workbook) ;
// Create one sheet
workbook->easy_addWorksheet_2("Sheet1");
// Get the table of data for the worksheet
EasyXLS::IExcelWorksheetPtr xlsFirstTab =
(EasyXLS::IExcelWorksheetPtr)workbook->easy_getSheet("Sheet1");
EasyXLS::IExcelTablePtr xlsTable = xlsFirstTab->easy_getExcelTable();
// Merge cells
xlsTable->easy_mergeCells_2("A1:C3");
// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Merge cells.xlsx");
Click here to see Continuous Code ListingC++.NET// Create an instance of the class that exports Excel files having one sheet
ExcelDocument ^workbook = gcnew ExcelDocument(1);
// Get the table of data for the worksheet
ExcelWorksheet ^xlsFirstTab =
safe_cast<ExcelWorksheet^>(workbook->easy_getSheet("Sheet1"));
ExcelTable ^xlsTable = xlsFirstTab->easy_getExcelTable();
// Merge cells
xlsTable->easy_mergeCells("A1:C3");
// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Merge cells.xlsx");
Click here to see Continuous Code Listing
// Create an instance of the class that exports Excel files having one sheet
ExcelDocument workbook = new ExcelDocument(1);
// Get the table of data for the worksheet
ExcelTable xlsTable =
((ExcelWorksheet)workbook.easy_getSheet("Sheet1")).easy_getExcelTable();
// Merge cells
xlsTable.easy_mergeCells("A1:C3");
// Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Merge cells.xlsx");
COM+:// Create an instance of the class that exports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");
// Create one sheet
$workbook->easy_addWorksheet_2("Sheet1");
// Get the table of data for the worksheet
$xlsTable = $workbook->easy_getSheet("Sheet1")->easy_getExcelTable();
// Merge cells
$xlsTable->easy_mergeCells_2("A1:C3");
// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Merge cells.xlsx");
Click here to see Continuous Code ListingJava:// Create an instance of the class that exports Excel files
$workbook = new java("EasyXLS.ExcelDocument");
// Create a worksheet
$workbook->easy_addWorksheet("Sheet1");
// Get the table of data for the worksheet
$xlsTable = $workbook->easy_getSheet("Sheet1")->easy_getExcelTable();
// Merge cells by range
$xlsTable->easy_mergeCells("A1:C3");
// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Merge cells.xlsx");
Click here to see Continuous Code Listing
' Create an instance of the class that exports Excel filesset workbook = Server.CreateObject("EasyXLS.ExcelDocument")
' Create one sheet
workbook.easy_addWorksheet_2("Sheet1")
' Get the table of data for the worksheetset xlsTable = workbook.easy_getSheet("Sheet1").easy_getExcelTable()
' Merge cells
xlsTable.easy_mergeCells_2("A1:C3")
' Export Excel file
workbook.easy_WriteXLSXFile ("C:\Samples\Merge cells.xlsx")
' Create an instance of the class that exports Excel filesSet workbook = CreateObject("EasyXLS.ExcelDocument")
' Create one sheet
workbook.easy_addWorksheet_2 ("Sheet1")
' Get the table of data for the worksheetSet xlsTable = workbook.easy_getSheet("Sheet1").easy_getExcelTable()
' Merge cells
xlsTable.easy_mergeCells_2 ("A1:C3")
' Export Excel file
workbook.easy_WriteXLSXFile ("C:\Samples\Merge cells.xlsx")
' Create an instance of the class that exports Excel filesSet workbook = CreateObject("EasyXLS.ExcelDocument")
' Create one sheet
workbook.easy_addWorksheet_2("Sheet1")
' Get the table of data for the worksheetSet xlsTable = workbook.easy_getSheet("Sheet1").easy_getExcelTable()
' Merge cells
xlsTable.easy_mergeCells_2("A1:C3")
' Export Excel file
workbook.easy_WriteXLSXFile ("C:\Samples\Merge cells.xlsx")
<!-- Create an instance of the class that exports Excel files --><cfobject type="java"class="EasyXLS.ExcelDocument"name="workbook"action="CREATE"><!-- Create one sheet --><cfset ret = workbook.easy_addWorksheet("Sheet1")><!-- Get the table of data for the worksheet --><cfset xlsTable = workbook.easy_getSheet("Sheet1").easy_getExcelTable()><!-- Merge cells --><cfset xlsTable.easy_mergeCells("A1:C3")><!-- Export Excel file --><cfset ret = workbook.easy_WriteXLSXFile("C:\Samples\Merge cells.xlsx")>
.NET:# Create an instance of the class that exports Excel files
workbook = ExcelDocument(1)
# Get the table of data for the worksheet
xlsTable = workbook.easy_getSheet("Sheet1").easy_getExcelTable()
# Merge cells by range
xlsTable.easy_mergeCells("A1:C3")
# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Merge cells.xlsx")
Click here to see Continuous Code ListingJava:# Create an instance of the class that exports Excel files
workbook = gateway.jvm.ExcelDocument(1)
# Get the table of data for the worksheet
xlsTable = workbook.easy_getSheet("Sheet1").easy_getExcelTable()
# Merge cells by range
xlsTable.easy_mergeCells("A1:C3")
# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Merge cells.xlsx")
Click here to see Continuous Code Listing
The screen shot below represents the exported Excel file with merged cells generated by the code sample above. The worksheet has merged cells from A1 to C3.
EasyXLS allows to wrap the cell text in multiple lines to fit the column width when the cell text is too long to fit on one line. The below source code sample shows how to apply Wrap property to a cell style from the Excel file.
// Create a style for the cell
ExcelStyle xlsStyle = new ExcelStyle();
xlsStyle.setWrap(true);
// Apply the style to the cell
xlsCell.setStyle(xlsStyle);
' Create a style for the cellDim xlsStyle As New ExcelStyle()
xlsStyle.setWrap(True)
' Apply the style to the cell
xlsCell.setStyle(xlsStyle)
C++// Create a style for the cell
EasyXLS::IExcelStylePtr xlsStyle;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelStyle),
NULL,
CLSCTX_ALL,
__uuidof(EasyXLS::IExcelStyle),
(void**) &xlsStyle);
xlsStyle->setWrap(true);
// Apply the style to the cell
xlsCell->setStyle(xlsStyle);
C++.NET// Create a style for the cell
ExcelStyle ^xlsStyle = gcnew ExcelStyle();
xlsStyle->setWrap(true);
// Apply the style to the cell
xlsCell->setStyle(xlsStyle);
// Create a style for the cell
ExcelStyle xlsStyle = new ExcelStyle();
xlsStyle.setWrap(true);
// Apply the style to the cell
xlsCell.setStyle(xlsStyle);
COM+:// Create a style for the cell
$xlsStyle = new COM("EasyXLS.ExcelStyle");
$xlsStyle->setWrap(true);
// Apply the style to the cell
$xlsCell->setStyle($xlsStyle);
Java:// Create a style for the cell
$xlsStyle = new java("EasyXLS.ExcelStyle");
$xlsStyle->setWrap(true);
// Apply the style to the cell
$xlsCell->setStyle($xlsStyle);
' Create a style for the cellset xlsStyle = Server.CreateObject("EasyXLS.ExcelStyle")
xlsStyle.setWrap(true)
' Apply the style to the cell
xlsCell.setStyle(xlsStyle)
' Create a style for the cellSet xlsStyle = CreateObject("EasyXLS.ExcelStyle")
xlsStyle.setWrap(True)
' Apply the style to the cell
xlsCell.setStyle(xlsStyle)
' Create a style for the cellSet xlsStyle = CreateObject("EasyXLS.ExcelStyle")
xlsStyle.setWrap(true)
' Apply the style to the cell
xlsCell.setStyle(xlsStyle)
<!-- Create a style for the cell --><cfobject type="java"class="EasyXLS.ExcelStyle"name="xlsStyle"action="CREATE"><cfset xlsStyle.setWrap(true)><!-- Apply the style to the cell --><cfset ret = xlsCell.setStyle(xlsStyle)>
.NET:# Create a style for the cell
xlsStyle = ExcelStyle()
xlsStyle.setWrap(True)
# Apply the style to the cell
xlsCell.setStyle(xlsStyle)
Java:# Create a style for the cell
xlsStyle = gateway.jvm.ExcelStyle()
xlsStyle.setWrap(True)
# Apply the style to the cell
xlsCell.setStyle(xlsStyle)
