VC中操作excel表格 ListCtrl数据导出到Excel

1.从Office的安装目录中找到对VC操作excel文件的动态库，在某些版本下这个文件是Excel8.olb或者Excel9.olb，在我的版本中是excel.exe这个exe也是动态库的形式，是微软公司主要的文件结果之一。选择vc的View菜单里面的ClassWizad命令，会弹出一个对话框；然后点击Add Class…按钮选择From a type library，会弹出一个打开对话框，从这里打开Office安装目录下…/Office11/EXCEL.EXE文件，从里面选择几个要用到的类：_Application, Workbooks, _Wrokbook, Worksheets, _WorkSheet, Range，点击OK按钮。会在程序中生成一个excel.h和excel.cpp文件，这些文件中包含了刚才我们选择的几个类的代码。

2.在App类中的InitInstance函数最前面加入AfxOleInit()函数初始化组件。

static void GetCellName(int nRow, int nCol, CString &strName)

{
CString strRow;
char cCell = (char)('A' + nCol - 1);

strName.Format(_T("%c"), cCell);
strRow.Format(_T( "%d "), nRow);
strName += strRow;
}

void CBqglDlg::OnBnClickedButtonSc()
{

//CFileDialog hFileDlg(false,L"xlsx",NULL,OFN_FILEMUSTEXIST | OFN_READONLY | OFN_PATHMUSTEXIST, NULL,NULL);

_Application app;
_Workbook book;
Workbooks books;
_Worksheet sheet;
Worksheets sheets;
Range range;
Font font;

//BOOL pp=app.CreateDispatch(L"Excel.Application");

if(!app.CreateDispatch(L"Excel.Application"))//判断是否存在EXCEL应用程序
{
AfxMessageBox(L"没有发现EXCEL应用程序");
return;
}
CFileDialog hFileDlg(false,NULL,NULL,OFN_FILEMUSTEXIST | OFN_READONLY | OFN_PATHMUSTEXIST, TEXT("Excel文件 (.xlsx)|.xlsx|.xls||"),NULL);


if(hFileDlg.DoModal() == IDOK)
{

COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);

app.SetVisible(FALSE);
app.SetUserControl(FALSE);

LPDISPATCH pwkbooks=app.GetWorkbooks();
books.AttachDispatch(pwkbooks,TRUE);
books = app.GetWorkbooks();
book = books.Add(covOptional);
sheets=book.GetWorksheets();
sheet = sheets.GetItem(COleVariant((short)1));
//添加工作表的名称
CTime tt=CTime::GetCurrentTime();
CString strtime=tt.Format("%Y-%m-%d-%H-%M-%S");
LPCTSTR st=strtime;
sheet.SetName(st);

VARIANT varUnit;
VariantInit(&varUnit);
varUnit.vt = VT_I4;
varUnit.lVal = 15;

range = sheet.GetRange(COleVariant(_T("A1")),COleVariant(_T("F1")));
range.Merge(covOptional);
font = range.GetFont();
font.SetName(COleVariant(_T("黑体")));
font.SetSize(varUnit);
range.SetValue2(COleVariant(_T("标签信息")));
varUnit.lVal = -4108;//水平：-4131靠左；-4152靠右。垂直：-4160靠上；-4107靠下。
range.SetHorizontalAlignment(varUnit); //xlCenter 水平居中
range.SetVerticalAlignment(varUnit);//xlCenter 垂直居中

CHeaderCtrl *pmyHeaderCtrl;
pmyHeaderCtrl = m_bqlist.GetHeaderCtrl();//此句取得CListCtrl控件的列表頭
int iRow,iCol;
int m_cols = pmyHeaderCtrl-> GetItemCount();
int m_rows = m_bqlist.GetItemCount();
HDITEM hdi;
TCHAR lpBuffer[256];
bool fFound = false;
hdi.mask = HDI_TEXT;
hdi.pszText = lpBuffer;
hdi.cchTextMax = 256;
CString colname;
CString strTemp;
for(iCol=0; iCol <m_cols; iCol++)//将列表的标题头写入EXCEL
{
GetCellName(2 ,iCol + 1, colname);
range = sheet.GetRange(COleVariant(colname),COleVariant(colname));
pmyHeaderCtrl-> GetItem(iCol, &hdi);
range.SetValue2(COleVariant(hdi.pszText));
int nWidth = m_bqlist.GetColumnWidth(iCol)/6;
//得到第iCol+1列
range.AttachDispatch(range.GetItem(COleVariant((long)(iCol+1)),covOptional).pdispVal,true);
//设置列宽
range.SetColumnWidth(COleVariant((long)nWidth));
}

// range = sheet.GetRange(COleVariant( _T("A1 ")), COleVariant(colname));
//range.SetRowHeight(COleVariant((long)50));//设置行的高度
// font = range.GetFont();
// font.SetBold(covTrue);
range.SetVerticalAlignment(COleVariant((short)-4108));//xlVAlignCenter = -410
COleSafeArray saRet;
DWORD numElements[]={m_rows,m_cols}; //5x2 element array
saRet.Create(VT_BSTR, 2, numElements);
range = sheet.GetRange(COleVariant( _T("A3 ")),covOptional);
range = range.GetResize(COleVariant((short)m_rows),COleVariant((short)m_cols));
long index[2];
range = sheet.GetRange(COleVariant( _T("A3 ")),covOptional);
range = range.GetResize(COleVariant((short)m_rows),COleVariant((short)m_cols));
for (iRow = 1; iRow<= m_rows;iRow++)//将列表内容写入EXCEL
{
for(iCol = 1;iCol<= m_cols; iCol++)
{
index[0]=iRow-1;
index[1]=iCol-1;
CString szTemp;
szTemp=m_bqlist.GetItemText(iRow-1,iCol-1);
BSTR bstr = szTemp.AllocSysString();
saRet.PutElement(index,bstr);
SysFreeString(bstr);
}
}
range.SetValue2(COleVariant(saRet));
saRet.Detach();

//app.SetVisible(TRUE);
//保存
//CString strFile = _T("D:\\WriteToExcelTest.xlsx");
//CString strFile;
/*strFile = "D:\\";
strFile +=strtime;
strFile +=".xlsx";*/

CString strFile;
strFile = hFileDlg.GetPathName();
int sel=hFileDlg.m_ofn.nFilterIndex;
if(sel==1)
{
strFile +=".xlsx";
}
else
strFile +=".xls";

//strExtName=hFileDlg.GetFileExt();
//strFile = strFile+strExtName;
UpdateData(TRUE);

//book.SaveAs(COleVariant(strFile),covOptional, covOptional, covOptional, covOptional, covOptional, 0, covOptional, covOptional, covOptional, covOptional ,covOptional);

book.SaveCopyAs(COleVariant(strFile));
book.SetSaved(true);
AfxMessageBox(L"保存成功！");


//结尾，释放
range.ReleaseDispatch(); // 释放Range对象
sheet.ReleaseDispatch(); // 释放Sheet对象
sheets.ReleaseDispatch(); // 释放Sheets对象
book.ReleaseDispatch();
books.ReleaseDispatch();
book.Close (covOptional, covOptional,covOptional);// 关闭Workbook对象
books.Close(); // 关闭Workbooks对象

app.Quit();
app.ReleaseDispatch();

}

}