#include "excelhelper.h"
ExcelHelper::ExcelHelper(QObject *parent)
: QObject(parent)
{
book = xlCreateXMLBook();
book->setKey( L"Halil Kural", L"windows-2723210a07c4e90162b26966a8jcdboe" );
xlsxfilename = "";
sheetNum = 0;
}
ExcelHelper::~ExcelHelper()
{
}
/*********************************************************************
* public函数,供外部调用
*********************************************************************/
/*************************************
* 新建并存储一个.xlsx文件
*************************************/
void ExcelHelper::NewXlsxFile(const QString filename,const QString sheetname)
{
if(!filename.isEmpty()){
xlsxfilename = filename;
}else{
qDebug()<<("文件名为空!");
}
if(!sheetname.isEmpty()){
addNewSheet(sheetname);
SetCurrenSheet(sheetname,-1);
}else{
qDebug()<<("sheet为空!");
}
// setCellValue(0,0,"fuck");
// FinishWriteXlsxFile();
}
bool ExcelHelper::AddSheetToXlsx(const QString sheetname)
{
return addNewSheet(sheetname);
}
QStringList ExcelHelper::ReturnSheeets()
{
return GetAllSheetsName();
}
void ExcelHelper::AddValue(int row,int column,QString value)
{
setCellValue(row,column,value);
}
bool ExcelHelper::FinishWriteXlsxFile()
{
return finishWriteXlsxFile();
}
/*************************************
* 打开一个.xlsx文件读取数据
*************************************/
bool ExcelHelper::OpenXlsxFile(const QString filename)
{
return openExcel(filename);
}
QString ExcelHelper::RetrunValue(int key,int row,int column)
{
return getValue(key,row,column);
}
QStringList ExcelHelper::ReturnAllValueOfSheet(int key)
{
return getAllValueOfSheet(key);
}
QList<QStringList> ExcelHelper::ReturnAllValue()
{
return getAllValue();
}
void ExcelHelper::CloseXlsxFile()
{
closeExcel();
}
/*************************************
* 操作&配置一个.xlsx文件
*************************************/
void ExcelHelper::SetCurrenSheet(QString sheetname,int key)
{
if(sheetname.compare("")!=0){
setCurrentSheet(sheetname);
}
if(key!=-1){
setCurrentSheet(key);
}
}
/*********************************************************************
* protected函数
*********************************************************************/
bool ExcelHelper::addNewSheet(const QString &sheetname)
{
QString tmp = sheetname;
qDebug()<<"get sheet name:"<<sheetname<<tmp;
bool ishaving = false;
if(!sheets.isEmpty()){
foreach(QString s,sheets){
if(s.compare(tmp)==0){
ishaving = true;
break;
}
}
}
if(!ishaving){
bool isOK = book->addSheet(reinterpret_cast<const wchar_t *>(sheetname.utf16()));
if(isOK){
sheets.append(sheetname);
sheetNum = book->sheetCount();
}else
qDebug()<<"add sheet failt!";
}
return !ishaving;
}
QStringList ExcelHelper::GetAllSheetsName()
{
QStringList sheetsname;
// int count = book->sheetCount();
// for(int i=0;i<count;i++){
// QString name = book->getSheetName(i);
// sheetsname<<name;
// }
foreach(QString str,sheets){
sheetsname<<str;
}
return sheetsname;
}
void ExcelHelper::setCellValue(int row,int column,const QString &value)
{
currensheet->writeStr(row,column,reinterpret_cast<const wchar_t *>(value.utf16()));
}
bool ExcelHelper::finishWriteXlsxFile()
{
bool isOK = book->save(reinterpret_cast<const wchar_t *>(xlsxfilename.utf16()));
if(isOK) book->release();
else qDebug()<<"save .xlsx file failt";
return isOK;
}
bool ExcelHelper::openExcel(const QString &filename)
{
bool isopen = book->load(reinterpret_cast<const wchar_t *>(filename.utf16()));
if(isopen) qDebug()<<("open .xlsx file success!");
else qDebug()<<("open .xlsx file failt!");
sheetNum = book->sheetCount();
qDebug()<<".xlsx file sheet is:"<<sheetNum;
return isopen;
}
QString ExcelHelper::getValue(int key,int row,int column)
{
QString values;
if(!setCurrentSheet(key)){
return values;
}
if(currensheet->lastRow()>0){
int rowcount = currensheet->lastRow();
int colcount = currensheet->lastCol();
if((row<rowcount&&row>=0)&&(column<colcount&&column>=0)){
values = QString::fromWCharArray(currensheet->readStr(row,column));
}else{
qDebug()<<"input row & column is error!";
}
qDebug()<<"getValue's data is:"<<values;
}else{
qDebug()<<"currentsheet is empty!";
}
return values;
}
QStringList ExcelHelper::getAllValueOfSheet(int key)
{
QStringList values;
if(!setCurrentSheet(key)){
return values;
}
qDebug()<<"sheet's first row is:"<<currensheet->lastRow();
if(currensheet->lastRow()>0){
int rowcount = currensheet->lastRow();
int colcount = currensheet->lastCol();
for(int i=0;i<rowcount;i++){
QString rowvalue = "";
for(int j=0;j<colcount;j++){
QString str = QString::fromWCharArray(currensheet->readStr(i,j));
rowvalue = rowvalue+str+"$";
}
values<<rowvalue;
}
qDebug()<<"getAllValueOfSheet's data is:\n"<<values;
}else{
qDebug()<<"this sheet is empty!";
}
return values;
}
QList<QStringList> ExcelHelper::getAllValue()
{
QList<QStringList> values;
int sheetcount = book->sheetCount();
if(sheetcount>0){
for(int x=0;x<sheetcount;x++){
QStringList tmp;
Sheet *tmpsheet = book->getSheet(x);
if(tmpsheet->lastRow()>0){
int rowcount = tmpsheet->lastRow();
int colcount = tmpsheet->lastCol();
for(int i=0;i<rowcount;i++){
QString rowvalue = "";
for(int j=0;j<colcount;j++){
QString str = QString::fromWCharArray(tmpsheet->readStr(i,j));
rowvalue = rowvalue+str+"$";
}
tmp<<rowvalue;
}
// qDebug()<<"sheet's data is:\n"<<values;
}else{
tmp<<"";
qDebug()<<"sheet["<<x<<"] is empty!";
}
values<<tmp;
}
qDebug()<<"getAllValue's data is:\n"<<values;
}else{
qDebug()<<"table["<<xlsxfilename<<"] is empty!";
}
return values;
}
void ExcelHelper::closeExcel()
{
book->release();
}
bool ExcelHelper::setCurrentSheet(QString &sheetname)
{
bool isOK = false;
QString tmp = sheetname;
qDebug()<<"get sheet name:"<<sheetname<<tmp;
bool ishaving = false;int index = 0;
if(!sheets.isEmpty()){
foreach(QString s,sheets){
if(s.compare(tmp)==0){
ishaving = true;
break;
}
index++;
}
}
if(ishaving){
currensheet = book->getSheet(index);
isOK = true;
}else{
qDebug()<<"no sheet name is:"<<sheetname;
}
return isOK;
}
bool ExcelHelper::setCurrentSheet(int index)
{
bool isOK = false;
if(index<=sheetNum){
currensheet = book->getSheet(index);
isOK = true;
}else{
qDebug()<<".xlsx file no have this sheet";
}
return isOK;
}
- 1
- 2
前往页