示例代码(将数据从数据库导入到Excel,将数据从Excel导入到数据库):
void slotExportBtn(){
QAxObject *excel = new QAxObject("Excel.Application");
if(excel->isNull()){
if(excel != NULL){
excel->dynamicCall("Quit()");
delete excel;
}
QMessageBox::critical(0, tr("error"), tr("Excel Application is not Exist"));
return ;
}
QAxObject *workbooks = NULL;
QAxObject *workbook = NULL;
QAxObject *worksheets = NULL;
QAxObject *worksheet = NULL;
excel->dynamicCall("SetVisible(bool)", false);
excel->setProperty("DisplayAlerts", false);
workbooks = excel->querySubObject("WorkBooks");
if(QFile::exists(fileName)){
workbook = workbooks->querySubObject("Open(const QString &)", fileName);
}else{
workbooks->dynamicCall("Add");
workbook = excel->querySubObject("ActiveWorkBook");
}
worksheets = workbook->querySubObject("Sheets");
worksheet = worksheets->querySubObject("Item(int)", 1);
QAxObject * usedrange = worksheet->querySubObject("UsedRange");
// QAxObject * rows = usedrange->querySubObject("Rows");
// QAxObject * columns = usedrange->querySubObject("Columns");
int intRowStart = usedrange->property("Row").toInt();
int intColStart = usedrange->property("Column").toInt();
// int intCols = columns->property("Count").toInt();
// int intRows = rows->property("Count").toInt();
QAxObject *cell = NULL;
QString str;
int rowCount = model->rowCount();
int colCount = model->columnCount();
// // 清空数据
// for(int i=intRowStart;i <intRowStart + intRows;i++)
// {
// for(int j=intColStart ;j<intColStart+intCols;j++)
// {
// QString a;
// switch (j) {
// case 1: a = "A" + QString::number(i);
// break;
// case 2: a = "B" + QString::number(i);
// break;
// case 3: a = "C" + QString::number(i);
// break;
// case 4: a = "D" + QString::number(i);
// break;
// }
// str = " ";
// cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
// cell->dynamicCall("SetValue(const QVariant&)", str);
// }
// }
//标题行
// QAxObject *cell;
cell=worksheet->querySubObject("Cells(int,int)", 1, 1);
cell->dynamicCall("SetValue(const QString&)", "地址表");
cell->querySubObject("Font")->setProperty("Size", 11);
//合并标题行
QString cellTitle;
cellTitle.append("A1:");
cellTitle.append(QChar(3 + 'A'));
cellTitle.append(QString::number(1));
QAxObject *range = worksheet->querySubObject("Range(const QString&)", cellTitle);
range->setProperty("WrapText", true);
range->setProperty("MergeCells", true);
range->setProperty("HorizontalAlignment", -4108);//xlCenter
range->setProperty("VerticalAlignment", -4108);//xlCenter
QString a;
a = "A2";
cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
cell->dynamicCall("SetValue(const QVariant&)", "id");
a = "B2";
cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
cell->dynamicCall("SetValue(const QVariant&)", "address");
a = "C2";
cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
cell->dynamicCall("SetValue(const QVariant&)", "warnmessage");
a = "D2";
cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
cell->dynamicCall("SetValue(const QVariant&)", "code");
// 插入数据
for (int i = intRowStart; i < intRowStart + rowCount; i++)
{
for(int j = intColStart; j < intColStart + colCount; j++)
{
QString a;
switch (j) {
case 1: a = "A" + QString::number(i+2);
break;
case 2: a = "B" + QString::number(i+2);
break;
case 3: a = "C" + QString::number(i+2);
break;
case 4: a = "D" + QString::number(i+2);
break;
}
str = model->record(i-1).value(j-1).toString();
cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
cell->dynamicCall("SetValue(const QVariant&)", str);
}
}
// QString a = "A1";
// cell = worksheet->querySubObject("Range(QVariant, QVariant)",a);
// cell->dynamicCall("SetValue(const QVariant&)", "123456");
// worksheet->dynamicCall(pp.toUtf8());
workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(fileName));//保存至filepath,注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\",不然一定保存不了。
workbook->dynamicCall("Close()");//关闭工作簿
excel->dynamicCall("Quit()");//关闭excel
delete workbook;
delete workbooks;
delete excel;
QMessageBox::information(0, tr("success"), tr("1111"));
}
void slotImportBtn(){
QString strFile = QFileDialog::getOpenFileName(this,QStringLiteral("选择Excel文件"),"",tr("Exel file(*.xls *.xlsx)"));
if (strFile.isEmpty()){
return;
}
QVector<QString> arr; //存储每一行数据
QVector<QVector<QString>> array; //将每行数据汇总,类似于二维数组,但不需要手动定义空间大小,造成内存浪费
QAxObject excel("Excel.Application"); //加载Excel驱动
excel.setProperty("Visible", false);//不显示Excel界面,如果为true会看到启动的Excel界面
QAxObject *work_books = excel.querySubObject("WorkBooks");
work_books->dynamicCall("Open (const QString&)", strFile); //打开指定文件
QAxObject *work_book = excel.querySubObject("ActiveWorkBook");
QAxObject *work_sheets = work_book->querySubObject("Sheets"); //获取工作表
QString ExcelName;
static int row_count = 0,column_count = 0;
int sheet_count = work_sheets->property("Count").toInt(); //获取工作表数目,如下图,有 3 页
if(sheet_count > 0)
{
QAxObject *work_sheet = work_book->querySubObject("Sheets(int)", 1); //设置为 获取第一页 数据
QAxObject *used_range = work_sheet->querySubObject("UsedRange");
QAxObject *rows = used_range->querySubObject("Rows");
row_count = rows->property("Count").toInt(); //获取行数
QAxObject *column = used_range->querySubObject("Columns");
column_count = column->property("Count").toInt(); //获取列数
//获取第一行第一列数据
ExcelName = work_sheet->querySubObject("Cells(int,int)", 1,1)->property("Value2()").toString();
// QAxObject *range = work_sheet->querySubObject("Cells(int,int)",1,1); //获取cell的值
// QString strVal = range->dynamicCall("Value2()").toString();
//获取表格中需要的数据,此处是从第三行第二列获取数据,具体原因看下图理解,根据自己的需求获取信息
for (int i =3; i <= row_count; i++) {
for (int j = 1; j <= column_count;j++) {
// QString txt = work_sheet->querySubObject("Cells(int,int)", i,j)->property("Value2()").toString(); //获取单元格内容
// arr.append(txt);
// qDebug()<<"lllllllll"<<txt;
QAxObject *range = work_sheet->querySubObject("Cells(int,int)",i,j); //获取cell的值
QString strVal = range->dynamicCall("Value2()").toString();
arr.append(strVal);
}
array.append(arr);
arr.clear(); //将每行数据存储到array后,清空arr,避免下次循环时数据累计;arr.appeng()是添加不是赋值
}
work_book->dynamicCall("Close(Boolean)", false); //关闭文件
excel.dynamicCall("Quit(void)"); //退出
}
qDebug()<<"lllllllll"<<array;
if(!array.isEmpty()){
SqlHelper::insertBlockToWarning(array);
}
}