How to generate excel XL document from ASP.NET C#

The below example demonstrate how easy it is to generate XL (excel) document in asp.net c#.

There many ways to achieve same but i am explaining one where you will not need MS word installed on your machine.

Here it goes

Download the dll from below path

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

Add a reference to it and try below code

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

Here is the output of the above sample on my machine.

image

You also might to modify this later….here it goes

public void AppendNewRow()
{

Workbook book = new Workbook();

            Worksheet sheet = null;
            book.Load(@”c:\test3.xls”);

            if (book.Worksheets.Count > 0)
            {
                sheet = book.Worksheets[0];
            }

            WorksheetRow row = sheet.Table.Rows.Add();

            row.Cells.Add(“Hello World”);
            row.Cells.Add(“Hello World”);
            row.Cells.Add(“Hello World”);
            row.Cells.Add(“Hello World”);

            book.Save(@”c:\test3.xls”);
}

image

You can see it is working like a charm.

There is a lot in the API if you want to do.

Let me incase

Tarun Juneja

Atticmedia Pvt. Ltd.

Noida

Advertisements

8 thoughts on “How to generate excel XL document from ASP.NET C#

  1. Hello,

    can you give some example code for ASP .Net, C#?

    Actually I tried this code in ASP .Net but
    book.Load(@”c:\test3.xls”); // here i used Server.Mappath
    this line gives an error.

    also I want to appened data not just add a single row.

    • I would like to tell you that…the xls file generated by my code are open xml xls files. so you can not edit these directly.

      i think you are trying to open an exiting xls file? Am i right?

      Sharing a new approach as well in couple of minutes…

    • And everytime you need to append a new row do below

      WorksheetRow row = sheet.Table.Rows.Add();
      row.Cells.Add(“new row data”);
      row.Cells.Add(“new row data”);
      row.Cells.Add(“new row data”);
      row.Cells.Add(“new row data”);

      if you want to append many data items then you can do same in foreach loop…i hope this helps.

    • Use below function to update the xls file directly.

      private void UpdateXls(object sender, EventArgs e)
      {
      try
      {
      System.Data.OleDb.OleDbConnection connection ;
      System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
      string sql = null;
      connection = new System.Data.OleDb.OleDbConnection(“provider=Microsoft.Jet.OLEDB.4.0;Data Source=’c:\\book1.xls’;Extended Properties=Excel 8.0;”);
      connection.Open();
      myCommand.Connection = connection;
      sql = “Update [Sheet1$] set name = ‘New Value’ where id=1”;
      myCommand.CommandText = sql;
      myCommand.ExecuteNonQuery();
      connection.Close();
      }
      catch (Exception ex)
      {
      throw;
      }
      }

      if you wish to append new rows fire a insert command instead of update.

      let me incase.

  2. Hi! i use carlosAg ExcelXmlWriter library and so far is great but i have one question. Is it possible to open the book before you save it, in other words make it visible in terms of interop. I’m searching for this because in most of the pc’s my application creates the test.xls and save it in c:/ or anywhere else i want but in some other pc’s my app can’t save the test.xls anywhere… so the book.Load(@”c:\test3.xls”); or the Prosses.Start(c:\test3.xls”); ….gives an error that the file wasn’t found..

    any ideas please!

  3. Pingback: Export to Excel in ASP.NET, issue with decimal formatting for numbers greater than 1000 | trouble86.com

  4. i am also facing the same problem i am using an excel which is created before i want to modify text of cell address like A1, B7, how would i be able to do the same? pls. suggest me i dont want to use oledb then how can i do it using load function of this api…

  5. Great goods from you, man. I have bear in mind your stuff prior to and you’re simply extremely excellent. I really like what you’ve obtained right here, certainly like what you’re saying and the way during which you are saying it. You make it enjoyable and you continue to take care of to stay it sensible. I can not wait to learn far more from you. This is actually a great website.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s