troldal / openxlsx Goto Github PK
View Code? Open in Web Editor NEWA C++ library for reading, writing, creating and modifying Microsoft Excel® (.xlsx) files.
License: BSD 3-Clause "New" or "Revised" License
A C++ library for reading, writing, creating and modifying Microsoft Excel® (.xlsx) files.
License: BSD 3-Clause "New" or "Revised" License
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!!!
and how to use this library in my projects?
Invalid characters in string.
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.
Hi!
Is it possible to remove @ from dirs? It is very uncommon.
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?
doc.Workbook().Worksheet("Sheet1").CellReference().Address()
is not a hyperlink. Just return this cell's address
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...
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
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
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?
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
There is one more library here on Github (https://github.com/tfussell/xlnt) which can be interesting.
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!
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
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!
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 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.
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
Many thanks and best regards
Could u pls help me
How to read data row by row like foreach , coz I don't know the row size.
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
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.
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. :-)
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".
XLColumn* colNode = &m_columns.at(columnNumber - 1); // vector is 0-based;
Here
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.
for instance, I want cell having bold font and grey background in first row, usually header row.
thanks.
@openxlsx\implementation\sources\XLColor_Impl.cpp
uses min() calls from std but no header provided.
"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.
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();
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!
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'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?
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.
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.
Encounter some xlsx files that can not be parsed by XLNT, but can be parsed by the c lib: https://github.com/brechtsanders/xlsxio
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;
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.
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
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
Very good work so far!
Can you please provide some more examples or documentation on how:
Would really appreciate that!
Hi, I would like to know if I can use this library to generate larger XLSX files larger then 2GB.
Is this possible?
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:
SUGGESTIONS:
make some lines of how to use this lib in README
maybe for these newbies as me...
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?
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?
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
In file @library\@openxlsx\implementation\sources\XLDocument_Impl.cpp
#include <libzip++.h>
must be #include <libzippp.h>
thx for reading.
best regards.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.