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<stringcolumns = 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;
}
}
ma**@mailinator.com 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
