C++ MFC读写excel
C++ MFC读写excel
直接上，楞干
参考如下两篇文章
##读取 ： https://blog.csdn.net/V10_x/article/details/78464453?utm_medium=distribute.pc_relevant_download.none-task-blog-baidujs-2.nonecase&depth_1-utm_source=distribute.pc_relevant_download.none-task-blog-baidujs-2.nonecase
##写：参考https://blog.csdn.net/ywx123_/article/details/77074038?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-7.control&dist_request_id=&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-7.control
//
1、 新建一个基于对话框的MFC应用程序
2、 添加Excel相关类
打开类向导，添加类下拉框选择类型库中的MFC类，来源选择注册表（不容易出错），可用的类型库选择"Microsoft Excel …"（根据Excel的版本有所不同）；接口选择：_Application，_WorkSheet，_WorkBook，Range，WorkSheets，WorkBooks；添加6个必要的类CApplication，CWorkbook，CWorksheet，CRange，CWorkbooks，CWorksheets
3、
在stdafx.h中包含，以下头文件，编译。
#include “CApplication.h”
#include “CFont0.h”
#include “CRange.h”
#include “CWorkbook.h”
#include “CWorkbooks.h”
#include “CWorksheet.h”
#include “CWorksheets.h”
**屏蔽掉导入每个头文件下的//#import “D:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE” no_namespace
**编译出现1>c:\users\desktop\exceltest\crange.h(335): warning C4003: “DialogBoxW”宏的实参不足。将DialogBox修改为_DialogBox。
//定义全局vector
//存储Excel读取的数据
vector vstrType;
vector vstrID;
vector vstrAlias;
vector vstrMCC;
vector vstrMNC;
vector vstrGroup;
vector vstrCou;
vector vstrDMOFreq;
导入按钮点击事件
//导入Excel表格void ExcelRW::OnBnClickedImportExcel()
{CFileDialog file(TRUE,NULL,NULL,OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT,_T("EXCEL文件(*.xls;*.xlsx)|*.xls;*.xlsx|| " ),AfxGetMainWnd());if (file.DoModal() == IDOK){CString strPath = file.GetPathName();//获取到要读取的路径if ( "" == strPath){//(_T("未选择文件"))return;}getExcelData(strPath);int ii = vstrType.size();for (int j=0;j导出按钮点击事件
//导出Excel表格
void ExcelRW::OnBnClickedExportExcel()
{CString strType, strID, strAlias, strMcc, strMnc, strGroupAddr, strCou, strDmoFreq;//1.创建基本对象CApplication App; //创建应用程序实例CWorkbooks Books; //工作簿，多个Excel文件CWorkbook Book; //单个工作簿CWorksheets sheets;//多个sheet页面CWorksheet sheet; //单个sheet页面CRange range; //操作单元格//2.打开指定Excel文件，如果不存在就创建char path[MAX_PATH];GetCurrentDirectory(MAX_PATH,(TCHAR*)path);//获取当前路径CString strExcelFile =(TCHAR*) path;CString strdevName = _T("\\Test111111111.xlsx"); //xls也行strExcelFile += strdevName;COleVariantcovTrue((short)TRUE),covFalse((short)FALSE),covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);LPDISPATCH lpdisp = NULL;//1.创建Excel实例if(!App.CreateDispatch(_T("Excel.Application"),NULL)){AfxMessageBox(_T("创建Excel实例失败"));exit(-1);}else{AfxMessageBox(_T("创建成功"));}App.put_Visible(TRUE); //打开ExcelApp.put_UserControl(FALSE); //2. 得到workbooks容器Books.AttachDispatch(App.get_Workbooks());Book.AttachDispatch(Books.Add(covOptional));sheets.AttachDispatch(Book.get_Worksheets());sheet.AttachDispatch(sheets.get_Item(COleVariant((short)1))); //获取sheet1sheet.put_Name(_T("TestName")); //设置sheet1名字//3. 加载要合并的单元格;
// range.AttachDispatch(sheet.get_Range(COleVariant(_T("B2")),COleVariant(_T("E2"))),TRUE);
// range.Merge(COleVariant((long)0)); //合并单元格;range.AttachDispatch(sheet.get_Cells(),TRUE); range.put_Item(COleVariant((long)1),COleVariant((long)1),COleVariant(_T("Type")));range.put_Item(COleVariant((long)1),COleVariant((long)2),COleVariant(_T("ID")));range.put_Item(COleVariant((long)1),COleVariant((long)3),COleVariant(_T("Alias")));range.put_Item(COleVariant((long)1),COleVariant((long)4),COleVariant(_T("MCC")));range.put_Item(COleVariant((long)1),COleVariant((long)5),COleVariant(_T("MNC")));range.put_Item(COleVariant((long)1),COleVariant((long)6),COleVariant(_T("GroupAddr")));range.put_Item(COleVariant((long)1),COleVariant((long)7),COleVariant(_T("Cou")));range.put_Item(COleVariant((long)1),COleVariant((long)8),COleVariant(_T("DMO Freq")));for (int i =0; i < 1000; i++ ){if (m_excel_list.GetItemText(i,1).IsEmpty()){break;}CString mm = m_excel_list.GetItemText(i,1);//加载所有单元格;range.put_Item(COleVariant((long)i+2),COleVariant((long)1),COleVariant(mm));range.put_Item(COleVariant((long)i+2),COleVariant((long)2),COleVariant(m_excel_list.GetItemText(i,2)));range.put_Item(COleVariant((long)i+2),COleVariant((long)3),COleVariant(m_excel_list.GetItemText(i,3)));range.put_Item(COleVariant((long)i+2),COleVariant((long)4),COleVariant(m_excel_list.GetItemText(i,4)));range.put_Item(COleVariant((long)i+2),COleVariant((long)5),COleVariant(m_excel_list.GetItemText(i,5)));range.put_Item(COleVariant((long)i+2),COleVariant((long)6),COleVariant(m_excel_list.GetItemText(i,6)));range.put_Item(COleVariant((long)i+2),COleVariant((long)7),COleVariant(m_excel_list.GetItemText(i,7)));range.put_Item(COleVariant((long)i+2),COleVariant((long)8),COleVariant(m_excel_list.GetItemText(i,8)));}range.AttachDispatch(sheet.get_UsedRange());//加载已使用的单元格range.put_WrapText(COleVariant((long)1)); //设置文本自动换行//5.设置对齐方式//水平对齐：默认 1 居中 -4108， 左= -4131，右=-4152//垂直对齐：默认 2 居中 -4108， 左= -4160，右=-4107range.put_VerticalAlignment(COleVariant((long)-4108));range.put_HorizontalAlignment(COleVariant((long)-4108));Book.SaveCopyAs(COleVariant(strExcelFile)); //保存Book.put_Saved(TRUE);//8.释放资源range.ReleaseDispatch();sheet.ReleaseDispatch();sheets.ReleaseDispatch();Book.ReleaseDispatch();Books.ReleaseDispatch();App.ReleaseDispatch();
}获取数据到vector
//获取Excel表格数据，传入地址
void ExcelRW::getExcelData(CString strPath)
{CApplication app;CWorkbooks books;CWorkbook book;CWorksheets sheets;CWorksheet sheet;CRange range;COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); if (!app.CreateDispatch(_T("Excel.Application"))) { MessageBox(_T("Error!Creat Excel Application Server Fail!")); exit(1); } books.AttachDispatch(app.get_Workbooks(),true); book.AttachDispatch(books.Add(_variant_t(strPath)));//获取选择的Excel文件sheets.AttachDispatch(book.get_Worksheets(),true);//获取文件中的所有sheetsheet.AttachDispatch(sheets.get_Item(_variant_t("sheet1")),true);//获取sheet1区域vstrType.clear();vstrID.clear();vstrAlias.clear();vstrMCC.clear();vstrMNC.clear();vstrGroup.clear();vstrCou.clear();vstrDMOFreq.clear();CString str;bool bExit = false;for(int irow=2;!bExit;irow++){for(int icolumn=1;icolumn<9;icolumn++){range.AttachDispatch(sheet.get_Cells(),true);//获取sheet1所有的单元格，重置当前区域为A1range.AttachDispatch(range.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal);//获取单元格switch(icolumn){case 1: str = range.get_Value2(); if(str.IsEmpty()){bExit = true; break;}vstrType.push_back(str); break;case 2: str = range.get_Value2();vstrID.push_back(str);break;case 3: str = range.get_Value2();str.Replace(" ", "");vstrAlias.push_back(str);break;case 4: str = range.get_Value2();str.Replace(" ", "");vstrMCC.push_back(str);break;case 5: str = range.get_Value2();vstrMNC.push_back(str);break;case 6: str = range.get_Value2();vstrGroup.push_back(str);break;case 7: str = range.get_Value2();vstrCou.push_back(str);break;case 8:str = range.get_Value2();vstrDMOFreq.push_back(str);default: ;}if(bExit){break;}}}range.ReleaseDispatch();//释放对象sheet.ReleaseDispatch();sheets.ReleaseDispatch();book.ReleaseDispatch(); books.ReleaseDispatch(); app.ReleaseDispatch(); app.Quit();//退出Excel程序if(!vstrType.empty()){//(_T("读取数据成功"));} else{//(_T("读取数据失败"));}}
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！