Giter VIP home page Giter VIP logo

openxlsx's Introduction

Hi there 👋 My name is Kenneth

My name is Kenneth Troldal Balslev. I actually don't have a background in Software Engineering, but in Chemical Engineering. I have worked for 16 years in the upstream oil/gas industry with Maersk Oil and Total, mostly as a Facilities Development Engineer, where I have worked with static and dynamic simulation, field development planning for large onshore and offshore fields, capital cost estimation, technical evaluations for M&A activities, as well as studied how new and emerging technologies can be used in the oil industry in the future.

I had a lot of fun in the oil industry, but a few years ago, I quit my job so I could explore Asia. I enjoyed that very much...until COVID-19 happened. So now I'm back in Denmark, and just recently began working as a project engineer at Nordic Sugar (a part of Nordzucker group). A different industry indeed, but no less interesting than the industry I worked in before.

While on the road, I'v spend some time on another passion of mine: computer programming. Mostly C++, but also some Python.

🔭 I’m currently working on OpenXLSX, a C++ library for manipulating Excel spreadsheets. This is my main project, and one I will continue to work on in the forseeable future.

🌱 I know a bit of Python, but I want to improve my skills. I have a plan of creating a Python interface to my OpenXLSX library. Python already have several Excel libraries, so it will mostly be for fun and to learn Python. I also want to learn C#.

⚡ Being a Chemical Engineer, I have an ambition of creating a library for doing Vapor-Liquid Equilibrium (e.g. multiphase flash) calculations. No cross-platform open-source library exists for that (that I'm aware of), and the commercial products cost a small fortune.

📫 You can reach me on [email protected] or at my LinkedIn profile: https://www.linkedin.com/in/kennethbalslev/

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

openxlsx's Issues

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

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.

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

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?

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

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?

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!

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

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

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!

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?

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

Can i get hyperlink by cell?

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

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!

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

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!

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

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.

Remove @

Hi!

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

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;

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?

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

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!

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.

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?

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

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

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.

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?

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

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

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

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

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.