TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Koteswararao Mallisetti
NA
817
564.8k
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
Reply
Answers (
2
)
button.visible
how to set the columns size width and hieght of grideview at the run time.