vendredi 2 février 2018

Writing a few missing listbox items to an existing Excel File using c#

Picture 1 Picture2 if above is broken in my project one listbox inside country names one button and excel files . Excel is nearly half filled with country names. I want to send listBox some items to excel comparing which excel file doesnt have ıtems from listbox to Excel with for loops. Here is my code. Code runs wrongly I need help. Sorry for my weak English.

private void button11_Click(object sender, EventArgs e)
    {

        Microsoft.Office.Interop.Excel.Workbook mWorkBook;
        Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
        Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
        Microsoft.Office.Interop.Excel.Application oXL;

        oXL = new Microsoft.Office.Interop.Excel.Application();
        oXL.Visible = true;
        oXL.DisplayAlerts = false;


        string path = @"C:\myexcel.xls";
        if (!File.Exists(path))
        {
            mWorkBook = oXL.Workbooks.Add();
            File.Create(path).Close();               }
        else
        {
            mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
                false, 0, true, false, false);
        }
        mWorkSheets = mWorkBook.Worksheets;

         mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.Item[1];  

        Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;

        int colCount = range.Columns.Count;
        int rowCount = range.Rows.Count;



        if (listBox2.Items.Count < listBox1.Items.Count)

        {


            int n = 0;
            int k = 0;

                for (int i = 0; i< listBox1.Items.Count; i++)
                {

                      for (int j = 0; j <=rowCount; j++)
                      {

                        if (!( mWSheet1.Cells[j + 1, 1]== listBox1.Items[i]))
                       {
                         n = n + 1;
                        }
                        else
                        {
                        k = k + 1;

                        }

                        if (n>0)
                        {
                        int ax = rowCount + i + 1;
                        mWSheet1.Cells[ax, 1] = listBox1.Items[i + n];
                        mWSheet1.Cells[ax , 2] = i + n;
                        n = 0;

                        }
                }

            }

        }



        mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing, Missing, Missing, Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
        Missing, Missing, Missing,
        Missing, Missing);

        mWorkBook.Close(Missing, Missing, Missing);
        mWSheet1 = null;

        mWorkBook = null;



        oXL.Quit();
        GC.WaitForPendingFinalizers();
        GC.Collect();
        GC.WaitForPendingFinalizers();
        GC.Collect();


    }

Aucun commentaire:

Enregistrer un commentaire