由于公司一个项目要用到excel的图表，如是这个任务自然落到我这个全能型码农的身上了（~~~^_^~~~ ，自恋一下）。
实现IDL对excel对图表的操作，因为对IDL编程不熟悉（没有学过，看过几行代码后写过IDL代码而已，任何语言感觉差不多，学了c/c++ 其他的语言都是浮云）。
如是我就先用c#对excel 进行操作，因为简单。（哪个语言对开发东西简单自然用哪个）。

网上例子真少。。。。。可以说对图表几乎没有，只能通过office的宏来分析怎么写代。

1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using MSExcel = Microsoft.Office.Interop.Excel;
6 using System.IO;
7 using System.Reflection;
8
9
10 namespace 操作Excel
11 {
12     class Program
13     {
14         static void Main(string[] args)
15         {
16             MSExcel.Application excelApp;              //Excel应用程序变量
17             MSExcel.Workbooks excelDoc;                     //Excel文档变量
18
19             excelApp = new MSExcel.ApplicationClass();
20             string path = @"F:\23.xls";
21             Object Nothing = Missing.Value;
22             excelDoc = excelApp.Workbooks;
23             excelApp.ShowStartupDialog = true;
24
25
26             MSExcel::Workbook doc = excelDoc.Open(path, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing);
27             if (null == doc)
28             {
29                 return;
30             }
31
32
33             MSExcel.Worksheet ws = (MSExcel.Worksheet)doc.Sheets[1];        //索引从1开始，难道c# 我用c#不熟悉
34
35             try
36             {
37
38                MSExcel.Worksheet ws2 =  ((MSExcel.Worksheet)excelApp.ActiveSheet);
39 //这里我找了好就才知道要用ChartObject 来接，我开始用Chart ,_Chat都不行，注意了
40                MSExcel.ChartObject c = (MSExcel.ChartObject)ws2.ChartObjects("Chart 1");
41                c.Activate();
42                MSExcel.Chart chart1 = excelApp.ActiveChart;
43                Console.WriteLine("{0}", chart1.ChartTitle.Text);
44                chart1.ChartTitle.Text = "hello world";//设置标题
45                 //设置 x,y轴。
46                MSExcel::Axis aa = (MSExcel::Axis)chart1.Axes(2, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
47                //aa.MinimumScale = 341;
48                aa.MaximumScale = 370;
49                MSExcel::Axis bb = (MSExcel::Axis)chart1.Axes(1, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
50                bb.MinimumScale = 0.5;
51
52             }
53             catch (System.Exception ex)
54             {
55                 Console.WriteLine("{0}", ex.Message);
56             }
57
58
59
60
61
62            Console.Read();
63
64
65             //////////////////////////////////////////////////////////////////////////
66             //  对表格进行修改
67            // MSExcel.Range r;
68             // r = ws.get_Range("c5", "c5");
69            // r.Value2 = 1234;
70             //
71             //////////////////////////////////////////////////////////////////////////
72
73             doc.Close(Nothing, Nothing, Nothing);
74             //excelApp.Save(Nothing);
75             excelApp.Quit();
76
77
78
79
80         }
81     }
82 }
83

附上IDL的代码，或许会帮到IDL编程的人。

1 ;-----------------------------------------------------------------
  2 ;   GetAllDataFromCurrentWorksheet Method. Returns all valid, contiguous data
  3 ;       stored in currently selected worksheet
  4 ;
  5 ;     Example:
  6 ;
  7 ;       oIDL_Excel = obj_new("ITCffExcel", fileName, SHEET='irreg_grid1')
  8 ;       data = oIDL_Excel->GetAllDataFromCurrentWorksheet()
  9 ;-
10 FUNCTION ITCffExcel::GetAllDataFromCurrentWorksheet
11
12     COMPILE_OPT IDL2
13
14     On_Error, 2
15
16     IF (Obj_Valid(self.oCellRange)) THEN Obj_Destroy, self.oCellRange
17         self.oSheet->GetProperty, Usedrange = oUsedCells
18         self.oCellRange = oUsedCells
19         self.oCellRange->GetProperty, Value = myValues
20     IF (Size(myValues, /Type) EQ 0) THEN BEGIN ; undefined
21         Message, 'Invalid Range '+range+'? Mixed data types not supported.'+ $
22             ' Data must be numeric and contiguous.', /Ioerror
23     ENDIF ELSE BEGIN
24         ; Rearrange into IDL column-major format so user doesn't have to
25         dims = Size(myValues, /Dimensions)
26         myValues = Transpose(Reform(Temporary(myValues), dims[1], dims[0]))
27
28         Return, myValues
29     ENDELSE
30 END
31
32
33 pro ITCffExcel::SetChart,inputRange
34
35 ; self.oSheet->GetProperty,Shapes = MyShapes
36   ;MyShapes->AddChart
37
38   self.oExcel->GetProperty,Charts = MyChart
39   MyChart->add
40   ;获得刚刚添加的表,对它进行设置
41   self.oExcel->GetProperty,ActiveChart = MyActiveChart
42   ;又要获取ChartType 进行赋值
43   MyActiveChart->GetProperty,ChartType = MyChartType
44   MyChartType = 51 ;这个值我是通过看C#代码跟进去看到。不想获取对象来获取
45   ;设置数据源
46     IF (Obj_Valid(self.oCellRange)) THEN Obj_Destroy, self.oCellRange
47
48     IF (Size(inputRange, /Dimensions) EQ 2) THEN BEGIN
49         self.oSheet->GetProperty, inputRange[0], inputRange[1], Range = oCellRange
50         self.oCellRange = oCellRange
51     ENDIF ELSE BEGIN
52         self.oSheet->GetProperty, inputRange, Range = oCellRange
53         self.oCellRange = oCellRange
54     ENDELSE
55   MyActiveChart->SetSourceData,self.oCellRange,2
56   MyActiveChart->Location,2,"sheet1"
57
58 END
59
60 ;;;设置图表标题
61 pro ITCffExcel::SetChartTitle,strTitle
62
63     MyChart = self.oSheet->ChartObjects("Chart 1")
64     MyChart->Activate
65     self.oExcel->GetProperty,ActiveChart = MyActiveChart
66     MyActiveChart->GetProperty,ChartTitle = ChatTitle
67     ChatTitle->SetProperty,Text = strTitle
68
69
70
71
72
73 END
74 ;-----------------------------------------------------------------
75 ;
76 ; 删除指定表格。这样就可以就填充了。
77 ;
78 pro ITCffExcel::DeleteChart,inputRange
79     IF (Obj_Valid(self.oCellRange)) THEN Obj_Destroy, self.oCellRange
80
81     IF (Size(inputRange, /Dimensions) EQ 2) THEN BEGIN
82         self.oSheet->GetProperty, inputRange[0], inputRange[1], Range = oCellRange
83         self.oCellRange = oCellRange
84     ENDIF ELSE BEGIN
85         self.oSheet->GetProperty, inputRange, Range = oCellRange
86         self.oCellRange = oCellRange
87     ENDELSE
88
89     ;进行删除
90     ;self.oCellRange->GetProperty,Select = MySelect
91     ObjSelect = self.oCellRange->Select()
92     ;ObjSelect->Delete
93     self.oCellRange->Delete
94
95   ;MyActiveChart->SetSourceData,self.oCellRange,2
96   ;MyActiveChart->Location,2,"sheet1"
97
98 END
99
100 ;;设置y轴的属性
101 ;XorY : 0 代表设置 x , 1 代表设置y
102 ;MaximumScale：最大值
103 ;MinimumScale: 最小值
104 ;MinorUnit: 最小刻度
105 ;self.oSheet
106 PRO ITCffExcel::SetXYAxes, XorY,nMaximumScale,nMinimumScale,nMajorUnit
107   MyChart = self.oSheet->ChartObjects("Chart 1")
108   MyChart->Activate
109
110   self.oExcel->GetProperty,ActiveChart = MyActiveChart
111   if (XorY EQ 0)then  begin
112   Axis = MyActiveChart->Axes(1,1)
113   endif else begin
114   Axis = MyActiveChart->Axes(2,1)
115   endelse
116   ;Axis->GetProperty,MaximumScale = MyMaximumScale
117   Axis->SetProperty,MaximumScale = nMaximumScale
118   Axis->SetProperty,MinimumScale = nMinimumScale
119   Axis->SetProperty,MajorUnit = nMajorUnit
120
121 End
122
123
124
125 ;-----------------------------------------------------------------
126 ;
127 ;保存文件
128 ;
129 PRO ITCffExcel::Save, fileName
130
131     COMPILE_OPT IDL2
132
133     IF N_Elements(fileName) EQ 0 THEN fileName = self.fileName
134
135     self.oWkBookData->SaveAs, fileName
136 END
137
138 ;-----------------------------------------------------------------
139 ;   GetData Method. Data from passed in range parameter
140 ;
141 ;   @param inputRange {in}{required}{scalar or 2 element string array - 1 and
142 ;       2D ranges supported} input
143 ;
144 ;     Example:
145 ;
146 ;       oExcel = Obj_New("ITCffExcel", "myData.xls")
147 ;       myData= oExcel->GetData("A1:A100" | ["P1:P100","S1:S100"])
148 ;-
149 FUNCTION ITCffExcel::GetRangeData, inputRange
150
151     COMPILE_OPT IDL2
152
153     On_Error, 2
154
155     IF (Obj_Valid(self.oCellRange)) THEN Obj_Destroy, self.oCellRange
156
157     IF (Size(inputRange, /Dimensions) EQ 2) THEN BEGIN
158         self.oSheet->GetProperty, inputRange[0], inputRange[1], Range = oCellRange
159         self.oCellRange = oCellRange
160     ENDIF ELSE BEGIN
161         self.oSheet->GetProperty, inputRange, Range = oCellRange
162         self.oCellRange = oCellRange
163     ENDELSE
164
165     self.oCellRange->GetProperty, Value = myValues
166
167     IF (Size(myValues, /Type) EQ 0) THEN BEGIN ; undefined
168         Message, 'Invalid Range '+range+'? Mixed data types not supported.'+ $
169          ' Data must be numeric and contiguous.', /Ioerror
170     ENDIF ELSE Return, myValues
171 END
172
173 ;-----------------------------------------------------------------
174 ;
175 ;设置单元格数据
176 ;
177 PRO ITCffExcel::SetRangeData, inputRange, value
178
179     COMPILE_OPT IDL2
180
181     On_Error, 2
182
183     IF (Obj_Valid(self.oCellRange)) THEN Obj_Destroy, self.oCellRange
184
185     IF (Size(inputRange, /Dimensions) EQ 2) THEN BEGIN
186         self.oSheet->GetProperty, inputRange[0], inputRange[1], Range = oCellRange
187         self.oCellRange = oCellRange
188     ENDIF ELSE BEGIN
189         self.oSheet->GetProperty, inputRange, Range = oCellRange
190         self.oCellRange = oCellRange
191     ENDELSE
192
193     self.oCellRange->SetProperty, 'select', Value = value
194
195     Obj_Destroy, self.oCellRange
196 END
197
198 ;-----------------------------------------------------------------
199 ;   Sets Properties on ITCffExcel Object
200 ;
201 ;   @Keyword:
202 ;
203 ;      Sheet {in}{optional}{scalar string} input
204 ;
205 ;      Example:
206 ;
207 ;      oExcel = Obj_New("ITCffExcel", "myData.xls")
208 ;      oExcel->SetPropery, SHEET="Sheet2"
209 ;-
210 PRO ITCffExcel::SetProperty, Sheet = sheet, _Extra = extra
211
212     COMPILE_OPT IDL2
213
214     On_Error, 2
215
216     IF (Obj_Valid(self.oSheet)) THEN Obj_Destroy, self.oSheet
217
218     self.oSheetCollection->GetProperty, sheet, Item = oSheet
219
220     self.oSheet = oSheet
221 END
222
223 ;-----------------------------------------------------------------
224 ;
225 ;析构
226 ;
227 PRO ITCffExcel::Cleanup
228
229     COMPILE_OPT IDL2
230
231     On_Error, 2
232
233     self.oWkBookData->SetProperty, Saved = 1
234     self.oWkBookCollection->Close
235     self.oExcel->Quit
236
237     Obj_Destroy, [self.oSheet, self.oCellRange, self.oSheetCollection, $
238                     self.oWkBookData, self.oWkBookCollection, self.oExcel]
239 END
240
241 ;-----------------------------------------------------------------
242 ;
243 ;初始化
244 ;
245 FUNCTION ITCffExcel::Init, fileName, Sheet = sheet
246
247     COMPILE_OPT IDL2
248
249     On_Error, 2
250
251     IF (N_Params() LT 1) THEN Message, 'ITCffExcel::Init requires file name'
252
253     ; Use the IDL COM import bridge to init Excel Application Object
254     self.oExcel = Obj_New("IDLcomIDispatch$PROGID$Excel_Application")
255
256     IF ~self.oExcel THEN Return, 0
257
258     ; Get a reference to the Excel Workbook Collection Object
259     self.oExcel->GetProperty, Workbooks = oWkBookCollection
260     self.oWkBookCollection = oWkBookCollection
261
262     self.fileName = fileName
263
264     ; Open the Excel data
265     self.oWkBookData = self.oWkBookCollection->Open(self.fileName)
266
267     ; Get a reference to the sheets collection
268     self.oWkBookData->GetProperty, Sheets = oSheetCollection
269     self.oSheetCollection = oSheetCollection
270
271     ; Parmamerized property - props can return a value
272     ; (this syntax used in collections) - Goofy COM

273     IF (Keyword_Set(sheet)) THEN BEGIN
274         self.oSheetCollection->GetProperty, sheet, Item = oSheet
275         self.oSheet = oSheet
276     ENDIF ELSE BEGIN
277         self.oSheetCollection->GetProperty, 'Sheet1', Item=oSheet
278         self.oSheet = oSheet
279     ENDELSE
280
281     Return, 1
282 END
283
284 ;-----------------------------------------------------------------
285 ;
286 ;定义
287 ;
288 PRO ITCffExcel__Define
289
290     COMPILE_OPT IDL2
291
292     void = {ITCffExcel                                      , $
293
294             ;参数
295             fileName            :   ''                      , $
296
297             ;对象
298             oExcel              :   Obj_New()               , $
299             oWkBookCollection   :   Obj_New()               , $
300             oWkBookData         :   Obj_New()               , $
301             oSheetCollection    :   Obj_New()               , $
302             oSheet              :   Obj_New()               , $
303             oCellRange          :   Obj_New()               $
304             }
305
306 END

posted on 2013-07-01 12:50 小鱼儿阅读(3778) 评论(1) 编辑收藏引用

FeedBack:

# re: c#对excel 图表操作

2015-02-02 16:03 | HappyCoding

不错. 但是使用free spire.xls的话好像更简单，用Chart.Add()方法就可以随意选择chart类型

Chart chart = ws.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = ws.Range["A1:C5"];

详细步骤可以看看：http://www.cnblogs.com/Yesi/p/4241593.html 回复更多评论

刷新评论列表

只有注册用户登录后才能发表评论。




网站导航: 博客园 IT新闻 BlogJava 博问 Chat2DB 管理

2013年7月

日

一

二

三

四

五

六

常用链接

留言簿(4)

随笔档案(25)

搜索

最新评论

阅读排行榜

评论排行榜