public string WriteCSVFile(string account, string perfix = "", string PathIFExist = "") { try { string filename = DateTime.Now.Day.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Year.ToString() + "-" + DateTime.Now.Hour.ToString() + "-" + DateTime.Now.Minute.ToString() + "-" + DateTime.Now.Second.ToString(); if (PathIFExist != "") { filename = PathIFExist; } if (Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + account) == false) { Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + account); } using (StreamWriter writer = new StreamWriter(AppDomain.CurrentDomain.BaseDirectory + account + "\\" + perfix + filename + ".csv", true)) { if (PathIFExist != "") { Rfc4180Writer.WriteDataTable(SearchResultTable, writer, false); } else { Rfc4180Writer.WriteDataTable(SearchResultTable, writer, true); } } return(AppDomain.CurrentDomain.BaseDirectory + account + "\\" + perfix + filename + ".csv"); } catch (Exception ex) { return(""); } }
public void PUSHToFile(string account, string path) { try { if (Directory.Exists(AppDomain.CurrentDomain.BaseDirectory + account) == false) { Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + account); } using (StreamWriter writer = new StreamWriter(path, true)) { Rfc4180Writer.WriteDataTable(SearchResultTable, writer, false); } } catch (Exception ex) { } }
/// <summary> /// Build the excel adp report and send the temporary stored path of the adp report /// </summary> /// <returns> adp report path in temporary folder </returns> private void buildCSVs() { try { var centerNo = Convert.ToInt32(ddSygmaCenterNo.SelectedValue); var weekend = Convert.ToDateTime(ddlWeekending.SelectedItem.ToString()); var ds = ATMDB.GetDataSet("up_p_sendToAdp", centerNo, weekend, UserName, AppSettings.GetAppSetting("adpmainreport", "ratetype", "required"), AppSettings.GetAppSetting("adpbonusreport", "ratetype", "required"), AppSettings.GetAppSetting("adpexpensereport", "ratetype", "required"), AppSettings.GetAppSetting("adpptoreport", "ratetype", "required")); var tableCount = ds.Tables.Count; for (var i = 0; i < tableCount; i++) { const int TABLE_INDEX_ADP_REPORT = 0; const int TABLE_INDEX_BONUS_REPORT_B = 1; const int TABLE_INDEX_BONUS_REPORT_C = 2; const int TABLE_INDEX_EXPENSE_REPORT = 3; string baseFileName; switch (i) { case TABLE_INDEX_ADP_REPORT: baseFileName = "ADP_Main"; break; case TABLE_INDEX_BONUS_REPORT_B: baseFileName = "ADP_Bonus_Tax_B"; break; case TABLE_INDEX_BONUS_REPORT_C: baseFileName = "ADP_Bonus_Tax_C"; break; case TABLE_INDEX_EXPENSE_REPORT: baseFileName = "ADP_Expense"; break; default: baseFileName = "ADP_Pto"; break; } var dataTable = ds.Tables[i]; var columnCount = dataTable.Columns.Count; var hasEarnings = false; //write header for (var c = 0; c < columnCount; c++) { if (dataTable.Columns[c].ColumnName.StartsWith("EarningCode")) { dataTable.Columns[c].Caption = "Earnings 3 Code"; hasEarnings = true; } else if (dataTable.Columns[c].ColumnName.StartsWith("EarningAmount")) { dataTable.Columns[c].Caption = "Earnings 3 Amount"; hasEarnings = true; } else if (dataTable.Columns[c].ColumnName.StartsWith("ExpenseCode")) { dataTable.Columns[c].Caption = "Adjust Ded Code"; hasEarnings = true; } else if (dataTable.Columns[c].ColumnName.StartsWith("ExpenseAmount")) { dataTable.Columns[c].Caption = "Adjust Ded Amount"; hasEarnings = true; } else if (dataTable.Columns[c].ColumnName.StartsWith("PayNumber")) { dataTable.Columns[c].Caption = "Pay #"; } else if (dataTable.Columns[c].ColumnName.StartsWith("TaxFrequency")) { dataTable.Columns[c].Caption = "Tax Frequency"; } else { dataTable.Columns[c].Caption = dataTable.Columns[c].ColumnName; } } //write data var fullFileName = getExcelFileFullName(baseFileName); using (var writer = new StreamWriter(fullFileName)) Rfc4180Writer.WriteDataTable(dataTable, writer, true); if (hasEarnings) { _email.Attachments.Add(new Attachment(fullFileName)); } } } catch (Exception exp) { throw new Exception("Error creating ADP Report Excel", exp); } }
private void BtnGetData_Click(object sender, RoutedEventArgs e) { try { lblErrorTxt.Content = ""; SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["SqlServer"].ConnectionString); var outCsvFile1 = @"C:\test\result1.csv"; var outCsvFile2 = @"C:\test\result2.csv"; var sqlCmd = new SqlDataAdapter("CustOrdersDetail", conn); sqlCmd.SelectCommand.CommandType = CommandType.StoredProcedure; SqlParameter orderIDParameter = sqlCmd.SelectCommand.Parameters.Add("@OrderID", SqlDbType.Int); orderIDParameter.Value = Convert.ToInt32(txtOrderID.Text); var ds1 = new DataSet(); var ds2 = new DataTable(); using (ds1) { sqlCmd.Fill(ds1); } using (ds2) { sqlCmd.Fill(ds2); } //created by me StringBuilder csvFileData = new StringBuilder(); if (ds1.Tables[0].Rows.Count > 0) { DataRow dr1 = ds1.Tables[0].Rows[0]; var headerCount = dr1.Table.Columns.Count; int index = 1; foreach (DataColumn dataColumn in dr1.Table.Columns) { csvFileData.Append(String.Format("\"{0}\"", dataColumn.ColumnName)); if (index < headerCount) { csvFileData.Append(","); } else { csvFileData.Append("\r\n"); } index++; } foreach (DataRow dataRow in dr1.Table.Rows) { string strRow = string.Empty; for (int x = 0; x <= headerCount - 1; x++) { strRow += "\"" + dataRow[x].ToString() + "\""; if (x < headerCount - 1 && x >= 0) { strRow += ","; } } csvFileData.Append(strRow + "\r\n"); } File.WriteAllText(outCsvFile1, csvFileData.ToString()); } //code found on internet static class use using (StreamWriter writer = new StreamWriter(outCsvFile2)) { Rfc4180Writer.WriteDataTable(ds2, writer, true); } conn.Close(); lblErrorTxt.Content = "Files have been sucessfully exported."; } catch (Exception ex) { lblErrorTxt.Content = ex.ToString(); } }