C++ MFC 导出ListControl数据到Excel

最新推荐文章于 2025-05-27 22:08:50 发布

陈同学是个程序员

最新推荐文章于 2025-05-27 22:08:50 发布

阅读量384

点赞数

分类专栏： C++ 文章标签： c++ mfc excel

本文链接：https://blog.youkuaiyun.com/weixin_38601677/article/details/120346383

版权

C++ 专栏收录该内容

1 篇文章

订阅专栏

一、导入对应的类（）
二、开启线程

void CLDITxtQueryDlg::OnBnClickedBnExportToXLS()
{
	// TODO: 在此添加控件通知处理程序代码
	if(ui_ListCtrlDataResult.GetItemCount()==0)
	{
		MessageBox("抱歉，查询结果中没有数据，不能导出！");
		return ;
	}
	CString newFileName="";
	SYSTEMTIME st = { 0 };
	GetLocalTime(&st);
	newFileName.Format(g_curQueryDataResult.m_strExportFileName+"%d-%02d-%02d %02d%02d%02d",st.wYear,st.wMonth,st.wDay,st.wHour,st.wMinute,st.wSecond);
	CFileDialog dlg(FALSE,_T("(*.xlsx)"),_T(newFileName),OFN_HIDEREADONLY |OFN_OVERWRITEPROMPT, _T("(*.*)|*.xlsx||"),NULL);
	if (dlg.DoModal() == IDOK)
	{
		//获取路径
		CString strFileName=dlg.GetPathName();
		g_curQueryDataResult.m_strExportFileName=strFileName;
		/*测试线程*/
		exportPro.dCurNum=0;
		exportPro.nExportState=0;
		exportPro.dTotalNum=ui_ListCtrlDataResult.GetItemCount();
		CEPorgressDlg epdlg;
		AfxBeginThread(ExportToExcel,this);//申请一个进程来执行方法，也就是主程序处理代码。
		epdlg.DoModal();
		/*结束*/
		MessageBox("    导出成功！");
	}
}

三、调用方法

UINT CLDITxtQueryDlg::ExportToExcel(LPVOID pParam)
{
	CLDITxtQueryDlg* pDlg = (CLDITxtQueryDlg*)pParam;
	//获取路径
	CString strFile=g_curQueryDataResult.m_strExportFileName;
	//获取listcontrol数据导出
	COleVariant covTrue((long)TRUE),covFalse((long)FALSE),covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
	CApplication app;
	CWorkbooks books;
	CWorkbook book;
	CWorksheets sheets;
	CWorksheet sheet;
	CRange range;
	CRange cols;
	CFont0 font;
	//创建Excel 办事器（启动Excel）
	if (!app.CreateDispatch(_T("Excel.Application")))
	{
		pDlg->MessageBox(_T("创建失败！"));
		return 0;
	}
	//app.put_Visible(TRUE);//设置表可见性
	//app.put_DisplayFullScreen(FALSE);//设置全屏显示
	app.put_DisplayAlerts(FALSE);//屏蔽警告
	//获取一个新的workbook.
	books = app.get_Workbooks();
	book = books.Add(covOptional);
	sheets = book.get_Worksheets();
	sheet = sheets.get_Item(COleVariant((long)1));
	

	CHeaderCtrl *pmyHeaderCtrl;
	pmyHeaderCtrl = pDlg->ui_ListCtrlDataResult.GetHeaderCtrl();//此句取得CListCtrl控件的列表头
	long iRow,iCol;
	long m_cols = pmyHeaderCtrl->GetItemCount();
	long m_rows = pDlg->ui_ListCtrlDataResult.GetItemCount();
	if(m_rows>1048576) 
	{
		m_rows=1048576;
		pDlg->MessageBox("查询结果超过excel的最大行数，超过部分数据将丢失！");
	}
	HDITEM hdi;
	TCHAR lpBuffer[256];
	bool fFound=false;
	hdi.mask=HDI_TEXT;
	hdi.pszText=lpBuffer;
	hdi.cchTextMax=256;
	CString colName;
	CString strTemp;
	if(g_curQueryDataResult.m_strExportFileName.Find("生产明细数据")>-1)
	{
		CString strHeader="序号,StartTM,EndTM,PN,SN,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col,col";
		vector<CString> vecHeader=Split(strHeader,",");
		m_cols=vecHeader.size();
		for(iCol=0;iCol<m_cols;iCol++)//将列表的标题头写入EXCEL
		{
			pDlg->GetCellName(1,iCol+1,colName);
			range = sheet.get_Range(COleVariant(colName),COleVariant(colName));
			pmyHeaderCtrl->GetItem(iCol,&hdi);
			range.put_Value2(COleVariant(vecHeader[iCol]));
			long nWidth = vecHeader.size()/4;
			//long nWidth=15;
			//得到第iCol+1列  
			range.AttachDispatch(range.get_Item(_variant_t((long)(iCol+1)),vtMissing).pdispVal,true);  
			//设置列宽 
			range.put_ColumnWidth(_variant_t((long)nWidth));
			//range.AutoFit();
			range.put_RowHeight(_variant_t(13.5));
		}
	}
	else
	{
		for(iCol=0;iCol<m_cols;iCol++)//将列表的标题头写入EXCEL
		{
			pDlg->GetCellName(1,iCol+1,colName);
			range = sheet.get_Range(COleVariant(colName),COleVariant(colName));
			pmyHeaderCtrl->GetItem(iCol,&hdi);
			range.put_Value2(COleVariant(hdi.pszText));
			long nWidth = pDlg->ui_ListCtrlDataResult.GetColumnWidth(iCol)/4;
			//得到第iCol+1列  
			range.AttachDispatch(range.get_Item(_variant_t((long)(iCol+1)),vtMissing).pdispVal,true);  
			//设置列宽 
			range.put_ColumnWidth(_variant_t((long)nWidth));
			//range.AutoFit();
			range.put_RowHeight(_variant_t(13.5));
		}
	}
	range = sheet.get_Range(COleVariant( _T("A1 ")),COleVariant(colName));
	range.put_RowHeight(_variant_t((long)14));//设置行的高度
	font = range.get_Font();
	font.put_Italic(_variant_t("Arial"));//
	font.put_Bold(covTrue);
	range.put_VerticalAlignment(COleVariant((long)-4108));//垂直对齐
	//range.put_HorizontalAlignment(COleVariant((long)-4108));//水平对齐
	COleSafeArray saRet;
	DWORD numElements[]={m_rows,m_cols};   
	saRet.Create(VT_BSTR,2,numElements);
	try
	{
		range = sheet.get_Range(COleVariant( _T("A2 ")),covOptional);
		range = range.get_Resize(COleVariant((long)m_rows),COleVariant((long)m_cols));
		long index[2];
		range = sheet.get_Range(COleVariant( _T("A2 ")),covOptional);
		range = range.get_Resize(COleVariant((long)m_rows),COleVariant((long)m_cols));
		if(g_curQueryDataResult.m_strExportFileName.Find("生产明细数据")>-1)
		{
			for(iRow = 1; iRow <= m_rows; iRow++)//将列表内容写入EXCEL
			{
				exportPro.dCurNum=iRow;
				for(iCol = 1;iCol<=m_cols;iCol++)  
				{
					index[0]=iRow-1;
					index[1]=iCol-1;
					CString szTemp;
					szTemp=g_curQueryDataResult.m_vecDataResult[iRow-1][iCol-1];
					BSTR bstr = szTemp.AllocSysString();
					saRet.PutElement(index,bstr);
					SysFreeString(bstr);
				}
			}
		}
		else
		{
			for(iRow = 1; iRow <= m_rows; iRow++)//将列表内容写入EXCEL
			{
				exportPro.dCurNum=iRow;
				for(iCol = 1;iCol<=m_cols;iCol++)  
				{
					index[0]=iRow-1;
					index[1]=iCol-1;
					CString szTemp;
					szTemp=pDlg->ui_ListCtrlDataResult.GetItemText(iRow-1,iCol-1);
					BSTR bstr = szTemp.AllocSysString();
					saRet.PutElement(index,bstr);
					SysFreeString(bstr);
				}
			}
		}
		range.put_Value2(COleVariant(saRet));
		//设置单元格格式
		range.put_RowHeight(_variant_t(13.5));//磅
		font = range.get_Font();
		font.put_Name(_variant_t("Arial"));//字体
		font.put_Size(_variant_t(10));
		 cols = range.get_EntireColumn();
			 cols.AutoFit();
	}
	catch(...)
	{
		TRACE("出现错误");
	}
	saRet.Detach();
	book.SaveCopyAs(COleVariant(strFile));
	book.put_Saved(true);
	exportPro.nExportState=1;
	book.ReleaseDispatch();  
	books.ReleaseDispatch();  
	app.Quit();
	app.ReleaseDispatch();
	pDlg->ShowWindow(true);
	return 0;
}

void CLDITxtQueryDlg::GetCellName(int nRow, int nCol, CString &strName)
{
	int nSeed = nCol;
	CString strRow;
	CString temp1;
	CString temp2;
	CString temp3;
	if((nCol-1)/26>0)//超过Z1，应该变成AA1，AB1
	{
		int c1=(nCol-1)/26-1;
		int c2=(nCol-1)%26;
		char cCell1='A'+c1;
		char cCell2='A'+c2;
		temp1.Format(_T("%c"), cCell1);
		temp2.Format(_T("%c"), cCell2);
		temp3.Format(_T( "%d "), nRow);
		strName=temp1+temp2+temp3;
	}
	else
	{
		char cCell = 'A' + nCol - 1;
		strName.Format(_T("%c"), cCell);
		strRow.Format(_T( "%d "), nRow);
		strName += strRow;
	}
}