Fans Tech: May 2008

在EXCEL中自动产生图表、背景并设定保护模式
http://www.st0754.net/dispQAInfo.php?id=398
2005-09-06 13:13:48

之前在站上看了许多使用 OLE 来制作 Excel的文章，大部份都是在讲如何填入数据，如何设定字段的格式等，
小弟因工作需要，必须在excel中产生图表，在网络上找了许多数据，也有了一些心得，
就在此抛砖引玉，提供一些小弟个人的心得，
因为站上很多如何填数据的文章，所以小弟就省略这一部份，
假设网友们已经填好数据，直接进入要产生图表的部份，
//一开始先定义OLE会用到的变量，建立excel的对象
Variant Excel,Workbooks,Workbook,Charts,Chart,Range;
Excel=CreateOleObject("Excel.Application");
Excel.OlePropertySet("Visible",true); //秀出Excel的画面
Workbooks=Excel.OlePropertyGet("Workbooks");
Workbook=Workbooks.OleFunction("Add");
Worksheets=Workbook.OlePropertyGet("Worksheets");
Worksheet=Worksheets.OleFunction("Add"); // 新增一worksheet
Worksheet.OlePropertySet("Name","test") ;// 设定worksheet 的名称

//取得Charts的对象
Charts=Workbook.OlePropertyGet("Charts");
.
.
. 先把所需资料填入cell中
.
.

//建立一个新的图表，这时图表会开在一个新的sheet里，而整个sheet就是一个图表
//之后会把图表放在其它的sheet里
Chart=Charts.OleFunction("Add");

//设定图表的格式，65是表示含有数据标记的折线图，后面会有图表格式的说明
Chart.OlePropertySet("ChartType",65);

//选定要制作图表的数据来源，这个例子我是要选择excel中从cell(1,1)到cell(10,5)的资料，
//此动作就好像用鼠标把这一部份的字段选起来一样
Range=Worksheet.OlePropertyGet("Range",
Worksheet.OlePropertyGet("Cells",1,1),
Worksheet.OlePropertyGet("Cells",10,5));

//设定图表的数据来源，xlRows表示数据是以列来做排列，如果资料是用行来排列，就用xlColumns，
//如果是用xlRows，选取范围的第一列就是x轴每一笔数据的名称，
//第一行是y轴数据的名称，其余的就是图表里的数值
Chart.OleProcedure("SetSourceData",Range,xlRows);

//设定图表的title
Chart.OlePropertySet("HasTitle",(Variant)true); //先设定图表有title
String Charttitle="test chart!!";
Chart.OlePropertyGet("ChartTitle").OlePropertySet("Text",Charttitle.c_str());

//设定图表x轴和y轴是否要秀出title和资料名称
Chart.OlePropertyGet("Axes",1).OlePropertySet("HasTitle",(Variant)true); //设定x轴是否要显示资料名称
Chart.OlePropertyGet("Axes",1).OlePropertyGet("AxisTitle").OlePropertySet("Text","时间");//设定x轴的title
Chart.OlePropertyGet("Axes",2).OlePropertySet("HasTitle",(Variant)true); //设定y轴是否要显示资料名称
Chart.OlePropertyGet("Axes",2).OlePropertyGet("AxisTitle").OlePropertySet("Text","次数");//设定y轴的title

//设定图表要放在哪一个sheet里

Chart.OleProcedure("Location",2,Worksheet.OlePropertyGet("Name"));

//设定图表的位置与长宽，
//Worksheet.OlePropertyGet("ChartObjects",1)表示是要设定这个sheet中第一个图表，
//如要设定第二个图表就用Worksheet.OlePropertyGet("ChartObjects",2)，
//图表的顺序依在sheet中产生或放置的先后排列

Worksheet.OlePropertyGet("ChartObjects",1).OlePropertySet("Top",15);
Worksheet.OlePropertyGet("ChartObjects",1).OlePropertySet("Left",20);
Worksheet.OlePropertyGet("ChartObjects",1).OlePropertyGet("Width",50);
Worksheet.OlePropertyGet("ChartObjects",1).OlePropertyGet("Height",30);

//此时图表已经漂漂亮亮的在sheet中?!

底下列出图表格式常数的定义:
typedef enum XlChartType
{
xlColumnClustered = 51,
xlColumnStacked = 52,
xlColumnStacked100 = 53,
xl3DColumnClustered = 54,
xl3DColumnStacked = 55,
xl3DColumnStacked100 = 56,
xlBarClustered = 57,
xlBarStacked = 58,
xlBarStacked100 = 59,
xl3DBarClustered = 60,
xl3DBarStacked = 61,
xl3DBarStacked100 = 62,
xlLineStacked = 63,
xlLineStacked100 = 64,
xlLineMarkers = 65,
xlLineMarkersStacked = 66,
xlLineMarkersStacked100 = 67,
xlPieOfPie = 68,
xlPieExploded = 69,
xl3DPieExploded = 70,
xlBarOfPie = 71,
xlXYScatterSmooth = 72,
xlXYScatterSmoothNoMarkers = 73,
xlXYScatterLines = 74,
xlXYScatterLinesNoMarkers = 75,
xlAreaStacked = 76,
xlAreaStacked100 = 77,
xl3DAreaStacked = 78,
xl3DAreaStacked100 = 79,
xlDoughnutExploded = 80,
xlRadarMarkers = 81,
xlRadarFilled = 82,
xlSurface = 83,
xlSurfaceWireframe = 84,
xlSurfaceTopView = 85,
xlSurfaceTopViewWireframe = 86,
xlBubble = 15,
xlBubble3DEffect = 87,
xlStockHLC = 88,
xlStockOHLC = 89,
xlStockVHLC = 90,
xlStockVOHLC = 91,
xlCylinderColClustered = 92,
xlCylinderColStacked = 93,
xlCylinderColStacked100 = 94,
xlCylinderBarClustered = 95,
xlCylinderBarStacked = 96,
xlCylinderBarStacked100 = 97,
xlCylinderCol = 98,
xlConeColClustered = 99,
xlConeColStacked = 100,
xlConeColStacked100 = 101,
xlConeBarClustered = 102,
xlConeBarStacked = 103,
xlConeBarStacked100 = 104,
xlConeCol = 105,
xlPyramidColClustered = 106,
xlPyramidColStacked = 107,
xlPyramidColStacked100 = 108,
xlPyramidBarClustered = 109,
xlPyramidBarStacked = 110,
xlPyramidBarStacked100 = 111,
xlPyramidCol = 112,
xl3DColumn = 0xFFFFEFFC,
xlLine = 4,
xl3DLine = 0xFFFFEFFB,
xl3DPie = 0xFFFFEFFA,
xlPie = 5,
xlXYScatter = 0xFFFFEFB7,
xl3DArea = 0xFFFFEFFE,
xlArea = 1,
xlDoughnut = 0xFFFFEFE8,
xlRadar = 0xFFFFEFC9
} XlChartType;
小弟尝试的结果，列出部份常数的意思，其余的就看有没有人补充?!
5:2D的饼图(Pie图)
16:泡泡图
51:2D的柱形图
52:2D的堆栈柱形图
53:2D的100%堆栈柱形图
54:3D的柱形图
55:3D的堆栈柱形图
56:3D的100%堆栈柱形图
57:2D的横条图
58:2D的堆栈横条图
59:2D的100%堆栈横条图
60:3D的横条图
61:3D的堆栈横条图
62:3D的100%堆栈横条图
63:折线图
65:含有数据标志的折线图
接下来设定EXCEL的背景图案
String BmpPath;
BmpPath="C:\background.bmp";
if(FileExists(BmpPath))
Worksheet.OleFunction("SetBackgroundPicture",BmpPath.c_str());
保护sheet
Worksheet.OleFunction("Protect","password",true,true,true,true) ;
Protect函式的定义如下:
template HRESULT /*[VT_HRESULT:0]*/ __fastcall
_WorksheetDispT::Protect(VARIANT Password/*[in,opt]*/, VARIANT DrawingObjects/*[in,opt]*/,
VARIANT Contents/*[in,opt]*/, VARIANT Scenarios/*[in,opt]*/,
VARIANT UserInterfaceOnly/*[in,opt]*/)
{
_TDispID _dispid(*this, OLETEXT("Protect"), DISPID(282));
TAutoArgs<5> _args;
_args[1] = Password /*[VT_VARIANT:0]*/;
_args[2] = DrawingObjects /*[VT_VARIANT:0]*/;
_args[3] = Contents /*[VT_VARIANT:0]*/;
_args[4] = Scenarios /*[VT_VARIANT:0]*/;
_args[5] = UserInterfaceOnly /*[VT_VARIANT:0]*/;
return OleFunction(_dispid, _args);
}

共有五个参数，第一个是解开保护的Password，
第二个是设定是否要保护sheet上面的 DrawingObjects，像是图表
第三个是设定是否要保护sheet的Contents，设true的话，使用者不能修改sheet的内容
第四个和第五个是设定是否要保护sheet的Scenarios和UserInterfaceOnly，
不过我不是很清这两个是保护什么!
Worksheet.OlePropertySet("EnableSelection",xlUnlockedCells);

这是要保护使用者连选取cell都不可以，
不过我发现这个保护只能在用OLE制作EXCEL时有效，
如果制作完，把EXCEL关掉，再打开，就又可以选取cell了，
可能有什么步骤没做好吧~
-----------------------------------------------------
有一点很重要，程序中用到的那些常数，像是xlRows、xlcolumns、xlUnlockedCells等，
还有各种图表格式的定义，以及OLE所提供的所有函数名称，
都是定义在excel_2k.h这个标头档里，
所以程序前面要加上
#include

要不然执行时会出现没有定义变量这个错误，
我很多功能都是看这个标头档学来的，
如果想进一步了解OLE 制作Excel还有哪里功能，
可以试着看看这个档，不过不容易看~我知道!
像是如果想看Chart还有提供什么操作，
可以把这个档打开，搜寻"_ChartDispT"这个字符串，
就可以找到Chart还有哪些属性和功能~
不过我的程序没办法直接include这个档，
所以我把我的程序中会用到的常数从excel_2k.h这个档中复制到一个自订的.h?，再include这个档~我自订的.h内容是:

typedef enum Constants
{
xlAll = 0xFFFFEFF8,
xlAutomatic = 0xFFFFEFF7,
xlBoth = 1,
xlCenter = 0xFFFFEFF4,
xlChecker = 9,
xlCircle = 8,
xlCorner = 2,
xlCrissCross = 16,
xlCross = 4,
xlDiamond = 2,
xlDistributed = 0xFFFFEFEB,
xlDoubleAccounting = 5,
xlFixedValue = 1,
xlFormats = 0xFFFFEFE6,
xlGray16 = 17,
xlGray8 = 18,
xlGrid = 15,
xlHigh = 0xFFFFEFE1,
xlInside = 2,
xlJustify = 0xFFFFEFDE,
xlLightDown = 13,
xlLightHorizontal = 11,
xlLightUp = 14,
xlLightVertical = 12,
xlLow = 0xFFFFEFDA,
xlManual = 0xFFFFEFD9,
xlMinusValues = 3,
xlModule = 0xFFFFEFD3,
xlNextToAxis = 4,
xlNone = 0xFFFFEFD2,
xlNotes = 0xFFFFEFD0,
xlOff = 0xFFFFEFCE,
xlOn = 1,
xlPercent = 2,
xlPlus = 9,
xlPlusValues = 2,
xlSemiGray75 = 10,
xlShowLabel = 4,
xlShowLabelAndPercent = 5,
xlShowPercent = 3,
xlShowValue = 2,
xlSimple = 0xFFFFEFC6,
xlSingle = 2,
xlSingleAccounting = 4,
xlSolid = 1,
xlSquare = 1,
xlStar = 5,
xlStError = 4,
xlToolbarButton = 2,
xlTriangle = 3,
xlGray25 = 0xFFFFEFE4,
xlGray50 = 0xFFFFEFE3,
xlGray75 = 0xFFFFEFE2,
xlBottom = 0xFFFFEFF5,
xlLeft = 0xFFFFEFDD,
xlRight = 0xFFFFEFC8,
xlTop = 0xFFFFEFC0,
xl3DBar = 0xFFFFEFFD,
xl3DSurface = 0xFFFFEFF9,
xlBar = 2,
xlColumn = 3,
xlCombination = 0xFFFFEFF1,
xlCustom = 0xFFFFEFEE,
xlDefaultAutoFormat = 0xFFFFFFFF,
xlMaximum = 2,
xlMinimum = 4,
xlOpaque = 3,
xlTransparent = 2,
xlBidi = 0xFFFFEC78,
xlLatin = 0xFFFFEC77,
xlContext = 0xFFFFEC76,
xlLTR = 0xFFFFEC75,
xlRTL = 0xFFFFEC74,
xlFullScript = 1,
xlPartialScript = 2,
xlMixedScript = 3,
xlMixedAuthorizedScript = 4,
xlVisualCursor = 2,
xlLogicalCursor = 1,
xlSystem = 1,
xlPartial = 3,
xlHindiNumerals = 3,
xlBidiCalendar = 3,
xlGregorian = 2,
xlComplete = 4,
xlScale = 3,
xlClosed = 3,
xlColor1 = 7,
xlColor2 = 8,
xlColor3 = 9,
xlConstants = 2,
xlContents = 2,
xlBelow = 1,
xlCascade = 7,
xlCenterAcrossSelection = 7,
xlChart4 = 2,
xlChartSeries = 17,
xlChartShort = 6,
xlChartTitles = 18,
xlClassic1 = 1,
xlClassic2 = 2,
xlClassic3 = 3,
xl3DEffects1 = 13,
xl3DEffects2 = 14,
xlAbove = 0,
xlAccounting1 = 4,
xlAccounting2 = 5,
xlAccounting3 = 6,
xlAccounting4 = 17,
xlAdd = 2,
xlDebugCodePane = 13,
xlDesktop = 9,
xlDirect = 1,
xlDivide = 5,
xlDoubleClosed = 5,
xlDoubleOpen = 4,
xlDoubleQuote = 1,
xlEntireChart = 20,
xlExcelMenus = 1,
xlExtended = 3,
xlFill = 5,
xlFirst = 0,
xlFloating = 5,
xlFormula = 5,
xlGeneral = 1,
xlGridline = 22,
xlIcons = 1,
xlImmediatePane = 12,
xlInteger = 2,
xlLast = 1,
xlLastCell = 11,
xlList1 = 10,
xlList2 = 11,
xlList3 = 12,
xlLocalFormat1 = 15,
xlLocalFormat2 = 16,
xlLong = 3,
xlLotusHelp = 2,
xlMacrosheetCell = 7,
xlMixed = 2,
xlMultiply = 4,
xlNarrow = 1,
xlNoDocuments = 3,
xlOpen = 2,
xlOutside = 3,
xlReference = 4,
xlSemiautomatic = 2,
xlShort = 1,
xlSingleQuote = 2,
xlStrict = 2,
xlSubtract = 3,
xlTextBox = 16,
xlTiled = 1,
xlTitleBar = 8,
xlToolbar = 1,
xlVisible = 12,
xlWatchPane = 11,
xlWide = 3,
xlWorkbookTab = 6,
xlWorksheet4 = 1,
xlWorksheetCell = 3,
xlWorksheetShort = 5,
xlAllExceptBorders = 6,
xlLeftToRight = 2,
xlTopToBottom = 1,
xlVeryHidden = 2,
xlDrawingObject = 14
} Constants;

typedef enum XlBorderWeight
{
xlHairline = 1,
xlMedium = 0xFFFFEFD6,
xlThick = 4,
xlThin = 2
} XlBorderWeight;

typedef enum XlLineStyle
{
xlContinuous = 1,
xlDash = 0xFFFFEFED,
xlDashDot = 4,
xlDashDotDot = 5,
xlDot = 0xFFFFEFEA,
xlDouble = 0xFFFFEFE9,
xlSlantDashDot = 13,
xlLineStyleNone = 0xFFFFEFD2
} XlLineStyle;

typedef enum XlEditionOptionsOption
{
xlAutomaticUpdate = 4,
xlCancel = 1,
xlChangeAttributes = 6,
xlManualUpdate = 5,
xlOpenSource = 3,
xlSelect = 3,
xlSendPublisher = 2,
xlUpdateSubscriber = 2
} XlEditionOptionsOption;

typedef enum XlChartType
{
xlColumnClustered = 51,
xlColumnStacked = 52,
xlColumnStacked100 = 53,
xl3DColumnClustered = 54,
xl3DColumnStacked = 55,
xl3DColumnStacked100 = 56,
xlBarClustered = 57,
xlBarStacked = 58,
xlBarStacked100 = 59,
xl3DBarClustered = 60,
xl3DBarStacked = 61,
xl3DBarStacked100 = 62,
xlLineStacked = 63,
xlLineStacked100 = 64,
xlLineMarkers = 65,
xlLineMarkersStacked = 66,
xlLineMarkersStacked100 = 67,
xlPieOfPie = 68,
xlPieExploded = 69,
xl3DPieExploded = 70,
xlBarOfPie = 71,
xlXYScatterSmooth = 72,
xlXYScatterSmoothNoMarkers = 73,
xlXYScatterLines = 74,
xlXYScatterLinesNoMarkers = 75,
xlAreaStacked = 76,
xlAreaStacked100 = 77,
xl3DAreaStacked = 78,
xl3DAreaStacked100 = 79,
xlDoughnutExploded = 80,
xlRadarMarkers = 81,
xlRadarFilled = 82,
xlSurface = 83,
xlSurfaceWireframe = 84,
xlSurfaceTopView = 85,
xlSurfaceTopViewWireframe = 86,
xlBubble = 15,
xlBubble3DEffect = 87,
xlStockHLC = 88,
xlStockOHLC = 89,
xlStockVHLC = 90,
xlStockVOHLC = 91,
xlCylinderColClustered = 92,
xlCylinderColStacked = 93,
xlCylinderColStacked100 = 94,
xlCylinderBarClustered = 95,
xlCylinderBarStacked = 96,
xlCylinderBarStacked100 = 97,
xlCylinderCol = 98,
xlConeColClustered = 99,
xlConeColStacked = 100,
xlConeColStacked100 = 101,
xlConeBarClustered = 102,
xlConeBarStacked = 103,
xlConeBarStacked100 = 104,
xlConeCol = 105,
xlPyramidColClustered = 106,
xlPyramidColStacked = 107,
xlPyramidColStacked100 = 108,
xlPyramidBarClustered = 109,
xlPyramidBarStacked = 110,
xlPyramidBarStacked100 = 111,
xlPyramidCol = 112,
xl3DColumn = 0xFFFFEFFC,
xlLine = 4,
xl3DLine = 0xFFFFEFFB,
xl3DPie = 0xFFFFEFFA,
xlPie = 5,
xlXYScatter = 0xFFFFEFB7,
xl3DArea = 0xFFFFEFFE,
xlArea = 1,
xlDoughnut = 0xFFFFEFE8,
xlRadar = 0xFFFFEFC9
} XlChartType;

typedef enum XlChartItem
{
xlDataLabel = 0,
xlChartArea = 2,
xlSeries = 3,
xlChartTitle = 4,
xlWalls = 5,
xlCorners = 6,
xlDataTable = 7,
xlTrendline = 8,
xlErrorBars = 9,
xlXErrorBars = 10,
xlYErrorBars = 11,
xlLegendEntry = 12,
xlLegendKey = 13,
xlShape = 14,
xlMajorGridlines = 15,
xlMinorGridlines = 16,
xlAxisTitle = 17,
xlUpBars = 18,
xlPlotArea = 19,
xlDownBars = 20,
xlAxis = 21,
xlSeriesLines = 22,
xlFloor = 23,
xlLegend = 24,
xlHiLoLines = 25,
xlDropLines = 26,
xlRadarAxisLabels = 27,
xlNothing = 28,
xlLeaderLines = 29,
xlDisplayUnitLabel = 30,
xlPivotChartFieldButton = 31,
xlPivotChartDropZone = 32
} XlChartItem;
typedef enum XlRowCol
{
xlColumns = 2,
xlRows = 1
} XlRowCol;

typedef enum XlEnableSelection
{
xlNoRestrictions = 0,
xlUnlockedCells = 1,
xlNoSelection = 0xFFFFEFD2
} XlEnableSelection;
typedef enum XlOrientation
{
xlDownward = 0xFFFFEFB6,
xlHorizontal = 0xFFFFEFE0,
xlUpward = 0xFFFFEFB5,
xlVertical = 0xFFFFEFBA
} XlOrientation;

这样就可以用这些常数了，
像是设定图表格式时，可以用xlLineMarkers来取代65~
如果不include也可以，就用数字来代替常数，
像是xlRows就是1，xlColumns是2，xlUnlockedCells是1等等
Borland除了excel_2k.h这个档之外，还有excel_97.h，excel_xp.h这些档，
不过这些有哪些功能不一样我就不清楚了!

Fans Tech

Tuesday, May 13, 2008

在EXCEL中自动产生图表、背景并设定保护模式

Monday, May 12, 2008

Tech-On!特集パソコンかケータイか，Eee PCとiPhoneの間に見えてきた大市場

ノープログラミングバッチ

Toolbox

Followers

Labels

Blog Archive

Blogger Syntax Highliter

Copyright