LibXL库使用详解---增删查改（篇二）

来源：互联网发布：word vba编程代码大全编辑：程序博客网时间：2024/06/06 15:41

LibXL库使用详解—增删查改（篇二）

1. 将数据存储到execl表格中

程序中索引是从0开始，excel中是从1开始

bool myinit(){    Book *book = xlCreateBook();    if (!book)    {        return false;    }    Sheet *sheet = book->addSheet(L"Mysheet1");    if (!sheet)    {        return false;    }    //设置标题字体    Font *titleFont = book->addFont();    titleFont->setName(L"Arial Black");    titleFont->setColor(COLOR_RED);//设置颜色为红色    titleFont->setSize(20);//设置字体大小    Format* titleFormat = book->addFormat();    titleFormat->setAlignH(ALIGNH_CENTER);//设置中心对齐    titleFormat->setFont(titleFont);    //设置属性字体和格式    Font *attributeFont = book->addFont();    attributeFont->setName(L"宋体");    attributeFont->setBold(TRUE);    Format *attributeFormat = book->addFormat();    attributeFormat->setAlignH(ALIGNH_CENTER);    attributeFormat->setFont(attributeFont);    //设置金额格式    Format *rmbFormat = book->addFormat();     rmbFormat->setAlignH(ALIGNH_LEFT);    rmbFormat->setNumFormat(NUMFORMAT_CURRENCY_NEGBRARED);    //设置日期格式    Format *dateFormat = book->addFormat();    dateFormat->setNumFormat(NUMFORMAT_DATE);    //合并第一行的0-4列    sheet->setMerge(0, 0, 0, 4);    //设置行的宽度    sheet->setCol(1, 1, 25);    //设置列高    sheet->setRow(1, 15);    //写入字符串数据    sheet->writeStr(0, 0, L"日程安排表", titleFormat);    sheet->writeStr(1, 0, L"序号", attributeFormat);    sheet->writeStr(1, 1, L"线路", attributeFormat);    sheet->writeStr(1, 2, L"人数", attributeFormat);    sheet->writeStr(1, 3, L"金额", attributeFormat);    sheet->writeStr(1, 4, L"日期", attributeFormat);    //填写表格    for (int i = 0; i < 5; ++i)    {        sheet->writeNum(i+2, 0, i);//序号、        sheet->writeStr(i + 2, 1, L"陕西-->西安-->周至-->尚村");//线路        sheet->writeNum(i + 2, 2, rand() % 400);//人数        sheet->writeNum(i + 2, 3, rand() % 400, rmbFormat);//金额        sheet->writeNum(i + 2, 4, book->datePack(2017, 6, 12), dateFormat);    }    if (book->save(L"xcxc.xls"))    {//      ::ShellExecute(NULL, L"open", L"xcxc.xls", NULL, NULL, SW_SHOW);    }    book->release();    return true;}

打开表格显示如下：

init

Font类是用来设置字体的一些属性，比如字体风格、颜色等
Format是用来控制数据在单元格中的格式，比如中心对齐、时间格式、金钱格式等

2. 增加数据

//添加数据    sheet->insertRow(2, 2);    sheet->writeNum(2, 0, 99);//序号、    sheet->writeStr(2, 1, L"小妖-->-->敏敏-->尚村-->武功");//线路    sheet->writeNum(2, 2, 2);//人数    sheet->writeNum(2, 3, 99999, rmbFormat);//金额    sheet->writeNum(2, 4, book->datePack(2016, 6, 12), dateFormat);

打开表格显示如下：

add

3. 查询数据

int rowfirst = sheet->firstRow();    int rowlast = sheet->lastRow();    int colfirst = sheet->firstCol();    int collast = sheet->lastCol();    wcout.imbue(locale(locale(), "", LC_CTYPE));//unicode 宽字节中文输出    wcout << L"数据开始行 ：" << rowfirst << endl;    wcout << L"数据结束行 ：" << rowlast << endl;    wcout << L"数据开始列 ：" << colfirst << endl;    wcout << L"数据结束列 ：" << collast << endl;    for (int i = rowfirst + 2; i < rowlast; ++i)    {        int serial = sheet->readNum(i, 0);        const TCHAR *address = sheet->readStr(i, 1);        int number = sheet->readNum(i, 2);        double dollar = sheet->readNum(i, 3);        int year, month, day;        book->dateUnpack(sheet->readNum(i, 4), &year, &month, &day);        wcout << setw(4)<< L"序号：" << setw(4) << serial << setw(4) << L"  地址:"<< setw(20) << address<< setw(4) << L"  人数:"<< setw(4) << number<< setw(4) << L"  金额:"<< setw(5) << dollar<< L"  日期:"<< year << L"-" << month << L"-" << day << endl;    }

打开表格显示如下：

select

4. 改动数据&删除数据

    //改变数据    sheet->writeNum(2, 3, 8888, rmbFormat);//金额    sheet->removeRow(2, 2);//删除第3行    sheet->removeCol(2, 3);//删除第3列 第4列

打开表格显示如下：

delm

5. 完整程序如下：

#include <iostream>#include <conio.h>#include <iomanip>>#include <windows.h>#include "libxl.h"#pragma comment(lib,"libxl.lib")using namespace libxl;using namespace std;bool myinit(){    Book *book = xlCreateBook();    if (!book)    {        return false;    }    Sheet *sheet = book->addSheet(L"Mysheet1");    if (!sheet)    {        return false;    }    //设置标题字体    Font *titleFont = book->addFont();    titleFont->setName(L"Arial Black");    titleFont->setColor(COLOR_RED);//设置颜色为红色    titleFont->setSize(20);//设置字体大小    Format* titleFormat = book->addFormat();    titleFormat->setAlignH(ALIGNH_CENTER);//设置中心对齐    titleFormat->setFont(titleFont);    //设置属性字体和格式    Font *attributeFont = book->addFont();    attributeFont->setName(L"宋体");    attributeFont->setBold(TRUE);    Format *attributeFormat = book->addFormat();    attributeFormat->setAlignH(ALIGNH_CENTER);    attributeFormat->setFont(attributeFont);    //设置金额格式    Format *rmbFormat = book->addFormat();     rmbFormat->setAlignH(ALIGNH_LEFT);    rmbFormat->setNumFormat(NUMFORMAT_CURRENCY_NEGBRARED);    //设置日期格式    Format *dateFormat = book->addFormat();    dateFormat->setNumFormat(NUMFORMAT_DATE);    //合并第一行的0-4列    sheet->setMerge(0, 0, 0, 4);    //设置行的宽度    sheet->setCol(1, 1, 25);    //设置列高    sheet->setRow(1, 15);    //写入字符串数据    sheet->writeStr(0, 0, L"日程安排表", titleFormat);    sheet->writeStr(1, 0, L"序号", attributeFormat);    sheet->writeStr(1, 1, L"线路", attributeFormat);    sheet->writeStr(1, 2, L"人数", attributeFormat);    sheet->writeStr(1, 3, L"金额", attributeFormat);    sheet->writeStr(1, 4, L"日期", attributeFormat);    //填写表格    for (int i = 0; i < 5; ++i)    {        sheet->writeNum(i+2, 0, i);//序号、        sheet->writeStr(i + 2, 1, L"陕西-->西安-->周至-->尚村");//线路        sheet->writeNum(i + 2, 2, rand() % 400);//人数        sheet->writeNum(i + 2, 3, rand() % 400, rmbFormat);//金额        sheet->writeNum(i + 2, 4, book->datePack(2017, 6, 12), dateFormat);    }    if (book->save(L"xcxc.xls"))    {//      ::ShellExecute(NULL, L"open", L"xcxc.xls", NULL, NULL, SW_SHOW);    }    book->release();    return true;}bool myfun(){    Book *book = xlCreateBook();    if (!book)    {        return false;    }    if (!book->load(L"xcxc.xls"))    {        return false;    }    Sheet* sheet = book->getSheet(0);    if (!sheet)    {        return false;    }    //设置金额格式    Format *rmbFormat = book->addFormat();    rmbFormat->setAlignH(ALIGNH_LEFT);    rmbFormat->setNumFormat(NUMFORMAT_CURRENCY_NEGBRARED);    //设置日期格式    Format *dateFormat = book->addFormat();    dateFormat->setNumFormat(NUMFORMAT_DATE);    //添加数据    sheet->insertRow(2, 2);    sheet->writeNum(2, 0, 99);//序号、    sheet->writeStr(2, 1, L"小妖-->-->敏敏-->尚村-->武功");//线路    sheet->writeNum(2, 2, 2);//人数    sheet->writeNum(2, 3, 99999, rmbFormat);//金额    sheet->writeNum(2, 4, book->datePack(2016, 6, 12), dateFormat);    //查询表格信息    int rowfirst = sheet->firstRow();    int rowlast = sheet->lastRow();    int colfirst = sheet->firstCol();    int collast = sheet->lastCol();    wcout.imbue(locale(locale(), "", LC_CTYPE));//unicode 宽字节中文输出    wcout << L"数据开始行 ：" << rowfirst << endl;    wcout << L"数据结束行 ：" << rowlast << endl;    wcout << L"数据开始列 ：" << colfirst << endl;    wcout << L"数据结束列 ：" << collast << endl;    for (int i = rowfirst + 2; i < rowlast; ++i)    {        int serial = sheet->readNum(i, 0);        const TCHAR *address = sheet->readStr(i, 1);        int number = sheet->readNum(i, 2);        double dollar = sheet->readNum(i, 3);        int year, month, day;        book->dateUnpack(sheet->readNum(i, 4), &year, &month, &day);        wcout << setw(4)<< L"序号：" << setw(4) << serial << setw(4) << L"  地址:"<< setw(20) << address<< setw(4) << L"  人数:"<< setw(4) << number<< setw(4) << L"  金额:"<< setw(5) << dollar<< L"  日期:"<< year << L"-" << month << L"-" << day << endl;    }    //改变数据    wcout << "change---2. 3" << endl;    sheet->writeNum(2, 3, 8888, rmbFormat);//金额    wcout<< sheet->readNum(2, 3)<<endl;   //删除数据    sheet->removeRow(2, 2);//删除第3行    sheet->removeCol(2, 3);//删除第3列 第4列    if (book->save(L"xcxc.xls"))    {        ::ShellExecute(NULL, L"open", L"xcxc.xls", NULL, NULL, SW_SHOW);        return true;    }    return false;}int main(){    myinit();    myfun();    Sleep(10000);    return 0;}

阅读全文

1 0