tableWidget的excel文件的数据导入导出

#include "excelengine.h" #include "qt_windows.h" QExcelEngine::QExcelEngine() { 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); } } QExcelEngine::QExcelEngine(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); } } QExcelEngine::~QExcelEngine() { if ( bIsOpen ) { //析构前，先保存数据，然后关闭workbook Close(); } OleUninitialize(); } /** *@brief 打开sXlsFile指定的excel报表 *@return true : 打开成功 * false: 打开失败 */ bool QExcelEngine::Open(int nSheet, bool visible) { if ( bIsOpen ) { Close(); } nCurrSheet = nSheet; bIsVisible = visible; if ( NULL == pExcel ) { pExcel = new QAxObject("Excel.Application"); //连接Excel控件 if ( pExcel ) { bIsValid = true; } else { bIsValid = false; bIsOpen = false; return bIsOpen; } pExcel->dynamicCall("SetVisible(bool)", bIsVisible); //bIsVisible 是否显示窗体 pExcel->setProperty("DisplayAlerts", false); //不显示任何警告信息 pWorkbooks = pExcel->querySubObject("WorkBooks"); //获取工作簿 } 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) { pWorkbook = pWorkbooks->querySubObject("Open(QString, QVariant)", sXlsFile, QVariant(0)); //打开xls对应的工作簿 } else { 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 QExcelEngine::Open(QString xlsFile, int nSheet, bool visible) { sXlsFile = xlsFile; nCurrSheet = nSheet; bIsVisible = visible; return Open(nCurrSheet,bIsVisible); } /** *@brief 保存表格数据，把数据写入文件 */ void QExcelEngine::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); pWorkbook->dynamicCall("SaveAs(const QString&)", sXlsFile); } bIsSaveAlready = true; } } /** *@brief 关闭前先保存数据，然后关闭当前Excel COM对象，并释放内存 */ void QExcelEngine::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: 失败 */ bool QExcelEngine::SaveDataFrTable(QTableWidget *tableWidget) { if ( NULL == tableWidget ) { return false; } if ( !bIsOpen ) { return false; } int tableR = tableWidget->rowCount(); int tableC = tableWidget->columnCount(); //获取表头写做第一行 for (int i=0; i<tableC; i++) { if ( tableWidget->horizontalHeaderItem(i) != NULL ) { this->SetCellData(1, i+1, tableWidget->horizontalHeaderItem(i)->text()); } } //写数据 for (int i=0; i<tableR; i++) { for (int j=0; j<tableC; j++) { if ( tableWidget->item(i,j) != NULL ) { this->SetCellData(i+2, j+1, tableWidget->item(i,j)->text()); } } } //保存 Save(); return true; } /** *@brief 从指定的xls文件中把数据导入到tableWidget中 *@param tableWidget : 执行要导入到的tablewidget指针 *@return 导入成功与否 true : 成功 * false: 失败 */ bool QExcelEngine::ReadDataToTable(QTableWidget *tableWidget) { if ( NULL == tableWidget ) { return false; } //先把table的内容清空 int tableColumn = tableWidget->columnCount(); tableWidget->clear(); for (int n=0; n<tableColumn; n++) { tableWidget->removeColumn(0); } int rowcnt = nStartRow + nRowCount; int columncnt = nStartColumn + nColumnCount; //获取excel中的第一行数据作为表头 QStringList headerList; for (int n = nStartColumn; n<columncnt; n++ ) { QAxObject * cell = pWorksheet->querySubObject("Cells(int,int)", nStartRow, n); if ( cell ) { headerList << cell->dynamicCall("Value2()").toString(); } } //重新创建表头 tableWidget->setColumnCount(nColumnCount);