using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using System.Data.SqlClient;

namespace ExcelWorkbook1
{
    public partial class Sheet1
    {
        private void Sheet1_Startup(object sender, System.EventArgs e)
        {
        }

        private void Sheet1_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.button1.Click += new System.EventHandler(this.button1_Click);
            this.Shutdown += new System.EventHandler(this.Sheet1_Shutdown);
            this.Startup += new System.EventHandler(this.Sheet1_Startup);

        }

        #endregion

        private void button1_Click(object sender, EventArgs e)
        {
            //import();
            export();
        }

        #region 数据库导入到xsl

        void import()
        {
            Excel.Application appXSL = new Microsoft.Office.Interop.Excel.Application();
            if (null == appXSL)
            {
                MessageBox.Show("Can Not Open Excel!");
                return;
            }
            SqlConnection sqlcon = new SqlConnection("server=.;uid=sa;pwd=;database=Article;");
            try
            {
                appXSL.Application.Workbooks.Add(true);
                sqlcon.Open();
                string sql = @"select * from articledetails";
                SqlCommand cmd = new SqlCommand(sql, sqlcon);
                SqlDataReader sdr = cmd.ExecuteReader();
                int rowCount = sdr.FieldCount;
                for (int i = 0; i < rowCount; i++)
                {
                    appXSL.Cells[1, i + 1] = sdr.GetName(i);
                }
                int currentRowNumber = 2;
                while (sdr.Read())
                {
                    for (int i = 0; i < rowCount; i++)
                    {
                        appXSL.Cells[currentRowNumber, i+1] = sdr.GetValue(i).ToString();
                    }
                    currentRowNumber++;
                }
                appXSL.Visible = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                if (sqlcon.State == ConnectionState.Open)
                {
                    sqlcon.Close();
                }
                appXSL.Quit();
            }
        }
        #endregion

        #region xsl导入到数据库
        void export()
        {
            Excel.Application appXSL = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                Excel.Workbook workbook = appXSL.Workbooks.Open(@"d:\2.xls", System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
                Excel.Worksheet sheet = appXSL.Sheets[1] as Excel.Worksheet;
                System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
                int rowCount = 5;
                int colCount = 8;
               
                string sqlTemp = @" insert into articledetails ( ";
                for (int i = 2; i <= colCount; i++)
                {
                    sqlTemp = sqlTemp + ((Excel.Range)sheet.Cells[1, i]).Text.ToString() + ",";
                }
                sqlTemp = sqlTemp.Substring(0, sqlTemp.Length - 1) + ") values (";

                for (int i = 2; i <= rowCount; i++)
                {
                    sbSql.Append(sqlTemp);
                    for (int j = 2; j < colCount; j++)
                    {
                        sbSql.Append("'");
                        sbSql.Append(((Excel.Range)sheet.Cells[i, j]).Text.ToString());
                        sbSql.Append("',");
                    }
                    sbSql.Append("'");
                    sbSql.Append(((Excel.Range)sheet.Cells[i, colCount]).Text.ToString() + "')");
                }
                openCon();
                SqlCommand cmd = new SqlCommand(sbSql.ToString(), sqlcon);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                MessageBox.Show("Export OK!");
                closeCon();
                appXSL.Quit();
                appXSL = null;
            }
        }
        #endregion

        #region 数据库
        SqlConnection sqlcon = new SqlConnection("server=.;uid=sa;pwd=;database=Article;");
        void openCon()
        {
            if (sqlcon.State == ConnectionState.Closed)
            {
                sqlcon.Open();
            }
        }
        void closeCon()
        {
            if (sqlcon.State == ConnectionState.Open)
            {
                sqlcon.Close();
            }
        }
        #endregion
    }
}