using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace excel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//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)?");
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 = 90; //แสดงมุมเอียงของตาราง
oResizeRange.WrapText = true;
//Fill the interior color of the headers.
oResizeRange.Interior.ColorIndex = 37; //เลือกสีในตาราง
//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;
}
//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;
}
}
}
Tag : .NET, Ms Access, Excel (Excel.Application), C#, VS 2008 (.NET 3.x)