OK, i will paste my entire code-----
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using MySql.Data.MySqlClient;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
namespace WorkingWithSQL_EXCEL
{
class Program
{
static string var1;
static void Main(string[] args)
{
try
{
if (Directory.Exists("C:\\Mica_Report"))
Console.WriteLine("C:\\Mica_Report already exists");
else
Directory.CreateDirectory("C:\\Mica_Report");
Console.WriteLine("1. Create Mica_Report if directory if it does not exist");
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
try
{
string MyConString = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
MySqlConnection sqlConn = new MySqlConnection(MyConString);
MySqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "DROP TABLE IF EXISTS `jmds`.`hesk_combined`";
sqlConn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
cmd.ExecuteNonQuery();
}
sqlConn.Close();
reader.Close();
Console.WriteLine("1. Checking if table exist, if it does. Then delete it");
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
try
{
string MyConString = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
MySqlConnection sqlConn = new MySqlConnection(MyConString);
MySqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "CREATE TABLE jmds.hesk_combined LIKE jmds.hesk_tickets";
sqlConn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
cmd.ExecuteNonQuery();
}
sqlConn.Close();
reader.Close();
Console.WriteLine("2. Create hesk_combined table (Still empty)");
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
try
{
string MyConString = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
MySqlConnection sqlConn = new MySqlConnection(MyConString);
MySqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = "INSERT INTO jmds.hesk_combined SELECT * FROM jmds.hesk_tickets";
sqlConn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
cmd.ExecuteNonQuery();
}
sqlConn.Close();
reader.Close();
Console.WriteLine("3. Insert hesk_tickets data into new table");
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
try
{
string MyConString2 = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
MySqlConnection sqlConn = new MySqlConnection(MyConString2);
MySqlCommand cmd2 = sqlConn.CreateCommand();
cmd2.CommandText = "UPDATE hesk_combined JOIN hesk_replies ON hesk_combined.id = hesk_replies.id SET hesk_combined.message = hesk_replies.message, hesk_combined.id =hesk_replies.id, hesk_combined.name =hesk_replies.name ;";
sqlConn.Open();
MySqlDataReader reader2 = cmd2.ExecuteReader();
while (reader2.Read())
{
cmd2.BeginExecuteReader();
}
sqlConn.Close();
reader2.Close();
Console.WriteLine("4. Update hesk_combined table with hesk_replies table data");
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
try
{
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
string MyConString3 = "Server=south-srv01;Database=jmds;Uid=root;Pwd=F1sh1ng;";
MySqlConnection sqlConn3 = new MySqlConnection(MyConString3);
MySqlCommand command = new MySqlCommand("select * from jmds.hesk_combined where email = @email", sqlConn3);
MySqlParameter param = new MySqlParameter();
param.ParameterName = "@email";
param.Value = (var1);
command.Parameters.Add(param);
sqlConn3.Open();
MySqlDataAdapter adptr = new MySqlDataAdapter();
adptr.SelectCommand = command;
adptr.Fill(dt);
ds.Tables.Add(dt);
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
Excel.Range oRange;
oXL = new Excel.Application();
oXL.DisplayAlerts = false;
oWB = oXL.Workbooks.Add(Missing.Value);
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
var1 = (string)dr["DBEmail"];
oSheet.Name = var1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
if (rowCount == 2)
{
oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
}
}
oRange = oSheet.get_Range(oSheet.Cells[1, 1],
oSheet.Cells[rowCount, dt.Columns.Count]);
oRange.EntireColumn.AutoFit();
oRange.WrapText = true;
oXL.Cells.Replace("<br />", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
oXL.Cells.Replace("", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
oXL.Cells.Replace(" oXL.Cells.Replace("> ", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
oWB.SaveCopyAs(@"C:\Mica_Report\new.xls");
// Save the sheet and close
oSheet = null;
oRange = null;
//oWB.SaveAs("NareenReport.xls", Excel.XlFileFormat.xlWorkbookNormal,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Excel.XlSaveAsAccessMode.xlExclusive,
// Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Missing.Value);
//oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();
// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
catch (Exception e)
{
Console.WriteLine("Error " + e);
}
Console.WriteLine("Reports done in Process");
}
}
}
Submit an article or tip