一、导入对应的类（）
二、开启线程
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;
}
}
