c#生成Excel饼图、柱状图

 

using Excel = Microsoft.Office.Interop.Excel;

namespace PrintProject.ChuangKou
{
    public partial class Chart
    {
    
        List<string> txDataPie = new List<string>() { "不合格", "合格" };
        List<int> tyDataPie=new List<string>() {826,185};
        List<string> txDataColumn=new List<string>() {"AAA","BBB","CCC"};
        List<int> tyDataNo=new List<string>() {0,0,641};
        List<int> tyDataOk=new List<string>() {0,0,185};

        private void btn_out_Click(object sender, EventArgs e)
        {
            //申明保存对话框   
            SaveFileDialog dlg = new SaveFileDialog();
            //默然文件后缀   
            dlg.DefaultExt = "xls ";
            //文件后缀列表   
            dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
            //默然路径是系统当前路径   
            dlg.InitialDirectory = System.IO.Directory.GetCurrentDirectory();
            //打开保存对话框   
            if (dlg.ShowDialog() == DialogResult.Cancel) return;
            //返回文件路径   
            string fileNameString = dlg.FileName;
            //验证strFileName是否为空或值无效   
            if (fileNameString.Trim() == " ")
            { return; }
            Excel.Application objExcel = null;
            Excel.Workbook objWorkbook = null;
            Excel.Worksheet objsheet = null;
            try
            {
                //申明对象   
                objExcel = new Excel.Application();
                objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
               //合格率
                objExcel.Cells[1, 1] = "开始时间"; 
                objExcel.Cells[1,2] = dateTimePicker2.Text;
                objExcel.Cells[1, 3] = "结束时间"; 
                objExcel.Cells[1,4] = dateTimePicker2.Text;
                objExcel.Cells[2, 1] = "不合格";
                objExcel.Cells[3, 1] = "合格";
                objExcel.Cells[2,2] = tyDataPie[0];
                objExcel.Cells[3,2] = tyDataPie[1];
                //饼图
                Excel.Range oResizeRange;
                Excel.Chart xlChart = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
                xlChart.ChartType= Excel.XlChartType.xlPie;//设置图形
                xlChart.SetSourceData(objsheet.get_Range("A2", "B3"),Excel.XlRowCol.xlColumns);
                objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, "合格率");
                objWorkbook.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);
                oResizeRange = (Excel.Range)objsheet.Rows.get_Item(7, Missing.Value);

                objsheet.Shapes.Item("Chart 1").Top = 70;  //调图表的位置上边距
                objsheet.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
                objsheet.Shapes.Item("Chart 1").Width = 200;   //调图表的宽度
                objsheet.Shapes.Item("Chart 1").Height = 150;  //调图表的高度
                #region 管理人员
                int col = 6;
                objExcel.Cells[2, col] = "用户名";
                objExcel.Cells[2, col+1] = "合格";
                objExcel.Cells[2, col + 2] = "不合格";
                int row = 3;
                for (int i=0;i< txDataColumn.Count;i++) {
                    objExcel.Cells[row, col] = txDataColumn[i];
                    row++;
                }
                row = 3;
                for (int i = 0; i < tyDataOk.Count; i++)
                {
                    objExcel.Cells[row, col+1] = tyDataOk[i];
                    row++;
                }
                row = 3;
                for (int i = 0; i < tyDataNo.Count; i++)
                {
                    objExcel.Cells[row, col + 2] = tyDataNo[i];
                    row++;
                }
                #endregion
                //柱状图
                Excel.Chart xlChart2 = (Excel.Chart)objWorkbook.Charts.Add(Type.Missing, objsheet, Type.Missing, Type.Missing);
                Excel.Range cellRange = objsheet.get_Range((Excel.Range)objsheet.Cells[2, 6], (Excel.Range)objsheet.Cells[3 + txDataColumn.Count - 1, 8]);
                //1-cellRange:数据源的范围，2-图表类型，3-Type.Missing，4-在图表上将列或行用作数据系列的方式，5、6-第五个第六个参数设置图表的x轴和y轴分别是数据源的哪些列/行，7-图表是否有图例，8、9、10-设置标题
                xlChart2.ChartWizard(cellRange,
                                Excel.XlChartType.xlColumnStacked, Type.Missing,
                                Excel.XlRowCol.xlColumns, 1, 1, true,
                                "管理人员校准情况", "用户名", "校准个数",
                                "");
                xlChart2.Location(Excel.XlChartLocation.xlLocationAsObject, objsheet.Name);
                Excel.Range oResizeRange1 = (Excel.Range)objsheet.Rows.get_Item(1);
                Excel.Range oResizeRange2 = (Excel.Range)objsheet.Columns.get_Item(10);
                objsheet.Shapes.Item("Chart 2").Top = oResizeRange1.Top;  //调图表的位置上边距--1行的高度
                objsheet.Shapes.Item("Chart 2").Left = (float)(double)oResizeRange2.Left;//调图表的位置左边距--10列的宽度
                objsheet.Shapes.Item("Chart 2").Width = 300;   //调图表的宽度
                objsheet.Shapes.Item("Chart 2").Height = 200;  //调图表的高度
                //保存文件   
                objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            finally
            {
                //关闭Excel应用   
                if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                if (objExcel != null) objExcel.Quit();
                objsheet = null;
                objWorkbook = null;
                objExcel = null;
            }
        }
    }
}