xlslib and libxls

一个非常完整的实例

void cratexlsfile(std::string filename,ustring name,ustring gender,ustring id,ustring Class,ustring PhoneNum)
{
    XLS obj;
    auto sheet = obj.createSheet("sheet1");

    obj.MergingUnit(0, 1, 5, 1);
    auto cell_0 = sheet->label(1, 0, L"合江县学生健康卡");
    cell_0->halign(HALIGN_CENTER);
    cell_0->valign(VALIGN_CENTER);
    cell_0->fontheight(400);
    cell_0->fontbold(boldness_option_t::BOLDNESS_HALF);
    obj.setWidth(0, 20);
    obj.setWidth(3, 20);
    obj.setWidth(4, 25);
    obj.setWidth(5, 20);
    obj.setHeight(1, 80);

    obj.MergingUnit(0, 2, 5, 2);
    cell_0 = sheet->label(2, 0, L"学生基本情况");
    cell_0->halign(HALIGN_CENTER);
    cell_0->valign(VALIGN_CENTER);
    cell_0->fontheight(200);
    //cell_0->fontbold(boldness_option_t::BOLDNESS_NORMAL);
    obj.setHeight(2, 50);


    cell_0 = obj.createLable(0, 3, L"姓名", 200, obj.createBorder());
    obj.setSize(0, 3, 10, 50);
    cell_0 = obj.createLable(1, 3, L"性别", 200, obj.createBorder());
    obj.MergingUnit(2, 3, 3, 3);
    cell_0 = obj.createLable(2, 3, L"身份证号码", 200, obj.createBorder());
    cell_0 = obj.createLable(3, 3, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(4, 3, L"就读学校/年纪/班级", 200, obj.createBorder());
    cell_0 = obj.createLable(5, 3, L"联系电话", 200, obj.createBorder());

    cell_0 = obj.createLable(0, 4, name, 200, obj.createBorder());
    obj.setSize(0, 4, 10, 50);
    cell_0 = obj.createLable(1, 4, gender, 200, obj.createBorder());
    obj.MergingUnit(2, 4, 3, 4);
    cell_0 = obj.createLable(2, 4, id, 200, obj.createBorder());
    cell_0 = obj.createLable(3, 4, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(4, 4, Class, 200, obj.createBorder());
    cell_0 = obj.createLable(5, 4, PhoneNum, 200, obj.createBorder());

    obj.setSize(0, 5, 10, 50);
    cell_0 = obj.createLable(0, 5, L"户籍所在地", 200, obj.createBorder());
    obj.MergingUnit(1, 5, 5, 5);
    cell_0 = obj.createLable(1, 5, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(2, 5, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(3, 5, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(4, 5, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(5, 5, L"", 200, obj.createBorder());

    obj.setSize(0, 6, 10, 50);
    cell_0 = obj.createLable(0, 6, L"假期居住地点", 200, obj.createBorder());
    obj.MergingUnit(1, 6, 5, 6);
    cell_0 = obj.createLable(1, 6, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(2, 6, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(3, 6, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(4, 6, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(5, 6, L"", 200, obj.createBorder());


    obj.setSize(0, 7, 10, 50);
    obj.setSize(0, 8, 10, 50);
    obj.MergingUnit(0, 7, 0, 8);
    cell_0 = obj.createLable(0, 7, L"是否曾前往疫区", 200, obj.createBorder());
    cell_0->halign(halign_option_t::HALIGN_JUSTIFY);
    //cell_0->fontstrikeout(true);
    cell_0 = obj.createLable(0, 8, L"", 200, obj.createBorder());
    obj.MergingUnit(1, 7, 2, 7);
    cell_0 = obj.createLable(1, 7, L"是/否（若是，填后栏）", 200, obj.createBorder());
    cell_0 = obj.createLable(2, 7, L"", 200, obj.createBorder());
    obj.MergingUnit(3, 7, 5, 7);
    cell_0 = obj.createLable(3, 7, L"具体地址", 200, obj.createBorder());
    cell_0 = obj.createLable(4, 7, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(5, 7, L"", 200, obj.createBorder());
    obj.MergingUnit(1, 8, 2, 8);
    cell_0 = obj.createLable(1, 8, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(2, 8, L"", 200, obj.createBorder());
    obj.MergingUnit(3, 8, 5, 8);
    cell_0 = obj.createLable(3, 8, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(4, 8, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(5, 8, L"", 200, obj.createBorder());

    obj.setSize(0, 9, 10, 50);
    obj.setSize(0, 10, 10, 50);
    obj.MergingUnit(0, 9, 0, 10);
    cell_0 = obj.createLable(0, 9, L"是否接触过疫区高危人员", 200, obj.createBorder());
    cell_0->halign(halign_option_t::HALIGN_JUSTIFY);
    //cell_0->fontstrikeout(true);
    cell_0 = obj.createLable(0, 10, L"", 200, obj.createBorder());
    obj.MergingUnit(1, 9, 2, 9);
    cell_0 = obj.createLable(1, 9, L"是/否（若是，填后栏）", 200, obj.createBorder());
    cell_0 = obj.createLable(2, 9, L"", 200, obj.createBorder());
    obj.MergingUnit(3, 9, 5, 9);
    cell_0 = obj.createLable(3, 9, L"高危人员状况（被隔离、医学观察、已解除医学观察、在家隔离）", 200, obj.createBorder());
    cell_0 = obj.createLable(4, 9, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(5, 9, L"", 200, obj.createBorder());
    obj.MergingUnit(1, 10, 2, 10);
    cell_0 = obj.createLable(1, 10, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(2, 10, L"", 200, obj.createBorder());
    obj.MergingUnit(3, 10, 5, 10);
    cell_0 = obj.createLable(3, 10, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(4, 10, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(5, 10, L"", 200, obj.createBorder());


    obj.MergingUnit(0, 11, 5, 11);
    cell_0 = sheet->label(11, 0, L"健康状况");
    cell_0->halign(HALIGN_CENTER);
    cell_0->valign(VALIGN_CENTER);
    cell_0->fontheight(200);
    //cell_0->fontbold(boldness_option_t::BOLDNESS_NORMAL);
    obj.setHeight(11, 50);

    obj.getWorksheet("sheet1")->defaultRowHeight(30);
    obj.setHeight(12, 50);
    obj.setHeight(13, 80);
    obj.MergingUnit(0, 12, 0, 13);
    obj.MergingUnit(1, 12, 3, 12);
    obj.MergingUnit(4, 12, 5, 12);
    obj.MergingUnit(1, 13, 2, 13);
    cell_0 = obj.createLable(0, 12, L"时间", 200, obj.createBorder());
    cell_0 = obj.createLable(0, 13, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(1, 12, L"学生身体健康状况", 200, obj.createBorder());
    cell_0 = obj.createLable(2, 12, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(3, 12, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(4, 12, L"家庭成员健康状况", 200, obj.createBorder());
    cell_0 = obj.createLable(5, 12, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(1, 13, L"是否正常(若否,填后栏)", 200, obj.createBorder());
    cell_0 = obj.createLable(2, 13, L"", 200, obj.createBorder());
    cell_0 = obj.createLable(3, 13, L"主要症状", 200, obj.createBorder());
    cell_0 = obj.createLable(4, 13, L"是否正常(若否,填后栏)", 200, obj.createBorder());
    cell_0 = obj.createLable(5, 13, L"主要症状", 200, obj.createBorder());




    obj.getWorksheet("sheet1")->defaultRowHeight(30);
    int time = 14;
    int index = 14;
    int day = 4;
    wchar_t buf[256] = { 0 };
    for (int i = 0; i < 14; i++)
    {
        wsprintfW(buf, L"2月%d日", day);
        obj.setHeight(index, 50);
        obj.MergingUnit(1, index, 2, index);
        cell_0 = obj.createLable(0, index, buf, 200, obj.createBorder());


        day++;
        index++;
    }

    obj.getWorksheet("sheet1")->defaultRowHeight(30);
    obj.setHeight(index, 80);
    obj.MergingUnit(0, index, 5, index);
    auto xf = obj.createBorder();
    cell_0 = obj.createLable(0, index, L"注：1.“疫情重点防控地区”指湖北、浙江、广州、上海和重庆等 
        2.学生健康状况是否正常主要指咳嗽、发热等疑似新冠肺炎主要症状，其他状况不列入统计",
        200,
        xf);
    cell_0->fontcolor(color_name_t::CLR_DARK_RED);
    cell_0->halign(halign_option_t::HALIGN_JUSTIFY);
    cell_0->valign(valign_option_t::VALIGN_TOP);
    for (int i = 1; i < 6; i++)
    {
        cell_0 = obj.createLable(i, index, L"", 200, obj.createBorder());
    }
    obj.save(filename);
}


int main()
{

    std::string filename;
    for (int i = 0; i < sizeof(StudentName) / W; i++)
    //for (int i = 0; i < 2; i++)
    {
        filename = std::string("StudentData/") + StudentName[i] + std::string(".xls");
        cratexlsfile(filename,
            StudentNameW[i],
            StudentGenderW[i],
            StudenIDW[i],
            StudentClass,
            StudentPhoneNumberW[i]);
        printf("create %s ok........
",filename.c_str());
    }


    printf("ok
");
    _sleep(3000);
    return 0;
}

{

　　由于涉及到大量个人数据,StudentNameW 你可能需要更改它

}

//头文件

#include <iostream>
#include <string.h>
#include <map>

#define CPP_BRIDGE_XLS 1
//支持C方式
//write xls
#include "xlslib.h"
//read xls
#include "libxls/xls.h"

using namespace xlslib_core;
using namespace std;
using namespace xls;

/*
row ======行======= x
col  ======列======= y
cell->halign(HALIGN_CENTER);   //单元格水平方向居中
    cell->valign(VALIGN_CENTER);  //单元格垂直方向居中

        cell->halign(HALIGN_LEFT);  //单元格水平方向靠左
    cell->valign(VALIGN_CENTER);



    xlslib导出excel，不能直接使用中文的字体例如宋体，楷体，给成这样的字符（非asii），程序会直接报错崩溃，正确的做法是将中文名称的字体转换为英文，具体转换的对应关系如下所示：

Mac OS的一些：

华文细黑：STHeiti Light [STXihei]
华文黑体：STHeiti
华文楷体：STKaiti
华文宋体：STSong
华文仿宋：STFangsong
俪黑 Pro：LiHei Pro Medium
俪宋 Pro：LiSong Pro Light
标楷体：BiauKai
苹果俪中黑：Apple LiGothic Medium
苹果俪细宋：Apple LiSung Light

Windows的一些：

    推荐：输入文本中使用中文字体

    [ 关于TextField无法显示中文字体效果的问题,困惑了很长时间 今天在看一个朋友开发的卡片制作系统时发现可以使用中文字体,带着满腔的疑问向其请教. ...]

新细明体：PMingLiU
细明体：MingLiU
标楷体：DFKai-SB
黑体：SimHei
宋体：SimSun
新宋体：NSimSun
仿宋：FangSong
楷体：KaiTi
仿宋_GB2312：FangSong_GB2312
楷体_GB2312：KaiTi_GB2312
微软正黑体：Microsoft JhengHei
微软雅黑体：Microsoft YaHei

装Office会生出来的一些：

隶书：LiSu
幼圆：YouYuan
华文细黑：STXihei
华文楷体：STKaiti
华文宋体：STSong
华文中宋：STZhongsong
华文仿宋：STFangsong
方正舒体：FZShuTi
方正姚体：FZYaoti
华文彩云：STCaiyun
华文琥珀：STHupo
华文隶书：STLiti
华文行楷：STXingkai
华文新魏：STXinwei

*/

/*
    cell->halign(HALIGN_CENTER);   //单元格水平方向居中
    cell->valign(VALIGN_CENTER);  //单元格垂直方向居中

        cell->halign(HALIGN_LEFT);  //单元格水平方向靠左
    cell->valign(VALIGN_CENTER);


    格式化规定符：
    %d 格式化为十进制有符号整数输出到缓冲区
    %u 格式化为十进制无符号整数输出到缓冲区
    %f 格式化为浮点数输出到缓冲区
    %s 格式化为字符串输出到缓冲区
    %c 格式化为单个字符输出到缓冲区
    %e 格式化为指数形式的浮点数输出到缓冲区
    %x 格式化为无符号以十六进制表示的整数(a-f小写输出)输出到缓冲区
    %X 格式化为无符号以十六进制表示的整数(a-f大写输出)输出到缓冲区
    %0 格式化为无符号以八进制表示的整数输出到缓冲区
    %g 格式化为自动选择合适的表示法输出到缓冲区

*/

class XLS
{
    ///==========================write
    //工作簿
    workbook *m_workbook = nullptr;
    worksheet *m_worksheet = nullptr;
    map<string, worksheet *> *m_mWS = nullptr;
public:
    XLS();
    ~XLS();
    //创建一个sheet
    worksheet *createSheet(string name);


    //保存
    void save(string name);
    void save();


    //xf
    xf_t *getXF();
    xf_t *xformat();

    //合并单元
    void MergingUnit(int x, int y, int xx, int yy);


    //获取sheet
    worksheet *getWorksheet(string name);
    worksheet *findWorksheet(string name);

    //选择sheet
    void selectSheet(string name);


    //设置单元宽
    void setWidth(int x, int w);
    //设置单元高
    void setHeight(int y, int h);
    void setSize(int x,int y,int w,int h);


    //创建lable 默认剧中垂直
    cell_t* createLable(int x, int y, ustring str, int fontSize = 0, xf_t* pxformat = nullptr);

    //创建一个四个边的粗边框
    xf_t *createBorder();

    ///===========================read
private:
public:

};

//源文件

#include "XLS.h"

XLS::XLS()
{
    m_workbook = new workbook();
    m_mWS = new map<string, worksheet *>();
}

XLS::~XLS()
{
    if (m_workbook)
    {
        delete m_workbook;
        m_workbook = nullptr;
    }
    if (m_mWS)
    {
        delete m_mWS;
        m_mWS = nullptr;
    }
}

worksheet *XLS::createSheet(string name)
{
    worksheet *ws = this->m_workbook->sheet(name);
    m_worksheet = ws;
    this->m_workbook->font("SimSun");
    ws->defaultColwidth(25);
    ws->defaultRowHeight(15);
    this->m_mWS->insert(std::pair<string, worksheet*>(name, ws));
    return ws;
}

void XLS::save(string name)
{
    this->m_workbook->Dump(name);
}

void XLS::save()
{
    this->save("workbook.xls");
}

xf_t *XLS::getXF()
{
    xf_t *ret = this->m_workbook->xformat();
    return ret;
}

xf_t *XLS::xformat()
{
    xf_t *ret = this->m_workbook->xformat();
    return ret;
}

void XLS::MergingUnit(int x, int y, int xx, int yy)
{
    this->m_worksheet->merge(y ,x,yy, xx);
}

worksheet *XLS::getWorksheet(string name)
{
    auto it = this->m_mWS->find(name);
    if (it != this->m_mWS->end())
    {
        return it->second;
    }
    printf("not find sheet
");
    return nullptr;
}

worksheet *XLS::findWorksheet(string name)
{
    auto ret = this->getWorksheet(name);
    return ret;
}

void XLS::selectSheet(string name)
{
    auto ret = this->findWorksheet(name);
    if (ret)
    {
        this->m_worksheet = ret;
    }
    printf("not find sheet
");
}

void XLS::setWidth(int x, int w)
{    
    this->m_worksheet->defaultColwidth(8);
    this->m_worksheet->colwidth(x, 256 * w);
}

void XLS::setHeight(int y, int h)
{
    this->m_worksheet->rowheight(y, h * 10);
}

void XLS::setSize(int x, int y, int w, int h)
{
    this->setWidth(x, w);
    this->setHeight(y, h);
}

cell_t* XLS::createLable(int x, int y, ustring str, int fontSize, xf_t* pxformat)
{
    cell_t *cell = nullptr;
    if (pxformat)
    {
        cell = this->m_worksheet->label(y, x, str,pxformat);
    }
    else
    {
        cell = this->m_worksheet->label(y, x, str);
    }
    cell->halign(HALIGN_CENTER);
    cell->valign(VALIGN_CENTER);
    if (fontSize > 0)
    {
        cell->fontheight(fontSize);
    }
    return cell;
}

xf_t *XLS::createBorder()
{
    int line = BORDER_THIN;
    auto xf = this->m_workbook->xformat();
    xf->SetBorderStyle(border_side_t::BORDER_TOP,
        (border_style_t)line);
    xf->SetBorderStyle(border_side_t::BORDER_BOTTOM,
        (border_style_t)line);
    xf->SetBorderStyle(border_side_t::BORDER_LEFT,
        (border_style_t)line);
    xf->SetBorderStyle(border_side_t::BORDER_RIGHT,
        (border_style_t)line);
    return xf;
}