c# 操作excel chart

private void button1_Click(object sender, System.EventArgs e)
{
  Excel.Application oXL;
  Excel._Workbook oWB;
  Excel._Worksheet oSheet;
  Excel.Range oRng;
 try
 {
  //Start Excel and get Application object.
  oXL = new Excel.Application();
  oXL.Visible = true;
  //Get a new workbook.
  oWB = ( Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
  oSheet = ( Excel._Worksheet)oWB.ActiveSheet;
  //Add table headers going cell by cell.
  oSheet.Cells[1, 1] = "First Name";
  oSheet.Cells[1, 2] = "Last Name";
  oSheet.Cells[1, 3] = "Full Name";
  oSheet.Cells[1, 4] = "Salary";
  //Format A1:D1 as bold, vertical alignment = center.
  oSheet.get_Range("A1", "D1").Font.Bold = true;
  oSheet.get_Range("A1", "D1").VerticalAlignment =
    Excel.XlVAlign.xlVAlignCenter;
  
  // Create an array to multiple values at once.
  string[,] saNames = new string[5,2];
  
  saNames[ 0, 0] = "John";
  saNames[ 0, 1] = "Smith";
  saNames[ 1, 0] = "Tom";
  saNames[ 1, 1] = "Brown";
  saNames[ 2, 0] = "Sue";
  saNames[ 2, 1] = "Thomas";
  saNames[ 3, 0] = "Jane";
  saNames[ 3, 1] = "Jones";
  saNames[ 4, 0] = "Adam";
  saNames[ 4, 1] = "Johnson";
         //Fill A2:B6 with an array of values (First and Last Names).
         oSheet.get_Range("A2", "B6").Value2 = saNames;
  //Fill C2:C6 with a relative formula (=A2 & " " & B2).
  oRng = oSheet.get_Range("C2", "C6");
  oRng.Formula = "=A2 & /" /" & B2";
  //Fill D2:D6 with a formula(=RAND()*100000) and apply format.
  oRng = oSheet.get_Range("D2", "D6");
  oRng.Formula = "=RAND()*100000";
  oRng.NumberFormat = "$0.00";
  //AutoFit columns A:D.
  oRng = oSheet.get_Range("A1", "D1");
  oRng.EntireColumn.AutoFit();
  //Manipulate a variable number of columns for Quarterly Sales Data.
  DisplayQuarterlySales(oSheet);
  //Make sure Excel is visible and give the user control
  //of Microsoft Excel's lifetime.
  oXL.Visible = true;
  oXL.UserControl = true;
 }
 catch( Exception theException )
 {
  String errorMessage;
  errorMessage = "Error: ";
  errorMessage = String.Concat( errorMessage, theException.Message );
  errorMessage = String.Concat( errorMessage, " Line: " );
  errorMessage = String.Concat( errorMessage, theException.Source );
  MessageBox.Show( errorMessage, "Error" );
 }
}
private void DisplayQuarterlySales( Excel._Worksheet oWS)
{
  Excel._Workbook oWB;
  Excel.Series oSeries;
  Excel.Range oResizeRange;
  Excel._ Chart oChart;
 String sMsg;
 int iNumQtrs;
 //Determine how many quarters to display data for.
 for( iNumQtrs = 4; iNumQtrs >= 2; iNumQtrs--)
 {
  sMsg = "Enter sales data for ";
  sMsg = String.Concat( sMsg, iNumQtrs );
  sMsg = String.Concat( sMsg, " quarter(s)?");
  DialogResult iRet = MessageBox.Show( sMsg, "Quarterly Sales?",
   MessageBoxButtons.YesNo );
  if (iRet == DialogResult.Yes)
   break;
 }
 sMsg = "Displaying data for ";
 sMsg = String.Concat( sMsg, iNumQtrs );
 sMsg = String.Concat( sMsg, " quarter(s)." );
 MessageBox.Show( sMsg, "Quarterly Sales" );
 //Starting at E1, fill headers for the number of columns selected.
 oResizeRange = oWS.get_Range("E1", "E1").get_Resize( Missing.Value, iNumQtrs);
 oResizeRange.Formula = "=/"Q/" & COLUMN()-4 & CHAR(10) & /"Sales/"";
 //Change the Orientation and WrapText properties for the headers.
 oResizeRange.Orientation = 38;
 oResizeRange.WrapText = true;
 //Fill the interior color of the headers.
 oResizeRange.Interior.ColorIndex = 36;
 //Fill the columns with a formula and apply a number format.
 oResizeRange = oWS.get_Range("E2", "E6").get_Resize( Missing.Value, iNumQtrs);
 oResizeRange.Formula = "=RAND()*100";
 oResizeRange.NumberFormat = "$0.00";
 //Apply borders to the Sales data and headers.
 oResizeRange = oWS.get_Range("E1", "E6").get_Resize( Missing.Value, iNumQtrs);
 oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThin;
 //Add a Totals formula for the sales data and apply a border.
 oResizeRange = oWS.get_Range("E8", "E8").get_Resize( Missing.Value, iNumQtrs);
 oResizeRange.Formula = "=SUM(E2:E6)";
 oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).LineStyle
  = Excel.XlLineStyle.xlDouble;
 oResizeRange.Borders.get_Item( Excel.XlBordersIndex.xlEdgeBottom ).Weight
  = Excel.XlBorderWeight.xlThick;
 //Add a Chart for the selected data.
 oWB = ( Excel._Workbook)oWS.Parent;
 oChart = ( Excel._ Chart)oWB.Charts.Add( Missing.Value, Missing.Value,
  Missing.Value, Missing.Value );
 //Use the ChartWizard to create a new chart from the selected data.
 oResizeRange = oWS.get_Range("E2:E6", Missing.Value ).get_Resize(
  Missing.Value, iNumQtrs);
 oChart.ChartWizard( oResizeRange, Excel.XlChartType.xl3DColumn, Missing.Value,
   Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value,
  Missing.Value, Missing.Value, Missing.Value, Missing.Value );
 oSeries = ( Excel.Series)oChart.SeriesCollection(1);
 oSeries.XValues = oWS.get_Range("A2", "A6");
 for( int iRet = 1; iRet <= iNumQtrs; iRet++)
 {
  oSeries = ( Excel.Series)oChart.SeriesCollection(iRet);
  String seriesName;
  seriesName = "=/"Q";
  seriesName = String.Concat( seriesName, iRet );
  seriesName = String.Concat( seriesName, "/"" );
  oSeries.Name = seriesName;
 }               
 
 oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name );
 //Move the chart so as not to cover your data.
 oResizeRange = ( Excel.Range)oWS.Rows.get_Item(10, Missing.Value );
 oWS.Shapes.Item(" Chart 1").Top = (float)(double)oResizeRange.Top;
 oResizeRange = ( Excel.Range)oWS.Columns.get_Item(2, Missing.Value );
 oWS.Shapes.Item(" Chart 1").Left = (float)(double)oResizeRange.Left;
}
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值