RF / weld · Files

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
//using Microsoft.Office.Interop.Excel;
//using Microsoft.Office.Interop;  
//using Microsoft.Office.Core;  

using MyClassLib;

namespace MyControlLib
{
    public partial class ExcelUpLoad : UserControl
    {
        public string tipText = "";
        public ExcelUpLoad()
        {
            InitializeComponent();
            textBox1.Text = CSqlHelper.GetConnSting();
            //toolStripStatusLabel3.Text = string.Format("【提示】请选择一张你导入的目标表格{0}}", tipText);
         //   GetTables();
        }
        public ExcelUpLoad(string tiptext)
        {
            InitializeComponent();
            textBox1.Text = CSqlHelper.GetConnSting();
            GetTables();
            tipText = tiptext;
            toolStripStatusLabel3.Text = string.Format("【提示】请选择此表格名称为{0}", tipText);
        }

     
        private System.Data.DataTable srcDt;

        private int TotalCount=0;
        private int ColumnCount=0;
        /// <summary>
        /// 获取Excel文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            if (textBox3.Text == "") { throw new ArgumentNullException(textBox3.Text); }
            openFileDialog1.Filter = "Excel文件(*.xls;*xlsx)|*.xls;*.xlsx";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                textBox2.Text = openFileDialog1.FileName;
                //ExcelUpload xls = new ExcelUpload();
                srcDt = GetExcelDataTable(openFileDialog1.FileName, textBox3.Text, "Yes");
                dataGridView1.DataSource = srcDt;
                this.TotalCount = srcDt.Rows.Count;
                toolStripStatusLabel1.Text = string.Format("总共数据条数【{0}】", TotalCount);
                toolStripProgressBar1.Maximum = TotalCount;
                toolStripProgressBar1.Step = 1;
                if (srcDt.Rows.Count > 0)
                {
                    int i = 0;
                    foreach (DataColumn dc in srcDt.Columns)
                    {
                        if (dc.ColumnName.Split('-')[1] != "*")
                        {
                            System.Windows.Forms.Label lbl = new System.Windows.Forms.Label();
                            lbl.Parent = flowLayoutPanel1;
                            lbl.Text = dc.ColumnName.Split('-')[0];
                            lbl.Name = string.Format("lableColName{0}", i);
                            ComboBox cbx = new ComboBox();
                            cbx.Parent = flowLayoutPanel1;
                            cbx.Name = string.Format("comboxColName{0}", i);
                            cbx.Text = dc.ColumnName.Split('-')[1];
                            i++;
                        }
                    }
                    this.ColumnCount = i;
                }
            }
        }

        void cbx_Click(object sender, EventArgs e)
        {
            //throw new NotImplementedException();

        }

        private void GetTables()
        {
            if (textBox1.Text == "") { throw new ArgumentNullException("textBox1.Text"); };
            DataSet ds= CSqlHelper.ExecuteDataset(textBox1.Text, CommandType.Text, "select name from sysobjects where xtype='u' ");
             System.Data.DataTable dt = ds.Tables[0];
            if (dt.Rows.Count > 0)
            {
                comboBox1.Items.Clear();
                foreach (DataRow dr in dt.Rows)
                {
                    comboBox1.Items.Add(dr[0].ToString());
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            GetTables();
        }

      
        private void GetColumn(string TableName)
        {
            if (TableName == "") { throw new ArgumentNullException("TableName"); }
            if (textBox2.Text == "") { throw new ArgumentNullException(textBox2.Text); }
            try
            {
                System.Data.DataTable dt = CSqlHelper.ExecuteDataset(string.Format("select top(1) *  from {0}", TableName)).Tables[0];
                foreach (Control cb in flowLayoutPanel1.Controls)
                {
                    if (cb is ComboBox)
                    {
                        ComboBox c = (ComboBox)cb;
                        c.Items.Clear();
                        foreach (DataColumn dc in dt.Columns)
                        {
                            c.Items.Add(dc.ColumnName);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogExecute.WriteExceptionLog("getcolumn", ex);
            }
        }
        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            foreach (Control ct in flowLayoutPanel1.Controls)
            {
                if (ct is ComboBox)
                {
                    if (ct.Text == "")
                    {
                        throw new ArgumentNullException(ct.Name);
                    }
                }
            }
            if (radioButton1.Checked == true&&radioButton2.Checked==false)
            {
                int count = InsertToDataBase();
                if (count  == TotalCount)
                {
                    MessageBox.Show(string.Format("数据插入成功!"));
                }
                else
                {
                    MessageBox.Show(string.Format("数据插入失败{0}!",TotalCount-count ));
                }
            }
            else if (radioButton1.Checked == false && radioButton2.Checked == true)
            { 
                
            }
            // 确定插入字符串
        }

        private string T_PreTable;
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

            if (textBox2.Text == "") { return; }
            if (T_PreTable != comboBox1.Text && comboBox1.Text != "")
            {
                GetColumn(comboBox1.Text);
                T_PreTable = comboBox1.Text;
            }
        }

        private int InsertToDataBase()
        {
            int count = 0;
            try
            {
                if (srcDt == null) { throw new ArgumentNullException(srcDt.ToString()); }
                
                foreach( DataRow dr in srcDt.Rows)
                {
                    int Total =0;
                    if (ColumnCount > 0)
                    {
                        Total = ColumnCount;
                    }
                    else 
                    {
                        Total = srcDt.Columns.Count;
                    }
                    int j = srcDt.Columns.Count-Total;
                    string InsertSql1 = string.Format("Insert into {0} (", comboBox1.Text);
                    string InsertSql2 = " ) Values ( ";
                    for (int i = 0; i < Total; i++)
                    {

                        string strName = string.Format("comboxColName{0}", i);
                        ComboBox cb = flowLayoutPanel1.Controls.Find(strName, false).ToArray()[0] as ComboBox;
                        InsertSql1 += cb.Text + ",";
                        InsertSql2 += "'" + dr[i+j].ToString() + "',";
                    }
                    InsertSql1 = InsertSql1.TrimEnd(',');
                    InsertSql2 = InsertSql2.TrimEnd(',')+" )";
                    string strSql = InsertSql1 + InsertSql2;
                    if (CSqlHelper.ExecuteNonQuery(textBox1.Text,CommandType.Text,strSql) == 1) 
                    { 
                        count++;
                        toolStripStatusLabel2.Text= string.Format("插入成功条数【{0}】", count);
                        toolStripProgressBar1.PerformStep();//按照Step的值进行递增！
                    }
                }
                
            }
            catch (Exception ex)
            {
                throw new ArgumentNullException(ex.Message);
            }
            return count;
        }

        private int UpdateToDataBase()
        {
            int count = 0;
            try
            {
                if (srcDt == null) { throw new ArgumentNullException(srcDt.ToString()); }

                foreach (DataRow dr in srcDt.Rows)
                {
                    int Total = srcDt.Columns.Count;
                    string UpdateSql = string.Format("update {0} set ", comboBox1.Text);
                    for (int i = 0; i < Total; i++)
                    {
                        string strName = string.Format("comboxColName{0}", i);
                        ComboBox cb = flowLayoutPanel1.Controls.Find(strName, false).ToArray()[0] as ComboBox;
                        UpdateSql += string.Format("{0}='{1}',", cb.Text, dr[i].ToString());
                    }
                    UpdateSql = UpdateSql.TrimEnd(',');
                    string strSql = UpdateSql;
                    if (CSqlHelper.ExecuteNonQuery(textBox1.Text,CommandType.Text,strSql) == 1)
                    {
                        count++;
                        toolStripStatusLabel2.Text = string.Format("插入成功条数【{0}】", count);
                        toolStripProgressBar1.PerformStep();//按照Step的值进行递增！
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ArgumentNullException(ex.Message);
            }
            return count;
        }


        #region ExcelToSQL server 2008R2
        /// <summary>
        /// 获取Excel文件中的数据到DataTable中 
        /// </summary>
        /// <param name="fileUrl">文件路径名称包括文件名</param>
        /// <param name="table">填充表格名称</param>
        /// <param name="YesOrNo">首行是标题则为Yes，否则为No</param>
        /// <returns></returns>
        public static DataTable GetExcelDataTable( string table, string YesOrNo)
        {
            OpenFileDialog openDialog = new OpenFileDialog();
            string fileUrl = "";
            if (openDialog.ShowDialog() == DialogResult.OK)
            {
                fileUrl = openDialog.FileName;
            }
            else
            {
                return null;
            }
            return GetExcelDataTable(fileUrl, table, YesOrNo);
        }

        /// <summary>
        /// 获取Excel文件中的数据到DataTable中 
        /// </summary>
        /// <param name="fileUrl">文件路径名称包括文件名</param>
        /// <param name="table">填充表格名称</param>
        /// <param name="YesOrNo">首行是标题则为Yes，否则为No</param>
        /// <returns></returns>
        public static DataTable GetExcelDataTable(string fileUrl, string table, string YesOrNo)
        {
            //office2007之前 仅支持.xls
            //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；
            //const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            if (fileUrl == "") { return null; }
            const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR={1}; IMEX=1'";
            DataTable dt = null;
            //建立连接
            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl, YesOrNo));
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }


                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                //获取Excel的第一个Sheet名称
                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();

                //查询sheet中的数据
                string strSql = "select * from [" + sheetName + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, table);
                dt = ds.Tables[0];

                return dt;
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

        }
        
        /// <summary>
        /// Dataset 导出变成Excel文件
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public  static bool ExportDatasetToExcel(DataSet ds,string SheetName)
        {
            if (ds == null) { throw new ArgumentNullException(); }
            string saveFilename = "";
            bool fileSaved = false ;
            SaveFileDialog savedialog = new SaveFileDialog();
            savedialog.DefaultExt = "xlsx";
            savedialog.Filter = "Excel 03以下 文件|*.xls|Excel 07以上文件|*.xlsx";
            if (SheetName == "") { SheetName = "Sheet1"; }
            savedialog.FileName = SheetName;
            if (savedialog.ShowDialog() == DialogResult.OK)
            {
                saveFilename=savedialog.FileName;
                return ExportDatasetToExcel(saveFilename, ds, SheetName);
            }
            return fileSaved;
        }
        /// <summary>
        /// 导出DataSet到Excel中
        /// </summary>
        /// <param name="FileUrl">Excel 文件路径名称（包括路径和文件名称)</param>
        /// <param name="ds">需要导出的DataSet</param>
        /// <param name="SheetName">导入Excel文件的第一个表格名称</param>
        /// <returns></returns>
        public static bool ExportDatasetToExcel(string FileUrl, DataSet ds, string SheetName)
        {
            bool fileSaved = false;
            if (FileUrl == "") { throw new ArgumentNullException(); }
            if (ds == null) { throw new ArgumentNullException(); }
            #region create
            string saveFilename = FileUrl;
            Microsoft.Office.Interop.Excel.Application xlapp = new Microsoft.Office.Interop.Excel.Application();
            if (xlapp == null)
            {
                MessageBox.Show("无法创建excel对象，可能您的机子未安装excel");
                return false;
            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlapp.Workbooks;
            Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            Microsoft.Office.Interop.Excel.Range rang;
            #endregion

            #region WriteData
            long totalcount = ds.Tables[0].Columns.Count;
            long rowread = 0;
            float percent = 0;
            //worksheet.Cells[1, 1] = "test";
            //写入字段
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                worksheet.Cells[2, i + 1] = ds.Tables[0].Columns[i].ColumnName;
                rang = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
                rang.Interior.ColorIndex = 15;
                rang.Font.Bold = true;
            }

            //写入数据
            for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
            {
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    worksheet.Cells[r + 3, i + 1] = ds.Tables[0].Rows[r][i];
                }
                rowread++;
                percent = ((float)(100 * rowread)) / totalcount;
                System.Windows.Forms.Application.DoEvents();
            }
            #endregion

            #region Style
            rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[ds.Tables[0].Rows.Count + 2, ds.Tables[0].Columns.Count]);
            rang.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
            rang.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
            rang.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            rang.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            if (ds.Tables[0].Columns.Count > 1)
            {
                rang.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
                rang.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                rang.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

            }
            #endregion

            #region Save && Exit
            if (saveFilename != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFilename);
                    fileSaved = true;
                    MessageBox.Show("文件已经成功导出...");
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    MessageBox.Show("导出文件时出错，文件可能正被打开!/n", ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }
            xlapp.Quit();
            GC.Collect();//强行销毁
            #endregion
            return fileSaved;
        }
        #endregion
    }
}