VC——操作excel

最新推荐文章于 2020-07-20 16:50:53 发布

wlq5800

最新推荐文章于 2020-07-20 16:50:53 发布

阅读量903

点赞数

分类专栏： VC数据块操作文章标签： excel application 服务器 null

本文链接：https://blog.csdn.net/wlq5800/article/details/6551512

版权

VC数据块操作专栏收录该内容

9 篇文章 0 订阅

订阅专栏

首先添加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();