Click here to Skip to main content
15,844,013 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
HI, I have this code to insert into and excel sheet :

try
{
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    TextReader readClientNumber = new System.IO.StringReader(txtRandClientNumbers.Text);
    TextReader readCardNumber = new System.IO.StringReader(txtRandCardNumbers.Text);
    TextReader readClientCurrency = new System.IO.StringReader(txtClientCurrency.Text);

    Random rand = new Random();
    int num = rand.Next(0, 99999);
    int rows = 0;
    if (this.TestCases > 0)
    {
        rows = this.TestCases;
    }
    else if (this.TestCases <= 0)
    {
        rows = txtRandCardNumbers.Lines.Count() - 1;
    }



    string[] textClientNumber = new string[0];

    string[] textCardNumber = new string[0];
    string[] textClientCurrency = new string[0];
    if (this.TestCases > 0)
    {
        textCardNumber = new string[rows];
        textClientCurrency = new string[rows];
        textClientNumber = new string[rows];
    }
    else
    {
        int rowsofCards = txtRandCardNumbers.Lines.Count();
        textCardNumber = new string[rows];
        int rowsOfClient = txtRandClientNumbers.Lines.Count();
        textClientNumber = new string[rowsOfClient];
        textClientCurrency = new string[rowsOfClient];
    }

    string[] textRetreivalRef = new string[rows];
    string[] textOrigPayment = new string[rows];



    xlApp = new Excel.Application();

    xlWorkBook = xlApp.Workbooks.Open("C:\\temp\\" + this.Excelfilename + ".xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

    Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
    Excel.Range range = xlWorkSheet.get_Range("A1", last);

    int lastUsedRow = last.Row;
    int lastUsedColumn = last.Column;

    var firstLetterCharacters = this.Excelfilename.TakeWhile(Char.IsLetter);
    string filename = new string(firstLetterCharacters.ToArray());
    xlWorkSheet.Cells.NumberFormat = "@";




if (this.Institution_number == "00000002" && (this.Service_id == 102 || this.Service_id == 201 || this.Service_id == 406 || this.Service_id == 408))
{
    try
    {
            for (int row = 1; row == 1; row++)
        {
                cardTypeInsert = row + 1;
                origpaymentrefInsert = row + 2;
                retreivalrefInsert = row + 3;
                cdIndicatorInsert = row + 4;
                institutionNumberInsert = row + 5;
                refundInsert = row + 6;
                cardNumberInsert = row + 7;
                captureMethodInsert = row + 8;
                eciIndicatorInsert = row + 9;
                clientNumberInsert = row + 10;
                processoridInsert = row + 11;
                currencyInsert = row + 12;
                amountInsert = row + 13;

                for (int col = 0; col < rows; col++)
            {
                lastUsedRow = lastUsedRow + 1;

                textClientNumber[col] = readClientNumber.ReadLine();
                textCardNumber[col] = readCardNumber.ReadLine();
                textClientCurrency[col] = readClientCurrency.ReadLine();




                if (this.Service_id == 406 && this.Service_contract == "Acq Balance Based")
                {
                     xlWorkSheet.Cells[lastUsedRow, processoridInsert] = "350";
                }
                else if (this.Service_id == 408 && this.Service_contract == "Acq Matching Based")
                {
                     xlWorkSheet.Cells[lastUsedRow, processoridInsert] = "360";
                }
                else if ((this.Service_id == 201 || this.Service_id == 102) && this.Service_contract == "Acq Matching Based")
                {
                        xlWorkSheet.Cells[lastUsedRow, processoridInsert] = "317";
                }
                else
                {
                  xlWorkSheet.Cells[lastUsedRow, processoridInsert] = this.processor_ref;
                }

                    xlWorkSheet.Cells[lastUsedRow, row] = DateTime.Now.ToString("yyyyMMdd");
                    xlWorkSheet.Cells[lastUsedRow, cardTypeInsert] = this.Card_type;
                    xlWorkSheet.Cells[lastUsedRow, origpaymentrefInsert] = " ";
                    xlWorkSheet.Cells[lastUsedRow, retreivalrefInsert] = " ";
                    xlWorkSheet.Cells[lastUsedRow, cdIndicatorInsert] = this.Cdindicator;
                    xlWorkSheet.Cells[lastUsedRow, institutionNumberInsert] = "";
                    xlWorkSheet.Cells[lastUsedRow, refundInsert] = " ";
                    string cardnumberstring = opClass.CardNumberToExport(textCardNumber[col].ToString());
                    xlWorkSheet.Cells[lastUsedRow, cardNumberInsert] = cardnumberstring;
                    xlWorkSheet.Cells[lastUsedRow, captureMethodInsert] = cmbCaptureMethod.SelectedValue.ToString();
                    xlWorkSheet.Cells[lastUsedRow, eciIndicatorInsert] = " ";
                    xlWorkSheet.Cells[lastUsedRow, clientNumberInsert] = "\'" + textClientNumber[col].ToString();
                    int randnum1 = rand.Next(1, 999999);
                    string randnumstring = opClass.AmountToExport(randnum1);

                    xlWorkSheet.Cells[lastUsedRow, amountInsert] = randnumstring;




                    if ( this.Service_id == 406 || this.Service_contract == "Acq Matching Based")
                    {
                        xlWorkSheet.Cells[lastUsedRow, currencyInsert] = cmbCurrency.SelectedValue.ToString();
                    }
                    else
                    {
                        xlWorkSheet.Cells[lastUsedRow, currencyInsert] = textClientCurrency[col].ToString();
                    }



            }
        }

        xlApp.DisplayAlerts = false;
        if (File.Exists("C:\\temp\\" + this.Excelfilename + ".xls"))
        {
            File.Delete(this.Excelfilename);
            xlWorkBook.SaveAs("C:\\temp\\" + this.Excelfilename + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        }

        xlWorkBook.Close();
        xlApp.Quit();

        Marshal.ReleaseComObject(xlWorkSheet);
        Marshal.ReleaseComObject(xlWorkBook);
        Marshal.ReleaseComObject(xlApp);

        MessageBox.Show("Excel file updated , you can find the file in C:\\temp\\" + this.Excelfilename + ".xls");
    }



    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }

}


What I have tried:

i do not know why marshal.release.... is not working, the workaround i am using is to kill processes with the following code:

foreach (Process clsProcess in Process.GetProcesses())
{
    if (clsProcess.ProcessName.Equals("EXCEL"))
    {
        clsProcess.Kill();
        break;
    }
}


but if you have an excel open that you do not want to close it will be closed , because i am killink all the processes with the name of excel.


Can somone tell me how i can kill a process when it is finished from it.
Posted
Updated 11-Sep-17 22:07pm

1 solution

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900