Export and Import Data
- 11 minutes to read
The Spreadsheet and Report Designer controls support an extensive list of spreadsheet file formats, currently represented by five groups:
Microsoft Excel® (XLS, XLT, XLSX and XLTX, i.e., old and new Microsoft Excel® formats, respectively);
LibreOffice/OpenOffice (ODS or the OpenDocument spreadsheet format; currently the ExpressSpreadSheet Suite only allows importing the OpenDocument spreadsheet files);
Web pages (including both the extensible (i.e., XML) and hypertext markup (HTML and HTM) language file formats);
Simple text table data (including the CSV (i.e., comma-separated values) and TXT file formats);
Binary data (the internal, i.e., ExpressSpreadSheet-specific).
You can use XLS, XLT, XLSX, XLTX, and binary file formats to save/load your data, including the current visual presentation (cell data formats, row and column groups, cell styles, embedded floating objects, etc.) as well as print settings (paper format, header and footer inscriptions, margin settings, row and column page breaks, etc.). Unlike other supported file formats, the CSV and TXT formats can store only the cell data, without concomitant information on cell and content styles, picture, shape, text box containers, printing settings, etc.
Additionally, the Spreadsheet control provides the capability to save spreadsheet document’s contents and settings into a stream (stream out) and then load these data and settings from a stream to another Spreadsheet control instance (stream back).
Loading from the supported file formats
In addition to loading cell styles and values as well as floating pictures and shapes from XLSX, XLTX, XLS, XLT, ODS, and binary files, the Spreadsheet control recognizes a number of built-in functions contained within cells and performs calculations if required. Supported functions are recognized by their tokens. If a function cannot be recognized by the Spreadsheet control, the corresponding cell will contain an Unknown formula value (this text is displayed within all cells containing unidentified formulas if the OptionsView.ShowFormulas property is set to True). If the loaded spreadsheet document contains floating shapes other than the rectangle (square), rounded rectangle (rounded square) or ellipse (circle), these shapes are imported as rectangles.
The Spreadsheet control can load XLS/XLT and XLSX/XLTX files encrypted by using the following algorithms:
40-bit RC4 and CryptoAPI RC4 encryptions (the XLS/XLT file format);
The AES encryption with 128-bit key (supported by Microsoft Excel® 2007 and newer) and Agile encryption (supported by Microsoft Excel® 2010 and newer) in the case of the XLSX/XLTX file format.
For more information on the Spreadsheet control’s password protection functionality, refer to the Password Protection topic.
The Spreadsheet control provides the LoadFromFile procedure to import spreadsheet documents from all supported file formats. To use this procedure, you need to pass the file name (including its full path and name extension) as the FileName parameter. However, LoadFromFile can identify the XLSX, XLTX, XLS, XLT, ODS, and binary file formats correctly even if the loaded files have incorrect name extensions. When a spreadsheet document is imported from a XLSX, XLTX, XLS, or XLT file, any Microsoft Excel® macros within the loaded file are ignored. For more information on importing cell data from CSV files, refer to the Working with the CSV File Format.
Normally, the LoadFromFile procedure is used in conjunction with the standard Open dialog. To set the masks of the available file formats in that dialog, obtain the spreadsheet format repository by calling the dxSpreadSheetFormatsRepository global function implemented in the dxSpreadSheetCore unit. Then, assign the returned result of the repository’s GetOpenDialogFilter function to the Open dialog’s Filter property before executing this dialog.
The following code example demonstrates how to load a file into the Spreadsheet control:
//...
// Set the file masks available in the dialog
OpenDialog1.Filter := dxSpreadSheetFormatsRepository.GetOpenDialogFilter;
OpenDialog1.Execute(Handle); // Open the dialog box for selecting the workbook file
dxSpreadSheet1.LoadFromFile(OpenDialog1.FileName);
To provide an end-user with the capability to load spreadsheet documents in all supported formats, you can either employ the Spreadsheet control’s API or link the OpenDocument command to a UI element in your application.
Saving to the supported file formats
The Spreadsheet control correctly saves cell values, styles, data formats, formula expressions, floating containers, and print settings to the XLSX, XLTX, XLS, XLT, and binary files. Custom implemented functions, that are not similar to those supported by Microsoft Excel® and other popular spreadsheet applications, can cause incompatibility.
The current document can be encrypted when saved into the XLSX, XLTX, XLS, or XLT file format, provided that a password is specified by using the Spreadsheet control’s Password property or Password Protection dialog. The Spreadsheet control uses the 40-bit RC4 (supported by Microsoft Excel® 2000 and higher) and AES 128-bit (supported by Microsoft Excel® 2007 and higher) encryption to save the document to the XLS/XLT and XLSX/XLTX file formats, respectively. For more information on the Spreadsheet control’s password protection functionality, refer to the Password Protection topic.
Due to the obvious technical limitations, the plain text-based CSV and TXT file formats store only cell data without concomitant information on styles, decorations, printing settings, etc. For additional information on text-based formats, refer to the Working with the CSV File Format and Working with the TXT File Format topics.
The HTML and XML file formats store the cell data along with a limited number of cell style settings. These file formats also support export of the embedded pictures and shapes that are saved as PNG files within the dedicated folder. For additional information on exporting to the web-based file formats, refer to the Saving Spreadsheet Data as Web Pages topic.
The Spreadsheet control provides the SaveToFile procedure to export spreadsheet documents to all supported file formats. To use this function, pass the file name (including its full path and name extension) as the FileName parameter. The file name extension is used to identify the desired file format. If no extension is specified, the SaveToFile function saves your workbook into the Spreadsheet control’s own binary format. Note that if you choose CSV or TXT as your target format, only the currently active worksheet is exported, the SaveToFile function omits the rest of a workbook.
Normally, the SaveToFile function is used in conjunction with the standard Save As dialog. To set the masks of the available file formats in that dialog, obtain the spreadsheet format repository by calling the dxSpreadSheetFormatsRepository global function implemented in the dxSpreadSheetCore unit. Then, assign the returned result of the repository’s GetSaveDialogFilter function to the TSaveDialog dialog’s Filter property before executing this dialog.
The following code example demonstrates how to save data contained within the Spreadsheet control to a file:
//...
// Set the file masks available in the dialog
SaveDialog1.Filter := dxSpreadSheetFormatsRepository.GetSaveDialogFilter;
SaveDialog1.Execute(Handle); // Open the dialog box for specifying a path to the file for saving the workbook
dxSpreadSheet1.SaveToFile(SaveDialog1.FileName);
To provide an end-user with the capability to save spreadsheet documents to the XLS, XLT, XLSX, XLTX, HTML, XML, CSV, and TXT file formats, you can either employ the Spreadsheet control’s API or link the SaveDocumentAs command to a UI element in your application.
Save to and Load from a Stream
The Spreadsheet control provides the LoadFromStream and SaveToStream procedures that allow you to save and load the spreadsheet document’s content to a stream and load workbook data from the previously saved steam to another Spreadsheet control instance, for example.
The SaveToStream procedure saves all data stored within a spreadsheet document, including information about all sheets, cell values, styles, formula expressions, fonts, floating picture and shape objects, printing settings, etc. Note that this procedure is also called by the SaveToFile procedure and can be used to implement custom methods for exporting spreadsheet document data.
The LoadFromStream procedure imports the same data and settings previously saved by the SaveToStream procedure. Similarly, LoadFromStream is called by the LoadFromFile procedure.
The following code uses a memory stream to transfer spreadsheet contents from one Spreadsheet control instance (dxSpreadSheet1) to another (dxSpreadSheet2).
var
Stream: TMemoryStream;
//...
Stream := TMemoryStream.Create;
dxSpreadSheet1.SaveToStream(Stream);
Stream.Position := 0;
dxSpreadSheet2.LoadFromStream(Stream);
Stream.Free;
Working with the CSV File Format
The CSV file format does not support Unicode, thus the Spreadsheet control generates CSV files in ANSI, converting Unicode strings according to the currently active locale. Note that only the content of the currently active worksheet is actually saved to a CSV file. Any information other than the cell data is ignored, due to the file format’s limitations.
Like the file format’s name (comma separated values) suggests, its standard determines that values within a file must be separated by commas. However, it is possible to create CSV files with values separated by tabulations, semicolons, or any other symbols instead of commas. To customize CSV format settings, invoke the public dxSpreadSheetCSVFormatSettings function implemented in the dxSpreadSheetFormatCSV unit. This function provides access to the TdxSpreadSheetCSVFormatSettings object containing all necessary settings and methods.
Since CSV files do not store information on distribution of the cell data between different worksheets within a spreadsheet document, the Spreadsheet control imports all information within the file into a single new sheet which replaces the previously opened document. Due to the same technical file format limitation, only the content of the currently active worksheet is exported into a CSV file.
Note
Since the CSV file format does not contain any format-specific identifiers, make sure that you specify .csv file extension of a file name you pass to the LoadFromFile procedure. Otherwise, the Spreadsheet control may not be able to import the file properly.
Working with the TXT File Format
Like CSV, the TXT file format stores the exported cell data as plain text without any formatting, style, or print layout information. However, compared to CSV, the TXT files, generated by the SaveToFile procedure, support Unicode, and are very easy to read. By default, individual non-empty exported cells are vertically aligned and delimited by one ore more spaces. However, you can customize the default TXT file format export settings by using the dxSpreadSheetTXTFormatSettings global variable declared in the dxSpreadSheetFormatTXT unit. The list of available TXT file format exporting options includes:
Customizing the character encoding (the dxSpreadSheetTXTFormatSettings.Encoding field);
Adding either one or two custom strings enclosing the content of each non-empty cell (the BeginString and EndString fields of the dxSpreadSheetTXTFormatSettings global variable);
Assigning the custom string used as a delimiter between cell values (the dxSpreadSheetTXTFormatSettings.Separator global variable).
Since TXT files do not store information on the distribution of the cell data within the workbook, the Spreadsheet control exports only the information stored within the currently active worksheet.
Note
Unlike the CSV file format, the Spreadsheet control does not support importing cell data from TXT files.
Saving Spreadsheet Data as Web Pages
To browse the spreadsheet data by using a web browser, you can export the worksheet contents into the HTML, HTM, or XML file formats. As with the CSV and TXT file formats, the SaveToFile procedure saves only the currently active worksheet.
In addition to the cell data, the SaveToFile procedure exports some of the accompanying visual styles information to the HTML, HTM, and XML files:
The solid cell brush fill color (any other brush patterns are ignored and displayed as solid);
Cell border styles, including both the pattern and color. The XML file format supports only solid borders while the HTML and HTM file formats also support dotted, dashed, and double border styles. If the cell border pattern is not supported by a file format, the Spreadsheet control changes it to one of the supported patterns;
Floating Containers. The XML file format supports only the visible pictures, shapes, text boxes, and comments that match the bounds of a single cell. Otherwise, the embedded objects in the exported worksheet are ignored. However, both the HTML and HTM file formats do not impose restrictions on floating picture and shape size and positioning.
Any pictures and shapes exported into the HTML, HTM, or XML file formats, are saved as PNG images within the dedicated folder whose name consists of the target file’s name with the ‘.images’ string added to its tail. If the Spreadsheet control is unable to export at least a single floating container from the currently active worksheet, the dedicated “images folder” is not created.
Note
As with the TXT file format, the Spreadsheet control does not allow you to import cell data from HTML, HTM, or XML files.