c#调用Excel绘制图表

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

c#调用Excel需要引用命名空间

using Microsoft.Office.Interop.Excel;

由于该程序不复杂，主要是根据不同数据画表和图，画的图像也并不复杂，因为画图和画表的操作会重复使用，所以，我把画表和图的操作放在一个类中

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
namespace Excel
{
class SheetControl
{
Worksheet sheet;
public SheetControl(Worksheet sheet)
{
this.sheet = sheet;
} /// <summary>
/// 根据条件绘制图表
/// </summary>
/// <param name="rg">绘制图表的区域</param>
/// <param name="data">数据区域</param>
/// <param name="type">图表类型，XlChartType枚举类型</param>
/// <param name="xlrc">设置以行或者列为系列，XlRowCol枚举类型</param>
/// <param name="title">表格标题</param>
/// <param name="CategoryTitle">表格分类坐标名称，即横坐标名称</param>
/// <param name="ValueTitle">表格数据坐标的名称，即纵坐标名称</param>
public void CreateChart(Range rg, Range data, Object type = null, XlRowCol xlrc = XlRowCol.xlColumns, string title = null, string CategoryTitle = null, string ValueTitle = null)
{
ChartObjects charts = (ChartObjects)sheet.ChartObjects(Type.Missing);
ChartObject chartObj = charts.Add(rg.Left, rg.Top, rg.Width, rg.Height);
Chart chart = chartObj.Chart;
chart.ChartWizard(data, type, Type.Missing, xlrc, , , true, title, CategoryTitle, ValueTitle, Type.Missing);
chart.Legend.Position = XlLegendPosition.xlLegendPositionTop; } /// <summary>
/// 根据参数创建表格
/// </summary>
/// <param name="title">表格第一个单元格内容</param>
/// <param name="rows">行标题，每行的首个单元格内容</param>
/// <param name="cols">列标题，没列的首个单元格</param>
/// <param name="data">表格数据</param>
/// <param name="startRow">行开始位置</param>
/// <param name="startCol">列开始位置</param>
public void CreateTable(string title,string[] rows,string[] cols,string[,] data,int startRow,int startCol)
{
sheet.Cells[startRow, startCol] = title;
for (int i = ; i < cols.Length; i++)
{
sheet.Cells[startRow, i + startCol + ] = cols[i];
}
for (int i = ; i < rows.Length; i++)
{
sheet.Cells[i+startRow+,startCol]=rows[i];
for (int j = ; j < cols.Length; j++)
{
sheet.Cells[i + startRow + , j + startCol + ] = data[i, j];
}
}
}
/// <summary>
/// 设置表格的信息，包括边框，文字等
/// </summary>
public void SetType()
{ sheet.UsedRange.Font.Size = ;
sheet.UsedRange.Font.Name = "华文楷体";
sheet.UsedRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;//竖直居中
sheet.UsedRange.VerticalAlignment = XlVAlign.xlVAlignCenter;//水平居中
sheet.UsedRange.Borders.LineStyle = ;//边框
sheet.UsedRange.Columns.AutoFit();//列宽自适应
sheet.UsedRange.Rows.AutoFit();//行高自适应
//设置边框
sheet.UsedRange.BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlThick, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
}
}
}

设置一个Excel全局变量

public Microsoft.Office.Interop.Excel.Application myexcel;

设置数据并新建一个Excel画表和图

private void button1_Click(object sender, EventArgs e)
{
string[] rows = { "国家机关办公建筑","办公建筑","商场建筑","宾馆饭店建筑","文化建筑","医疗卫生建筑","体育建筑","综合建筑","教育建筑","其他建筑","总计"};
string[] cols = { "本月","上月" };
string[,] data ={{"",""},
{"",""},
{"",""},
{"",""},
{"",""},
{"",""},
{"",""},
{"",""},
{"",""},
{"",""},
{"",""}}; DialogResult res = MessageBox.Show("是否生成新的Excel ?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
if (res == DialogResult.Yes)
{ myexcel = new Microsoft.Office.Interop.Excel.Application();
myexcel.Application.Workbooks.Add(Type.Missing);
myexcel.Caption = "excel test";
myexcel.Visible = true;
Worksheet sheet = (Worksheet)myexcel.ActiveWorkbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
sheet.Name = "表2";
SheetControl sc = new SheetControl(sheet);
sc.CreateChart(sheet.get_Range("F2:O12"), sheet.get_Range((Range)sheet.Cells[, ], (Range)sheet.Cells[ + rows.Length - , + cols.Length]), XlChartType.xlColumnClustered, XlRowCol.xlColumns, "本月建筑能耗监测情况", null, "月度单位面积电耗");
//sheet.get_Range(sheet.Cells[2,2],sheet.Cells[2+rows.Length,2+cols.Length])
sc.CreateTable("建筑类型", rows, cols, data, , );
sc.SetType();
}
}