What's New in Document Solutions for Excel Java v7
DsExcel for .NET v7 - December 13, 2023
Important Information: A Shift from ‘GrapeCity Documents’ to Document Solutions
In tandem with our commitment to continuous improvement, GrapeCity Documents has rebranded to Document Solutions. This change extends to the APIs and controls within the Document Solutions suite. Below, you'll find the updated nomenclature:
Document Solutions for Excel (DsExcel Java) - previously GrapeCity Documents for Excel, Java (GcExcel Java)
We've made it easy to upgrade your existing packages to the new packages using the Documents Migration tool. This tool is included in the trial Downloads zip file found on the above product links. For the v7.0 release cycle, packages with old names will be provided separately, ensuring access to the same v7 new feature updates. This approach is designed to facilitate a seamless transition for users.
It's important to emphasize that despite the adoption of new package names, only the package names themselves are altered. The namespace and type names remain unchanged, eliminating the need for any modifications in your C#/VB user codes.
Excel supports asynchronous calculations through the use of custom functions that leverage asynchronous programming techniques. Suppose you have a scenario where a custom function needs to fetch data from an external source or perform a time-consuming computation. Traditionally, synchronous functions would halt Excel's responsiveness until the calculation is complete, causing delays in the user interface. With asynchronous calculations, you can improve responsiveness by allowing other operations to continue while the time-consuming task is in progress.
In DsExcel, a user-defined function derived from the new AsyncCustomFunction class now supports asynchronous calculations. Additionally, the CalcError type introduces a 'Busy' enum, signifying that a cell is currently engaged in the calculation of an asynchronous formula. This enhancement in DsExcel empowers users to leverage asynchronous calculations within their custom functions, providing flexibility and efficiency in scenarios involving complex computations.
Have a look on following resources to view usage of this new class.
Preserving the aspect ratio of images is crucial for several reasons. Firstly, it ensures that certain images, such as country flags, maintain their proportional relationship between width and height, preventing them from appearing distorted. Secondly, it prevents images from appearing smaller and losing important details. In the v7 release, new property namely image.keepaspect or image.ka, have been introduced to DsExcel Templates. When set to true, these properties ensure that the aspect ratio of the image is maintained, allowing it to fit within the cell size regardless of its height or width.
Example: Following code sets image.keepaspect to true for a Flag image
Note that the image stretches to the cell along the height and width when image.keepaspect is False, while when True, the image maintains it’s aspect ratio within the cell.
DsExcel Templates has been providing support for pagination mode enabling the pagination of worksheets within a report. This feature is particularly useful for reports with a consistent layout on each sheet but varying data, such as invoices, progress reports, and medical test reports. In such reports, there is often a need to repeat shapes and images, including logos or graphics, along with the data. In the v7 release, DsExcel Templates introduces enhanced support for the repetition of shapes and images with data in an Excel file when the Excel template is processed. This enhancement ensures a more dynamic handling of shapes and images, adding flexibility to your report generation process.
The shapes or images will repeat as per the parent of the top left cell from where the image or shape starts.
Example: In following snapshot, the image repeats to 10 rows (CP=10) as per the parent cell of D12 which is B12
DsExcel has long supported the export of Excel spreadsheets to HTML, offering the flexibility to include a separate CSS file as a single entity. Notably, the CSS Inline option becomes crucial in scenarios where content needs to be shared as HTML email or integrated into a CMS (Content Management System). In such instances, the styling tags are directly embedded within the content.
In the latest update, DsExcel introduces a new feature to export Excel to HTML with the CSS Inline option. This functionality allows you to export an Excel file with style attributes embedded directly within HTML elements. Accompanying this capability, DsExcel introduces the HtmlSaveOptions.CssExportType enum, providing three values for exporting Excel files to HTML with different CSS options:
- External: Export css to separate file.
- Internal: Export css with the style tag in HTML.
- Inline: Export css with the style attribute inside HTML elements.
Example: Following code exports Excel workbook to HTML with CssExportType.Inline option. Note that the style tags are embedded within the HTML file.
When printing an Excel sheet, users can incorporate the 'FirstPageNumber' property within the Page Setup Dialog to introduce numbering to the pages. To utilize a default numbering system, there exists an option to set this property to 'Auto.'
For programmatic use, DsExcel introduces the IPageSetup.IsAutoFirstPageNumber property, facilitating the retrieval and modification of whether the first page number is set to 'Auto' when printing. The default value is true; however, if the IPageSetup.FirstPageNumber property is explicitly set, the IPageSetup.IsAutoFirstPageNumber property becomes false and requires resetting.
Example: The following code snippet demonstrates how to set the IPageSetup.IsAutoFirstPageNumber property to true for a worksheet that already has the FirstPageNumber set to 3. This ensures that the first page number is automatically determined when printing, even if it was previously set to a specific value.
//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
var fileStream = GetResourceStream("xlsx\\ConfigIsAutoFirstPageNumber.xlsx");
IWorksheet worksheet = workbook.Worksheets;
worksheet.PageSetup.CenterFooter = "&P";
//Set auto page number, original first page number is 3.
worksheet.PageSetup.IsAutoFirstPageNumber = true;
// Save to an excel file
When graphing data in a chart, it is often crucial to visually represent the underlying trends. Trendlines in Excel serve as a valuable tool for analyzing data and predicting future values based on existing trends. DsExcel has been supporting the addition of trendline equations to charts using the ITrendline interface, specifically through the ITrendline.DisplayEquation and ITrendline.DisplayRSquared properties.
With the v7 release, to enhance the visual interpretation of trendlines, DsExcel introduces formatting properties to the ITrendline interface. A new property, DataLabel, is added to the ITrendline interface, allowing users to access the data label associated with the trendline. This feature enables users to utilize properties such as Font, Format, NumberFormat, Orientation, Direction, and AutoText from the IDataLabel interface to format the trendline equation label.
Furthermore, the v7 release includes the ITrendline.Delete method, providing a means to remove the trendline equation label when needed. Notably, trendline equations will now be supported in exports to PDF, HTML, and images, ensuring consistency across various formats. These enhancements contribute to a more comprehensive and visually appealing representation of trendline data in DsExcel.
Example: In the following snapshot, a trendline equation is formatted using the new properties of IDataLabel interface.
Users can now have the option to establish a default value for a cell. If the regular value is unspecified, the default value will be shown and included in the calculations. DsExcel adds IRange.DefaultValue property to get or set the default value of cell.
Example: Following code adds a default value for cell range C5:C8 but overrides formula for cells C6 & C8. The calculation takes into account Default values for C4, C5 & C7.
In pursuit of a visually appealing representation and a comprehensive understanding of the charted data, DsExcel now offers support for smooth chart lines during the export of Excel files to PDF.
The following chart types benefit from this feature:
- Line Chart
- Scatter Chart
- Combo Chart
The latest update to DsExcel introduces a powerful feature, allowing users to create Acroforms directly using the DsExcel API when exporting spreadsheets to PDF. A new property, PdfSaveOptions.FormFields, has been added. When set to true, this property enables the export of Excel Form controls as PDF Form controls during the export of an Excel file to PDF. The corresponding properties of certain Form controls are mapped to their respective PDF Form control properties.
For a detailed understanding of the mapped properties, limitations and to explore this feature further, please refer to the resources provided following the snapshot below. This enhancement enhances the versatility of DsExcel, providing users with more comprehensive options for creating and managing Acroforms seamlessly.
DsExcel will also now support Funnel Charts on exporting Excel files to PDF. So you can now add a Funnel chart to excel and directly save the file to PDF.
//Create a funnel chart.
IShape shape = worksheet.Shapes.AddChart(ChartType.Funnel, 10, 150, 300, 200);
//Set funnel chart style.
shape.Chart.ChartTitle.Text = "Funnel Chart";
foreach (var s in shape.Chart.SeriesCollection)
s.DataLabels.Font.Color.RGB = Color.White;
// Save to a pdf file
There are instances where it becomes necessary to export specific columns in Excel with quotation marks. This precaution is taken to prevent any potential misrepresentation of the data when exported to CSV. DsExcel adds new property CsvSaveOptions.QuoteColumns to designate the specific columns requiring quotation marks. The indices of the column can be specified in the property.
If the property is set to null, the behavior is determined by CsvSaveOptions.ValueQuoteType. However, if the property is not null, CsvSaveOptions.ValueQuoteType is overridden. When CsvSaveOptions.QuoteColumns is not null, only the data in the specified column will be enclosed in quotes, leaving the data in other columns unquoted. It's important to note that if a value contains special characters such as quotation marks or separators, it will be enclosed in quotes regardless.
Example: In the following example, CsvSaveOptions.QuoteColumns property defines the columns requiring quotation marks, before exporting to CSV.
DsExcel incorporates support for additional SpreadJS features, enhancing compatibility with the client-side SpreadJS product. It's important to note that these added features exclusively function with SpreadJS, and are not compatible with Microsoft Excel.
- Async User-Defined Function Support
- Enhancements in DsExcel Templates
- Export Excel to HTML with CSS Inline option
- Set First Page Number to 'Auto' in Page Setup
- Grouping for OptionButton Controls
- Formatting for Trendline Equations in Charts (and Export)
- Specify Default Value for Cell
- Support Smooth Lines in Chart in PDF Export
- Direct Acroform Creation with DsExcel API
- Support Exporting of Funnel Charts to PDF
- Specify Columns to Quote on Exporting to CSV
- SpreadJS Integration