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.
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(); } } }
What about time in datetime element? How can we store it to MSExcel to read it?
ReplyDeleteThanks
Just look at the sample code provided in this post
ReplyDeleteSorry, 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.
ReplyDeleteThanks a lot.
Great post! What would be required to allow setting the row height for a given row?
ReplyDeleteHi, any idea on how to merge 2 ( or more) cells (horizontally or vertically)
ReplyDeleteThank you in advance
Hoa
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?
ReplyDeleteI need to store the values using formulas.
ReplyDeletedocument[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.
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.
ReplyDeleteHi,
ReplyDeleteFirst 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
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
ReplyDeleteI understand.
ReplyDeleteThis 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?
Great code! Thanks very much for share.
ReplyDeleteHow can export using xlsx (2007-2010) extension?
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.
ReplyDeleteThanks for this code. I just want to know how to merge cells in this code
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteCan 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
ReplyDelete"#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.
Hi, may I know how to implement TextWrap in creating Excel file?
ReplyDeleteHello
ReplyDeleteI 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?
Hi, I would like to change the existing excel workbook using your code. How can I do that?
ReplyDeleteHow to read properties of a cells in excel file such as font name,color, whether it bold or italic etc etc?
ReplyDelete