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");
}
}
