VSTO学习之二

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;

        }

    }

}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值