008-libxlsxwriter-C++导出excel文件

1.excel基本概念

2.示例代码

#include <xlsxwriter.h>

int main() {
    // 创建工作薄，参数为文件名
    lxw_workbook *workbook = workbook_new("hello.xlsx");
    // 创建工作页，参数为工作薄对象、工作页名称
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, nullptr);
    // 写入数据，参数为工作页对象、行、列、数据、格式
    worksheet_write_string(worksheet,0,0,"Hello libxlsx.",nullptr);
    worksheet_write_string(worksheet,0,1,"中文测试.",nullptr);


    workbook_close(workbook);
}

运行结果：

更多功能：

• 列宽指定
• 格式设置：字体加粗、字体大小、字体颜色、背景颜色、数据对齐、数值格式等
• 字符串数据
• 数值数据
• 日期数据
• 公式数据（计算数据）
• 条件格式应用：斑马线效果（相邻行或列，颜色间隔）
• 表头锁定

3.详细示例程序

下面为xlsxwriter库中日期时间的结构体定义：

typedef struct lxw_datetime {

    /** Year     : 1900 - 9999 */
    int year;
    /** Month    : 1 - 12 */
    int month;
    /** Day      : 1 - 31 */
    int day;
    /** Hour     : 0 - 23 */
    int hour;
    /** Minute   : 0 - 59 */
    int min;
    /** Seconds  : 0 - 59.999 */
    double sec;

} lxw_datetime;

main.cpp:

//
// Created by WDJ on 2025/10/2.
//

#include <xlsxwriter.h>
#include <string>
#include<memory>
#include<sstream>

struct YMD {
    int y; //年
    int m; //月
    int d; //日
};

//将自定义的日期格式转化为库的时间格式
lxw_datetime to_datetime(YMD const &date) {
    lxw_datetime datetime{};
    datetime.year = date.y;
    datetime.month = date.m;
    datetime.day = date.d;
    return datetime;
}

struct Fruit {
    std::string name; //名称
    double price; //单价

    int totalSales; //今日销量
    YMD offline; //下架日期
};

void hello() {
    lxw_workbook *workbook = workbook_new("hello.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, nullptr);

    worksheet_write_string(worksheet, 0, 0, "Hello libxlsx.", nullptr);
    worksheet_write_string(worksheet, 0, 1, "中文测试.", nullptr);

    workbook_close(workbook);
}

void prices() {
    Fruit const products[] = {
        {"苹果", 25.55, 5600, {2025, 11, 30}},
        {"香蕉", 36.80, 280, {2026, 1, 13}},
        {"橙子", 67.11, 1205, {2026, 2, 14}},
        {"葡萄", 120.03, 673, {2025, 6, 15}},
        {"桃子", 18.50, 193, {2026, 4, 16}}
    };

    lxw_workbook *workbook = workbook_new("prices.xlsx");
    //哨兵
    std::unique_ptr<lxw_workbook, lxw_error(*)(lxw_workbook *)> guard_workbook(workbook, workbook_close);
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, "报价清单");

    //创建“价格”格式
    lxw_format *fmt_money = workbook_add_format(workbook);
    format_set_num_format(fmt_money, "￥#,##0.00");
    //创建“日期”格式
    lxw_format *fmt_date = workbook_add_format(workbook);
    format_set_num_format(fmt_date, "yyyy-mm-dd");

    //显式设置第1到5列宽度
    worksheet_set_column(worksheet, 0, 0, 12.5, nullptr); //名字
    worksheet_set_column(worksheet, 1, 1, 16, fmt_money); //单价
    worksheet_set_column(worksheet, 2, 2, 14.5, nullptr); //销量
    worksheet_set_column(worksheet, 3, 3, 14, fmt_money); //收入
    worksheet_set_column(worksheet, 4, 4, 16, fmt_date); //下架日期

    //准备表头格式
    lxw_format *fmt_header = workbook_add_format(workbook);
    format_set_bold(fmt_header); //粗体
    format_set_font_size(fmt_header, 12); //字号
    format_set_font_color(fmt_header, LXW_COLOR_WHITE); //字体颜色：白色
    format_set_bg_color(fmt_header, 0x4f81bd); //背景颜色：灰蓝色
    format_set_align(fmt_header, LXW_ALIGN_CENTER); //居中对齐

    lxw_format *fmt_name = workbook_add_format(workbook);
    format_set_bold(fmt_name); //粗体
    format_set_align(fmt_name, LXW_ALIGN_CENTER); //居中对齐

    int row = 0;
    //写入列标题
    worksheet_write_string(worksheet, row, 0, "水果", fmt_header);
    worksheet_write_string(worksheet, row, 1, "单价（元/斤）", fmt_header);
    worksheet_write_string(worksheet, row, 2, "销量（斤）", fmt_header);
    worksheet_write_string(worksheet, row, 3, "收入（元）", fmt_header);
    worksheet_write_string(worksheet, row, 4, "下架日期", fmt_header);

    /*填充数据*/
    for (auto const &product: products) {
        row++;
        worksheet_write_string(worksheet, row, 0, product.name.c_str(), fmt_name);
        worksheet_write_number(worksheet, row, 1, product.price, nullptr);
        worksheet_write_number(worksheet, row, 2, product.totalSales, nullptr);\
        //填充公式
        std::stringstream ss;
        ss << "=B" << row + 1 << "*C" << row + 1;
        worksheet_write_formula(worksheet, row, 3, ss.str().c_str(), nullptr);
        auto date = to_datetime(product.offline);
        worksheet_write_datetime(worksheet, row, 4, &date, nullptr);
    }

    /* 加上斑马线效果（隔行变色）*/
    //1.创建浅色背景
    lxw_format *bkgnd_format = workbook_add_format(workbook);
    format_set_bg_color(bkgnd_format, 0xDAEEF3); //浅蓝

    //2.设置条件格式（使用EXCEL公式）
    lxw_conditional_format cf{}; //条件格式结构
    cf.type = LXW_CONDITIONAL_TYPE_FORMULA;
    cf.value_string = "MOD(ROW(),2)<>0"; //奇数行公式
    cf.format = bkgnd_format; //条件满足时，应用该格式

    //3.应用条件格式到指定区域
    worksheet_conditional_format_range(
        worksheet,
        1, 1, //起始行列
        std::size(products) - 1, 4, //结束行列
        &cf
    );

    /*锁定表头和首列*/
    //表示：顶部锁一行，左侧锁一行
    worksheet_freeze_panes(worksheet, 1, 1);
}

int main() {
    prices();
}