void MainWindow::on_btnExport_clicked()
{
QDir dir(QDir::currentPath());
QDate date;
QSettings config(dir.currentPath()+"//app.ini",QSettings::IniFormat);
QString path = config.value("/SETTINGS/path").toString();
QString fileName = QFileDialog::getSaveFileName(this,tr("保存文件"),path + "/" + date.currentDate().toString("yyyy-MM-dd")+ui->cbWareH->currentText()+".xls",tr("Microsoft Office 2003(*.xls)"));
if(fileName.isEmpty()){
QMessageBox::critical(0,tr("错误"),tr("要保存的文件名为空"));
return;
}
QSqlQuery query;
query.exec(QString("execute dbo.pr_report_jxc_item;1 @as_from = '%1', @as_to = '%2', @as_branch = '%3', @as_itemcls = '%'")
.arg(date.currentDate().toString("yyyy-MM-dd"))
.arg(date.currentDate().toString("yyyy-MM-dd"))
.arg(ui->cbWareH->currentData().toString()));
QAxObject *excel = new QAxObject("Excel.Application");
if(!excel){
QMessageBox::critical(0,tr("错误"),tr("找不到Excel组件！"));
delete excel;
return;
}
excel->dynamicCall("SetVisible(bool)",false);
excel->setProperty("Visible", false);
excel->setProperty("DisplayAlerts", false);
QAxObject *workbooks = excel->querySubObject("WorkBooks");
workbooks->dynamicCall("Add");
QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
QAxObject *worksheets = workbook->querySubObject("Sheets");
QAxObject *worksheet = worksheets->querySubObject("Item(int)",1);
int row = 1;
while(query.next()){
setCellValue(worksheet,row,"A","'"+query.value("cItem_C").toString());
setCellValue(worksheet,row,"B","'"+query.value("cItem_N").toString());
setCellValue(worksheet,row,"C","0");
setCellValue(worksheet,row,"D",query.value("nSettleQty"));
row ++;
};
workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(fileName));
config.setValue("/SETTINGS/path",fileName.left(fileName.lastIndexOf("/")));
QMessageBox::information(this,tr("操作成功"),tr("保存成功！"));
workbook->dynamicCall("Close()");
worksheet->clear();
excel->dynamicCall("Quit()");
delete excel;
}
bool MainWindow::writeSalary()
{
QString tagFilePath = ui->lineEdit_3->text();
if(tagFilePath.isEmpty() == true)
{
ui->textEdit->append(tr("2 zhaobudao"));
return false;
}
//Excel应用程序包括一系列的workbooks，每个workbook又由多个sheets组成。
QString nameStart = ui->nameEdit2->text();
QString salaryStart = ui->salaryEdit2->text();
if(nameStart.right(1) != salaryStart.right(1))
{
ui->textEdit->append(tr("qishi weizhi buyiyang"));
return false;
}
QString nameChar = nameStart.left(1);
QString salaryChar = salaryStart.left(1);
bool isOk = false;
int index = nameStart.right(1).toInt(&isOk);
if(isOk == false)
{
ui->textEdit->append(tr("youwenti"));
return false;
}
QAxObject* excel = new QAxObject( "Excel.Application" );
excel->dynamicCall( "SetVisible(bool)", false );
QAxObject *workbooks = excel->querySubObject( "Workbooks" ); //得到Workbooks集合的指针
//QAxObject *workbookSrc = workbooks->querySubObject( "Open(const QString&)", "F:\\QExcelConvert\\test.xls" );
QAxObject *workbookSrc = workbooks->querySubObject( "Open(const QString&)", tagFilePath );
QAxObject *worksheet = workbookSrc->querySubObject("Worksheets(int)", ui->spinBox2->value());
QMap<QString, int> mapHelp;
int totalCount = m_map.count();
bool result = true;
while(1)
{
QString namePos = QString("Range(%1%2)").arg(nameChar).arg(index);
QAxObject* nameAx = worksheet->querySubObject(namePos.toStdString().c_str());
QString name = nameAx->property("Value").toString().trimmed();
QString salaryPos = QString("Range(%1%2)").arg(salaryChar).arg(index);
QAxObject* salaryAx = worksheet->querySubObject(salaryPos.toStdString().c_str());
if(name.isEmpty() == true)
{
break;
}
if(mapHelp.contains(name) == true)
{
result = false;
ui->textEdit->append(QString("2 mizichongfu %1").arg(mapHelp.value(name)));
break;
}
if( m_map.contains(name) )
{
salaryAx->setProperty("Value", m_map.value(name));
m_map.remove(name);
ui->textEdit->append(QString("%1 %2 %3 OK").arg(index).arg(name).arg(m_map.value(name)));
}
else
{
salaryAx->clear();
}
mapHelp.insert(name, index);
index++;
}
if(result == true)
{
int nowCount = m_map.count();
ui->textEdit->append(QString("wancheng %1/%2").arg(totalCount - nowCount).arg(totalCount));
if(nowCount > 0)
{
QList<QString> keys = m_map.keys();
for(int i = 0; i < nowCount; ++i)
{
int j = index + 5 + i;
QString namePos = QString("Range(%1%2)").arg(nameChar).arg(j);
QAxObject* nameAx = worksheet->querySubObject(namePos.toStdString().c_str());
nameAx->setProperty("Value", keys.at(i));
QString salaryPos = QString("Range(%1%2)").arg(salaryChar).arg(j);
QAxObject* salaryAx = worksheet->querySubObject(salaryPos.toStdString().c_str());
salaryAx->setProperty("Value", m_map.value(keys.at(i)));
}
}
}
workbookSrc->dynamicCall("Save (void)");
workbookSrc->dynamicCall("Close (Boolean)", false);
excel->dynamicCall("Quit (void)");
delete excel;
return result;
}
