C++使用VS2010导出Excel的方法及步骤

最新推荐文章于 2018-11-19 18:47:23 发布

sfl8688

最新推荐文章于 2018-11-19 18:47:23 发布

阅读量1.8k

点赞数

分类专栏： vc++ 文章标签： excel c++ 2010 books microsoft mfc

vc++ 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

环境：

Windows7

Microsoft Visual Studio 2010

Microsoft Office Excel 2003

1、新建C++工程

新建自己的C++工程。

2、添加Excel类库

在工程名上右键，选择Add---Class，选择MFC Class From TypeLib，如图：

然后添加如图的几个类。

3、修改头文件

进入刚添加进来的几个类头文件中将#import开头的这句注掉

4、修改提示的错误

将修改过的工程编译一下，出现如下错误：

双击提示，在DialogBox（）前加下划线，如图：

或者改为VARIANT RangeDialogBox()

5、添加头文件

在使用导出功能的文件中添加头文件：

6、使用Excel类库提供的函数将需要导出的数据导出为.xls文件

经过以上几步，现在可以使用Excel类库提供的函数导出数据了。

与网上许多文章相对比一下，导出流程一样。只不过类库函数有所改变。函数名由Get改为get_,Set改为put_。

附录一：导出Excel格式的简单流程

CString strFile = _T("E:\\hanhan.xls");

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(strFile));

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 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(1 ,iCol + 1, colname);

range = sheet.get_Range(COleVariant(colname),COleVariant(colname));

pmyHeaderCtrl-> 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 index[2];

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++)

{

index[0]=iRow-1;

index[1]=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 <= m_rows; iRow++)//设置EXCEL其余的背景颜色（颜色交替变换）

// {

// int state=iRow%2;

// CString index;

// index.Format( _T("A%d "),iRow+1);

// range = sheet.get_Range(COleVariant(index),covOptional);

// range = range.get_Resize(COleVariant((short)1),COleVariant((short)m_cols));

// books=range.get_Interior();

// cellinterior.AttachDispatch(books);

// if(!state)

// cellinterior.put_ColorIndex(COleVariant((short)36));

// else

// cellinterior.put_ColorIndex(COleVariant((short)24));

// }

saRet.Detach();

book.SaveCopyAs(COleVariant(strFile));

// cellinterior.ReleaseDispatch();

book.put_Saved(true);

book.ReleaseDispatch();

books.ReleaseDispatch();

app.Quit();

app.ReleaseDispatch();

由于不需要，Excel单元格样式及颜色等属性设置没有做，有兴趣的可以自己去试试。代码中设置的有错。

自己写的一个获取单元头的函数：

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

{

int nSeed = nCol;

CString strRow;

char cCell = 'A' + nCol - 1;

strName.Format(_T("%c"), cCell);

strRow.Format(_T( "%d "), nRow);

strName += strRow;

}

补充：

通过类型库接口，将ListCtrl导出为Excel2003，并实现Excel多种功能：

ExportToExcel()
{
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;
   CRange cols;
   CRange resizedrange;
   Cnterior interior;
   CBorders borders;
   CBorder bottomborder;
   CPageSetup myPage;
   CWindow0 myWin;

   // 建立Excel2003服务器.
   if(!app.CreateDispatch("Excel.Application"))
   {
      AfxMessageBox("无法建立Excel2003服务器！");
      return;
   }

   //获取 workbook.
   books = app.get_Workbooks();
   book = books.Add (covOptional);

   //获取第一个 sheet.
   sheets =book.get_Sheets();
   sheet = sheets.get_Item(COleVariant((short)1));
   sheet.put_Name("表1");

   //填写标题.
   range = sheet.get_Range(COleVariant("A1"),COleVariant("A1"));
   range.put_Value2(COleVariant("列1"));
   range = sheet.get_Range(COleVariant("B1"),COleVariant("B1"));
   range.put_Value2(COleVariant("列2"));
   range = sheet.get_Range(COleVariant("C1"),COleVariant("C1"));
   range.put_Value2(COleVariant("列3"));
   range = sheet.get_Range(COleVariant("D1"),COleVariant("D1"));
   range.put_Value2(COleVariant("列4"));
   range = sheet.get_Range(COleVariant("E1"),COleVariant("E1"));
   range.put_Value2(COleVariant("列5"));

   //设置标题粗体.
   range = sheet.get_Range(COleVariant("A1"), COleVariant("E1"));
   font = range.get_Font();
   font.put_Bold(covTrue);
   font.put_Name(COleVariant("仿宋_GB2312"));
   font.put_Size(COleVariant(short(14)));
   range.put_RowHeight(COleVariant(short(35)));   //设置行高

   //导入数据.
   CString cellNo;
   char cellChar;
   char jump;
   int nItem=m_list.GetItemCount();
   for(int i=0;i<nItem;i++)
   {
    cellChar='A';    ;
    for(int k=0;k<5;k++)
    {
       //添加数据
     cellNo.Format("%s%d",CString(cellChar),i+1）;
     range = sheet.get_Range(COleVariant(cellNo),COleVariant(cellNo));
     range.put_Value2(COleVariant(m_list.GetItemText(i,k+1)));

    cellChar++;
    }
   }
   // 自适应窗格大小
   cellNo.Format("%s%d","E",nItem+1);
   range = sheet.get_Range(COleVariant("A1"), COleVariant(cellNo));
   cols = range.get_EntireColumn();
   cols.AutoFit();

//添加线条.
range = sheet.get_Range(COleVariant("A1"), COleVariant(cellNo));
range.put_VerticalAlignment(COleVariant((short)-4108));   //xlVAlignCenter = -4108
range.put_HorizontalAlignment(COleVariant((short)-4108));
borders = range.get_Borders();
borders.put_Weight(COleVariant((short)2));   //xlThin = 2
range = sheet.get_Range(COleVariant("A2"), COleVariant(cellNo));
range.put_RowHeight(COleVariant(short(18)));   //设置行高

//设置页脚页眉及标题
myPage=sheet.get_PageSetup();
myPage.put_HeaderMargin(1/0.035);              //设置上下及页眉边距，1/0.035:
myPage.put_TopMargin(2.7/0.035);               //将1厘米转换成 points点数
myPage.put_BottomMargin(1.5/0.035);
myPage.put_CenterHeader("&\"黑体,常规\"&24测试表"); // 需要用斜杠将引号转义，VB中用"转义
myPage.put_PrintTitleRows("$1:$1");            //1 2行作为必打项
myPage.put_PrintTitleColumns("$A:$B");         //A B列作为必打项
myPage.put_Orientation(2);//xlLandscape=2 横向

   //冻结窗格
   range = sheet.get_Range(COleVariant("C2"), COleVariant("C2"));
   range.Select();
   myWin=app.get_ActiveWindow();
   myWin.put_FreezePanes(1);
   //显示Excel程序
   app.put_Visible(TRUE);
   app.put_UserControl(TRUE);
}

sfl8688

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
C++使用VS2010导出Excel的方法及步骤

环境：Windows7Microsoft Visual Studio 2010Microsoft Office Excel 20031、新建C++工程新建自己的C++工程。2、添加Excel类库在工程名上右键，选择Add---Class，选择MFC Class From TypeLib，如图：然后添加如图的几个类。 3、修改头文件
复制链接

扫一扫