Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.dotnet.framework,microsoft.public.excel.programming,microsoft.public.dotnet.framework.aspnet
|
|||
|
|||
generating Excel files w/ .NET? (an ASP.NET, C# web app)
hello,
can anyone speak to some of the common or preferred methods for building Excel .XLS files, programmatically thru the .NET framework? i have an intranet app that needs to generate & email .xls files for users. these are not reports, but rather more like forms. its a pretty simple doc, two worksheets -- the first is basic info common to all recepients. the second is a simple data table that, depending on the recepient, contains pre-populated rows of data. there are some blank date fields in the table for the recepient to fill in. (and yep, after they do so and save, it will eventually get sent back to us for parsing of the values) i wont really get into the why's of this project, but suffice it to say this is the workflow we need. i had hoped i could retrieve an ADO.NET datatable of my recepient's rows, loop thru it, and add rows/cells to an in-memory Excel doc. then stream it out to the user or email it. as i understand VBA was designed to do this, but i would prefer to avoid VBA and keep it all .NET. ive found some 3rd party components to do such a thing, but at $7,500 a CPU, they seem pretty expensive. are there any other alternatives? thanks! matt |
#2
Posted to microsoft.public.dotnet.framework,microsoft.public.dotnet.framework.aspnet,microsoft.public.excel.programming
|
|||
|
|||
generating Excel files w/ .NET? (an ASP.NET, C# web app)
You can use the Excel COM wrapper, but I've found it quite easy to write
Excel-XML to a file (then you don't even need Excel installed on your server). If you name that file with an .xls extension, when you double-click on it, it brings up Excel and gets interpreted just fine. The trick is to build a real Excel spreadsheet the way you want then save it as XML to see what tricks are involved in using the XML. Then just do that in code. I just recently did this to create a spreadsheet with 3 tabs, auto-wrapped cells, data filters, inter-tab hyperlinks, cell highlighting, and freeze-panes. It's all right there in the XML, you just do what they do. Here's a good starter article: http://msdn.microsoft.com/library/de.../odc_xmlss.asp " wrote: hello, can anyone speak to some of the common or preferred methods for building Excel .XLS files, programmatically thru the .NET framework? i have an intranet app that needs to generate & email .xls files for users. these are not reports, but rather more like forms. its a pretty simple doc, two worksheets -- the first is basic info common to all recepients. the second is a simple data table that, depending on the recepient, contains pre-populated rows of data. there are some blank date fields in the table for the recepient to fill in. (and yep, after they do so and save, it will eventually get sent back to us for parsing of the values) i wont really get into the why's of this project, but suffice it to say this is the workflow we need. i had hoped i could retrieve an ADO.NET datatable of my recepient's rows, loop thru it, and add rows/cells to an in-memory Excel doc. then stream it out to the user or email it. as i understand VBA was designed to do this, but i would prefer to avoid VBA and keep it all .NET. ive found some 3rd party components to do such a thing, but at $7,500 a CPU, they seem pretty expensive. are there any other alternatives? thanks! matt |
#3
Posted to microsoft.public.dotnet.framework,microsoft.public.excel.programming,microsoft.public.dotnet.framework.aspnet
|
|||
|
|||
generating Excel files w/ .NET? (an ASP.NET, C# web app)
I just copy pasted from a project I did last year... Given a
dataTable, it creates the XLS and all that and returns the file name (there was a segment of the code the generated a file name, but I deleted that). This works great for everything we need... Basically, you just create a table and start putting stuff in the table. That said, there are some seriously anal details it wants. public static string CreateExcelReport(DataTable dataTable) { string tempFolder = System.Configuration.ConfigurationManager.AppSetti ngs["TempFolder"]; if (tempFolder.Length < 1) { throw new IapException("TempFolder was not specified in configuration file."); } string excelString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + tempFolder; string statements = ""; string excelFileName = "MyFileName.xls"; excelString += excelFileName; excelString += "; Extended Properties=Excel 8.0"; string current = DateTime.Now.ToString("D").Replace(" ", "").Replace(",", ""); List<string columns = new List<string( ); foreach (DataColumn dc in dataTable.Columns) { columns.Add(dc.ColumnName); } bool first = true; using (OleDbConnection connection = new OleDbConnection(excelString)) { connection.Open( ); OleDbCommand cmd = new OleDbCommand( ); cmd.Connection = connection; string createStatement = "create table Report" + current + " ("; foreach (string column in columns) { if (!first) { createStatement += ", "; } string column2 = column.Replace("-", ""); createStatement += "[" + column2 + "] VarChar(200)"; first = false; } createStatement += ");"; statements += createStatement + "\n"; cmd.CommandText = createStatement; cmd.ExecuteNonQuery( ); } using (OleDbConnection connection = new OleDbConnection(excelString)) { DataTable dt = new DataTable("ExcelTable"); OleDbDataAdapter da = new OleDbDataAdapter( ); string insertStatement = "insert into [Report" + current + "] ("; first = true; foreach (string column in columns) { if (!first) { insertStatement += ", "; } string column2 = column.Replace("-", ""); insertStatement += "[" + column2 + "]"; first = false; } insertStatement += ") values ("; first = true; foreach (string column in columns) { if (!first) { insertStatement += ", "; } string column2 = column.Replace("-", ""); insertStatement += "@" + column2.Replace(" ", ""); first = false; } insertStatement += ");"; statements += insertStatement + "\n"; da.InsertCommand = new OleDbCommand(insertStatement, connection); foreach (string column in columns) { string column2 = column.Replace("-", ""); da.InsertCommand.Parameters.Add("@" + column.Replace(" ", ""), OleDbType.VarChar).SourceColumn = column2; } foreach (string column in columns) { string column2 = column.Replace("-", ""); dt.Columns.Add(column2, Type.GetType("System.String")); } int n = 0; foreach (DataRow row in dataTable.Rows) { DataRow excelRow = dt.NewRow( ); foreach (string column in columns) { excelRow[column.Replace("-", "")] = row[column]; } dt.Rows.Add(excelRow); } da.Update(dt); return excelFileName; } } wrote: hello, can anyone speak to some of the common or preferred methods for building Excel .XLS files, programmatically thru the .NET framework? i have an intranet app that needs to generate & email .xls files for users. these are not reports, but rather more like forms. its a pretty simple doc, two worksheets -- the first is basic info common to all recepients. the second is a simple data table that, depending on the recepient, contains pre-populated rows of data. there are some blank date fields in the table for the recepient to fill in. (and yep, after they do so and save, it will eventually get sent back to us for parsing of the values) i wont really get into the why's of this project, but suffice it to say this is the workflow we need. i had hoped i could retrieve an ADO.NET datatable of my recepient's rows, loop thru it, and add rows/cells to an in-memory Excel doc. then stream it out to the user or email it. as i understand VBA was designed to do this, but i would prefer to avoid VBA and keep it all .NET. ive found some 3rd party components to do such a thing, but at $7,500 a CPU, they seem pretty expensive. are there any other alternatives? thanks! matt |
#4
Posted to microsoft.public.dotnet.framework,microsoft.public.excel.programming,microsoft.public.dotnet.framework.aspnet
|
|||
|
|||
generating Excel files w/ .NET? (an ASP.NET, C# web app)
There are a variety of ways to export to excel, and most of them are
outlined in this article: http://SteveOrr.net/Articles/ExcelExport.aspx Here's another option: http://SteveOrr.net/Articles/ExportPanel.aspx And here are a few good 3rd party options that make it easy to do complex things. http://SteveOrr.net/reviews/AsposeExcel.aspx http://officewriter.softartisans.com...writer-37.aspx http://www.syncfusion.com/products/xlsio/ -- I hope this helps, Steve C. Orr, MCSD, MVP http://SteveOrr.net wrote in message oups.com... hello, can anyone speak to some of the common or preferred methods for building Excel .XLS files, programmatically thru the .NET framework? i have an intranet app that needs to generate & email .xls files for users. these are not reports, but rather more like forms. its a pretty simple doc, two worksheets -- the first is basic info common to all recepients. the second is a simple data table that, depending on the recepient, contains pre-populated rows of data. there are some blank date fields in the table for the recepient to fill in. (and yep, after they do so and save, it will eventually get sent back to us for parsing of the values) i wont really get into the why's of this project, but suffice it to say this is the workflow we need. i had hoped i could retrieve an ADO.NET datatable of my recepient's rows, loop thru it, and add rows/cells to an in-memory Excel doc. then stream it out to the user or email it. as i understand VBA was designed to do this, but i would prefer to avoid VBA and keep it all .NET. ive found some 3rd party components to do such a thing, but at $7,500 a CPU, they seem pretty expensive. are there any other alternatives? thanks! matt |
#5
Posted to microsoft.public.dotnet.framework,microsoft.public.excel.programming,microsoft.public.dotnet.framework.aspnet
|
|||
|
|||
generating Excel files w/ .NET? (an ASP.NET, C# web app)
"$7,500 a CPU, they seem pretty expensive"
Indeed expensive. xlsgen : http://xlsgen.arstdesign.com -- xlsgen - native Excel generator http://xlsgen.arstdesign.com xlsgen RSS feed : http://www.arstdesign.com/BBS/rssxlsgen.php a écrit dans le message de oups.com... hello, can anyone speak to some of the common or preferred methods for building Excel .XLS files, programmatically thru the .NET framework? i have an intranet app that needs to generate & email .xls files for users. these are not reports, but rather more like forms. its a pretty simple doc, two worksheets -- the first is basic info common to all recepients. the second is a simple data table that, depending on the recepient, contains pre-populated rows of data. there are some blank date fields in the table for the recepient to fill in. (and yep, after they do so and save, it will eventually get sent back to us for parsing of the values) i wont really get into the why's of this project, but suffice it to say this is the workflow we need. i had hoped i could retrieve an ADO.NET datatable of my recepient's rows, loop thru it, and add rows/cells to an in-memory Excel doc. then stream it out to the user or email it. as i understand VBA was designed to do this, but i would prefer to avoid VBA and keep it all .NET. ive found some 3rd party components to do such a thing, but at $7,500 a CPU, they seem pretty expensive. are there any other alternatives? thanks! matt |
#6
Posted to microsoft.public.dotnet.framework,microsoft.public.excel.programming,microsoft.public.dotnet.framework.aspnet
|
|||
|
|||
generating Excel files w/ .NET? (an ASP.NET, C# web app)
VB/VBA is COM based. .Net is not.
You would need a COM wrapper for any .Net components and call the wrapper from Excel/VBA. But why not use the standard ADO library from VBA and avoid the .Net completely. Also the email side is striaght forward : http://www.rondebruin.nl/sendmail.htm If you want to "keep it all .NET", you can't use VBA. NickHK wrote in message oups.com... hello, can anyone speak to some of the common or preferred methods for building Excel .XLS files, programmatically thru the .NET framework? i have an intranet app that needs to generate & email .xls files for users. these are not reports, but rather more like forms. its a pretty simple doc, two worksheets -- the first is basic info common to all recepients. the second is a simple data table that, depending on the recepient, contains pre-populated rows of data. there are some blank date fields in the table for the recepient to fill in. (and yep, after they do so and save, it will eventually get sent back to us for parsing of the values) i wont really get into the why's of this project, but suffice it to say this is the workflow we need. i had hoped i could retrieve an ADO.NET datatable of my recepient's rows, loop thru it, and add rows/cells to an in-memory Excel doc. then stream it out to the user or email it. as i understand VBA was designed to do this, but i would prefer to avoid VBA and keep it all .NET. ive found some 3rd party components to do such a thing, but at $7,500 a CPU, they seem pretty expensive. are there any other alternatives? thanks! matt |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
generating Excel files w/ .NET? (an ASP.NET, C# web app)
Hello Stephane,
I have used very successfully both ADO.NET and Carlos Aguilar's XmlWriter library (http://www.carlosag.net/Tools/ExcelX...r/Default.aspx) to manipulate Excel spreadsheets. The way you describe your quandary, looks to me like you could easily use either one just depends on your taste. Take a look at David Hayden's article on manipulating Excel spreadsheets using ADO.NET http://davidhayden.com/blog/dave/arc...5/26/2973.aspx Hope it helps, Jose "$7,500 a CPU, they seem pretty expensive" Indeed expensive. xlsgen : http://xlsgen.arstdesign.com a écrit dans le message de oups.com... hello, can anyone speak to some of the common or preferred methods for building Excel .XLS files, programmatically thru the .NET framework? i have an intranet app that needs to generate & email .xls files for users. these are not reports, but rather more like forms. its a pretty simple doc, two worksheets -- the first is basic info common to all recepients. the second is a simple data table that, depending on the recepient, contains pre-populated rows of data. there are some blank date fields in the table for the recepient to fill in. (and yep, after they do so and save, it will eventually get sent back to us for parsing of the values) i wont really get into the why's of this project, but suffice it to say this is the workflow we need. i had hoped i could retrieve an ADO.NET datatable of my recepient's rows, loop thru it, and add rows/cells to an in-memory Excel doc. then stream it out to the user or email it. as i understand VBA was designed to do this, but i would prefer to avoid VBA and keep it all .NET. ive found some 3rd party components to do such a thing, but at $7,500 a CPU, they seem pretty expensive. are there any other alternatives? thanks! matt |
#9
Posted to microsoft.public.dotnet.framework,microsoft.public.excel.programming,microsoft.public.dotnet.framework.aspnet
|
|||
|
|||
generating Excel files w/ .NET? (an ASP.NET, C# web app)
thanks all for the replies.
i am not interested in merely exporting data to excel, thats easy. but rather in designing mid-complexity documents, some of which is based on dynamic data. i will investigate your suggested links. thanks! matt |
#10
Posted to microsoft.public.dotnet.framework,microsoft.public.dotnet.framework.aspnet,microsoft.public.excel.programming
|
|||
|
|||
generating Excel files w/ .NET? (an ASP.NET, C# web app)
Tim Johnson wrote: The trick is to build a real Excel spreadsheet the way you want then save it as XML to see what tricks are involved in using the XML. Then just do that in code. thanks tim! that sounds really sweet. however, i didnt see "XML Spreadsheet" as a "Save As..." option. then i realized we're on Excel 2000, and it appears the xml format was introduced in Excell 2002. doh. looks like i cant do that then, huh? matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
minor but irritating problem when generating Excel files with Perl | Excel Programming | |||
Generating HTML from Excel | Excel Programming | |||
Generating Charts in Excel | Excel Discussion (Misc queries) | |||
Generating excel combinations | Excel Discussion (Misc queries) | |||
VBA Excel - Generating Sounds | Excel Programming |