Aspose.Cells for .NET 17.3.0 Release Notes

KeySummaryCategory
CELLSNET-45037Support Custom Total & Grand Total Labels for Pivot TablesNew Feature 
CELLSNET-45156Synchronize the paging of the two grids (Aspose.Cells.GridWeb) New Feature 
CELLSNET-45140Validate updated cells only and ignore validation of other cells (Aspose.Cells.GridWeb) New Feature 
CELLSNET-45158Change in pivot data source adds additional LinkSourceEnhancement 
CELLSNET-45168The shape is skewed/tilted a bit with its text “1” position changedBug 
CELLSNET-45163Text in a shape is inverted while rendering worksheet to imageBug 
CELLSNET-45161Angle of the picture in spreadsheet is slightly differentBug 
CELLSNET-45159Auto shape objects have rendered incorrectlyBug 
CELLSNET-45138PrintArea doesn’t manage to transform images correctlyBug 
CELLSNET-45136Boxes are rotated when Excel is converted to PDF Bug 
CELLSNET-45182Resultant spreadsheet cannot be loaded in Excel without repairBug 
CELLSNET-45143Aspose.Cells produces corrupted pivot table with report filter when saved to XLSBBug 
CELLSNET-45135Images are overlapping while importing HTML as XLSXBug 
CELLSNET-45134Column widths are lost while importing HTML as XLSXBug 
CELLSNET-45133Content styling is lost while importing HTML as XLSXBug 
CELLSNET-45132WrapText enabled in HTML is lost when saved in XLSX formatBug 
CELLSNET-45127Font style and size is changed after importing HTML as XLSXBug 
CELLSNET-45115Refreshing the pivot table after adding some data generates corrupt Excel fileBug 
CELLSNET-45109Error in Refresh of Pivot Table grouped by Date columnBug 
CELLSNET-45081Pivot Table’s merge labels feature does not workBug 
CELLSNET-45076Alignment doesn’t work and text go out of the cell in Excel to HTML conversionBug 
CELLSNET-45057Conversion of HTML to XLSX is not correctBug 
CELLSNET-45040Some pivot field items are rendered as Date values instead of NumbersBug 
CELLSNET-40265Issue with culture of input filesBug 
CELLSNET-45142Issue with VLOOKUP function when external range is usedBug 
CELLSNET-45150Impossible to expand or collapse the last rows of worksheetBug 
CELLSNET-45139Paging is not working with two GridWebs on one pageBug 
CELLSNET-45137Grouped rows or columns have issues in GridWeb when it is run on Internet Explorer 11Bug 
CELLSNET-45203Characters are overlapping when rendering an Excel file to PDFBug 
CELLSNET-45196Printout is garbled in exported XPSBug 
CELLSNET-45187Tags should be in ascending order as per TrueType specificationBug 
CELLSNET-45169Warning: Maximum size for region is 262144 bytes, at SheetRender.ToImage on MonoBug 
CELLSNET-45167SheetRender.ToImage renders the contents garbled and incomplete on MonoBug 
CELLSNET-45166SheetRender.ToImage renders only the page header and footer on MonoBug 
CELLSNET-43732Rendering to EMF and pasting into Word causes text overlapping in resultant PDFBug 
CELLSNET-45186Area chart not showing on PDF exportBug 
CELLSNET-45178Axis labels are trimmed while rendering chart to imageBug 
CELLSNET-45177Axis labels are missing while rendering chart to image - IIBug 
CELLSNET-45151Charts lose all formatting while rendering spreadsheet to PDFBug 
CELLSNET-45147Chart is missing while rendering spreadsheet to PDFBug 
CELLSNET-44998Chart’s data labels change after converting spreadsheet to PDFBug 
CELLSNET-44984The trendline in the second diagram is not rendered properly in the output PDFBug 
CELLSNET-41539Green ellipse word art/picture is wrongly rendered around the chart in the PDFBug 
CELLSNET-45197Range.MoveTo does not move data validation and conditional formattingBug 
CELLSNET-45183The “Locked” attribute of ActiveX control (ComboBox) is not changedBug 
CELLSNET-45179Setting CodeName on XLSX file is not working fineBugs
CELLSNET-45175Picture formatting doesn’t work for XLSX filesBug 
CELLSNET-45174Print_Area range broke after DeleteRangeBug 
CELLSNET-45173XLSX file is corrupted after savingBug 
CELLSNET-45171Moving the range causes the inner ranges to shrink being one cell longBug 
CELLSNET-45162Chart is not calculated/refreshed correctly after deleting a rangeBug 
CELLSNET-45155Cells.InsertColumns/DeleteColumns causes “#REF” for the ListObject/TableBug 
CELLSNET-45154Moving the range does not update the references on another sheetBug 
CELLSNET-45145Cannot delete CameraTool objectBug 
CELLSNET-45060Button’s font settings changed after simple load and save operationBug 
CELLSNET-43722File error: data may have been lost, thrown by Excel while opening XLSBug 
CELLSNET-45190Exception occurred while calculating WORKDAY formulaException 
CELLSNET-45199Excel to PDF - Index out of Array exceptionException 

Public API and Backwards Incompatible Changes

The following is a list of any changes made to the public API such as added, renamed, removed or deprecated members as well as any non-backward compatible change made to Aspose.Cells for .NET. If you have concerns about any change listed, please raise it on the Aspose.Cells support forum.

Customize Globalization Settings of a Pivot Table

Using the recent 17.3.0 or higher version, developers can customize the globalization settings of a pivot table in an Excel file. They may change the Pivot Total, Sub Total, Grand Total, All Items, Multiple Items, Column Labels, Row Labels, Blank Values text as per the requirements. Developers can incorporate this feature in their .NET applications, regardless of the Excel text language. It may be Arabic, Hindi, Polish, etc. All the new supported methods are listed below:

  1. Adds GlobalizationSettings.GetPivotTotalName() method - It gets the name of “Total” label in the PivotTable. Developers can override this method when the PivotTable contains two or more PivotFields in the data area.
  2. Adds GlobalizationSettings.GetPivotGrandTotalName() method - It gets the name of “Grand Total” label in the PivotTable.
  3. Adds GlobalizationSettings.GetMultipleItemsName() method - It gets the name of “(Multiple Items)” label in the PivotTable.
  4. Adds GlobalizationSettings.GetAllName() method - It gets the name of “(All)” label in the PivotTable.
  5. Adds GlobalizationSettings.GetColumnLablesName() method - It gets the name of “Column Labels” label in the PivotTable.
  6. Adds GlobalizationSettings.GetRowLablesName() method - It gets the name of “Row Labels” label in the PivotTable.
  7. Adds GlobalizationSettings.GetEmptyDataName() method - It gets the name of “(blank)” label in the PivotTable.
  8. Adds GlobalizationSettings.GetSubTotalName(PivotFieldSubtotalType subTotalType) method - It gets the name of “PivotFieldSubtotalType” type in the PivotTable.

This code example elaborates how to customize the globalization settings of a pivot table. It creates a class CustomPivotTableGlobalizationSettings derived from a base class GlobalizationSettings and overrides all of its necessary methods. These methods return the customized text for the Pivot Total, Sub Total, Grand Total, All Items, Multiple Items, Column Labels, Row Labels, Blank Values. Then it assigns the object of this class to Workbook.GlobalizationSettings property. The code loads the source excel file that contains the pivot table, refreshes and calculate its data and saves it as an output PDF file. Developers can also save the workbook in any supported format.

.NET, C#

 //Load your excel file

Workbook wb = new Workbook("samplePivotTableGlobalizationSettings.xlsx");



//Setting Custom Pivot Table Globalization Settings

wb.Settings.GlobalizationSettings = new CustomPivotTableGlobalizationSettings();



//Hide first worksheet that contains the data of the pivot table

wb.Worksheets[0].IsVisible = false;



//Access second worksheet

Worksheet ws = wb.Worksheets[1];



//Access the pivot table, refresh and calculate its data

PivotTable pt = ws.PivotTables[0];

pt.RefreshDataFlag = true;

pt.RefreshData();

pt.CalculateData();

pt.RefreshDataFlag = false;



//Pdf save options - save entire worksheet on a single pdf page

PdfSaveOptions options = new PdfSaveOptions();

options.OnePagePerSheet = true;



//Save the output pdf 

wb.Save("outputPivotTableGlobalizationSettings.pdf", options);



// it derives a new class, called CustomPivotTableGlobalizationSettings, from the GlobalizationSettings class, as follows:

private class CustomPivotTableGlobalizationSettings : GlobalizationSettings

{

    //Gets the name of "Total" label in the PivotTable.

    //You need to override this method when the PivotTable contains two or more PivotFields in the data area.

    public override string GetPivotTotalName()

    {

        Debug.WriteLine("---------GetPivotTotalName-------------");

        return "AsposeGetPivotTotalName";

    }



    //Gets the name of "Grand Total" label in the PivotTable.

    public override string GetPivotGrandTotalName()

    {

        Debug.WriteLine("---------GetPivotGrandTotalName-------------");

        return "AsposeGetPivotGrandTotalName";

    }



    //Gets the name of "(Multiple Items)" label in the PivotTable.

    public override string GetMultipleItemsName()

    {

        Debug.WriteLine("---------GetMultipleItemsName-------------");

        return "AsposeGetMultipleItemsName";

    }



    //Gets the name of "(All)" label in the PivotTable.

    public override string GetAllName()

    {

        Debug.WriteLine("---------GetAllName-------------");

        return "AsposeGetAllName";

    }





    //Gets the name of "Column Labels" label in the PivotTable.

    public override string GetColumnLablesName()

    {

        Debug.WriteLine("---------GetColumnLablesName-------------");

        return "AsposeGetColumnLablesName";

    }



    //Gets the name of "Row Labels" label in the PivotTable.

    public override string GetRowLablesName()

    {

        Debug.WriteLine("---------GetRowLablesName-------------");

        return "AsposeGetRowLablesName";

    }



    //Gets the name of "(blank)" label in the PivotTable.

    public override string GetEmptyDataName()

    {

        Debug.WriteLine("---------GetEmptyDataName-------------");

        return "(blank)AsposeGetEmptyDataName";

    }



    //Gets the name of PivotFieldSubtotalType type in the PivotTable.

    public override string GetSubTotalName(PivotFieldSubtotalType subTotalType)

    {

        Debug.WriteLine("---------GetSubTotalName-------------");



        switch (subTotalType)

        {

            case PivotFieldSubtotalType.Sum:

                return "AsposeSum";//polish



            case PivotFieldSubtotalType.Count:

                return "AsposeCount";



            case PivotFieldSubtotalType.Average:

                return "AsposeAverage";



            case PivotFieldSubtotalType.Max:

                return "AsposeMax";



            case PivotFieldSubtotalType.Min:

                return "AsposeMin";



            case PivotFieldSubtotalType.Product:

                return "AsposeProduct";



            case PivotFieldSubtotalType.CountNums:

                return "AsposeCount";



            case PivotFieldSubtotalType.Stdev:

                return "AsposeStdDev";



            case PivotFieldSubtotalType.Stdevp:

                return "AsposeStdDevp";



            case PivotFieldSubtotalType.Var:

                return "AsposeVar";



            case PivotFieldSubtotalType.Varp:

                return "AsposeVarp";



        }



        return "AsposeSubTotalName";

    }

}//End CustomPivotTableGlobalizationSettings

Execute the Client Side Script on Page Change Event of GridWeb Control

Using OnPageChangeClientFunction property of GridWeb control, developers can execute a client side script on the page change event because the GridWeb control can hold data in multiple pages. They may need to display the current page index in their web applications.

  1. Adds an OnPageChangeClientFunction property in GridWeb Control - it gets or sets the client side script function to be called when the page index is changing. It only takes effect when EnablePaging is true.

This code example shows the use of OnPageChangeClientFunction property. It sets the property with the client side function named MyOnPageChange. Now, whenever the user will change the GridWeb page, it will call the client side function MyOnPageChange which prints the current page index on the console:

.NET, C#

 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="TestGridWeb.aspx.cs" Inherits="TestGridWeb" %>



<%@ Register TagPrefix="acw" Namespace="Aspose.Cells.GridWeb" Assembly="Aspose.Cells.GridWeb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Test GridWeb</title>

    <script type="text/javascript">

        function MyOnPageChange(index) {

            console.log("current page is:" + index);

        }

    </script>

</head>

<body>

    <form id="form1" runat="server">

        <div>

            <div>

                <b>GridWeb Version:&nbsp </b>

                <asp:Label ID="lblVersion" runat="server" Text="Label"></asp:Label>

                <br />

            </div>

            <acw:GridWeb ID="GridWeb1"

            runat="server" XhtmlMode="True"

            Height="504px" Width="1119px" EnablePaging="true"

            OnPageChangeClientFunction ="MyOnPageChange">

            </acw:GridWeb>

        </div>

    </form>

</body>

</html>

//........................................................

// It is the client side function MyOnPageChange that will be executed because of setting OnPageChangeClientFunction ="MyOnPageChange"property in GridWeb.

function MyOnPageChange(index) {

    console.log("current page is:" + index);

}

Validate the Entire Excel Worksheet

By default, GridWeb validates only the updated cells and does not validate the entire Excel worksheet. However, if developers require to validate the entire Excel worksheet on client side before GridWeb posts request to the server, then you should set the needValidateall variable inside the acwmain.js to true.

Usage Examples

Please check the list of help topics added in the Aspose.Cells Wiki docs: 

  1. Customize Globalization Settings for Pivot Table
  2. Execute client side function on GridWeb page change
  3. Validate entire worksheet instead of only the updated cells