AE读excel及输出excel表

最新推荐文章于 2020-07-14 19:02:39 发布

PuttyTree

最新推荐文章于 2020-07-14 19:02:39 发布

阅读量4.2k

点赞数 1

分类专栏： jVC++ jArcGIS.Engine 文章标签： excel merge application null 网格 c

本文链接：https://blog.csdn.net/PuttyTree/article/details/4442071

版权

jVC++ 同时被 2 个专栏收录

24 篇文章 0 订阅

订阅专栏

jArcGIS.Engine

15 篇文章 0 订阅

订阅专栏

【注】真正要用AE接口读入excel表可能要用到IPersistStream接口，本代码纯为赝品，只是把用AE接口把每个excel表格画出来然后组合在一起，但缺少能读入excel。

一：读入excel数据

CString sExcelPath;

IGroupElementPtr pGroupEle;

double dWidth = 1.0,dHeight = 0.5;

double dTextSize = 10;

IGraphicsContainerPtr pGraphicsContainer;

IPageLayoutControlPtr pPageCtrl;

pGroupEle.CreateInstance(__uuidof(GroupElement));

CloneExcelData(sExcelPath,pGroupEle,pPoint,dWidth,dHeight,dTextSize,dExpand);

pGraphicsContainer =pMap; //pPagelayout = pPageCtrl->PageLayout;
  pEle = pGroupEle;
  pElementProperties = pEle;
pElementProperties->PutName(_bstr_t("ExcelData"));
  pGraphicsContainer->AddElement(pEle,0);
  pGraphicsContainer->UpdateElement(pEle);
  pAV->PartialRefresh(esriViewGeography,NULL,NULL);

说明： sExcelPath：Excel路径

pGroupEle：组合元素接口

pPtStart ：显示Excel的开始点

dWidth ，dHeight ：Excel的格子宽、高度

dTextSize：字体大小

void CloneExcelData(CString sExcelPath,IGroupElementPtr &pGroupEle,IPointPtr pPtStart,double dWidth ,double dHeight ,double dTextSize )
{

IRgbColorPtr         pRgb;
ISimpleLineSymbolPtr pSimLineSym;
IPolylinePtr         pPolyline;
IPointPtr            pPtFrom,pPtTo;
IFillSymbolPtr       pFillSymbol;
IFillShapeElementPtr pFillShpEle;
ILineElementPtr      pLineElement;
IEnvelopePtr         pEnvGrid;
IGeometryPtr         pGeometry;
IElementPtr          pElement;
ITextSymbolPtr       pTextSymbol;
CString              sText;
ITextElementPtr      pTextElement;
IPointPtr            pPtText;
IEnvelopePtr         pEnvText;
IPointPtr            pPtGrid;
IPointCollectionPtr pPtColl;
IFontPtr             pFont;

HRESULT hr = S_FALSE;
pFont.CreateInstance(CLSID_StdFont);
pFont->put_Name((_bstr_t)(LPCTSTR)"宋体_GB2321");
pFont->put_Italic(FALSE);
pFont->put_Bold(FALSE);
pPolyline.CreateInstance(__uuidof(polyline));

pPtColl = pPolyline;

pTextSymbol.CreateInstance(__uuidof(TextSymbol));
pTextSymbol->PutFont((IFontDispPtr)pFont);
pTextSymbol->put_Size(dTextSize);
pTextSymbol->PutHorizontalAlignment(esriTHACenter);
pTextSymbol->PutVerticalAlignment(esriTVACenter);

pRgb.CreateInstance(__uuidof(RgbColor));
pRgb->PutRed(0);
pRgb->PutGreen(0);
pRgb->PutBlue(0);

pSimLineSym.CreateInstance(__uuidof(SimpleLineSymbol));
pSimLineSym->put_Width(0.2); //设置边线宽度
pSimLineSym->PutColor(pRgb);//设置边线的颜色

pRgb.CreateInstance(__uuidof(RgbColor));
pRgb->PutTransparency(0);//填充色透明

pFillSymbol.CreateInstance(__uuidof(SimpleFillSymbol));//将线类型属性放置到填充类型中
pFillSymbol->PutOutline(pSimLineSym);
pFillSymbol->PutColor(pRgb);//设置填充色

pFillShpEle.CreateInstance(__uuidof(RectangleElement));
pFillShpEle->put_Symbol(pFillSymbol);

Excel::_ApplicationPtr ptrExcelApp;
Excel::WorkbooksPtr    pBooks;
Excel::_WorkbookPtr    ptrBook;
Excel::_WorksheetPtr   ptrSheet;
Excel::RangePtr        ptrRange;
Excel::RangePtr        ptrResizeRange;
Excel::RangePtr        ptrCols;

ptrExcelApp.CreateInstance(L"Excel.Application");
pBooks = ptrExcelApp->Workbooks;

pBooks->Open(_bstr_t(sExcelPath));

ptrSheet = ptrExcelApp->ActiveSheet;

int iCol = ptrSheet->GetUsedRange()->GetColumns()->GetCount();
int iRow = ptrSheet->GetUsedRange()->GetRows()->GetCount();

_variant_t   var;
    CString      sRange;
CString      sValue;
    BOOL         bNoneMerge = TRUE;
CStringArray sGridArr;

//画横的矩形边框
for (int i=0; i<2; i++)
{
  if ( pPtColl->GetPointCount() > 0)
  {
   pPtColl->RemovePoints(0,pPtColl->GetPointCount());

  }
  pPtFrom.CreateInstance(__uuidof(Point));
  pPtFrom->PutX(pPtStart->GetX());
  pPtFrom->PutY(pPtStart->GetY() - i*iRow*dHeight);
  pPtColl->AddPoint(pPtFrom);

  pPtTo.CreateInstance(__uuidof(Point));
  pPtTo->PutX(pPtFrom->GetX() + iCol*dWidth);
  pPtTo->PutY(pPtFrom->GetY());
  pPtColl->AddPoint(pPtTo);
  pPolyline = pPtColl;

pLineElement.CreateInstance(__uuidof(LineElement));
pLineElement->put_Symbol(pSimLineSym);

  pGeometry = pPolyline;
  pElement = pLineElement;
  hr = pElement->put_Geometry(pGeometry);
  if( !FAILED(hr))
   pGroupEle->AddElement(pElement);
}
//画竖的矩形边框
for ( int i=0; i<2; i++)
{
  if ( pPtColl->GetPointCount() > 0)
  {
   pPtColl->RemovePoints(0,pPtColl->GetPointCount());

  }
  pPtFrom.CreateInstance(__uuidof(Point));
  pPtFrom->PutX(pPtStart->GetX() + i*dWidth*iCol);
  pPtFrom->PutY(pPtStart->GetY());
  pPtColl->AddPoint(pPtFrom);

  pPtTo.CreateInstance(__uuidof(Point));
  pPtTo->PutX(pPtFrom->GetX());
  pPtTo->PutY(pPtFrom->GetY() - iRow*dHeight);
  pPtColl->AddPoint(pPtTo);
  pPolyline = pPtColl;

pLineElement.CreateInstance(__uuidof(LineElement));
pLineElement->put_Symbol(pSimLineSym);

  pGeometry = pPolyline;
  pElement = pLineElement;
  hr = pElement->put_Geometry(pGeometry);
  if( !FAILED(hr))
   pGroupEle->AddElement(pElement);

}

//显示数据
for ( int i=0; i<iRow; i++)
{
  pPtGrid.CreateInstance(__uuidof(Point));
  pPtGrid->PutCoords(pPtStart->GetX(),pPtStart->GetY()-(i+1)*dHeight);
  for ( int j=0; j<iCol; j++)
  {
   bNoneMerge = TRUE;
   FormatRangeName(i,j,sRange);
   var   = ptrSheet->Range[_variant_t(sRange)]->Value;
   sText = (LPCTSTR)(_bstr_t)var;

   for(int no=0;no<sGridArr.GetSize();no++)
    if(sRange==sGridArr.GetAt(no))
    {
     bNoneMerge=FALSE;
     break;
    }

   if ( bNoneMerge)
   {

    int merge_rows=1;
    int merge_cols=1;
                int     nRangeCount = 0;
    _variant_t vResult=ptrSheet->Range[_variant_t(sRange)]->GetMergeCells(); //获得合并区域。
    if(vResult.boolVal=-1)
    {
     CString strc;
                    pEnvGrid.CreateInstance(__uuidof(Envelope));
     merge_rows=ptrSheet->Range[_variant_t(sRange)]->GetMergeArea()->GetRows()->GetCount();
     merge_cols=ptrSheet->Range[_variant_t(sRange)]->GetMergeArea()->GetColumns()->GetCount();
     for(int i2=0;i2<merge_rows;i2++)
     {
      for(int j2=0;j2<merge_cols;j2++)
      {

       if(i2==0 && j2==0)
        continue;
       FormatRangeName(i+i2,j+j2,sRange);
       sGridArr.Add(sRange);
       nRangeCount++;
      }

      画合并区右侧的竖线
      if ( pPtColl->GetPointCount() > 0)
      {
       pPtColl->RemovePoints(0,pPtColl->GetPointCount());

      }
      pPtFrom.CreateInstance(__uuidof(Point));
      pPtFrom->PutX(pPtGrid->GetX() + (j+merge_cols)*dWidth);
      pPtFrom->PutY(pPtGrid->GetY() + dHeight - i2*dHeight);
      pPtColl->AddPoint(pPtFrom);

      pPtTo.CreateInstance(__uuidof(Point));
      pPtTo->PutX(pPtFrom->GetX());
      pPtTo->PutY(pPtFrom->GetY() - dHeight);
      pPtColl->AddPoint(pPtTo);
      pPolyline = pPtColl;

pLineElement.CreateInstance(__uuidof(LineElement));
pLineElement->put_Symbol(pSimLineSym);

      pGeometry = pPolyline;
      pElement = pLineElement;
      hr = pElement->put_Geometry(pGeometry);
      if( !FAILED(hr))
       pGroupEle->AddElement(pElement);

     }
     //画合并区底侧横线
     if ( pPtColl->GetPointCount() > 0)
     {
      pPtColl->RemovePoints(0,pPtColl->GetPointCount());

     }
     pPtFrom.CreateInstance(__uuidof(Point));
     pPtFrom->PutX(pPtGrid->GetX() + j*dWidth);
     pPtFrom->PutY(pPtGrid->GetY() - (merge_rows-1)*dHeight);
     pPtColl->AddPoint(pPtFrom);

     pPtTo.CreateInstance(__uuidof(Point));
     pPtTo->PutX(pPtFrom->GetX() +merge_cols*dWidth);
     pPtTo->PutY(pPtFrom->GetY());

pPtColl->AddPoint(pPtTo);
pPolyline = pPtColl;

pLineElement.CreateInstance(__uuidof(LineElement));
pLineElement->put_Symbol(pSimLineSym);

     pGeometry = pPolyline;
     pElement = pLineElement;
     hr = pElement->put_Geometry(pGeometry);
     if( !FAILED(hr))
      pGroupEle->AddElement(pElement);

//得到Excel合并网格的矩形范围
pEnvGrid->PutCoords(pPtFrom->GetX(),pPtFrom->GetY(),pPtTo->GetX(),pPtTo->GetY()+merge_rows*dHeight);

}

if( FALSE)//nRangeCount <= 0)
{//画线

     //画横线
     if ( pPtColl->GetPointCount() > 0)
     {
      pPtColl->RemovePoints(0,pPtColl->GetPointCount());

     }
     pPtFrom.CreateInstance(__uuidof(Point));
     pPtFrom->PutX(pPtGrid->GetX() + j*dWidth);
     pPtFrom->PutY(pPtGrid->GetY());
     pPtColl->AddPoint(pPtFrom);

     pPtTo.CreateInstance(__uuidof(Point));
     pPtTo->PutX(pPtFrom->GetX() + dWidth);
     pPtTo->PutY(pPtFrom->GetY());
     pPtColl->AddPoint(pPtTo);
     pPolyline = pPtColl;

pLineElement.CreateInstance(__uuidof(LineElement));
pLineElement->put_Symbol(pSimLineSym);

     pGeometry = pPolyline;
     pElement = pLineElement;
     hr = pElement->put_Geometry(pGeometry);
     if( !FAILED(hr))
      pGroupEle->AddElement(pElement);

     //画竖线
     pPtColl->RemovePoints(0,pPtColl->GetPointCount());

     pPtFrom.CreateInstance(__uuidof(Point));
     pPtFrom->PutX(pPtGrid->GetX() + (j+1)*dWidth);
     pPtFrom->PutY(pPtGrid->GetY() + dHeight);
     pPtColl->AddPoint(pPtFrom);

     pPtTo.CreateInstance(__uuidof(Point));
     pPtTo->PutX(pPtFrom->GetX());
     pPtTo->PutY(pPtGrid->GetY());
     pPtColl->AddPoint(pPtTo);
     pPolyline = pPtColl;

pLineElement.CreateInstance(__uuidof(LineElement));
pLineElement->put_Symbol(pSimLineSym);

     pGeometry = pPolyline;
     pElement = pLineElement;
     hr = pElement->put_Geometry(pGeometry);
     if( !FAILED(hr))
      pGroupEle->AddElement(pElement);
    }

    //显示标注
    int nLength = sText.GetLength();
    double dEnvWidth = pEnvGrid->GetWidth();
    double dTextLength = dTextSize*nLength;

    pTextElement.CreateInstance(__uuidof(TextElement));

    pTextElement->PutText(_bstr_t(sText));
    pTextElement->PutSymbol(pTextSymbol);

    pPtText.CreateInstance(__uuidof(Point));
    pPtText->PutCoords((pEnvGrid->GetXMax()+pEnvGrid->GetXMin())/2,(pEnvGrid->GetYMax()+pEnvGrid->GetYMin())/2 );//+ 0.25*dExpand
    pElement = pTextElement;
    pElement->PutGeometry(pPtText);
    pGroupEle->AddElement(pElement);
   }

}
}
pBooks->Close();
ptrExcelApp->Quit();

}

BOOL FormatRangeName(int nItem,int nSubItem,CString &sRangeName)
{
     nSubItem++;
    CString s = "";
    CString sTemp;
   do
   {
          nSubItem--;

sTemp.Format("%c",nSubItem%26 + 'A');
s = sTemp + s;

          nSubItem/=26;
   }
    while(nSubItem>0);
    sRangeName.Format("%s%d",s,nItem+1);

return TRUE;

}

BOOL FormatColName(int nSubItem,CString &sColName)
{
     nSubItem++;
     CString s = "";
   CString sTemp;
    do
   {
      nSubItem--;

sTemp.Format("%c",nSubItem%26 + 'A');
s = sTemp + s;

       nSubItem/=26;
   }
    while(nSubItem>0);

sColName = s;

return TRUE

}

二：输出Excel

#include <atlsafe.h>

CExcel::ExportToExcel(CString sExcelPath)
{

Excel::_ApplicationPtr ptrExcelApp;
Excel::_WorkbookPtr ptrBook;
Excel::_WorksheetPtr ptrSheet;
Excel::RangePtr ptrRange;
Excel::RangePtr ptrResizeRange;
Excel::RangePtr ptrCols;

try
{

    INT_PTR nRows = 10,nCols = 9;
    // SetTableColNameByType(spTb,m_sLayoutType);
    //AccumulateTable(spTb,m_sLayoutType);

//
   ptrExcelApp.CreateInstance(__uuidof(Excel::Application));
   if(ptrExcelApp == NULL) return FALSE;

ptrBook = ptrExcelApp->Workbooks->Open((_bstr_t)sExcelPath);
ptrSheet = ptrExcelApp->ActiveSheet;

    //A1-- H1：表头部分
    CString sValue = _T("");
    ptrRange = ptrSheet->GetRange(_variant_t("A1"), _variant_t("H1"));
    ptrRange->Merge();

    sValue.Format("Excel表头部分");
    ptrRange->Value = (LPCTSTR)sValue;
   ptrRange->HorizontalAlignment = (short)Excel::xlVAlignCenter;
    ptrRange->VerticalAlignment   = (short)Excel::xlVAlignCenter;

    //H2---I2：面积单位部分
    CString sColRange = _T("");
    ptrRange = ptrSheet->GetRange(_variant_t("H2"), _variant_t("I2"));
    ptrRange->Merge();

CString sAreaUnit = _T("平方米");
sValue.Format("面积单位:%s",sAreaUnit);

   ptrRange->Value = (LPCTSTR)sValue;
   ptrRange->Font->Size = (short)9;
   ptrRange->HorizontalAlignment = (short)Excel::xlVAlignCenter;
   ptrRange->VerticalAlignment   = (short)Excel::xlVAlignCenter;

  //填充表格
   DWORD nIndex[2];
   nIndex[0] = nRows;
   nIndex[1] = nCols;
   CComSafeArrayBound bound[2];

   bound[0].SetCount( nRows);
   bound[0].SetLowerBound( 0 );
   bound[1].SetCount( nCols );
   bound[1].SetLowerBound( 0 );
   CComSafeArray<BSTR> saData( bound, 2 );

   CString sItemValue = _T("");
   _bstr_t bsColName;
   CString sRowName,sColName;

for( nIndex[0]=0; nIndex[0] < nRows; nIndex[0]++ )
   {
    for( nIndex[1] = 0; nIndex[1] < nCols; nIndex[1]++ )
     {
       /*

CMap<int,int,CString,LPCTSTR> i_sColMap;//列名记录
CMap<int,int,CString,LPCTSTR> i_sRowMap;//行名记录

       */
        i_sRowMap.Lookup(nIndex[0],sRowName);  //取得行名
        i_sColMap.Lookup(nIndex[1],sColName);//取得列名
        sItemValue = (LPCTSTR)spTb->GetItem((LPCTSTR)sRowName,(LPCTSTR)sColName);//得到i，j数据
        // sItemValue.TrimRight('0');

        saData.MultiDimSetAt((long*)nIndex,sItemValue.AllocSysString());
     }
   }

ptrRange = ptrSheet->GetRange( _variant_t("A5"), _variant_t("A5") );//作为写的起始格

   if(nRows > 0 && nCols >0)
   {

ptrResizeRange = ptrRange->GetResize( _variant_t((long)nRows), _variant_t((long)nCols) );

     _variant_t vArray;
     vArray.vt = VT_ARRAY | VT_BSTR;
     vArray.parray = saData.Detach();
     ptrResizeRange->Value = vArray;
     ptrResizeRange->HorizontalAlignment = (short)Excel::xlVAlignCenter;
     ptrResizeRange->VerticalAlignment = (short)Excel::xlVAlignCenter;
     ptrResizeRange->Font->Size = (short)9;
     ptrResizeRange->Borders->Weight = (short)Excel::xlThin;

     ptrResizeRange = ptrRange->GetResize( vtMissing, _variant_t((long)nCols) );
     ptrResizeRange->Font->Bold = FALSE;
     ptrResizeRange->Font->Size = (short)9;
     ptrCols = ptrResizeRange->GetEntireColumn();
     ptrCols->AutoFit();
     ptrResizeRange->HorizontalAlignment = (short)Excel::xlHAlignCenter;
     ptrResizeRange->VerticalAlignment = (short)Excel::xlVAlignCenter;
     ///

}

//
ptrExcelApp->PutVisible( VARIANT_TRUE);

return TRUE;

}
   catch( _com_error& ce )
   {
     if( 0x800A03EC != ce.Error() )
    {
       CString strMsg;
       strMsg.Format( _T("Code: 0x%x/nMessage: %s"),
       ce.Error(), ce.ErrorMessage() );
       AfxMessageBox( strMsg );
     }
    }

return TRUE;
}

PuttyTree

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
AE读excel及输出excel表

【注】真正要用AE接口读入excel表可能要用到IPersistStream接口，本代码纯为赝品，只是把用AE接口把每个excel表格画出来然后组合在一起，但缺少能读入excel。一：读入excel数据 CString sExcelPath; IGroupElementPtr pGroupEle; double
复制链接

扫一扫

专栏目录