how to append the data to excel sheet

Sep 4 2010 3:21 AM
hello i am creating the one excel sheet as per my requirements

1.create new .xls file if that is not exist
2.append the data to that xls file and save it

for that i am writing the method as follows it work fine with out any errors
but the problems is it can't display any appended text on the sheet it always shows the heading only what is the problem

 private static void createMyBook(string path)
        {
            Microsoft.Office.Interop.Excel.Application xl = null;
            Microsoft.Office.Interop.Excel._Workbook wb = null;
            Microsoft.Office.Interop.Excel._Worksheet sheet = null;

            //VBIDE.VBComponent module = null;
            bool SaveChanges = false;
            try
            {
                xl = new Microsoft.Office.Interop.Excel.Application();
                #region "create New File IF not exist"
                if (!File.Exists(path))
                {
                    GC.Collect();

                    // Create a new instance of  Microsoft.Office.Interop.Excel from scratch


                    xl.Visible = false;
                    wb = (Microsoft.Office.Interop.Excel._Workbook)(xl.Workbooks.Add());
                    wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);



                    // Get a reference to the one and only worksheet in our workbook

                    //sheet = ( Microsoft.Office.Interop.Excel._Worksheet)wb.ActiveSheet;
                    sheet = (Microsoft.Office.Interop.Excel._Worksheet)(wb.Sheets[1]);

                    // Fill spreadsheet with sample data
                    //sheet.Name = "Test";

                    //for (int r = 0; r < 20; r++)
                    //{

                    //    for (int c = 0; c < 10; c++)
                    //    {
                    //        sheet.Cells[r + 1, c + 1] = 125;
                    //    }
                    //}

                    // set come column heading names
                    sheet.Name = "logReport";
                    sheet.Cells[1, 1] = "FileName";
                    sheet.Cells[1, 2] = "Full Path";
                    sheet.Cells[1, 3] = "Action";
                    sheet.Cells[1, 4] = "File Size";
                    sheet.Cells[1, 5] = "Date Of Creation";
                    // sheet.Cells[1, 6] = "Time Of Creation";
                    sheet.Cells[1, 6] = "user";
                    sheet.Cells[1, 7] = "hash Code";
                    sheet.Cells[1, 8] = " Date at Action";
                    sheet.Cells[1, 9] = "Time at Action";

                    // Let loose control of the  Microsoft.Office.Interop.Excel instance


                    // Set a flag saying that all is well and it is ok to save our changes to a file.

                    SaveChanges = true;

                    //  Save the file to disk

                    wb.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                              null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
                              false, false, null, null, null);

                }
                #endregion
                //xl = new Microsoft.Office.Interop.Excel.Application();
                xl.Visible = false;
                wb = (Microsoft.Office.Interop.Excel._Workbook)(xl.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true));
                sheet = wb.Worksheets.get_Item(1);
                int nofRows = sheet.Rows.Count;
                sheet.Cells[2, 1] = "append";
                sheet.Cells[3, 2] = "append";
                sheet.Cells[3, 3] = "append";
                sheet.Cells[3, 6] = "append";
                sheet.Cells[3, 7] = "append";
                sheet.Cells[3, 8] = "append";
                sheet.Cells[3, 9] = "append";
                SaveChanges = true;

                //  Save the file to disk

                SaveChanges = true;
                wb.Save();

                //wb.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                //          null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
                //          false, false, null, null, null);

            }
            catch (Exception err)
            {
                String msg;
                msg = "Error: ";
                msg = String.Concat(msg, err.Message);
                msg = String.Concat(msg, " Line: ");
                msg = String.Concat(msg, err.Source);
                Console.WriteLine(msg);
            }
            finally
            {

                try
                {
                    // Repeat xl.Visible and xl.UserControl releases just to be sure
                    // we didn't error out ahead of time.

                    xl.Visible = false;
                    xl.UserControl = false;
                    // Close the document and avoid user prompts to save if our method failed.
                    wb.Close(SaveChanges, null, null);
                    xl.Workbooks.Close();
                }
                catch { }

                // Gracefully exit out and destroy all COM objects to avoid hanging instances
                // of  Microsoft.Office.Interop.Excel.exe whether our method failed or not.

                xl.Quit();

                //if (module != null) { Marshal.ReleaseComObject(module); }
                if (sheet != null) { Marshal.ReleaseComObject(sheet); }
                if (wb != null) { Marshal.ReleaseComObject(wb); }
                if (xl != null) { Marshal.ReleaseComObject(xl); }

                //module = null;
                sheet = null;
                wb = null;
                xl = null;
                GC.Collect();
            }
        }
        #endregion

Answers (2)