[转] C#操作EXCEL,生成图表的全面应用

2022-09-17 17:00:13

　　近来我在开发一个运用C#生成EXCEL文档的程序，其中要根据数据生成相应的图表，该图表对颜色和格式都有严格的要求，在百度和谷歌中搜索了所有的相关信息，只有部分介绍，具体格式的介绍没有，经过我不断的实践和探索，终于完成了这项艰巨的任务。

　　有两种实现方式，一种是利用OWC11组件完成，一种运用Excel完成！

　　运用OWC11的完成，适合生成一个图形文件，之后不能不在文件中编辑；运用Excel则更适合利用EXCEL文件中的数据直接在文件中加入图表，方便以后编辑！两种我都尝试了，由于我更适合于使用第二种，所以我开发的较完善。在这里公布源码供大家参考使用！

　　实例问题：在EXCEL文档中生成如下数据（部分，数据中是日期对应的两种数据），在数据右侧根据数据生成图表。基金净值指数图表如下。

开放式基金	2008-1-2	5158.0456
4246.8861	2008-1-4	5214.2867
4325.5252	2008-1-8	5252.3962

一、利用Microsoft.Office.Interop.Excel组件

using System.IO;

using System.Runtime.InteropServices;

using Excel = Microsoft.Office.Interop.Excel;

private void Btn_Click(object sender, EventArgs e)

{

        //创建一个EXCEL文档

        CreateExcel("标题","文档.XLS", "工作表名");

}

private void CreateExcel(string title,  string fileName, string sheetNames)

{

            //待生成的文件名称

            string FileName = fileName;

            string FilePath = strCurrentPath + FileName;

            FileInfo fi = new FileInfo(FilePath);

            if (fi.Exists)     //判断文件是否已经存在,如果存在就删除!

            {

                fi.Delete();

            }

            if (sheetNames != null && sheetNames != "")

            {

                Excel.Application m_Excel = new Excel.Application();//创建一个Excel对象(同时启动EXCEL.EXE进程)

                m_Excel.SheetsInNewWorkbook = 1;//工作表的个数

                Excel._Workbook m_Book = (Excel._Workbook)(m_Excel.Workbooks.Add(Missing.Value));//添加新工作簿

                Excel._Worksheet m_Sheet; 

                #region 处理

                DataSet ds= ScData.ListData("exec Vote_2008.dbo.P_VoteResult_Update " + int.Parse(fdate));

                if (ds.Tables.Count<= 0)

                {

                        MessageBox.Show("没有最新数据!");

                        return;

                 }

                 DataTableToSheet(title,  ds.Tables[0], m_Sheet, m_Book, 0);

                #endregion

                #region 保存Excel,清除进程

                m_Book.SaveAs(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,   Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                //m_Excel.ActiveWorkbook._SaveAs(FilePath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

                m_Book.Close(false, Missing.Value, Missing.Value);

                m_Excel.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Book);

                System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Excel);

                m_Book = null;

                m_Sheet = null;

                m_Excel = null;

                GC.Collect();

                //this.Close();//关闭窗体

                #endregion

            }

   }

        #region 将DataTable中的数据写到Excel的指定Sheet中

        /// <summary>

        /// 将DataTable中的数据写到Excel的指定Sheet中

        /// </summary>

        /// <param name="dt"></param>

        /// <param name="m_Sheet"></param>

        public void DataTableToSheet(string title, DataTable dt, Excel._Worksheet m_Sheet,

                                    Excel._Workbook m_Book,   int startrow)

        {

            //以下是填写EXCEL中数据

            Excel.Range range = m_Sheet.get_Range(m_Sheet.Cells[1, 1], m_Sheet.Cells[1, 2]);

            range.MergeCells = true;  //合并单元格

            range.Font.Bold = true;   //加粗单元格内字符

            //写入题目

            m_Sheet.Cells[startrow, startrow] = title;

            int rownum = dt.Rows.Count;//行数

            int columnnum = dt.Columns.Count;//列数

            int num = rownum + 2;   //得到数据中的最大行数

            //写入列标题

            for (int j = 0; j < columnnum; j++)

            {

                    int bt_startrow = startrow + 1;

                    //将字段名写入文档

                    m_Sheet.Cells[bt_startrow, 1 + j] = dt.Columns[j].ColumnName;

                     //单元格内背景色

                    m_Sheet.get_Range(m_Sheet.Cells[bt_startrow, 1 + j], m_Sheet.Cells[bt_startrow, 1 + j]).Interior.ColorIndex = 15;             }

            //逐行写入数据

            for (int i = 0; i < rownum; i++)

            {

                for (int j = 0; j < columnnum; j++)

                {

                        m_Sheet.Cells[startrow + 2 + i, 1 + j] = dt.Rows[i][j].ToString();

                }

            }

            m_Sheet.Columns.AutoFit();

          //在当前工作表中根据数据生成图表

           CreateChart(m_Book, m_Sheet, num);

        }

       private void CreateChart(Excel._Workbook m_Book,Excel._Worksheet m_Sheet,int num)

        {

            Excel.Range oResizeRange;

            Excel.Series oSeries;

            m_Book.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value);

            m_Book.ActiveChart.ChartType = Excel.XlChartType.xlLine;//设置图形

            //设置数据取值范围

            m_Book.ActiveChart.SetSourceData(m_Sheet.get_Range("A2", "C" + num.ToString()), Excel.XlRowCol.xlColumns);

            //m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, title);

            //以下是给图表放在指定位置

            m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, m_Sheet.Name);

            oResizeRange = (Excel.Range)m_Sheet.Rows.get_Item(10, Missing.Value);

            m_Sheet.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;  //调图表的位置上边距

            oResizeRange = (Excel.Range)m_Sheet.Columns.get_Item(6, Missing.Value);  //调图表的位置左边距

           // m_Sheet.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;

            m_Sheet.Shapes.Item("Chart 1").Width = 400;   //调图表的宽度

            m_Sheet.Shapes.Item("Chart 1").Height = 250;  //调图表的高度

            m_Book.ActiveChart.PlotArea.Interior.ColorIndex = 19;  //设置绘图区的背景色

            m_Book.ActiveChart.PlotArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//设置绘图区边框线条

            m_Book.ActiveChart.PlotArea.Width = 400;   //设置绘图区宽度

            //m_Book.ActiveChart.ChartArea.Interior.ColorIndex = 10; //设置整个图表的背影颜色

            //m_Book.ActiveChart.ChartArea.Border.ColorIndex = 8;// 设置整个图表的边框颜色

            m_Book.ActiveChart.ChartArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//设置边框线条

            m_Book.ActiveChart.HasDataTable = false;

            //设置Legend图例的位置和格式

            m_Book.ActiveChart.Legend.Top = 20.00; //具体设置图例的上边距

            m_Book.ActiveChart.Legend.Left = 60.00;//具体设置图例的左边距

            m_Book.ActiveChart.Legend.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

            m_Book.ActiveChart.Legend.Width = 150;

            m_Book.ActiveChart.Legend.Font.Size = 9.5;

            //m_Book.ActiveChart.Legend.Font.Bold = true;

            m_Book.ActiveChart.Legend.Font.Name = "宋体";

            //m_Book.ActiveChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;//设置图例的位置

            m_Book.ActiveChart.Legend.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//设置图例边框线条

            //设置X轴的显示

            Excel.Axis xAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);

            xAxis.MajorGridlines.Border.LineStyle = Excel.XlLineStyle.xlDot;

            xAxis.MajorGridlines.Border.ColorIndex = 1;//gridLine横向线条的颜色

            xAxis.HasTitle = false;

            xAxis.MinimumScale = 1500;

            xAxis.MaximumScale = 6000;

            xAxis.TickLabels.Font.Name = "宋体";

            xAxis.TickLabels.Font.Size = 9;

            //设置Y轴的显示

            Excel.Axis yAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);

            yAxis.TickLabelSpacing = 30;

            yAxis.TickLabels.NumberFormat = "M月D日";

            yAxis.TickLabels.Orientation = Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;//Y轴显示的方向,是水平还是垂直等

            yAxis.TickLabels.Font.Size = 8;

            yAxis.TickLabels.Font.Name = "宋体";

            //m_Book.ActiveChart.Floor.Interior.ColorIndex = 8;

            /***以下是设置标题*****

            m_Book.ActiveChart.HasTitle=true;

            m_Book.ActiveChart.ChartTitle.Text = "净值指数";

            m_Book.ActiveChart.ChartTitle.Shadow = true;

            m_Book.ActiveChart.ChartTitle.Border.LineStyle = Excel.XlLineStyle.xlContinuous;

            */

            oSeries = (Excel.Series)m_Book.ActiveChart.SeriesCollection(1);

            oSeries.Border.ColorIndex = 45;

            oSeries.Border.Weight = Excel.XlBorderWeight.xlThick;

            oSeries = (Excel.Series)m_Book.ActiveChart.SeriesCollection(2);

            oSeries.Border.ColorIndex = 9;

            oSeries.Border.Weight = Excel.XlBorderWeight.xlThick;

        }

二、利用Microsoft.Office.Interop.OWC11

        using OWC = Microsoft.Office.Interop.Owc11;

        public void DataTableToSheet(string title, DataTable dt, Excel._Worksheet m_Sheet, Excel._Workbook m_Book,   int startrow)

        {

            。。。。。。（同上篇）。。。。。。

                //使用OWC11组件画图

                showChart(OWC.ChartChartTypeEnum.chChartTypeLine);  //生成图表文件

                //将图表文件插入到EXCEL文档中

                m_Sheet.Shapes.AddPicture("E://chart.gif", MsoTriState.msoFalse, MsoTriState.msoTrue, 250, 100, 350, 250 );

        }

    private void showChart(OWC.ChartChartTypeEnum Type)

        {

            OWC.ChartChartTypeEnum _Type;

            OWC.ChartSpace axChartSpace1 = new OWC.ChartSpaceClass();

            try

            {

                axChartSpace1.Clear();

                OWC.ChChart objChart = axChartSpace1.Charts.Add(0);

                OWC.ChAxis axis = objChart.Axes[0];   //X轴

                OWC.ChAxis axis1 = objChart.Axes[1];  //Y轴

                objChart.Type = Type;

                objChart.HasLegend = true;

                objChart.Legend.Position = OWC.ChartLegendPositionEnum.chLegendPositionTop;

                //objChart.HasTitle = true;

                //objChart.Title.Caption = "净值指数图";

                //objChart.Axes[0].HasTitle = true ;

                //objChart.Axes[0].Title.Caption = "日期";

                //objChart.Axes[1].HasTitle = true;

                //objChart.Axes[1].Title.Caption = "数值";

                objChart.SeriesCollection.Add(0);

                objChart.SeriesCollection[0].SetData(OWC.ChartDimensionsEnum.chDimSeriesNames,

                 +(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, "开放式基金");

                DataSet ds = ScData.ListData("exec zb_LiCaiZhouBao " + int.Parse(txtStartDate.Text.Replace("-","") ) + ",1");

                string X_Value1 = "";

                string X_Value2 = "";

                string Y_Value = "";

                if (ds.Tables.Count > 0)

                {

                    foreach (DataRow dr in ds.Tables[0].Rows)

                    {

                       // Console.WriteLine(dr["日期"].ToString());

                        Y_Value = Y_Value + dr["日期"].ToString() + '/t';

                        X_Value1 = X_Value1 + dr["开放式基金"].ToString() + '/t';

                        X_Value2 = X_Value2 + dr["偏股型基金"].ToString() + '/t';

                    }

                }

                objChart.SeriesCollection[0].SetData(OWC.ChartDimensionsEnum.chDimCategories,

                 +(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, Y_Value);

                              objChart.SeriesCollection[0].SetData(OWC.ChartDimensionsEnum.chDimValues,

                 (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, X_Value1);

                objChart.SeriesCollection.Add(1);

                objChart.SeriesCollection[1].SetData(OWC.ChartDimensionsEnum.chDimSeriesNames,

                 +(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, "偏股型基金");

                objChart.SeriesCollection[1].SetData(OWC.ChartDimensionsEnum.chDimCategories,

                 +(int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, Y_Value);

                objChart.SeriesCollection[1].SetData(OWC.ChartDimensionsEnum.chDimValues,

                 (int)OWC.ChartSpecialDataSourcesEnum.chDataLiteral, X_Value2);

                objChart.SeriesCollection[0].Line.Color = "orange";

                //objChart.SeriesCollection[0].Line.Weight = OWC.ChLine.LineWeightEnum.owcLineWeightThick;   //线条加粗

                objChart.SeriesCollection[1].Line.Color = "maroon";

                //objChart.SeriesCollection[1].Line.Weight = OWC.LineWeightEnum.owcLineWeightThick; 

                axis.TickMarkSpacing = 30;

                axis.HasTickLabels = true;

                axis.TickLabelSpacing =30;

                axis1.HasMajorGridlines = true;

                axis1.MajorGridlines.Line.DashStyle = OWC.ChartLineDashStyleEnum.chLineDashDotDot;//.chLineDashDot;

                //axis1.MajorGridlines.Line.Color = "orange";

                objChart.PlotArea.Interior.Color = "LightYellow";    //图表区的背景色

                // objChart.Interior.Color = "green";

               //objChart.Interior.BackColor = "yellow";

                //axis.TickMarkSpacing = 50;

            }

            catch (Exception ex)

            {

                //timer1.Enabled = false;

                MessageBox.Show(ex.Message);

            }

            finally

            {

                _Type = Type;

                //输出成GIF文件.

                string strAbsolutePath = "E://chart.gif"; //生成图形文件

                axChartSpace1.ExportPicture(strAbsolutePath, "GIF", 600, 350);

            }

        }

码农公寓

一、利用Microsoft.Office.Interop.Excel组件

二、利用Microsoft.Office.Interop.OWC11

相关文章