Qt 读取Excel表格数据 生成Excel表格并写入数据_qt 读取execl表多个单元格写入一个字段

qtxlsx读取Excel,无需本地存在excel或者wps驱动，读写操作更方便简洁。

一、 读取Excel表格

修改.pro文件，增加 axcontainer

QT       += axcontainer
1

导入头文件

#include "QFileDialog"
#include "QAxObject"
1
2

生成打开文件窗口

  QString strFile = QFileDialog::getOpenFileName(this,QStringLiteral("选择Excel文件"),"",tr("Exel file(*.xls *.xlsx)"));
    if (strFile.isEmpty()){
        return;
    }
1
2
3
4

定义一个容器，存储读取数据

    QVector<QString> arr;	//存储每一行数据
    QVector<QVector<QString>> array;	//将每行数据汇总，类似于二维数组，但不需要手动定义空间大小，造成内存浪费
1
2

读Exceld的具体过程

    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("Value").toString();
        //获取表格中需要的数据，此处是从第三行第二列获取数据，具体原因看下图理解，根据自己的需求获取信息
        for (int i =3; i <= row_count; i++) {
            for (int j = 2; j <= column_count;j++) {
                QString txt = work_sheet->querySubObject("Cells(int,int)", i,j)->property("Value").toString(); //获取单元格内容
                arr.append(txt);
            }
            array.append(arr);
            arr.clear();	//将每行数据存储到array后，清空arr，避免下次循环时数据累计；arr.appeng()是添加不是赋值
        }

        work_book->dynamicCall("Close(Boolean)", false);  //关闭文件
        excel.dynamicCall("Quit(void)");  //退出
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

我的Excel表

二、 将数据导入Excel表,生成一个新表

include "QFile"
1

设置路径，并生成一个空白Excel

QString desktop_path = QStandardPaths::writableLocation(QStandardPaths::DesktopLocation);//获取桌面路径
QString excel_path_1 = desktop_path + "/LCU接线属性表.xlsx"; //设置文件路径、名、格式
1
2

---

if(!excel_path_1.isEmpty()){
	QAxObject *excel = new QAxObject(this);
	excel->setControl("Excel.Application");//连接Excel控件
	excel->dynamicCall("SetVisible (bool Visible)","false");//不显示窗体
	excel->setProperty("DisplayAlerts", true);//不显示任何警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示
	
	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);//获取工作表集合的工作表1，即sheet1
	
	//标题行
	QAxObject *cell;
	cell=worksheet->querySubObject("Cells(int,int)", 1, 1);
	cell->dynamicCall("SetValue(const QString&)", "LCU接线属性表");
	cell->querySubObject("Font")->setProperty("Size", 11);
	//合并标题行
	QString cellTitle;
	cellTitle.append("A1:");
	cellTitle.append(QChar(4 + '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
	
	
	QAxObject *cellA,*cellB,*cellC,*cellD,*cellE;
	
	//设置标题
	int cellrow=2;
	QString A="A"+QString::number(cellrow);//设置要操作的单元格，如A1
	QString B="B"+QString::number(cellrow);
	QString C="C"+QString::number(cellrow);
	QString D="D"+QString::number(cellrow);
	QString E="E"+QString::number(cellrow);
	
	cellA = worksheet->querySubObject("Range(QVariant, QVariant)",A);//获取单元格
	cellB = worksheet->querySubObject("Range(QVariant, QVariant)",B);
	cellC=worksheet->querySubObject("Range(QVariant, QVariant)",C);
	cellD=worksheet->querySubObject("Range(QVariant, QVariant)",D);
	cellE=worksheet->querySubObject("Range(QVariant, QVariant)",E);
	cellA->dynamicCall("SetValue(const QVariant&)",QVariant("序号"));//设置单元格的值
	cellB->dynamicCall("SetValue(const QVariant&)",QVariant("IO面板"));
	cellC->dynamicCall("SetValue(const QVariant&)",QVariant("接线板"));
	cellD->dynamicCall("SetValue(const QVariant&)",QVariant("KW打包地址"));
	cellE->dynamicCall("SetValue(const QVariant&)",QVariant("KW地址"));
	
	for(int i=0;i<120;i++){
		QString A="A"+QString::number(i+3);//设置要操作的单元格，如A1
		QString B="B"+QString::number(i+3);
		QString C="C"+QString::number(i+3);
		QString D="D"+QString::number(i+3);
		QString E="E"+QString::number(i+3);
		cellA = worksheet->querySubObject("Range(QVariant, QVariant)",A);//获取单元格
		cellB = worksheet->querySubObject("Range(QVariant, QVariant)",B);
		cellC=worksheet->querySubObject("Range(QVariant, QVariant)",C);
		cellD=worksheet->querySubObject("Range(QVariant, QVariant)",D);
		cellE=worksheet->querySubObject("Range(QVariant, QVariant)",E);
		
		cellA->dynamicCall("SetValue(const QVariant&)",QVariant(i+1));//设置单元格的值
		range = worksheet->querySubObject("Range(const QString&)", A); //设置A单元格元素居中显示
		range->setProperty("HorizontalAlignment", -4108);//xlCenter
		range->setProperty("VerticalAlignment", -4108);//xlCenter
		
		cellB->dynamicCall("SetValue(const QVariant&)",QVariant(array[i][0]));
		range = worksheet->querySubObject("Range(const QString&)", B); //设置B单元格元素居中显示
		range->setProperty("HorizontalAlignment", -4108);//xlCenter
		range->setProperty("VerticalAlignment", -4108);//xlCenter
		
		cellC->dynamicCall("SetValue(const QVariant&)",QVariant(array[i][1]));
		range = worksheet->querySubObject("Range(const QString&)", C); //设置C单元格元素居中显示
		range->setProperty("HorizontalAlignment", -4108);//xlCenter
		range->setProperty("VerticalAlignment", -4108);//xlCenter
		
		cellD->dynamicCall("SetValue(const QVariant&)",QVariant(array[i][2]));
		range = worksheet->querySubObject("Range(const QString&)", D); //设置D单元格元素居中显示
		range->setProperty("HorizontalAlignment", -4108);//xlCenter
		range->setProperty("VerticalAlignment", -4108);//xlCenter
		
		cellE->dynamicCall("SetValue(const QVariant&)",QVariant(array[i][3]));
		range = worksheet->querySubObject("Range(const QString&)", E); //设置E单元格元素居中显示
		range->setProperty("HorizontalAlignment", -4108);//xlCenter
		range->setProperty("VerticalAlignment", -4108);//xlCenter
	}
	
	workbook->dynamicCall("SaveAs(const QString&)",QDir::toNativeSeparators(excel_path_1));//保存至filepath，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\"，不然一定保存不了。
	workbook->dynamicCall("Close()");//关闭工作簿
	excel->dynamicCall("Quit()");//关闭excel
	delete excel;
	excel=NULL;
	QMessageBox::information(NULL,"","LCU接线属性表导出完成");
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95