//testExcel2.pro#————————————————-## Project created by QtCreator 2013-04-02T21:09:33##————————————————-QT+= core guigreaterThan(QT_MAJOR_VERSION, 4): QT += widgetsTARGET = testExcel2TEMPLATE = appCONFIG += qaxcontainerSOURCES += main.cpp\mainwindow.cpp \office.cpp \excel.cppHEADERS += mainwindow.h \excel.h \office.h//mainwindow.h#ifndef MAINWINDOW_H#define MAINWINDOW_H#include <QPushButton>#include "excel.h"#include <QAxObject>#include <QFileDialog>#include <QFileinfo>#include <QDir>#include <qmath.h>#include <QDebug>class MainWindow : public QPushButton{Q_OBJECTpublic:MainWindow(QWidget *parent = 0);~MainWindow();public slots:void click_pushbutton();};#endif // MAINWINDOW_H//main.cpp#include "mainwindow.h"#include <QApplication>int main(int argc, char *argv[]){QApplication a(argc, argv);MainWindow w;w.show();return a.exec();}//mainwindow.cpp#include "mainwindow.h"MainWindow::MainWindow(QWidget *parent): QPushButton(parent){connect(this,SIGNAL(clicked()),this,SLOT(click_pushbutton()));this->setText(tr("Push Me"));}MainWindow::~MainWindow(){}void MainWindow::click_pushbutton(){Excel::Application *excel = new Excel::Application(this);excel->SetVisible(false);//不显示Excel窗体excel->SetDisplayAlerts(false);//不显示警告Excel::Workbooks *workbooks = excel->Workbooks();//工作簿集合Excel::Workbook *workbook = workbooks->Add();//添加工作簿Excel::Sheets *worksheets = workbook->Sheets();//工作表集合//这里若写Excel::Worksheets *worksheets = workbook->Sheets();//会出现cannot convert ‘Excel::Sheets*’ to ‘Excel::Worksheets*’ in initialization这样的错误Excel::Worksheet* worksheet = new Excel::Worksheet(new Excel::_Worksheet(worksheets->Item(1)));//获取工作表//这里若写Excel::Worksheet *worksheet = worksheets->Item(1);//会出现cannot convert ‘IDispatch*’ to ‘Excel::Worksheet*’ in initialization这样的错误//因为worksheets->Item(1)返回的是IDispatch*类型。要将IDispatch*转换为Excel的对象，就得用上面的//new的方法。若Excel空间中包含_XXXX这样的类（如Excel::_Worksheet），则生成XXXX对象//（如Excel::Worksheet）时，就用new Excel::XXXX(new Excel_XXXX(IDispatch*));这样的方法，例如//Excel::Worksheet* worksheet = new Excel::Worksheet(new Excel::_Worksheet(worksheets->Item(1)));//如果某对象XXXX没有_XXXX这样的类，那么将IDispatch*转换为该对象的指针时只用new XXXX(IDispatch*)//这样的方法就行了，例如下面的//Excel::SeriesCollection *seriesCollection = new Excel::SeriesCollection(chart->SeriesCollection());//因为源码是utf8格式的，所以这里用QString::fromUtf8(…)，否则Excel中会出现乱码worksheet->SetName(QString::fromUtf8("【测试】"));//设置中文表名QString picpath = QFileDialog::getOpenFileName(this,tr("Open"),".",tr("picture file (*.jpg | *.png)"));if(!picpath.isEmpty()){worksheet->SetBackgroundPicture(QDir::toNativeSeparators(picpath));//为工作表设置背景图片//有关字体的Background属性，参见//http://msdn.microsoft.com/en-us/library/office/bb220875(v=office.12).aspxworksheet->Cells()->Font()->SetBackground(Excel::xlBackgroundAutomatic);//设置背景图片和文字重叠时的显示效果worksheet->Cells()->Font()->SetBold(false);//不要粗体}Excel::Range *cell;Excel::Range *cells = worksheet->Cells();//获取工作表中的所有单元格for(int i=0;i<10;i++){cells->SetItem(i+1,i+1,(i+1)*(i+1));//设置第i+1行，第i+1列的单元格的值QString range = QString(‘A’+i)+QString::number(i+1);cell = worksheet->Range(range);获取第i+1行，第i+1列的单元格cell->Font()->SetItalic(true);//设置字体为斜体cell->Font()->SetSize(25);//设置字号cell->Font()->SetColor(QRgb("#FF0000")+i*30);//设置字体颜色，每次循环颜色都不同}//测试中文输入cells->SetItem(3,5,QString::fromUtf8("输入中文"));//在第3行，第5列的单元格中输入中文字符//测试Excel的函数1cells->SetItem(2,1,5);cell = worksheet->Range("A3");//获取A3单元格cell->SetFormula("=sum(A1:A2)");//求取A1:A2单元格中值的和//测试Excel的函数1cells->SetItem(3,2,10);cells->SetItem(4,2,"=sum(B1:B3)");//求取B1:B3单元格中值的和//以上两种方式说明用setFormula和setItem来设置函数，效果是一样的//Qt的dumpcpp工具没有生成Cells(int,int)这种用法，所以用以下方法//间接调用Cells(int,int)函数QAxObject* range = worksheet->querySubObject("Cells(int,int)",4,6);cell = (Excel::Range*)range;cell->SetValue(QString::fromUtf8("因为dumcpp没有自动生成的函数Cells(int,int)，故用querySubObject(\&;Cells(int,int),\&;,4,6)的方式调用"));//由于Cells(int,int)十分常用，所以我在excel.h的Worksheet类中添加了Excel::Range* Worksheet::Cells(int,int)函数//在excel.h中搜索class EXCEL_EXPORT Worksheet就可以找到Worksheet类//然后在其中添加函数Cells(int rowIndex,int colIndex)即可。如：//inline Excel::Range* Cells(int rowIndex,int colIndex){// QAxObject* range = this->querySubObject("Cells(int,int)",rowIndex,colIndex);// return (Excel::Range*)range;//}cell = worksheet->Cells(5,6);cell->SetValue(QString::fromUtf8("手动在类Excel::Worksheet中添加函数Cells(int,int)后，可直接调用"));//测试图表//生成数据Excel::Range* cellX;Excel::Range* cellY;double y;for(int x=0;x<37;x++){y=sin(2*3.141592653/36*x);cellX = worksheet->Cells(x+1,12);cellY = worksheet->Cells(x+1,13);cellX->SetValue(x);cellY->SetValue(y);}Excel::Range *xValues = worksheet->Range("L1:L37");Excel::Range *yValues = worksheet->Range("M1:M37");//先选择一列，则待会儿在Chart中会创建一个Series，否则会创建多个SeriesxValues->Select();//用workbook->Charts()->Add()添加一个图表工作表。由于返回的是IDispatch*类型//所以需用如下方法，转为Excel::Chart*类型。这与前面获取Worksheet*时类似。Excel::Chart *chart = new Excel::Chart(new Excel::_Chart(workbook->Charts()->Add()));chart->SetChartType(Excel::xlXYScatterSmooth);//设置图表类型为带点迹的平滑散点图chart->SetName(QString::fromUtf8("【测试图表】"));//设置表名//获取Series集合。由于chart->SeriesCollection()返回的是IDispatch*类型，//故用如下方法转换为Excel::SeriesCollection*Excel::SeriesCollection *seriesCollection = new Excel::SeriesCollection(chart->SeriesCollection());Excel::Series *series = seriesCollection->Item(1);//获取第1条曲线series->SetName(QString::fromUtf8("正弦"));series->SetXValues(xValues->asVariant());//设置横坐标series->SetValues(yValues->asVariant());//设置对应的纵坐标//在工作表内插入图表//获取图表对象集合。同样，，由于worksheet->ChartObjects()返回的是IDispatch*，//故要用new的方式来转换为Excel::ChartObjects*Excel::ChartObjects *chartObjects = new Excel::ChartObjects(worksheet->ChartObjects());//添加图表，Left=800,Top=10,Height=500,Width=500，单位磅Excel::ChartObject *chartObject = chartObjects->Add(800,10,500,500);Excel::Chart *innerChart = chartObject->Chart();//使用xValues作为数据源，Excel::xlColumns表示每列都为一组数据，即1个SeriesinnerChart->SetSourceData(xValues,Excel::xlColumns);innerChart->SetHasTitle(true);//显示标题innerChart->SetChartType(Excel::xlXYScatterSmoothNoMarkers);//图表为不包含数据点的散点图//获取散点图的系列（Series）集合Excel::SeriesCollection *innerSeriesCollection = new Excel::SeriesCollection(innerChart->SeriesCollection());//选取系列1，因为我只有1个系列，即一条曲线Excel::Series *innerSeries = innerSeriesCollection->Item(1);innerSeries->SetName(QString::fromUtf8("正弦"));//设置系列名innerSeries->SetXValues(xValues->asVariant());//设置横坐标innerSeries->SetValues(yValues->asVariant());//设置对应的纵坐标delete innerSeriesCollection;innerSeriesCollection=NULL;delete chartObjects;chartObjects=NULL;delete seriesCollection;seriesCollection=NULL;delete chart;chart=NULL;//QFileDialog::DontConfirmOverwrite表示如果遇到文件已存在，不提示，直接覆盖QString filepath = QFileDialog::getSaveFileName(this,tr("Save"),".",tr("Microsoft Office 2007 (*.xlsx)\n""Microsoft Office 2003 (*.xls)"),0,QFileDialog::DontConfirmOverwrite);//Excel::xlLocalSessionChanges表示遇到冲突时覆盖,且不提示//SaveAs的详细用法参见(v=office.15).aspxif(QFileInfo(filepath).suffix() == "xls"){workbook->SaveAs(QDir::toNativeSeparators(filepath),Excel::xlExcel8,"","",false,false,Excel::xlNoChange,Excel::xlLocalSessionChanges);}else{workbook->SaveAs(QDir::toNativeSeparators(filepath),Excel::xlOpenXMLWorkbook,"","",false,false,Excel::xlNoChange,Excel::xlLocalSessionChanges);}workbook->Close();excel->Quit();delete worksheet;worksheet=NULL;delete excel;excel=NULL;} 在上例中，我测试了单元格的使用、函数的使用、图表的使用。还添加了Cells(int,int)函数，插入表格背景图片、中文输入无乱码，xls和xlsx两种版本的保存。
偶尔，我一个人站在黄昏的荒野，
