tableview连接数据库后导入导出类.zip

#include "excelengine.h" #include <QStandardItemModel> #include <QMessageBox> #include <QFileDialog> #include <QStandardPaths> #include <QDesktopServices> ExcelEngine::ExcelEngine(QObject *parent) : QObject(parent) { pExcel = NULL; pWorkbooks = NULL; pWorkbook = NULL; pWorksheet = NULL; sXlsFile = ""; nRowCount = 0; nColumnCount = 0; nStartRow = 0; nStartColumn = 0; bIsOpen = false; bIsValid = false; bIsANewFile = false; bIsSaveAlready = false; // HRESULT r = OleInitialize(0); // if (r != S_OK && r != S_FALSE) // { // qDebug("Qt: Could not initialize OLE (error %x)", (unsigned int)r); // } } ExcelEngine::ExcelEngine(QString xlsFile) { pExcel = NULL; pWorkbooks = NULL; pWorkbook = NULL; pWorksheet = NULL; sXlsFile = xlsFile; nRowCount = 0; nColumnCount = 0; nStartRow = 0; nStartColumn = 0; bIsOpen = false; bIsValid = false; bIsANewFile = false; bIsSaveAlready = false; // HRESULT r = OleInitialize(0); // if (r != S_OK && r != S_FALSE) // { // qDebug("Qt: Could not initialize OLE (error %x)", (unsigned int)r); // } } ExcelEngine::~ExcelEngine() { if ( bIsOpen ) { //析构前，先保存数据，然后关闭workbook Close(); } //OleUninitialize(); } /** *@brief 打开sXlsFile指定的excel报表 *@return true : 打开成功 * false: 打开失败 */ bool ExcelEngine::Open(UINT nSheet, bool visible) { if(bIsOpen) { Close(); } nCurrSheet = nSheet; bIsVisible = visible; if(NULL == pExcel) { pExcel = new QAxObject("Excel.Application"); if(pExcel) { bIsValid = true; } else { bIsValid = false; bIsOpen = false; return bIsOpen; } pExcel->dynamicCall("SetVisible(bool)", bIsVisible); } if(!bIsValid) { bIsOpen = false; return bIsOpen; } if(sXlsFile.isEmpty()) { bIsOpen = false; return bIsOpen; } /*如果指向的文件不存在，则需要新建一个*/ QFile f(sXlsFile); if(!f.exists()) { bIsANewFile = true; } else { bIsANewFile = false; } if(!bIsANewFile) { pWorkbooks = pExcel->querySubObject("WorkBooks"); //获取工作簿 pWorkbook = pWorkbooks->querySubObject("Open(QString, QVariant)",sXlsFile,QVariant(0)); //打开xls对应的工作簿 } else { pWorkbooks = pExcel->querySubObject("WorkBooks"); //获取工作簿 pWorkbooks->dynamicCall("Add"); //添加一个新的工作薄 pWorkbook = pExcel->querySubObject("ActiveWorkBook"); //新建一个xls } pWorksheet = pWorkbook->querySubObject("WorkSheets(int)", nCurrSheet);//打开第一个sheet //至此已打开，开始获取相应属性 QAxObject *usedrange = pWorksheet->querySubObject("UsedRange"); //获取该sheet的使用范围对象 QAxObject *rows = usedrange->querySubObject("Rows"); QAxObject *columns = usedrange->querySubObject("Columns"); //因为excel可以从任意行列填数据而不一定是从0,0开始，因此要获取首行列下标 nStartRow = usedrange->property("Row").toInt(); //第一行的起始位置 nStartColumn = usedrange->property("Column").toInt(); //第一列的起始位置 nRowCount = rows->property("Count").toInt(); //获取行数 nColumnCount = columns->property("Count").toInt(); //获取列数 bIsOpen = true; return bIsOpen; } /** *@brief Open()的重载函数 */ bool ExcelEngine::Open(QString xlsFile, UINT nSheet, bool visible) { sXlsFile = xlsFile; nCurrSheet = nSheet; bIsVisible = visible; return Open(nCurrSheet,bIsVisible); } /** *@brief 保存表格数据，把数据写入文件 */ void ExcelEngine::Save() { if(pWorkbook) { if(bIsSaveAlready) { return ; } if(!bIsANewFile) { pWorkbook->dynamicCall("Save()"); } else /*如果该文档是新建出来的，则使用另存为COM接口*/ { pWorkbook->dynamicCall("SaveAs (const QString&,int,const QString&,const QString&,bool,bool)", sXlsFile,56,QString(""),QString(""),false,false); } bIsSaveAlready = true; } } /** *@brief 关闭前先保存数据，然后关闭当前Excel COM对象，并释放内存 */ void ExcelEngine::Close() { Save();//关闭前先保存数据 if(pExcel && pWorkbook) { pWorkbook->dynamicCall("Close(bool)", true); pExcel->dynamicCall("Quit()"); delete pExcel; pExcel = NULL; bIsOpen = false; bIsValid = false; bIsANewFile = false; bIsSaveAlready = true; } } /** *@brief 把tableWidget中的数据保存到excel中 *@param tableWidget : 指向GUI中的tablewidget指针 *@return 保存成功与否 true : 成功 * false: 失败 */ //第一个参数是表格控件。第二个参数是在选择tableview时候的导出表的标题 bool ExcelEngine::SaveDataFrTable(/*QTableWidget *tableWidget,*/QTableView *tableview,QString title) { // /*第一种方法,暂时在tablewidget中可以实现*/ // QString fileName = QFileDialog::getSaveFileName(tableview, "保存",QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation),"Excel 文件(*.xls *.xlsx)"); // if(fileName != NULL); // { // QStandardItemModel *model = new QStandardItemModel(); // tableview->setModel(model); // if(NULL == tableview) // { // return false; // } // if(!bIsOpen) // { // return false; // } // // int tableR = tableWidget->rowCount(); // // int tableC = tableWidget->columnCount(); // int tableR = model->rowCount(); // int tableC = model->columnCount(); // //获取表头写做第一行 // for(int i=0;i<tableC;i++) // { // //if(tableWidget->horizontalHeaderItem(i) != NULL) // if(model->horizontalHeaderItem(i) != NULL) // { // //this->SetCellData(1,i+1,tableWidget->horizontalHeaderItem(i)->text()); // this->SetCellData(1,i+1,model->horizontalHeaderItem(i)->text()); // } // } // //写数据 // for(int i=0;i<tableR;i++) // { // for(int j=0;j<tableC;j++) // { // //if(tableWidget->item(i,j) != NULL) // if(model->item(i,j) != NULL) // { // //this->SetCellData(i+2,j+1,tableWidget->item(i,j)->text()); // this->SetCellData(i+2,j+1,model->item(i,j)->text()); // } // } // } // //保存 // Save(); // return true; // } /*第二种方法*/ QString fileName = QFileDialog::getSaveFileName(tableview, "保存",QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation),"Excel 文件(*.xls *.xlsx)"); if (fileName!="") { QAxObject *excel = new QAxObject; if (excel->setControl("Excel.Application")) //连接Excel控件 { excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体 excel->setProperty("DisplayAlerts", false);//不显示任何警告信息。如果为true那么在关闭是会出现类似“�