c++操作excel(CApplication、CWorkbook、CWorksheet、CHyperlinks)代码 / 四六文摘

零点零一 2019-02-16 13:57:00

首先创建MFC，打开类向导，添加类（类型库中的MFC类），选择文件位置(EXCEL.exe)，添加类需要的类（参考下面代码的头文件），会生成对应的头文件(CWorkbook.h、CHyperlinks.h…)
注释每个生成的头文件中的代码：
//#import "C:\\Program Files\\Microsoft Office\\Office15\\EXCEL.EXE" no_namespace
1
下面代码对excel操作：
#include "msexcel/CBorders.h"
#include "msexcel/CApplication.h"
#include "msexcel/CFont0.h"
#include "msexcel/CRange.h"
#include "msexcel/CWorkbook.h"
#include "msexcel/CWorkbooks.h"
#include "msexcel/CWorksheet.h"
#include "msexcel/CWorksheets.h"
#include "msexcel/CHyperlinks.h"
#include "afxdisp.h"
//参考注释，选取需要的代码
void opExcel(){
   //获取当前绝对路径
   char buffer[_MAX_PATH];
   _getcwd(buffer, _MAX_PATH);

string v[6] = { "A1", "B1", "C1", "D1", "E1", "F1" };
string first_row[6] = { "类别", "坐标X1", "坐标Y1", "坐标X2", "坐标Y2", "类型" };
string head_v[6] = { "A", "B", "C", "D", "E", "F" };

CoInitialize(NULL);//初始化COM，最后还有CoUninitialize
COleVariant vMissing((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

CApplication app;
   CWorkbooks workbooks;
   CWorkbook workbook;
   CWorksheet worksheet;
   CRange range;
   CHyperlinks hls;

if (!app.CreateDispatch(_T("EXCEL.application"))) //启动EXCEL
   {
       AfxMessageBox(_T("居然你连OFFICE都没有安装吗?"));
       return;
   }
   app.put_Visible(FALSE);//是否显示excel
   app.put_DisplayFullScreen(FALSE);//设置全屏显示
   app.put_DisplayAlerts(FALSE);//屏蔽警告

//打开工作簿
   CString strPath;//excel文件名
   strPath.Format("%s", filename.c_str());
   workbooks = app.get_Workbooks();
   try
   {
       workbook = workbooks.Add(_variant_t(strPath));//打开excel文件
   }
   catch (CException* e)
   {
       workbook = workbooks.Add(vtMissing);//找不到就新建一个
   }

worksheet = workbook.get_ActiveSheet();//获取当前工作表

//设置excel第一行的值
   for (int i = 0; i < 6; i++){
       range = worksheet.get_Range(_variant_t(v[i].c_str()), _variant_t(v[i].c_str()));
       range.put_Value2(_variant_t(first_row[i].c_str()));//参数是二维数组可以批量设置单元格值
   }
   //插入数据
   for (int i = 0; i < detectAlgorithm.instanceRect_vector.size(); i++)
   {
       stringstream ss;
       ss << (i + 2);
       string s1 = ss.str();
       //类别
       if (detectAlgorithm.instanceRect_vector[i].instanceClass == 1)//电阻
       {
           range = worksheet.get_Range(_variant_t((head_v[0] + s1).c_str()), _variant_t((head_v[0] + s1).c_str()));
           range.put_Value2(_variant_t("电阻"));//参数是二维数组可以批量设置单元格值
       }
       else if (detectAlgorithm.instanceRect_vector[i].instanceClass == 2)//芯片
       {
           range = worksheet.get_Range(_variant_t((head_v[0] + s1).c_str()), _variant_t((head_v[0] + s1).c_str()));
           range.put_Value2(_variant_t("芯片"));//参数是二维数组可以批量设置单元格值
       }
       //坐标
       range = worksheet.get_Range(_variant_t((head_v[1] + s1).c_str()), _variant_t((head_v[1] + s1).c_str()));
       range.put_Value2(_variant_t(detectAlgorithm.instanceRect_vector[i].left_top.x));//参数是二维数组可以批量设置单元格值
       range = worksheet.get_Range(_variant_t((head_v[2] + s1).c_str()), _variant_t((head_v[2] + s1).c_str()));
       range.put_Value2(_variant_t(detectAlgorithm.instanceRect_vector[i].left_top.y));//参数是二维数组可以批量设置单元格值
       range = worksheet.get_Range(_variant_t((head_v[3] + s1).c_str()), _variant_t((head_v[3] + s1).c_str()));
       range.put_Value2(_variant_t(detectAlgorithm.instanceRect_vector[i].right_bottom.x));//参数是二维数组可以批量设置单元格值
       range = worksheet.get_Range(_variant_t((head_v[4] + s1).c_str()), _variant_t((head_v[4] + s1).c_str()));
       range.put_Value2(_variant_t(detectAlgorithm.instanceRect_vector[i].right_bottom.y));//参数是二维数组可以批量设置单元格值
       //类型
       if (detectAlgorithm.instanceRect_vector[i].instanceWord != "")//如果器件上有数字
       {
           range = worksheet.get_Range(_variant_t((head_v[5] + s1).c_str()), _variant_t((head_v[5] + s1).c_str()));
           range.put_Value2(_variant_t(detectAlgorithm.instanceRect_vector[i].instanceWord.c_str()));//参数是二维数组可以批量设置单元格值
       }
       else//否则保存图片并插入图片超链接
       {
           string outImg_pathName = string(buffer) + "\\images\\" + head_v[5] + s1 + ".jpg";
           //切割并保存图片
           Mat outImg = detectAlgorithm.cutRect(detectAlgorithm.detect_src_img, detectAlgorithm.instanceRect_vector[i].left_top, detectAlgorithm.instanceRect_vector[i].right_bottom);
           cv::imwrite(outImg_pathName, outImg);
           //插入超链接
           hls = (worksheet.get_Hyperlinks());//超链接
           COleVariant vCell((head_v[5] + s1).c_str());
           COleVariant vText("图片");
           COleVariant vTip("图片超链接");
           hls.Add(worksheet.get_Range(vCell, vMissing), outImg_pathName.c_str(), vMissing, vTip, vText);//添加超链接
       }
   }

//保存文件
   workbook.SaveAs(COleVariant(strPath), covOptional,
       covOptional, covOptional,
       covOptional, covOptional, (long)0, covOptional, covOptional, covOptional,
       covOptional, covOptional);

//释放资源
   hls.ReleaseDispatch();
   worksheet.ReleaseDispatch();
   workbook.ReleaseDispatch();
   workbooks.ReleaseDispatch();
   range.ReleaseDispatch();
   app.Quit();
   app.ReleaseDispatch();
   CoUninitialize();//对应CoInitialize

---------------------
作者：chanshibing
来源：CSDN
原文：https://blog.csdn.net/chanshibing/article/details/86304169
版权声明：本文为博主原创文章，转载请附上博文链接！

c++操作excel(CApplication、CWorkbook、CWorksheet、CHyperlinks)代码

相关推荐