#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那么在关闭是会出现类似“�
