【注】真正要用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;
}