I too have done some work with Excel automation and think this will be useful utility. The only problem with it is the average deployed version of Office is still 2000 and 2000 does not store as an XML object.
I think that the automation and VBA libraries are overly complex. I have often read that they improve development time by up to 16 x over 4 GL but I am still not impressed. In my experience, they have been messy and sometimes COM under VBA just isn't logical. There are times when VBA can't tell between properties and functions on COM objects. There are also times where you have to cast a member to an interface before you can get members of a member. It can be a real headache.
This sounds like a great tool! Just what I am looking to do...
Have you thought of making the DLL open-source?
Anonymous
September 20, 2005
Hi Carlos,
The XML Writer is a great tool. However I have come across one issue, it doesn't seem to be able to generate more than 64 Worksheets into a Workbook?
Any ideas how to get around this?
Thanks
Andrew
Anonymous
September 22, 2005
This is a great tool. Coupled with the code generator, this has saved me hours of writing Excel/XML code.
Thanks Carlos!
Anonymous
September 27, 2005
This is a superb tool to helps lot!!! Thanks for this great work.
Anonymous
October 20, 2005
I'm fairly new to the .NET framework, C# and XML and was creating a simple financial management system for personal use. I was trying to figure out how I could make a simple backup by creating a file that was downloadable from my server. I ended up simply writing a .csv with the tables that I needed. After coming across your XML Excel Writer I was impressed. This really opens my mind as to the possibilites of XML. Thanks for the effort in creating this library.
Anonymous
October 25, 2005
Carlos, I found something missing in your library. There is no way to protect/unprotect individual cell in a worksheet.
You should add to WorksheetStyle a new property
The XML traslation inside the Style tag is: <s:Style s:ID="s90"> <s:Protection s:Protected="0" /> </s:Style>
Thanks for this great library
Anonymous
December 31, 2005
Carlos
Hour tool has been extremely useful in the work I am doing now.
Thank you very much.
For me you have become something like the Madre Teresa of free tools for making easy, difficult tasks.
Thank you very much again.
Anonymous
January 15, 2006
Hi there, as others have said, excellent work. Without access to the source, it's not possible to extend this further and I would like to request support for external datasources for PivotTables.
The current PTSource does not seem to offer support for the <QuerySource> element that would allow for this.
This has proved invaluable in learning how to manipulate the excel file format. Thanks again.
Anonymous
January 19, 2006
Nice tool.
Any reason why it does not function on Excel 2000?
Anonymous
February 09, 2006
Harvey,
This tool does not function on Excel 2000, because it is only later versions of Excel that store data as XML objects.
Anonymous
February 15, 2006
Carlos tested it in Excel 2003, but I can confirm it works well in 2002 version, as well.
Anonymous
March 09, 2006
How can we use Pivot Table in Excel. this API is very nice, i worked on it and i process some work in this API(Set the DataBase in this and also in WorkSheets as well), but i cann't be able to create Pivot Table this is how i am trying to do :)...
Anonymous
March 23, 2006
this tool is simply great but using this i get excel xml so can u tell me how i represent this xml into proper format as it look in excel spreadsheet
Anonymous
March 24, 2006
this tool is simply great but using this i get excel xml so can >u tell me how i represent this xml into proper format as it >look in excel spreadsheet
why donot you use Office API it is better then this CarlosAg .... (Primary Interop Assembly -- PIA)
Anonymous
April 24, 2006
The comment has been removed
Anonymous
May 02, 2006
Hi I think this is a great tool.
I tested a lot of other tools around and definetely this is the fastest, ideal for a web based app.
Do someone knows something similar for Word?
Is there any chance to get the source of the assembly or buy them? It is unlikely as a company we can use something without sources.
Rosario
Anonymous
May 12, 2006
I have a work book which contains Validation Format on some columns.
The CodeGen does not seam to create code for the Validation Format.
My Validation Format is using a Range Based List in a separate worksheet of the same workboobk.
Any ideas or advice?
Thanks,
Anonymous
May 16, 2006
Hi. How can I write to cell value like a "00:00:00". I try set DateTime format, but then can't open workbook. it's message "Problem during load <Load>". What can I do?
Anonymous
July 13, 2006
The comment has been removed
Anonymous
July 16, 2006
The comment has been removed
Anonymous
July 19, 2006
Carlos
Your library is awesome. Thanks a million
Anonymous
July 26, 2006
ExcelWriter is a great tool for generating excel . It was of great help in my project. Thanks to Marcos. If he can include the ability for OLe objects to be included in this it , would be even more complete .
Anonymous
July 29, 2006
Encore&nbsp;quelquechose que je ne connaissais pas encore et qui pourtant répondrait à un besoin qui...
Anonymous
July 30, 2006
OpenOffice 2 is said to understand all Excel formats, however it does not recognize the file created by that application. OO thinks its a text and opens it in Writer.
May be some xml tag is missing? It would be very nice for us poor guys not able pay for MS Office ;)
Anonymous
August 17, 2006
Does Anyone knows if this supports using excel template (.xlt)? Thanks alot.
Anonymous
August 17, 2006
Does Anyone knows if this supports using excel template (.xlt)? Thanks alot.
Anonymous
September 07, 2006
Is there anyway to make the excel print certain rows/columns on every page that is printed?
Anonymous
September 14, 2006
Great Tool!!!!!
Thanks for the great tool and effort..
Anonymous
October 18, 2006
Really a great tutorial .. It really helped me a lot..
Anonymous
October 29, 2006
I m developing an Application which shall open an Existing Excel File(*.xls).The Application shall let the User do some formatting and editing in the Excel File, but when the User SAVES the Excel File,The File should be Saved in my Format. I managaed to open the File in an WebBrowser control but am stuck in Saving the File in my formatCan any body help meI am using .Net 2003 , C# language
Anonymous
December 10, 2006
hi...We are Exporting chinese data to Excel....while exporting sometimes chinese chars appears as special chars in the excel sheet and sometimes not.We have tried lot many thing for making it consistent.But....Is this application is useful i this case...??
Anonymous
January 15, 2007
Great Effort to make developers life easy,I used your tool, it is excellent for creating excel files, but can you help me if i want to open that file in oledb connection, Any workaround for it ?Keep It up
Anonymous
January 18, 2007
A great implementation!At least i can generate the results on demand and "Save" it directly to a memory stream. Exactly what i needed!Works really fine. Thanks a lot.Only one question. Is it possible to enter a LineBreak into a cell? (EXCEL: ALT + ENTER)The normal Escape sequence "n" will be removed.(And i hate sealed classes :p)
Anonymous
February 24, 2007
Thank you this is great.Is there any way to load an XML file that has been previously created and then pass values to a selected sheet that already exists. As opposed to book.Worksheets.Add("SampleSheet"); using a similar feature to the Excel orkbook.Worksheets.get_Item(currentSheet);Any information would be muchly appreciated.
Anonymous
March 11, 2007
Thanks it looks great!But how can I read a Excel-File and store it to a DataSet?Thank youBest regards,Isha
Anonymous
March 20, 2007
Does it work on Mac OS X version of Microsoft Office?I have tried it with no luck. It gives an error on the Style tag.Please let me know.
Anonymous
March 30, 2007
I downloaded this tool and used it to build up an order form for our website so customers could print it out and fax it to us. It worked perfectly, as long as you have a version of Excel > 2000. I found this out when I went to do a demo for everyone and instead of opening as a beautiful order form, it opened as a bunch of xml markup in cells.Are HTML tables the only way to do this if Excel 2000 compatability is required?Also, is there a way to make only a portion of a cell bold, underlined, etc?
Anonymous
April 11, 2007
I tried this CarlosAg.ExcelXmlWriter and found it great. But one thing i would like to ask that I tried to creat style and set the NumberFormat to Percent, but its not working. I mean the cell to which this style is applied is not showing correct Percent format.If anyone knows how to do this or anything I am missing,. please inform me asap.
Anonymous
April 12, 2007
Does This liberary support column auto fit?
Anonymous
April 27, 2007
This library is awsome !!Keep the good work,Thanks.
Anonymous
May 08, 2007
Plsase suggest me the method to lock the call of OWC spreadsheet.
Anonymous
May 10, 2007
Hi,i´ve been using your library for sometime, i´ve a question,is it possible to insert page breaks in the code?thanks, and keep on bringing new ideasBrunio
Anonymous
May 19, 2009
This is excellent work,thanks for sharing it
Anonymous
July 30, 2009
Dear All,Pls Help me!public class ExcelGenerator{ // Methods public static Workbook Generate(DataGridView dataGridView) { //ExcelWorkbook workbook = new ExcelWorkbook(); Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets.Add("Sheet1"); WorksheetRow worksheetRow = new WorksheetRow(); //foreach (DataGridViewColumn dataGridViewColumn = default(DataGridViewColumn) in dataGridView.Columns) foreach (DataGridViewColumn dataGridViewColumn in dataGridView.Columns) { worksheet.Table.Columns.Add(new WorksheetColumn(dataGridViewColumn.Width)); worksheetRow.Cells.Add(new WorksheetCell(dataGridViewColumn.HeaderText.Trim())); } worksheet.Table.Rows.Insert(0, worksheetRow); WorksheetStyle worksheetDefaultStyle = ExcelGenerator.GetWorksheetStyle(dataGridView.DefaultCellStyle, "Default"); workbook.Styles.Add(worksheetDefaultStyle); int rowIndex = 0; for (rowIndex = 0; rowIndex <= dataGridView.RowCount - 1; rowIndex++) { worksheetRow = worksheet.Table.Rows.Add(); int columnIndex = 0; for (columnIndex = 0; columnIndex <= dataGridView.ColumnCount - 1; columnIndex++) { DataGridViewCell cell = dataGridView.Rows[rowIndex].Cells[columnIndex]; //dataGridView.Item(columnIndex, rowIndex); WorksheetStyle cellStyle = ExcelGenerator.GetWorksheetStyle(cell.InheritedStyle, string.Concat(new object[] { "column", columnIndex, "row", rowIndex })); if (((cellStyle != null))) { workbook.Styles.Add(cellStyle); } else { cellStyle = worksheetDefaultStyle; } DataType dataType = ExcelGenerator.GetDataType(cell.ValueType); worksheetRow.Cells.Add(cell.FormattedValue.ToString().Trim(),dataType, cellStyle.ID); //worksheetRow.Cells.Add(cell.FormattedValue.ToString(), dataType, cellStyle.ID); } } return workbook; } private static string GetColorName(Color color) { return ("#" + color.ToArgb().ToString("X").Substring(2)); //return ("#" + color.ToArgb.ToString("X").Substring(2)); } private static DataType GetDataType(Type valueType) { if (((!object.ReferenceEquals(valueType, typeof(DateTime))))) { if ((object.ReferenceEquals(valueType, typeof(string)))) { return DataType.String; } if (((((((object.ReferenceEquals(valueType, typeof(sbyte))) || (object.ReferenceEquals(valueType, typeof(byte)))) || ((object.ReferenceEquals(valueType, typeof(short))) || (object.ReferenceEquals(valueType, typeof(UInt16))))) || (((object.ReferenceEquals(valueType, typeof(int))) || (object.ReferenceEquals(valueType, typeof(UInt32)))) || ((object.ReferenceEquals(valueType, typeof(long))) || (object.ReferenceEquals(valueType, typeof(UInt64)))))) || ((object.ReferenceEquals(valueType, typeof(float))) || (object.ReferenceEquals(valueType, typeof(double))))) || (object.ReferenceEquals(valueType, typeof(decimal))))) { return DataType.Number; } } return DataType.String; } private static WorksheetStyle GetWorksheetStyle(DataGridViewCellStyle dataGridViewCellStyle, string id) { WorksheetStyle worksheetStyle = null; if (((dataGridViewCellStyle != null))) { worksheetStyle = new WorksheetStyle(id); if (!dataGridViewCellStyle.BackColor.IsEmpty) { if (dataGridViewCellStyle.BackColor.Name.ToString()!="Window") { //MessageBox.Show(dataGridViewCellStyle.BackColor.Name.ToString()); worksheetStyle.Interior.Color = ExcelGenerator.GetColorName(dataGridViewCellStyle.BackColor); worksheetStyle.Interior.Pattern = StyleInteriorPattern.Solid; } } if (!dataGridViewCellStyle.ForeColor.IsEmpty) { worksheetStyle.Font.Color = ExcelGenerator.GetColorName(dataGridViewCellStyle.ForeColor); } if (((dataGridViewCellStyle.Font != null))) { worksheetStyle.Font.Bold = dataGridViewCellStyle.Font.Bold; worksheetStyle.Font.FontName = dataGridViewCellStyle.Font.Name; worksheetStyle.Font.Italic = dataGridViewCellStyle.Font.Italic; worksheetStyle.Font.Size = (int)dataGridViewCellStyle.Font.Size; worksheetStyle.Font.Strikethrough = dataGridViewCellStyle.Font.Strikeout; worksheetStyle.Font.Underline = (dataGridViewCellStyle.Font.Underline ? UnderlineStyle.Single : UnderlineStyle.None); } //worksheetStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1, "Black"); //worksheetStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1, "Black"); //worksheetStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1, "Black"); //worksheetStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1, "Black"); } return worksheetStyle; }}I used that class for export to excel file but file export completed big size and spend long time to open it. Pls help me make it down size and open quickly.thank you so much.
Anonymous
November 15, 2009
Hello...Thank you so much for this usefull tool...this really works for me...and is so easy for implemantation too...I have just one question...see...I use your dll to create an Excel file from my web app...the problem is that when I run the app and generate the first Excel document, it works perfect, but if I generate another Excel document without restarting the app when I try to open the document it says that there is a problem with the table and the document cannot be opened...sometimes it says that there is an error with style too and the document cannot be opened...I try creating the table again, assigning the table = null, but it says that the table is read only so the only solution that I found was to make a Redirect to the same page so all the objects and variables needs to be inicialized again...but make a Redirect each time that I generate an Excel file is so annoying...Could you tell me please why Im getting this error message when I generate more than ONE Excel document in my app ???...Thanks...
Anonymous
February 19, 2010
I am getting the following warning message when using the excel writer with excel2007The file you are trying to open, ‘test.xls’ is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Dou you wan to open the file now?I could find a solution here at : http://codeasp.net/blogs/vivek_iit/microsoft-net/92/the-file-you-are-trying-to-open-xxxxls-is-in-a-different-format-than-specified-by-the-file-extensionbut changing the registry setting of the client is not practical solution.Please let me know if you have any soultion on this?
Anonymous
April 07, 2010
Thank you very much for this excellent library.I implemented Excel Automation using this as i couldnt install Excel on server.I'm stuck on a tiny little detail here, I want the header Row to appear on each page in page setup for printing.I dont find a property to set it up in carlos Library.Am i missing something ? Please throw me few suggestions.
Anonymous
April 11, 2010
Im trying to generate an excel file with the xmlwriter library and the file size of 4000 rows is 100+MB why this could be happenig?
Anonymous
September 21, 2010
Hi, nice tool, but, I am able to open the generated file only in Microsoft's Excel, when I try to open it in Open Office's Calc it shows me the XML how can I make it right?
Anonymous
December 01, 2010
Does anyone know how to encode a pound sign properly in a number style..? I used the tool on Carlos' page to create VB code from an XML example but the supplied code results in a non printing character appearing in front of the pound sign.Cheers
Anonymous
December 28, 2011
The comment has been removed
Anonymous
October 14, 2013
The comment has been removed
Anonymous
July 20, 2016
Please how can I add picture in worksheet using CarlogAg, i neet this quicly.