qt tablewidget 导出excel

1 #include <QTableWidget>
2 #include <QFileDialog>
3 #include <QDesktopServices>
4 #include <QMessageBox>
5 #include <QAxObject>

 1 //第一个参数是页面上的表格，第二个是导出文件的表头数据
 2 
 3 void FaJianDialog::Table2ExcelByHtml(QTableWidget *table,QString title)
 4 {
 5     QString fileName = QFileDialog::getSaveFileName(table, "保存",QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation),"Excel 文件(*.xls *.xlsx)");
 6     if (fileName!="")
 7     {
 8         QAxObject *excel = new QAxObject;
 9         if (excel->setControl("Excel.Application")) //连接Excel控件
10         {
11             excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
12             excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
13             QAxObject *workbooks = excel->querySubObject("WorkBooks");//获取工作簿集合
14             workbooks->dynamicCall("Add");//新建一个工作簿
15             QAxObject *workbook = excel->querySubObject("ActiveWorkBook");//获取当前工作簿
16             QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1);
17             int i,j,colcount=table->columnCount();
18             QAxObject *cell,*col;
19             //标题行
20             cell=worksheet->querySubObject("Cells(int,int)", 1, 1);
21             cell->dynamicCall("SetValue(const QString&)", title);
22             cell->querySubObject("Font")->setProperty("Size", 18);
23             //调整行高
24             worksheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 30);
25             //合并标题行
26             QString cellTitle;
27             cellTitle.append("A1:");
28             cellTitle.append(QChar(colcount - 1 + ‘A‘));
29             cellTitle.append(QString::number(1));
30             QAxObject *range = worksheet->querySubObject("Range(const QString&)", cellTitle);
31             range->setProperty("WrapText", true);
32             range->setProperty("MergeCells", true);
33             range->setProperty("HorizontalAlignment", -4108);//xlCenter
34             range->setProperty("VerticalAlignment", -4108);//xlCenter
35             //列标题
36             for(i=0;i<colcount;i++)
37             {
38                 QString columnName;
39                 columnName.append(QChar(i  + ‘A‘));
40                 columnName.append(":");
41                 columnName.append(QChar(i + ‘A‘));
42                 col = worksheet->querySubObject("Columns(const QString&)", columnName);
43                 col->setProperty("ColumnWidth", table->columnWidth(i)/6);
44                 cell=worksheet->querySubObject("Cells(int,int)", 2, i+1);
45                 columnName=table->horizontalHeaderItem(i)->text();
46                 cell->dynamicCall("SetValue(const QString&)", columnName);
47                 cell->querySubObject("Font")->setProperty("Bold", true);
48                 cell->querySubObject("Interior")->setProperty("Color",QColor(191, 191, 191));
49                 cell->setProperty("HorizontalAlignment", -4108);//xlCenter
50                 cell->setProperty("VerticalAlignment", -4108);//xlCenter
51             }
52             //数据区
53             for(i=0;i<table->rowCount();i++){
54                 for (j=0;j<colcount;j++)
55                 {
56                     worksheet->querySubObject("Cells(int,int)", i+3, j+1)->dynamicCall("SetValue(const QString&)", table->item(i,j)?table->item(i,j)->text():"");
57                 }
58             }
59             //画框线
60             QString lrange;
61             lrange.append("A2:");
62             lrange.append(colcount - 1 + ‘A‘);
63             lrange.append(QString::number(table->rowCount() + 2));
64             range = worksheet->querySubObject("Range(const QString&)", lrange);
65             range->querySubObject("Borders")->setProperty("LineStyle", QString::number(1));
66             range->querySubObject("Borders")->setProperty("Color", QColor(0, 0, 0));
67             //调整数据区行高
68             QString rowsName;
69             rowsName.append("2:");
70             rowsName.append(QString::number(table->rowCount() + 2));
71             range = worksheet->querySubObject("Range(const QString&)", rowsName);
72             range->setProperty("RowHeight", 20);
73             workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(fileName));//保存至fileName
74             workbook->dynamicCall("Close()");//关闭工作簿
75             excel->dynamicCall("Quit()");//关闭excel
76             delete excel;
77             excel=NULL;
78             if (QMessageBox::question(NULL,"完成","文件已经导出，是否现在打开？",QMessageBox::Yes|QMessageBox::No)==QMessageBox::Yes)
79             {
80                 QDesktopServices::openUrl(QUrl("file:///" + QDir::toNativeSeparators(fileName)));
81             }
82         }
83         else
84         {
85             QMessageBox::warning(NULL,"错误","未能创建 Excel 对象，请安装 Microsoft Excel。",QMessageBox::Apply);
86         }
87     }
88 }