About Us

Carlo

Hi all,

First ,I really thank you for your consideration.

What is my demo want to do ?

I want to transfer the data of datatable to an Excel file format.

What is my solution?

1. Adding the Excel COM to my Web site;

2. Using excel programming module to create a new excel file

3. Write the record in the datatable to the excel file one by one

4. Save the Excel file.

What are the problems?

When users browse my site and click the "Export to Excel" button, it will
create a new excel app instance.For example,if there are 4 users user this
function,it will be 4 excel app processes in the server.

We could see them in the windows tasks management.

For dealing with this ,I use the singleton pattern to design the class which
is responsibility for transferring .

('TransferToExcel.cs' in my demo).

You know ,the new question is coming !

How could I deal with the concurrency ?

When many users use the Excel app instance to transfer their data,what I can
do in the server to control the concurrency?

If you have your own ideas or better solution or seggestions,please let me
know!

Thanks and best regards,

Carlo

2007-12-2

Accessory:My code

1. Please new a web site.

2.instead default.aspx like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="_Default" %

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"

<html xmlns="http://www.w3.org/1999/xhtml"
<head runat="server"
<titleUntitled Page</title
</head
<body
<form id="form1" runat="server"
<div

<asp:GridView ID="GridView1" runat="server"
</asp:GridView

</div
<asp:Button ID="Button1" runat="server" onclick="Button1_Click"
Text="ExportToExcel" /
</form
</body
</html
3.instead default.aspx.cs like this:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page
{
private DataTable dt;
protected void Page_Load(object sender, EventArgs e)
{

if (!Page.IsPostBack)
{
if (dt == null)
{
dt = new DataTable();
DataColumn dc = new DataColumn("ID");
dt.Columns.Add(dc);
dc = new DataColumn("Name");
dt.Columns.Add(dc);
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
dt.Rows[0]["ID"] = "1";
dt.Rows[0]["Name"] = "Carlo";

Session["sourceData"] = dt;
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string parentPath = Server.MapPath("Excel");
//call function to transfer the data to the ExcelFile
string strFileName =
TransferToExcel.TransferToExcelFile(parentPath, "carlo",
(DataTable)Session["sourceData"]);
if (strFileName.Length 0)
{

string strMachineName =
Request.ServerVariables["SERVER_NAME"];
Response.Write("http://" + strMachineName + "/Excel/" +
strFileName);
}
else
{
Response.Write("Sorry!There are some mistakes.");
}

}
}

4.Add App-code folder and add a new class file named TransferToExcel.cs. The
code in the cs file is as following:

using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System;

/// <summary
/// Transfer the data which are in a datatable to a file of excel format ///
/// </summary
public class TransferToExcel : IDisposable
{
private bool isDisposed = false;
private static ApplicationClass xApp;
public static string TransferToExcelFile(string parentPath, string
fileName, System.Data.DataTable data)
{

if (xApp == null)
{
xApp = new ApplicationClass();
// xApp.Visible = true;
}

if (xApp != null)
{
try
{
GC.Collect();
WorkbookClass xBook = xApp.Workbooks.Add(Missing.Value) as
WorkbookClass;
if (xBook != null)
{
//WorksheetClass xSheet = xBook.Sheets[1] as
WorksheetClass;
Worksheet xSheet = xApp.ActiveSheet as Worksheet;
Range range = null;
if (xSheet != null)
{
int columnCount = data.Columns.Count;
for (int i = 0; i < columnCount; i++)
{
range = (Range)xSheet.Cells[1, i + 1];
if (range != null)
range.Value2 = data.Columns[i].ColumnName;
}
int rowsCount = data.Rows.Count;
for (int i = 0; i < rowsCount; i++)
{
for (int col = 0; col < columnCount; col++)
{
range = (Range)xSheet.Cells[i + 2, col + 1];
if (range != null)
range.Value2 = (data.Rows[i][col] ==
null) ? "" : data.Rows[i][col].ToString();
}
}
}

fileName = fileName + DateTime.Now.Ticks.ToString() +
".xlsx";
string savePath = parentPath + "\\" + fileName;

xBook.SaveAs(savePath,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

xBook.Close(Missing.Value, Missing.Value, Missing.Value);
xSheet = null;
xBook = null;
//GC.Collect();
return fileName;
}
}
catch
{
return "";
}
finally
{
GC.Collect();
}
}
return "";

}

public void Dispose()
{
if (!isDisposed)
{
if (xApp != null)
{
xApp.Quit();
xApp = null;
GC.Collect();

}
}
isDisposed = true;
}

~TransferToExcel()
{

Dispose();

}
}
5.add a new folder named 'Excel' under the root folder.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
To deal with #NA	Amin	Excel Worksheet Functions	2	April 28th 10 07:06 AM
Has anyone made a Deal or No Deal Gamebord	[email protected]	Excel Discussion (Misc queries)	2	April 17th 08 12:12 AM
What's the deal here? thanks	pgarcia	Excel Discussion (Misc queries)	6	September 17th 07 07:08 PM
How to deal with no Workbook_Open for add-ins...?	[email protected]	Excel Programming	2	September 15th 06 07:58 PM
How to deal with the character ' in a SQL string	KS Wong[_2_]	Excel Programming	1	March 19th 06 06:52 AM

Menu

About Us