hello,
can anyone speak to some of the common or preferred methods for
building Excel .XLS files, programmaticall y 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 8 2461
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
"ma**@mailinato r.com" wrote:
hello,
can anyone speak to some of the common or preferred methods for
building Excel .XLS files, programmaticall y 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
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 CreateExcelRepo rt(DataTable dataTable) {
string tempFolder =
System.Configur ation.Configura tionManager.App Settings["TempFolder "];
if (tempFolder.Len gth < 1) {
throw new IapException("T empFolder was not specified in
configuration file.");
}
string excelString = @"Provider=Micr osoft.Jet.OLEDB .4.0;
Data Source=" + tempFolder;
string statements = "";
string excelFileName = "MyFileName.xls ";
excelString += excelFileName;
excelString += "; Extended Properties=Exce l 8.0";
string current = DateTime.Now.To String("D").Rep lace(" ",
"").Replace("," , "");
List<stringcolu mns = new List<string>( );
foreach (DataColumn dc in dataTable.Colum ns) {
columns.Add(dc. ColumnName);
}
bool first = true;
using (OleDbConnectio n 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.ExecuteNonQ uery( );
}
using (OleDbConnectio n connection = new
OleDbConnection (excelString)) {
DataTable dt = new DataTable("Exce lTable");
OleDbDataAdapte r da = new OleDbDataAdapte r( );
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.InsertComman d = new OleDbCommand(in sertStatement,
connection);
foreach (string column in columns) {
string column2 = column.Replace( "-", "");
da.InsertComman d.Parameters.Ad d("@" +
column.Replace( " ", ""), OleDbType.VarCh ar).SourceColum n = column2;
}
foreach (string column in columns) {
string column2 = column.Replace( "-", "");
dt.Columns.Add( column2,
Type.GetType("S ystem.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(exc elRow);
}
da.Update(dt);
return excelFileName;
}
} ma**@mailinator .com wrote:
hello,
can anyone speak to some of the common or preferred methods for
building Excel .XLS files, programmaticall y 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
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
<ma**@mailinato r.comwrote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .
hello,
can anyone speak to some of the common or preferred methods for
building Excel .XLS files, programmaticall y 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,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
<ma**@mailinato r.coma écrit dans le message de
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .
hello,
can anyone speak to some of the common or preferred methods for
building Excel .XLS files, programmaticall y 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
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
<ma**@mailinato r.comwrote in message
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .
hello,
can anyone speak to some of the common or preferred methods for
building Excel .XLS files, programmaticall y 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
The MicrosoftExcelC lient is a simple class any developer can use in their
codes to interface to Excel Documents.
It has been created in C#.NET and uses OleDb. http://www.codeproject.com/useritems...xcelclient.asp
I use it with success
Regards
Nicolas Guinet
<ma**@mailinato r.coma écrit dans le message de news: 11************* *********@i42g2 00...legr oups.com...
hello,
can anyone speak to some of the common or preferred methods for
building Excel .XLS files, programmaticall y 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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Oci-One Kanubi |
last post by:
Everything works fine in Access, but when I double-click on the
resultant Excel files the first one opens correctly, but subsequent
ones, and any other Excel files I try to open, fail to display at all;
I get the Excel window frame with corrupted contents. The only way I
can get decent functionality from Excel is to open Excel from the
Start menu, open each of these new files from the Excel File menu, and
save each one. Thereafter Excel...
|
by: Raghavendra |
last post by:
hi,
we r using forms authetication.
problem :- i am using the below code to generate excel report
but since we r using forms authetication.. after generating excel report the
browser directs to login.apsx which is specified in web.config file.
this code is in one file.aspx
|
by: Boris Condarco |
last post by:
Hi gurus,
I'm using excel 2000 to show data that comes from datagrid. The problem is
that for any reason the asp.net application maintains the excel open, even
though, i do close it. Besides, does anyone know any third party compononet
to write excel files without having it installed?
My code looks like:
Public Function toExcel(ByVal fileName As String, ByVal dv As DataView)
|
by: daniele.balducci |
last post by:
Hi All,
I'm generating XLS files from ASP(.Net) code using the usual code
chunks ...
Response.ContentType = "application/vnd.ms-excel"
Response.AppendHeader("Content-Disposition", "attachment; filename=""" &
NomeFile & """")
Response.Flush()
Response.write("<table border=""1"" .....
|
by: Barbara Alderton |
last post by:
I’m relatively new to .NET. I would like to write a VB.NET app (I read that using C# isn't as elegant when dealing with Office products) that reads in Excel files (forms) of both 2000 and 2003 versions, off a file system. I want to access the different cells, some fixed and some variable, and retrieve this data and input it into a SQL 2000 database. Can I do this using the Excel Object model within the VB.NET code? What are the...
| |
by: mwazir |
last post by:
Dear all,
I have a .NET app that processes some excel file and it was working in all
scenarios.
Recently however we received excel files from a new client which my
application has been unable to process. The problem is that this file has
external links and when you open the excel file, it prompts you to either
update linked information or keep exisiting information.
|
by: gonzlobo |
last post by:
No, I don't want to destroy them (funny how the word 'decimate' has
changed definition over the years) :).
We have a data acquisition program that saves its output to Excel's
..xls format. Unfortunately, the programmer was too stupid to write
files the average user can read.
I'd like some advice on how to go about:
1. Reading a large Excel file and chop it into many Excel files (with
only 65535 lines per file)
|
by: c83 |
last post by:
some time ago i wrote an asp.net application which generated excel files using Microsoft.Office.Interop.Excel. It is working but it takes a lot of time to generate the excel file.
Yeasterday i modyfied it and i am generating them like HTML files and then send them to the user like:
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader( "Content-Disposition", "attachment; filename=result.xls" );
and i must say that it is...
|
by: helplakshmi |
last post by:
Hi All,
I wish you a Happy new year!!!..
My requirement is to upload an excel file using PHP and to update the data in MSSQL. For which i have designed a screen so that the user can browse 2 input files and update the database. html code for the same
<table cellspacing="3" cellpadding="3" style="color:#0000b9; background-color:#d7deec; ">
<tbody>
<tr>
<td>
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |