[![Aspose.Cells for .NET logo][1]. Do you want to know all exciting features and other enhancements? Here you go.

Autofit Rows for Merged Cells

Autofitting rows is a very common operation which you perform while working with the Excel files. This feature was already there in Aspose.Cells API however many people asked for more control over this operation. To fulfill this requirement we have provided [AutoFitMergedCellsType][2] enumerator which contains the following options:

  • None: Ignore merged cells.
  • FirstLine: Only expands the height of the first row.
  • LastLine: Only expands the height of the last row.
  • EachLine: Expands the height of each row.

Here is a snapshot of this feature where the effect of one of the options EechLine is shown.

autofit rows in merged cells in Excel

You can test this feature using following sample code.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
//Output directory
string outputDir = RunExamples.Get_OutputDirectory();
// Instantiate a new Workbook
Workbook wb = new Workbook();
// Get the first (default) worksheet
Worksheet _worksheet = wb.Worksheets[0];
// Create a range A1:B1
Range range = _worksheet.Cells.CreateRange(0, 0, 1, 2);
// Merge the cells
range.Merge();
// Insert value to the merged cell A1
_worksheet.Cells[0, 0].Value = "A quick brown fox jumps over the lazy dog. A quick brown fox jumps over the lazy dog....end";
// Create a style object
Aspose.Cells.Style style = _worksheet.Cells[0, 0].GetStyle();
// Set wrapping text on
style.IsTextWrapped = true;
// Apply the style to the cell
_worksheet.Cells[0, 0].SetStyle(style);
// Create an object for AutoFitterOptions
AutoFitterOptions options = new AutoFitterOptions();
// Set auto-fit for merged cells
options.AutoFitMergedCellsType = AutoFitMergedCellsType.EachLine;
// Autofit rows in the sheet(including the merged cells)
_worksheet.AutoFitRows(options);
// Save the Excel file
wb.Save(outputDir + "AutofitRowsforMergedCells.xlsx");

For more details on this topic please visit [here][3].

Convert Table to Range with Options

Conversion of the table to a range was available earlier. However what if you want to control the formatting of destination range like formatting only the partial output. The good news is that this feature is available now and you don’t need to write extra code to format the output range. We have introduced a new class TableToRangeOptions where LastRow property is available to set the last row to which formatting is copied from the source table. Here is the view of a sample table and converted range which is formatted up to row 5.

Autofit rows in Excel

Here is a sample code which is used to create the above range.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Open an existing file that contains a table/list object in it
Workbook workbook = new Workbook(dataDir + "book1.xlsx");
TableToRangeOptions options = new TableToRangeOptions();
options.LastRow = 5;
// Convert the first table/list object (from the first worksheet) to normal range
workbook.Worksheets[0].ListObjects[0].ConvertToRange(options);
// Save the file
workbook.Save(dataDir + "output.xlsx");

For detailed information on conversions follow [this][4] link.

Import Data from JSON

As you know that JavaScript Object Notation is a common data interchange format which is quite lightweight and can be read by human and machines easily. Now, for example, you get data from some Web API in JSON format and need it to be imported into some Excel file, then Aspose.Cells is there to provide this facility.

JSONUtility class is introduced by Aspose.Cells for this purpose having ImportData method that not only imports data but also performs different conversions and formatting using the JsonLayoutOptions object during the import process. You may try this exciting feature by following article [Importing Data from JSON][5]. Here is a sample code to use this feature:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
// Read File
string jsonInput = File.ReadAllText(dataDir + "Test.json");
// Set Styles
CellsFactory factory = new CellsFactory();
Style style = factory.CreateStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Color = System.Drawing.Color.BlueViolet;
style.Font.IsBold = true;
// Set JsonLayoutOptions
JsonLayoutOptions options = new JsonLayoutOptions();
options.TitleStyle = style;
options.ArrayAsTable = true;
// Import JSON Data
JsonUtility.ImportData(jsonInput, worksheet.Cells, 0, 0, options);
// Save Excel file
workbook.Save(dataDir + "ImportingFromJson.out.xlsx");

You can see that result is cool as fully formatted Excel file is created without using any extra code to parse the JSON string from any source like Web API. Isn’t it? For example, if you get the following string from Web API, then output Excel file is shown in the image below:

[{“color”: “red”,“value”: “#f00”},{“color”: “green”,“value”: “#0f0”},{“color”: “blue”,“value”: “#00f”},{“color”: “cyan”,“value”: “#0ff”},{“color”: “magenta”,“value”: “#f0f”},{“color”: “yellow”,“value”:"#ff0"},{“color”: “black”,“value”: “#000”}]

Microsoft Excel supports external links for fetching data from different sources. We have provided the option to retrieve these links from the Excel file using Name.GetRefferedAreas method. This method returns ReferredArea which has many useful properties as follows:

  • EndColumn: The end column of the area
  • EndRow: The end row of the area
  • ExternalFileName: Get the external file name if this is an external reference
  • IsArea: Indicates whether this is an area
  • IsExternalLink: Indicates whether this is an external link
  • SheetName: Indicates which sheet this reference is in
  • StartColumn: The start column of the area
  • StartRow: The start row of the area

You may find details on how to [get range with external links][6] for your reference. In the following example, an external file having named range “Names” is linked with the Excel file. We can access this named range in the linked file and display its properties mentioned above.

Keep Separators for Blank Rows During Spreadsheet to CSV Conversion

Many times you convert the Excel sheets to CSV for using it in some other environment or applications. You may need to decide yourself about the separators for blank rows in the spreadsheet while exporting it to CSV. For example, there is a blank row in the source spreadsheet and you want either a blank row in the CSV or have a row with predefined separators. We have provided this feature now and you can get details about this feature [here][7].

For better understanding, have a look at the following image which shows the result of this feature.

Import Data from JSON to Excel

Following is a sample code which demonstrates this new feature.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
string filePath = dataDir + "Book1.xlsx";
// Create a Workbook object and opening the file from its path
Workbook wb = new Workbook(filePath);
// Instantiate Text File's Save Options
TxtSaveOptions options = new TxtSaveOptions();
// Set KeepSeparatorsForBlankRow to true show separators in blank rows
options.KeepSeparatorsForBlankRow = true;
// Save the file with the options
wb.Save(dataDir + "output.csv", options);

To use these useful features and avail other enhancements, I recommend you to try the release [Aspose.Cells for .NET v19.3][8]. And, if you have more time, browse [Developers’ Guide][9] for your complete reference on what you can deliver using the rendering extension API. You are always welcome to share your review, concerns or feedback on [forums][10].

[1]: https://blog.aspose.com/wp-content/uploads/sites/2/2013/08/aspose-Cells-for-net_100.png “Aspose.Cells for .NET logo”)](https://www.aspose.com/products/cells/net “Aspose.Cells for .NET API”)Guys, are you ready? Before we dive into its details: I want you to get an essence of the public release. I am giving you little preview of the new features and other enhancements available in the release for quick reference. I guess you know the valuable usage of MS Excel’s auto-fit rows/cols feature. Yet, you are one step ahead and perform the operation on merged cells by Aspose.Cells. While working in MS Excel IDE, you might not dislike formatted Tables but sometimes you need to convert to raw range. Here comes the library to accomplish these tasks via the APIs. Some of you might also like importing from JSON. So let’s not wait another moment to review the [release notes](https://docs.aspose.com/display/cellsnet/Aspose.Cells+for+.NET+19.3+Release+Notes [2]: https://reference.aspose.com/net/cells/aspose.cells/autofitmergedcellstype [3]: https://docs.aspose.com/display/cellsnet/Autofit+Rows+and+Columns#AutoFitRowsandColumns-AutoFitRowsforMergedCells [4]: https://docs.aspose.com/display/cellsnet/Tables+and+Ranges#TablesandRanges-ConvertTabletoRangewithOptions [5]: https://docs.aspose.com/display/cellsnet/Import+Data+into+Worksheet#ImportDataintoWorksheet-ImportingDatafromJSON [6]: https://docs.aspose.com/display/cellsnet/Get+Range+with+External+Links [7]: https://docs.aspose.com/display/cellsnet/Keep+Separators+for+Blank+Rows+while+exporting+spreadsheets+to+CSV+format [8]: https://downloads.aspose.com/cells/net/new-releases/aspose.cells-for-.net-19.3/ [9]: https://docs.aspose.com/display/cellsnet/Developer+Guide [10]: https://forum.aspose.com/c/cells