troldal / openxlsx Goto Github PK

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

thx for reading.

best regards.

Could u pls help me

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

Encounter some xlsx files that can not be parsed by XLNT, but can be parsed by the c lib: https://github.com/brechtsanders/xlsxio

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();

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?

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!!!

@openxlsx\implementation\sources\XLColor_Impl.cpp uses min() calls from std but no header provided.

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?

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!

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...

"wks.ColumnCount()" and "wks.RowCount()" is accroding to "LastCell()"'s position, but why if I create a new sheet, the ColumnCount is 1 and RowCount is 0 ?
The same as other sheet, ColumnCount always bigger than real it is.

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

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!

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?

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

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

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!

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...

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:

Help!

Invalid characters in string.

I have no idea to solve this problem. So I download all the files manually.

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. :-)

cell.ValueType should be expanded to handle currency, date, etc
no formatting would be necessary, I.e. should just return a double or int respectively ,but if I know a cell is a date, it can be handled.

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.

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.

There is one more library here on Github (https://github.com/tfussell/xlnt) which can be interesting.

This may be similar to #10 as I also get a Target does not exist! exception in OpenDocument. I have attached a minimal example file which may help reproduce the issue.

read-tdne.xlsx

Hi!

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

When opening a document and set a value to a cell (using Value() = ), it seems that cell aligment format is lost. Vertical align goes to bottom and horizontal aligment is set to none.

XLColumn* colNode = &m_columns.at(columnNumber - 1); // vector is 0-based;

Here

and how to use this library in my projects?

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;

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?

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

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!

for instance, I want cell having bold font and grey background in first row, usually header row.

thanks.

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.

I create one .xlsx by OpenXLSX，and write something in it then save it. It's 160KB, and then I open it by Excel, and save it. it's 72KB. why?

I tried to compile the project and found:

After reviewing the Microsoft official CRT documentation, I think this header file needed to include a header file:

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

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

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

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.

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?

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

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".

In file @library\@openxlsx\implementation\sources\XLDocument_Impl.cpp
#include <libzip++.h> must be #include <libzippp.h>

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

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