VS2010的导入导出EXCEL.docx

C+使用VS2010导出/导入Excel的方法及步骤最近工作需要将listctrl中的数据导出到Excel中。网上找了很多，但多数是VC6.0的。结合VC6.0导出的方法，自己琢磨了不少时间，终于将问题解决了。轻松之余，将过程分享出来，希望对需要的人有所帮助。VC6.0的请看这里：/t/20050718/15/4151984.html一、设置环境：Windows7Microsoft Visual Studio 2010Microsoft Office Excel 2003说明：用的公司的电脑，操作系统和软件都是正版的。1、新建C+工程新建自己的C+工程。2、添加Excel类库在工程名上右键，选择Add-Class，选择MFC Class From TypeLib，如图：然后添加如图的几个类。3、修改头文件进入刚添加进来的几个类头文件中将#import开头的这句注掉4、修改提示的错误将修改过的工程编译一下，出现如下错误：双击提示，在DialogBox（）前加下划线，如图：5、添加头文件在使用导出功能的文件中添加头文件：6、使用Excel类库提供的函数将需要导出的数据导出为.xls文件经过以上几步，现在可以使用Excel类库提供的函数导出数据了。与网上许多文章相对比一下，导出流程一样。只不过类库函数有所改变。函数名由Get改为get_,Set改为put_。二、导出附录一： 导出Excel格式的简单流程CFileDialog dlg(FALSE,_T(*.xls),NULL,OFN_HIDEREADONLY |OFN_OVERWRITEPROMPT,_T(*.xls)|*.xls|),NULL);if (dlg.DoModal() = IDOK)/获取路径CString strFileName=dlg.GetPathName(); COleVariant covTrue(short)TRUE), covFalse(short)FALSE), covOptional(long)DISP_E_PARAMNOTFOUND, VT_ERROR); CApplication0 app; CWorkbook0 book; CWorkbooks0 books; CWorksheet0 sheet; CWorksheets0 sheets; CRange0 range; CFont1 font; if (!app.CreateDispatch(_T(Excel.Application) MessageBox(_T(Error!); books = app.get_Workbooks(); book = books.Add(covOptional); sheets = book.get_Worksheets(); sheet = sheets.get_Item(COleVariant(short)1); range = sheet.get_Range(COleVariant(_T(A1),COleVariant(_T(A1); range.put_Value2(COleVariant(_T(hanhan); book.SaveCopyAs(COleVariant(strFileName); book.put_Saved(true); book.ReleaseDispatch(); books.ReleaseDispatch(); app.Quit(); app.ReleaseDispatch();附录二：将ListCtrl中的数据导出为Excel格式 CString strFile = _T(E:Test.xls); COleVariant covTrue(short)TRUE), covFalse(short)FALSE), covOptional(long)DISP_E_PARAMNOTFOUND, VT_ERROR); CApplication app; CWorkbooks books; CWorkbook book; CWorksheets sheets; CWorksheet sheet; CRange range; CFont0 font; if (!app.CreateDispatch(_T(Excel.Application) MessageBox(_T(创建失败！); return; /Get a new workbook. books = app.get_Workbooks(); book = books.Add(covOptional); sheets = book.get_Worksheets(); sheet = sheets.get_Item(COleVariant(short)1); /CListCtrl控件report风格/ CHeaderCtrl *pmyHeaderCtrl; pmyHeaderCtrl = m_clcData.GetHeaderCtrl();/此句取得CListCtrl控件的列表頭 int iRow,iCol; int m_cols = pmyHeaderCtrl- GetItemCount(); int m_rows = m_clcData.GetItemCount(); HDITEM hdi; TCHAR lpBuffer256; bool fFound = false; hdi.mask = HDI_TEXT; hdi.pszText = lpBuffer; hdi.cchTextMax = 256; CString colname; CString strTemp; for(iCol=0; iCol GetItem(iCol, &hdi); range.put_Value2(COleVariant(hdi.pszText); int nWidth = m_clcData.GetColumnWidth(iCol)/6; /得到第iCol+1列 range.AttachDispatch(range.get_Item(_variant_t(long)(iCol+1),vtMissing).pdispVal,true); /设置列宽 range.put_ColumnWidth(_variant_t(long)nWidth); range = sheet.get_Range(COleVariant( _T(A1 ), COleVariant(colname); range.put_RowHeight(_variant_t(long)50);/设置行的高度 font = range.get_Font(); font.put_Bold(covTrue); range.put_VerticalAlignment(COleVariant(short)-4108);/xlVAlignCenter = -4108 COleSafeArray saRet; DWORD numElements=m_rows,m_cols; /5x2 element array saRet.Create(VT_BSTR, 2, numElements); range = sheet.get_Range(COleVariant( _T(A2 ),covOptional); range = range.get_Resize(COleVariant(short)m_rows),COleVariant(short)m_cols); long index2; range = sheet.get_Range(COleVariant( _T(A2 ),covOptional); range = range.get_Resize(COleVariant(short)m_rows),COleVariant(short)m_cols); for ( iRow = 1; iRow = m_rows; iRow+)/将列表内容写入EXCEL for ( iCol = 1; iCol = m_cols; iCol+) index0=iRow-1; index1=iCol-1; CString szTemp; szTemp=m_clcData.GetItemText(iRow-1,iCol-1); BSTR bstr = szTemp.AllocSysString(); saRet.PutElement(index,bstr); SysFreeString(bstr); range.put_Value2(COleVariant(saRet);/ Cnterior cellinterior;/ range = sheet.get_Range(COleVariant( _T(A1 ),covOptional);/ range = range.get_Resize(COleVariant(short)1),COleVariant(short)m_cols);/ books = range.get_Interior();/ cellinterior.AttachDispatch(books);/ cellinterior.put_ColorIndex(COleVariant(short)37);/设置EXCEL头一行的背景颜色/ for( iRow=1; iRow MessageBox(_T(无法创建Excel应用);return;books = app.get_Workbooks();/打开Excel，其中pathname为Excel表的路径名lpDisp = books.Open(strFilePath,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional);book.AttachDispatch(lpDisp);sheets = book.get_Worksheets();sheet = sheets.get_Item(COleVariant(short)1);/app.put_Visible(TRUE); /表格可见/app.put_UserControl(FALSE);/获得坐标为（A，1）的单元格/*range = sheet.get_Range(COleVariant(_T(A1),COleVariant(_T(A1);*/获得单元格的内容/COleVariant rValue;rValue =/ COleVariant(range.get_Value2();/转换成宽字符/rValue.ChangeType(VT_BSTR);/转换格式，并输出/this-MessageBox(CString(rValue.bstrVal);/*读取Excel表中的多个单元格的值，在listctrl中显示*/COleVariant vResult;/读取已经使用区域的信息，包括已经使用的行数、列数、起始行、起始列range.AttachDispatch(sheet.get_UsedRange();range.AttachDispatch(range.get_Rows();/取得已经使用的行数long iRowNum = range.get_Count(); range.AttachDispatch(range.get_Columns();/取得已经使用的列数long iColNum = range.get_Count(); /取得已使用区域的起始行，从1开始long iStartRow = range.get_Row();/取得已使用区域的起始列，从1开始long iStartCol = range.get_Column();for(int i=iStartRow;i=iRowNum;i+)/*CString strRowName = _T();strRowName.Format(_T(%d), i);m_ListCtrl.InsertItem(i-1, strRowName);*/for(int j=iStartCol;j=iColNum;j+)/读取单元格的值range.AttachDispatch(sheet.get_Cells();range.AttachDispatch(range.get_Item(COleVariant(long)i),COleVariant(long)j).pdispVal);vResult = range.get_Value2();CString str,stry,strm,strd;SYSTEMTIME st;if(vResult.vt = VT_BSTR) /若是字符串str = vResult.bstrVal;else if (vResult.vt = VT_R8) /8字节的数字str.Format(L%f,vResult.dblVal);else if(vResult.vt = VT_DATE) /时间格式VariantTimeToSystemTime(vResult.date, &st);stry.Format(L%d,st.wYear);strm.Format(L%d,st.wMonth);strd.Format(L%d,st.wDay);str = stry+L-+strm+L-+strd;else if(v