How to create excel sheet data directly from the .net application?

Question

I want to create excel sheet from asp.net application using C# language.

please reply me how to create it and also if having source. I don't know how to create it.

Also I want drop down list in one of column. how we can do this.

Please reply me its urgent....

Regards, Girish

Answer 1

I'm using the ExcelPackage library which uses the OpenXML standard file format from Office 2007 and makes it really easy to create your own Excel sheets fro your C# or VB.NET app with little effort.

WORD OF WARNING: it has come to my attention (I didn't pay enough attention to that fact) that the ExcelPackage on CodePlex is actually licensed under a rather strict GPL license, which makes it virtually unusable for anyone but hobbyists. If you use it, you'll have to reveal all your source code of stuff produced with it.

It might look something like this:

using OfficeOpenXml;  // namespace for the ExcelPackage assembly

FileInfo newFile = new FileInfo(@"C:\mynewfile.xlsx"); 
using (ExcelPackage xlPackage = new ExcelPackage(newFile)) 
{
   ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");

   // write some titles into column 1
   worksheet.Cell(1, 1).Value = "Product";

   worksheet.Cell(4, 1).Value = "Peas";
   worksheet.Cell(5, 1).Value = "Total";

   // write some values into column 2
   worksheet.Cell(1, 2).Value = "Tins Sold";

   ExcelCell cell = worksheet.Cell(2, 2);
   cell.Value = "15"; // tins of Beans sold
   string calcStartAddress = cell.CellAddress;  // we want this for the formula
   worksheet.Cell(3, 2).Value = "32";  // tins Carrots sold

   worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})",
   calcStartAddress, calcEndAddress);
}

Totally free, available with source, doesn't require an installation of Office on the machine it runs on (e.g. your web server), no slow and messy COM interop - it just works like a charm! Very highly recommended.

Marc

Answer 2

You can use Excel COM objects to create Excel sheets in ASP.NET.

Try this link.

Answer 3

try spread Sheet Gear third party component

http://www.spreadsheetgear.com/

Answer 4

Another option (no third party tools) is treat Excel Workbooks as ADO.NET Data Sources. Ref.: http://support.microsoft.com/kb/316934

Answer 5

The two basic approaches to your question are to use ADO.NET or to use system.xml libraries. If you use XML you will be creating a XML based Office 2007 format file (as well as read/write), whereas with ADO.NET you will need an existing file, afaik.

I'd recommend using a library such as the one suggested above to work with it as an XML file, and distribute it as a XLSX/XLSM based file. Even if the user is on Office 2003, they can use the compatibility back to open such files (basic download from Microsoft).

Answer 6

Another option (use the third party component) is SmartXLS.

Answer 7

An OpenSource Excel export library.

http://excelexportlib.codeplex.com/

Another OpenSource Excel export library.

http://xmlspreadgear.codeplex.com/