bool Excel::writeRow(int row, QVariantList &list)
{
try {
char sCell[18];
memset(sCell, 0, 18);
int col = list.count();
if (col <= 26)
sprintf(sCell, " %c", 'A' + col - 1);
else
sprintf(sCell, "%c%c", 'A' + col / 26 - 1, 'A' + col % 26 - 1);
QString cell = sCell;
columnName = columnName.toUpper() > cell.toUpper() ? columnName : cell;
cell = cell.trimmed() + QString::number(row);
QString srange = "Range(\"A" + QString::number(row) + "\",\"" + cell + "\")";
if (!excelSheet)
return false;
QAxObject *range = excelSheet->querySubObject(srange.toLocal8Bit());
if (!range)
return false;
range->dynamicCall("SetValue2(const QVariantList&)", QVariant(list));
delete range;
return true;
} catch (...) {
return false;
}
}
QAxObject * ExcelOnglet::recupererPremiereCelluleVidePremiereColonne()
{
QAxObject * derniereCellulePleinePremiereColonne =
recupererDerniereCelluleNonVidePremiereColonne();
return (QAxObject *)
derniereCellulePleinePremiereColonne->querySubObject( "Offset(int, int)", 1, 0 );
}
QVariantList Excel::getAll(int *rows, int *cols)
{
QVariant result;
char sCell[18];
try {
getRowsCols(rows, cols);
memset(sCell, 0, 18);
if (*cols <= 26)
sprintf(sCell, "%c", 'A' + *cols - 1);
else
sprintf(sCell, "%c%c", 'A' + *cols / 26 - 1, 'A' + *cols % 26 - 1);
QString cell = sCell;
cell = cell.trimmed() + QString::number(*rows);
QString srange = "Range(\"A1\",\"" + cell + "\")";
if (excelSheet) {
QAxObject *range = excelSheet->querySubObject(srange.toLocal8Bit());
if (range) {
result = range->property("Value");
delete range;
}
}
} catch (...) {}
QVariantList list = qVariantValue<QVariantList>(result);
return list;
}
void QEXCEL::mergeCells(const QString& cell)
{
QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
range->setProperty("VerticalAlignment", -4108);//xlCenter
range->setProperty("WrapText", true);
range->setProperty("MergeCells", true);
}
void test2()
{
QAxObject* excel = new QAxObject( "Excel.Application", 0);
excel->setProperty("Visible", true);
excel->dynamicCall("Quit()");
delete excel;
}
/**
* @brief Create Excel File
* @param file [QString] the name of the opened file
* @return 0:success -1:failed
*/
bool QEXCEL::CreateExcel(QString file)
{
QDir dTemp;
if(dTemp.exists(file))
{
qDebug()<<" QExcel::CreateExcel: exist file"<<file;
return false;
}
qDebug()<<" QExcel::CreateExcel: succes";
/**< create new excel sheet file.*/
QAxObject * workSheet = excel->querySubObject("WorkBooks");
workSheet->dynamicCall("Add");
/**< save Excel.*/
QAxObject * workExcel= excel->querySubObject("ActiveWorkBook");
excel->setProperty("DisplayAlerts", 0);
workExcel->dynamicCall("SaveAs (const QString&,int,const QString&,const QString&,bool,bool)",file,56,QString(""),QString(""),false,false);
excel->setProperty("DisplayAlerts", 1);
workExcel->dynamicCall("Close (Boolean)", false);
/**< exit Excel.*/
//excel->dynamicCall("Quit (void)");
return true;
}
void QExcel::insertSheet(QString sheetName)
{
sheets->querySubObject("Add()");
QAxObject * a = sheets->querySubObject("Item(int)", 1);
// qDebug() << sheetName;
a->setProperty("Name", sheetName);
}
void TechnicalReport::writeHeader()
{
QAxObject *range = getRange(QString("A%1:B%1").arg(currentRow));
range->dynamicCall("Merge()");
range->dynamicCall("SetValue(const QVariant&)",
QObject::tr("Категория ") + query->value(1).value<QString>());
currentRow++;
}
int QEXCEL::getUsedRowsCount()
{
QAxObject *usedRange = sheet->querySubObject("UsedRange");
int topRow = usedRange->property("Row").toInt();
QAxObject *rows = usedRange->querySubObject("Rows");
int bottomRow = topRow + rows->property("Count").toInt() - 1;
return bottomRow;
}
QString QExcel::CellValue(int x, int y){
if( x < 1 || y < 1 || _sheet == NULL)
return NULL;
QAxObject* cell = _sheet->querySubObject("Cells(int,int)", y, x);
QString ret = cell->property("Value").toString();
return ret;
}
void QEXCEL::clearCell(int row, int column)
{
QString cell;
cell.append(QChar(column - 1 + 'A'));
cell.append(QString::number(row));
QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
range->dynamicCall("ClearContents()");
}
void QEXCEL::setCellTextCenter(int row, int column)
{
QString cell;
cell.append(QChar(column - 1 + 'A'));
cell.append(QString::number(row));
QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
range->setProperty("HorizontalAlignment", -4108);//xlCenter
}
void TechnicalReport::writeSuperHeader()
{
QAxObject *range = getRange(QString("A%1:B%1").arg(currentRow));
range->dynamicCall("Merge()");
range->dynamicCall("SetValue(const QVariant&)",
QObject::tr("Технические результаты ") + query->value(0).value<QString>() + " " +
query->value(2).value<QDate>().toString("dd.MM.yyyy"));
currentRow++;
}
void PulkaReport::writeRec(uint draw_number)
{
bool right = draw_number > 8;
draw_number = (draw_number - 1) % 8;
uint offset = (currentPage - 1) * pageHeight + firstRecOffset + draw_number * 4;
QAxObject *range = getRange((right ? QString("L%1:M%2") : QString("A%1:B%2")).arg(offset).arg(offset + 1));
range->dynamicCall("SetValue(const QVariant&)",
query->value(3).toString() + " " + query->value(4).toString() + " " + query->value(5).toString());
}
void QEXCEL::setCellTextWrap(int row, int column, bool isWrap)
{
QString cell;
cell.append(QChar(column - 1 + 'A'));
cell.append(QString::number(row));
QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
range->setProperty("WrapText", isWrap);
}
bool ItemsList::generateSchedule()
{
if (!m_actualTable.isEmpty() && mainOrderActive )
{
setAnimationVisible(true);
emit animationVisible(getAnimationVisible());
if( setPath().isEmpty() ) {
runMsg("Nie wybrano lokalizacji.");
setAnimationVisible(false);
emit animationVisible(getAnimationVisible());
return false;
}
QAxObject* excel;
QAxObject* wbooks;
QAxObject* book;
QFileInfo scheduleFile("schedule.xlsm");
QVariant excelPath;
QVariant destPath;
excelPath = QVariant(scheduleFile.absoluteFilePath().replace("/", "\\\\"));
excel = new QAxObject("Excel.Application", this);
excel->setProperty("Visible", false);
excel->setProperty("DisplayAlerts",0);
wbooks = excel->querySubObject("Workbooks");
book = wbooks->querySubObject("Open (const QString&)", excelPath);
destPath = excel->dynamicCall("Run(QVariant)", QVariant("runMacro"));
book->dynamicCall("Close()");
excel->dynamicCall("Quit()");
runMsg("Wygenerowano harmonogram",destPath.toString());
delete book;
delete wbooks;
delete excel;
setAnimationVisible(false);
emit animationVisible(getAnimationVisible());
csvFile->remove();
tableDialog->model->clear();
mainOrderActive = false;
return true;
}
else if(m_actualTable.isEmpty() || !mainOrderActive )
{
runMsg("Zamówienie jest puste.");
return false;
}
return false;
}
void QEXCEL::setCellFontBold(int row, int column, bool isBold)
{
QString cell;
cell.append(QChar(column - 1 + 'A'));
cell.append(QString::number(row));
QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
range = range->querySubObject("Font");
range->setProperty("Bold", isBold);
}
void QEXCEL::setAutoFitRow(int row)
{
QString rowsName;
rowsName.append(QString::number(row));
rowsName.append(":");
rowsName.append(QString::number(row));
QAxObject * rows = sheet->querySubObject("Rows(const QString &)", rowsName);
rows->dynamicCall("AutoFit()");
}
void QEXCEL::setCellFontSize(int row, int column, int size)
{
QString cell;
cell.append(QChar(column - 1 + 'A'));
cell.append(QString::number(row));
QAxObject *range = sheet->querySubObject("Range(const QString&)", cell);
range = range->querySubObject("Font");
range->setProperty("Size", size);
}
QString QExcel::CellValue(int sheetIdx, int x, int y){
if( sheetIdx < 1 || x < 1 || y < 1)
return NULL;
QAxObject* sheet = _sheets->querySubObject( "Item( int )", sheetIdx );
QAxObject* cell = sheet->querySubObject("Cells(int,int)", y, x);
QString ret = cell->property("Value").toString();
return ret;
}
void QEXCEL::setRowHeight(int row, int height)
{
QString rowsName;
rowsName.append(QString::number(row));
rowsName.append(":");
rowsName.append(QString::number(row));
QAxObject * r = sheet->querySubObject("Rows(const QString &)", rowsName);
r->setProperty("RowHeight", height);
}
void QEXCEL::setColumnWidth(int column, int width)
{
QString columnName;
columnName.append(QChar(column - 1 + 'A'));
columnName.append(":");
columnName.append(QChar(column - 1 + 'A'));
QAxObject * col = sheet->querySubObject("Columns(const QString&)", columnName);
col->setProperty("ColumnWidth", width);
}
void TechnicalReport::writeLine()
{
QAxObject *range = getRange(QString("A%1").arg(currentRow)),
*range2 = getRange(QString("B%1").arg(currentRow));
range->dynamicCall("SetValue(const QVariant&)",
QString("%1.").arg(query->value(6).value<uint>()));
range2->dynamicCall("SetValue(const QVariant&)",
QString("%1 (%2 %3)").arg(query->value(3).toString(), query->value(5).toString(),
query->value(4).toString()));
currentRow++;
}
/**
*@brief 获取指定单元格的数据
*@param row : 单元格的行号
*@param column : 单元格的列号
*@return [row,column]单元格对应的数据
*/
QVariant ExcelEngine::getCellData(UINT row, UINT column)
{
QVariant data;
QAxObject *cell = pWorksheet->querySubObject("Cells(int,int)",row,column);//获取单元格对象
if ( cell )
{
data = cell->dynamicCall("Value2()");
}
return data;
}
/**
*@brief 从指定的xls文件中把数据导入到tableWidget中
*@param tableWidget : 执行要导入到的tablewidget指针
*@return 导入成功与否 true : 成功
* false: 失败
*/
bool ExcelEngine::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);
tableWidget->setHorizontalHeaderLabels(headerList);
//插入新数据
for (int i = nStartRow+1, r = 0; i < rowcnt; i++, r++ ) //行
{
tableWidget->insertRow(r); //插入新行
for (int j = nStartColumn, c = 0; j < columncnt; j++, c++ ) //列
{
QAxObject * cell = pWorksheet->querySubObject("Cells(int,int)", i, j );//获取单元格
//在r新行中添加子项数据
if ( cell )
{
tableWidget->setItem(r,c,new QTableWidgetItem(cell->dynamicCall("Value2()").toString()));
}
}
}
return true;
}
QString Excel::sheetName(int index)
{
try {
if (!excelSheets)
return QString();
QAxObject *s = excelSheets->querySubObject("Item(int index)", index);
if (s) {
return s->property("Name").toString();
}
} catch (...) {}
return QString();
}
void DrawingReport::writeLine(uint written)
{
sheet->querySubObject("Range(const QString&)", QString('A') + QString::number(currentRow))
->dynamicCall("SetValue(const QVariant&)", QString::number(written+1));
for(int i = 2, fieldsCount = query->record().count(); i < fieldsCount; ++i)
{
QAxObject *range = sheet->querySubObject("Range(const QString&)",
QString('A'+i-1) + QString::number(currentRow));
range->dynamicCall("SetValue(const QVariant&)",
(i != 3 ? query->value(i).value<QString>() : query->value(i).value<QDate>().toString("dd.MM.yyyy")));
}
currentRow++;
}
QT_BEGIN_NAMESPACE
QAxBase *qax_create_object_wrapper(QObject *object)
{
IDispatch *dispatch = 0;
QAxObject *wrapper = 0;
qAxFactory()->createObjectWrapper(object, &dispatch);
if (dispatch) {
wrapper = new QAxObject(dispatch, object);
wrapper->setObjectName(object->objectName());
dispatch->Release();
}
return wrapper;
}
bool Excel::setFormula(const QString& cell, const QString& szCal)
{
try {
if (!excelSheet)
return false;
QAxObject *range = excelSheet->querySubObject("Range(const QVariant&)", QVariant(cell));
if (!range)
return false;
return range->setProperty("Formula", szCal);
} catch (...) {
return false;
}
}
void ExcelOnglet::ajouterLigne( std::vector< std::string > informations )
{
QAxObject * celluleCourante = recupererPremiereCelluleVidePremiereColonne();
for ( std::vector< std::string >::const_iterator iteInfo = informations.begin();
iteInfo != informations.end();
++iteInfo )
{
celluleCourante->dynamicCall( "SetValue(String)", iteInfo->c_str() );
celluleCourante = offsetCelluleDroite( celluleCourante );
}
//const int idPremiereLigneVide = premiereLigneVide->property( "Row" ).toInt();
//std::cout << "idPremiereLigneVide: " << idPremiereLigneVide << std::endl;
}
