1 #include <QAxObject> 2 #include <QDir> 3 #include <QFile> 4 class Range:QObject 5 { 6 public: 7 Range(QAxObject* p,QObject* parent=nullptr):QObject(parent) { m_pRange = p; } 8 ~Range() 9 { 10 } 11 public: 12 //垂直居中 13 Range* Vcenter() 14 { 15 m_pRange->setProperty("VerticalAlignment", -4108); 16 return this; 17 } 18 //水平居中 19 Range* Hcenter() 20 { 21 m_pRange->setProperty("HorizontalAlignment", -4108); 22 return this; 23 } 24 //行高 25 Range* RowHeight(int nHeight) 26 { 27 m_pRange->setProperty("RowHeight", nHeight); 28 return this; 29 } 30 //列宽 31 Range* ColumnWidth(int nWidth) 32 { 33 m_pRange->setProperty("ColumnWidth", nWidth); 34 return this; 35 } 36 //自动换行 37 Range* AutoWrapText() 38 { 39 m_pRange->setProperty("WrapText", true); //内容过多，自动换行 40 return this; 41 } 42 //背景色 43 Range* BackgroundColor(QColor crBg) 44 { 45 QAxObject* interior = m_pRange->querySubObject("Interior"); 46 interior->setProperty("Color", crBg); 47 return this; 48 } 49 Range* BorderColor(QColor crBorder) 50 { 51 QAxObject* border = m_pRange->querySubObject("Borders"); 52 border->setProperty("Color", crBorder); //设置单元格边框色（蓝色） 53 /* 54 .LineStyle = xlContinuous border->setProperty("LineStyle", 4);,下面类同 55 .ColorIndex = 0 56 .TintAndShade = 0 57 .Weight = xlThin*/ 58 return this; 59 } 60 //设置值 61 Range* Value(QVariant v) 62 { 63 m_pRange->dynamicCall("Value", v); 64 return this; 65 } 66 //设置字体 67 Range* Font(QString strFaceName, bool bBold, bool bItalic, int nSize, QColor crText) 68 { 69 QAxObject *font = m_pRange->querySubObject("Font"); //获取单元格字体 70 font->setProperty("Name", strFaceName); //设置单元格字体font->setProperty("Name", QStringLiteral("华文彩云")); //设置单元格字体 71 font->setProperty("Bold", bBold); //设置单元格字体加粗 72 font->setProperty("Size", nSize); //设置单元格字体大小 73 font->setProperty("Italic", bItalic); //设置单元格字体斜体 74 //font->setProperty("Underline", 2); //设置单元格下划线 75 font->setProperty("Color", crText); //设置单元格字体颜色（红色） 76 return this; 77 } 78 private: 79 QAxObject* m_pRange; 80 }; 81 class ExcelHelper:public QObject 82 { 83 public: 84 ExcelHelper(QObject* parent = nullptr); 85 void Open(const QString &fileName, bool bVisible=true); 86 void New(bool bVisible=true); 87 void ActiveSheet(int nItem); //激活sheet 88 void ActiveSheet(const QString &sheetName); //激活sheet 89 void AddSheet(const QString &sheetName); 90 91 Range* GetRange(int row, int column);//获得一个单元格区域 92 Range* GetRange(QString str);//获得一个区域,如A3:B18或A1 93 void SetCellValue(int row, int column,const QVariant &value); 94 void Save(const QString &fileName); 95 void Close(); 96 97 QAxObject *m_pApplication; 98 QAxObject *m_pWorkBooks; 99 QAxObject *m_pWorkBook; 100 QAxObject *m_pSheets; 101 QAxObject *m_pActiveSheet; 102 };
CPP：
1 #include "stdafx.h" 2 #include "ExcelHelper.h" 3 ExcelHelper::ExcelHelper(QObject* parent) :QObject(parent) 4 { 5 m_pApplication = new QAxObject(); 6 m_pApplication->setControl("Excel.Application"); //连接Excel控件 7 } 8 9 10 void ExcelHelper::Open(const QString &fileName, bool bVisible) 11 { 12 QFile file(fileName); 13 if (file.exists()) 14 { 15 m_pApplication->dynamicCall("SetVisible(bool)", bVisible); //是否显示窗体 16 m_pApplication->setProperty("DisplayAlerts", false); //不显示任何警告信息。 17 m_pWorkBooks = m_pApplication->querySubObject("Workbooks"); 18 m_pWorkBook = m_pWorkBooks->querySubObject("Open(const QString &)", fileName); 19 //默认有一个sheet 20 m_pSheets = m_pWorkBook->querySubObject("Sheets"); 21 m_pActiveSheet = m_pSheets->querySubObject("Item(int)", 1); 22 } 23 else 24 { 25 QMessageBox::critical(nullptr, T("系统提示"), T("打开:") + fileName + T("失败,请确认该文件存在并未被占用")); 26 } 27 } 28 //新建Execl文件 29 void ExcelHelper::New(bool bVisible) 30 { 31 m_pApplication->dynamicCall("SetVisible(bool)", bVisible); //是否显示窗体 32 m_pApplication->setProperty("DisplayAlerts", false); //不显示任何警告信息。 33 m_pWorkBooks = m_pApplication->querySubObject("Workbooks"); 34 m_pWorkBooks->dynamicCall("Add"); 35 m_pWorkBook = m_pApplication->querySubObject("ActiveWorkBook"); 36 //默认有一个sheet 37 m_pSheets = m_pWorkBook->querySubObject("Sheets"); 38 m_pActiveSheet = m_pSheets->querySubObject("Item(int)", 1); 39 } 40 void ExcelHelper::ActiveSheet(int nItem) 41 { 42 m_pActiveSheet = m_pSheets->querySubObject("Item(int)", nItem); 43 m_pActiveSheet->dynamicCall("Select"); 44 } 45 void ExcelHelper::ActiveSheet(const QString &sheetName) 46 { 47 m_pActiveSheet = m_pWorkBook->querySubObject("Sheets(string)", sheetName); 48 m_pActiveSheet->dynamicCall("Select"); 49 } 50 51 void ExcelHelper::AddSheet(const QString &sheetName) 52 { 53 int cnt = 1; 54 QAxObject *pLastSheet = m_pSheets->querySubObject("Item(int)", cnt); 55 m_pSheets->querySubObject("Add(QVariant)", pLastSheet->asVariant()); 56 m_pActiveSheet = m_pSheets->querySubObject("Item(int)", cnt); 57 pLastSheet->dynamicCall("Move(QVariant)", m_pActiveSheet->asVariant()); 58 m_pActiveSheet->setProperty("Name", sheetName); 59 } 60 61 void ExcelHelper::SetCellValue(int row, int column, const QVariant &value) 62 { 63 QAxObject *pRange = m_pActiveSheet->querySubObject("Cells(int,int)", row, column); 64 pRange->dynamicCall("Value", value); 65 } 66 Range* ExcelHelper::GetRange(int row, int column) 67 { 68 QAxObject *pRange = m_pActiveSheet->querySubObject("Cells(int,int)", row, column); 69 return new Range(pRange); 70 } 71 Range* ExcelHelper::GetRange(QString str) 72 { 73 QAxObject *pRange = m_pActiveSheet->querySubObject("Range(string)",str); 74 return new Range(pRange,this); 75 } 76 //保存Excel 77 void ExcelHelper::Save(const QString &fileName) 78 { 79 m_pWorkBook->dynamicCall("SaveAs(string)", fileName); 80 } 81 82 //关闭Excel 83 void ExcelHelper::Close() 84 { 85 if (m_pApplication != NULL) { 86 m_pApplication->dynamicCall("Quit()"); 87 delete m_pApplication; 88 m_pApplication = NULL; 89 } 90 }
使用：
1 ExcelHelper excel; 2 excel.New(true); 3 excel.AddSheet(T("sheet2")); 4 excel.AddSheet(T("sheet3")); 5 excel.AddSheet(T("sheet4")); 6 excel.ActiveSheet(T("sheet3")); 7 excel.SetCellValue(1, 1, "1"); 8 excel.SetCellValue(1, 2, 2); 9 excel.GetRange(T("A1:B3"))->Value(T("华文行楷"))->BackgroundColor(QColor(255, 0, 0))->BorderColor(QColor(0, 255, 0))->Font(T("华文行楷"), true, true, 20, QColor(255, 0, 255)); 10 excel.Save(T("D:\aaaaa.xlsx")); 11 excel.Close();