Example #1
0
        //20140606 by Jack
        public static bool ExportMultiSheetToExcelFile(List <DataGridView> dbgResultAll, ref string sExportFileName, bool IsShowOK,
                                                       bool IsShowDialog, bool IsDeleteFile, bool IsCopyFromTemplate, List <string> WorkSheetName)
        {
            SaveFileDialog dlgSaveFile    = new SaveFileDialog();
            string         sTempExcelFile = Application.StartupPath + "\\ReportTemplate.xl_";
            string         sql            = "";
            string         connstr        = "";

            System.Data.OleDb.OleDbConnection conn = null;
            System.Data.OleDb.OleDbCommand    cmd  = null;

            dlgSaveFile.InitialDirectory = Application.StartupPath;
            dlgSaveFile.DefaultExt       = ".xls";
            dlgSaveFile.Filter           = "Excel File (*.xls) | *.xls";
            dlgSaveFile.FilterIndex      = 0;
            if (!IsShowDialog || dlgSaveFile.ShowDialog() == DialogResult.OK)
            {
                if (IsShowDialog)
                {
                    sExportFileName = dlgSaveFile.FileName;
                }

                try
                {
                    if (IsDeleteFile && File.Exists(sExportFileName))
                    {
                        File.Delete(sExportFileName);
                    } //if

                    if (IsCopyFromTemplate)
                    {
                        File.Copy(sTempExcelFile, sExportFileName, true);
                    }
                    connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExportFileName +
                              ";Extended Properties=\"Excel 8.0;HDR=Yes\";";
                    conn = new System.Data.OleDb.OleDbConnection(connstr);
                    cmd  = new System.Data.OleDb.OleDbCommand();

                    conn.Open();
                    cmd.Connection  = conn;
                    cmd.CommandType = CommandType.Text;

                    //sql = "Drop Table [Report$];";
                    //cmd.CommandText = sql;
                    //cmd.ExecuteNonQuery();

                    foreach (DataGridView dbgResult in dbgResultAll)
                    {
                        sql = "";
                        foreach (DataGridViewColumn grid_col in dbgResult.Columns)
                        {
                            if (!grid_col.Visible)
                            {
                                continue;
                            }
                            sql += (sql.Length > 0 ? ", " : "Create Table [" + WorkSheetName[dbgResultAll.IndexOf(dbgResult)] + "] ( ") +
                                   "[" + grid_col.HeaderText.Replace('.', '#') + "]";
                            switch (grid_col.ValueType.Name)
                            {
                            case "Int":
                            case "Int32":
                                sql += " Integer";
                                break;

                            case "Decimal":
                            case "Single":
                                sql += " Decimal";
                                break;

                            case "DateTime":
                                sql += " DateTime";
                                break;

                            default:
                                sql += " Text";
                                break;
                            } // switch
                        }     // foreach
                        sql            += " );";
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();

                        foreach (DataGridViewRow grid_row in dbgResult.Rows)
                        {
                            DataRowView rowView = grid_row.DataBoundItem as DataRowView;
                            if (rowView == null)
                            {
                                continue;
                            }

                            DataRow row = rowView.Row;
                            string  sql_col_List = "", sql_value_List = "";
                            foreach (DataGridViewColumn grid_col in dbgResult.Columns)
                            {
                                if (!grid_col.Visible)
                                {
                                    continue;
                                }
                                sql_col_List += (sql_col_List.Length > 0 ? "," : "") +
                                                "[" + grid_col.HeaderText.Replace('.', '#') + "]";
                                switch (grid_col.ValueType.Name)
                                {
                                case "Int":
                                case "Int32":
                                    sql_value_List += (sql_value_List.Length > 0 ? "," : "") +
                                                      ParamStr(CommonUnit.ToInt(row[grid_col.DataPropertyName]).ToString());
                                    break;

                                case "Decimal":
                                case "Single":
                                    sql_value_List += (sql_value_List.Length > 0 ? "," : "") +
                                                      ParamStr(CommonUnit.ToDecimal(row[grid_col.DataPropertyName]).ToString());
                                    break;

                                case "DateTime":
                                    if (row[grid_col.DataPropertyName].Equals(DBNull.Value))
                                    {
                                        sql_value_List += (sql_value_List.Length > 0 ? "," : "") + " Null ";
                                    }
                                    else
                                    {
                                        sql_value_List += (sql_value_List.Length > 0 ? "," : "") + "'" +
                                                          ((DateTime)row[grid_col.DataPropertyName]).ToString("yyyy/MM/dd HH:mm:ss") + "'";
                                    }
                                    break;

                                default:
                                    sql_value_List += (sql_value_List.Length > 0 ? "," : "") + "'" +
                                                      ParamStr(row[grid_col.DataPropertyName].ToString()) + "'";
                                    break;
                                } // switch
                            }     // foreach
                            sql = "Insert Into [" + WorkSheetName[dbgResultAll.IndexOf(dbgResult)] + "] (" + sql_col_List + ") Values(" +
                                  sql_value_List + ")";
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        } // foreach
                    }     // foreach

                    conn.Close();

                    if (IsShowOK)
                    {
                        MessageBox.Show("資料已匯出完成!", "完成", MessageBoxButtons.OK,
                                        MessageBoxIcon.Information);
                    }
                    return(true);
                } // try
                catch (Exception E)
                {
                    if (IsShowDialog)
                    {
                        MessageBox.Show(E.Message);
                    }

                    if (conn != null && conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }

                    if (!IsShowDialog)
                    {
                        throw;
                    }
                } // catch
            }     // if
            return(false);
        }
Example #2
0
        private void btnExportExcel_Click(object sender, EventArgs e)
        {
            //DTCompare
            DataGridView dgv_export = new DataGridView();
            DataTable    dtTemp     = dSCompare.DTCompare.CopyToDataTable();

            dgv_export.DataSource = dtTemp;
            this.Controls.Add(dgv_export);
            dgv_export.Name    = "dgv_export";
            dgv_export.Dock    = DockStyle.Fill;
            dgv_export.Visible = false;
            //DTCompareDetail
            DataGridView dgv_exportDetail = new DataGridView();
            DataTable    dtTempDetail     = dSCompare.DTCompareDetail.CopyToDataTable();

            dgv_exportDetail.DataSource = dtTempDetail;
            this.Controls.Add(dgv_exportDetail);
            dgv_exportDetail.Name    = "dgv_exportDetail";
            dgv_exportDetail.Dock    = DockStyle.Fill;
            dgv_exportDetail.Visible = false;
            //DTECS
            DataGridView dgv_exportECS = new DataGridView();
            DataTable    dtTempECS     = dSCompare.DTECS.CopyToDataTable();

            dgv_exportECS.DataSource = dtTempECS;
            this.Controls.Add(dgv_exportECS);
            dgv_exportECS.Name    = "dgv_exportECS";
            dgv_exportECS.Dock    = DockStyle.Fill;
            dgv_exportECS.Visible = false;
            //DTECSDetail
            DataGridView dgv_exportECSDetail = new DataGridView();
            DataTable    dtTempECSDetail     = dSCompare.DTECSDetail.CopyToDataTable();

            dgv_exportECSDetail.DataSource = dtTempECSDetail;
            this.Controls.Add(dgv_exportECSDetail);
            dgv_exportECSDetail.Name    = "dgv_exportECSDetail";
            dgv_exportECSDetail.Dock    = DockStyle.Fill;
            dgv_exportECSDetail.Visible = false;
            //DTESB
            DataGridView dgv_exportESB = new DataGridView();
            DataTable    dtTempESB     = dSCompare.DTESB.CopyToDataTable();

            dgv_exportESB.DataSource = dtTempESB;
            this.Controls.Add(dgv_exportESB);
            dgv_exportESB.Name    = "dgv_exportESB";
            dgv_exportESB.Dock    = DockStyle.Fill;
            dgv_exportESB.Visible = false;
            //DTESBDetail
            DataGridView dgv_exportESBDetail = new DataGridView();
            DataTable    dtTempESBDetail     = dSCompare.DTESBDetail.CopyToDataTable();

            dgv_exportESBDetail.DataSource = dtTempESBDetail;
            this.Controls.Add(dgv_exportESBDetail);
            dgv_exportESBDetail.Name    = "dgv_exportESBDetail";
            dgv_exportESBDetail.Dock    = DockStyle.Fill;
            dgv_exportESBDetail.Visible = false;

            List <DataGridView> lstDGV = new List <DataGridView>();

            lstDGV.Add(dgv_export);
            lstDGV.Add(dgv_exportDetail);
            lstDGV.Add(dgv_exportECS);
            lstDGV.Add(dgv_exportECSDetail);
            lstDGV.Add(dgv_exportESB);
            lstDGV.Add(dgv_exportESBDetail);
            List <string> lstSheetName = new List <string>();

            lstSheetName.Add("DTCompare");
            lstSheetName.Add("DTCompareDetail");
            lstSheetName.Add("DTECS");
            lstSheetName.Add("DTECSDetail");
            lstSheetName.Add("DTESB");
            lstSheetName.Add("DTESBDetail");
            string FileName = string.Format(@"ESBDataVaild_{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssffff"));
            string PathN    = Path.Combine(ESBSetting.SaveOutPutPath, FileName);

            if (CommonUnit.ExportMultiSheetToExcelFile(lstDGV, ref PathN, false, false, true, true, lstSheetName))
            {
                MessageBox.Show("匯出Excel成功!");
            }
            else
            {
                MessageBox.Show("匯出Excel失敗!");
            }

            foreach (DataGridView dgv in lstDGV)
            {
                this.Controls.Remove(dgv);
            }
        }