DataSet To Excel&Chart

using System;
using System.Drawing;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Reflection;

namespace ExcelOperator
{
    /// <summary>
    /// For Excel Operation
    /// </summary>
    public class ExcelObject
    {
        public static bool DoExcelOperator(string fileName)
        {
            //Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            //Excel.Workbooks wbs = excel.Workbooks.Open(fileName, Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);           
            //Excel.QueryTables m_objQryTables = null;
            //Excel.QueryTable m_objQryTable = null;
            Excel.Application m_objExcel = null;
            Excel.Workbooks m_objBooks = null;
            Excel.Workbook m_objBook = null;
            Excel.Sheets m_objSheets = null;
            Excel.Worksheet m_objSheet = null;
            Excel.Range m_objRange = null;

            try
            {

                m_objExcel = new Excel.Application();
                m_objExcel.Visible = false;
                m_objExcel.DisplayAlerts = false;

                m_objBooks = m_objExcel.Workbooks;
                m_objBooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                m_objBook = (Excel.Workbook)m_objBooks.get_Item(1);
                m_objSheets = (Excel.Sheets)m_objBook.Worksheets;

                for (int i = 1; i <= m_objSheets.Count; i++)
                {
                    try
                    {
                        m_objSheet = (Excel.Worksheet)m_objSheets.get_Item(i);
                        m_objSheet.Cells.Columns.AutoFit();
                        m_objSheet.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
                        //m_objSheet.Cells.Columns.Cells.AutoFit();
                        m_objRange = m_objSheet.get_Range("A1", "D1");
                        m_objRange.Font.Color = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
                        m_objRange.Font.Size = 10;
                        m_objRange.Borders.LineStyle = Excel.Constants.xlSolid;
                        m_objRange.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 0, 0).ToArgb();
                        int n = 1;
                        while (m_objSheet.get_Range("A" + (++n).ToString(), Type.Missing).Text.ToString() != "") ;

                        m_objRange = m_objSheet.get_Range("A2", "D" + n.ToString());
                        m_objRange.Borders.Weight = 4;
                        m_objRange.Borders.LineStyle = 3;

                        m_objRange.NumberFormat = "0.00";
                        m_objRange.HorizontalAlignment = Excel.Constants.xlCenter;

                        for (int j = 2; j < n; j++)
                        {
                            m_objSheet.Cells[j, 2] = m_objSheet.get_Range("B" + j.ToString(), Type.Missing).Text.ToString();
                        }
                    }
                    catch (Exception ex)
                    {
                        m_objSheet.Cells[1, 20] = ex.ToString();
                    }
                                      
                }

                //m_objQryTables = m_objSheet.QueryTables;
                //string sqlstr = "SELECT [day01],[day02],[day03],[day04],[day05],[day06],[day07],[day08],[day09],[day10],[day11],[day12],[day13],[day14]";
                //sqlstr += ",[week01] ,[week02],[week03],[week04],[week05],[week06],[week07],[week08],[week09],[week10],[week11],[week12],[week13],[week14]";
                //sqlstr += ",[week15],[week16],[week17],[week18],[week19],[week20],[week21],[week22],[week23],[week24]";
                //sqlstr += " FROM [GDS_Service].[dbo].[GDS_Service_Report_Base] order by groupID ,id";
                ////¿ÉÒÔ·ÅÔÚÅäÖÃÎļþÀï34       
                //string conn = @"Provider=SQLOLEDB.1;Data Source=MININT-G87PHNA/SQLEXPRESS;uid=xijun_ke;Password=12345678;  Initial Catalog=GDS_Service;Persist Security Info=False;";
                //m_objQryTable = (Excel.QueryTable)m_objQryTables.Add("OLEDB;" + conn, m_objRange, sqlstr);
                //m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
                //m_objQryTable.Refresh(false);
                m_objBook.RefreshAll();

                m_objBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                m_objBook.Close(false, Type.Missing, Type.Missing);
                m_objExcel.Quit();
                GC.Collect();
            }
            catch (Exception ex)
            {
                m_objBook.RefreshAll();
                m_objBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                m_objBook.Close(false, Type.Missing, Type.Missing);
                m_objExcel.Quit();
                GC.Collect();
                return false;
            }
            return true;
        }

        public static bool DoExcelOperator_1(string fileName)
        {
            //Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            //Excel.Workbooks wbs = excel.Workbooks.Open(fileName, Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);           
            //Excel.QueryTables m_objQryTables = null;
            //Excel.QueryTable m_objQryTable = null;
            Excel.Application m_objExcel = null;
            Excel.Workbooks m_objBooks = null;
            Excel.Workbook m_objBook = null;
            Excel.Sheets m_objSheets = null;
            Excel.Worksheet m_objSheet = null;
            Excel.Range m_objRange = null;

            try
            {

                m_objExcel = new Excel.Application();
                m_objExcel.Visible = false;
                m_objExcel.DisplayAlerts = false;

                m_objBooks = m_objExcel.Workbooks;
                m_objBooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                m_objBook = (Excel.Workbook)m_objBooks.get_Item(1);
                m_objSheets = (Excel.Sheets)m_objBook.Worksheets;

                for (int i = 1; i <= m_objSheets.Count; i++)
                {
                    m_objSheet = (Excel.Worksheet)m_objSheets.get_Item(i);
                    m_objSheet.Cells.Columns.AutoFit();
                    m_objSheet.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
                    //m_objSheet.Cells.Columns.Cells.AutoFit();
                    m_objRange = m_objSheet.get_Range("A1", "D1");
                    m_objRange.Font.Color = System.Drawing.Color.FromArgb(255, 255, 255).ToArgb();
                    m_objRange.Font.Size = 10;
                    m_objRange.Borders.LineStyle = Excel.Constants.xlSolid;
                    m_objRange.Cells.Interior.Color = System.Drawing.Color.FromArgb(255, 0, 0).ToArgb();
                    int n = 1;
                    while (m_objSheet.get_Range("A" + (++n).ToString(), Type.Missing).Text.ToString() != "") ;

                    m_objRange = m_objSheet.get_Range("A2", "D" + n.ToString());
                    m_objRange.Borders.Weight = 4;
                    m_objRange.Borders.LineStyle = 3;

                    m_objRange.NumberFormat = "0";
                    m_objRange.HorizontalAlignment = Excel.Constants.xlCenter;

                    for (int j = 2; j < n; j++)
                    {
                        m_objSheet.Cells[j, 2] = m_objSheet.get_Range("B" + j.ToString(), Type.Missing).Text.ToString();
                    }

                    SetChartValue(m_objBook, m_objSheet, n - 1);
                }

                //m_objQryTables = m_objSheet.QueryTables;
                //string sqlstr = "SELECT [day01],[day02],[day03],[day04],[day05],[day06],[day07],[day08],[day09],[day10],[day11],[day12],[day13],[day14]";
                //sqlstr += ",[week01] ,[week02],[week03],[week04],[week05],[week06],[week07],[week08],[week09],[week10],[week11],[week12],[week13],[week14]";
                //sqlstr += ",[week15],[week16],[week17],[week18],[week19],[week20],[week21],[week22],[week23],[week24]";
                //sqlstr += " FROM [GDS_Service].[dbo].[GDS_Service_Report_Base] order by groupID ,id";
                ////¿ÉÒÔ·ÅÔÚÅäÖÃÎļþÀï34       
                //string conn = @"Provider=SQLOLEDB.1;Data Source=MININT-G87PHNA/SQLEXPRESS;uid=xijun_ke;Password=12345678;  Initial Catalog=GDS_Service;Persist Security Info=False;";
                //m_objQryTable = (Excel.QueryTable)m_objQryTables.Add("OLEDB;" + conn, m_objRange, sqlstr);
                //m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
                //m_objQryTable.Refresh(false);
                m_objBook.RefreshAll();

                m_objBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                m_objBook.Close(false, Type.Missing, Type.Missing);
                m_objExcel.Quit();
                GC.Collect();
            }
            catch (Exception ex)
            {
                m_objBook.Close(false, Type.Missing, Type.Missing);
                m_objExcel.Quit();
                GC.Collect();
                return false;
            }
            return true;
        }

        /// <summary>
        /// Constract data from special Cell
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="rowCount"></param>
        public static void SetChartValue(Excel.Workbook wbs, Excel.Worksheet ws, int rowCount)
        {
            try
            {
                if (rowCount < 2)
                {
                    return;
                }

                int maxCol = 2;
                int startCol = 2;
                int startRow = rowCount + 4;

                ws.Cells[startRow, 1] = ws.Cells[2, 1];
                ws.Cells[startRow, 2] = ws.Cells[2, 2];

                for (int i = 3; i <= rowCount; i++)
                {
                    if (ws.get_Range("A" + startRow.ToString(), Type.Missing).Text.Equals(ws.get_Range("A" + i.ToString(), Type.Missing).Text))
                    {
                        ws.Cells[startRow, ++startCol] = ws.Cells[i, 2];
                        if (startCol > maxCol)
                        {
                            maxCol = startCol;
                        }
                    }
                    else
                    {
                        ++startRow;
                        startCol = 2;
                        ws.Cells[startRow, 1] = ws.Cells[i, 1];
                        ws.Cells[startRow, startCol] = ws.Cells[i, 2];
                    }
                }

                for (int j = 1; j < maxCol; j++)
                {
                    ws.Cells[rowCount + 3, j + 1] = j.ToString() + " Times";
                }

                Excel.Range m_objRange = ws.get_Range(ws.Cells[rowCount + 3, 1], ws.Cells[startRow, maxCol]);
                m_objRange.Borders.Weight = 4;
                m_objRange.Borders.LineStyle = 3;

                DrawChart(wbs, ws, m_objRange);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
        }

        public static void DrawChart_backup(Excel.Workbook wb, Excel.Worksheet ws, Excel.Range rge)
        {
            try
            {
                wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                wb.ActiveChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
                wb.ActiveChart.SetSourceData(rge, Excel.XlRowCol.xlRows);
                wb.ActiveChart.Location(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAutomatic, ws.Name);
                wb.ActiveChart.HasTitle = true;
                wb.ActiveChart.ChartTitle.Text = "EQP HSMS Connection History";
                wb.ActiveChart.HasDataTable = false;
                wb.ActiveChart.Refresh();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
        }

        public static void DrawChart(Excel.Workbook wb, Excel.Worksheet ws, Excel.Range rge)
        {
            try
            {

                // The following code draws the chart
                ws.Activate();
                rge.Select();
                Excel.ChartObjects chartobjects = (Excel.ChartObjects)ws.ChartObjects(Missing.Value);

                Excel.ChartObject chartobject = (Excel.ChartObject)chartobjects.Add(double.Parse(rge.Left.ToString()) /*Left*/, double.Parse(rge.Top.ToString()) /*Top*/, 400 /*Width*/, 250 /*Height*/);

                Excel._Chart chart = (Excel._Chart)chartobject.Chart;

                chart.ChartWizard(rge, Excel.XlChartType.xlLine, Type.Missing, Excel.XlRowCol.xlRows,
                    1, 1, true, ws.Name + " Line-Chart", "Disconnection times", "HSMS Disconnect Interval", Type.Missing);

                Excel.ChartObjects chartobjects1 = (Excel.ChartObjects)ws.ChartObjects(Missing.Value);

                double cht2Left = chartobject.Left + chartobject.Width;
                Excel.ChartObject chartobject1 = (Excel.ChartObject)chartobjects1.Add(cht2Left /*Left*/, chartobject.Top/*Top*/, 400 /*Width*/, 250 /*Height*/);

                Excel._Chart chart1 = (Excel._Chart)chartobject1.Chart;

                chart1.ChartWizard(rge, Excel.XlChartType.xl3DColumn, Type.Missing, Excel.XlRowCol.xlRows,
                    1, 1, true, ws.Name + " Column-Chart", "Disconnection times", "HSMS Disconnect Interval", Type.Missing);
                // Call to chart.ChartWizard() is shown using late binding technique solely for the demonstration purposes
                //Object[] args7 = new Object[11];
                //args7[0] = rge; // Source
                //args7[1] = Excel.XlChartType.xlLine; // Gallery
                //args7[2] = Missing.Value; // Format
                //args7[3] = Excel.XlRowCol.xlRows; // PlotBy
                //args7[4] = 0; // CategoryLabels
                //args7[5] = 0; // SeriesLabels
                //args7[6] = true; // HasLegend
                //args7[7] = ws.Name; // Title
                //args7[8] = "Sample Category Type"; // CategoryTitle
                //args7[9] = "Sample Value Type"; // ValueTitle
                //args7[10] = Missing.Value; // ExtraTitle
                //chart.GetType().InvokeMember("ChartWizard", BindingFlags.InvokeMethod, null, chart, args7);
                //rge.Hidden = true;
                //chartobject.Locked = true;
                wb.Save();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }
        }

    }
}