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;        }

        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_Valuehttp://www.mamicode.com/= "";                if (ds.Tables.Count > 0)                {                    foreach (DataRow dr in ds.Tables[0].Rows)                    {                       // Console.WriteLine(dr["日期"].ToString());                        Y_Value = http://www.mamicode.com/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);            }        }