Andre Davis
Reputation: 59
How To Close Excel and Workbook (C#)?
My goal is to read an excel file, make some modifications, and then save the workbook. I have the code working for generating the file, but it seems that the file then gets locked for editing/read only mode after the second iteration when I'm using the existing file. Please take a look. Thanks! The error is prompted from where I am trying to delete the initial file after saving it to a temporary file.
private void button9_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application oXL;
_Workbook oWB;
_Worksheet oSheet;
_Worksheet TrackingSheet;
try
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
if (track == false)
{
//oXL.Visible = true;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
//Unique ID Tab
TrackingSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
TrackingSheet.Name = "UniqueID Tracking List";
for (int i = 0; i < listBox1.Items.Count; i++)
{
TrackingSheet.Cells[i+1, 1] = listBox1.Items[i];
UIDList.add(listBox1.Items[i]);
}
TrackingSheet = oWB.Worksheets.Add();
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
oSheet.Name = "SlackTrend";
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "Status Date";
oSheet.Cells[2, 1] = "Unique ID";
oSheet.Cells[2, 2] = "Task Name";
oSheet.Cells[2, 3] = "% Complete";
oSheet.Cells[2, 4] = "Finish Date";
oSheet.Cells[2, 5] = "Baseline Finish";
//Creates the monthly bins (User entry for range) - goes from current month onward if creating
int monthS = comboBox2.SelectedIndex + 1;
int yearS = comboBox3.SelectedIndex + 2000;
Date status = CurrentFile.ProjectProperties.StatusDate;
int yearstatus = status.getYear();
int monthstatus = status.getMonth();
int daystatus = status.getDate();
System.String statusDate = (monthstatus + 1) + "/" + daystatus + "/" + (yearstatus + 1900);
System.String statuskeyCode = (monthstatus + 1) + "-" + (yearstatus + 1900);
System.String monthYear = monthS + "-" + yearS;
int slackLocation = 6; //Double Check this logic
for (int i = 0; i < 15; i++)
{
oSheet.Cells[2, 6 + i] = monthYear;
monthS++;
if (monthS > 12)
{
monthS = 1;
yearS++;
}
if (monthYear.Equals(statuskeyCode))
{
oSheet.Cells[1, 6 + i] = statusDate;
slackLocation = 6 + i;
}
monthYear = monthS + "-" + yearS;
}
for (int i = 0; i < UIDList.size(); i++)
{
net.sf.mpxj.Task c = CurrentFile.GetTaskByUniqueID(java.lang.Integer.valueOf((Int32.Parse(UIDList.get(i).ToString()))));
Duration d = c.Duration;
System.String name = c.Name;
//Date Conversion
Date d1 = c.Finish;
Number percent = c.PercentageComplete;
Date Baseline = c.BaselineFinish;
int year = d1.getYear();
int month = d1.getMonth();
int day = d1.getDate();
int yearb = Baseline.getYear();
int monthb = Baseline.getMonth();
int dayb = Baseline.getDate();
double slack = c.TotalSlack.Duration;
System.String newDate = month + 1 + "/" + day + "/" + (year + 1900);
System.String newBaselineDate = month + 1 + "/" + day + "/" + (year + 1900);
oSheet.Cells[i + 3, 1] = UIDList.get(i);
oSheet.Cells[i + 3, 2] = name;
oSheet.Cells[i + 3, 3] = percent;
oSheet.Cells[i + 3, 4] = newDate;
oSheet.Cells[i + 3, 5] = newBaselineDate;
oSheet.Cells[i + 3, slackLocation] = slack;
oSheet.Cells[1, slackLocation] = statusDate;
}
//Expands columns
Microsoft.Office.Interop.Excel.Range aRange = oSheet.get_Range("A1", "Z1");
aRange.EntireColumn.AutoFit();
oWB.SaveAs(name4);
System.Windows.Forms.Application.Exit();
//oWB.Close(true, name4, null);
//oXL.Quit();
//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}
else
{
//else statement for existing tracking file
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
xlWorkBook = oXL.Workbooks.Open(name3,0,false);
//Selects active sheet
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheetFocus = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
xlWorkSheetFocus.Activate();
//Collecting UIDs to trend (Assuming no added/deleted)
ArrayList UIDList = new ArrayList();
/*
for (int i = 1; i < xlWorkSheetFocus.UsedRange.Rows.Count + 1; i++)
{
UIDList.add(xlWorkSheetFocus.Cells[i, 1].Value.ToString());
}
*/
for (int i = 0; i < listBox1.Items.Count; i++)
{
xlWorkSheetFocus.Cells[i + 1, 1] = listBox1.Items[i];
UIDList.add(listBox1.Items[i]);
}
//Trending new data points for existing UIDs
xlWorkSheetFocus = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheetFocus.Activate();
//user-inputted status month bin
int monthS = comboBox2.SelectedIndex+1;
int yearS = comboBox3.SelectedIndex+2000;
Date status = CurrentFile.ProjectProperties.StatusDate;
int yearstatus = status.getYear();
int monthstatus = status.getMonth();
int daystatus = status.getDate();
System.String statusDate = (monthstatus + 1) + "/" + daystatus + "/" + (yearstatus + 1900);
System.String statuskeyCode = (monthstatus + 1) + "-" + (yearstatus + 1900);
System.String monthYear = monthS + "/1/" + yearS + " 12:00:00 AM";
//Create List for Tracking File Comparison
ArrayList existingList = new ArrayList();
//Making List to check if UID already exists
for(int i = 3; i < xlWorkSheetFocus.UsedRange.Rows.Count+1; i++)
{
existingList.add(xlWorkSheetFocus.Cells[i, 1].Value.ToString());
}
// loop through UID List
for (int j = 0; j < UIDList.size(); j++)
{
if (existingList.contains(UIDList.get(j).ToString()) == false)
{
System.String UID = (System.String)UIDList.get(j);
net.sf.mpxj.Task c = CurrentFile.GetTaskByUniqueID(java.lang.Integer.valueOf(UID));
Duration d = c.Duration;
System.String name = c.Name;
//Date Conversion
Date d1 = c.Finish;
Number percent = c.PercentageComplete;
Date Baseline = c.BaselineFinish;
int year = d1.getYear();
int month = d1.getMonth();
int day = d1.getDate();
int yearb = Baseline.getYear();
int monthb = Baseline.getMonth();
int dayb = Baseline.getDate();
double slack = c.TotalSlack.Duration;
System.String newDate = month + 1 + "/" + day + "/" + (year + 1900);
System.String newBaselineDate = month + 1 + "/" + day + "/" + (year + 1900);
//updating existing data from latest IMS
xlWorkSheetFocus.Cells[xlWorkSheetFocus.UsedRange.Rows.Count + 1, 1] = UID;
xlWorkSheetFocus.Cells[xlWorkSheetFocus.UsedRange.Rows.Count, 2] = name;
xlWorkSheetFocus.Cells[xlWorkSheetFocus.UsedRange.Rows.Count, 3] = percent;
xlWorkSheetFocus.Cells[xlWorkSheetFocus.UsedRange.Rows.Count, 4] = newDate;
xlWorkSheetFocus.Cells[xlWorkSheetFocus.UsedRange.Rows.Count, 5] = newBaselineDate;
for (int z = 0; z < xlWorkSheetFocus.UsedRange.Columns.Count - 6; z++)
{
if (monthYear.Equals(xlWorkSheetFocus.Cells[2, 6 + z].Value.ToString()))
{
xlWorkSheetFocus.Cells[xlWorkSheetFocus.UsedRange.Rows.Count, 6 + z] = slack;
xlWorkSheetFocus.Cells[1, 6 + z] = statusDate;
}
}
}
//loop through trend sheet list
else
{
for (int k = 3; k < xlWorkSheetFocus.UsedRange.Rows.Count + 1; k++)
{
//check to see if list UIDs match
if (UIDList.get(j).ToString().Equals(xlWorkSheetFocus.Cells[k, 1].Value.ToString()))
{
// Collect latest task data -> CORRECT THIS COMPARE
System.String UID = (System.String)UIDList.get(j);
net.sf.mpxj.Task c = CurrentFile.GetTaskByUniqueID(java.lang.Integer.valueOf(UID));
Duration d = c.Duration;
System.String name = c.Name;
//Date Conversion
Date d1 = c.Finish;
Number percent = c.PercentageComplete;
Date Baseline = c.BaselineFinish;
int year = d1.getYear();
int month = d1.getMonth();
int day = d1.getDate();
int yearb = Baseline.getYear();
int monthb = Baseline.getMonth();
int dayb = Baseline.getDate();
double slack = c.TotalSlack.Duration;
System.String newDate = month + 1 + "/" + day + "/" + (year + 1900);
System.String newBaselineDate = month + 1 + "/" + day + "/" + (year + 1900);
//updating existing data from latest IMS
xlWorkSheetFocus.Cells[k, 2] = name;
xlWorkSheetFocus.Cells[k, 3] = percent;
xlWorkSheetFocus.Cells[k, 4] = newDate;
xlWorkSheetFocus.Cells[k, 5] = newBaselineDate;
for (int z = 0; z < xlWorkSheetFocus.UsedRange.Columns.Count - 6; z++)
{
if (monthYear.Equals(xlWorkSheetFocus.Cells[2, 6 + z].Value.ToString()))
{
xlWorkSheetFocus.Cells[k, 6 + z] = slack;
xlWorkSheetFocus.Cells[1, 6 + z] = statusDate;
}
}
}
}
}
}
Microsoft.Office.Interop.Excel.Range aRange = xlWorkSheetFocus.get_Range("A1", "Z1");
aRange.EntireColumn.AutoFit();
System.Windows.Forms.Application.Exit();
xlWorkBook.SaveAs("C:\\Users\\acdavis1\\Desktop\\temp.xlsx");
//xlWorkBook.SaveAs(name3);
//File.Delete("C:\\Users\\acdavis1\\Desktop\\temp.xlsx");
//xlWorkBook.Save(); // cant open read only
xlWorkBook.Close(0);
oXL.Quit();
Marshal.ReleaseComObject(oXL);
File.Delete(name3);
oXL.Visible = true;
oXL.UserControl = true;
}
}
catch (System.Exception theException)
{
System.String errorMessage;
errorMessage = "Error: ";
errorMessage = System.String.Concat(errorMessage, theException.Message);
errorMessage = System.String.Concat(errorMessage, " Line: ");
errorMessage = System.String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
Upvotes: 0
Views: 655
Answers (1)
Ivan García Topete
Reputation: 829
You should NEVER use two dots (call a method or a property of a property of an object) in COM objects (like in oXL.Workbooks.Add) AND you should properly release ALL the COM objects created (you only release oXL) even if something goes wrong (try adding a finally clause to your try - catch releasing all the objects)
(Note: I'm not on my computer at the time, try it and comment if needed):
using Excel = Microsoft.Office.Interop.Excel;
private void button9_Click(object sender, EventArgs e)
{
Excel.Application oXL = null;
Excel.Workbooks oWBs = null;
Excel.Workbook oWB = null;
Excel.Worksheets oSheets = null;
Excel.Worksheet oSheet = null;
Excel.Worksheet TrackingSheet = null;
Excel.Range aRange = null;
Excel.Range oSheetCells = null;
Excel.Range oSheetColumns = null;
try
{
//Start Excel and get Application object.
oXL = new Microsoft.Office.Interop.Excel.Application();
if (track == false)
{
//oXL.Visible = true;
//Get a new workbook.
oWBs = oXL.Workbooks;
oWB = (Excel.Workbook)oWBs.Add(Missing.Value);
//Unique ID Tab
TrackingSheet = (Excel.Worksheet)oWB.ActiveSheet;
TrackingSheet.Name = "UniqueID Tracking List";
for (int i = 0; i < listBox1.Items.Count; i++)
{
TrackingSheet.Cells[i+1, 1] = listBox1.Items[i];
UIDList.add(listBox1.Items[i]);
}
TrackingSheet = (Excel.Worksheet)oWBs.Add();
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oSheet.Name = "SlackTrend";
//Add table headers going cell by cell.
oSheet.Cells[1, 1] = "Status Date";
oSheet.Cells[2, 1] = "Unique ID";
oSheet.Cells[2, 2] = "Task Name";
oSheet.Cells[2, 3] = "% Complete";
oSheet.Cells[2, 4] = "Finish Date";
oSheet.Cells[2, 5] = "Baseline Finish";
//Creates the monthly bins (User entry for range) - goes from current month onward if creating
int monthS = comboBox2.SelectedIndex + 1;
int yearS = comboBox3.SelectedIndex + 2000;
Date status = CurrentFile.ProjectProperties.StatusDate;
int yearstatus = status.getYear();
int monthstatus = status.getMonth();
int daystatus = status.getDate();
System.String statusDate = (monthstatus + 1) + "/" + daystatus + "/" + (yearstatus + 1900);
System.String statuskeyCode = (monthstatus + 1) + "-" + (yearstatus + 1900);
System.String monthYear = monthS + "-" + yearS;
int slackLocation = 6; //Double Check this logic
for (int i = 0; i < 15; i++)
{
oSheet.Cells[2, 6 + i] = monthYear;
monthS++;
if (monthS > 12)
{
monthS = 1;
yearS++;
}
if (monthYear.Equals(statuskeyCode))
{
oSheet.Cells[1, 6 + i] = statusDate;
slackLocation = 6 + i;
}
monthYear = monthS + "-" + yearS;
}
for (int i = 0; i < UIDList.size(); i++)
{
net.sf.mpxj.Task c = CurrentFile.GetTaskByUniqueID(java.lang.Integer.valueOf((Int32.Parse(UIDList.get(i).ToString()))));
Duration d = c.Duration;
System.String name = c.Name;
//Date Conversion
Date d1 = c.Finish;
Number percent = c.PercentageComplete;
Date Baseline = c.BaselineFinish;
int year = d1.getYear();
int month = d1.getMonth();
int day = d1.getDate();
int yearb = Baseline.getYear();
int monthb = Baseline.getMonth();
int dayb = Baseline.getDate();
double slack = c.TotalSlack.Duration;
System.String newDate = month + 1 + "/" + day + "/" + (year + 1900);
System.String newBaselineDate = month + 1 + "/" + day + "/" + (year + 1900);
oSheet.Cells[i + 3, 1] = UIDList.get(i);
oSheet.Cells[i + 3, 2] = name;
oSheet.Cells[i + 3, 3] = percent;
oSheet.Cells[i + 3, 4] = newDate;
oSheet.Cells[i + 3, 5] = newBaselineDate;
oSheet.Cells[i + 3, slackLocation] = slack;
oSheet.Cells[1, slackLocation] = statusDate;
}
//Expands columns
aRange = oSheet.get_Range("A1", "Z1");
aRange = aRange.EntireColumn;
aRange.AutoFit();
oWB.SaveAs(name4);
//System.Windows.Forms.Application.Exit();
//oWB.Close(true, name4, null);
//oXL.Quit();
//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}
else
{
//else statement for existing tracking file
oWBs = oXL.Workbooks;
oWB = (Excel.Workbook)oWBs.Open(name3,0,false);
//Selects active sheet
oSheets = oWB.Worksheets;
oSheet = (Excel.Worksheet)oSheets.get_Item(2);
oSheet.Activate();
aRange = oSheet.UsedRange;
aRange = aRange.Rows;
oSheetCells = oSheet.Cells;
//Collecting UIDs to trend (Assuming no added/deleted)
ArrayList UIDList = new ArrayList();
/*
for (int i = 1; i < aRange.Count + 1; i++)
{
object value = oSheetCells[i, 1].Value;
UIDList.add(value.ToString());
}
*/
for (int i = 0; i < listBox1.Items.Count; i++)
{
oSheetCells[i + 1, 1] = listBox1.Items[i];
UIDList.add(listBox1.Items[i]);
}
//Trending new data points for existing UIDs
oSheet = (Excel.Worksheet)oSheets.get_Item(1);
oSheet.Activate();
//user-inputted status month bin
int monthS = comboBox2.SelectedIndex+1;
int yearS = comboBox3.SelectedIndex+2000;
Date status = CurrentFile.ProjectProperties.StatusDate;
int yearstatus = status.getYear();
int monthstatus = status.getMonth();
int daystatus = status.getDate();
System.String statusDate = (monthstatus + 1) + "/" + daystatus + "/" + (yearstatus + 1900);
System.String statuskeyCode = (monthstatus + 1) + "-" + (yearstatus + 1900);
System.String monthYear = monthS + "/1/" + yearS + " 12:00:00 AM";
//Create List for Tracking File Comparison
ArrayList existingList = new ArrayList();
//Making List to check if UID already exists
for(int i = 3; i < aRange.Count+1; i++)
{
object value = oSheetCells[i, 1].Value;
existingList.add(value.ToString());
}
// loop through UID List
for (int j = 0; j < UIDList.size(); j++)
{
if (existingList.contains(UIDList.get(j).ToString()) == false)
{
System.String UID = (System.String)UIDList.get(j);
net.sf.mpxj.Task c = CurrentFile.GetTaskByUniqueID(java.lang.Integer.valueOf(UID));
Duration d = c.Duration;
System.String name = c.Name;
//Date Conversion
Date d1 = c.Finish;
Number percent = c.PercentageComplete;
Date Baseline = c.BaselineFinish;
int year = d1.getYear();
int month = d1.getMonth();
int day = d1.getDate();
int yearb = Baseline.getYear();
int monthb = Baseline.getMonth();
int dayb = Baseline.getDate();
double slack = c.TotalSlack.Duration;
System.String newDate = month + 1 + "/" + day + "/" + (year + 1900);
System.String newBaselineDate = month + 1 + "/" + day + "/" + (year + 1900);
//updating existing data from latest IMS
oSheetCells[aRange.Count + 1, 1] = UID;
oSheetCells[aRange.Count, 2] = name;
oSheetCells[aRange.Count, 3] = percent;
oSheetCells[aRange.Count, 4] = newDate;
oSheetCells[aRange.Count, 5] = newBaselineDate;
oSheetColumns = oSheet.UsedRange;
oSheetColumns = oSheetColumns.Columns;
for (int z = 0; z < oSheetColumns.Count - 6; z++)
{
object value = oSheetCells[2, 6 + z].Value;
if (monthYear.Equals(value.ToString()))
{
oSheetCells[aRange.Count, 6 + z] = slack;
oSheetCells[1, 6 + z] = statusDate;
}
}
}
//loop through trend sheet list
else
{
for (int k = 3; k < aRange.Count + 1; k++)
{
//check to see if list UIDs match
object value = oSheetCells[k, 1].Value;
if (UIDList.get(j).ToString().Equals(value.ToString()))
{
// Collect latest task data -> CORRECT THIS COMPARE
System.String UID = (System.String)UIDList.get(j);
net.sf.mpxj.Task c = CurrentFile.GetTaskByUniqueID(java.lang.Integer.valueOf(UID));
Duration d = c.Duration;
System.String name = c.Name;
//Date Conversion
Date d1 = c.Finish;
Number percent = c.PercentageComplete;
Date Baseline = c.BaselineFinish;
int year = d1.getYear();
int month = d1.getMonth();
int day = d1.getDate();
int yearb = Baseline.getYear();
int monthb = Baseline.getMonth();
int dayb = Baseline.getDate();
double slack = c.TotalSlack.Duration;
System.String newDate = month + 1 + "/" + day + "/" + (year + 1900);
System.String newBaselineDate = month + 1 + "/" + day + "/" + (year + 1900);
//updating existing data from latest IMS
oSheetCells[k, 2] = name;
oSheetCells[k, 3] = percent;
oSheetCells[k, 4] = newDate;
oSheetCells[k, 5] = newBaselineDate;
oSheetColumns = oSheet.UsedRange;
oSheetColumns = oSheetColumns.Columns;
for (int z = 0; z < oSheetColumns.Count - 6; z++)
{
object value = oSheetCells[2, 6 + z].Value;
if (monthYear.Equals(value.ToString()))
{
oSheetCells[k, 6 + z] = slack;
oSheetCells[1, 6 + z] = statusDate;
}
}
}
}
}
}
aRange = oSheet.get_Range("A1", "Z1");
aRange = aRange.EntireColumn;
aRange.AutoFit();
//System.Windows.Forms.Application.Exit();
oWB.SaveAs("C:\\Users\\acdavis1\\Desktop\\temp.xlsx");
//xlWorkBook.SaveAs(name3);
//File.Delete("C:\\Users\\acdavis1\\Desktop\\temp.xlsx");
//xlWorkBook.Save(); // cant open read only
oWB.Close(0);
File.Delete(name3);
oXL.Visible = true;
oXL.UserControl = true;
}
}
catch (System.Exception theException)
{
System.String errorMessage;
errorMessage = "Error: ";
errorMessage = System.String.Concat(errorMessage, theException.Message);
errorMessage = System.String.Concat(errorMessage, " Line: ");
errorMessage = System.String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
finally
{
if (oXL != null) oXL.Quit();
/* release the newest first */
ReleaseCOM(oSheetColumns);
ReleaseCOM(oSheetCells);
ReleaseCOM(aRange);
ReleaseCOM(TrackingSheet);
ReleaseCOM(oSheet);
ReleaseCOM(oSheets);
ReleaseCOM(oWB);
ReleaseCOM(oWBs);
ReleaseCOM(oXL);
System.Windows.Forms.Application.Exit();
}
}
private void ReleaseCOM(object com)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(com);
com = null;
}
catch
{
com = null;
}
finally
{
GC.Collect();
}
}
Upvotes: 2
Related Questions
- Closing an Excel Workbook
- How to close excel in C#?
- (C#) How to close excel workbook
- Close excel workbook without killing the excel process or other workbooks - C#
- Closing Microsoft Excel sheets in visual studio c#
- Cannot close excel from my code
- How can I completely close an Excel application/workbook?
- C#: How can I open and close an Excel workbook?
- How I can close a excel windows automatic?
- Closing workbook in Excel programmatically