class Program
{
public static bool DebugMode { get; set; }
public static string TargetFolder { get; set; }
public static DateTime StartDate { get; set; }
public static DateTime EndDate { get; set; }
public static string DebugFolder { get; set; }
static void Main(string[] args)
{
Program p = new Program();
DebugMode = Convert.ToBoolean(ConfigurationManager.AppSettings["DebugMode"]);
TargetFolder = Convert.ToString(ConfigurationManager.AppSettings["TargetFolder"]);
DebugFolder = Convert.ToString(ConfigurationManager.AppSettings["DebugFolder"]);
StartDate = Convert.ToDateTime(ConfigurationManager.AppSettings["StartDate"]);
EndDate = Convert.ToDateTime(ConfigurationManager.AppSettings["EndDate"]);
if (DebugMode)
{
var GeminiConnString = ConfigurationManager.ConnectionStrings["GeminiConnString"].ConnectionString;
SqlConnection conn = null;
SqlCommand command = null;
DataTable dt = new DataTable();
DataSet ds = new DataSet();
using (conn = new SqlConnection(GeminiConnString))
{
using (command = new SqlCommand("dbo.GetGeminiRecordByDate", conn))
{
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@startDate", StartDate);
command.Parameters.AddWithValue("@endDate", EndDate);
command.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(ds);
dt = ds.Tables[0];
foreach (DataRow dRow in dt.Rows)
{
Console.WriteLine();
Console.Write(dRow["issueid"].ToString());
Console.Write('\t');
Console.Write(dRow["typedesc"].ToString());
}
}
}
p.ExportToExcel(ds, TargetFolder);
}
}
private void ExportToExcel(DataSet ds, string folderPath)
{
try
{
Excel.Application excelApp = new Excel.Application();
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add();
foreach (DataTable dt in ds.Tables)
{
Excel.Worksheet workSheet = excelApp.Sheets.Add();
for (int i = 0; i < dt.Columns.Count; i++)
{
workSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName;
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i].ItemArray[j].ToString();
}
}
}
excelWorkBook.SaveCopyAs(folderPath);
excelWorkBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
excelApp.Quit();
Console.Write("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
}