Want to generate tables for a user - Excel or OpenOffice Calc?

Tables. For centuries, they have been used to present similar data for record-keeping, counting amounts, and data analysis. For a long time, tables have been a constant tool in accounting and statistics. And it was not until the end of the 20th century that they acquired a completely different quality. Spreadsheets became a symbiosis of past and present technologies. Convenient data organization and calculation methods were combined with computers' computational abilities. This allowed you to quickly perform calculations and analyse huge amounts of data. It also solved the problem of data storage and transfer.

Spreadsheets are built not as an entity, but as an application program, also called a tabular processor. Such program allows you to keep records of data in tables, make various calculations and conversions. Tabular processors are used everywhere, as well as tables once. The main advantage of such programs is that they do not require specific knowledge in accounting, it is enough to study the program a little to use its basic functionality, which is enough for most users. Perhaps there is not a single PC user who has not heard the word Excel. It is thanks to this that we all open a tabular editor when you need to "patch up" the estimate for repairs in the apartment, or just write a list of guests for your wedding.

At the moment there are quite a few similar tabular processors. But perhaps the most famous of them are Microsoft Excel and OpenOffice Calc. The purpose of this article is to examine file formats of these table processors and to understand which of them should be used in this or that situation.

XLS format

Historically, the world giant of the Microsoft software market has monopolized the niche of tabular processors. This continued from the early nineties until 2006, when a significant competitor of OpenOffice.org appeared.

Until 2007, Microsoft Excel used its proprietary data format with XLS extension.

Besides tables with the data and formulas, file xls can store vector and raster drawings, charts and macros (scripts).

The xls file is actually a BIFF binary format. Before xlsx in 2007, it was the main format for storing Excel documents. And, of course, it was a closed format. Therefore, Excel had no competitors for quite some time.

If we dig a little deeper, the basis of the BIFF format was the Interchange File Format (IFF), developed in 1985. A file in this format is a container that contains a document, metadata, multimedia data and graphics.

ODS format

As already mentioned, Microsoft Excel has long been a monopolist in the field of spreadsheet processing and largely due to its closed data format. But in the beginning of two thousand years the group of companies decided to create their own standard for electronic documents, which would allow them to completely abandon Microsoft Office. In 2006, the ODF format received the ISO/IEC 26300 standard. This format represents a whole family of formats for different products:

Odt - for word processor, ods - for tabular processor, odp - for electronic presentations and others.

In the context of this article we are interested in the ODS format, but it is built on the same concept as the rest of the ODF format group. The essence of the format is that it uses a container - ZIP archive, which contains inside XML - the document itself, metadata files, graphics and multimedia files. Thus, we have seemingly one file, but with all the necessary "content" of the document inside. You can easily open the ods file in the archiver if you change the extension to zip. And inside you will see all the files described above.

The main advantage of this format was that it became open and had an official standard. Using XML to form the main document made it easy for them to create and understand this format. Thanks to this, support for this format quickly appeared in most tabular processors. Although the first standard had some complaints and skepticism from users, ODF quickly gained popularity not only among ODF enthusiasts, but also among many commercial and nonprofit organizations that wanted to give up Microsoft's expensive solution.

XLSX format

However, this state of affairs did not suit Microsoft, and on the wave of the general development boom of the application arsenal, the company releases its open format - xlsx. And although the name differs from the previous format with only one letter, it is essentially a completely different format. It is similar in structure to ODS. It is also a ZIP container with an XML file inside. Also, besides XML, there are files with graphics, multimedia, scripts inside.

This format appeared a year later than ODF, in 2007. And, starting from Excel 2007, it became the official default format. The support of the old xls format has certainly survived, because over the years it has accumulated a huge number of documents that are not easy to convert to the new format. By inertia, users continued to work with the old format due to the presence of old versions of MS Office in enterprises for quite a long time. Many MS Office users did not understand what the new format was for. And it has appeared for two reasons: it is a policy of the company which has begun to enter the market of software with an open source code, and aspiration to keep clients who were interested in free products of OpenOffice. But the new format also brought practical benefit. Files with the extension xlsx are much smaller in size in comparison with xls. The programmers were finally able to parse Excel documents in their programs.

Comparison

So, we found out that the ODF and XLSX formats have a similar architecture, thanks to the use of a ZIP container they are much smaller than the outdated xls format, they both have an open standard. In addition, the new formats are more resistant to damage than the outdated binary formats. Without part of the files inside the container, the document will remain operable.

Obviously, you have to choose which one to use between the two. On the one hand, the little known product OpenOffice, which many perceived as "Chinese" fake under MS Office. On the other hand - long known and super popular MS Excel tablet processor. Obviously, the Microsoft client base developed over many years favors the xlsx format. Therefore, the popularity of this format is much more than ODS.

OpenOffice strives to defend its format and supports xlsx read-only. However, it has extensive capabilities to read and save documents in the legacy xls format, which also became open after xlsx, though for non-commercial use.

This is not the case with all open source tabular processors. For example, LibreOffice Calc (which is based on OpenOffice) supports xlsx fully. But no matter how much the manufacturers try to support other formats, there will always be problems. Support for macros in Microsoft formats will still be a problem.

Microsoft Excel also has support for the format of the competitors of ODS. But also not everything is smooth here. Often the formatting is different from the original.

So everybody tries to provide support for competitor formats, but it does not work out perfectly. Therefore it is impossible to say use free software for work with Excel documents. Exactly as it is impossible to recommend using MS Excel as the universal solution for work with any formats. You will have to define area of application of the tabular processor. For the companies which long worked with MS Office transition to opsensor products can appear painful. Many older documents will be displayed with errors, and macros will work partially.

However, many large companies have already switched to OpenOffice and have survived the troubles described above. Problems with native formats in free office packages are not, so these products will occupy their niche.

For Russia, the problem with the name "import substitution" has recently become urgent. Replace the imported paid software is quite difficult, because of the lack of decent solutions for replacement. But in the situation with office products it is not so. Free office packages may well compete with MS Office, there is only the compatibility problem, which we mentioned above.

Report generation in XLSX and ODS

Often, reporting is presented in tabular format. And if you use a report generator, you will surely convert these reports into spreadsheets and convenient editing. Therefore, as part of this article, it would be interesting to see what the same report, exported in xlsx and ods format, will look like when viewed in spreadsheet processors MS Excel and OO Calc (OpenOffice).

The FastReport.Net report generator supports all three spreadsheet formats we reviewed. Let's take a look at what a report in XLSX and ODS formats will look like when viewed on a native and third-party spreadsheet processor. Here is the original pre-export report:

Let’s execute its export to Excel 2007(xlsx):

And if you view the same file in OpenOffice Calc, the picture will not please you:

But OO Calc will display the report in ODS format well:

But if to open ods in Excel, the result will be:

Now you have seen with your own eyes how third party documents are actually supported. We don't need that kind of compatibility!

From this we can conclude that, by and large, there is no difference in what format to use, the main thing is that it is used in the native tabular processor, otherwise disappointment and upset will be your constant companions.