?using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.Runtime.InteropServices;
//using Microsoft.Office.Tools.Excel.dll;


namespace Elysian
{
    class ListViewExport
    {
        //public static void ExpToExcel(ListView listviewname)
        //{
        //    if (listviewname.Items.Count == 0) return;
        //    Excel.Application excel = new Excel.Application();
        //    Excel.Workbooks workbooks = excel.Workbooks;
        //    Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
        //    Excel.Sheets worksheets = workbook.Worksheets;
        //    Excel.Worksheet sheet = (Excel.Worksheet)worksheets.get_Item(1);
        //    excel.Visible = true;

        //    Excel.Range range;
        //    excel.Cells.Select();
        //    excel.Cells.RowHeight = 30;
        //    excel.Cells.Font.Size = 10;
        //    excel.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

        //    int rowPos = 2;
        //    range = excel.get_Range(sheet.Cells[rowPos, 1], sheet.Cells[rowPos, 1]);
        //    range.Select();

        //    for (int i = 1; i <= listviewname.Columns.Count; i++)
        //    {
        //        range = excel.get_Range(sheet.Cells[rowPos, i], sheet.Cells[rowPos, i]);
        //        range.Borders.LineStyle = 1;
        //        range.Font.Name = "華文仿宋";
        //        range.Font.Size = 16;
        //        range.Font.Bold = true;
        //        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
        //        range.ColumnWidth = 18;
        //        sheet.Cells[rowPos, i] = listviewname.Columns[i - 1].Text.ToString();
        //    }
        //    rowPos++;

        //    foreach (ListViewItem item in listviewname.Items)
        //    {
        //        for (int i = 1; i <= listviewname.Columns.Count; ++i)
        //        {
        //            range = excel.get_Range(sheet.Cells[rowPos, i], sheet.Cells[rowPos, i]);
        //            range.Borders.LineStyle = 1;
        //            range.Font.Name = "華文仿宋";
        //            range.Font.Size = 12;
        //            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
        //            sheet.Cells[rowPos, i] = item.SubItems[i - 1].Text;
        //        }
        //        rowPos++;
        //    }

        //}


        public static int ExportToExcel(ListView listView, string destFileName, string tableName, bool overWrite)
        {
            //得到字段名
            string szFields = "";
            string szValues = "";
            for (int i = 0; i < listView.Columns.Count; i++)
            {
                szFields += "[" + listView.Columns[i].Text + "],";
            }
            szFields = szFields.TrimEnd(',');
            SqlConnection sqlconn = new SqlConnection();
            sqlconn.ConnectionString = ListViewExport.GetConnectionString(destFileName);
            
            SqlCommand sqlcomm = new SqlCommand();
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandType = CommandType.Text;
            try
            {
                sqlconn.Open();
            }
            catch(Exception ex)
            {
                MessageBox.Show("目標文件路徑錯誤",ex.ToString());
            }
            //創(chuàng)建數(shù)據(jù)庫表
            try
            {
                sqlcomm.CommandText = ListViewExport.GetCreateTableSql("[" + tableName + "]", szFields.Split(','));
                sqlcomm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //如果允許覆蓋則刪除已有數(shù)據(jù)
                if (overWrite)
                {
                    try
                    {
                        sqlcomm.CommandText = "DROP TABLE [" + tableName + "]";
                        sqlcomm.ExecuteNonQuery();
                        sqlcomm.CommandText = ListViewExport.GetCreateTableSql("[" + tableName + "]", szFields.Split(','));
                        sqlcomm.ExecuteNonQuery();
                    }
                    catch (Exception ex1)
                    {
                        throw ex1;
                    }
                }
                else
                {
                    throw ex;
                }
            }
            try
            {
                int recordCount = 0;
                for (int i = 0; i < listView.Items.Count; i++)
                {
                    szValues = "";
                    for (int j = 0; j < listView.Columns.Count; j++)
                    {
                        if (j >= listView.Items[i].SubItems.Count)
                        {
                            szValues += "'',";
                        }
                        else
                        {
                            szValues += "'" + listView.Items[i].SubItems[j].Text + "',";
                        }
                    }
                    szValues = szValues.TrimEnd(',');
                    //組合成SQL語句并執(zhí)行
                    string szSql = "INSERT INTO [" + tableName + "](" + szFields + ") VALUES(" + szValues + ")";
                    sqlcomm.CommandText = szSql;
                    recordCount += sqlcomm.ExecuteNonQuery();
                }
                sqlconn.Close();
                return recordCount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //得到連接字符串
        private static String GetConnectionString(string fullPath)
        {
            string szConnection;
            szConnection = "data source=.;initial catalog=ElysianDB;integrated security=true;Extended Properties=Excel 8.0" + fullPath;
            return szConnection;
        }
        private static string GetCreateTableSql(string tableName, string[] fields)
        {
            string szSql = "CREATE TABLE " + tableName + "(";
            for (int i = 0; i < fields.Length; i++)
            {
                szSql += fields[i] + " VARCHAR(200),";
            }
            szSql = szSql.TrimEnd(',') + ")";
            return szSql;
        }

    }

}