自己派生了个ListCtrl控件。写了个函数,用来将控件中的数据保存到EXCEL中。
函数代码如下:
SaveToExcel()
{
CFileDialog FileDialog(FALSE,//Save;
"xls",NULL,OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,"Microsoft Excel 2000 (*.xls)|*.xls|所有文件(*.*)|*.*||",this);
if(FileDialog.DoModal()!=IDOK)
return;
CString strFile=FileDialog.GetPathName();
if(::PathFileExists(strFile))
DeleteFile(strFile);
///
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
_Application app;
Workbooks books;
_Workbook book;
Worksheets sheets;
_Worksheet sheet;
Range range;
Font font;
// Start Excel and get Application object.
if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't start Excel and get Application object.");
return;
}
//Get a new workbook.
books = app.GetWorkbooks();
book = books.Add (covOptional);
//Get the first sheet.
sheets =book.GetSheets();
sheet = sheets.GetItem(COleVariant((short)1));
CHeaderCtrl *pmyHeaderCtrl;
pmyHeaderCtrl=GetHeaderCtrl();//此句取得CListCtrl控件的列表頭
int iRow,iCol;
int m_cols = pmyHeaderCtrl->GetItemCount();
int m_rows=GetItemCount();
HDITEM hdi;
TCHAR lpBuffer[256];
bool fFound = false;
hdi.mask = HDI_TEXT;
hdi.pszText = lpBuffer;
hdi.cchTextMax = 256;
char name='A'-1;
CString colname;
for(iCol=0; iCol<m_cols; iCol++)//将列表的标题头写入EXCEL
{
name+=1;
colname.Format("%c1",name);
range = sheet.GetRange(COleVariant(colname),COleVariant(colname));
pmyHeaderCtrl->GetItem(iCol, &hdi);
range.SetValue(COleVariant(hdi.pszText));
int nWidth = GetColumnWidth(iCol)/6;
//得到第iCol+1列
range.AttachDispatch(range.GetItem(_variant_t((long)(iCol+1)),vtMissing).pdispVal,true);
//设置列宽
range.SetColumnWidth(_variant_t((long)nWidth));
}
range = sheet.GetRange(COleVariant("A1"), COleVariant(colname));
range.SetRowHeight(_variant_t((long)50));//设置第一行的高度
font = range.GetFont();
font.SetBold(covTrue);
range.SetVerticalAlignment(COleVariant((short)-4108)); //xlVAlignCenter = -4108
COleSafeArray saRet;
DWORD numElements[]={m_rows,m_cols}; //5x2 element array
saRet.Create(VT_BSTR, 2, numElements);
range = sheet.GetRange(COleVariant("A2"),covOptional);
range = range.GetResize(COleVariant((short)m_rows),COleVariant((short)m_cols));
long index[2];
range = sheet.GetRange(COleVariant("A2"),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=GetItemText(iRow-1,iCol-1);
BSTR bstr = szTemp.AllocSysString();
saRet.PutElement(index,bstr);
SysFreeString(bstr);
}
}
range.SetValue(COleVariant(saRet));
Interior cellinterior;
range = sheet.GetRange(COleVariant("A1"),covOptional);
range = range.GetResize(COleVariant((short)1),COleVariant((short)m_cols));
books=range.GetInterior();
cellinterior.AttachDispatch(books);
cellinterior.SetColorIndex(COleVariant((short)37));//设置EXCEL头一行的背景颜色
for( iRow=1; iRow <= m_rows; iRow++)//设置EXCEL其余的背景颜色(颜色交替变换)
{
int state=iRow%2;
CString index;
index.Format("A%d",iRow+1);
range = sheet.GetRange(COleVariant(index),covOptional);
range = range.GetResize(COleVariant((short)1),COleVariant((short)m_cols));
books=range.GetInterior();
cellinterior.AttachDispatch(books);
if(!state)
cellinterior.SetColorIndex(COleVariant((short)36));
else
cellinterior.SetColorIndex(COleVariant((short)24));
}
saRet.Detach();
book.SaveCopyAs(COleVariant(strFile));
cellinterior.ReleaseDispatch();
book.SetSaved(true);
book.ReleaseDispatch();
books.ReleaseDispatch();
app.Quit();
app.ReleaseDispatch();
}
函数代码如下:
SaveToExcel()
{
CFileDialog FileDialog(FALSE,//Save;
"xls",NULL,OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,"Microsoft Excel 2000 (*.xls)|*.xls|所有文件(*.*)|*.*||",this);
if(FileDialog.DoModal()!=IDOK)
return;
CString strFile=FileDialog.GetPathName();
if(::PathFileExists(strFile))
DeleteFile(strFile);
///
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
_Application app;
Workbooks books;
_Workbook book;
Worksheets sheets;
_Worksheet sheet;
Range range;
Font font;
// Start Excel and get Application object.
if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't start Excel and get Application object.");
return;
}
//Get a new workbook.
books = app.GetWorkbooks();
book = books.Add (covOptional);
//Get the first sheet.
sheets =book.GetSheets();
sheet = sheets.GetItem(COleVariant((short)1));
CHeaderCtrl *pmyHeaderCtrl;
pmyHeaderCtrl=GetHeaderCtrl();//此句取得CListCtrl控件的列表頭
int iRow,iCol;
int m_cols = pmyHeaderCtrl->GetItemCount();
int m_rows=GetItemCount();
HDITEM hdi;
TCHAR lpBuffer[256];
bool fFound = false;
hdi.mask = HDI_TEXT;
hdi.pszText = lpBuffer;
hdi.cchTextMax = 256;
char name='A'-1;
CString colname;
for(iCol=0; iCol<m_cols; iCol++)//将列表的标题头写入EXCEL
{
name+=1;
colname.Format("%c1",name);
range = sheet.GetRange(COleVariant(colname),COleVariant(colname));
pmyHeaderCtrl->GetItem(iCol, &hdi);
range.SetValue(COleVariant(hdi.pszText));
int nWidth = GetColumnWidth(iCol)/6;
//得到第iCol+1列
range.AttachDispatch(range.GetItem(_variant_t((long)(iCol+1)),vtMissing).pdispVal,true);
//设置列宽
range.SetColumnWidth(_variant_t((long)nWidth));
}
range = sheet.GetRange(COleVariant("A1"), COleVariant(colname));
range.SetRowHeight(_variant_t((long)50));//设置第一行的高度
font = range.GetFont();
font.SetBold(covTrue);
range.SetVerticalAlignment(COleVariant((short)-4108)); //xlVAlignCenter = -4108
COleSafeArray saRet;
DWORD numElements[]={m_rows,m_cols}; //5x2 element array
saRet.Create(VT_BSTR, 2, numElements);
range = sheet.GetRange(COleVariant("A2"),covOptional);
range = range.GetResize(COleVariant((short)m_rows),COleVariant((short)m_cols));
long index[2];
range = sheet.GetRange(COleVariant("A2"),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=GetItemText(iRow-1,iCol-1);
BSTR bstr = szTemp.AllocSysString();
saRet.PutElement(index,bstr);
SysFreeString(bstr);
}
}
range.SetValue(COleVariant(saRet));
Interior cellinterior;
range = sheet.GetRange(COleVariant("A1"),covOptional);
range = range.GetResize(COleVariant((short)1),COleVariant((short)m_cols));
books=range.GetInterior();
cellinterior.AttachDispatch(books);
cellinterior.SetColorIndex(COleVariant((short)37));//设置EXCEL头一行的背景颜色
for( iRow=1; iRow <= m_rows; iRow++)//设置EXCEL其余的背景颜色(颜色交替变换)
{
int state=iRow%2;
CString index;
index.Format("A%d",iRow+1);
range = sheet.GetRange(COleVariant(index),covOptional);
range = range.GetResize(COleVariant((short)1),COleVariant((short)m_cols));
books=range.GetInterior();
cellinterior.AttachDispatch(books);
if(!state)
cellinterior.SetColorIndex(COleVariant((short)36));
else
cellinterior.SetColorIndex(COleVariant((short)24));
}
saRet.Detach();
book.SaveCopyAs(COleVariant(strFile));
cellinterior.ReleaseDispatch();
book.SetSaved(true);
book.ReleaseDispatch();
books.ReleaseDispatch();
app.Quit();
app.ReleaseDispatch();
}