[Go] C # operations Excel, generate a comprehensive application of the chart

Gailzhao about C # operations Excel, creating a comprehensive application of charts

Recently I am developing a program that uses C # to generate Excel documents, which will generate the corresponding chart according to the data, the chart has strict requirements for color and format, search all relevant information in Baidu and Google, only partial introduction, the specific format of the introduction did not, after my continuous practice and exploration, The arduous task was finally fulfilled.

There are two implementations, one is the use of OWC11 components to complete, a use of Excel!

With the completion of the OWC11, it is suitable to generate a graphic file, then cannot be edited in the file, Excel is more suitable to use the data in Excel file directly in the file to add the chart, easy to edit later! I have tried both, because I am more suitable to use the second kind, so I developed more perfect. Publish the source code here for your reference use!

Example problem: Generate the following data in an Excel document (in part, the data is the two data of the date ), and generate the chart on the right side of the data based on the data. The Net Fund index chart is as follows.

Open-End Funds	2008-1-2	5158.0456
4246.8861	2008-1-4	5214.2867
4325.5252	2008-1-8	5252.3962

Oneusing Microsoft.Office.Interop.ExcelComponent

Using system.io;using system.runtime.interopservices;using Excel = microsoft.office.interop.excel;private void Btn_ Click (object sender, EventArgs e) {//Create an Excel document Createexcel ("title", "document.") XLS "," Worksheet name ");} private void Createexcel (string title, String fileName, String sheetnames) {//file name to be generated string filen            ame = FileName;            String FilePath = Strcurrentpath + FileName;            FileInfo fi = new FileInfo (FilePath); if (FI.            Exists)//Determine if the file already exists and delete it if it exists! {fi.            Delete (); } if (sheetnames = null && sheetnames! = "") {Excel.Application m_excel = n                EW Excel.Application ();//Create an Excel object (start the EXCEL.EXE process at the same time) M_excel.sheetsinnewworkbook = 1;//the number of worksheets Excel._workbook M_book = (Excel._workbook) (M_EXCEL.WORKBOOKS.ADD (Missing.Value));//Add a new workbook Excel._workshe                 ET M_sheet; #region processing the DataSet ds= scdata.ListData ("Exec vote_2008.dbo. P_voteresult_update "+ Int.                Parse (fdate)); if (ds.                        tables.count<= 0) {MessageBox.Show ("No latest data!");                 Return } datatabletosheet (title, ds.                Tables[0], M_sheet, M_book, 0); #endregion #region Save Excel, clear the process M_book.saveas (FilePath, Missing.Value, Missing.Value, Missi Ng. 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, nulls, 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 ();//Closes the form #endregion}} #region writes data from the DataTable to the specified sheet in Excel///<sum Mary>////writes data in DataTable to the specified sheet in Excel///</summary>//<param name= "DT" ></para m>//<param name= "M_sheet" ></param> public void Datatabletosheet (string title, DataTable DT            , Excel._worksheet M_sheet, Excel._workbook m_book, int startrow) {            The following is a complete list of data in Excel excel.range Range = M_sheet.get_range (m_sheet.cells[1, 1], m_sheet.cells[1, 2]); Range.  MergeCells = true; Merge cell range.   Font.Bold = true;            Bold cell characters//write topic m_sheet.cells[startrow, startrow] = title; int rownum = dt.           rows.count;//Number of rows int columnnum = dt.   columns.count;//number of columns int num = rownum + 2; Get the maximum number of rows in the data//write column header for (int j = 0; J < Columnnum; J + +) {int bt_s                    Tartrow = StartRow + 1; Write the field name to the document M_sheet.cells[bt_startrow, 1 + j] = dt. COLUMNS[J].                     ColumnName; In-cell background color M_sheet.get_range (M_sheet.cells[bt_startrow, 1 + j], M_sheet.cells[bt_startrow, 1 + j]).             Interior.ColorIndex = 15; }//Line-write data 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 ();          Generate Chart Createchart (M_book, M_sheet, num) in the current worksheet based on data; } 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;//Set the graphics//settings data range M_Book.ActiveChart.SetSour Cedata (M_sheet.get_range ("A2", "C" + Num.)            ToString ()), Excel.XlRowCol.xlColumns);            M_Book.ActiveChart.Location (Excel.XlChartLocation.xlLocationAutomatic, title);            The following is given to the chart at the specified position m_Book.ActiveChart.Location (Excel.XlChartLocation.xlLocationAsObject, m_sheet.name);            oResizeRange = (Excel.Range) M_Sheet.Rows.get_Item (ten, Missing.Value); M_Sheet.Shapes.Item ("Chart 1").  Top = (float) (double) oresizerange.top;  Adjust the position of the chart top margin oResizeRange = (excel.range) m_Sheet.Columns.get_Item (6, Missing.Value); Adjust the position of the chart to the left margin//M_Sheet.Shapes.Item ("Chart 1").            left = (float) (double) oresizerange.left; M_Sheet.Shapes.Item ("Chart 1"). Width= 400; Adjust the width of the chart m_Sheet.Shapes.Item ("Chart 1").  Height = 250;  Adjust the height of the chart m_Book.ActiveChart.PlotArea.Interior.ColorIndex = 19;            Set the background color of the plot area M_Book.ActiveChart.PlotArea.Border.LineStyle = excel.xllinestyle.xllinestylenone;//Set the plot area border line   M_Book.ActiveChart.PlotArea.Width = 400; Set Plot Area width//m_book.activechart.chartarea.interior.colorindex = 10; Set the background color of the entire chart//m_book.activechart.chartarea.border.colorindex = 8;//Sets the border color of the entire chart M_book.activechart . ChartArea.Border.LineStyle = excel.xllinestyle.xllinestylenone;//Set border line m_Book.ActiveChart.HasDataTable = False            ; Sets the position and format of the Legend legend M_Book.ActiveChart.Legend.Top = 20.00; Set the top margin of the legend M_Book.ActiveChart.Legend.Left = 60.00;//Specify the left margin of the legend 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 = "Song Body"; M_Book.ActiveChart.Legend.Position = excel.xllegendposition.xllegendpositiontop;//Sets the location of the legend M_book.activechart.  Legend.Border.LineStyle = excel.xllinestyle.xllinestylenone;//Set legend border Line//set X-axis display 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 The color of the horizontal line xaxis.hastitle = false;            Xaxis.minimumscale = 1500;            Xaxis.maximumscale = 6000;            XAxis.TickLabels.Font.Name = "Song Body";             XAxis.TickLabels.Font.Size = 9; Sets the display of the y-axis excel.axis YAxis = (excel.axis) m_Book.ActiveChart.Axes (Excel.XlAxisType.xlCategory, Excel.xlaxisgroup            . xlprimary);     yaxis.ticklabelspacing = 30;       YAxis.TickLabels.NumberFormat = "M-month D-Day";            yAxis.TickLabels.Orientation = excel.xlticklabelorientation.xlticklabelorientationhorizontal;//y axis display direction, horizontal or vertical, etc.            YAxis.TickLabels.Font.Size = 8;            YAxis.TickLabels.Font.Name = "Song Body";             M_Book.ActiveChart.Floor.Interior.ColorIndex = 8;            /*** Below is the set title * * * * M_BOOK.ACTIVECHART.HASTITLE=TRUE;            M_Book.ActiveChart.ChartTitle.Text = "equity index";            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; }

second, the use of 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) { 。。。。。。                (IBID.) ... Use the OWC11 component to draw Showchart (OWC.  Chartcharttypeenum.chcharttypeline); Generate a chart file//insert a chart file into an Excel document 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-Axis OWC.  ChAxis axis1 = objchart.axes[1];                Y-Axis objchart.type = Type;      Objchart.haslegend = true;          ObjChart.Legend.Position = OWC.                Chartlegendpositionenum.chlegendpositiontop;                Objchart.hastitle = true;                objChart.Title.Caption = "equity index graph"; Objchart.axes[0].                HasTitle = true; Objchart.axes[0].                Title.caption = "date"; OBJCHART.AXES[1].                HasTitle = true; OBJCHART.AXES[1].                Title.caption = "value";                OBJCHART.SERIESCOLLECTION.ADD (0); Objchart.seriescollection[0]. SetData (OWC. Chartdimensionsenum.chdimseriesnames, + (int) OWC.                Chartspecialdatasourcesenum.chdataliteral, "Open-end Fund"); 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["Date").                        ToString ()); Y_value = Y_value + dr["date"].                        ToString () + '/t '; x_value1 = x_value1 + dr["open Fund"].                        ToString () + '/t '; X_value2 = x_value2 + dr["partial stock Fund"].                    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, "Partial-stock fund"); 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; Line Bold 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";   Background color of chart area//ObjChart.Interior.Color = "green";                ObjChart.Interior.BackColor = "Yellow"; Axis.            tickmarkspacing = 50; } catch (Exception ex) {//timer1.                Enabled = false; MessageBox.Show (ex.            Message);                } finally {_type = Type;                  Output as a GIF file. String Strabsolutepath = "E://chart.gif";            Generate graphics file Axchartspace1.exportpicture (Strabsolutepath, "GIF", 600, 350); }        }

[Go] C # operations Excel, generate a comprehensive application of the chart