VBA简介
VBA是Visual Basic for Application的缩写,是一种应用程序自动化语音。所谓应用程序自动化,是指通过程序或者脚本让应用程序,例如让Microsoft Excel、Word自动化完成一些工作。
VBA具有VB语言的大多数特征和易用性,它最大特点就是将Excel作为开发平台来开发应用程序,可以应用Excel的所有已有功能,例如数据处理、图表绘制、数据库连接、内置函数等等。
VBA开发环境
VBA集成开发环境(IDE,Integrated Development Environment)是进行VBA程序设计和代码编写的地方,同一版本的Office共享同一IDE。VBA代码和Excel文件是保存在一起的,可以通过点击“工具——宏——Visual Basic编辑器”打开VBA的IDE环境,进行程序设计和代码编写。
Excel对象的主要层次结构
Application对象->Workbook对象->Worksheet对象->Range对象
QT操作Excel
使用QAxObject组件可以方便的使用Office套件的API,QAxObject对COM对象进行了封装,QAxObject派生自QAxBase,而QAxBase提供了一组API,该API通过IUnknown指针直接访问COM对象,我们这里讲的Excel也是一个COM对象,可以通过QAxObject来操作它。
新建一个excel
QAxObject *pApplication = NULL;
QAxObject *pWorkBooks = NULL;
QAxObject *pWorkBook = NULL;
QAxObject *pSheets = NULL;
QAxObject *pSheet = NULL;
void newExcel(const QString &fileName)
{
pApplication = new QAxObject();
pApplication->setControl("Excel.Application");//连接Excel控件
pApplication->dynamicCall("SetVisible(bool)", false);//false不显示窗体
pApplication->setProperty("DisplayAlerts", false);//不显示任何警告信息。
pWorkBooks = pApplication->querySubObject("Workbooks");
QFile file(fileName);
if (file.exists())
{
pWorkBook = pWorkBooks->querySubObject("Open(const QString &)", fileName);
}
else
{
pWorkBooks->dynamicCall("Add");
pWorkBook = pApplication->querySubObject("ActiveWorkBook");
}
pSheets = pWorkBook->querySubObject("Sheets");// 得到Sheets对象的指针
pSheet = pSheets->querySubObject("Item(int)", 1);// 得到第一个sheet对象的指针
//pSheet = pSheets->querySubObject("Item(const QVariant)", QVariant(“AAA”));// 得到第一个sheet对象的指针}
}
增加1个Worksheet(插入至最后一行)
void appendSheet(const QString &sheetName)
{
int cnt = pSheets->querySubObject(“Item(int)”).toInt();// 获取工作表数目
QAxObject *pLastSheet = pSheets->querySubObject("Item(int)", cnt);
pSheets->querySubObject("Add(QVariant)", pLastSheet->asVariant());
pSheet = pSheets->querySubObject("Item(int)", cnt);
pLastSheet->dynamicCall("Move(QVariant)", pSheet->asVariant());
pSheet->setProperty("Name", sheetName);
}
删除1个Worksheet
void deletedSheet(const QString &sheetName)
{
pSheet = pSheets->querySubObject("Item(const QVariant)", QVariant(sheetName));
pSheet->dynamicCall(“Delete”);
}
向Excel单元格中写入数据
void setCellValue(int row, int column, const QString &value)
{
QAxObject *pRange = pSheet->querySubObject("Cells(int,int)", row, column);
pRange->dynamicCall("Value", value);
}
释放Excel
void freeExcel()
{
if (pApplication != NULL)
{
pApplication->dynamicCall("Quit()");
delete pApplication;
pApplication = NULL;
}
}
//获取标题
QVarianttitle_value = excel.property("Caption");
//操作单元格(第2行第2列)
QAxObject *cell =work_sheet->querySubObject("Cells(int,int)", 2, 2);
//设置单元格值
cell->setProperty("Value","ABC");
//设置单元格行高
cell->setProperty("RowHeight",50);
//设置单元格列宽
cell->setProperty("ColumnWidth",30);
//左对齐(xlLeft):-4131 居中(xlCenter):-4108 右对齐(xlRight):-4152
cell->setProperty("HorizontalAlignment",-4108);
//上对齐(xlTop)-4160 居中(xlCenter):-4108 下对齐(xlBottom):-4107
cell->setProperty("VerticalAlignment",-4108);
//内容过多,自动换行
cell->setProperty("WrapText",true);
//清空单元格内容
cell->dynamicCall("ClearContents()");
//设置单元格背景色(绿色)
QAxObject* interior =cell->querySubObject("Interior");
interior->setProperty("Color",QColor(0, 255, 0));
//设置单元格边框色(蓝色)
QAxObject* border =cell->querySubObject("Borders");
border->setProperty("Color",QColor(0, 0, 255));
//获取单元格字体
QAxObject *font =cell->querySubObject("Font");
//设置单元格字体
font->setProperty("Name",QStringLiteral("华文彩云"));
//设置单元格字体加粗
font->setProperty("Bold",true);
//设置单元格字体大小
font->setProperty("Size", 20);
//设置单元格字体斜体
font->setProperty("Italic",true);
//设置单元格下划线
font->setProperty("Underline",2);
//设置单元格字体颜色(红色)
font->setProperty("Color",QColor(255, 0, 0));
//自动保存文件
work_book->dynamicCall("Save()");
//另存为另一个文件
work_book->dynamicCall("SaveAs(constQString&)", "E:\\test2.xlsx");
//关闭文件
work_book->dynamicCall("Close(Boolean)",false);
//退出
excel.dynamicCall("Quit(void)");
//合并单元格
//mergeRange = sheet->querySubObject("Rows(int)", iRowCount);
mergeRange = sheet->querySubObject("Range(QVariant)",tr("A%1:L%2").arg(iRowCount).arg(iRowCount));
mergeRange->dynamicCall("Merge(QVariant)", "false");
//自动展开到合适宽度
QAxObject *autoFitRange = sheet->querySubObject("Columns(A:I)");
autoFitRange->dynamicCall("AutoFit");
void EvaExcel::ToExcel(char* strPath,const Result* pResult)
{
try
{
// 获取一个Excel对象
//QAxWidget excel("Excel.Application"); //Widgets must be created in the GUIthread
QAxObject excel("Excel.Application");
if(excel.isNull()) return;
// 得到Workbooks集合的指针
QAxObject* workbooks= excel.querySubObject("Workbooks");
if(workbooks == NULL ) return;
workbooks->dynamicCall(("Open(const QString&"),strPath);
excel.setProperty("Caption","QtExcel");
添加一个新的工作簿
//workbooks->dynamicCall("Add");
//获取当前工作簿
QAxObject* workbook= excel.querySubObject("ActiveWorkBook");
if(workbook == NULL ) return;
//得到Sheets对象的指针
QAxObject* sheets= workbook->querySubObject("Sheets");
if(sheets == NULL ) return;
//获取工作表集合中的工作表,即sheet1
QAxObject* worksheet= sheets->querySubObject("Item(const QVariant &)",QVariant("Sheet1"));
if(worksheet == NULL ) return;
//设置sheet1工作为选中状态
worksheet->dynamicCall("Select()");
//获取起始行
QAxObject *used_range= worksheet->querySubObject("UsedRange");
QAxObject *rows= used_range->querySubObject("Rows");
int row_start = used_range->property("Row").toInt();
//获取行数
int row_count = rows->property("Count").toInt();
worksheet->querySubObject("Rows(int)",1)->querySubObject("insert()");
worksheet->querySubObject("Rows(int)",2)->querySubObject("insert()");
worksheet->querySubObject("Rows(int)",3)->querySubObject("insert()");
worksheet->querySubObject("Rows(int)",4)->querySubObject("insert()");
worksheet->querySubObject("Range(const QVariant&)",QVariant("A4:H4"))->querySubObject("Merge()");
QString qstrTemp;
qstrTemp = pResult->GetTestDate();
qstrTemp +=" ";
qstrTemp += pResult->GetTestTime();
qstrTemp +=" ";
qstrTemp += pResult->GetSampleName();
QAxObject *cellX= worksheet->querySubObject("Cells(int,int)",1,1); // x,y
if(cellX == NULL)
{
workbook->dynamicCall("Close()");
excel.dynamicCall("Quit()");
return;
}
QAxObject *fontX= cellX->querySubObject("Font");
fontX->setProperty("Name",QString("Microsoft YaHei"));
fontX->setProperty("Size",14);
//cellX->setProperty("HorizontalAlignment",-4108);//xlCenter
cellX->setProperty("ColumnWidth",12);
cellX->dynamicCall("SetValue(const QVariant&)",QVariant(qstrTemp));
worksheet->querySubObject("Range(const QVariant&)",QVariant("A1:H1"))->querySubObject("Merge()");
QString qstrContent= pResult->GetContent();
SplitResultToList(qstrContent);
QVector<ResultTable*>::iterator iter;
int i = 1;
for(iter = m_ResultExcel.begin();iter != m_ResultExcel.end();++iter)
{
QString strName= (*iter)->GetEleName();
QString strContent= (*iter)->GetEleContent();
QAxObject *cell2= worksheet->querySubObject("Cells(int,int)",2,i); // x,y
if(cell2 == NULL)
{
workbook->dynamicCall("Close()");
excel.dynamicCall("Quit()");
return;
}
QAxObject *font2= cell2->querySubObject("Font");
font2->setProperty("Name",QString("Microsoft YaHei"));
font2->setProperty("Size",14);
cell2->setProperty("HorizontalAlignment", -4108);//xlCenter
cell2->setProperty("ColumnWidth",12);
cell2->dynamicCall("SetValue(const QVariant&)",QVariant(strName));
QAxObject *cell3= worksheet->querySubObject("Cells(int,int)",3,i); // x,y
if(cell3 == NULL)
{
workbook->dynamicCall("Close()");
excel.dynamicCall("Quit()");
return;
}
QAxObject *font3= cell3->querySubObject("Font");
font3->setProperty("Name",QString("Microsoft YaHei"));
font3->setProperty("Size",14);
cell3->setProperty("HorizontalAlignment", -4108);//xlCenter
cell3->setProperty("ColumnWidth",12);
cell3->dynamicCall("SetValue(const QVariant&)",QVariant(strContent));
i++;
}
workbook->querySubObject("Save()");
//关闭工作簿
workbook->dynamicCall("Close()");
//关闭excel
excel.dynamicCall("Quit()");
}
catch(...)
{
return ;
}
}