最近项目中需要读取Excel指定的内容并insert SqlServer中，在学习读取Excel过程中，该文给予了非常大的帮助 C++使用OLE高速读写EXCEL的源码，本文代码也是在此基础上修改而成的。
使用前提条件，引用的文章里有也就不废话，直接上代码
头文件
#pragma once//OLE的头文件#include ".\CRange.h"#include ".\CWorkbook.h"#include ".\CWorkbooks.h"#include ".\CWorksheet.h"#include ".\CWorksheets.h"#include ".\CApplication.h"#include
我采用多线程读取的方法：采用预加载的方式，将EXCEL的内容块先在主线程中读取出来，然后在开启2个线程，正反方向读取处理内容块。 CPP文件
/******************************************************************************************这个类是从网上下载的，感谢原来的作者，我只试试是稍稍做了一下修正。修正包括一些参数的使用不谨慎，bool 改为BOOL等,对于对象关系，我改了一部分，感觉原来的作者对于OO的思路部分不是很清楚。对于这类东西OLE，我完全不了解，用别人封装的东西感觉还是放心了很多，C++，伟大的C++/gyssoft/archive//04/29/1592104.aspxOLE读写EXCEL都比较慢，所以应该尽量减少OLE的次数对于读取，还有解决方法，请试用一下预加载的方式，这个方法一次加载所有的读取数据,如此速度就飞快了。据说写数据是没有什么方法加快的/t/0626/21/1962211.html增加了一些写入方式的代码，保证可以写入EXCEL数据区，但是对于保存，我发现如果调用CLOSE并且保存的方式，速度非常慢，我不理解为什么。所以我吧EXCEL打开了，让你进行后续管理,修改了部分代码以使其支持多线程读取Excel******************************************************************************************///-----------------------excelfile.cpp----------------#include "StdAfx.h"#include "ExcelTool.h"COleVariantcovTrue((short)TRUE),covFalse((short)FALSE),covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); //CApplication CExcelTool::excel_application_;STR_excelData *stu_excelData=NULL;CExcelTool::CExcelTool():already_preload_(FALSE){}CExcelTool::~CExcelTool(){//CloseExcelFile();ReleaseExcel();}//初始化EXCEL文件，BOOL CExcelTool::InitExcel(){// if (excel_application_) //因为excel_application_是一个全局对象，因此在线程里需要将其ReleaseDispatch// excel_application_.ReleaseDispatch();//创建Excel 2000服务器(启动Excel if (!excel_application_.CreateDispatch("Excel.Application",NULL)) { AfxMessageBox("创建Excel服务失败,你可能没有安装EXCEL，请检查!"); return FALSE;}excel_application_.put_DisplayAlerts(FALSE); return TRUE;}//void CExcelTool::ReleaseExcel(){excel_application_.Quit();excel_application_.ReleaseDispatch();excel_application_=NULL;}//打开excel文件BOOL CExcelTool::OpenExcelFile(const char *file_name){//先关闭CloseExcelFile();//利用模板文件建立新文档 excel_books_.AttachDispatch(excel_application_.get_Workbooks(),true); LPDISPATCH lpDis = NULL;lpDis = excel_books_.Add(COleVariant(file_name)); if (lpDis){excel_work_book_.AttachDispatch(lpDis); //得到Worksheets excel_sheets_.AttachDispatch(excel_work_book_.get_Worksheets(),true); //记录打开的文件名称open_excel_file_ = file_name;return TRUE;}return FALSE;}//关闭打开的Excel 文件,默认情况不保存文件void CExcelTool::CloseExcelFile(BOOL if_save){//如果已经打开，关闭文件if (open_excel_file_.IsEmpty() == FALSE){//如果保存,交给用户控制,让用户自己存，如果自己SAVE，会出现莫名的等待if (if_save){ShowInExcel(TRUE);}else{//excel_work_book_.Close(COleVariant(short(FALSE)),COleVariant(open_excel_file_),covOptional);excel_books_.Close();}//打开文件的名称清空open_excel_file_.Empty();}excel_sheets_.ReleaseDispatch();excel_work_sheet_.ReleaseDispatch();excel_current_range_.ReleaseDispatch();excel_work_book_.ReleaseDispatch();excel_books_.ReleaseDispatch();}void CExcelTool::SaveasXSLFile(const CString &xls_file){excel_work_book_.SaveAs(COleVariant(xls_file),covOptional,covOptional,covOptional,covOptional,covOptional,0,covOptional,covOptional,covOptional,covOptional,covOptional);return;}int CExcelTool::GetSheetCount(){return excel_sheets_.get_Count();}CString CExcelTool::GetSheetName(long table_index){CWorksheet sheet;sheet.AttachDispatch(excel_sheets_.get_Item(COleVariant((long)table_index)),true);CString name = sheet.get_Name();sheet.ReleaseDispatch();return name;}//按照序号加载Sheet表格,可以提前加载所有的表格内部数据BOOL CExcelTool::LoadSheet(long table_index,BOOL pre_load){LPDISPATCH lpDis = NULL;excel_current_range_.ReleaseDispatch();excel_work_sheet_.ReleaseDispatch();lpDis = excel_sheets_.get_Item(COleVariant((long)table_index));if (lpDis){excel_work_sheet_.AttachDispatch(lpDis,true);excel_current_range_.AttachDispatch(excel_work_sheet_.get_Cells(), true);}else{return FALSE;}already_preload_ = FALSE;//如果进行预先加载if (pre_load){PreLoadSheet();already_preload_ = TRUE;}return TRUE;}//按照名称加载Sheet表格,可以提前加载所有的表格内部数据BOOL CExcelTool::LoadSheet(const char* sheet,BOOL pre_load){LPDISPATCH lpDis = NULL;excel_current_range_.ReleaseDispatch();excel_work_sheet_.ReleaseDispatch();lpDis = excel_sheets_.get_Item(COleVariant(sheet));if (lpDis){excel_work_sheet_.AttachDispatch(lpDis,true);excel_current_range_.AttachDispatch(excel_work_sheet_.get_Cells(), true);}else{return FALSE;}//already_preload_ = FALSE;//如果进行预先加载if (pre_load){already_preload_ = TRUE;PreLoadSheet();}return TRUE;}//得到列的总数int CExcelTool::GetColumnCount(){CRange range;CRange usedRange;usedRange.AttachDispatch(excel_work_sheet_.get_UsedRange(), true);range.AttachDispatch(usedRange.get_Columns(), true);int count = range.get_Count();usedRange.ReleaseDispatch();range.ReleaseDispatch();return count;}//得到行的总数int CExcelTool::GetRowCount(){CRange range;CRange usedRange;usedRange.AttachDispatch(excel_work_sheet_.get_UsedRange(), true);range.AttachDispatch(usedRange.get_Rows(), true);int count = range.get_Count();usedRange.ReleaseDispatch();range.ReleaseDispatch();return count;}//得到行列数void CExcelTool::GetRowColumnCount(int& iRowSize,int& iColumnSize){CRange range;CRange usedRange;usedRange.AttachDispatch(excel_work_sheet_.get_UsedRange(), true);range.AttachDispatch(usedRange.get_Rows(), true);iRowSize = range.get_Count();range.ReleaseDispatch();range.AttachDispatch(usedRange.get_Columns(), true);iColumnSize = range.get_Count();range.ReleaseDispatch();usedRange.ReleaseDispatch();stu_excelData->m_iRow=iRowSize;stu_excelData->m_iColumn=iColumnSize;}//检查一个CELL是否是字符串BOOL CExcelTool::IsCellString(long irow, long icolumn){CRange range;range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);COleVariant vResult =range.get_Value2();//VT_BSTR标示字符串if(vResult.vt == VT_BSTR) {return TRUE;}return FALSE;}//检查一个CELL是否是数值BOOL CExcelTool::IsCellInt(long irow, long icolumn){CRange range;range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);COleVariant vResult =range.get_Value2();//好像一般都是VT_R8if(vResult.vt == VT_INT || vResult.vt == VT_R8) {return TRUE;}return FALSE;}//CString CExcelTool::GetCellString(long irow, long icolumn){COleVariant vResult ;CString str;//字符串if (already_preload_ == FALSE){CRange range;range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);vResult =range.get_Value2();range.ReleaseDispatch();}//如果数据依据预先加载了else{long read_address[2];VARIANT val;read_address[0] = irow;read_address[1] = icolumn;ole_safe_array_.GetElement(read_address, &val);vResult = val;}if(vResult.vt == VT_BSTR){str=vResult.bstrVal;}//整数else if (vResult.vt==VT_INT){str.Format("%d",vResult.pintVal);}//8字节的数字 else if (vResult.vt==VT_R8){str.Format("%0.0f",vResult.dblVal);}//时间格式else if(vResult.vt==VT_DATE) {SYSTEMTIME st;VariantTimeToSystemTime(vResult.date, &st);CTime tm(st); str=tm.Format("%Y-%m-%d");}//单元格空的else if(vResult.vt==VT_EMPTY) {str="";} return str;}double CExcelTool::GetCellDouble(long irow, long icolumn){double rtn_value = 0;COleVariant vresult;//字符串if (already_preload_ == FALSE){CRange range;range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);vresult =range.get_Value2();range.ReleaseDispatch();}//如果数据依据预先加载了else{long read_address[2];VARIANT val;read_address[0] = irow;read_address[1] = icolumn;ole_safe_array_.GetElement(read_address, &val);vresult = val;}if (vresult.vt==VT_R8){rtn_value = vresult.dblVal;}return rtn_value;}//VT_R8int CExcelTool::GetCellInt(long irow, long icolumn){int num;COleVariant vresult;if (already_preload_ == FALSE){CRange range;range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);vresult = range.get_Value2();range.ReleaseDispatch();}else{long read_address[2];VARIANT val;read_address[0] = irow;read_address[1] = icolumn;ole_safe_array_.GetElement(read_address, &val);vresult = val;}//num = static_cast(vresult.dblVal);return num;}void CExcelTool::SetCellString(long irow, long icolumn,CString new_string){COleVariant new_value(new_string);CRange start_range = excel_work_sheet_.get_Range(COleVariant("A1"),covOptional);CRange write_range = start_range.get_Offset(COleVariant((long)irow -1),COleVariant((long)icolumn -1) );write_range.put_Value2(new_value);start_range.ReleaseDispatch();write_range.ReleaseDispatch();}void CExcelTool::SetCellInt(long irow, long icolumn,int new_int){COleVariant new_value((long)new_int);CRange start_range = excel_work_sheet_.get_Range(COleVariant("A1"),covOptional);CRange write_range = start_range.get_Offset(COleVariant((long)irow -1),COleVariant((long)icolumn -1) );write_range.put_Value2(new_value);start_range.ReleaseDispatch();write_range.ReleaseDispatch();}//void CExcelTool::ShowInExcel(BOOL bShow){excel_application_.put_Visible(bShow);excel_application_.put_UserControl(bShow);}//返回打开的EXCEL文件名称CString CExcelTool::GetOpenFileName(){return open_excel_file_;}//取得打开sheet的名称CString CExcelTool::GetLoadSheetName(){return excel_work_sheet_.get_Name();}//取得列的名称，比如27->AAchar *CExcelTool::GetColumnName(long icolumn){ static char column_name[64];size_t str_len = 0;while(icolumn > 0){int num_data = icolumn % 26;icolumn /= 26;if (num_data == 0){num_data = 26;icolumn--;}column_name[str_len] = (char)((num_data-1) + 'A' );str_len ++;}column_name[str_len] = '\0';//反转_strrev(column_name);return column_name;}//预先加载void CExcelTool::PreLoadSheet(){CRange used_range;used_range = excel_work_sheet_.get_UsedRange();VARIANT ret_ary = used_range.get_Value2();if (!(ret_ary.vt & VT_ARRAY)){return;}//ole_safe_array_.Clear();ole_safe_array_.Attach(ret_ary); }//读取Excel里内容，并保存进全局map，这里采用的是2个线程，一个从头到尾读取，一个反着读，一旦两人相遇，即停止。void CExcelTool::GetContent(long IN iStartRow,long IN iEndRow,std::map>& OUT excelContents){std::map>* mapContents=&stu_excelData->m_mapContents; if (iEndRow>iStartRow) //正向读取 { if (iEndRow>stu_excelData->m_iRow) iEndRow=stu_excelData->m_iRow; for (int i=iStartRow;i<=iEndRow;i++) { std::map >::iterator it=mapContents->find(i); if (it!=mapContents->end()) { return; } std::vector arrColumns; for (int j=1;j<=stu_excelData->m_iColumn;j++) { COleVariant vResult ; if (already_preload_ == FALSE) //使用多线程时，应该必须使用预加载 { CRange range; range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)i),COleVariant((long)j)).pdispVal, true); vResult =range.get_Value2(); range.ReleaseDispatch(); } //如果数据依据预先加载了 else { long read_address[2]; VARIANT val; read_address[0] = i; read_address[1] = j; ole_safe_array_.GetElement(read_address, &val); vResult = val; } arrColumns.push_back(vResult); } InsertExcelData(mapContents,i,arrColumns); } }else { if (iStartRow>stu_excelData->m_iRow) iStartRow=stu_excelData->m_iRow; for (int i=iStartRow;i>=iEndRow;i--) { std::map >::iterator it=mapContents->find(i); if (it!=mapContents->end()) { return; } std::vector arrColumns; for (int j=1;j<=stu_excelData->m_iColumn;j++) { COleVariant vResult ; if (already_preload_ == FALSE) { CRange range; range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)i),COleVariant((long)j)).pdispVal, true); vResult =range.get_Value2(); range.ReleaseDispatch(); } //如果数据依据预先加载了 else { long read_address[2]; VARIANT val; read_address[0] = i; read_address[1] = j; ole_safe_array_.GetElement(read_address, &val); vResult = val; } arrColumns.push_back(vResult); } InsertExcelData(mapContents,i,arrColumns); } } excelContents=*mapContents; } //写入数据时需要互斥，不同同时写入，以免报错 void CExcelTool::InsertExcelData(std::map >*& mapData,int iRow, std::vector & arrColumnData) { stu_excelData->m_muMapData.lock(); mapData->insert(make_pair(iRow,arrColumnData)); stu_excelData->m_muMapData.unlock(); }
这里只引用了两个线程，如果逻辑正确，可以再多开线程。
如果觉得《C++ 多线程ole读取Excel》对你有帮助，请点赞、收藏，并留下你的观点哦！
