using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Spire.Xls;
using System.Data.OleDb;

namespace SALESSUPPORT_2062
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Workbook workbook = new Workbook();
            //Initailize worksheet
            Worksheet sheet = workbook.Worksheets[0];
            DataTable dt = ConnectDataBase();
            //export datatable to excel
            sheet.InsertDataTable(dt, true, 1, 1);
            //Add a new  chart worsheet to workbook
            Chart chart = sheet.Charts.Add();

            chart.ChartType = ExcelChartType.Line;

            //Set region of chart data
            chart.DataRange = sheet.Range["A1" + ":E" + dt.Rows.Count];

            //Set position of chart
            chart.LeftColumn = dt.Columns.Count + 1;
            chart.TopRow = 6;
            chart.RightColumn = 20;
            chart.BottomRow = 29;

            //Chart title
            chart.ChartTitle = "Sales market by country";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;

            chart.PrimaryCategoryAxis.Title = "Month";
            chart.PrimaryCategoryAxis.Font.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;

            chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
            chart.PrimaryValueAxis.MinValue = 1000;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;

            foreach (Spire.Xls.Charts.ChartSerie cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            chart.PlotArea.Fill.Visible = false;

            chart.Legend.Position = LegendPositionType.Top;
            workbook.SaveToFile("Sample.xls");
            DocViewer(workbook.FileName);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Spire.DataExport.PDF.PDFExport PDFExport = new Spire.DataExport.PDF.PDFExport();
            PDFExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
            PDFExport.DataTable = ConnectDataBase();
            PDFExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
            PDFExport.SaveToFile("Sample.pdf");
            DocViewer(PDFExport.FileName);

        }
        private DataTable ConnectDataBase()
        {
            //connect database
            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = @"Provider=""Microsoft.ACE.OLEDB.12.0"";Data Source=""..\..\Database.accdb"";User Id=;Password=";
            OleDbCommand command = new OleDbCommand();
            command.CommandText = "select * from Chart";
            DataSet dataSet = new System.Data.DataSet();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText, connection);
            dataAdapter.Fill(dataSet);
            DataTable t = dataSet.Tables[0];
            return t;
        }
        private void DocViewer(string fileName)
        {
            try
            {
                System.Diagnostics.Process.Start(fileName);
            }
            catch { }
        }
    }
}
