PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > How could I deal with the concurrency in my project ?

Reply
Thread Tools Rate Thread

How could I deal with the concurrency in my project ?

 
 
Carlo
Guest
Posts: n/a
 
      3rd Dec 2007


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[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.
 
Reply With Quote
 
 
 
 
Tim Williams
Guest
Posts: n/a
 
      4th Dec 2007
Excel is not recommended for use server-side (or, more specifically,
recommended *not* to be used).

There are third-party components you can use instead which will likely
lessen the load.

Tim


"Carlo" <(E-Mail Removed)> wrote in message
news:C2347B33-E2A1-4235-BDE7-(E-Mail Removed)...
>
>
> 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[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.
>
>
>
>
 
Reply With Quote
 
Carlo
Guest
Posts: n/a
 
      4th Dec 2007
Hi Tim,
Thank you for your qucik response and suggestion.
And could you do me a favor to provide a solution for this request.
In my project,firstly,I use the RadGridViewControl,and do like this:

RadGrid1.AllowFilteringByColumn = false;
RadGrid1.ExportSettings.ExportOnlyData = true;
RadGrid1.ExportSettings.IgnorePaging = true;
RadGrid1.MasterTableView.ExportToExcel2007(fileName, true, true);
It can export the data of the table which is the data source of the girdview.
Excel application can open it too,but the format of exported file is 'HTML'
,is not a excel format.
Now ,what is the better solution for exporting the data of a tablt to the
excel format?
thanks and best regards,
Carlo
2007-12-4




"Tim Williams" wrote:

> Excel is not recommended for use server-side (or, more specifically,
> recommended *not* to be used).
>
> There are third-party components you can use instead which will likely
> lessen the load.
>
> Tim
>
>
> "Carlo" <(E-Mail Removed)> wrote in message
> news:C2347B33-E2A1-4235-BDE7-(E-Mail Removed)...
> >
> >
> > 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[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.
> >
> >
> >
> >
>
>
>
 
Reply With Quote
 
Tim Williams
Guest
Posts: n/a
 
      5th Dec 2007
I'm not familiar either with ASP .NET or the control you're using.

In general if you want the output sent to the client to be recognized as
Excel, then you have to add the correct content-type MIME header.

See: http://support.microsoft.com/kb/199841

Tim


"Carlo" <(E-Mail Removed)> wrote in message
news:6489B681-11B4-41C9-86FA-(E-Mail Removed)...
> Hi Tim,
> Thank you for your qucik response and suggestion.
> And could you do me a favor to provide a solution for this request.
> In my project,firstly,I use the RadGridViewControl,and do like this:
>
> RadGrid1.AllowFilteringByColumn = false;
> RadGrid1.ExportSettings.ExportOnlyData = true;
> RadGrid1.ExportSettings.IgnorePaging = true;
> RadGrid1.MasterTableView.ExportToExcel2007(fileName, true,
> true);
> It can export the data of the table which is the data source of the
> girdview.
> Excel application can open it too,but the format of exported file is
> 'HTML'
> ,is not a excel format.
> Now ,what is the better solution for exporting the data of a tablt to the
> excel format?
> thanks and best regards,
> Carlo
> 2007-12-4
>
>
>
>
> "Tim Williams" wrote:
>
>> Excel is not recommended for use server-side (or, more specifically,
>> recommended *not* to be used).
>>
>> There are third-party components you can use instead which will likely
>> lessen the load.
>>
>> Tim
>>
>>
>> "Carlo" <(E-Mail Removed)> wrote in message
>> news:C2347B33-E2A1-4235-BDE7-(E-Mail Removed)...
>> >
>> >
>> > 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[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.
>> >
>> >
>> >
>> >
>>
>>
>>
 
Reply With Quote
 
 
 
Reply

« Move around excel sheet | POSITION OF ErrorHandler? »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Has anyone made a Deal or No Deal Gameboard? kajunlion@yahoo.com Microsoft Excel Programming 2 17th Apr 2008 12:12 AM
Has anyone made a Deal or No Deal Gamebord kajunlion@yahoo.com Microsoft Excel Misc 2 17th Apr 2008 12:12 AM
AMD64 or Semperon, deal or no deal? Tad Confused Computer Hardware 13 13th Apr 2006 06:43 PM
Using multicast socketing to deal with ADO.NET concurrency Jay Riggs Microsoft ADO .NET 1 17th May 2004 01:01 AM
Re: Is this 181 GB drive on EBay a good deal or a bad deal? GMAN Storage Devices 0 24th Jun 2003 08:45 AM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 05:52 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top