[轉] C#操作EXCEL,組建圖表的全面應用

標籤：

gailzhao 原文 關於C#操作EXCEL,組建圖表的全面應用

 

　　近來我在開發一個運用C#產生EXCEL文檔的程式，其中要根據資料產生相應的圖表，該圖表對顏色和格式都有嚴格的要求，在百度和Google中搜尋了所有的相關資訊，只有部分介紹，具體格式的介紹沒有，經過我不斷的實踐和探索，終於完成了這項艱巨的任務。

　　有兩種實現方式，一種是利用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);            }        }

 

[轉] C#操作EXCEL,組建圖表的全面應用