How to create an Excel file using C#

Hi All

I want to create an EXCEL file from a Data table, how can I do that. I don't have excel installed on my web server. And customer can't afford it to. Is there any solution with out any third party components?

XML, CSV etc. can be readable by excel. Is that nothelpful for you?

No, I am able to use or generate XML very easily. But the client wants it in XLS format.

There is an ExcelXmlWriter libray for .NET from CarlosAg which is avaialble for free.

URL: http://www.carlosag.net/Tools/ExcelXmlWriter/

Not much powerful but still powerful.

Here is a sample provided by them: 

using CarlosAg.ExcelXmlWriter;

class TestApp {
    static void Main(string[] args) {
        Workbook book = new Workbook();
        Worksheet sheet = book.Worksheets.Add("Sample");
        WorksheetRow row =  sheet.Table.Rows.Add();
        row.Cells.Add("Hello World");
        book.Save(@"c:\test.xls");
    }
}

Please read the following page, before you start thinking about Office automation in your server.

http://support.microsoft.com/kb/257757

Thanks for the reply

@Praveen : It is not working as per my requirement. Its seems like it is using Office XML Formats. Excel 2003 Viewer displaying it as a "Invalid File format" And 2007, I am getting some warning.

More on Wikipedia : http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats

@Sreejumon :I am not doing Office automation. Excel is not installed on my machine.

And I found one solution, but I am not sure it is good or not

I just created a Template XLS file with all the columns. And I am copying it and I am modifing the file using the following code.

using (OleDbConnection connection = new
            OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + fileName + "; Extended Properties=Excel 8.0;"))
        {
            connection.Open();
            string query = "INSERT INTO [Sheet1$] (Column1, Column2, Column3, Column4, Column5) VALUES(@Column1, @Column2, @Column3, @Column4, @Column5)";
            using (OleDbCommand command = new OleDbCommand(query, connection))
            {
                foreach (DataRow item in this.dt.Rows)
                {
                    command.Parameters.AddWithValue("@Column1", item["Column1"].ToString());
                    command.Parameters.AddWithValue("@Column2", item["Column2"].ToString());
                    command.Parameters.AddWithValue("@Column3", item["Column3"].ToString());
                    command.Parameters.AddWithValue("@Column4", item["Column4"].ToString());
                    command.Parameters.AddWithValue("@Column5", item["Column5"].ToString());

                    command.ExecuteNonQuery();
                }
            }
            connection.Close();
        }

Now it works fine. But only problem, the columns should be pre-defined.

Some logical issues, I need to Open and Close everytime

using (OleDbConnection connection = new
            OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + fileName + "; Extended Properties=Excel 8.0;"))
        {
 foreach (DataRow item in this.dt.Rows)
 {
  connection.Open();
  string query = "INSERT INTO [Sheet1$] (Column1, Column2, Column3, Column4, Column5) VALUES  (@Column1, @Column2, @Column3, @Column4, @Column5)";
  using (OleDbCommand command = new OleDbCommand(query, connection))
  {
   command.Parameters.AddWithValue("@Column1", item["Column1"].ToString());
   command.Parameters.AddWithValue("@Column2", item["Column2"].ToString());
   command.Parameters.AddWithValue("@Column3", item["Column3"].ToString());
   command.Parameters.AddWithValue("@Column4", item["Column4"].ToString());
   command.Parameters.AddWithValue("@Column5", item["Column5"].ToString());
   command.ExecuteNonQuery();
                }
  connection.Close();
        }
}

for 100% free solutions visit the following link

http://www.codeproject.com/KB/cs/Simple_Excel_Automation.aspx

Hi Felix

Thanks for the reply. I resolved this issue. The solution you provided is not free, we need to install MS Excel in my server and we need to do the Office Automation. It got some issues(Check sreejumon's post).