VC–操作excel

现在的位置: 首页 > 自动控制 > 工业·编程 > 正文

RSS

上篇下篇

2012-07-08 21:38 ⁄ 工业·编程 ⁄ 共 3585字 ⁄ 字号小中大 ⁄ 暂无评论

首先添加excel9.h和excel9.cpp到你的工程下http://download.csdn.net/source/3372984

在要操作excel的cpp文件中

#include "excel.h"

使用方法如下：

1.初始化

//打开Excel模板
      _xls_Application ExcelApp;
Workbooks Excelbooks;
_Workbook Excelbook;
Worksheets Excelsheets;
COleVariant vResult;
_Worksheet sheet;
COleVariant covTrue((short)TRUE);
COleVariant covFalse((short)FALSE);
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
if (!ExcelApp.CreateDispatch("Excel.Application"))
{
AfxMessageBox("无法启动Excel服务器!");
break;
}
ExcelApp.SetVisible(FALSE);         //使Excel不可见
ExcelApp.SetUserControl(TRUE);      //允许其它用户控制Excel
ExcelApp.SetDisplayAlerts(FALSE);   //不弹出提示
Excelbooks = ExcelApp.GetWorkbooks();
Excelbooks.AttachDispatch(ExcelApp.GetWorkbooks(), true);
Excelbook.AttachDispatch(Excelbooks.Add(COleVariant(pinfo->strExcel)));
Excelsheets.AttachDispatch(Excelbook.GetWorksheets(), true);
int shtCount = Excelsheets.GetCount();
for (short k = 1; k <= shtCount; k++)
{
LPDISPATCH lpDispatch = Excelsheets.GetItem(COleVariant(k));
if (lpDispatch != NULL)
{
   sheet.AttachDispatch(lpDispatch, true);
   CString sheetName = sheet.GetName();//得到sheet的名称
   if (sheetName == "Revision")
   {//excel读取数据
    _xls_Range range,usedRange,rgMyRge;
    int j=0;
    CString strItemName1,strItemName2,strItemName3;
    rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
    VARIANT varItemName;
    usedRange.AttachDispatch(wsMysheet.GetUsedRange());
    rgMyRge.AttachDispatch(usedRange.GetRows());
    long iRowNum = rgMyRge.GetCount();//获得已经使用的行数
    for(j = 6;j<iRowNum;j++)
    {//从第6行开始读取数据
     rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
     cell.AttachDispatch( rgMyRge.GetItem( COleVariant( (long)(j)), COleVariant( (long)1 ) ).pdispVal, TRUE );
     varItemName = cell.GetText();
     strItemName1 = varItemName.bstrVal;
     cell.AttachDispatch( rgMyRge.GetItem( COleVariant( (long)(j)), COleVariant( (long)2 ) ).pdispVal, TRUE );
     varItemName = cell.GetText();
     strItemName2 = varItemName.bstrVal;
     cell.AttachDispatch( rgMyRge.GetItem( COleVariant( (long)(j)), COleVariant( (long)3 ) ).pdispVal, TRUE );
     varItemName = cell.GetText();
     strItemName3 = varItemName.bstrVal;
    }
    //excel写数据，
    rgMyRge.AttachDispatch(wsMysheet.GetCells(), true);
    cell.AttachDispatch(rgMyRge.GetItem(COleVariant((long)(2)),COleVariant((long)(1))).pdispVal,true);
    cell.SetValue2(COleVariant("姓名")); //写在第二行第1列
    cell.ReleaseDispatch();
    cell.AttachDispatch(rgMyRge.GetItem(COleVariant((long)(2)),COleVariant((long)(2))).pdispVal,true);
    cell.SetValue2(COleVariant("学号")); //写在第二行第2列
    cell.ReleaseDispatch();
    cell.AttachDispatch(rgMyRge.GetItem(COleVariant((long)(2)),COleVariant((long)(3))).pdispVal,true);
    cell.SetValue2(COleVariant("分数")); //写在第二行第3列
    cell.ReleaseDispatch();
    rgMyRge.AttachDispatch(wsMysheet.GetUsedRange());//加载已使用的单元格
    rgMyRge.SetWrapText(_variant_t((long)1));//设置单元格内的文本为自动换行
    rgMyRge.SetColumnWidth(_variant_t((long)10));
    _xls_Range cols=rgMyRge.GetEntireRow();
    cols.AutoFit();
    //设置齐方式为水平垂直居中
    //水平对齐：默认＝1,居中＝-4108,左＝-4131,右＝-4152
    //垂直对齐：默认＝2,居中＝-4108,左＝-4160,右＝-4107
    rgMyRge.SetHorizontalAlignment(_variant_t((long)-4108));//
    rgMyRge.SetVerticalAlignment(_variant_t((long)-4108));
    rgMyRge.AttachDispatch(wsMysheet.GetRange(_variant_t("A1"),_variant_t("E1")),TRUE);
    rgMyRge.Merge(_variant_t((long)0));
   }
   wsMysheet.ReleaseDispatch();
}
catch(...)
{
   continue;
}
}
rgMyRge.ReleaseDispatch();
//关闭excel
wbMyBook.SaveAs(COleVariant(fileName),covOptional,covOptional,
covOptional,covOptional,covOptional,long(0),
covOptional,covOptional,covOptional,covOptional); //另存为
wssMysheets.ReleaseDispatch();
wbMyBook.ReleaseDispatch();
wbsMyBooks.Close();
wbsMyBooks.ReleaseDispatch();
ExcelApp.Quit();
ExcelApp.ReleaseDispatch();