Amjad Sahi March 28, 20180 Comments

Access and modify the display label of the linked Ole Object and group pivot fields in the Pivot Table using Aspose.Cells for .NET 18.3

Access and modify the display label of the linked Ole Object and group pivot fields in the Pivot Table using Aspose.Cells for .NET 18.3

March 28, 2018
Share on FacebookTweet about this on TwitterShare on LinkedIn


Aspose.Cells for .NET logo
We are pleased to announce the release of Aspose.Cells for .NET v18.3. Please check the document on how to install Aspose for .NET APIs directly from NuGet repository. In this release, we have added some worthy features and other enhancements for the users. Please check the release notes in order to get an idea about what is new and what has been enhanced or fixed with this revision of Aspose.Cells for .NET.

Access and modify the display label of the linked Ole Object

Microsoft Excel allows you to change the display label of the Ole Object. You can also access or modify the display label of the Ole object via Aspose.Cells APIs using the OleObject.Label property. The following sample code loads an Excel file that contains the Ole Object. The code accesses the Ole Object and changes its Label. Please notice the console output that will show the effect of the sample code.

//Load the sample Excel file 
Workbook wb = new Workbook("Book1.xlsx");
 
//Access first worksheet
Worksheet ws = wb.Worksheets[0];
 
//Access first Ole Object
OleObject oleObject = ws.OleObjects[0];
 
//Display the Label of the Ole Object
Console.WriteLine("Ole Object Label - Before: " + oleObject.Label);
 
//Modify the Label of the Ole Object
oleObject.Label = "Aspose APIs";
 
//Save workbook to memory stream
MemoryStream ms = new MemoryStream();
wb.Save(ms, SaveFormat.Xlsx);
 
//Set the workbook reference to null
wb = null;
 
//Load workbook from memory stream
wb = new Workbook(ms);
 
//Access first worksheet
ws = wb.Worksheets[0];
 
//Access first Ole Object
oleObject = ws.OleObjects[0];
 
//Display the Label of the Ole Object that has been modified earlier
Console.WriteLine("Ole Object Label - After: " + oleObject.Label);

Please see the document/article with attachment(s) on how to access and modify the display label of the linked Ole Object for your reference.

Group pivot fields in the Pivot Table

Microsoft Excel allows you to group pivot fields in the Pivot Table. When there is large amount of data related to a pivot field, it is better to group them into sections. Aspose.Cells provides this feature via the PivotTable.SetManualGroupField() method. This was a long-awaited feature requested by many users. The following sample code loads the template Excel file and performs grouping operation on the first pivot field in the Pivot Table using the PivotTable.SetManualGroupField() method.

//Load sample workbook
Workbook wb = new Workbook("sampleGroupPivotFieldsInPivotTable.xlsx");
 
//Access the second worksheet
Worksheet ws = wb.Worksheets[1];
 
//Access the pivot table
PivotTable pt = ws.PivotTables[0];
 
//Specify the start and end date time
DateTime dtStart = new DateTime(2008, 1, 1);//1-Jan-2018
DateTime dtEnd = new DateTime(2008, 9, 5); //5-Sep-2018
 
//Specify the group type list, we want to group by months and quarters
ArrayList groupTypeList = new ArrayList();
groupTypeList.Add(PivotGroupByType.Months);
groupTypeList.Add(PivotGroupByType.Quarters);
 
//Apply the grouping on first pivot field
pt.SetManualGroupField(0, dtStart, dtEnd, groupTypeList, 1);
 
//Refresh and calculate pivot table
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;
 
//Save the output Excel file
wb.Save("outputGroupPivotFieldsInPivotTable.xlsx");

Please see the document/article with attachment(s) on how to group pivot fields in the Pivot Table for your reference.

Export similar border Style when border style is not supported by web browsers

Microsoft Excel also supports dashed border types which are not supported by most web browsers. When you convert such an Excel file into HTML using Aspose.Cells, the borders are removed. However, Aspose.Cells allows you to accomplish the task and supports to display such borders with HtmlSaveOptions.ExportSimilarBorderStyle property. For more detail on the feature, please see this article/document for your reference.

Find if the worksheet is a Dialog sheet

Dialog sheet is an older format that contains a dialog box. You can find if a sheet is dialog or some other type with Worksheet.Type property. If it returns enumeration value SheetType.Dialog, then it means, you are dealing with Dialog sheet. For more detail, please see this article.

Get DrawObject and Bound while rendering to PDF using DrawObjectEventHandler class

Aspose.Cells provides an abstract class DrawObjectEventHandler which has a Draw() method. Users can implement DrawObjectEventHandler and utilize the Draw() method to get the DrawObject and Bound while rendering Excel file to PDF or image format. For more detail, please see this article.

Preserve single quote prefix of cell value or range

When you put some value inside a cell that has leading apostrophe or single quote mark, then Microsoft Excel hides it, but when you select the cell, it displays the leading apostrophe or single quote in a formula bar. Aspose.Cells provides StyleFlag.QuotePrefix property that will handle either preserving the quote or not preserving the quote for your needs. For more detail, please see this article.

Change the decimal separator from Numeric keypad

By default, Aspose.Cells.GridWeb (similar to MS Excel) displays numeric data accordingly based on the locale/regional settings on the machine. You can change the decimal separator from Numeric keypad programmatically using Aspose.Cells.GridWeb API. So, when a file is imported into GridWeb matrix or you input some numeric data (from the numeric keypad) into a new worksheet cell, it should have your desired decimal separator set visually. For more detail, please see this article.

Other Enhancements and Fixes

There are a few other enhancements included and some exceptions handled in the new release for the users. A few of the worth mentioning features and other improvements are as follows.

In Aspose.Cells 18.3, we fixed several important bugs and other issues. For example, issues around reading/writing MS Excel file formats, manipulating and rendering shapes, applying conditional formatting, applying formatting/styles to the cells, protecting worksheets, rendering and manipulating charts, manipulating PivotTables, rendering images from Excel worksheets, rendering images files from charts and exporting Excel workbooks to PDF format have been resolved. Moreover, the Aspose.Cells formula calculation engine is further enhanced in the release.

Changes to the Public API

This version of Aspose.Cells for .NET has made some changes to the Public API. A few of the worth mentioning changes are as follow:

  • Added HtmlSaveOptions.ExportSimilarBorderStyle property, it indicates whether exporting the similar border style when the border style is not supported by browsers.
  • Added new enum GridValidationType.CustomServerFunction type, it represents custom server-side function validation.
  • Added ChartType.Map enum member, it represents the map chart.
  • Added OleObject.Label property, it gets and sets the display label of the linked Ole Object.
  • Added StyleFlag.QuotePrefix enum member, it indicates whether applying the style’s QuotePrefix property.
  • Added DialogBox class, it represents the dialog box sheet.

Aspose.Cells for .NET Resources

The resources, you may need to accomplish your tasks:

Leave a comment

Posted inAspose.Cells Product Family
 

Related Articles