I made a utility in windows form application that reads a text file and performed some business logic and then exported that data to the excel, for excel processing I have used Microsoft Excel XX.X Object Library. I have added following methods that are used to initialize the Excel library objects and then add data to it.
For creating the excel doc :
public void createDoc()
{
try
{
app = new Excel.Application();
//app.Visible = true;
workbook = app.Workbooks.Add(1);
worksheet = (Excel.Worksheet)workbook.Sheets[1];
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
finally
{
}
}
for adding data to excel :
public void addData(int row, int col, string data)
{
try
{
worksheet.Cells[row, col] = data;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
then I have added the following code to run in background :
void backgroundProcess(object sender, DoWorkEventArgs e)
{
SpecialCharacter specialCharObj = new SpecialCharacter();
//creating excel Doc
CreateExcelDoc excell_app = new CreateExcelDoc();
//creates the main header
excell_app.createHeaders();
string[] readText = File.ReadAllLines(txtAddFile.Text);
//MsgLabel.Text = "Exporting to excel......";
foreach (string s in readText)
{
specialCharObj.setAllFields(s);
excell_app.addDataToExcel(specialCharObj,count);
count++;
bgw.ReportProgress(count-2);
}
excell_app.writeDoc(txtAddFile.Text.ToString()+"_Output.xlsx");
}
the addDataToExcel calls the addData method of the excel class which adds multiple fields in the excel sheet as follows :
public void addDataToExcel(SpecialCharacter obj,int count)
{
this.addData(count, 1, "Debenhams");
this.addData(count, 2, obj.identifier);
this.addData(count, 3, obj.date);
this.addData(count, 4, obj.referenceNo);
this.addData(count, 5, obj.IdentificationCode);
this.addData(count, 6, obj.franchiseName);
this.addData(count, 7, obj.storeNum);
this.addData(count, 8, obj.InvoiceNum);
this.addData(count, 9, obj.divisionNum);
}
now the real issue i am facing is i have a form closing method that process the work at the closure of form in between the processing, the method is written as follows :
private void DebenhamsSpecialCharUtility_FormClosing(object sender, FormClosingEventArgs e)
{
if (MsgLabel.Text == "Exported")
{
Application.Exit();
}
else
{
if (e.CloseReason == CloseReason.UserClosing)
{
DialogResult result = MessageBox.Show("Do you really want to exit?", "Dialog Title", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
//excell_app.excelAppClose();
Application.Exit();
}
else
{
e.Cancel = true;
}
}
else
{
e.Cancel = true;
}
}
now what I am required to do is end my excel processing that is running in the background if the user close the form during processing , but if I do that it raise an exception as it is looping through the file and adding data to excel and so it cannot release the Excel object. I have use following code for deallocating excel objects :
workbook.Close(true, misValue, misValue);
app.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
So I need someone to provide me with the solution of ending the process without raising any exception as it is looping through the data and adding the data to excel.