Qt 读写 Execl 之第三方库 libxl 实战版

官方资料及代码示例

Direct reading and writing Excel files
LibXL is a library that can read and write Excel files. It doesn’t require Microsoft Excel and .NET framework, combines an easy to use and powerful features. Library can be used to

• Generate a new spreadsheet from scratch
• Extract data from an existing spreadsheet
• Edit an existing spreadsheet

LibXL can help your applications in exporting and extracting data to/from Excel files with minimum effort. Also it can be used as report engine. Library can be used in C, C++, C#, Delphi, PHP, Python, PowerBASIC, Xojo, Fortran and other languages. Supports Excel 97-2003 binary formats (xls) and Excel 2007-2016 xml formats (xlsx/xlsm). Supports Unicode and 64-bit platforms. There are a wrapper for .NET developers and separate Linux, Mac and iOS editions. See features of the library in demo.xls or demo.xlsx files.
Simple interoperate, no more Excel dependency
LibXL has C/C++ headers, Delphi unit and .NET assembly for including in your project. No OLE automation.
High performance
Writing speed is about 2 100 000 cells per second for numbers and 240 000 cells per second for 8-character random strings in binary xls format (CPU 3.2 GHz).
Royalty-free distribution with your application
Our customers can use this library in their commercial applications without any additional fees.

官网资料 www.libxl.com
下载传送门：libxl 下载

Code example - C

#include "libxl.h"


int main()
{
    BookHandle book = xlCreateBook(); // xlCreateXMLBook()
    if(book) 
    {
        SheetHandle sheet = xlBookAddSheet(book, L"Sheet1");
        if(sheet) 
        {
            xlSheetWriteStr(sheet, 2, 1, L"Hello, World !", NULL);
            xlSheetWriteNum(sheet, 3, 1, 1000, NULL);
        }
        xlBookSave(book, L"example.xls");
        xlBookRelease(book);
    }
    return 0;
}

Code example - C++

#include "libxl.h"
using namespace libxl;

int main() 
{
    Book* book = xlCreateBook(); // xlCreateXMLBook() for xlsx
    if(book)
    {
        Sheet* sheet = book->addSheet(L"Sheet1");
        if(sheet)
        {
            sheet->writeStr(2, 1, L"Hello, World !");
            sheet->writeNum(3, 1, 1000);
        }
        book->save(L"example.xls");
        book->release();
    } 
    return 0;
}

Code example - C#

class Program
{ 
    static void Main(string[] args)
    {
        try 
        {
            Book book = new BinBook(); // use XmlBook() for xlsx
            Sheet sheet = book.addSheet("Sheet1");
            sheet.writeStr(2, 1, "Hello, World !");
            sheet.writeNum(3, 1, 1000);
            book.save("example.xls");    
        }
        catch (System.Exception e)
        {
            Console.WriteLine(e.Message);
        }
    }
}

Code example - Delphi

var

  Book: TBook;
  Sheet: TSheet;

begin

  Book := TBinBook.Create; // use TXmlBook() for xlsx
  Sheet := Book.addSheet('Sheet1');

  Sheet.writeStr(2, 1, 'Hello, World !');
  Sheet.writeNum(3, 1, 1000);

  Book.save('example.xls');
  Book.Free;

end;

官方读写示例

官方给出了详细的使用教程

LibXL is a library for direct reading and writing Excel files.


Package contents:

  bin              32-bit dynamic library (libxl.dll) and libraries with different names for 32-bit and 64-bit (can be used in the same folder)
  bin64            64-bit dynamic library (libxl.dll)
  doc              C++ documentation
  examples         C, C++, C#, Delphi and Fortran examples (MinGW, Visual Studio, Qt, Code::Blocks)
  include_c        headers for C
  include_cpp      headers for C++
  lib              Microsoft Visual C++ 32-bit import libraries for libxl.dll/libxl32.dll and 64-bit import library for libxl64.dll in the bin folder
  lib64            Microsoft Visual C++ 64-bit import library for libxl.dll in the bin64 folder
  net              .NET wrapper (assembly)
  php              compiled plug-in for PHP 
  stdcall          32-bit dynamic library with stdcall calling convention
  changelog.txt    change log
  libxl.url        link to home page
  license.txt      end-user license agreement
  readme.txt       this file


Using library:


1. Microsoft Visual C++

   - add include directory in your project, for example: c:\libxl\include_cpp

     Project -> Properties -> C/C++ -> General -> Additional Include Directories

   - add library directory in your project, for example: c:\libxl\lib
    
     Project -> Properties -> Linker -> General -> Additional Library Directories

   - add libxl.lib in project dependencies:
    
     Project -> Properties -> Linker -> Input -> Additional Dependencies

   - copy bin\libxl.dll to directory of your project
     

2. MinGW

   Type in examples/c++/mingw directory:

     g++ generate.cpp -o generate -I../../../include_cpp -L../../../bin -lxl
   
   Use mingw32-make for building examples.


3. C# and other .NET languages

   Use assembly libxl.net.dll in net directory: Project -> Add reference... -> Browse

   Also copy bin\libxl.dll to Debug or Release directory of your project.


4. Qt

   - add the following lines to a configuration file (.pro):

     INCLUDEPATH = c:/libxl-3.6.4.0/include_cpp
     LIBS += c:/libxl-3.6.4.0/lib/libxl.lib

   - copy bin\libxl.dll to the build directory of your project   
   

5. Borland C++ and Embarcadero C++ Builder

   - create an import library for your compiler:

       implib -a libxl.lib libxl.dll
     
   - add the include directory to your project, for example: c:\libxl-3.9.1.0\include_cpp
  
     Project -> Options -> Building -> C++ Compiler -> Directories and Conditionals -> Include file search path

     or 

     Project -> Options -> Directories/Conditionals -> Include path (for old C++ Bulder versions)

   - add library directory to your project (only for old C++ Builder versions)

     Project -> Options -> Directories/Conditionals -> Library path

   - add libxl.lib to your project

     Project -> Add to Project...

   - copy libxl.dll from the bin folder to <your_project_directory>/Win32/Debug or <your_project_directory>/Win32/Release folder

     If your target is "Windows 64-bit" copy libxl.dll from the bin64 folder.

6. Delphi

   - add the directory with the LibXL.pas unit, for example: c:\libxl-3.9.1.0\examples\delphi12

     Project -> Options -> Building -> Delphi Compiler -> Search path

   - copy libxl.dll from the bin folder to <your_project_directory>\Win32\Debug or <your_project_directory>\Win32\Release folder

Documentation:

  http://www.libxl.com/doc

Contact:

  support@libxl.com

甚至还给出了各语言的使用例子

├─c
├─c#
├─c++
├─cppbuilder
├─delphi12
├─delphi7
├─fortran
├─powerbasic
├─python
├─visualbasic
└─xbase++

Qt 使用

.pro 头文件添加如下内容

INCLUDEPATH += $$PWD/src/include_cpp
contains(QT_ARCH, i386) {
       LIBS +=   -L$$PWD/src/ib -llibxl32
    } else {
       LIBS +=   -L$$PWD/src/lib -llibxl64
 }        

头文件引用

#include "libxl.h"
using namespace libxl;

文件读取

Book *book = NULL;
if(filePath.endsWith("xls", Qt::CaseInsensitive)) { //判断是否是.xls文件，不区分大小写
    book = xlCreateBook(); // xlCreateBook() for xls
} else if(filePath.endsWith("xlsx", Qt::CaseInsensitive)) { //判断是否是.xlsx文件，不区分大小写
    book = xlCreateXMLBook(); // xlCreateXMLBook() for xlsx
}
book->setKey(L"XXXX", L"windows-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX");//注册
bool f = book->load( reinterpret_cast<const wchar_t *>(filePath.utf16())); 
CellType ct;
QString strData = QString();
if(book) {
    Sheet *sheet = book->getSheet(0); //表1
    int  cellnum = 5;
//        qDebug() << "row:" << sheet->lastRow(); //有多少行，注意：有些行是空行，没有数据但是也要计入
    for (int i = 0; i < sheet->lastRow(); i++) {
        for (int j = 0; j < cellnum; j++) {//取前5列数据
            ct = sheet->cellType(i, j); //单元格的数据类型  1表示是数字  2表示是字符串
            if(ct == 2) {
                qDebug()<< sheet->readStr(i, j);//读取字符串
            } else if(ct == 1) {
                qDebug()<<(int)sheet->readNum(i, j);//读取数字
            }
        }            
    }
} else {
    qDebug() << "文件打开失败！";
}

写入数据

//call xlBookSetKeyPrivate(book,"GCCG","windows-282123090cc0e6036db16b60a1o3p0h9")
Book *book = NULL;
 if(fname.endsWith("xls", Qt::CaseInsensitive)) { //判断是否是.xls文件，不区分大小写
     //qDebug() << "xls";
     book = xlCreateBook(); // xlCreateBook() for xls
 } else if(fname.endsWith("xlsx", Qt::CaseInsensitive)) { //判断是否是.xlsx文件，不区分大小写
     book = xlCreateXMLBook(); // xlCreateXMLBook() for xlsx
 }
 book->setKey(L"XXXX", L"windows-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX");//注册码
 if(book) {
     Sheet* sheet = book->addSheet(L"Sheet1");
     if(sheet) {        
	            sheet->writeStr(2, 1, L"Hello, World !");//写入字符串
				sheet->writeNum(3, 1, 1000);//写入数字
     }
     book->save(reinterpret_cast<const wchar_t *>(fname.utf16()));
     book->release();
     qDebug()<<"write complete!");
 }

需要注意的是，如果没有注册激活，第一行会被写入未激活信息，且只能操作前三百行数据