QT实现EXCEL文件的展示及编辑功能

概要

使用QT实现EXCEL文件的展示及编辑功能。

整体架构流程

`第一，使用成熟的qtxlsx实现EXCEL文件的读写，源代码网址：https://github.com/dbzhang800/QtXlsxWriter；
第二，使用QTableWidget作为EXCEL的展示及编辑接口；

技术细节

第一，在项目的Pro文件里面加入qtxlsx模块，具体如下：

include ($$PWD/../Public/xlsx/qtxlsx.pri)
INCLUDEPATH += $$PWD/../Public/xlsx

根据实际qtxlsx的路径设置。

第二，QTableWidget的实现，部分代码如下：
.h文件：

#ifndef SPREADSHEET_H
#define SPREADSHEET_H

#include <QTableWidget>
#include <QUndoCommand>
#include "qtobjectcontroller.h"
#include "head.h"
#include "frmdevicereplace.h"

class CSpreadSheetItem;

typedef struct TSpan
{
    quint16 row;
    quint16 column;
    quint16 rowSpanCount;
    quint16 columnSpanCount;
}TSpan;

class CSpreadsheet : public QTableWidget
{
    Q_OBJECT
    Q_ENUMS(ReportType)

    Q_PROPERTY(int m_rows READ getRows WRITE setRows)
    Q_PROPERTY(int m_columns READ getColumns WRITE setColumns)

protected:
    bool eventFilter(QObject *watched, QEvent *event) override;

public:
    CSpreadsheet(QtObjectController *Object, QWidget *parent = 0);

    int getRows()                 const;
    int getColumns()              const;

    void setRows(int rows);
    void setColumns(int columns);

    QString currentLocation() const;
    QTableWidgetSelectionRange selectedRange() const;
    void clear();
    bool openFile(const QString &fileName);
    bool saveFile(void);
    void setFileName(const QString &fileName);
    QString getFileName(void);

    QColor getCurrentItemColor(void);
    QFont getCurrentItemFont(void);
    int getCurrentItemCount(void);
    bool isAlignLeft(void);
    bool isAlignCenter(void);
    bool isAlignRight(void);
    bool isMerge(void);
    void setText(int row, int column, const QString &text);
    CSpreadSheetItem *itemCell(int row, int column) const;

    QUndoStack *undoStack() const;

    void removeOneColumn(int column);
    void removeOneRow(int row);
    void insertOneColumn(int column);
    void insertOneRow(int row);
    void refreshListSpan(void);
    void refreshPropertyView(void);

public slots:
    void cut(void);
    void copy(void);
    void paste(void);
    void del(void);
    void merge(void);
    void setFontBold(bool bold);
    void setFontItalic(bool italic);
    void setFontUnderline(bool underline);
    void setAlignLeft(bool align);
    void setAlignCenter(bool align);
    void setAlignRight(bool align);

    void setTextFamily(const QString &f);
    void setTextSize(const QString &p);
    void setTextColor(const QColor &c);
    void setTextFillColor(const QColor &c);

    void onRemoveColumn(void);
    void onRemoveRow(void);
    void onInsertColumn(void);
    void onInsertRow(void);

    void selectCurrentRow(void);
    void selectCurrentColumn(void);

    void onSetColumnWidth(void);
    void onSetRowHeight(void);

signals:
    void modified();

private slots:
    void onCurrentSelectChanged();
    void onItemDoubleClicked(QTableWidgetItem *item);
    void onItemChanged(QTableWidgetItem *item);
    void onColumnWidthChanged(int logicalIndex, int oldSize, int newSize);
    void onRowHeightChanged(int logicalIndex, int oldSize, int newSize);

private:
    enum
    {
        MagicNumber = 0x88888800,
    };

    void removeSpan(quint16 row, quint16 column);
    bool hasSpan(quint16 row, quint16 column);
    QString text(int row, int column) const;

    int m_rows;
    int m_columns;
    QList<TSpan> m_listSpan;   //存储合并单元格
    QString m_fileName;
    QUndoStack *m_undoStack;

    QTableWidgetItem *m_lastDoubleClickedItem;
    QString m_lasetDoubleClickedItemText;
    QtObjectController *m_propertyView;
    bool m_columnWidthOrRowHeightChangedStart;
    bool m_headerMouseEnter;
};

#endif // SPREADSHEET_H

.cpp文件

#include <QtWidgets>
#include <QtCore>
#include "quihelper.h"
#include "spreadsheetitem.h"
#include "spreadsheet.h"
#include "xlsxdocument.h"
#include "xlsxcellrange.h"
#include "undocommand.h"
#include "getsizedialog.h"
#include "databaseapp.h"

using namespace QXlsx;

//列标题ABCD
static QString col_to_name(int col_num)
{
    QString col_str;

    int remainder;
    while (col_num) {
        remainder = col_num % 26;
        if (remainder == 0)
            remainder = 26;
        col_str.prepend(QChar('A' + remainder - 1));
        col_num = (col_num - 1) / 26;
    }

    return col_str;
}

CSpreadsheet::CSpreadsheet(QtObjectController *Object, QWidget *parent)
    : QTableWidget(parent)
{
    m_propertyView = Object;
    m_rows = 0;
    m_columns = 0;
    m_columnWidthOrRowHeightChangedStart = false;
    m_headerMouseEnter = false;
    m_undoStack = new QUndoStack(this);
    m_undoStack->setUndoLimit(100);
    setItemPrototype(new CSpreadSheetItem);
    setSelectionMode(ContiguousSelection);
    connect(this, &CSpreadsheet::itemSelectionChanged, this, &CSpreadsheet::onCurrentSelectChanged);
    clear();

    this->horizontalHeader()->installEventFilter(this);
    this->verticalHeader()->installEventFilter(this);

    // 连接水平表头的sectionResized信号
    connect(horizontalHeader(), &QHeaderView::sectionResized, this, &CSpreadsheet::onColumnWidthChanged);

    // 连接垂直表头的sectionResized信号
    connect(verticalHeader(), &QHeaderView::sectionResized, this, &CSpreadsheet::onRowHeightChanged);
}

int CSpreadsheet::getRows()        const
{
    return this->m_rows;
}

int CSpreadsheet::getColumns()     const
{
    return this->m_columns;
}
void CSpreadsheet::setRows(int rows)
{
    if (this->m_rows == rows)
        return;

    if (rows < 1)
        rows = 1;
    else if (rows > 200)
        rows = 200;

    if (this->m_rows > rows)
    {
        m_undoStack->beginMacro("");    //删除行
        int count = this->m_rows - rows;
        while (count)
        {
            this->setCurrentCell(this->rowCount() - 1, 0);
            onRemoveRow();
            count--;
        }
        m_undoStack->endMacro();
    }
    else
    {
        m_undoStack->beginMacro("");    //插入行
        int count = rows - this->m_rows;
        while (count)
        {
            this->setCurrentCell(this->rowCount() - 1, 0);
            onInsertRow();
            count--;
        }
        m_undoStack->endMacro();
    }
}

void CSpreadsheet::setColumns(int columns)
{
    if (this->m_columns == columns)
        return;

    if (columns < 1)
        columns = 1;
    else if (columns > 200)
        columns = 200;

    if (this->m_columns > columns)
    {
        m_undoStack->beginMacro("");    //删除列
        int count = this->m_columns - columns;
        while (count)
        {
            this->setCurrentCell(0, this->columnCount()-1);
            onRemoveColumn();
            count--;
        }
        m_undoStack->endMacro();
    }
    else
    {
        m_undoStack->beginMacro("");    //插入列
        int count = columns - this->m_columns;
        while (count)
        {
            this->setCurrentCell(0, this->columnCount()-1);
            onInsertColumn();
            count--;
        }
        m_undoStack->endMacro();
    }
}

//返回当前单元格的位置,它是按照电子制表软件的通常格式
QString CSpreadsheet::currentLocation() const
{
    return QChar('A' + currentColumn()) + QString::number(currentRow() + 1);
}

QTableWidgetSelectionRange CSpreadsheet::selectedRange() const
{
    QList<QTableWidgetSelectionRange> ranges = selectedRanges();
    if (ranges.isEmpty())
        return QTableWidgetSelectionRange();
    return ranges.first();
}

void CSpreadsheet::clear()
{
    setRowCount(0);
    setColumnCount(0);
    clearContents();  //释放内存
    setRowCount(m_rows);
    setColumnCount(m_columns);
    m_listSpan.clear();

    for (int i = 0; i < m_columns; i++)
    {
        QTableWidgetItem *item = new QTableWidgetItem;
        item->setText(col_to_name(i+1));
        setHorizontalHeaderItem(i, item);
        setColumnWidth(i, 80);

        for (int j = 0; j < m_rows; j++)
        {
            setText(j, i, QString(QChar('A' + i)) + QString("(%1, %2)").arg(i).arg(j));
            if (i == 0)
                setRowHeight(j, 25);
        }
    }

    setCurrentCell(0, 0);
}

bool CSpreadsheet::openFile(const QString &fileName)
{
    Document xlsx(fileName);
    Worksheet *sheet = dynamic_cast<Worksheet *>(xlsx.sheet(xlsx.sheetNames().at(0)));
    if (!sheet)
        return false;

    m_fileName = fileName;
    m_rows = sheet->dimension().lastRow();
    m_columns = sheet->dimension().lastColumn();
    qDebug()<<"m_rows"<<m_rows<<m_columns;
    reportType = (ReportType)xlsx.documentProperty("category").toInt(); //类别
    linkValuePathList = xlsx.documentProperty("description");     //备注
    clear();

    for (int row = 0; row < m_rows; row++)
    {
        for (int column = 0; column < m_columns; column++)
        {
            Cell *cell = sheet->cellAt(row + 1, column + 1);
            if (!cell)
                continue;

            //文本
            setText(row, column, cell->value().toString());

            //字体
            if (cell->format().hasFontData())
                itemCell(row, column)->setFont(cell->format().font());

            //对齐
            Qt::Alignment align;
            switch (cell->format().horizontalAlignment())
            {
            case Format::AlignLeft:
                align |= Qt::AlignLeft;
                break;
            case Format::AlignRight:
                align |= Qt::AlignRight;
                break;
            case Format::AlignHCenter:
                align |= Qt::AlignHCenter;
                break;
            case Format::AlignHJustify:
                align |= Qt::AlignJustify;
                break;
            default:
                break;
            }
            switch (cell->format().verticalAlignment())
            {
            case Format::AlignTop:
                align |= Qt::AlignTop;
                break;
            case Format::AlignBottom:
                align |= Qt::AlignBottom;
                break;
            case Format::AlignVCenter:
                align |= Qt::AlignVCenter;
                break;
            default:
                break;
            }
            itemCell(row, column)->setTextAlignment(align);

            //字体颜色
            if (cell->format().fontColor().isValid())
                itemCell(row, column)->setForeground(QBrush(cell->format().fontColor()));

            //背景颜色
            if (cell->format().patternBackgroundColor().isValid())
                itemCell(row, column)->setBackground(cell->format().patternBackgroundColor());

            //列宽
            if (row == 0)
            {
                if (sheet->columnWidth(column + 1) != 0)
                    this->setColumnWidth(column, sheet->columnWidth(column + 1)*6); //宽度与excel有差别
            }
        }

        //行高
        if (sheet->rowHeight(row + 1) != 0)
            this->setRowHeight(row, sheet->rowHeight(row + 1)*1.6);
    }

    //合并单元
    foreach (CellRange range, sheet->mergedCells())
    {
        TSpan span;
        span.row = range.firstRow() - 1;
        span.column =range.firstColumn() - 1;
        span.rowSpanCount = range.rowCount();
        span.columnSpanCount = range.columnCount();
        this->setSpan(span.row, span.column, span.rowSpanCount, span.columnSpanCount);
        m_listSpan.append(span);
    }
    connect(this, &CSpreadsheet::itemPressed, this, &CSpreadsheet::onItemDoubleClicked);
    connect(this, &CSpreadsheet::itemChanged, this, &CSpreadsheet::onItemChanged);
    refreshPropertyView();
    return true;
}

bool CSpreadsheet::saveFile(void)
{
    Document xlsx;
    for (int row = 0; row < m_rows; row++)
    {
        for (int column = 0; column < m_columns; column++)
        {
            QString str = text(row, column);

            //格式
            Format format;
            format.setFont(itemCell(row, column)->font());
            switch (itemCell(row, column)->textAlignment())
            {
            case Qt::AlignLeft | Qt::AlignVCenter:
                format.setHorizontalAlignment(Format::AlignLeft);
                break;
            case Qt::AlignRight | Qt::AlignVCenter:
                format.setHorizontalAlignment(Format::AlignRight);
                break;
            default:
                format.setHorizontalAlignment(Format::AlignHCenter);
                break;
            }
            format.setVerticalAlignment(Format::AlignVCenter);  //垂直固定在中间
            format.setBorderStyle(Format::BorderThin);          //边框线
            format.setFontColor(itemCell(row, column)->foreground().color()); //字体颜色
            format.setPatternBackgroundColor(itemCell(row, column)->background().color()); //填充颜色
            xlsx.write(row + 1, column + 1, str, format);

            //设置列宽
            if (row == 0)
                xlsx.setColumnWidth(column + 1, column + 1, this->columnWidth(column)/6); //宽度与excel有差别
        }

        //设置行高
        xlsx.setRowHeight(row + 1, this->rowHeight(row)/1.6);
    }

    //合并单元格
    for (int i = 0; i <m_listSpan.count(); i++)
    {
        int firstRow = m_listSpan.at(i).row + 1;
        int firstColumn = m_listSpan.at(i).column + 1;
        int lastRow = m_listSpan.at(i).row + m_listSpan.at(i).rowSpanCount;
        int lastColumn = m_listSpan.at(i).column + m_listSpan.at(i).columnSpanCount;
        xlsx.mergeCells(CellRange(firstRow, firstColumn, lastRow, lastColumn));
    }

    //类别
    xlsx.setDocumentProperty("category", QString::number(reportType));

    //备注
    xlsx.setDocumentProperty("description", linkValuePathList);

    if (!xlsx.saveAs(m_fileName))
    {
        QUIHelper::showMessageBoxError("保存文件失败，请确保文件不在只读状态。");
        return false;
    }
    return true;
}

void CSpreadsheet::setFileName(const QString &fileName)
{
    m_fileName = fileName;
}

QString CSpreadsheet::getFileName(void)
{
    return m_fileName;
}

QColor CSpreadsheet::getCurrentItemColor(void)
{
    QColor col = QColor(Qt::black);
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return col;

    col = items.at(0)->foreground().color();
    return col;
}

QFont CSpreadsheet::getCurrentItemFont(void)
{
    QFont font;
    font.fromString("SimSun,10,-1,5,50,0,0,0,0,0");
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return font;

    font = items.at(0)->font();
    return font;
}

int CSpreadsheet::getCurrentItemCount(void)
{
    return selectedItems().count();
}

bool CSpreadsheet::isAlignLeft(void)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return false;

    if (items.at(0)->textAlignment() == (Qt::AlignLeft | Qt::AlignVCenter))
        return true;
    return false;
}

bool CSpreadsheet::isAlignCenter(void)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return false;

    if (items.at(0)->textAlignment() == Qt::AlignCenter)
        return true;
    return false;
}

bool CSpreadsheet::isAlignRight(void)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return false;

    if (items.at(0)->textAlignment() == (Qt::AlignRight | Qt::AlignVCenter))
        return true;
    return false;
}

bool CSpreadsheet::isMerge(void)
{
    QList<QTableWidgetSelectionRange> selectRanges = selectedRanges();

    //判断是否有合并单元格
    foreach (auto select, selectRanges)
    {
        int row = this->rowSpan(select.topRow(), select.leftColumn());
        int column = this->columnSpan(select.topRow(), select.leftColumn());
        if ((row > 1) || (column > 1))
            return true;
    }
    return false;
}

void CSpreadsheet::cut(void)
{
    copy();
    del();
}

void CSpreadsheet::copy(void)
{
    QString copiedText;
    QModelIndexList currentSelectedIndexs = this->selectedIndexes();
    int currentRow = currentSelectedIndexs.at(0).row();
    for(int i = 0; i < currentSelectedIndexs.count(); i++)
    {
        if(currentRow != currentSelectedIndexs.at(i).row())
        {
            currentRow = currentSelectedIndexs.at(i).row();
            copiedText.append("\n");
            copiedText.append(currentSelectedIndexs.at(i).data().toString());
            continue;
        }
        if(0 != i)
        {
            copiedText.append("\t");
        }
        copiedText.append(currentSelectedIndexs.at(i).data().toString());
    }
    copiedText.append("\n");
    QApplication::clipboard()->setText(copiedText);
}

void CSpreadsheet::paste(void)
{
    QModelIndexList currentSelectedIndexs = this->selectedIndexes();
    if (currentSelectedIndexs.count() == 0)
        return;

    disconnect(this, &CSpreadsheet::itemChanged, this, &CSpreadsheet::onItemChanged); //粘贴时第一个点触发了重复修改
    QString textToPast = QApplication::clipboard()->text();
    QStringList tableRowDataList = textToPast.split("\n");
    QModelIndex currentIndex = selectedIndexes().at(0);    //防止选择多个时currentIndex为最后一个的情况
    m_undoStack->beginMacro("");    //粘贴
    for (int i = 0; i < rowCount() - currentIndex.row() && i < tableRowDataList.length()-1; ++i)
    {
        QStringList rowDataList = tableRowDataList.at(i).split("\t");
        for(int k = 0; k < columnCount() - currentIndex.column() && k < rowDataList.length(); k++)
        {
//            //空数据不覆盖
//            if (rowDataList.at(k) == "")
//                continue;

            //只对可编辑的单元允许粘贴
            int row = i+currentIndex.row();
            int column = k+currentIndex.column();
            if ((item(row, column)->flags() & Qt::ItemIsEditable) != 0)
            {
                if (item(row, column)->text() != rowDataList.at(k))
                    m_undoStack->push(new CTextCommand(row, column, item(row, column)->text(), rowDataList.at(k), this));
            }
        }
    }

    //只复制了一个信息，粘贴多行的情况
    if (tableRowDataList.length() == 2)
    {
        QStringList rowDataList = tableRowDataList.at(0).split("\t");
        if (rowDataList.count() == 1)
        {
            QModelIndexList current_selected_indexs = this->selectedIndexes();
            for(int i = 0; i < current_selected_indexs.count(); i++)
            {
                //只对可编辑的单元允许粘贴
                int row = current_selected_indexs.at(i).row();
                int column = current_selected_indexs.at(i).column();
                if ((item(row, column)->flags() & Qt::ItemIsEditable) != 0)
                {
                    if (item(row, column)->text() != tableRowDataList.at(0))
                        m_undoStack->push(new CTextCommand(row, column, item(row, column)->text(), tableRowDataList.at(0), this));
                }
            }
        }
    }
    m_undoStack->endMacro();
    connect(this, &CSpreadsheet::itemChanged, this, &CSpreadsheet::onItemChanged);
    m_lastDoubleClickedItem = NULL;
}

void CSpreadsheet::del(void)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //删除
    foreach (QTableWidgetItem *item, items)
        m_undoStack->push(new CTextCommand(item->row(), item->column(), item->text(), "", this));
    m_undoStack->endMacro();
}

void CSpreadsheet::merge(void)
{
    TSpan span;
    QList<QTableWidgetSelectionRange> selectRanges = selectedRanges();

    //判断是否有合并单元格需要拆分
    bool bFlag = false;
    foreach (auto select, selectRanges)
    {
        int row = this->rowSpan(select.topRow(), select.leftColumn());
        int column = this->columnSpan(select.topRow(), select.leftColumn());
        if ((row > 1) || (column > 1))
        {
            //取消合并
            span.row = select.topRow();
            span.column = select.leftColumn();
            span.rowSpanCount = row;
            span.columnSpanCount = column;
            m_undoStack->push(new CUnMergeCommand(span, this));
            removeSpan(select.topRow(), select.leftColumn());
            bFlag = true;
        }
    }

    if (bFlag == true)
        return;

    //合并单元格
    span.row = selectRanges.first().topRow();
    span.column = selectRanges.first().leftColumn();
    span.rowSpanCount = selectRanges.last().bottomRow() - selectRanges.first().topRow() + 1;
    span.columnSpanCount = selectRanges.last().rightColumn() - selectRanges.first().leftColumn() + 1;
    m_undoStack->push(new CMergeCommand(span, this));
    m_listSpan.append(span);
}

void CSpreadsheet::setFontBold(bool bold)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //加粗
    foreach (QTableWidgetItem *item, items)
    {
        QFont newFont = item->font();
        newFont.setBold(bold);
        m_undoStack->push(new CFontCommand(item->row(), item->column(), item->font(), newFont, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::setFontItalic(bool italic)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //倾斜
    foreach (QTableWidgetItem *item, items)
    {
        QFont newFont = item->font();
        newFont.setItalic(italic);
        m_undoStack->push(new CFontCommand(item->row(), item->column(), item->font(), newFont, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::setFontUnderline(bool underline)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //下划线
    foreach (QTableWidgetItem *item, items)
    {
        QFont newFont = item->font();
        newFont.setUnderline(underline);
        m_undoStack->push(new CFontCommand(item->row(), item->column(), item->font(), newFont, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::setAlignLeft(bool align)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //左对齐
    foreach (QTableWidgetItem *item, items)
    {
        int newAlign;
        if (align)
            newAlign = Qt::AlignLeft | Qt::AlignVCenter;
        else
            newAlign = Qt::AlignCenter;
        m_undoStack->push(new CTextAlignmentCommand(item->row(), item->column(), item->textAlignment(), newAlign, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::setAlignCenter(bool align)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //居中对齐
    foreach (QTableWidgetItem *item, items)
    {
        int newAlign;
        if (align)
            newAlign = Qt::AlignCenter;
        else
            newAlign = Qt::AlignRight | Qt::AlignVCenter;
        m_undoStack->push(new CTextAlignmentCommand(item->row(), item->column(), item->textAlignment(), newAlign, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::setAlignRight(bool align)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //右对齐
    foreach (QTableWidgetItem *item, items)
    {
        int newAlign;
        if (align)
            newAlign = Qt::AlignRight | Qt::AlignVCenter;
        else
            newAlign = Qt::AlignCenter;
        m_undoStack->push(new CTextAlignmentCommand(item->row(), item->column(), item->textAlignment(), newAlign, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::setTextFamily(const QString &f)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //字体
    foreach (QTableWidgetItem *item, items)
    {
        QFont newFont = item->font();
        newFont.setFamily(f);
        m_undoStack->push(new CFontCommand(item->row(), item->column(), item->font(), newFont, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::setTextSize(const QString &p)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //字体大小
    foreach (QTableWidgetItem *item, items)
    {
        QFont newFont = item->font();
        newFont.setPointSize(p.toInt());
        m_undoStack->push(new CFontCommand(item->row(), item->column(), item->font(), newFont, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::setTextColor(const QColor &c)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //字体颜色
    foreach (QTableWidgetItem *item, items)
    {
        m_undoStack->push(new CTextColorCommand(item->row(), item->column(), item->foreground().color(), c, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::setTextFillColor(const QColor &c)
{
    QList<QTableWidgetItem *> items = selectedItems();
    if (items.isEmpty())
        return;

    m_undoStack->beginMacro("");    //填充颜色
    foreach (QTableWidgetItem *item, items)
    {
        m_undoStack->push(new CTextFillColorCommand(item->row(), item->column(), item->background().color(), c, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::onRemoveColumn(void)
{
    QTableWidgetSelectionRange range = selectedRange();
    m_undoStack->beginMacro("");    //删除列
    for (int column = range.leftColumn(); column <= range.rightColumn(); column++)
    {
        //用于后退时恢复文字
        for (int row = 0; row < m_rows; row++)
        {
            m_undoStack->push(new CTextCommand(row, column, text(row, column), "", this));
            m_undoStack->push(new CFontCommand(row, column, itemCell(row, column)->font(), itemCell(row, column)->font(), this));
            m_undoStack->push(new CTextAlignmentCommand(row, column, itemCell(row, column)->textAlignment(),
                                                        itemCell(row, column)->textAlignment(), this));
            m_undoStack->push(new CTextColorCommand(row, column, itemCell(row, column)->foreground().color(),
                                                    itemCell(row, column)->foreground().color(), this));
            m_undoStack->push(new CTextFillColorCommand(row, column, itemCell(row, column)->background().color(),
                                                        itemCell(row, column)->background().color(), this));
        }
        m_undoStack->push(new CColumnWidthCommand(column, this->columnWidth(column), this->columnWidth(column), this));
    }
    m_undoStack->push(new CMulMergeCommand(m_listSpan, this));
    for (int column = range.rightColumn(); column >= range.leftColumn(); column--)
    {
        //从大到小删除
        m_undoStack->push(new CRemoveColumnCommand(column, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::onRemoveRow(void)
{
    QTableWidgetSelectionRange range = selectedRange();
    m_undoStack->beginMacro("");    //删除行
    for (int row = range.topRow(); row <= range.bottomRow(); row++)
    {
        //用于后退时恢复文字
        for (int column = 0; column < m_columns; column++)
        {
            m_undoStack->push(new CTextCommand(row, column, text(row, column), "", this));
            m_undoStack->push(new CFontCommand(row, column, itemCell(row, column)->font(), itemCell(row, column)->font(), this));
            m_undoStack->push(new CTextAlignmentCommand(row, column, itemCell(row, column)->textAlignment(),
                                                        itemCell(row, column)->textAlignment(), this));
            m_undoStack->push(new CTextColorCommand(row, column, itemCell(row, column)->foreground().color(),
                                                    itemCell(row, column)->foreground().color(), this));
            m_undoStack->push(new CTextFillColorCommand(row, column, itemCell(row, column)->background().color(),
                                                        itemCell(row, column)->background().color(), this));
        }
        m_undoStack->push(new CRowHeightCommand(row, this->rowHeight(row), this->rowHeight(row), this));
    }
    m_undoStack->push(new CMulMergeCommand(m_listSpan, this));
    for (int row = range.bottomRow(); row >= range.topRow(); row--)
    {
        //从大到小删除
        m_undoStack->push(new CRemoveRowCommand(row, this));
    }
    m_undoStack->endMacro();
}

void CSpreadsheet::onInsertColumn(void)
{
    int column = this->currentColumn();
    m_undoStack->push(new CInsertColumnCommand(column+1, this));
}

void CSpreadsheet::onInsertRow(void)
{
    int row = this->currentRow();
    m_undoStack->push(new CInsertRowCommand(row+1, this));
}

void CSpreadsheet::selectCurrentRow()
{
    selectRow(currentRow());
}

void CSpreadsheet::selectCurrentColumn()
{
    selectColumn(currentColumn());
}

void CSpreadsheet::onSetColumnWidth(void)
{
    QTableWidgetSelectionRange range = selectedRange();
    CSetColumnWidthDialog dlg;
    dlg.setWidth(this->columnWidth(range.leftColumn()));
    if(dlg.exec() == CSetColumnWidthDialog::Accepted)
    {
        int nWidth = dlg.getWidth();
        m_undoStack->beginMacro("");    //设置列宽
        for (int column = range.leftColumn(); column <= range.rightColumn(); column++)
        {
            m_undoStack->push(new CColumnWidthCommand(column, this->columnWidth(column), nWidth, this));
        }
        m_undoStack->endMacro();
    }
}

void CSpreadsheet::onSetRowHeight(void)
{
    QTableWidgetSelectionRange range = selectedRange();
    CSetRowHeightDialog dlg;
    dlg.setHeight(this->rowHeight(range.topRow()));
    if(dlg.exec() == CSetColumnWidthDialog::Accepted)
    {
        int nHeight = dlg.getHeight();
        m_undoStack->beginMacro("");    //设置行高
        for (int row = range.topRow(); row <= range.bottomRow(); row++)
        {
            m_undoStack->push(new CRowHeightCommand(row, this->rowHeight(row), nHeight, this));
        }
        m_undoStack->endMacro();
    }
}

void CSpreadsheet::slotValueChanged(QWidget *widget, const QString &propertyName, const QVariant &val)
{
    if (widget != this)
        return;

    QVariant oldVal = widget->property(propertyName.toStdString().c_str());
    if (oldVal == val)
        return;

    if (propertyName == "m_rows")
    {
        setRows(val.toInt());
        return;
    }

    if (propertyName == "m_columns")
    {
        setColumns(val.toInt());
        return;
    }

    m_undoStack->push(new CPropertyCommand(propertyName, oldVal, val, this));
}

void CSpreadsheet::onCurrentSelectChanged()
{
    emit modified();
}

//记录当前双击编辑内容，用于onItemChanged使用
void CSpreadsheet::onItemDoubleClicked(QTableWidgetItem *item)
{
    m_lastDoubleClickedItem = item;
    m_lasetDoubleClickedItemText = m_lastDoubleClickedItem->text();
}

void CSpreadsheet::onItemChanged(QTableWidgetItem *item)
{
    if (m_lastDoubleClickedItem != item)
        return;
    m_lastDoubleClickedItem = NULL;

    if (m_lasetDoubleClickedItemText == item->text())
        return;
    m_undoStack->push(new CTextCommand(item->row(), item->column(), m_lasetDoubleClickedItemText, item->text(), this));
}

void CSpreadsheet::onColumnWidthChanged(int logicalIndex, int oldSize, int newSize)
{
    if (!m_headerMouseEnter)
        return;

    if (!m_columnWidthOrRowHeightChangedStart)
    {
        m_undoStack->beginMacro("");    //设置列宽
        m_columnWidthOrRowHeightChangedStart = true;
    }

    m_undoStack->push(new CChangeSectionSizeCommand(logicalIndex, oldSize, newSize, horizontalHeader()));
}

void CSpreadsheet::onRowHeightChanged(int logicalIndex, int oldSize, int newSize)
{
    if (!m_headerMouseEnter)
        return;

    if (!m_columnWidthOrRowHeightChangedStart)
    {
        m_undoStack->beginMacro("");    //设置行高
        m_columnWidthOrRowHeightChangedStart = true;
    }

    m_undoStack->push(new CChangeSectionSizeCommand(logicalIndex, oldSize, newSize, verticalHeader()));
}

//tex()私有函数可以返回给定单元格中的文本。如果cell( )返回的是一个空指针,则表示该单元格是空的,因而返回一个空字符串。
QString CSpreadsheet::text(int row, int column) const
{
    CSpreadSheetItem *c = itemCell(row, column);
    if (c)
        return c->text();
    return "";
}

void CSpreadsheet::removeSpan(quint16 row, quint16 column)
{
    for (int i = 0; i <m_listSpan.count(); i++)
    {
        if ((m_listSpan.at(i).row == row) && (m_listSpan.at(i).column == column))
        {
            m_listSpan.removeAt(i);
            break;
        }
    }
}

bool CSpreadsheet::hasSpan(quint16 row, quint16 column)
{
    for (int i = 0; i <m_listSpan.count(); i++)
    {
        if (((row >= m_listSpan.at(i).row) && (row < m_listSpan.at(i).row + m_listSpan.at(i).rowSpanCount))
            && ((column >= m_listSpan.at(i).column) && (column < m_listSpan.at(i).column + m_listSpan.at(i).columnSpanCount)))
        {
            return true;
        }
    }
    return false;
}

void CSpreadsheet::setText(int row, int column, const QString &text)
{
    CSpreadSheetItem *c = itemCell(row, column);
    if (!c)
    {
        c = new CSpreadSheetItem;
        setItem(row, column, c);
    }
    c->setData(Qt::EditRole, text);
}

//和QTableWidget::item()函数的作用一样,只不过它返回的是一个Cell指针,而不是一个QTableWidgetItem指针
CSpreadSheetItem *CSpreadsheet::itemCell(int row, int column) const
{
    return static_cast<CSpreadSheetItem *>(item(row, column));
}

QUndoStack *CSpreadsheet::undoStack() const
{
    return m_undoStack;
}

void CSpreadsheet::removeOneColumn(int column)
{
    removeColumn(column);
    m_columns--;

    for (int i = 0; i < m_columns; i++)
    {
        this->horizontalHeaderItem(i)->setText(col_to_name(i+1));
    }
    refreshListSpan();
    refreshPropertyView();
}

void CSpreadsheet::removeOneRow(int row)
{
    removeRow(row);
    m_rows--;
    refreshListSpan();
    refreshPropertyView();
}

void CSpreadsheet::insertOneColumn(int column)
{
    insertColumn(column);
    m_columns++;

    QTableWidgetItem *item = new QTableWidgetItem;
    setHorizontalHeaderItem(column, item);

    for (int i = 0; i < m_columns; i++)
    {
        this->horizontalHeaderItem(i)->setText(col_to_name(i+1));
    }
    refreshListSpan();

    //初始化item
    for (int j = 0; j < m_rows; j++)
    {
        setText(j, column, "");
    }
    refreshPropertyView();
}

void CSpreadsheet::insertOneRow(int row)
{
    insertRow(row);
    m_rows++;
    refreshListSpan();

    //初始化item
    for (int j = 0; j < m_columns; j++)
    {
        setText(row, j, "");
    }
    refreshPropertyView();
}

//刷新合并单元list，防止删除列行时导致之前的list不对
void CSpreadsheet::refreshListSpan(void)
{
    m_listSpan.clear();
    for (int row = 0; row < m_rows; row++)
    {
        for (int column = 0; column < m_columns; column++)
        {
            if (hasSpan(row, column))
                continue;

            int rSpan = this->rowSpan(row, column);
            int colSpan = this->columnSpan(row, column);
            if ((rSpan > 1) || (colSpan > 1))
            {
                TSpan span;
                span.row = row;
                span.column = column;
                span.rowSpanCount = rSpan;
                span.columnSpanCount = colSpan;
                m_listSpan.append(span);
            }
        }
    }
}

小结

实现了对EXCEL大部分的编辑功能，包括合并单元格，对单元格的字体进行编辑，单元格填充颜色等功能，可应用于一些需要自定义表格的项目中。