bool CShearDoc::ExportToExcel()
{
/*******************************************************************
ExportToExcel() 中加入一列参数: 列数--->导出数据的个数,
#define DATA_V_PREUS 1 //垂直压力
#define DATA_V_POSI 2 //垂直位移
#define DATA_H_PREUS 4 //水平压力
#define DATA_H_POSI 8 //水平位移
#define DATA_IN_PREUS 16 //进水压力
#define DATA_OUT_PREUS 32 //出水压力
#define DATA_SEEPAGE_RATE 64 //透水率
#define DATA_SEEPAGE_TIME 128 //渗流时间
*******************************************************************/
int i=DATA_V_PREUS;
//
COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
_Application objApp;
_Workbook objBook;
Workbooks objBooks;
Worksheets objSheets;
_Worksheet objSheet;
Range range;
_Chart chart;
ChartObjects chartobjects;
Charts charts;
LPDISPATCH lpDisp;
COleVariant covTrue((short)TRUE),covFalse((short)FALSE),covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
bool m_bFillWithStrings;
//创建Excel 2000服务器(启动Excel)
if (!objApp.CreateDispatch("Excel.Application",NULL))
{
AfxMessageBox("创建Excel服务失败!");
return false;
}
//使用指定的文件名创建一个表
objBooks = objApp.GetWorkbooks();
objBook = objBooks.Add(VOptional);
objSheets = objBook.GetWorksheets();
objSheet = objSheets.GetItem(COleVariant((short)1));
//Get the range where the starting cell has the address
//m_sStartingCell and it's dimensions are m_iNumRows x m_iNumCols.
range = objSheet.GetRange(COleVariant("A1"),VOptional);
//获取范围
range = range.GetResize(COleVariant((long)(m_DataArray.GetSize()+1)),COleVariant((short)7));
//*** Fill the range with an array of values.
//Create the SAFEARRAY.
COleSafeArray saRet;
DWORD numElements[2];
numElements[0]= m_DataArray.GetSize()+1; //Number of rows in the range.
numElements[1]= 7; //Number of columns in the range.
m_bFillWithStrings=false;
if(m_bFillWithStrings)
{
saRet.Create(VT_BSTR, 2, numElements);
}
else
{
saRet.Create(VT_R8, 2, numElements); //VT_R8 dobule
}
//Fill the SAFEARRAY.
long index[2];
long iRow;
long iCol;
//m_DataArray 是文档的保存数据的数组
long m_iNumRows = m_DataArray.GetSize()+1;
long m_iNumCols =7;
for(iRow=0;iRow<=m_iNumRows-2;iRow++)
{
for(iCol=0;iCol<=m_iNumCols-1;iCol++)
{
index[0] = iRow;
index[1] = iCol;
if(m_bFillWithStrings) //Fill with Strings.
{
VARIANT v;
CString s;
VariantInit(&v);
v.vt = VT_BSTR;
s.Format("r%dc%d", iRow, iCol);
v.bstrVal = s.AllocSysString();
saRet.PutElement(index, v.bstrVal);
SysFreeString(v.bstrVal);
VariantClear(&v);
}
else //Fill with Numbers.
{
double d;
switch(iCol)
{
case 0:
d=m_DataArray[iRow].Vertical_Pressure;
break;
case 1:
d=m_DataArray[iRow].Vertical_Position;
break;
case 2:
d=m_DataArray[iRow].level_Pressure;
break;
case 3:
d=m_DataArray[iRow].level_Position;
break;
case 4:
d=m_DataArray[iRow].In_Water_Pressure;
break;
case 5:
d=m_DataArray[iRow].Out_Water_Pressure;
break;
case 6:
d=m_DataArray[iRow].SeePage_Weight;
break;
default:
d = (iRow*1000) + iCol;
break;
}
saRet.PutElement(index, &d);
}
}
}
//Set the range value to the SAFEARRAY.
range.SetValue2(COleVariant(saRet));
saRet.Detach();
//因为将原始数据导入EXCEL后,可以进一步操作,所以这里先只导入原始数据,等客户要求再改变
//图表
long left, top, width, height;
left = 100;
top = 10;
width = 350;
height = 250;
lpDisp = objSheet.ChartObjects(covOptional);
ASSERT(lpDisp);
chartobjects.AttachDispatch(lpDisp); // Attach the lpDisp pointer
// for ChartObjects to the chartobjects
// object.
ChartObject chartobject = chartobjects.Add(left, top, width, height);
//defines the rectangle,
// adds a new chart at that rectangle and
// assigns its object reference to a
// ChartObject variable named chartobject
chart.AttachDispatch(chartobject.GetChart()); // GetChart() returns
// LPDISPATCH, and this attaches
// it to your chart object.
lpDisp = objSheet.GetRange(COleVariant("A1:A7815"), COleVariant("E1:E7815"));
// The range containing the data to be charted.
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
VARIANT var; // ChartWizard needs a Variant for the Source range.
var.vt = VT_DISPATCH; // .vt is the usable member of the tagVARIANT
// Struct. Its value is a union of options.
var.pdispVal = lpDisp; // Assign IDispatch pointer
// of the Source range to var.
/*
chart.ChartWizard(var, // Source.
COleVariant((short)4), // Gallery: 3d Column. xlXYScatterSmoothNoMarkers=73 2d=
covOptional, // Format, use default.
COleVariant((short)2), // PlotBy: xlRows.=1行 列xlColums =2
COleVariant((short)0), // CategoryLabels.
COleVariant((short)1), // SeriesLabels.
COleVariant((short)TRUE), // HasLegend.
COleVariant("Use by Month"), // Title.
COleVariant("Month"), // CategoryTitle.
COleVariant("Usage in Thousands"), // ValueTitles.
covOptional // ExtraTitle.
);
// The return is void.
::Sleep(3000);*/
SeriesCollection serc;
serc=chart.SeriesCollection(covOptional);
Series ser;
ser=serc.NewSeries();
ser.SetType(4);
ser.SetMarkerStyle(0); //无标记
ser.SetSmooth(true);
lpDisp=objSheet.GetRange(COleVariant("A1"), COleVariant("A7815"));
var.pdispVal = lpDisp;
ser.SetXValues(var);
lpDisp=objSheet.GetRange(COleVariant("E2"), COleVariant("E7815"));
var.pdispVal = lpDisp;
ser.SetValues(var);
//
//Return control of Excel to the user.
objApp.SetVisible(TRUE);
objApp.SetUserControl(TRUE);
return false;
}