eDocEngine VCL
Create documents and reports in 18 formats
Compatibility
Delphi C++Builder

Generating More User-Friendly Excel Documents When Using eDocEngine

Do you want to easily get better Excel output when creating Excel documents using eDocEngine? This article gives you some tips that you can use to make the generated Excel documents look better, be more useable and readable.
By Shivaranjini M and Bhamini S

As you know, the Excel format is a row/column (spreadsheet) based data representation format and is extensively used for storing columnar data and for mathematical manipulation of numerical data. With eDocEngine, you can create Excel documents either as a process of exporting from third-party reporting tools or through direct use of the eDocEngine APIs. In either case, there are some techniques you can make use of, to generate a more useable, better looking and more readable Excel document. Read on to know the techniques.

TgtExcelEngine component, the component in eDocEngine responsible for creation of Excel documents, provides several properties to surface these techniques. These properties are listed below with explanation on their use.

AutoFormatCells

In Excel, most of the time, it is required to do some mathematical calculations like SUM, AVERAGE, etc., on the numerical data available in the document. However, when any report is exported to Excel using eDocEngine, the textual contents will be rendered as text/string without parsing the text item for any particular format. You could use the ‘Preferences.AutoFormatCells’ property of the TgtExcelEngine to control the format of the textual contents of the document.

gtExcelEngine1.Preferences.AutoFormatCells := True;

This will automatically set the correct format of the individual cells depending on the contents like General, Number, Currency, Date, and Time.

gtExcelEngine1.Preferences.AutoFormatCells := False;

All the cells will be formatted as General i.e. text format.

RowSpacing and ColumnSpacing

During ReportExport process, the data can be rendered in Excel with or without extra blank rows/columns. Inserting blank rows may sometimes enhance the readability of the document. The ‘RowSpacing’ and ‘ColumnSpacing’ properties of gtExcelEngine can be used to control this behavior. A brief explanation of the same is given below:

gtExcelEngine1.Formatting.RowSpacing := -1;
gtExcelEngine1.Formatting.ColumnSpacing := -1;

The report will be exported to Excel with the default rendering process without any additional formatting.

gtExcelEngine1.Formatting.RowSpacing := 0;
gtExcelEngine1.Formatting.ColumnSpacing := 0;

Any extra blank rows/columns that are present while exporting the reports will be removed in the output Excel document.

gtExcelEngine1.Formatting.RowSpacing := 1; //2, 3, etc
gtExcelEngine1.Formatting.ColumnSpacing := 1; //2, 3, etc

The specified number of blank rows/columns will be inserted after each row/column respectively in the Excel document.

ContinuousMode

Excel, being a columnar data representation format, does not follow the concept of pages. However, in eDocEngine, the contents in the Excel can be rendered page wise, wherein, extra blank rows will be inserted, if required, to adjust to the height of the page. If it is not required to create the Excel output in this way, then you could use the Preferences.ContinuousMode property of the gtExcelEngine.

gtExcelEngine1.Preferences.ContinuousMode := True;

This will remove the extra blank rows inserted, if any, between the pages.

gtExcelEngine1.Preferences.ContinuousMode := False;

This will add required number of blank rows between the pages to adjust to the specified page height.

ApplyAlignment

While rendering reports to Excel with eDocEngine, eDocEngine only gets the contents of the report as individual text elements. eDocEngine renders these elements to the individual cells in Excel, based on the left-top coordinate position of the text element and hence, there is no way to calculate the exact cell position of the text item if the text items in the original report are not left aligned, and if the alignment information is not available. This may sometimes lead to misalignment of the data in Excel.

With some reporting tools, it is possible to get the information regarding the alignment of the contents in the report. Hence, for such reports, during exporting using eDocEngine, you could increase the precision of the output by using the ‘Preferences.ApplyAlignment’ property.

gtExcelEngine1.Preferences.ApplyAlignment := True;

This will consider the alignment information to calculate the cell positions during the creation of Excel document. However, this will not set the alignment of the data items in the generated document.

gtExcelEngine1.Preferences.ApplyAlignment := False;

This will render the Excel document with the default rendering process without considering any alignment information.

---o0O0o---

Our .NET Developer Tools
XtremeDocumentStudio .NET

Multi-format document-processing component suite for .NET developers.

PDFOne .NET

A .NET PDF component suite to create, edit, view, print, reorganize, encrypt, annotate, and bookmark PDF documents in .NET applications.

Our Delphi/C++Builder developer tools
XtremeDocumentStudio Delphi

Multi-format document-processing component suite for Delphi/C++Builder developers, covering both VCL and FireMonkey platforms.

eDocEngine VCL

A Delphi/C++Builder component suite for creating documents in over 20 formats and also export reports from popular Delphi reporting tools.

PDFtoolkit VCL

A Delphi/C++Builder component suite to edit, enhance, view, print, merge, split, encrypt, annotate, and bookmark PDF documents.

Our Java developer tools
XtremeDocumentStudio (for Java)

Multi-format document-processing component suite for Java developers.

PDFOne (for Java)

A Java PDF component suite to create, edit, view, print, reorganize, encrypt, annotate, bookmark PDF documents in Java applications.

Our Platform-Agnostic Cloud and On-Premises APIs
StarDocs

Cloud-hosted and On-Premises REST-based document-processing and document-viewing APIs

Privacy | Legal | Feedback | Newsletter | Blog | Resellers © 2002-2017 Gnostice Information Technologies Private Limited. All rights reserved.