public static void GenerateExcel()
{
string conString = @"Server=DESKTOP-QVPUIDU\SQLEXPRESS; Database=Erasmus_Students;Integrated Security = SSPI;";
StringBuilder query = new StringBuilder();
query.Append("select * from ErasmusStudent");
SQL.DataTable dtProducts = new SQL.DataTable();
using (SqlConnection cn = new SqlConnection(conString))
{
using (SqlDataAdapter da = new SqlDataAdapter(query.ToString(), cn))
{
da.Fill(dtProducts);
}
}
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Application oXL;
oXL = new Application();
oWB = oXL.Workbooks.Add(Missing.Value);
oSheet = (_Worksheet)oWB.ActiveSheet;
oSheet = (_Worksheet)oXL.Worksheets.Add();
oSheet.Name = "Erasmus";
try
{
SQL.DataTable dtCategories = dtProducts.DefaultView.ToTable(true, "FirstName");
foreach (SQL.DataRow category in dtCategories.Rows)
{
string[] colNames = new string[dtProducts.Columns.Count];
int col = 0;
foreach (SQL.DataColumn dc in dtProducts.Columns)
colNames[col++] = dc.ColumnName;
string lastColumn = "A";
if (dtProducts.Columns.Count > 25)
lastColumn += (char)(65 + dtProducts.Columns.Count - 1);
else
lastColumn = Convert.ToString((char)(65 + dtProducts.Columns.Count - 1));
oSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
oSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
oSheet.get_Range("A1", lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
SQL.DataRow[] dr = dtProducts.Select(string.Format("FirstName='{0}'", category[0].ToString()));
string[,] rowData = new string[dr.Count<SQL.DataRow>(), dtProducts.Columns.Count];
int rowCnt = 0;
int redRows = 2;
foreach (SQL.DataRow row in dr)
{
for (col = 0; col < dtProducts.Columns.Count; col++)
{
rowData[rowCnt, col] = row[col].ToString();
}
/*if (int.Parse(row["ReorderLevel"].ToString()) < int.Parse(row["UnitsOnOrder"].ToString()))
{
Range range = oSheet.get_Range("A" + redRows.ToString(), "J" + redRows.ToString());
range.Cells.Interior.Color = System.Drawing.Color.Red;
} */
redRows++;
rowCnt++;
}
oSheet.get_Range("A2", lastColumn + rowCnt.ToString()).Value2 = rowData;
}
oXL.DisplayAlerts = false;
oWB.SaveAs("Erasmus.xlsx");
oWB.Close(0);
oXL.Quit();
SendEmail();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
Marshal.ReleaseComObject(oXL);
}
}
So the problem is that every time i run the build one process of excel doesn't close i finished my day with 30-40 open processes and now i realized this. I tried to close the file with quit and close and also i tried to release it with Marshal i'm out of ideas at the moment. Any suggestion/code improvement is welcome.
