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

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

WORD OF WARNING : It got my attention (I didn't pay enough attention to this fact) that ExcelPackage

CodePlex is actually licensed under a fairly strict GPL license, making it practically unusable for anyone but hobbyists. If you use it, you will need to disclose the entire source code of the material generated 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);
}

      

        
        
        
      

    

Completely free, available with source, no need to install Office on the computer it runs on (like your web server), no slow and messy COM interactions - it just works like a charm! Highly recommended.

Mark

The two main approaches to your question are using ADO.NET or using the system.xml libraries. If you are using XML, you will create an XML-based Office 2007 format file (as well as read / write) whereas with ADO.NET you will need an existing afaik.

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

Another option (using a third party component) is SmartXLS .

Another option (no third party tools) is to use Excel Workbooks as ADO.NET data sources. Ref .: http://support.microsoft.com/kb/316934