Refresh Children Pivot Tables of Parent Pivot Table, Group Pivot Fields in the Pivot Table and Set Margins of Comment or Shape in Aspose.Cells for Android via Java 18.3

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedIn

Aspose.Cells for Android logoWe are pleased to announce the release of Aspose.Cells for Android via Java 18.3. This release includes many new features, enhancements and other bug fixes that further improve the overall stability and usability of the API. We also recommend our clients to use the powerful Aspose for Java APIs directly in their Maven Projects with simple configurations. Please check the release notes in order to get an idea about what is new and what has been fixed with this revision of Aspose.Cells for Android via Java. While you are downloading the latest build, here is a look at the most worth mentioning features in this release.

Find and Refresh the Nested or Children Pivot Tables of Parent Pivot Table

Sometimes, one pivot table uses other pivot table as a data source, so it is called a child pivot table or nested pivot table. You can find the children pivot tables of a parent pivot table using the PivotTable.getChildren() method. For more detail, please see the following article.

The following sample code finds the children pivot tables of the pivot table using the PivotTable.getChildren() method and then refreshes them one by one.

//Load sample Excel file
Workbook wb = new Workbook(strFilePath);

//Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

//Access third pivot table
PivotTable ptParent = ws.getPivotTables().get(2);

//Access the children of the parent pivot table
PivotTable[] ptChildren = ptParent.getChildren();

//Refresh all the children pivot table
int count = ptChildren.length;
for (int idx = 0; idx < count; idx++)
{
	//Access the child pivot table
	PivotTable ptChild = ptChildren[idx];

	//Refresh the child pivot table
	ptChild.refreshData();
	ptChild.calculateData();
}

Parsing Pivot Cached Records while loading Excel file

When you create a Pivot Table, Microsoft Excel takes a copy of the source data and stores it in the Pivot Cache. The Pivot Cache is held inside the memory of Microsoft Excel. When you load your Excel file inside the Workbook object, you can decide whether you also want to load the records of Pivot Cache or not, using the LoadOptions.ParsingPivotCachedRecords property. The default value of this property is false. If Pivot Cache is quite big, it can increase the performance. For more detail, please see the following article.

Export Document, Workbook and Worksheet Properties in Excel to HTML conversion

When Microsoft Excel file is exported to HTML using Microsoft Excel or Aspose.Cells APIs, it also exports various types of Document, Workbook and Worksheet properties. You can avoid exporting these properties by setting the HtmlSaveOptions.ExportDocumentProperties, HtmlSaveOptions.ExportWorkbookProperties and HtmlSaveOptions.ExportWorksheetProperties as false. The default value of these properties is true. For more detail, please see the following article.

Exclude unused Styles during Excel to HTML conversion

Microsoft Excel file may contain many unnecessary unused styles. When you export the Excel file to HTML file format, these unused styles are also exported. This could increase the size of HTML. You can exclude the unused styles during the conversion of Excel file to HTML using the HtmlSaveOptions.ExcludeUnusedStyles property. For more detail, please see the following article.

Handle Automatic Units of Chart Axis like Microsoft Excel

Early versions of Aspose.Cells were not able to handle automatic units of chart axis properly when chart is rendered to image or pdf. Now, Aspose.Cells supports the handling of automatic units of chart axis. There is no code change. Just convert your chart into image or pdf and it will render chart axis just like Microsoft Excel renders it. For more detail, please see the following article.

Create PdfBookmarkEntry for Chart Sheet

Earlier, Aspose.Cells would create PdfBookmarkEntry for normal sheet. But now Aspose.Cells can also create PdfBookmarkEntry for chart sheet. Since, chart sheet does not have any other cell except cell A1, so it will create PdfBookmarkEntry for cell A1 only. For more detail, please see the following article.

Set Margins of Comment or Shape inside the Worksheet

Aspose.Cells allows you to set the margins of any shape or comment using the Shape.TextBody.TextAlignment property. This property returns the object of ShapeTextAlignment class which has different properties e.g. TopMarginPt, LeftMarginPt, BottomMarginPt, RightMarginPt etc. that can be used to set the top, left, bottom and right margins. For more detail, please see the following article.

The following code loads the sample Excel file and accesses its shapes one by one and sets their top, left, bottom and right margins.

//Load the sample Excel file
Workbook wb = new Workbook(dirPath + "sampleSetMarginsOfCommentOrShape.xlsx");

//Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

for(int idx =0; idx<ws.getShapes().getCount(); idx++)
{
	//Access the shape
	Shape sh = ws.getShapes().get(idx);
	
	//Access the text alignment
	ShapeTextAlignment txtAlign = sh.getTextBody().getTextAlignment();

	//Set auto margin false
	txtAlign.setAutoMargin(false);

	//Set the top, left, bottom and right margins
	txtAlign.setTopMarginPt(10);
	txtAlign.setLeftMarginPt(10);
	txtAlign.setBottomMarginPt(10);
	txtAlign.setRightMarginPt(10);	    
}

//Save the output Excel file
wb.save(dirPath + "outputSetMarginsOfCommentOrShape.xlsx");

Specify Formula Fields while Importing Data to Worksheet

You can specify formula fields when you import data into your worksheet using the ImportTableOptions.setFormulas() method. This method takes the Boolean array where the value true means the field is a formula field. For example, if the third field is a formula field, then third value in the array will be true. For more detail, please see the following article.

Specify Maximum Rows of Shared Formula

Aspose.Cells provides the Workbook.Settings.MaxRowsOfSharedFormula property that can be used to specify the maximum rows of the shared formula. The shared formula will be split to several shared formulas if the total rows of shared formula is greater than it. For more detail, please see the following article.

Control External Resources using WorkbookSetting.StreamProvider

Sometime, your Excel file contains external resources e.g. linked images etc. Aspose.Cells allows you to control these external resources using Workbook.Settings.StreamProvider which takes the implementation of IStreamProvider interface. Whenever you will try to render your worksheet containing external resources e.g. linked images, the methods of IStreamProvider interface will be invoked which will enable you to take appropriate actions for your external resources. For more detail, please see the following article.

Filter Defined Names while loading Workbook

Aspose.Cells allows you to filter or remove defined names present inside the workbook. Please use LoadDataFilterOptions.DEFINED_NAMES to load defined names and use ~LoadDataFilterOptions.DEFINED_NAMES to remove them while loading the workbook. Please note, if you will remove defined names, then formulas inside the workbook may break up. For more detail, please see the following article.

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(dirPath + "sampleGroupPivotFieldsInPivotTable.xlsx");

//Access the second worksheet
Worksheet ws = wb.getWorksheets().get(1);

//Access the pivot table
PivotTable pt = ws.getPivotTables().get(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.setRefreshDataFlag(true);
pt.refreshData();
pt.calculateData();
pt.setRefreshDataFlag(false);

//Save the output Excel file
wb.save(dirPath + "outputGroupPivotFieldsInPivotTable.xlsx");

Please see the following article for more detail on how to group pivot fields in the Pivot Table for your reference.

Get DrawObject and Bound while rendering to PDF using DrawObjectEventHandler class

Aspose.Cells provides an abstract class DrawObjectEventHandler which has a draw() method. User can implement DrawObjectEventHandler and utilize the draw() method to get the DrawObject and Bound while rendering Excel to Pdf or Image. If you are rendering Excel file to Pdf, then you can utilize DrawObjectEventHandler class with PdfSaveOptions.DrawObjectEventHandler. Similarly, if you are rendering Excel file to Image, you can utilize DrawObjectEventHandler class with ImageOrPrintOptions.DrawObjectEventHandler. The following sample code explains the usage of DrawObjectEventHandler class with the PdfSaveOptions.DrawObjectEventHandler.

//Implement the concrete class of DrawObjectEventHandler
class clsDrawObjectEventHandler extends DrawObjectEventHandler
{
	public void draw(DrawObject drawObject, float x, float y, float width, float height)
	{
		System.out.println();

		//Print the coordinates and the value of Cell object
		if (drawObject.getType() == DrawObjectEnum.CELL)
		{
			System.out.println("[X]: " + x + " [Y]: " + y + " [Width]: " + width + " [Height]: " + height + " [Cell Value]: " + drawObject.getCell().getStringValue());
		}

		//Print the coordinates and the shape name of Image object
		if (drawObject.getType() == DrawObjectEnum.IMAGE)
		{
			System.out.println("[X]: " + x + " [Y]: " + y + " [Width]: " + width + " [Height]: " + height + " [Shape Name]: " + drawObject.getShape().getName());
		}

		System.out.println("----------------------");
	}
}
	 
//-------------------------------------------------------------
	 
void Run() throws Exception
{
	//Load sample Excel file
	Workbook wb = new Workbook(dirPath + "sampleGetDrawObjectAndBoundUsingDrawObjectEventHandler.xlsx");
 
	//Specify Pdf save options
	PdfSaveOptions opts = new PdfSaveOptions();
 
	//Assign the instance of DrawObjectEventHandler class
	opts.setDrawObjectEventHandler(new clsDrawObjectEventHandler());
 
	//Save to Pdf format with Pdf save options
	wb.save(dirPath + "outputGetDrawObjectAndBoundUsingDrawObjectEventHandler.pdf", opts);
}

Please see the following article for more detail about this feature.

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 for your reference.

Find if the Worksheet is 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.

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. Please see the following article with sample code and attachments explaining how to access and modify the display label of the linked Ole Object for your reference.

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.

Read Axis Labels after Calculating the Chart

You can read axis labels of your chart after calculating its values using the Chart.calculate() method. Please use the Axis.AxisLabels property for this purpose that will return the list of axis labels. For more detail, please see the following article.

Specify Document Version of the Excel File using BuiltIn Document Properties

You can change the Version number of Excel file by right clicking the file and then selecting Properties > Details and then editing the Version number field. Please use BuiltInDocumentPropertyCollection.DocumentVersion property to change it programmatically using Aspose.Cells APIs. For more detail, please see the following article.

Aspose.Cells for Android via Java Resources

The resources, you may need to accomplish your tasks:


To keep up with our news, you can follow us on Twitter or follow our Facebook page.

Leave a Reply