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