Qt实现对Excel文件的操作-CSDN博客

本文链接：https://blog.csdn.net/qq_62301456/article/details/147624999

1、打开Excel应用

//pro文件添加 QT += axcontainer
QAxObject  *excel = new QAxObject("Excel.Application", this);
excel->setProperty("Visible", false);//false:不显示excel true：显示excel界面
QAxObject *workbooks = excel->querySubObject("Workbooks");

2、打开excel文件

QString path = "C:/Users/新建 Microsoft Excel 工作表.xlsx";
QAxObject *workbook  = workbooks->querySubObject("Open(const QString&)", path);

3，读取指定sheet页数据

QAxObject *worksheets = workbook->querySubObject("Sheets");
int sheet_count = worksheets->property("Count").toInt();//获取sheet页的数目
int sheet_index = 1;//sheet索引，从1开始
QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", sheet_index);//打开指定sheet页
QAxObject *usedRange = worksheet->querySubObject("UsedRange");//读取整张表数据
QVariant var = usedRange->dynamicCall("Value");
QVariantList varRows = var.toList();
const int row_count = varRows.size();//sheet页行数
const int col_count = varRows[0].toList().size();//sheet页列数，需注意空表的特殊情况，防止程序异常关闭
QString info = varRows[0].toList()[0].toString();//读取excel表格中A1单元格信息

4、在excel最后插入一个sheet页

//获取最后一个sheet页索引
int last_sheet_index = sheet_count;

//获取最后一个工作表lastSheet
QAxObject *lastSheet = worksheets->querySubObject("Item(int)", last_sheet_index);

//在lastSheet之前插入一个新工作表
worksheets->dynamicCall("Add(QVariant)", lastSheet->asVariant());

//获取新创建的sheet页索引
int newSheet_index = last_sheet_index;

//新sheet页名称
QString newSheet_name = "newSheet";

// 获取新增的工作表newSheet
QAxObject *newSheet = worksheets->querySubObject("Item(int)", newSheet_index);

// 设置新Sheet页名称
newSheet->setProperty("Name", newSheet_name);

// 将lastSheet移动到newSheet之前
lastSheet->dynamicCall("Move(QVariant)", newSheet->asVariant());

5、设置单元格内容

void SetCellProperties(QAxObject *worksheet, int row, int col, QString value)
{
    //worksheet当前sheet页,row，col为单元格行列，皆从1开始，value为单元格值
    QAxObject *pCell = worksheet->querySubObject("Cells(int, int)", row, col);
    pCell->setProperty("Value", value);
    pCell->setProperty("HorizontalAlignment", -4108);//水平居中
}

6、数值转换成excel列号

//1-->A
QString NumberToColName(int col)
{
    QString col_name;
    while (col > 0)
    {
        int remainder = (col - 1) % 26;//计算余数
        col_name.prepend(QChar('A' + remainder));
        col = (col - 1) / 26;
    }
    return col_name;
}

7、合并单元格

void MergeCells(QAxObject *worksheet, int start_row, int start_col, int end_row, int end_col)
{
    QString cell_range = NumberToColName(start_col) + QString::number(start_row) + ":" + NumberToColName(end_col) + QString::number(end_row);
    QAxObject *range = worksheet->querySubObject("Range(const Qvariant&)", QVariant(cell_range));
    range->setProperty("MergeCells", true); // 合并单元格
    range->setProperty("HorizontalAlignment", -4108);//水平居中
}

8、设置边框

// 获取目标单元格范围（例如A1:D5）
QString str = "A1:B1";
QAxObject *BKrange = worksheet->querySubObject("Range(const Qvariant&)", QVariant(str));

// 定义边框枚举值（参考VBA XlBordersIndex）
const int xlEdgeTop = 8;
const int xlEdgeBottom = 9;
const int xlEdgeLeft = 7;
const int xlEdgeRight = 10;
const int xlInsideVertical = 11;
const int xlInsideHorizontal = 12;

// 定义线条样式（参考VBA XlLineStyle）
const int xlContinuous = 1; // 实线

// 获取边框集合
QAxObject *borders = BKrange->querySubObject("Borders");

// 设置外边框
setBorderStyle(borders, xlEdgeTop, xlContinuous, 2, 0x000000); // 上边框
setBorderStyle(borders, xlEdgeBottom, xlContinuous, 2, 0x000000); // 下边框
setBorderStyle(borders, xlEdgeLeft, xlContinuous, 2, 0x000000); // 左边框
setBorderStyle(borders, xlEdgeRight, xlContinuous, 2, 0x000000); // 右边框

// 设置内部边框
setBorderStyle(borders, xlInsideVertical, xlContinuous, 2, 0x808080); // 垂直内线
setBorderStyle(borders, xlInsideHorizontal, xlContinuous, 2, 0x808080); // 水平内线


void setBorderStyle(QAxObject *borders, int borderIndex, int lineStyle, int weight, int color)
{
    QAxObject *border = borders->querySubObject("Item(int)", borderIndex);
    border->setProperty("LineStyle", lineStyle);  // 线条样式
    border->setProperty("Weight", weight);       // 线宽（1-细, 2-中等, 3-粗）
    border->setProperty("Color", color);         // RGB颜色（0x00BBGGRR）
    delete border;
}