[C++知识库]C++ 使用ODBC操作Excel文件

C++ 使用ODBC操作Excel文件

一、ODBC介绍

? 开放数据库连接（Open Database Connectivity，ODBC）是为解决异构数据库间的数据共享而产生的，现已成为WOSA(The Windows Open System Architecture(Windows开放系统体系结构))的主要部分和基于Windows环境的一种数据库访问接口标准。ODBC 为异构数据库访问提供统一接口，允许应用程序以SQL 为数据存取标准，存取不同DBMS管理的数据；使应用程序直接操纵DB中的数据，免除随DB的改变而改变。用ODBC 可以访问各类计算机上的DB文件，甚至访问如Excel 表和ASCI I数据文件这类非数据库对象。

二、环境

Visual Studio 2015

三、项目搭建

创建有MFC环境的32位控制台程序

提示： 32位控制台程序只是用于本地测试，程序代码还可以移植到其它有MFC环境的程序中。

四、编码

0）移除默认生成文件

将默认生成的代码文件移除，看着乱

1）excel_util.h

操作excel工具类的头文件

#include <iostream>
#include<stdlib.h>
#include <vector>
#include <map>
#include <afxdb.h>
#include <afx.h>
using namespace std;

CString get_columns_str(CString columns[], int columns_num);
CString get_create_sheet_sql(CString sheetName, CString columns[], CString columns_type[], int columns_num);
void WriteToExcel(CString filePath, CString sheetName, CString columns[], CString columns_type[], int columns_num, vector<vector<CString>> dataVec);
vector<vector<CString>> ReadFromExcel(CString filePath, CString sheetName, CString columns[], int columns_num);

2）excel_util.cpp

编写操作excel的工具类代码

#include "excel_util.h"

/*
获取sheet页的各列列名，返回格式如：[列1,列2,列3,···]
@param columns		sheet的各列名的数组
@param columns_num	sheet的列数
@author				lds
*/
CString get_columns_str(CString columns[], int columns_num)
{
	//printf("-----------------get_columns_str start ---------------\n");
	CString columns_str = "";  // 拼接的表头字符串，如：aa,bb,cc,dd
	for (size_t i = 0; i < columns_num; i++)
	{
		if (i == 0) {
			columns_str = columns_str + columns[i];
		}
		else
		{
			columns_str = columns_str + "," + columns[i];
		}
	}
	//printf("columns_str:[%s]", columns_str);
	//printf("-----------------get_columns_str end ---------------\n");
	return columns_str;
}

/*
获取创建excel的sheet页的sql语句
@param database		odbc数据源连接对象
@param sheetName	创建的sheet页名称
@param columns		sheet页的每列首行标题
@param columns_type	sheet页的每列单元格的类型
@param columns_num	sheet页的总列数
@return sql			创建excel的sheet页的sql语句
@author				lds
*/
CString get_create_sheet_sql(CString sheetName, CString columns[], CString columns_type[], int columns_num)
{
	//printf("----------------------get_create_sheet_sql start ------------------------\n");
	//创建表结构
	CString sql = "CREATE TABLE " + sheetName + "(";
	for (size_t i = 0; i < columns_num; i++)
	{
		if (i == 0) {
			sql = sql + columns[i] + " " + columns_type[i];
		}
		else
		{
			sql = sql + "," + columns[i] + " " + columns_type[i];
		}
	}
	sql = sql + ")";
	//printf("----------------------get_create_sheet_sql end ------------------------\n");
	return sql;

}

/*
使用ODBC往Excel里写数据。
@param filePath		创建excel文件的地址			如：C:/demo.xlsx；文件不存在时会自动生成
@param sheetName	创建的sheet页名称			如：sheet1
@param columns		sheet页的每列首行标题
如：CString columns[11] = { "序号","任务名称","物料识别码","对应的流程","设计人" ,"状态","当前任务信息","当前节点责任人","开始时间" ,"持续时间（天）","完成时间" };
@param columns_type	sheet页的每列单元格的类型
如：CString columns_type[11] = { "NUMBER","TEXT","TEXT","TEXT","TEXT" ,"TEXT","TEXT","TEXT","TEXT" ,"NUMBER","TEXT" };
@param columns_num	sheet页的总列数				如：int:11
@param data			待写入的所有数据
如：vector<vector<CString>> data = {
{ "1","名称1","识别码1" ,"流程1" ,"设计人1" ,"状态1" ,"任务信息1" ,"责任人1" ,"2022-03-10 16:38:07" ,"0" ,"2022-03-10 16:38:11" },
{ "2","名称2","识别码2" ,"流程2" ,"设计人2" ,"状态2" ,"任务信息2" ,"责任人2" ,"2022-03-10 16:38:07" ,"0" ,"2022-03-10 16:38:11" },
{ "3","名称3","识别码3" ,"流程3" ,"设计人3" ,"状态3" ,"任务信息3" ,"责任人3" ,"2022-03-10 16:38:07" ,"0" ,"2022-03-10 16:38:11" },
{ "4","名称4","识别码4" ,"流程4" ,"设计人4" ,"状态4" ,"任务信息4" ,"责任人4" ,"2022-03-10 16:38:07" ,"0" ,"2022-03-10 16:38:11" }
};
@author				lds
*/
void WriteToExcel(CString filePath, CString sheetName, CString columns[], CString columns_type[], int columns_num, vector<vector<CString>> dataVec)
{
	printf("-------------------------WriteToExcel start----------------------------\n");
	CDatabase database;
	CString sDriver = "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)";//Excel安装驱动
	CString sql; //db执行语句
				 //创建进行存取的字符串
	sql.Format("DRIVER={%s};DSN='Excel nFiles';READONLY=FALSE;DBQ=%s", sDriver, filePath);;
	//创建数据库(即Excel表格文件)
	try
	{
		if (database.OpenEx(sql, CDatabase::noOdbcDialog))
		{
			CRecordset recset(&database);
			// 查询sheet页是否存在，不存在则创建
			sql = "select * from " + sheetName;
			// cout << "查询sheet页是否存在sql:[" << sql << "]" << endl;
			try
			{
				recset.Open(CRecordset::forwardOnly, sql, CRecordset::readOnly);
				// printf("----sheet页已经存在!\n");
			}
			catch (...)
			{
				// printf("----sheet页不存在!创建sheet页!\n");
				sql = get_create_sheet_sql(sheetName, columns, columns_type, columns_num);
				// cout << "create sheet sql:[" << sql << "]" << endl;
				try
				{
					database.ExecuteSQL(sql);
					// printf("create [%s] sheet success!\n", sheetName);
				}
				catch (...)
				{
					printf("create [%s] sheet is fail!\n", sheetName);
				}
			}

			printf("Startting To Write Data In Excel...\n");
			// 获取各列名
			CString columns_str = get_columns_str(columns, columns_num);
			// 循环插入数据
			int success_count = 0;  // 插入数据成功个数
			int error_count = 0;  // 插入数据成功个数
			for (int i = 0; i < dataVec.size(); i++)
			{
				sql = "insert into " + sheetName + "(" + columns_str + ")VALUES(";
				vector<CString> rowData = dataVec[i]; // 每行数据
				for (int j = 0; j < rowData.size(); j++)
				{
					if (j == 0)
					{
						sql = sql + "\'" + rowData[j] + "\'";
					}
					else
					{
						sql = sql + "," + "\'" + rowData[j] + "\'";
					}
				}
				sql = sql + ")";
				// cout << "insert sql:[" << sql << "]" << endl;
				// 逐行写入
				try {
					database.ExecuteSQL(sql);
					success_count = success_count + 1;
				}
				catch (...)
				{
					printf("insert data is fail！SQL is [%s]\n", sql);
					error_count = error_count + 1;
				}

			}
			printf("All Data Write To Excel complete!Success count:%d；Error count:%d。\n", success_count, error_count);
		}
	}
	catch (...) {
		printf("Register ODBC Driver Is Fail!\n");
	}
	//关闭数据库
	database.Close();
	printf("-------------------------WriteToExcel end----------------------------\n");
}

/*
使用ODBC读Excel表格数据
@param filePath		创建excel文件的地址			如：C:/demo.xlsx；文件不存在时会自动生成
@param sheetName	创建的sheet页名称			如：sheet1
@param columns		sheet页的每列首行标题
如：CString columns[11] = { "序号","任务名称","物料识别码","对应的流程","设计人" ,"状态","当前任务信息","当前节点责任人","开始时间" ,"持续时间（天）","完成时间" };
@param columns_num	sheet页的总列数				如：int:11
@return				sheet页所有行数据
@author				lds
*/
vector<vector<CString>> ReadFromExcel(CString filePath, CString sheetName, CString columns[], int columns_num)
{
	printf("-------------------------------ReadFromExcel start------------------------------\n");
	//获取查询结果
	vector<vector<CString>> rows;   // sheet页所有行数据（不包含首行-标题行）
	CDatabase database;
	CString sql;
	CString driver = "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)";
	printf("driver:[%s]", driver);
	//创建进行存储的字符串
	CString ssql;
	ssql.Format("ODBC;DRIVER={%s};DSN='Excel nFiles';DBQ=%s", driver, filePath);
	//打开数据库
	try
	{
		if (database.Open(NULL, false, false, ssql))
		{
			/*
			要实现对结果集的数据操作，就要用到CRecordSet类。
			CRecordSet类定义了从数据库接收或者发送数据到数据库的成员变量，CRecordSet类定义的记录集可以是表的所有列，也可以是其中的一列，这是由SQL语句决定的。
			CRecordSet类的成员变量m_hstmt代表了定义该记录集的SQL语句句柄，m_nFields成员变量保存了记录集中字段的个数，m_nParams成员变量保存了记录集所使用的参数个数
			*/
			CRecordset recset(&database);
			//设置读取的查询语句
			CString columns_str = get_columns_str(columns, columns_num);
			sql = "SELECT " + columns_str + " FROM " + sheetName;
			printf("执行的查询sql是:[%s]\n", sql);
			//执行查询语句
			try
			{
				recset.Open(CRecordset::forwardOnly, sql, CRecordset::readOnly);
				while (!recset.IsEOF())
				{
					vector<CString> row;
					for (int i = 0; i < columns_num; i++)
					{
						CString item;
						recset.GetFieldValue(columns[i], item);
						printf("%s:%s；", columns[i], item);
						row.push_back(item);
					}
					printf("\n");
					rows.push_back(row);
					//移动到下一行
					recset.MoveNext();
				}
			}
			catch (...)
			{
				printf("执行查询sheet页数据失败！\n");
				printf("可能原因1：sql的列标题与实际的excel列标题对应不上！sql的列标题是: [%s]，请检查excel的列标题！\n", columns_str);
				printf("可能原因2：执行查询sheet页数据的sql有问题，sql是: [%s]\n", sql);

			}
			database.Close();
			printf("Read Excel is complete！Results count is %d\n", rows.size());
		}
	}
	catch (...)
	{
		printf("Register ODBC Driver Is Fail!\n");
	}
	printf("-------------------------------ReadFromExcel end------------------------------\n");
	return rows;
}

3）main.cpp

程序入口，进行测试。

#include "excel_util.h"

int main()
{
	CString columns[11] = { "序号","任务名称","物料识别码","对应的流程","设计人" ,"状态","当前任务信息","当前节点责任人","开始时间" ,"持续时间（天）","完成时间" };
	CString columns_type[11] = { "NUMBER","TEXT","TEXT","TEXT","TEXT" ,"TEXT","TEXT","TEXT","TEXT" ,"NUMBER","TEXT" };
	vector<vector<CString>> data = {
		{ "1","名称1","识别码1" ,"流程1" ,"设计人1" ,"状态1" ,"任务信息1" ,"责任人1" ,"2022-03-10 16:38:07" ,"0" ,"2022-03-10 16:38:11" },
		{ "2","名称2","识别码2" ,"流程2" ,"设计人2" ,"状态2" ,"任务信息2" ,"责任人2" ,"2022-03-10 16:38:07" ,"0" ,"2022-03-10 16:38:11" },
		{ "3","名称3","识别码3" ,"流程3" ,"设计人3" ,"状态3" ,"任务信息3" ,"责任人3" ,"2022-03-10 16:38:07" ,"0" ,"2022-03-10 16:38:11" },
		{ "4","名称4","识别码4" ,"流程4" ,"设计人4" ,"状态4" ,"任务信息4" ,"责任人4" ,"2022-03-10 16:38:07" ,"0" ,"2022-03-10 16:38:11" }
	};
	// cout << size(columns) << endl;
	CString filePath = "E:/writeDemo1111.xlsx";
	CString sheetName = "mysheet1";
	int columns_num = size(columns);
	WriteToExcel(filePath, sheetName, columns, columns_type, columns_num, data);
	vector<vector<CString>> results = ReadFromExcel(filePath, sheetName, columns, columns_num);
	system("pause");
	return 0;
}

4、最终项目结构

五、测试

本地可以看到excel成功生成了，并且数据也是正确写入了

六、可能异常及解决

异常一：预编译头未使用

解决：

异常二：参数类型转换异常

解决：

异常三：未知编码格式错误

解决：