Qt下Excel报表制作

Excel的对象层次
这里写图片描述
1个excel就有1个Application对象，1个Application对象由多个Workbook对象组成，这些Workbook 对象由Workbooks对象统一管理，Workbook对象下可以包含若干个Worksheet，这些Worksheet对象也有一个 WorkSheets对象来统一管理，接下来是Range对象，这个对象对应Worksheet里的表格单元.

封装类实现

class ExcelEngine : public QObject
{
public:
    explicit ExcelEngine(QObject *parent = 0);//打开工作表集合
    ~ExcelEngine();

public:
    QAxObject * getWorkBooks();
    QAxObject * getWorkBook();
    QAxObject * getWorkSheets();
    QAxObject * getWorkSheet();

public:
    /**************************************************************************/
    /* 工作表                                                                 */
    /**************************************************************************/
    bool selectSheet(const QString& sheetName);
    //sheetIndex 起始于 1
    //选择工作表
    bool selectSheet(int sheetIndex);
    void deleteSheet(const QString& sheetName);
    void deleteSheet(int sheetIndex);
    void insertSheet(QString sheetName);
    int getSheetsCount();
    //在 selectSheet() 之后才可调用
    QString getSheetName();
    QString getSheetName(int sheetIndex);

    /**************************************************************************/
    /* 单元格                                                                 */
    /**************************************************************************/
    void setCellString(int row, int column, const QString& value);
    //cell 例如 "A7"
    void setCellString(const QString& cell, const QString& value);
    //range 例如 "A5:C7"
    void mergeCells(const QString& range);
    void mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn);
    QVariant getCellValue(int row, int column);
    void clearCell(int row, int column);
    void clearCell(const QString& cell);

    /**************************************************************************/
    /* 布局格式                                                               */
    /**************************************************************************/
    void getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn);
    void setColumnWidth(int column, int width);
    void setRowHeight(int row, int height);
    void setCellTextCenter(int row, int column);
    void setCellTextCenter(const QString& cell);
    void setCellTextWrap(int row, int column, bool isWrap);
    void setCellTextWrap(const QString& cell, bool isWrap);
    void setAutoFitRow(int row);
    void mergeSerialSameCellsInAColumn(int column, int topRow);
    int  getUsedRowsCount();
    void setCellFontBold(int row, int column, bool isBold);
    void setCellFontBold(const QString& cell, bool isBold);
    void setCellFontSize(int row, int column, int size);
    void setCellFontSize(const QString& cell, int size);

    /**************************************************************************/
    /* 文件                                                                   */
    /**************************************************************************/
    bool open(const QString &filename, bool bVisable);
    void save();
    void close();
    bool isOpen(){return m_bOpened;}

private:
    QAxObject * excel = nullptr;
    QAxObject * workBooks = nullptr;
    QAxObject * workBook = nullptr;
    QAxObject * sheets = nullptr;
    QAxObject * sheet = nullptr;

    bool m_bOpened = false;

excelengine.h

ExcelEngine::ExcelEngine(QObject *parent) : QObject (parent)
{
}

ExcelEngine::~ExcelEngine()
{
    close();
}

void ExcelEngine::close()
{
    excel->dynamicCall("Quit()");

    delete sheet;
    delete sheets;
    delete workBook;
    delete workBooks;
    delete excel;

    excel = nullptr;
    workBooks = nullptr;
    workBook = nullptr;
    sheets = nullptr;
    sheet = nullptr;
}

QAxObject *ExcelEngine::getWorkBooks()
{
    return workBooks;
}

QAxObject *ExcelEngine::getWorkBook()
{
    return workBook;
}

QAxObject *ExcelEngine::getWorkSheets()
{
    return sheets;
}

QAxObject *ExcelEngine::getWorkSheet()
{
    return sheet;
}

bool ExcelEngine::selectSheet(const QString& sheetName)
{
    sheet = sheets->querySubObject("Item(const QString&)", sheetName);
    if(!sheet){
        return false;
    }
    return true;
}

void ExcelEngine::deleteSheet(const QString& sheetName)
{
    QAxObject * a = sheets->querySubObject("Item(const QString&)", sheetName);
    if(!a){
        qDebug()<<QString("there is no sheetName %1").arg(sheetName);
        return;
    }
    a->dynamicCall("delete");
}

void ExcelEngine::deleteSheet(int sheetIndex)
{
    QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex);
    if(!a){
        qDebug()<<QString("there is no index %1").arg(sheetIndex);
        return;
    }
    a->dynamicCall("delete");
}

bool ExcelEngine::selectSheet(int sheetIndex)
{
    if(sheetIndex > (getSheetsCount() + 1)){
        return false;
    }
    sheet = sheets->querySubObject("Item(int)", sheetIndex);
    return true;
}

void ExcelEngine::setCellString(int row, int column, const QString& value)
{
    QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column);
    range->dynamicCall("SetValue(const QString&)", value);
}

void ExcelEngine::setCellFontBold(int row, int column, bool isBold)
{
    QString cell;
    cell.append(QChar(column - 1 + 'A'));
    cell.append(QString::number(row));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Bold", isBold);
}

void ExcelEngine::setCellFontSize(int row, int column, int size)
{
    QString cell;
    cell.append(QChar(column - 1 + 'A'));
    cell.append(QString::number(row));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Size", size);
}

void ExcelEngine::mergeCells(const QString& cell)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("VerticalAlignment", -4108);//xlCenter
    range->setProperty("WrapText", true);
    range->setProperty("MergeCells", true);
}

void ExcelEngine::mergeCells(int topLeftRow, int topLeftColumn, int bottomRightRow, int bottomRightColumn)
{
    QString cell;
    cell.append(QChar(topLeftColumn - 1 + 'A'));
    cell.append(QString::number(topLeftRow));
    cell.append(":");
    cell.append(QChar(bottomRightColumn - 1 + 'A'));
    cell.append(QString::number(bottomRightRow));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("VerticalAlignment", -4108);//xlCenter
    range->setProperty("WrapText", true);
    range->setProperty("MergeCells", true);
}

QVariant ExcelEngine::getCellValue(int row, int column)
{
    QVariant data;
    QAxObject *range = sheet->querySubObject("Cells(int,int)", row, column);
    //        return range->property("value");
    if ( range )
    {
        data = range->dynamicCall("Value2()");
    }
    return data;
}

void ExcelEngine::save()
{
    workBook->dynamicCall("Save()");
}

int ExcelEngine::getSheetsCount()
{
    return sheets->property("Count").toInt();
}

QString ExcelEngine::getSheetName()
{
    return sheet->property("Name").toString();
}

QString ExcelEngine::getSheetName(int sheetIndex)
{
    if(sheetIndex > (getSheetsCount() + 1)){
        return "";
    }
    QAxObject * a = sheets->querySubObject("Item(int)", sheetIndex);
    return a->property("Name").toString();
}

void ExcelEngine::getUsedRange(int *topLeftRow, int *topLeftColumn, int *bottomRightRow, int *bottomRightColumn)
{
    QAxObject *usedRange = sheet->querySubObject("UsedRange");
    *topLeftRow = usedRange->property("Row").toInt();
    *topLeftColumn = usedRange->property("Column").toInt();

    QAxObject *rows = usedRange->querySubObject("Rows");
    *bottomRightRow = *topLeftRow + rows->property("Count").toInt() - 1;

    QAxObject *columns = usedRange->querySubObject("Columns");
    *bottomRightColumn = *topLeftColumn + columns->property("Count").toInt() - 1;
}

void ExcelEngine::setColumnWidth(int column, int width)
{
    QString columnName;
    columnName.append(QChar(column - 1 + 'A'));
    columnName.append(":");
    columnName.append(QChar(column - 1 + 'A'));

    QAxObject * col = sheet->querySubObject("Columns(const QString&)", columnName);
    col->setProperty("ColumnWidth", width);
}

void ExcelEngine::setCellTextCenter(int row, int column)
{
    QString cell;
    cell.append(QChar(column - 1 + 'A'));
    cell.append(QString::number(row));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("HorizontalAlignment", -4108);//xlCenter
}

void ExcelEngine::setCellTextWrap(int row, int column, bool isWrap)
{
    QString cell;
    cell.append(QChar(column - 1 + 'A'));
    cell.append(QString::number(row));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("WrapText", isWrap);
}

void ExcelEngine::setAutoFitRow(int row)
{
    QString rowsName;
    rowsName.append(QString::number(row));
    rowsName.append(":");
    rowsName.append(QString::number(row));

    QAxObject * rows = sheet->querySubObject("Rows(const QString &)", rowsName);
    rows->dynamicCall("AutoFit()");
}

void ExcelEngine::insertSheet(QString sheetName)
{
    sheets->querySubObject("Add()");
    QAxObject * a = sheets->querySubObject("Item(int)", 1);

    int count = getSheetsCount();
    for(int i = 0; i < count; i++){
        if(sheetName == getSheetName(i+1)){
            return;
        }
    }
    a->setProperty("Name", sheetName);
}

void ExcelEngine::mergeSerialSameCellsInAColumn(int column, int topRow)
{
    int a,b,c,rowsCount;
    getUsedRange(&a, &b, &rowsCount, &c);

    int aMergeStart = topRow, aMergeEnd = topRow + 1;

    QString value;
    while(aMergeEnd <= rowsCount)
    {
        value = getCellValue(aMergeStart, column).toString();
        while(value == getCellValue(aMergeEnd, column).toString())
        {
            clearCell(aMergeEnd, column);
            aMergeEnd++;
        }
        aMergeEnd--;
        mergeCells(aMergeStart, column, aMergeEnd, column);

        aMergeStart = aMergeEnd + 1;
        aMergeEnd = aMergeStart + 1;
    }
}

void ExcelEngine::clearCell(int row, int column)
{
    QString cell;
    cell.append(QChar(column - 1 + 'A'));
    cell.append(QString::number(row));

    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->dynamicCall("ClearContents()");
}

void ExcelEngine::clearCell(const QString& cell)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->dynamicCall("ClearContents()");
}

int ExcelEngine::getUsedRowsCount()
{
    QAxObject *usedRange = sheet->querySubObject("UsedRange");
    int topRow = usedRange->property("Row").toInt();
    QAxObject *rows = usedRange->querySubObject("Rows");
    int bottomRow = topRow + rows->property("Count").toInt() - 1;
    return bottomRow;
}

void ExcelEngine::setCellString(const QString& cell, const QString& value)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->dynamicCall("SetValue(const QString&)", value);
}

void ExcelEngine::setCellFontSize(const QString &cell, int size)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Size", size);
}

bool ExcelEngine::open(const QString &filename, bool bVisable)
{
    QFile file(filename);
    if (!file.exists()){
        qDebug()<<"file is not exists";
        return false;
    }
    excel = new QAxObject();
    bool bFlag = excel->setControl("excel.Application");//初始化COM对象，新建一个excel应用程序
    if(!bFlag)
    {
        bFlag = excel->setControl("kwps.Application");//尝试用wps打开
        qDebug()<<"wps";
        if(!bFlag)
            return false;
    }
    //是否可视化excel
    excel->dynamicCall("SetVisible(bool Visible)", false);
    //是否弹出警告窗口
    excel->setProperty("DisplayAlerts", false);

    //获取工作簿集合
    workBooks = excel->querySubObject("WorkBooks");
    if(!workBooks){
        qDebug()<<"workBooks init failed";
        return false;
    }
    //打开一个工作簿
    workBooks->dynamicCall("Open(const QString&)", filename);
    //获取当前工作簿
    workBook = excel->querySubObject("ActiveWorkBook");
    if(!workBook){
        qDebug()<<"workBook init failed";
        return false;
    }
    //获取工作表格集合
    sheets = workBook->querySubObject("Sheets");
    if(!sheets){
        qDebug()<<"sheets init failed";
        return false;
    }
    m_bOpened = true;
    return true;
}

void ExcelEngine::setCellTextCenter(const QString &cell)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("HorizontalAlignment", -4108);//xlCenter
}

void ExcelEngine::setCellFontBold(const QString &cell, bool isBold)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range = range->querySubObject("Font");
    range->setProperty("Bold", isBold);
}

void ExcelEngine::setCellTextWrap(const QString &cell, bool isWrap)
{
    QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
    range->setProperty("WrapText", isWrap);
}

void ExcelEngine::setRowHeight(int row, int height)
{
    QString rowsName;
    rowsName.append(QString::number(row));
    rowsName.append(":");
    rowsName.append(QString::number(row));

    QAxObject * r = sheet->querySubObject("Rows(const QString &)", rowsName);
    r->setProperty("RowHeight", height);
}

excelengine.cpp

测试：

    //打开文件，取得工作簿
    ExcelEngine excel;
    if(!excel.open(QStringLiteral("C:/Users/DELL/Desktop/test/xl.xls"), false)){
        qDebug()<<"xls open failed";
    }
    //=======工作表操作=======
    //+-工作表
    excel.insertSheet("abc");
    excel.deleteSheet(1);
    //    取得工作表名称
    qDebug()<<"SheetName 1"<<excel.getSheetName(1);
    //取得工作表数量
    qDebug()<<excel.getSheetsCount();
    //=========数据操作=============
    //+-数据
    if(excel.selectSheet(1))
        excel.setCellString(1, 1, "addString");
//    excel.clearCell(1,1);
    //读值
    if(excel.selectSheet(1))
        qDebug()<<excel.getCellValue(1,1).toString();
    //取得工作表已使用范围
    if(excel.selectSheet(1)){
        int topLeftRow, topLeftColumn, bottomRightRow, bottomRightColumn;
        excel.getUsedRange(&topLeftRow, &topLeftColumn, &bottomRightRow, &bottomRightColumn);
        qDebug()<<topLeftRow<<","<<topLeftColumn<<","<<bottomRightRow<<","<<bottomRightColumn;
    }
    //=======布局样式============
    //合并单元格
    if(excel.selectSheet(1)){
        excel.mergeCells("G1:H2");
        excel.mergeCells(4, 7, 5 ,8);
        excel.save();
    }
    //设置列宽
    if(excel.selectSheet(1)){
        excel.setColumnWidth(1, 20);
        excel.save();
    }
    //设置粗体
    excel.selectSheet(1);
    excel.setCellFontBold(2, 2, true);
    excel.setCellFontBold("A2", true);
    excel.save();
    //设置文字大小
    //excel.selectSheet(1);
    //excel.setCellFontSize("B3", 20);
    //excel.setCellFontSize(1, 2, 20);
    //excel.save();
    //设置单元格文字居中
    //excel.selectSheet(2);
    //excel.setCellTextCenter(1, 2);
    //excel.setCellTextCenter("A2");
    //excel.save();
    //设置单元格文字自动折行
    //excel.selectSheet(1);
    //excel.setCellTextWrap(2,2,true);
    //excel.setCellTextWrap("A2", true);
    //excel.save();
    //设置一行自适应行高
    //excel.selectSheet(1);
    //excel.setAutoFitRow(2);
    //excel.save();
    //合并一列中相同连续的单元格
    //excel.selectSheet(1);
    //excel.mergeSerialSameCellsInColumn(1, 2);
    //excel.save();
    //获取一张工作表已用行数
    //excel.selectSheet(1);
    //qDebug()<<excel.getUsedRowsCount();
    //设置行高
    //excel.selectSheet(1);
    // excel.setRowHeight(2, 30);
    excel.save();
    excel.close();

test

posted @ 2020-09-18 16:41 Kylin_170 阅读(644) 评论(0) 收藏举报

刷新页面返回顶部

Kylin_170

Qt下Excel报表制作

公告