Generate Excel files without using Microsoft Excel (Part 2)

After publishing my article on CodeProject I received a lot of questions how to change the font, alignment or color of the cells in the resulting Excel file. I improved the code of the ExcelWriter library and added the possibility to format cells. You can download the new release of the ExcelWriter library here: http://users.telenet.be/serhiy.perevoznyk/download/XLSExportDemo.zip

Update from 31 Jan. 2012:
This code is provided to show the possibility of easy export of the information to Excel files, not to manipulate existing Excel files or performing the complex formatting operations. The aim of this demo is to make the export easy and simple. In case if you do more complex tasks I can recommend to use another library, for example http://www.smartxls.com/index.htm. I do not have any plans to extend the provided code in the future.

namespace XLSExportDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            
            ExcelDocument document = new ExcelDocument();
            document.UserName = "Perevoznyk";
            document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage;

            document.ColumnWidth(0, 120);
            document.ColumnWidth(1, 80);
            
            document[0, 0].Value = "ExcelWriter Demo";
            document[0 ,0].Font = new System.Drawing.Font("Tahoma", 10, System.Drawing.FontStyle.Bold);
            document[0, 0].ForeColor = ExcelColor.DarkRed;
            document[0, 0].Alignment = Alignment.Centered;
            document[0, 0].BackColor = ExcelColor.Silver;

            document.WriteCell(1, 0, "int");
            document.WriteCell(1, 1, 10);

            document.Cell(2, 0).Value = "double";
            document.Cell(2, 1).Value = 1.5;

            document.Cell(3, 0).Value = "date";
            document.Cell(3, 1).Value = DateTime.Now;
            document.Cell(3, 1).Format = @"dd/mm/yyyy";

            FileStream stream = new FileStream("demo.xls", FileMode.Create);

            document.Save(stream);
            stream.Close();
        }
    }
}

Comments

  1. What about time in datetime element? How can we store it to MSExcel to read it?

    Thanks

    ReplyDelete
  2. Just look at the sample code provided in this post

    ReplyDelete
  3. Sorry, I didn't explained my self enough. I'm talking about date and time. I saw your sample stores the number of days between a base date and today, as an integer, but what about the HH:mm:ss, that's what I'm looking to store in the xls file.

    Thanks a lot.

    ReplyDelete
  4. Great post! What would be required to allow setting the row height for a given row?

    ReplyDelete
  5. Hi, any idea on how to merge 2 ( or more) cells (horizontally or vertically)
    Thank you in advance

    Hoa

    ReplyDelete
  6. I used this solution to build XLS file, but when I tried to use the resulting file to do a mail merge in word, I got the error "Data source contains no visible tables.". Is there something that needs to be in the header to correct this?

    ReplyDelete
  7. I need to store the values using formulas.
    document[1,3].value = "SUM(A1:B1)" is storing the value as "SUM(A1:B1)" in Excel . It is reading the values as string. How to create formula using this for Excel.
    Also, can we implement TextWrap?

    Thanks in advance.

    ReplyDelete
  8. This is a really good and small component. A huge improvement comparing to exporting CSV-files. Thank you very much. I especially like that the code is so well made and is easily merged into other code. Thank you also for the generous licensing.

    ReplyDelete
  9. Hi,

    First off, great job on this one.

    I have one question though.

    Is it possible to write the data to an existing Excel file? I got the idea actually from reading your code, from the code "FileMode.OpenOrCreate", which actually specified that if the file already exists, then open it. However, all the old data is deleted from the file when the writer flushes the new data to the Excel file. My idea was to create an Excel file manually with images and already existing data and use this as a template, where I would just add text data with the help from your application.

    A possible solution for this would be to actually read the Excel file first to store all the old data, and adding this back to the new file. However, your application cannot read Excel files, right?

    An answer to this question would be much appreciated.

    Thank you and keep up the good work.
    // Marcus

    ReplyDelete
  10. This library was created to simplify the data export to excel and does not support of reading and modifying the existing files. I have no plans to implement this feature because a lot of freeware libraries already exists where the editing of the excel files is implemented

    ReplyDelete
  11. I understand.

    This problem could be solved with all the formatting coding you added to the library.
    However, is there any way to add an image to the Excel sheet via your library?

    ReplyDelete
  12. Great code! Thanks very much for share.
    How can export using xlsx (2007-2010) extension?

    ReplyDelete
  13. Great man, you are the King. If you could just ad one more feature and that is add formula then that would be great. Thanks in advance.

    ReplyDelete
  14. Thanks for this code. I just want to know how to merge cells in this code

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. Can any one let me know how I can specify colors which are not define in system, e.g I want to set cell backcolors like

    "#65c294" (light green)

    But when I set this kind of color in cell, it was changed to nearer color (green).

    I need your help!

    Thanks in advance.

    ReplyDelete
  18. Hi, may I know how to implement TextWrap in creating Excel file?

    ReplyDelete
  19. Anonymous20 May, 2012

    Hello
    I read your article, and also the article on CodeProject and I found them very interesting: I discovered a new way to generate excel files (BIFF), thanks.
    Where can I find documentation about the method used in your code?

    ReplyDelete
  20. Hi, I would like to change the existing excel workbook using your code. How can I do that?

    ReplyDelete
  21. How to read properties of a cells in excel file such as font name,color, whether it bold or italic etc etc?

    ReplyDelete

Post a Comment

Popular posts from this blog

Quricol - QR code generator library

Smir - backup and restore Windows desktop icons position

EIDNative Library 2.0 released