Urgent!!! Generate Excel workbook with 3 excel sheets in it

Question

Hi i want to export the data to genearte excel workbook which should contain 3 worksheets. I don't want to use Microsoft.Interop.Excel as i found few things not working while hosting. pls advice me any free dlls and code to get a workbook with 3 excel sheets in that. Thanks in advance Vijay

Prasant Jinaga · Answer

Hi Vijay Please find the attached project ..there you will get everything and make sure excel file will only open where excel is installed. Thanks Parsant

Vijay · Answer

Hi M getting errror while opening the excel :

Prasant Jinaga · Answer

Hi Vijay You dont need to add any kind of dll to the solution.Suppose you have the Dtaset which contains some datatables call the method ExportToExcel having the parameter of your Dataset and file path name.and make sure you add the Constants class which contains the xml tags to read from there.then when you run the project it will save the excel file in multiple sheets of the data having in datasets in the Path where you pass as a parameter. Note:The excel sheet which created will only open in that machine where excel is installed. Thanks Prasant

Vijay · Answer

Hi Prasant Thanks a lot for ur interest in the post I would like to know how to use it... Thanks in advance Vijay

Prasant Jinaga · Answer

Hi Vijay I also faced this situation then i exported to excel using xml tags Here is the Code to export to excel public static void ExportToExcel(DataSet dsInput string ExcelFileName) { System.IO.StreamWriter ExportToExcelDoc; ExportToExcelDoc = new System.IO.StreamWriter(ExcelFileName); //const string startExcelXML =  
 // xmlns=\urn:schemas-microsoft-com:office:spreadsheet\r
 + //  xmlns:o=\urn:schemas-microsoft-com:office:office\r
  + // xmlns:x=\urn:schemas- microsoft-com:office: + // excel\r
 xmlns:ss=\urn:schemas-microsoft-com: + // office:spreadsheet\>
 
  + //  
  + //  
  + // 
 
 
  + // 
 
 
  + //  
 // x:Family=\Swiss\ ss:Bold=\1/>
 
  + //  
 //  ss:Format=\@/>
 
 // ss:ID=\Decimal\>
 // ss:Format=\0.0000/>
 
  + //  
 // ss:Format=\0/>
 
 // ss:ID=\DateLiteral\>
 // ss:Format=\mm/dd/yyyy;@/>
 
  + //  
 ; //Footer Part of the Excel is declared here // const string endExcelXML =  ; int rowCount = 0; int sheetCount = 1; //ExportToExcelDoc.Write(startExcelXML); ExportToExcelDoc.Write(Constants.con.startExcelXML); for (int i = 0; i { string Sheetname = GetSheetname(dsInputi); //ExportToExcelDoc.Write( ); //ExportToExcelDoc.Write(Constants.con.startWorksheet + sheetCount + \>); ExportToExcelDoc.Write(Constants.con.startWorksheet + Sheetname + \>); //ExportToExcelDoc.Write( ); //ExportToExcelDoc.Write( ); ExportToExcelDoc.Write(Constants.con.startTable); // Create Column Header //ExportToExcelDoc.Write( ); ExportToExcelDoc.Write(Constants.con.startRow); for (int x = 0; x { //ExportToExcelDoc.Write( ); ExportToExcelDoc.Write(Constants.con.BoldColumn); ExportToExcelDoc.Write(dsInput.Tables[i].Columns[x].ColumnName); ExportToExcelDoc.Write(Constants.con.dataCell); } ExportToExcelDoc.Write(Constants.con.endRow); for (int y = 0; y { ExportToExcelDoc.Write(Constants.con.startRow); for (int j = 0; j { ExportToExcelDoc.Write(Constants.con.stringLiteral + Constants.con.dataTypeString); ExportToExcelDoc.Write(dsInput.Tables[i].Rows[y].ItemArray[j].ToString()); ExportToExcelDoc.Write(Constants.con.dataCell); } //ExportToExcelDoc.Write(  + //  ); //ExportToExcelDoc.Write(dsInput.Tables[i].Rows[y].ItemArray[1]); //ExportToExcelDoc.Write( ); ExportToExcelDoc.Write(Constants.con.endRow); } //ExportToExcelDoc.Write( ); ExportToExcelDoc.Write(Constants.con.endTable); ExportToExcelDoc.Write(Constants.con.endWorksheet); sheetCount++; } ExportToExcelDoc.Write(Constants.con.endExcelXML); ExportToExcelDoc.Close(); } Attached is the Constants class from where its reading the Constants. I have 9 data tables which i added in a single data set.each datatable data will be put in a each worksheet. just you need to pass the dataset and filepath where uou need to save the file after exporting to excel. Thanks Prasant