#include "tfv_excel_file_process.h"
#include <QMessageBox>
#include <QDebug>
#include <QCoreApplication>
#include <QDateTime>
//! 形如:"y = -9.113ln(x) + 78.016"
//! 则 a=-9.113 b=78.016
void getFormulaCoeffcient(QString strFormula, double &a, double &b)
{
int posEqual = strFormula.indexOf('='); //2
int posLn = strFormula.indexOf('l'); //ln 10
QString strA = strFormula.mid(posEqual+1,posLn-posEqual-1);
if (strA == " ") a =1;
else a = strA.toDouble();
int posSign = strFormula.indexOf('+'); //16
if (posSign == -1) posSign = strFormula.indexOf('-');
if (-1 == posSign) b = 0;
else
{
QString strB = strFormula.mid(posSign+1);
b = strB.toDouble();
}
}
TFVExcelFileProcess::TFVExcelFileProcess(const QString &fileName, QAxObject *excelServer)
: Excel(fileName, excelServer)
{
countEachRow(); ///< 统计一行的有效数据个数
sortDataEachCol(); ///< 每列排序后的数据集合(不含空白)
// QAxObject *range = worksheet->querySubObject("Range(QVariant, QVariant)", "A3");
// QVariant re = range->dynamicCall("Formula", "=C7+D7");
// qDebug() <<re;
}
TFVExcelFileProcess::~TFVExcelFileProcess()
{
}
void TFVExcelFileProcess::sortDataEachCol()
{
int colEnd = usedColsCount();
for (int i=2; i<=colEnd; i++) // 从第二列开始遍历每一列
{
QVariantList colData = usedColData(i);
TypDataEachCol oneCol;
for (int j=1; j<colData.size(); j++) // 遍历一列
{
if (colData.at(j).isValid()) oneCol.push_back(colData.at(j).toInt());
else continue; // 空白不计入
} // for (int j=2; j<=colData.size(); j++) // 遍历一列
std::sort(oneCol.begin(), oneCol.end());
vecSortDataEachCol.push_back(oneCol);
} // for (int i=2; i<=colEnd; i++) // 从第二列开始遍历每一列
}
void TFVExcelFileProcess::countEachRow()
{
int rowEnd = usedRowsCount();
for (int i=2; i<=rowEnd; i++) // 从第二行开始统计
{
QVariantList rowData = usedRowData(i);
int size = rowData.size();
int j = size-1;
for (; j>0; j--)
{
if (rowData.at(j).isValid()) break;
}
vecCountEachRow.push_back(j);
std::sort(vecCountEachRow.begin(), vecCountEachRow.end());
// // 统计100列,从后往前,碰到非空的就认为是一行的末尾,这样就可以将前面的空白计入
// int j;
// for ( j=100; j>=1; j--)
// {
// if (!cellData(i,j).isValid()) continue;
// else break;
// } // for (int j=100; j>=1; j--)
// vecCountEachRow.push_back(j-1);
} // for (int i=2; i<=rowEnd; i++) // 从第二行开始统计
}
std::vector<int> TFVExcelFileProcess::getColFenwei(double percentage)
{
_colFenwei.clear();
for (int i=0; i<vecSortDataEachCol.size(); i++)
{
if (vecSortDataEachCol.at(i).size() == 0) break;
int fenwei = vecSortDataEachCol.at(i).at( vecSortDataEachCol.at(i).size()*percentage );
_colFenwei.push_back(fenwei);
}
return _colFenwei;
}
int TFVExcelFileProcess::getRowFenwei(double percentage)
{
_rowFenwei = vecCountEachRow.at(vecCountEachRow.size()*percentage);
QString str;
for (int i=0; i<vecCountEachRow.size(); i++)
str += (QString::number(vecCountEachRow.at(i)) +" ");
qDebug() <<"Each Row Count: "<<str;
return _rowFenwei;
}
void TFVExcelFileProcess::formula(double percentage)
{
//getColFenwei(percentage);
///////////////////////////////////////
QAxObject *shapes = _curWorkSheet->querySubObject("Shapes");
QAxObject *emptyCell = _curWorkSheet->querySubObject("Cells(int,int)", 1, usedColsCount()+3 );
// 先选中一个空白的地方,不然后面的SeriesCollection会先将所有行都做个散点图。。。
emptyCell->dynamicCall("Select(void)");
shapes->dynamicCall("AddChart(int)",-4169,100,100,500,800); // height width
// 再将视野拖回到左上角区域
QAxObject *range = _curWorkSheet->querySubObject("Range(QVariant)","$B$1:$D$1");
range->dynamicCall("Select(void)");
int nbChart = shapes->property("Count").toInt();
qDebug() << "chart size :" <<nbChart;
QAxObject *shape = shapes->querySubObject("Range(int)",nbChart);
qDebug() << "Name" <<shape->property("Name").toString(); // 获取Name属性
qDebug() << "Title" <<shape->property("Title").toString(); // 获取Title属性
//shape->dynamicCall("ScaleWidth(QVariant,QVariant)",2,0);
shape->dynamicCall("Select(void)");
QAxObject *chart = _curWorkbook->querySubObject("ActiveChart");
QAxObject *seriesCollection = chart->querySubObject("SeriesCollection()");
seriesCollection->dynamicCall("NewSeries (void)");
int nb = seriesCollection->property("Count").toInt();
QAxObject *series = chart->querySubObject("SeriesCollection(int)",nb);
series->setProperty("Name","SeriesNo1");
// 选中部分单元格
//QAxObject *range = _curWorkSheet->querySubObject("Range(QVariant,QVariant)",coordInt2String(1,2),coordInt2String(1,9));
//QAxObject *range = _curWorkSheet->querySubObject("Range(QVariant)","$B$1:$Q$1");
//range->dynamicCall("Select(void)");
//QAxObject * xvalues = worksheet->querySubObject("Range(A2:A9)");
//QAxObject * yvalues = worksheet->querySubObject("Range(B2:B9)");
//serie->setProperty("XValues", xvalues->asVariant());
//serie->setProperty("Values", yvalues->asVariant());
// 给该系列添加源数据
// 这里我的数据来自其他部分,非Excel文件,
// 想使用Excel文件数据可采用上面的方法,先选中数据单元格,然后传递过去
QList<QVariant> listXValues, listValues;
for (int i=1; i<_colFenwei.size(); i++)
{
listXValues.push_back(i+1);
listValues.push_back(_colFenwei.at(i));
}
series->setProperty("XValues",listXValues);
series->setProperty("Values",listValues);
series->dynamicCall("Select(void)");
// 添加趋势线
QAxObject *trendLines = series->querySubObject("Trendlines()");
if (trendLines) {
trendLines->dynamicCall("Add()");
int nbTrendLines = trendLines->property("Count").toInt();
QAxObject *trendLine = series->querySubObject("Trendlines(int)",nbTrendLines);
// 设定图标格式为 “散点图”,数字-4133来自 枚举变量 XlTrendlineType : xlLogarithmic
trendLine->setProperty("Type",-4133); // XlTrendlineType : xlLogarithmic
trendLine->setProperty("Name","TrendLineName");
trendLine->setProperty("DisplayEquation",true);
//trendLine->setProperty("DisplayRSquared",true);
QAxObject *dataLabel = trendLine->querySubObject("DataLabel");
QString strFormula = dataLabel->property("Formula").toString();
qDebug() << strFormula; // "y = -9.113ln(x) + 78.016"
getFormulaCoeffcient(strFormula,_cofA, _cofB);
qDebug() << _cofA;
qDebug() << _cofB;
}
// 保存文件
/*
QString qstrFileName = QCoreApplication::applicationDirPath().append("/FCQ_");
qstrFileName.append(QDateTime::currentDateTime().toString("yyyyMMddHHmmss"));
qstrFileName.replace("/", "\\");
//qDebug() << qstrFileName;
//保存文件并退出
//_curWorkbook->dynamicCall("SaveCopyAs(QString)",qstrFileName);
_curWorkbook->dynamicCall("SaveAs(QString)",qstrFileName);
*/
}
- 1
- 2
- 3
前往页