using System;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Diagnostics;
using System.Drawing;
namespace AutoExcel
{
//Application:代表Excel应用程序本身,Application公开了大量有关正在
//运行的应用程序,应用于该实例的选项及在该实例中打开的当前对象的信息。
//WorkBook:表示Excel应用程序的单个工作簿,通过提供该类(该类能访问WordBooks
//中的所有对象成员),以及数据绑定功能和附加事件来扩展该类。
//WorkSheet:该对象是WorkSheets集合成员,它的许多属性,方法和事件都与Application/WorkBook
//相似。另外Sheets是WorkBook对象属性。
//Range:Excel中的单元格对应的对象。
//chartObjects:取得一个对象,该对象表示工作表上的单个嵌入式图表(chartObject)或者嵌入式图表集合(chartObjects)。
//chart:该控件是公开事件的图表对象,可以绑定到数据上。
class AutoExcelTest
{
public static void Main()
{
CloseExcel();
//创建Application对象
Console.WriteLine("Creating new Excel.Application");
Application app = new Application();
if (app == null)
{
Console.WriteLine("ERROR: EXCEL couldn't be started!");
CloseExcel();
return;
}
Console.WriteLine("Making Application visible");
app.visible = true;
//创建WorkBook对象集合
Console.WriteLine("Get Workbokks collection");
Workbooks workBooks = app.Workbooks;
//创建一个新的WorkBook对象
Console.WriteLine("Add a new Workbook");
Workbook workBook = workBooks.Add(XlWBATemplate.xlWBATWorksheet);
Console.WriteLine("Get Worksheet collection");
//创建WorkSheet对象集合
Sheets sheets = workBook.Worksheets;
if (sheets == null)
{
Console.WriteLine("Worksheet creating Error!");
CloseExcel();
return;
}
_Worksheet workSheet = (_Worksheet)sheets.get_Item(1);
if (workSheet == null)
{
Console.WriteLine("Error:WorkSheet is null!");
CloseExcel();
return;
}
Console.WriteLine("Setting the value for cell");
//获取单元格G1
Range range1 = workSheet.get_Range("G1", Missing.Value);
if (range1 == null)
{
Console.WriteLine("Error:Range is null");
CloseExcel();
return;
}
const int nCells = 5;
range1.Value2 = nCells;
//获取A1到E1的Range对象(5个单元格)
Range range2 = workSheet.get_Range("A1", "E1");
int[] arr2 = new int[5];
for (int i = 0; i < arr2.GetLength(0); i++)
{
int x = i;
arr2[i] = x + 1;
}
range2.Value2 = arr2;
//获取A3到E4的Range对象(10)
Range range3 = workSheet.get_Range("A3", "E4");
int[,] arr3 = new int[2, 5];
for (int i = 0; i < arr3.GetLength(0); i++)
{
for (int j = 0; j < arr3.GetLength(1); j++)
{
arr3[i, j] = i * 10 + j;
}
}
range3.Value2 = arr3;
//比较A3到E4的值
Range range4 = workSheet.get_Range("A3", "E4");
Object[,] arr4 = (Object[,])range4.Value2;
for (int i = arr4.GetLength(0); i <= arr4.GetLength(0); i++)
{
for (int j = arr4.GetLength(1); j <= arr4.GetLength(1); j++)
{
bool isComparision = (double)arr4[i, j] != arr3[i - 1, j - 1];
if (isComparision)
{
Console.WriteLine("Comparision Faild");
CloseExcel();
return;
}
}
}
//获取A5到J6的Range对象(20)
Range range5 = workSheet.get_Range("A6", "J7");
double[,] arr5 = new double[2, 10];
for (int j = 0; j < arr5.GetLength(1); j++)
{
double arg = Math.PI / arr5.GetLength(1) * j;
arr5[0, j] = Math.Sin(arg);
arr5[1, j] = Math.Cos(arg);
}
range5.Value2 = arr5;
range5.Select();
//获取嵌入式图表对象
ChartObjects chartObjs = (ChartObjects)workSheet.ChartObjects(Missing.Value);
ChartObject chartObj = (ChartObject)chartObjs.Add(20/*Left*/, 100/*Top*/, 450/*Width*/, 260/*Height*/);
_Chart chart = (_Chart)chartObj.Chart;
Object[] arr6 = new object[11];
arr6[0] = range5;//Source:表示图表元数据范围
arr6[1] = XlChartType.xl3DColumn; //Gallery:图表类型
arr6[2] = Missing.Value;//Format:内置自动套用格式的选项编号
arr6[3] = XlRowCol.xlRows;//PlotBy:指定每个系列的绘制是按行还是按列
arr6[4] = 0;//GategotyLables:一个整数,指定源范围包含类别标签的行数或列数
arr6[5] = 0;//SeriesLables:一个整数,指定源范围中包含系列标签的行数或列数
arr6[6] = true;//HasLegend:包含图例
arr6[7] = "ExcelTest";//Title:Chart控件标题文本
arr6[8] = "ExcelTest Category Titel";//CategoryTitle:分类轴标题文本
arr6[9] = "ExcelTest Value Tile";//ValueTitle:数值轴标题文本
arr6[10] = Missing.Value;//ExtraTitle:三维图表的系列轴标题或二维图表的第二个数值轴标题
chart.GetType().InvokeMember("ChartWizard", BindingFlags.InvokeMethod, null, chart, arr6);
}
/// <summary>
/// 关闭Excel进程
/// </summary>
private static void CloseExcel()
{
Process[] process = Process.GetProcessesByName("EXCEL");
foreach (Process p in process)
{
p.CloseMainWindow();
}
}
}
}
AutoExcel
最新推荐文章于 2024-09-11 11:28:25 发布