create and write to an excel file from c#

Question

I need to create a very simple excel file from C# code, I saw the following question but got mixed up about teh various options. could any one point me what's the best and simplest way to do so. I need it to run also on xls as well as on xlsx. in addition I need a formula to color a specific column according to content.

Answer 1

Many threads on SO about this.

For starters, take a look at: http://stackoverflow.com/questions/15828/reading-excel-files-from-c

Answer 2

Are you creating this file on the client or server side? If it's on the server side you shouldn't use automation. Aspose has a product that seems to work well, I think it's called Aspose.Cells. It's not free though.

Answer 3

One of the options I didn't see in the earlier thread was NPoi -- http://npoi.codeplex.com/

I believe this allows you to conditionally change cell color.

Some questions:

• Which versions of Excel do you need to support?
• Is this client-side or server-side?
• Will Excel be installed on the client?

Answer 4

SpreadsheetGear for .NET can create and write xls and xlsx workbooks from C# and supports conditional formats which can be used to color cells based on the values in cells.

You can see live ASP.NET samples here and download the free trial here if you want to try it yourself.

Here is code to create a workbook with random numbers between 0 and 1000 and a conditional format which sets the background to blue and text color to white for values >500. Load the resulting workbooks into Excel (or into the SpreadsheetGear for Windows application which is installed with the SpreadsheetGear trial software) and notice what happens when you press F9 to recalculate:

using System;
using SpreadsheetGear;

namespace FormatConditions
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a new empty workbook.
            IWorkbook workbook = Factory.GetWorkbook();
            IRange cells = workbook.Worksheets[0].Cells["A1:A5"];
            // Place formulas resulting in random numbers between 0 and 1000.
            cells.Formula = "=RAND()*1000";
            cells.NumberFormat = "0";
            // Add a format condition to use blue background and white text for numbers >500.
            IFormatCondition fc = cells.FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Greater, "500", "");
            fc.Interior.Color = System.Drawing.Color.Navy;
            fc.Font.Color = System.Drawing.Color.White;
            // Save to xls and xlsx.
            workbook.SaveAs(@"c:\tmp\FormatConditions.xls", FileFormat.Excel8);
            workbook.SaveAs(@"c:\tmp\FormatConditions.xlsx", FileFormat.OpenXMLWorkbook);
        }
    }
}

Disclaimer: I own SpreadsheetGear LLC

Answer 5

I've used Gembox.Spreadsheet and found it simple and fast. It supports xls and xlsx and can manipulate cell styles and formulas. They have a free edition that supports only 150 rows per worksheet and 5 worksheets per workbook.

Answer 6

This and this might give you an insight to jump-start writing xls files.