線上訂房服務-台灣趴趴狗聯合訂房中心
發文 回覆 瀏覽次數:10689
推到 Plurk!
推到 Facebook!

使用OLE,在EXCEL中自動產生圖表、背景,並設定保護模式

 
wivern
初階會員


發表:31
回覆:63
積分:28
註冊:2002-07-31

發送簡訊給我
#1 引用回覆 回覆 發表時間:2003-11-14 14:19:02 IP:61.219.xxx.xxx 未訂閱
之前在站上看了許多 使用 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
 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) ;
後面五個參數, 第一個是解開保護的Password第二個是設定是否要保護sheet上面的 DrawingObjects,像是圖表 第三個是設定是否要保護sheet的Contents,設true的話,使用者不能修改sheet的內容 第四個和第五個是設定是否要保護sheet的Scenarios和UserInterfaceOnly, 不過我不是很清這兩個是保護什麼!
Worksheet.OlePropertySet("EnableSelection",xlUnlockedCells);
這是要保護使用者連選取cell都不可以, 不過我發現這個保護只能在用OLE製作EXCEL時有效, 如果製作完,把EXCEL關掉,再打開,就又可以選取cell了, 可能有什麼步驟沒做好吧~ 以上是我使用OLE的一些心得,還請各位多多指教! 發表人 - wivern 於 2003/11/14 14:28:59
wivern
初階會員


發表:31
回覆:63
積分:28
註冊:2002-07-31

發送簡訊給我
#2 引用回覆 回覆 發表時間:2003-11-17 13:54:02 IP:61.219.xxx.xxx 未訂閱
有一點很重要的事忘了提,程式中用到的那些常數,像是xlRows、xlcolumns、xlUnlockedCells等, 還有各種圖表格式的定義,以及OLE所提供的所有函數名稱, 都是定義在excel_2k.h這個標頭檔裡, 所以程式前面要加上
#include 
要不然執行時會出現沒有定義變數這個錯誤, 我很多功能都是看這個標頭檔學來的, 如果想進一步了解OLE 製作Excel還有哪裡功能, 可以試著看看這個檔,不過不容易看~我知道! 像是如果想看>"這個字串, 就可以找到 class="code"> 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這些檔, 不過這些有哪些功能不一樣我就不清楚了! 發表人 -
北方佬
一般會員


發表:15
回覆:35
積分:10
註冊:2005-07-27

發送簡訊給我
#3 引用回覆 回覆 發表時間:2005-08-24 08:56:23 IP:210.243.xxx.xxx 未訂閱
請問wivern大大,可以將.bmp的小畫家圖檔貼到指定的sheet與格子中嗎?其指令又該如何下呢?因為我收尋找不到相關的資訊,因此到這來詢問。麻煩您了。
gaiazhang
一般會員


發表:2
回覆:7
積分:1
註冊:2009-02-09

發送簡訊給我
#4 引用回覆 回覆 發表時間:2010-08-05 17:50:05 IP:222.92.xxx.xxx 訂閱
我也有同样的问题,怎样把图片贴到excel中呢?请帮忙指点一下吧
------
Gaia
gaiazhang
一般會員


發表:2
回覆:7
積分:1
註冊:2009-02-09

發送簡訊給我
#5 引用回覆 回覆 發表時間:2010-08-09 14:57:58 IP:222.92.xxx.xxx 訂閱
自己回答下这个问题
[code cpp]
if(OpenDialog1->Execute()==1 )
{

String strXlsFile=OpenDialog1->FileName.c_str();
String strImgFile="D:\\ROHS.bmp ";
Variant vExcelApp=CreateOleObject("Excel.Application");
vExcelApp.OlePropertySet("Visible", true);
vExcelApp.OlePropertyGet( "WorkBooks") .OleFunction( "Open",strXlsFile.c_str());
vExcelApp.OlePropertyGet( "Cells",3,4) .OleFunction("Select");
vExcelApp.OlePropertyGet( "ActiveSheet") .OlePropertyGet( "Pictures").OleFunction( "Insert",strImgFile.c_str());

}

[/code]

------
Gaia
系統時間:2024-03-29 18:48:58
聯絡我們 | Delphi K.Top討論版
本站聲明
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。
2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。
3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇!