//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);
}
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);
}
}