creating Excel SpreadSheet using Streams

Question

hi to all how to create a excel spread sheet using streams and how to write and read data to and from the spread sheet plz help me thank you

Lalit M · Answer

use this code sample using System; using System.Collections.Generic; using System.Text; using System.IO; using System.Runtime.InteropServices; using System.Reflection; namespace CreateExcelSheets { class Program { [STAThread] static void Main(string[] args) { //string MyFile = Path.GetFullPath(".") + @"\sample.xls"; string MyFile = @"c:\sample.xls"; CreateWorkbook(MyFile); Console.WriteLine("File Saved to " + MyFile); Console.ReadLine(); } #region Create Workbook private static void CreateWorkbook(string FileName) { Excel.Application xl = null; Excel._Workbook wb = null; Excel._Worksheet sheet = null; //VBIDE.VBComponent module = null; bool SaveChanges = false; try { if (File.Exists(FileName)) { File.Delete(FileName); } GC.Collect(); // Create a new instance of Excel from scratch xl = new Excel.Application(); xl.Visible = false; // Add one workbook to the instance of Excel wb = (Excel._Workbook)(xl.Workbooks.Add(Missing.Value)); wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); wb.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value); // Get a reference to the one and only worksheet in our workbook //sheet = (Excel._Worksheet)wb.ActiveSheet; sheet = (Excel._Worksheet)(wb.Sheets[1]); // Fill spreadsheet with sample data //sheet.Name = "Test"; for (int r = 0; r { for (int c = 0; c { sheet.Cells[r + 1, c + 1] = 125; } } // set come column heading names sheet.Name = "Jan"; sheet.Cells[1, 1] = "Heading"; sheet = (Excel._Worksheet)(wb.Sheets[2]); sheet.Name = "Feb"; sheet.Cells[1, 1] = "Heading"; sheet = (Excel._Worksheet)(wb.Sheets[3]); sheet.Name = "Mar"; sheet.Cells[1, 1] = "Heading"; sheet = (Excel._Worksheet)(wb.Sheets[4]); sheet.Name = "Apr"; sheet.Cells[1, 1] = "Heading"; // Let loose control of the Excel instance xl.Visible = false; xl.UserControl = false; // 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(FileName, Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, 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 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 } } show here another example http://www.c-sharpcorner.com/UploadFile/ggaganesh/CreateExcelSheet12012005015333AM/CreateExcelSheet.aspx http://www.sharpprogrammer.com/dotnet/how-to-read-excel-file-in-c-net/ http://www.woodwardweb.com/dotnet/generating_exce.html

srinivas · Answer

i executed your code by keeping using Excel = Microsoft.Office.Interop.Excel; but iam getting the above error plz help me

srinivas · Answer

thank you for your help but iam getting the following error and in my system Microsoft Excel is not installed i have Open Office . Is it necessary to install excel The type or namespace name 'Excel' could not be found (are you missing a using directive or an assembly reference?) plz help me thank you

creating Excel SpreadSheet using Streams

Answers (3)