Giter VIP home page Giter VIP logo

openxlsx's Introduction

OpenXLSX

OpenXLSX is a C++ library for reading, writing, creating and modifying Microsoft Excel® files, with the .xlsx format.

(aral-matrix) 19/20 August 2024 updated pull requests

Closed a multitude of pull requests that had been implemented in the May 2024 Update, implemented two more editorials from PR #246 and #253.

May 2024 Update

After a long period of inactivity, I have decided to resume development of OpenXLSX. The code has been cleaned up and a significant number of bugs have been fixed. The library has been tested on Windows, macOS and Linux, and should work on all platforms.

I would like to extend my sincere thanks to all the people who have contributed to the project, either by reporting bugs, suggesting features or by submitting pull requests. I would also like to thank all the people who have starred the project, and who have shown interest in the project.

In particular, I would like to thank Lars Uffmann (https://codeberg.org/lars_uffmann/) for his contributions to the project. Lars has spent significant time and effort in cleaning up the code, fixing bugs and implementing new features. Without his help, the project would not have been in the state it is today.

20/05/2024

  • Google Benchmark is no longer included in the repository. To run the benchmarks, Google Benchmark have to be installed on the system. If you use vcpkg, it will be downloaded automatically.
  • Minor performance improvements to XLCellReference.

06/08/2024

  • Housekeeping updates to the code.
  • Added Makefile.GNU for building on Linux. The project will continue to be based on CMake, and the makefile may not always be kept up-to-date.

Table of Contents

Motivation

Many programming languages have the ability to modify Excel files, either natively or in the form of open source libraries. This includes Python, Java and C#. For C++, however, things are more scattered. While there are some libraries, they are generally less mature and have a smaller feature set than for other languages.

Because there are no open source library that fully fitted my needs, I decided to develop the OpenXLSX library.

Ambition

The ambition is that OpenXLSX should be able to read, write, create and modify Excel files (data as well as formatting), and do so with as few dependencies as possible. Currently, OpenXLSX depends on the following 3rd party libraries:

  • PugiXML
  • Zippy (C++ wrapper around miniz)
  • Boost.Nowide (for opening files with non-ASCII names on Windows)

These libraries are all header-only and included in the repository, i.e. it's not necessary to download and build separately.

Also, focus has been put on speed, not memory usage (although there are options for reducing the memory usage, at the cost of speed; more on that later).

Compatibility

OpenXLSX has been tested on the following platforms/compilers. Note that a '-' doesn't mean that OpenXLSX doesn't work; it just means that it hasn't been tested:

GCC Clang MSVC
Windows MinGW MinGW +
Cygwin - - N/A
MacOS + + N/A
Linux + + N/A

The following compiler versions should be able to compile OpenXLSX without errors:

  • GCC: Version 7
  • Clang: Version 8
  • MSVC: Visual Studio 2019

Clang 7 should be able to compile OpenXLSX, but apparently there is a bug in the implementation of std::variant, which causes compiler errors.

Visual Studio 2017 should also work, but hasn't been tested.

Build Instructions

OpenXLSX uses CMake as the build system (or build system generator, to be exact). Therefore, you must install CMake first, in order to build OpenXLSX. You can find installation instructions on www.cmake.org.

The OpenXLSX library is located in the OpenXLSX subdirectory to this repo. The OpenXLSX subdirectory is a self-contained CMake project; if you use CMake for your own project, you can add the OpenXLSX folder as a subdirectory to your own project. Alternatively, you can use CMake to generate make files or project files for a toolchain of your choice. Both methods are described in the following.

Integrating into a CMake project structure

By far the easiest way to use OpenXLSX in your own project, is to use CMake yourself, and then add the OpenXLSX folder as a subdirectory to the source tree of your own project. Several IDE's support CMake projects, most notably Visual Studio 2019, JetBrains CLion, and Qt Creator. If using Visual Studio, you have to specifically select 'CMake project' when creating a new project.

The main benefit of including the OpenXLSX library as a source subfolder, is that there is no need to locate the library and header files specifically; CMake will take care of that for you. Also, the library will be build using the same configuration (Debug, Release etc.) as your project. In particular, this a benefit on Windows, where is it not possible to use Release libraries in a Debug project (and vice versa) when STL objects are being passed through the library interface, as they are in OpenXLSX. When including the OpenXLSX source, this will not be a problem.

By using the add_subdirectory() command in the CMakeLists.txt file for your project, you can get access to the headers and library files of OpenXLSX. OpenXLSX can generate either a shared library or a static library. By default it will produce a shared library, but you can change that in the OpenXLSX CMakeLists.txt file. The library is located in a namespace called OpenXLSX; hence the full name of the library is OpenXLSX::OpenXLSX.

The following snippet is a minimum CMakeLists.txt file for your own project, that includes OpenXLSX as a subdirectory. Note that the output location of the binaries are set to a common directory. On Linux and MacOS, this is not really required, but on Windows, this will make your life easier, as you would otherwise have to copy the OpenXLSX shared library file to the location of your executable in order to run.

cmake_minimum_required(VERSION 3.15)
project(MyProject)

set(CMAKE_CXX_STANDARD 17)

# Set the build output location to a common directory
set(CMAKE_ARCHIVE_OUTPUT_DIRECTORY ${CMAKE_BINARY_DIR}/output)
set(CMAKE_LIBRARY_OUTPUT_DIRECTORY ${CMAKE_BINARY_DIR}/output)
set(CMAKE_RUNTIME_OUTPUT_DIRECTORY ${CMAKE_BINARY_DIR}/output)

add_subdirectory(OpenXLSX)

add_executable(MyProject main.cpp)
target_link_libraries(MyProject OpenXLSX::OpenXLSX)

Using the above, you should be able to compile and run the following code, which will generate a new Excel file named 'Spreadsheet.xlsx':

#include <OpenXLSX.hpp>

using namespace OpenXLSX;

int main() {

    XLDocument doc;
    doc.create("Spreadsheet.xlsx");
    auto wks = doc.workbook().worksheet("Sheet1");

    wks.cell("A1").value() = "Hello, OpenXLSX!";

    doc.save();

    return 0;
}

Building as a separate library

If you wish to produce the OpenXLSX binaries and include them in your project yourself, it can be done using CMake and a compiler toolchain of your choice.

From the command line, navigate the OpenXLSX subdirectory of the project root, and execute the following commands:

mkdir build
cd build
cmake ..

The last command will configure the project. This will configure the project using the default toolchain. If you want to specify the toolchain, type cmake -G "<toolchain>" .. with <toolchain> being the toolchain you wish to use, for example "Unix Makefiles", "Ninja", "Xcode", or "Visual Studio 16 2019". See the CMake documentation for details.

Finally, you can build the library using the command:

cmake --build . --target OpenXLSX --config Release

You can change the --target and --config arguments to whatever you wish to use.

When built, you can install it using the following command:

cmake --install .

This command will install the library and header files to the default location on your platform (usually /usr/local/ on Linux and MacOS, and C:\Program Files on Windows). You can set a different location using the --prefix argument.

Note that depending on the platform, it may not be possible to install both debug and release libraries. On Linux and MacOS, this is not a big issue, as release libraries can be used for both debug and release executables. Not so for Windows, where the configuration of the library must be the same as for the executable linking to it. For that reason, on Windows, it is much easier to just include the OpenXLSX source folder as a subdirectory to your CMake project; it will save you a lot of headaches.

Current Status

OpenXLSX is still work in progress. The following is a list of features which have been implemented and should be working properly:

  • Create/open/save files
  • Read/write/modify cell contents
  • Copy cells and cell ranges
  • Copy worksheets
  • Cell ranges and iterators
  • Row ranges and iterators

Features related to formatting, plots and figures have not been implemented, and are not planned to be in the near future.

It should be noted, that creating const XLDocument objects, is currently not working!

Performance

The table below is the output from a benchmark (using the Google Benchmark library), which shows that read/write access can be done at a rate of around 4,000,000 cells per second. Floating point numbers are somewhat lower, due to conversion to/from strings in the .xml file.

Run on (16 X 2300 MHz CPU s)
CPU Caches:
  L1 Data 32 KiB (x8)
  L1 Instruction 32 KiB (x8)
  L2 Unified 256 KiB (x8)
  L3 Unified 16384 KiB (x1)
Load Average: 2.46, 2.25, 2.19
---------------------------------------------------------------------------
Benchmark                 Time             CPU   Iterations UserCounters...
---------------------------------------------------------------------------
BM_WriteStrings        2484 ms         2482 ms            1 items=8.38861M items_per_second=3.37956M/s
BM_WriteIntegers       1949 ms         1949 ms            1 items=8.38861M items_per_second=4.30485M/s
BM_WriteFloats         4720 ms         4719 ms            1 items=8.38861M items_per_second=1.77767M/s
BM_WriteBools          2167 ms         2166 ms            1 items=8.38861M items_per_second=3.87247M/s
BM_ReadStrings         1883 ms         1882 ms            1 items=8.38861M items_per_second=4.45776M/s
BM_ReadIntegers        1641 ms         1641 ms            1 items=8.38861M items_per_second=5.11252M/s
BM_ReadFloats          4173 ms         4172 ms            1 items=8.38861M items_per_second=2.01078M/s
BM_ReadBools           1898 ms         1898 ms            1 items=8.38861M items_per_second=4.4205M/s

Caveats

File Size

An .xlsx file is essentially a bunch of .xml files in a .zip archive. Internally, OpenXLSX uses the miniz library to compress/decompress the .zip archive, and it turns out that miniz has an upper limit regarding the file sizes it can handle.

The maximum allowable file size for a file in an archive (i.e. en entry in a .zip archive, not the archive itself) is 4 GB (uncompressed). Usually, the largest file in an .xlsx file/archive, will be the .xml files holding the worksheet data. I.e., the worksheet data may not exceed 4 GB. What that translates to in terms of rows and columns depend a lot on the type of data, but 1,048,576 rows x 128 columns filled with 4-digit integers will take up approx. 4 GB. The size of the compressed archive also depends on the data held in the worksheet, as well as the compression algorithm used, but a workbook with a single worksheet of 4 GB will usually have a compressed size of 300-350 MB.

The 4 GB limitation is only related to a single entry in an archive, not the total archive size. That means that if an archive holds multiple entries with a size of 4GB, miniz can still handle it. For OpenXLSX, this means that a workbook with several large worksheets can still be opened.

Memory Usage

OpenXLSX uses the PugiXML library for parsing and manipulating .xml files in .xlsx archive. PugiXML is a DOM parser, which reads the entire .xml document into memory. That makes parsing and manipulation incredibly fast.

However, all choices have consequences, and using a DOM parser can also demand a lot of memory. For small spreadsheets, it shouldn't be a problem, but if you need to manipulate large spreadsheets, you may need a lot of memory.

The table below gives an indication of how many columns of data can be handled by OpenXLSX (assuming 1,048,576 rows):

Columns
8 GB RAM 8-16
16 GB RAM 32-64
32 GB RAM 128-256

Your milage may vary. The performance of OpenXLSX will depend on the type of data in the spreadsheet.

Note also that it is recommended to use OpenXLSX in 64-bit mode. While it can easily be used in 32-bit mode, it can only access 4 GB of RAM, which will severely limit the usefulness when handling large spreadsheets.

If memory consumption is an issue for you, you can build the OpenXLSX library in compact mode (look for the ENABLE_COMPACT_MODE in the CMakeLists.txt file), which will enable PugiXML's compact mode. OpenXLSX will then use less memory, but also run slower. See further details in the PugiXML documentation here. A test case run on Linux VM with 8 GB RAM revealed that OpenXLSX could handle a worksheet with 1,048,576 rows x 32 columns in compact mode, versus 1,048,576 rows x 16 columns in default mode.

Unicode

By far the most questions I get about OpenXLSX on Github, is related to Unicode. It is apparently (and understandably) a source of great confusion for many people.

Early on, I decided that OpenXLSX should focus on the Excel part, and not be a text encoding/conversion utility also. Therefore, all text input/output to OpenXLSX MUST be in UTF-8 encoding... Otherwise it won't work as expected. It may also be necessary that the source code files are saved in UTF-8 format. If, for example, a source file is saved in UTF-16 format, then any string literals will also be in UTF-16. So if you have any hard-coded string literals in your source code, then the source file must also be saved in UTF-8 format.

All string manipulations and usage in OpenXLSX uses the C++ std::string, which is encoding agnostic, but can easily be used for UTF-8 encoding. Also, Excel uses UTF-8 encoding internally (actually, it might be possible to use other encodings, but I'm not sure about that).

For the above reason, if you work with other text encodings, you have to convert to/from UTF-8 yourself. There are a number of options (e.g. Boost.Nowide or Boost.Text). Internally, OpenXLSX uses Boost.Nowide; it has a number of handy features for opening files and converting between std::string and std::wstring etc. I will also suggest that you watch James McNellis' presentation at CppCon 2014, and read Joel Spolsky's blog.

Unicode on Windows is particularly challenging. While UTF-8 is well supported on Linux and MacOS, support on Windows is more limited. For example, output of non-ASCII characters (e.g. Chinese or Japanese characters) to the terminal window will look like gibberish. As mentioned, sometimes you also have to be mindful of the text encoding of the source files themselves. Some users have had problems with OpenXLSX crashing when opening/creating .xlsx files with non-ASCII filenames, where it turned out that the source code for the test program was in a non-UTF-8 encoding, and hence the input string to OpenXLSX was also non-UTF-8. To stay sane, I recommend that source code files are always in UTF-8 files; all IDE's I know of can handle source code files in UTF-8 encoding. Welcome to the wonderful world of unicode on Windows 🤮

Zip Library

An Excel-file is essentially just a bunch of .xml files wrapped in a .zip archive. OpenXLSX uses a 3rd party library to extract the .xml files from the .zip archive. The default library used by OpenXLSX is Zippy, which is an object-oriented wrapper around miniz. The miniz library is fast, and is header-only, which is ideal for OpenXLSX.

However, it is possible to use a different zip-library, if you want to. In rare cases, you may experience stability issues with miniz. In those cases, it may be useful to try a different zip-library.

Using the Zippy/miniz library requires no special efforts; it will work straight out of the box. Using a different zip-library, however, will require some work.

In order to use a different zip-library, you must create a wrapper class that conforms to the interface specified by the IZipArchive class. Note that this is implemented using type erasure, meaning that no inheritance is required; the class just needs to have a conforming interface, thats all. After that, provide an object of the class and supply it to the OpenXLSX constructor.

To see an example of how this is done, take a look at Demo1A in the Examples folder. This example uses a class called CustomZip (using libzip as the zip library) which can be found under Examples/external/CustomZip. In order to build the example program, make sure that libzip (and it's dependencies) is installed on your computer, and enable the OPENXLSX_ENABLE_LIBZIP option in the CMakeLists.txt file in the OpenXLSX root.

As mentioned, the Demo1A example program uses libzip. libzip is a very stable library and widely used. However, my experience is that it is quite slow for large zip files, such as large spreadsheets. For that reason, libzip may not be the ideal solution, but it is useful for showing how a different zip library can be used.

Example Programs

In the 'Examples' folder, you will find several example programs, that illustrates how to use OpenXLSX. Studying those example programs is the best way to learn how to use OpenXLSX. The example programs are annotated, so it should be relatively easy to understand what's going on.

Changes

New in version 0.4.x

OpenXLSX can now use other zip libraries than the default Zippy/miniz library. See Demo1A as an example of how it's done

New in version 0.3.x

This version includes row ranges and iterators. It also support assignment of containers of cell values to XLRow objects. This is significantly faster (up to x2) than using cell ranges or accessing cells by cell references.

New in version 0.2.x

The internal architecture of OpenXLSX has been significantly re-designed since the previous version. The reason is that the library was turning into a big ball of mud, and it was increasingly difficult to add features and fix bugs. With the new architecture, it will (hopefully) be easier to manage and to add new features.

Due to the re-design of the architecture, there are a few changes to the public interface. These changes, however, are not significant, and it should be easy to update:

  • All internal objects are now handled as values rather than pointers, similar to the approach taken in the underlying PugiXML library. This means that when requesting a certain worksheet from a workbook, the resulting worksheet is not returned as a pointer, but as an object that supports both copying and moving.
  • The distinction between interface objects and implementation objects are now gone, as it made it very difficult to manage changes. It was an attempt to implement the pimpl idiom, but it wasn't very effective. In the future, I may try to implement pimpl again, but only if it can be done in a simpler way.
  • All member functions have been renamed to begin with a small letter (camelCase), i.e. the member function WorksheetCount() is renamed to worksheetCount(). This was done mostly for cosmetic reasons.

I realise that these changes may cause problems for some users. Because of that, the previous version of OpenXLSX can be found in the "legacy" branch of this repository. However, I strongly recommend that you transition to the new version instead.

Credits

  • Thanks to @zeux for providing the awesome PugiXML library.
  • Thanks to @richgel999 for providing the miniz library.
  • Thanks to @tectu for cleaning up my CMakeLists.txt files.

openxlsx's People

Contributors

gvollant avatar i-do-cpp avatar joelpelaez avatar laighside avatar lodup29 avatar mario-lumedix avatar nenesekai avatar reder2000 avatar sisco0 avatar troldal avatar wciofqmandra avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

openxlsx's Issues

cell.ValueType() == XLValueType::Integer with formula

create a new xlsx,
in cell A1, write =SUM(1 + 1)

while reading, in my switch statement
cell.ValueType() is a XLValueType::Integer
and cell.Value().get() throws an exception.

I found I can use cellValue.AsString() as a workaround

Problem detecting column type

I have a problem with integer and date columns, for this I get the type OpenXLSX::XLValueType::Error and a exception when I try to get the value of this columns.

My test code:

#include <iostream>
#include <iomanip>
#include <OpenXLSX/OpenXLSX.h>

using namespace std;
using namespace OpenXLSX;

int main(int argc, char** argv)
{
   XLDocument doc;

   if (argc < 2)
   {
      cout << "Usage: " << argv[0] << " <file.xlsx>" << endl;
      return 1;
   }

   doc.OpenDocument(argv[1]);
   auto wks = doc.Workbook().Worksheet("Tabelle1");

   auto tA2 = wks.Cell("A2").ValueType();

   if (tA2 == OpenXLSX::XLValueType::Error)
      cout << "Format error at cell A2" << endl;

   auto tC2 = wks.Cell("C3").ValueType();

   if (tC2 == OpenXLSX::XLValueType::Error)
      cout << "Format error at cell C2" << endl;

   auto tD2 = wks.Cell("D2").ValueType();

   if (tD2 == OpenXLSX::XLValueType::Error)
      cout << "Format error at cell D2" << endl;

   auto A1 = wks.Cell("A1").Value().Get<std::string>();
   auto A2 = wks.Cell("A2").Value().Get<unsigned int>();
   auto B1 = wks.Cell("B1").Value().Get<std::string>();
   auto B2 = wks.Cell("B2").Value().Get<std::string>();

   cout << "Cell A1: " << A1 << endl;
   cout << "Cell A2: " << A2 << endl;
   cout << "Cell B1: " << B1 << endl;
   cout << "Cell B2: " << B2 << endl;

   return 0;
}

The result:

~/build/playground> excel U1.xlsx 
Format error at cell A2
Format error at cell C2
terminate called after throwing an instance of 'OpenXLSX::XLException'
  what():  Cell value is not Integer

U1.xlsx

Many thanks and best regards

Load XLSX File question

Let's suppose that I have a big XLSX file (like 50GB of data in a single file), but the data are splited in multiples Excel tabs.

So, I would like to load just a single data located in a specific Excel tab using the OpenXLSX library.

Now, I would like to know:
Will the OpenXLSX library load fisrt the entire 50GB in memory, and than after return to me the specific data that I need?
Or is there a way to load just the data in the specific Excel tab, without load any other file data?

don't support chinese????

i use your library, write chinese in cell and create a xlsx , so using excel to open the file ,but it warning me that file is damaged;

OpenXLSX Access violation opening a file

Exception thrown: read access violation.
this->m_relationshipNode._root was 0xFFFFFFFFFFFFFFC7.

the offending file is attached to OpenXLSX fails to read file which can be opened in Excel and other libraries #36

Issue in the amount of data saved in sheet.xml

I found a second issue, which I had after saving an existing xlsx file with only one cell modified : the layout changed.

To reproduce the problem, I reduced my file to the minimum :
good.xlsx
This is a very simple file, with A and B column always visible and a fixed number of columns.

When I execute this code :

int main()
{
  XLDocument doc;

  doc.OpenDocument("./good.xlsx");

  auto wks = doc.Workbook().Worksheet("2019");

  wks.Cell("K11").Value() = 77;

  doc.SaveDocument();
  doc.CloseDocument();

  return 0;
}

I obtain a file that lost the limited column number :
bad.xlsx

You can notice the problem when looking at the horizontal slide bar in both documents.
It also dramatically increases the file size as a consequence.
I am not sure the fact that I modify one cell has something to do with it, since I think I also had the problem without modifying anything (not sure though).

I think that the document should keep its features after being opened and modifed by the lib.

Compiling to web assembly? Has anyone tried it?

I need to be able to read an Excel file from within a web assembly. Has anyone tried using OpenXLSX?

I've tried compiling XLCell.cpp, but it failed on a few things.

Thanks in advance,

Tony.

can't find string_view

Hi, thanks for your excellent work! But I use cmake+vs 2015 , it produce an error "can't find <string_view>" . I know it the feature of c++17, so I want to ask: how to fix it or which version can compile with vs2015?

Date Time

Posting here as it was hard to find, number styles map to styles.xml, attribute numFmtId.

if ((numFmtId >= 14 && numFmtId <= 22) || (numFmtId >= 165 && numFmtId <= 180))
{
// Is a date
// greater than 165 is custom and adds new children , I.e.
// -numFmts count="2"-
// -numFmt numFmtId="165" formatCode="m/d/yy\ h:mm;@"/-
// -numFmt numFmtId="166" formatCode="[$-409]m/d/yy\ h:mm\ AM/PM;@"/-
// -/numFmts-
}

The time data is COleDateTime, windows users can do something like

SYSTEMTIME st;
COleDateTime time;
time.m_dt = cellValue.Get();
 if (time.GetAsSystemTime(st))
      t = CTime(st).GetTime();

Numbers below 0.003 can not be recognized as float.

Environment: Windows 10, Office 365

For example, here is a excel file created by office 365.
NumberTest.xlsx

In this file, numbers below 0.003 can not be recognized as float.
"0.002" can only be read by "Get<int>()" and the result is "2" which is obviously wrong.
"0.001" can only be read by "Get<int>()" and the result is "1" which is obviously wrong.
"0.0021" can not be read by "Get<int>()" or "Get<float>()" and I don't know how to read this number.

All these numbers cannot be read by "Get<float>()".I tried alot of times. And it throws an exception:"XLException".

precision loss when I read 0.001

it seems that when i try to read a cell which is 0.02, which result is correct. However, when I try to read a cell which is 0.002, it will be recognized as type XLValueType::Integer and value would be 2,which is not what i want.
it will cause loss of precision when I try to read 0.001,0.002...

compile with C++11

Hello Troldal,

I'm using the library in my project which is very cool I would say.

However, my existing library does not compatible with C++17 standard. Is there a way to compile or convert the C++17 features to C++11 compatible?

Thank you very much.

OpenXLSX seems to ignore whitespaces

Hello,

First of all, thank you for your work.
I think there is a problem with cells that contains only spaces.
For instance, when I execute :

int main()
{
    XLDocument doc;

    doc.CreateDocument("./t.xlsx");

    auto wks = doc.Workbook().Worksheet("Sheet1");

    wks.Cell(1, 1).Value() = "    ";

    doc.SaveDocument();
    doc.CloseDocument();

    return 0;
 }

The document created does not contain the spaces, whearas when I write "test" for example, it works.
I could also notice the problem when opening an existing document containing cells with only spaces and saving it right afterwards : the spaces disappear.

Thank you for your time.

Using in Visual Studio (Missing .lib file)

I'm trying to use this library in a program that is written with Visual Studio. From what I've read, I need 3 things to do this: the dll file, the header files, and an import library (.lib) file. I've been able to get the dll file and header files successfully but I can't seem to get the lib file.

Since the Readme says that trying to compile in Windows can have issues, I set up Ubuntu 18.04 in the Windows Subsystem for Linux. Then I installed x86_64-w64-mingw32-gcc and x86_64-w64-mingw32-gcc and followed CMake's guide to cross compiling to Windows. This successfully compiled the code to the dll and put all the headers in a folder.

Am I missing something to get the lib file?

Is OpenXLSX thread safe

Hello,

If I compile OpenXLSX into a .dylib on MacOSX and use it in a multi-threaded environment does OpenXLSX support multi threading? Is it thread safe?

How to read rows

Could u pls help me

How to read data row by row like foreach , coz I don't know the row size.

About how to use this library and these download links...

ok, i read through these close issues..
and too, i found these questions and confusions as they do.
also, i am from china.

THE QUESTIONS ARE:

  1. i cmake. under ubuntu and vs2019, but cann't go through because of these download links, failed..maybe because of the great wall.
  2. i try to install zippy (but aptitude search Zippy, no results; so i choose to install miniz. ) and pugixml (install sucess, pugixml.hpp is in dir: /usr/include ), but still cann't go through to make step..so, no openxlsx_export.h file, and also barks for "pugi dose not name a type.." in the step " make -j8" if i ignore the failed before this step

SUGGESTIONS:
make some lines of how to use this lib in README
maybe for these newbies as me...

m_sheetNameNodes not being populated properly?

image

dear @troldal , When i try to open an existing workbook with multiple sheets, The plugin properly finds the sheets and populates it under m_sheetNodes, but m_sheetnameNodes is not populated as expected(test1 which is an existing sheet is missing, sheet1 is something that i added using the plugin)

Is this expected?what is the significance of m_sheetNameNodes? what does it contain?

the exception for missing sheet is thrown inside this function

XLWorksheet * XLWorkbook::Worksheet(const std::string &sheetName)
{
    if (m_sheets.find(sheetName) == m_sheets.end()) throw std::range_error("Sheet \"" + sheetName + "\" does not exist");

    if (m_sheets.at(sheetName) == nullptr) {
        m_sheets.at(sheetName) = make_unique<XLWorksheet>(*this, sheetName, **m_sheetPaths.at(sheetName))**;
        m_childXmlDocuments.at(m_sheetPaths.at(sheetName)) = m_sheets.at(sheetName).get();
    }

Thanks for the awesome job!!!

build problem on aix

Hi,
I have a problem with building source code on ibm aix unix.
I've got there cmake version 3.14.3
gcc/g++ version 7.2.0 (the newest for ibm aix)

The building fails on the errors:
/opt/freeware/gcc7/lib/gcc/powerpc-ibm-aix7.1.0.0/7.2.0/include/c++/bits/stl_heap.h:108:3: error: template with C linkage
template

Any suggestions?

Thx a lot
Tomas

Return type OpenXLSX::XMLDocument is ambiguous with OpenXLSX::Impl XLDocument

Visual studio 2019 x64 compile failed, I had to fully qualify
OpenXLSX::XMLDocument* Impl::XLCell::XmlDocument()
const OpenXLSX::XMLDocument* Impl::XLCell::XmlDocument() const
OpenXLSX::XMLDocument* Impl::XLAbstractXMLFile::XmlDocument()
const OpenXLSX::XMLDocument* Impl::XLAbstractXMLFile::XmlDocument() const

Remove @

Hi!

Is it possible to remove @ from dirs? It is very uncommon.

cmake version

Dear @troldal, I wonder two things.

1️⃣ I would like to know the cmake version that applies to this project.

I failed to make makefile with cmake version 3.10. (Is 3.9 minimum requirement?)

I succeeded with cmake version 3.13.2.

  • Ubuntu 18.04 (WSL)
    • Linux DESKTOP-AJ9QK8Q 4.4.0-17763-Microsoft #253-Microsoft Mon Dec 31 17:49:00 PST 2018 x86_64 x86_64 x86_64 GNU/Linux
  • cmake version 3.13.2
  • gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0
  • GNU Make 4.1, Built for x86_64-pc-linux-gnu

2️⃣ Do you know why the following make error message occurs?

/github/OpenXLSX$ make
[  9%] Built target zlib
[ 18%] Built target bzip
[ 28%] Built target libzip
[ 37%] Built target libzip++
[ 47%] Built target pugi
[ 77%] Built target OpenXLSX
[ 78%] Linking CXX executable ../../install/bin/Benchmark
/mnt/d/workspace/github/OpenXLSX/install/lib/libOpenXLSX.so: undefined reference to `HMAC_Final'
/mnt/d/workspace/github/OpenXLSX/install/lib/libOpenXLSX.so: undefined reference to `AES_encrypt'
/mnt/d/workspace/github/OpenXLSX/install/lib/libOpenXLSX.so: undefined reference to `RAND_bytes'
/mnt/d/workspace/github/OpenXLSX/install/lib/libOpenXLSX.so: undefined reference to `AES_set_encrypt_key'
/mnt/d/workspace/github/OpenXLSX/install/lib/libOpenXLSX.so: undefined reference to `HMAC_CTX_init'
/mnt/d/workspace/github/OpenXLSX/install/lib/libOpenXLSX.so: undefined reference to `HMAC_CTX_cleanup'
/mnt/d/workspace/github/OpenXLSX/install/lib/libOpenXLSX.so: undefined reference to `EVP_sha1'
/mnt/d/workspace/github/OpenXLSX/install/lib/libOpenXLSX.so: undefined reference to `HMAC_Update'
/mnt/d/workspace/github/OpenXLSX/install/lib/libOpenXLSX.so: undefined reference to `HMAC_Init_ex'
/mnt/d/workspace/github/OpenXLSX/install/lib/libOpenXLSX.so: undefined reference to `PKCS5_PBKDF2_HMAC_SHA1'
collect2: error: ld returned 1 exit status
@examples/@benchmark/CMakeFiles/Benchmark.dir/build.make:83: recipe for target 'install/bin/Benchmark' failed
make[2]: *** [install/bin/Benchmark] Error 1
CMakeFiles/Makefile2:381: recipe for target '@examples/@benchmark/CMakeFiles/Benchmark.dir/all' failed
make[1]: *** [@examples/@benchmark/CMakeFiles/Benchmark.dir/all] Error 2
Makefile:129: recipe for target 'all' failed
make: *** [all] Error 2

Can I get information about the environment where the build succeeds?

And my native language is not English and my English is not fluent. Please, use EASY English. :-)

Can't read xlsm-file

Is xlsm format supported?
I open a document, but I get an error when calling Worksheet ("Sheet1")

Error text:
terminate called after throwing an instance of 'std :: invalid_argument'
   what (): stoul
Process finished with exit code 134

Version of Mingw

Which version of mingw are you using? Im using gcc version 8.2.0 (MinGW.org GCC-8.2.0-3)

And i get the following error. when compiling on windows 8.1

[ 0%] Building C object lib/CMakeFiles/zip.dir/zip_add.c.obj
In file included from C:\Users\M\Desktop\Ironchef_excel\OpenXLSX-master\build@l
ibrary\libzip-prefix\src\libzip\lib\zip.h:61,
from C:\Users\M\Desktop\Ironchef_excel\OpenXLSX-master\build@l
ibrary\libzip-prefix\src\libzip\lib\zipint.h:53,
from C:\Users\M\Desktop\Ironchef_excel\OpenXLSX-master\build@l
ibrary\libzip-prefix\src\libzip\lib\zip_add.c:36:
c:\mingw\include\stdio.h:345:12: error: expected '=', ',', ';', 'asm' or 'attr
ibute
' before '__mingw__snprintf'
extern int mingw_stdio_redirect(snprintf)(char*, size_t, const char*, ...);

        ^~~~~~~~~~~~~~~~~~~~~~~~

lib\CMakeFiles\zip.dir\build.make:62: recipe for target 'lib/CMakeFiles/zip.dir/
zip_add.c.obj' failed

get many _x000d if cell contain \n

I'm trying to use OpenXLSX to read XLSX. It's a great library but I find something strange. If a cell's value contains multiple lines, the "\n" will be read as x000d.
"abc
abc" will become "abc_x000d__000d
..abc"
any idea?

I fail to get the value for a Cell whose language is Korean mixed English.

Just like I say, I try to get the value for a Cell whose language is Korean mixed English lile "abc 초".In my test,A Cell which is Korean is OK,as well as English.But A mixed Cell get empty string.Here is the code:

    using namespace OpenXLSX;
    XLDocument document;
    document.OpenDocument(R"(../Ress/ress.xlsx)");
    XLWorkbook xlWorkbook = document.Workbook();
    XLWorksheet worksheet = xlWorkbook.Worksheet("Sheet1");

    string a1 = worksheet.Cell("A1").Value().AsString();
    string b1 = worksheet.Cell("B1").Value().AsString();
    string c1 = worksheet.Cell("C1").Value().AsString();
    cout << a1.size() << " " << b1.size() <<" "<< c1.size() << endl;  //0 4 3
    cout << a1 << " " << b1 << c1 << endl;  // abcd 분

here is the file:
https://github.com/MagicGeeker/tmp/blob/master/ress.xlsx
I unzip the xlsx ,it seems that excel split the Cell

Compile Project

XLCellReference.h(52): 'openxlsx_export.h' file not found

I am trying to compile Demo1.cpp into a project and I get this error.
I can't find this file in the downloaded packages.
Can you help me?
Thank you for your attention!

Larger Files Support: >2GB

Hi, I would like to know if I can use this library to generate larger XLSX files larger then 2GB.
Is this possible?

Missing include file openxslsx_export.h

Hi,

that sounds already like a stupid question but I'm not able to compile your library due to a seemingly missing include file "openxlsx_export.h". It's being referred to in pretty much every other include file.

Thanks!

More examples

Very good work so far!

Can you please provide some more examples or documentation on how:

  • to open and read a document
  • read a certain sheet (given by name/index) out of the document

Would really appreciate that!

Target does not exist(running)

code:
std::setlocale(LC_ALL, "ru_RU.UTF-8");
std::string filename="/home/file.xlsx";
XLDocument doc;
doc.OpenDocument(filename);
err:
terminate called after throwing an instance of 'OpenXLSX::XLException'
what(): Target does not exist!

Build document failed

Hello, When I build this project with "CREATE_DOCS=ON", I met an error below:

error: problems opening rtf file namespaces.rtf for reading
An error occurred during post-processing the RTF files!

It seems the file namespaces.rtf could not be found. After check, I found this issue is related with a Doxygen config option: "SHOW_NAMESPACES"

In project file doxy/Doxyfile.in, the option is:
SHOW_NAMESPACES = NO

If I change it to "SHOW_NAMESPACES = YES", then the issue could not be reproduced.

My environment is Ubuntu 14.04/18.04.
Could you help to check this issue and fix it?

Thanks

Can i get hyperlink by cell?

doc.Workbook().Worksheet("Sheet1").CellReference().Address()
is not a hyperlink. Just return this cell's address

Demo5: error C2059 syntax error: 'type'

I built openxlsx library and demos successfully using msvc 2019. I test the library and demos using msvc 2015, it works except Demo5. Here's the detail error message:

image

Help!

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.