C
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">
<title>Untitled 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.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[col] ==
null) ? "" : data.Rows[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.
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">
<title>Untitled 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.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[col] ==
null) ? "" : data.Rows[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.