11 excl导出导入数据和图表
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Web.UI.WebControls;
usingExcel;
usingSystem.Reflection;
usingSystem.Web;
namespaceBLL
{
public class ExclOut
{
stringstrUrl ;//保存路径
GridViewdgv;
public int DateCount;//导出条数
intDataGridViewColumncount ;//取得网格控件列数
public int DataGridViewRowCount;//取得网格控件行数
public Object[,] MyData;//用于接收数据的对象
intCount, i, j;
publicExclOut(GridView GridView,string url)
{
strUrl = url ;
dgv = GridView;//构造方法取得要导出的网格控件
DateCount = dgv.Rows.Count;
}
///<summary>
///导出excl和图表
///参数:type输出类型strRange1,strRange2图表输出范围,str图表内部名称数组,workSheetName基础表标签页名
///</summary>
///<returns></returns>
public string exutExcel(inttype, string strRange1, string strRange2, string[] str, string workSheetName)
{
//HttpContext.Current.Response.Write("1");
DataGridViewColumncount =dgv.Columns.Count;//取得网格控件列数
DataGridViewRowCount =dgv.Rows.Count+1;//取得网格控件行数
MyData = newObject[DataGridViewRowCount + 1,DataGridViewColumncount];//用于接收数据的对象
//HttpContext.Current.Response.Write(":2");
ApplicationClassMyExcel ;//excel对象
WorkbooksMyWorkBooks;//工作簿对象集合
WorkbookMyWorkBook;//工作簿
WorksheetMyWorkSheet;//标签页
ChartMyChart;//图表对象
RangeMyRange, MyRange1;//范围对象
try
{
MyExcel = new ApplicationClass();//实例化excle对象
//MyExcel.Visible= true;//在界面打开excle窗口
if(MyExcel == null)
{
//MessageBox.Show("Excel程序无法启动!", "信息提示",MessageBoxButtons.OK, MessageBoxIcon.Information);
returnnull;
}
MyWorkBooks =MyExcel.Workbooks;
MyWorkBook = MyWorkBooks.Add(Missing.Value);
MyWorkSheet = (Worksheet)MyWorkBook.Worksheets[1];
MyWorkSheet.Name =workSheetName;//设置标签页名称
MyRange = MyWorkSheet.get_Range("A1", "F1");
//取得标题
Count = 0;
for(i = 0; i < dgv.Columns.Count; i++)
{
MyData[0, i] =dgv.HeaderRow.Cells[i].Text;
Count = Count + 1;
}
//设标题为黑体字
MyWorkSheet.get_Range(MyWorkSheet.Cells[1, 1], MyWorkSheet.Cells[1,Count]).Font.Name = "黑体";
//标题字体加粗
MyWorkSheet.get_Range(MyWorkSheet.Cells[1, 1], MyWorkSheet.Cells[1,Count]).Font.Bold = true;
//设表格边框样式
MyWorkSheet.get_Range(MyWorkSheet.Cells[1, 1], MyWorkSheet.Cells[1,Count]).Borders.LineStyle = 1;
//----------------升序排列--------------
j = 1;
//从网格控件中遍历取得数据
foreach(GridViewRow var indgv.Rows)
{
inti1 = 0;
for(i = 0; i < DataGridViewColumncount; i++)
{
stringstr1 = var.Cells[i].Text;
if (str1 != ""&& var.Cells[i].Visible == true)
{
MyData[j, i1++] =str1;
}
}
j++;
}
MyRange =MyRange.get_Resize(DataGridViewRowCount + 1, DataGridViewColumncount); //定义数据写入区域
//删除多余标签页
foreach(Excel.Worksheet ws inMyWorkBook.Worksheets)
if(ws != MyExcel.ActiveSheet)
{
ws.Delete();
}
foreach(Excel.Chart cht inMyWorkBook.Charts)
cht.Delete();
//生成图表
switch(type)
{
case1:
//导出多个图表月表
orderbydesc();
exportChatr(MyWorkBook,MyWorkSheet, DataGridViewRowCount, out MyChart,out MyRange1);
break;
case2:
//导出柱线图周表
orderbydesc();//降序
//-------Production Plan Adherance -------
string[] str1 = { "ProductionPlan Adherance ", "MONTH","total", "柱轴标题", "柱轴标签", "ProductionPlan Adherance " };
string strR1 = "D1:D"+ DataGridViewRowCount + ",AB1:AC"+ DataGridViewRowCount;
chart1(MyWorkBook,MyWorkSheet, out MyChart, out MyRange1, strR1, null,str1[5], str1);
//------Safety Stock -------
string[] str2 = { "Safety Stock", "MONTH", "total", "柱轴标题", "柱轴标签", "SafetyStock " };
string strR2 = "D1:D"+ DataGridViewRowCount + ",X1:Y" +DataGridViewRowCount;
chart1(MyWorkBook,MyWorkSheet, out MyChart, out MyRange1, strR2, null,str2[5], str2);
break;
case3://导出雷达图
//降序排列
//orderbydesc();
string[] str3 = { "radar ","MONTH", "total","柱轴标题","柱轴标签","radar " };
string strR3 = "A1:C"+ (DataGridViewRowCount) ;
chartRadar(MyWorkBook,MyWorkSheet, out MyChart, out MyRange1, strR3, null,str3[5], str3);
break;
}
//HttpContext.Current.Response.Write(":5");
//----------将数据和图表输出到excel----------------
MyRange.Value2 = MyData;//将接收了数据的对象数组传入range中,同时将数据写入excel中
MyRange.EntireColumn.AutoFit();//列宽自动
//MyWorkBook.SaveAs("c:\\exle.xls",Excel.XlFileFormat.xlWorkbookNormal,
// "", "", false,false, 0,
// "", 0, "","", "");
System.Windows.Forms.Application.DoEvents();
MyWorkBook.Saved = true;
//保存excl
MyWorkBook.SaveCopyAs(strUrl);
returnnull;
}
catch(Exception MyEx)
{
//MessageBox.Show(MyEx.Message, "信息提示",MessageBoxButtons.OK, MessageBoxIcon.Information);
returnMyEx.ToString();
}
}
//------------降序排列---------------
privatevoid orderbydesc()
{
intk=1;
for(j = DataGridViewRowCount-2 ; j >= 0; j--)
{
for(i = 0; i < DataGridViewColumncount; i++)
{
stringstr1 = dgv.Rows[j].Cells[i].Text;
if(str1 != "" &&dgv.Rows[j].Cells[i].Visible == true)
{
MyData[k, i] = str1;
}
}
k++;
}
}
//同时导出多个图表(月表)
privatestatic voidexportChatr(Workbook MyWorkBook, Worksheet MyWorkSheet, intDataGridViewcount, out ChartMyChart, out RangeMyRange1)
{
//--------DPPM--------
string[]str2 = { "DPPM", "MONTH", "VALUE","柱轴标题","柱轴标签","DPPM" };
stringstrR2 = "C1:C" + DataGridViewcount+ ",L1:N" + DataGridViewcount;
chart2(MyWorkBook, MyWorkSheet,strR2, null, str2[5], str2);
//-------OTIF-------
string[]str1 = { "OTIF", "MONTH", "percent","柱轴标题","柱轴标签","OTIF" };
stringstrR1 = "C1:C" + DataGridViewcount+ ",F1:H" + DataGridViewcount;
chart2(MyWorkBook,MyWorkSheet, strR1, null, str1[5], str1);
//---------ProblemResolution-----
string[]str3 = { "Problem Resolution", "MONTH", "VALUE","柱轴标题","柱轴标签","Problem Resolution" };
stringstrR3 = "C1:C" + DataGridViewcount+ ",O1:Q" + DataGridViewcount;
chartColumLine(MyWorkBook, MyWorkSheet,out MyChart, outMyRange1, strR3, null, str3[5], str3);
//-------AuditPerformance-------
string[]str4 = { "Audit Performance", "MONTH", "VALUE","柱轴标题","柱轴标签","Audit Performance" };
stringstrR4 = "C1:C" + DataGridViewcount+ ",AI1:AI" + DataGridViewcount;
chartDf(MyWorkBook, MyWorkSheet, out MyChart, outMyRange1, strR4, null, str4[5], str4);
//-------AuditPerformance-------
string[]str5 = { "Load Capacity", "MONTH", "total","柱轴标题","柱轴标签","Load Capacity" };
stringstrR5 = "C1:C" + DataGridViewcount+ ",U1:U" + DataGridViewcount;
chartDf(MyWorkBook, MyWorkSheet, out MyChart, outMyRange1, strR5, null, str5[5], str5);
//-------FAIRRight First Time-------
string[]str6 = { "FAIR Right First Time", "MONTH", "value","柱轴标题","柱轴标签","FAIR Right First Time" };
stringstrR6 = "C1:C" + DataGridViewcount+ ",AD1:AE" + DataGridViewcount;
chartColum2All(MyWorkBook,MyWorkSheet, out MyChart, out MyRange1, strR6, null,str6[5], str6);
//-------ProductionPlan Adherance-------
string[]str7 = { "Production Plan Adherance","MONTH", "total","柱轴标题","柱轴标签","Production Plan Adherance" };
stringstrR7 = "C1:C" + DataGridViewcount+ ",AA1:AB" + DataGridViewcount;
chart1(MyWorkBook, MyWorkSheet, out MyChart, outMyRange1, strR7, null, str7[5], str7);
//-------SafetyStock -------
string[]str8 = { "Safety Stock ", "MONTH", "total","柱轴标题","柱轴标签","Safety Stock " };
stringstrR8 = "C1:C" + DataGridViewcount+ ",W1:X" + DataGridViewcount;
chart1(MyWorkBook, MyWorkSheet, out MyChart, outMyRange1, strR8, null, str8[5], str8);
}
//生成图表柱状
privatestatic voidchartDf(Workbook MyWorkBook, Worksheet MyWorkSheet, outChart MyChart, outRange MyRange1, stringstrRange1, string strRange2, string chartName, string[]str)
{
//---------------生成图表-------------------
MyChart = (Chart)MyWorkBook.Charts.Add(Missing.Value, Missing.Value,Missing.Value, Missing.Value);
MyChart.Name = chartName;//设置标签页名
//取得表中的区域,"B2:B8"为第一列"C2:C8"为第二列
//get_Resize(Missing.Value,5);后面的表示柱状图的大小,最大
MyRange1 = MyWorkSheet.get_Range(strRange1, Type.Missing);
//-----------------chart向导,设置图表的基本信息-------------
//生成图表的方法.MyRange1(数据源)、XlChartType.xl3DColumn(图表样式)、(套用样式格式的编号)
//XlRowCol.xlColumns(分组是按行还是列)、(第几列是分类也就是x轴)、(从第几行开始取数,空出行,可以作为图例)、true(有图列)
//"KPI指标"(标题)、"姓名"(x轴标题)、"数量"(y轴标题)、"系列轴标题"(第二数字轴标题)
MyChart.ChartWizard
(MyRange1, Type.Missing, Type.Missing,
XlRowCol.xlColumns,1, 1, true,
str[0], str[1], str[2],str[3]);
//实例化chart对象的图形设置对象
Excel.ChartGroupgrp = (Excel.ChartGroup)MyChart.ChartGroups(1);
//grp.GapWidth= 10;
//grp.VaryByCategories= true;
//设置系列格式,也就是图表中字段的显示格式。SeriesCollection(1),括号中表示第几列。
Excel.Seriess1 = (Excel.Series)grp.SeriesCollection(1);
s1.Fill.ForeColor.SchemeColor = 5;//设置前景色
s1.HasDataLabels = true;//柱状头上的标签打开,显示数据
//设置统计图的标题和图例的显示
TitStyl(MyChart);
//设置x轴
Excel.AxiscategoryAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
//categoryAxis.AxisTitle.Font.Size= 7;
categoryAxis.TickLabels.Font.Size =8;//x轴标签字体大小
}
//生成图表柱状+1线
privatestatic voidchart1(Workbook MyWorkBook, Worksheet MyWorkSheet, outChart MyChart, outRange MyRange1,stringstrRange1,string strRange2,string chartName,string[]str)
{
//---------------生成图表-------------------
MyChart = (Chart)MyWorkBook.Charts.Add(Missing.Value, Missing.Value,Missing.Value, Missing.Value);
MyChart.Name = chartName;//设置标签页名
//取得表中的区域,"B2:B8"为第一列"C2:C8"为第二列
//get_Resize(Missing.Value,5);后面的表示柱状图的大小,最大
MyRange1 =MyWorkSheet.get_Range(strRange1, Type.Missing);
//-----------------chart向导,设置图表的基本信息-------------
//生成图表的方法.MyRange1(数据源)、XlChartType.xl3DColumn(图表样式)、(套用样式格式的编号)
//XlRowCol.xlColumns(分组是按行还是列)、(第几列是分类也就是x轴)、(从第几行开始取数,空出行,可以作为图例)、true(有图列)
//"KPI指标"(标题)、"姓名"(x轴标题)、"数量"(y轴标题)、"系列轴标题"(第二数字轴标题)
MyChart.ChartWizard
(MyRange1, Type.Missing, Type.Missing,
XlRowCol.xlColumns,1, 1, true,
str[0], str[1], str[2],str[3]);
//实例化chart对象的图形设置对象
Excel.ChartGroupgrp = (Excel.ChartGroup)MyChart.ChartGroups(1);
//grp.GapWidth= 10;
//grp.VaryByCategories= true;
//设置系列格式,也就是图表中字段的显示格式。SeriesCollection(1),括号中表示第几列。
Excel.Seriess2 = (Excel.Series)grp.SeriesCollection(2);
s2.HasDataLabels = true;//柱状头上的标签打开,显示数据
s2.Fill.ForeColor.SchemeColor = 5;
//s2.ChartType= XlChartType.xlLine;//柱状设置为线型
Excel.Seriess1 = (Excel.Series)grp.SeriesCollection(1);
s1.Border.ColorIndex = 3;
//s1.HasDataLabels= true;//柱状头上的标签打开,显示数据
s1.ChartType = XlChartType.xlLine;//柱状设置为线型
//设置统计图的标题和图例的显示
TitStyl(MyChart);
设置两个轴的属性,Excel.XlAxisType.xlValue对应的是Y轴,Excel.XlAxisType.xlCategory对应的是X轴:
//Excel.AxisvalueAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlValue, XlAxisGroup.xlPrimary);
//valueAxis.AxisTitle.Orientation= -90;
Excel.AxiscategoryAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
//categoryAxis.AxisTitle.Font.Size= 7;
categoryAxis.TickLabels.Font.Size =8;//x轴标签字体大小
}
//生成图表柱状+2线
privatestatic voidchart2(Workbook MyWorkBook, Worksheet MyWorkSheet, stringstrRange1, string strRange2, string chartName, string[]str)
{
//---------------生成图表-------------------
ChartMyChart = (Chart)MyWorkBook.Charts.Add(Missing.Value, Missing.Value,Missing.Value, Missing.Value);
MyChart.Name = chartName;//设置标签页名
//取得表中的区域,"B2:B8"为第一列"C2:C8"为第二列
//get_Resize(Missing.Value,5);后面的表示柱状图的大小,最大
RangeMyRange1 = MyWorkSheet.get_Range(strRange1, Type.Missing);
//-----------------chart向导,设置图表的基本信息-------------
/*生成图表的方法.MyRange1(数据源)、XlChartType.xl3DColumn(图表样式)、(套用样式格式的编号)
XlRowCol.xlColumns(分组是按行还是列)、(第几列是分类也就是x轴)、(从第几行开始取数,空出行,可以作为图例)、true(有图列)
"KPI指标"(标题)、"姓名"(x轴标题)、"数量"(y轴标题)、"系列轴标题"(第二数字轴标题)*/
MyChart.ChartWizard
(MyRange1, Type.Missing, Type.Missing,
XlRowCol.xlColumns,1, 1, true,
str[0], str[1], str[2],str[3]);
//实例化chart对象的图形设置对象
Excel.ChartGroupgrp = (Excel.ChartGroup)MyChart.ChartGroups(1);
//设置系列格式,也就是图表中字段的显示格式。SeriesCollection(1),括号中表示第几列。
Excel.Seriess3 = (Excel.Series)grp.SeriesCollection(3);
//s3.HasDataLabels= true;//柱状头上的标签打开,显示数据
s3.Border.ColorIndex = 3;//设置系列颜色
s3.Fill.ForeColor.SchemeColor = 5;//设置系列的前景色
s3.ChartType = XlChartType.xlLine;//系列设置为线型
//int a=Convert.ToInt16( s3.Border.Color);
Excel.Seriess2 = (Excel.Series)grp.SeriesCollection(2);
//s2.HasDataLabels= true;//柱状头上的标签打开,显示数据
s2.Border.ColorIndex = 1;//设置边框系列颜色
s2.ChartType = XlChartType.xlLine;//系列设置为线型
Excel.Seriess1 = (Excel.Series)grp.SeriesCollection(1);
s1.Fill.ForeColor.SchemeColor = 5;//设置系列的前景色
s1.HasDataLabels = true;//柱状头上的标签打开,显示数据
//设置统计图的标题和图例的显示
TitStyl(MyChart);
设置两个轴的属性,Excel.XlAxisType.xlValue对应的是Y轴,Excel.XlAxisType.xlCategory对应的是X轴:
//Excel.AxisvalueAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlValue, XlAxisGroup.xlPrimary);
//valueAxis.AxisTitle.Orientation= -90;
//Excel.AxiscategoryAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlCategory,XlAxisGroup.xlPrimary);
//categoryAxis.AxisTitle.Font.Name= "MS UI Gothic";
//设置x轴
Excel.AxiscategoryAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
//categoryAxis.AxisTitle.Font.Size= 7;
categoryAxis.TickLabels.Font.Size =8;//x轴标签字体大小
}
//生成图表柱状
privatestatic voidchartColum(Workbook MyWorkBook, Worksheet MyWorkSheet, outChart MyChart, outRange MyRange1, stringstrRange1, string strRange2, string chartName,string[]str)
{
//---------------生成图表-------------------
MyChart = (Chart)MyWorkBook.Charts.Add(Missing.Value, Missing.Value,Missing.Value, Missing.Value);
MyChart.Name = chartName;//设置标签页名
//取得表中的区域,"B2:B8"为第一列"C2:C8"为第二列
//get_Resize(Missing.Value,5);后面的表示柱状图的大小,最大
MyRange1 =MyWorkSheet.get_Range(strRange1, Type.Missing);
//-----------------chart向导,设置图表的基本信息-------------
//生成图表的方法.MyRange1(数据源)、XlChartType.xl3DColumn(图表样式)、(套用样式格式的编号)
//XlRowCol.xlColumns(分组是按行还是列)、(第几列是分类也就是x轴)、(从第几行开始取数,空出行,可以作为图例)、true(有图列)
//"KPI指标"(标题)、"姓名"(x轴标题)、"数量"(y轴标题)、"系列轴标题"(第二数字轴标题)
MyChart.ChartWizard
(MyRange1, XlChartType.xl3DColumn, Type.Missing,
XlRowCol.xlColumns,1, 1, true,
str[0], str[1], str[2],str[3]);
//实例化chart对象的图形设置对象
Excel.ChartGroupgrp = (Excel.ChartGroup)MyChart.ChartGroups(1);
//grp.GapWidth= 10;
//grp.VaryByCategories= true;
//现在Chart的条目的显示形状是Box,我们让它们变成圆柱形,并给它们显示加上数据标签:
Excel.Seriess = (Excel.Series)grp.SeriesCollection(1);
//s.BarShape= XlBarShape.xlCylinder;//柱状设置成圆形
s.HasDataLabels = true;//柱状头上的标签打开,显示数据
//设置统计图的标题和图例的显示
TitStyl(MyChart);
//设置x轴
Excel.AxiscategoryAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
//categoryAxis.AxisTitle.Font.Size= 7;
categoryAxis.TickLabels.Font.Size =8;//x轴标签字体大小
}
//生成图表柱状上两个系列+1线
privatestatic voidchartColumLine(Workbook MyWorkBook, Worksheet MyWorkSheet, outChart MyChart, outRange MyRange1, stringstrRange1, string strRange2, string chartName, string[]str)
{
//---------------生成图表-------------------
MyChart = (Chart)MyWorkBook.Charts.Add(Missing.Value, Missing.Value,Missing.Value, Missing.Value);
MyChart.Name = chartName;//设置标签页名
//取得表中的区域,"B2:B8"为第一列"C2:C8"为第二列
//get_Resize(Missing.Value,5);后面的表示柱状图的大小,最大
MyRange1 =MyWorkSheet.get_Range(strRange1, Type.Missing);
//-----------------chart向导,设置图表的基本信息-------------
//生成图表的方法.MyRange1(数据源)、XlChartType.xl3DColumn(图表样式)、Type.Missing是默认样式(套用样式格式的编号)Type.Missing是默认样式
//XlRowCol.xlColumns(分组是按行还是列)、(第几列是分类也就是x轴)、(从第几行开始取数,空出行,可以作为图例)、true(有图列)
//"KPI指标"(标题)、"姓名"(x轴标题)、"数量"(y轴标题)、"系列轴标题"(第二数字轴标题)
//MyChart.ChartType= XlChartType.xlColumnClustered;
//MyChart.SubType= 4;//子图表类型
MyChart.ChartWizard
(MyRange1, Type.Missing, 3,
XlRowCol.xlColumns,1, 1, true,
str[0], str[1], str[2],str[3]);
// 设置图表类型,d柱状在向导中设置会报错。所以在向导后设置。
//MyChart.ChartType= XlChartType.xlColumnClustered;
//MyChart.SubType= 2;//子图表类型
//实例化chart对象的图形设置对象
Excel.ChartGroupgrp = (Excel.ChartGroup)MyChart.ChartGroups(1);
//grp.GapWidth= 10;
//grp.VaryByCategories= true;
//声明系列对象:
Excel.Seriess3 = (Excel.Series)grp.SeriesCollection(3);
//s3.Border.ColorIndex= 5;//设置系列颜色
s3.Fill.ForeColor.SchemeColor = 3;//系列前景色
//s3.ChartType= XlChartType.xlLine;//系列设置为线型
//s.HasDataLabels= true;//柱状头上的标签打开,显示数据
Excel.Seriess2 = (Excel.Series)grp.SeriesCollection(2);
//s2.Border.ColorIndex= 3;//设置系列边框颜色
s2.Fill.ForeColor.SchemeColor = 4;系列前景色
//s2.ChartType= XlChartType.xlLine;//系列设置为线型
//s.HasDataLabels= true;//柱状头上的标签打开,显示数据
Excel.Seriess1 = (Excel.Series)grp.SeriesCollection(1);
s1.Border.ColorIndex = 3;//设置系列边框颜色
//s1.Fill.ForeColor.SchemeColor= 3;//系列前景色
s1.ChartType = XlChartType.xlLine;//系列设置为线型
//s.HasDataLabels= true;//柱状头上的标签打开,显示数据
//设置统计图的标题和图例的显示
TitStyl(MyChart);
//设置x轴
Excel.AxiscategoryAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
//categoryAxis.AxisTitle.Font.Size= 7;
categoryAxis.TickLabels.Font.Size =8;//x轴标签字体大小
}
//生成图表柱状上两个系列全满
privatestatic voidchartColum2All(Workbook MyWorkBook, Worksheet MyWorkSheet, outChart MyChart, outRange MyRange1, stringstrRange1, string strRange2, string chartName, string[]str)
{
//---------------生成图表-------------------
MyChart = (Chart)MyWorkBook.Charts.Add(Missing.Value, Missing.Value,Missing.Value, Missing.Value);
MyChart.Name = chartName;//设置标签页名
//取得表中的区域,"B2:B8"为第一列"C2:C8"为第二列
//get_Resize(Missing.Value,5);后面的表示柱状图的大小,最大
MyRange1 =MyWorkSheet.get_Range(strRange1, Type.Missing);
//-----------------chart向导,设置图表的基本信息-------------
//生成图表的方法.MyRange1(数据源)、XlChartType.xl3DColumn(图表样式)、Type.Missing是默认样式(套用样式格式的编号)Type.Missing是默认样式
//XlRowCol.xlColumns(分组是按行还是列)、(第几列是分类也就是x轴)、(从第几行开始取数,空出行,可以作为图例)、true(有图列)
//"KPI指标"(标题)、"姓名"(x轴标题)、"数量"(y轴标题)、"系列轴标题"(第二数字轴标题)
//MyChart.ChartType= XlChartType.xlColumnClustered;
//MyChart.SubType= 3;//子图表类型
MyChart.ChartWizard
(MyRange1, XlChartType.xl3DColumn, 3,
XlRowCol.xlColumns,1, 1, true,
str[0], str[1], str[2], str[3]);
// 设置图表类型,d柱状在向导中设置会报错。所以在向导后设置。
//MyChart.ChartType= XlChartType.xlColumnClustered;
//MyChart.SubType= 3;//子图表类型
//实例化chart对象的图形设置对象
Excel.ChartGroupgrp = (Excel.ChartGroup)MyChart.ChartGroups(1);
//grp.GapWidth= 10;
//grp.VaryByCategories= true;
//声明系列对象:
Excel.Seriess2 = (Excel.Series)grp.SeriesCollection(2);
//s2.Border.ColorIndex= 3;//设置系列边框颜色
s2.Fill.ForeColor.SchemeColor = 4;系列前景色
//s2.ChartType= XlChartType.xlLine;//系列设置为线型
//s.HasDataLabels= true;//柱状头上的标签打开,显示数据
Excel.Seriess1 = (Excel.Series)grp.SeriesCollection(1);
//s1.Border.ColorIndex= 3;//设置系列边框颜色
s1.Fill.ForeColor.SchemeColor = 3;//系列前景色
//s1.ChartType = XlChartType.xlLine;//系列设置为线型
//s.HasDataLabels= true;//柱状头上的标签打开,显示数据
//设置统计图的标题和图例的显示
TitStyl(MyChart);
//设置x轴
Excel.AxiscategoryAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
//categoryAxis.AxisTitle.Font.Size= 7;
categoryAxis.TickLabels.Font.Size =8;//x轴标签字体大小
}
//生成图表饼图
privatestatic voidchartCake(Workbook MyWorkBook, Worksheet MyWorkSheet, outChart MyChart, outRange MyRange1, stringstrRange1, string strRange2, string chartName,string[]str)
{
//---------------生成图表-------------------
MyChart = (Chart)MyWorkBook.Charts.Add(Missing.Value, Missing.Value,Missing.Value, Missing.Value);
MyChart.Name = chartName;//设置标签页名
MyChart.Name = chartName;
//取得表中的区域,"B2:B8"为第一列"C2:C8"为第二列
//get_Resize(Missing.Value,5);后面的表示柱状图的大小,最大
MyRange1 =MyWorkSheet.get_Range(strRange1, Type.Missing);
//-----------------chart向导,设置图表的基本信息-------------
//生成图表的方法.MyRange1(数据源)、XlChartType.xl3DColumn(图表样式)、(套用样式格式的编号)
//XlRowCol.xlColumns(分组是按行还是列)、(第几列是分类也就是x轴)、(从第几行开始取数,空出行,可以作为图例)、true(有图列)
//"KPI指标"(标题)、"姓名"(x轴标题)、"数量"(y轴标题)、"系列轴标题"(第二数字轴标题)
MyChart.ChartWizard
(MyRange1, XlChartType.xlPie, 1,
XlRowCol.xlColumns,1, 1, true,
str[0], str[1], str[2],str[3]);
//实例化chart对象的图形设置对象
Excel.ChartGroupgrp = (Excel.ChartGroup)MyChart.ChartGroups(1);
//grp.GapWidth= 10;
//grp.VaryByCategories= true;
//现在Chart的条目的显示形状是Box,我们让它们变成圆柱形,并给它们显示加上数据标签:
Excel.Seriess = (Excel.Series)grp.SeriesCollection(1);
//s.BarShape= XlBarShape.xlCylinder;//柱状设置成圆形
s.HasDataLabels = true;//柱状头上的标签打开,显示数据
s.HasLeaderLines = true;//百分比打开
//设置统计图的标题和图例的显示
TitStyl(MyChart);
设置两个轴的属性,Excel.XlAxisType.xlValue对应的是Y轴,Excel.XlAxisType.xlCategory对应的是X轴:
//Excel.AxisvalueAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlValue,XlAxisGroup.xlPrimary);
//valueAxis.AxisTitle.Orientation= -90;
//Excel.AxiscategoryAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlCategory,XlAxisGroup.xlPrimary);
//categoryAxis.AxisTitle.Font.Name= "MS UI Gothic";
}
//生成图表雷达
privatestatic voidchartRadar(Workbook MyWorkBook, Worksheet MyWorkSheet, outChart MyChart, outRange MyRange1, stringstrRange1, string strRange2, string chartName, string[]str)
{
//---------------生成图表-------------------
MyChart = (Chart)MyWorkBook.Charts.Add(Missing.Value, Missing.Value,Missing.Value, Missing.Value);
MyChart.Name = chartName;//设置标签页名
//取得表中的区域,"B2:B8"为第一列"C2:C8"为第二列
//get_Resize(Missing.Value,5);后面的表示柱状图的大小,最大
MyRange1 =MyWorkSheet.get_Range(strRange1, Type.Missing);
//-----------------chart向导,设置图表的基本信息-------------
//生成图表的方法.MyRange1(数据源)、XlChartType.xl3DColumn(图表样式)、(套用样式格式的编号)
//XlRowCol.xlColumns(分组是按行还是列)、(第几列是分类也就是x轴)、(从第几行开始取数,空出行,可以作为图例)、true(有图列)
//"KPI指标"(标题)、"姓名"(x轴标题)、"数量"(y轴标题)、"系列轴标题"(第二数字轴标题)
MyChart.ChartWizard
(MyRange1, XlChartType.xlRadar, 2,
XlRowCol.xlColumns,1, 1, true,
str[0], str[1], str[2],str[3]);
MyChart.SubType = 2;//设置子类型
//实例化chart对象的图形设置对象
Excel.ChartGroupgrp = (Excel.ChartGroup)MyChart.ChartGroups(1);
//grp.GapWidth= 10;
//grp.VaryByCategories= true;
Excel.Seriess3 = (Excel.Series)grp.SeriesCollection(2);
//s.BarShape= XlBarShape.xlCylinder;//柱状设置成圆形
//s.HasDataLabels= true;//柱状头上的标签打开,显示数据
//s.HasLeaderLines = true;//百分比打开
s3.Fill.ForeColor.SchemeColor = 4;
Excel.Seriess2 = (Excel.Series)grp.SeriesCollection(1);
//s.BarShape= XlBarShape.xlCylinder;//柱状设置成圆形
//s.HasDataLabels= true;//柱状头上的标签打开,显示数据
//s.HasLeaderLines = true;//百分比打开
s2.Fill.ForeColor.SchemeColor =3;
//设置统计图的标题和图例的显示
TitStyl(MyChart);
设置两个轴的属性,Excel.XlAxisType.xlValue对应的是Y轴,Excel.XlAxisType.xlCategory对应的是X轴:
//Excel.AxisvalueAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlValue,XlAxisGroup.xlPrimary);
//valueAxis.AxisTitle.Orientation= -90;
//Excel.AxiscategoryAxis = (Excel.Axis)MyChart.Axes(Excel.XlAxisType.xlCategory,XlAxisGroup.xlPrimary);
//categoryAxis.TickMarkSpacing
}
设置统计图的标题和图例的显示
privatestatic voidTitStyl(Chart MyChart)
{
MyChart.Legend.Position = XlLegendPosition.xlLegendPositionTop;
MyChart.ChartTitle.Font.Size = 24;
MyChart.ChartTitle.Shadow = true;
MyChart.ChartTitle.Border.LineStyle= Excel.XlLineStyle.xlContinuous;
}
}
}