开发excel,免不了要开发excel的chart:
代码
1
#region
operate chart
2 // 创建chart对象
3 Excel.Chart chart = m_objExcel.Charts.Add(Type.Missing, Ocuurent_sheet, Type.Missing, Type.Missing) as Excel.Chart;
4 // chart类型
5 chart.ChartType = Excel.XlChartType.xlLineMarkers;
6 // chart.set_HasAxis();
7 // 设置chart对应excel的sheet里的数据矩阵范围
8 chartrange = Ocuurent_sheet.get_Range(Ocuurent_sheet.Cells[ 2 , 2 ], Ocuurent_sheet.Cells[ 5 , ( 1 + Ds_occurent.Tables[ 0 ].Columns.Count)]);
9 // 绑定数据
10 // chart.SetSourceData(chartrange,Excel.XlRowCol.xlRows);
11
12 chart.HasLegend = true ;
13 // 标题
14 chart.HasTitle = true ;
15 // 标题名称
16 chart.ChartTitle.Text = " HUB occurrence " ;
17 chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
18 // 设置excely和x轴
19 Excel.Axis categoryAxis = (Excel.Axis)m_objExcel.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
20 categoryAxis.CategoryType = Excel.XlCategoryType.xlCategoryScale;
21 Excel.Series se = (Excel.Series)chart.SeriesCollection( 3 );
22 // chart颜色设置
23 se.Border.ColorIndex = 5 ;
24 se = (Excel.Series)chart.SeriesCollection( 2 );
25 se.Border.ColorIndex = 6 ;
26 se = (Excel.Series)chart.SeriesCollection( 1 );
27 se.Border.ColorIndex = 3 ;
28 // se.MarkerBackgroundColorIndex = 5;
29 // se.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleStar;
30 chart.Location(Excel.XlChartLocation.xlLocationAsObject, Ocuurent_sheet.Name);
31 // chart位置
32 Ocuurent_sheet.Shapes.Item( 1 ).IncrementTop( - 116 );
33 Ocuurent_sheet.Shapes.Item( 1 ).IncrementLeft( - 166 );
34 float a = 0.84f ;
35 // chart大小
36 Ocuurent_sheet.Shapes.Item( 1 ).ScaleHeight(a, MsoTriState.msoFalse, MsoScaleFrom.msoScaleFromTopLeft);
37 #endregion
2 // 创建chart对象
3 Excel.Chart chart = m_objExcel.Charts.Add(Type.Missing, Ocuurent_sheet, Type.Missing, Type.Missing) as Excel.Chart;
4 // chart类型
5 chart.ChartType = Excel.XlChartType.xlLineMarkers;
6 // chart.set_HasAxis();
7 // 设置chart对应excel的sheet里的数据矩阵范围
8 chartrange = Ocuurent_sheet.get_Range(Ocuurent_sheet.Cells[ 2 , 2 ], Ocuurent_sheet.Cells[ 5 , ( 1 + Ds_occurent.Tables[ 0 ].Columns.Count)]);
9 // 绑定数据
10 // chart.SetSourceData(chartrange,Excel.XlRowCol.xlRows);
11
12 chart.HasLegend = true ;
13 // 标题
14 chart.HasTitle = true ;
15 // 标题名称
16 chart.ChartTitle.Text = " HUB occurrence " ;
17 chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
18 // 设置excely和x轴
19 Excel.Axis categoryAxis = (Excel.Axis)m_objExcel.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
20 categoryAxis.CategoryType = Excel.XlCategoryType.xlCategoryScale;
21 Excel.Series se = (Excel.Series)chart.SeriesCollection( 3 );
22 // chart颜色设置
23 se.Border.ColorIndex = 5 ;
24 se = (Excel.Series)chart.SeriesCollection( 2 );
25 se.Border.ColorIndex = 6 ;
26 se = (Excel.Series)chart.SeriesCollection( 1 );
27 se.Border.ColorIndex = 3 ;
28 // se.MarkerBackgroundColorIndex = 5;
29 // se.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleStar;
30 chart.Location(Excel.XlChartLocation.xlLocationAsObject, Ocuurent_sheet.Name);
31 // chart位置
32 Ocuurent_sheet.Shapes.Item( 1 ).IncrementTop( - 116 );
33 Ocuurent_sheet.Shapes.Item( 1 ).IncrementLeft( - 166 );
34 float a = 0.84f ;
35 // chart大小
36 Ocuurent_sheet.Shapes.Item( 1 ).ScaleHeight(a, MsoTriState.msoFalse, MsoScaleFrom.msoScaleFromTopLeft);
37 #endregion
如果我们使用.net的com,那么我们可以对excel的增益集合进行录制(工具--》增益集合--》录制增益集),录制完后,我们然后打开vba编辑器,查看相应的代码。我们根据vba代码来写c#代码,进行稍微的改动就可以用了。VBA操作看这里
加上一个小东西:
我们在读excel的时候不用text二世用value2的时候,读取时间有时候会是double类型,那么我们怎么样把它转换成时间类型?DateTime.FromOADate
double转换成时间如下:
1
DateTime getDate
=
new
DateTime(
2009
,
12
,
30
);
2 double Number = getDate.ToOADate(); // convert the datetime to double
3
4 // double doubleValue = 0 ;
5 DateTime doubleDate = DateTime.FromOADate(Number); // convert the double to datetime
6
7 Console.WriteLine(Number); // 0
8
9 Console.WriteLine(doubleDate.ToString()); // 12/30/2009 12:00:00 AM
10 Console.WriteLine(doubleDate.ToString( " yyyyMMdd HH:mm:ss " )); //2009 1230 00:00:00
11
2 double Number = getDate.ToOADate(); // convert the datetime to double
3
4 // double doubleValue = 0 ;
5 DateTime doubleDate = DateTime.FromOADate(Number); // convert the double to datetime
6
7 Console.WriteLine(Number); // 0
8
9 Console.WriteLine(doubleDate.ToString()); // 12/30/2009 12:00:00 AM
10 Console.WriteLine(doubleDate.ToString( " yyyyMMdd HH:mm:ss " )); //2009 1230 00:00:00
11