PDA

View Full Version : [CLOSED] Export To Excel with multiple tabs



Fahd
Jun 22, 2012, 9:32 PM
Hi,
I need to export multiple Store of data to one Excel file. Each Store data gets exported to a different tab within an Excel file. My code below only exports one Store of data. Can you please show me how to export Store1 and Store2 to the "submittedData.xls" file with Store1's data goes into the first tab and Store2 goes into the second tab? Also, how do you assign names to the tabs? I would like to name it something other than the word "record".

I attached my code for your review. Thank you very much in advance for your help!

Below is my code:


<%@ Page Language="C#" %>

<%@ Import Namespace="System.Xml.Xsl" %>
<%@ Import Namespace="System.Xml" %>

<%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>

<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.Store1.DataSource = new object[]
{
new object[] { 1, "3m Co", 71.72, 0.02, 0.03, "9/1 12:00am" },
new object[] { 2, "Alcoa Inc", 29.01, 0.42, 1.47, "9/1 12:00am" },
new object[] { 3, "Altria Group Inc", 83.81, 0.28, 0.34, "9/1 12:00am" },
new object[] { 4, "American Express Company", 52.55, 0.01, 0.02, "9/1 12:00am" },
new object[] { 5, "American International Group, Inc.", 64.13, 0.31, 0.49, "9/1 12:00am" },
new object[] { 6, "AT&T Inc.", 31.61, -0.48, -1.54, "9/1 12:00am" },
new object[] { 7, "Boeing Co.", 75.43, 0.53, 0.71, "9/1 12:00am" },
new object[] { 8, "Caterpillar Inc.", 67.27, 0.92, 1.39, "9/1 12:00am" },
new object[] { 9, "Citigroup, Inc.", 49.37, 0.02, 0.04, "9/1 12:00am" },
new object[] { 10, "E.I. du Pont de Nemours and Company", 40.48, 0.51, 1.28, "9/1 12:00am" },
new object[] { 11, "Exxon Mobil Corp", 68.1, -0.43, -0.64, "9/1 12:00am" },
new object[] { 12, "General Electric Company", 34.14, -0.08, -0.23, "9/1 12:00am" }
};

this.Store2.DataSource = new object[]
{
new object[] { 13, "General Motors Corporation", 30.27, 1.09, 3.74, "9/1 12:00am" },
new object[] { 14, "Hewlett-Packard Co.", 36.53, -0.03, -0.08, "9/1 12:00am" },
new object[] { 15, "Honeywell Intl Inc", 38.77, 0.05, 0.13, "9/1 12:00am" },
new object[] { 16, "Intel Corporation", 19.88, 0.31, 1.58, "9/1 12:00am" },
new object[] { 17, "International Business Machines", 81.41, 0.44, 0.54, "9/1 12:00am" },
new object[] { 18, "Johnson & Johnson", 64.72, 0.06, 0.09, "9/1 12:00am" },
new object[] { 19, "JP Morgan & Chase & Co", 45.73, 0.07, 0.15, "9/1 12:00am" },
new object[] { 20, "McDonald\"s Corporation", 36.76, 0.86, 2.40, "9/1 12:00am" },
new object[] { 21, "Merck & Co., Inc.", 40.96, 0.41, 1.01, "9/1 12:00am" },
new object[] { 22, "Microsoft Corporation", 25.84, 0.14, 0.54, "9/1 12:00am" },
new object[] { 23, "Pfizer Inc", 27.96, 0.4, 1.45, "9/1 12:00am" },
new object[] { 24, "The Coca-Cola Company", 45.07, 0.26, 0.58, "9/1 12:00am" },
new object[] { 25, "The Home Depot, Inc.", 34.64, 0.35, 1.02, "9/1 12:00am" },
new object[] { 26, "The Procter & Gamble Company", 61.91, 0.01, 0.02, "9/1 12:00am" },
new object[] { 27, "United Technologies Corporation", 63.26, 0.55, 0.88, "9/1 12:00am" },
new object[] { 28, "Verizon Communications", 35.57, 0.39, 1.11, "9/1 12:00am" },
new object[] { 29, "Wal-Mart Stores, Inc.", 45.45, 0.73, 1.63, "9/1 12:00am" }
};

this.Store1.DataBind();
this.Store2.DataBind();
}
}

protected void ToExcel(object sender, EventArgs e)
{
string json = GridData.Value.ToString();
StoreSubmitDataEventArgs eSubmit = new StoreSubmitDataEventArgs(json, null);
XmlNode xml = eSubmit.Xml;

this.Response.Clear();
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.AddHeader("Content-Disposition", "attachment; filename=submittedData.xls");
XslCompiledTransform xtExcel = new XslCompiledTransform();
xtExcel.Load(Server.MapPath("Excel.xsl"));
xtExcel.Transform(xml, null, this.Response.OutputStream);
this.Response.End();
}


</script>

<!DOCTYPE html>

<html>
<head id="Head1" runat="server">
<title>Export Data to Excel from Ext.NET 2.0 Examples</title>

<script type="text/javascript">
var saveData = function () {
App.GridData.setValue(Ext.encode(App.Store1.getRec ordsValues({ selectedOnly: false })));
};
</script>
</head>
<body>
<form id="Form1" runat="server">
<ext:ResourceManager ID="ResourceManager1" runat="server" />

<h1>Export Data to Excel in multiple Tabs</h1>

<ext:Hidden ID="GridData" runat="server" />

<ext:Store ID="Store1" runat="server">
<Model>
<ext:Model ID="Model1" runat="server">
<Fields>
<ext:ModelField Name="id" Type="Int" />
<ext:ModelField Name="company" />
<ext:ModelField Name="price" Type="Float" />
<ext:ModelField Name="change" Type="Float" />
<ext:ModelField Name="pctChange" Type="Float" />
<ext:ModelField Name="lastChange" Type="Date" DateFormat="M/d hh:mmtt" />
</Fields>
</ext:Model>
</Model>
</ext:Store>

<ext:Store ID="Store2" runat="server">
<Model>
<ext:Model ID="Model2" runat="server">
<Fields>
<ext:ModelField Name="Deptid" Type="Int" />
<ext:ModelField Name="CostCenter" />
<ext:ModelField Name="Cost" Type="Float" />
<ext:ModelField Name="Costchange" Type="Float" />
<ext:ModelField Name="pctChange" Type="Float" />
<ext:ModelField Name="lastChange" Type="Date" DateFormat="M/d hh:mmtt" />
</Fields>
</ext:Model>
</Model>
</ext:Store>
<ext:GridPanel
ID="GridPanel1"
runat="server"
StoreID="Store1"
Title="Export Data"
Width="600"
Height="350">
<ColumnModel ID="ColumnModel1" runat="server">
<Columns>
<ext:Column ID="Column1" runat="server" Text="Company" DataIndex="company" Flex="1" />
<ext:Column ID="Column2" runat="server" Text="Price" Width="75" DataIndex="price">
<Renderer Format="UsMoney" />
</ext:Column>
<ext:Column ID="Column3" runat="server" Text="Change" Width="75" DataIndex="change" />
<ext:Column ID="Column4" runat="server" Text="Pct.Change" Width="75" DataIndex="pctChange" />
<ext:DateColumn ID="DateColumn1" runat="server" Text="Last Updated" Width="85" DataIndex="lastChange" />
</Columns>
</ColumnModel>
<SelectionModel>
<ext:RowSelectionModel ID="RowSelectionModel1" runat="server" Mode="Multi" />
</SelectionModel>
<View>
<ext:GridView ID="GridView1" runat="server" StripeRows="true" TrackOver="true" />
</View>
<TopBar>
<ext:Toolbar ID="Toolbar1" runat="server">
<Items>
<ext:ToolbarFill ID="ToolbarFill1" runat="server" />

<ext:Button ID="Button2" runat="server" Text="To Excel" AutoPostBack="true" OnClick="ToExcel" Icon="PageExcel">
<Listeners>
<Click Fn="saveData" />
</Listeners>
</ext:Button>

</Items>
</ext:Toolbar>
</TopBar>
</ext:GridPanel>
</form>
</body>
</html>

Daniil
Jun 25, 2012, 1:09 PM
Hi,

There are two tasks:

1. Get the two Stores data on server.

You should call the getRecordsValues method for the second Store as well and combine two results of calling the getRecordsValues for the both Stores. This way you can get the Stores data on server.

Here is some example how to use the GridPanel getRowsValues for the same task.
http://examples2.ext.net/#/GridPanel/Miscellaneous/Submit_Two_Grids/

2. Convert these data to Excel format.

Generally, it is out of our competence. There are no any special things in Ext.NET to convert to Excel format. We just created the example to demonstrate some basics and the fact that it's possible. Unfortunately, we are not experts in this area. I would try to look it up on the internet or ask on some specialized forums like some Microsoft .NET forums or stackoverflow.

feanor91
Jun 25, 2012, 1:22 PM
Hello

Here my own solution :



Public Sub ExportToExcel(sender As Object, e As Ext.Net.DirectEventArgs)
Dim grd As Ext.Net.GridPanel = Ext.Net.X.GetCmp(e.ExtraParams("Grid"))
Dim ColumnModel As GridHeaderContainer = grd.ColumnModel
Dim LST_Field As List(Of String) = New List(Of String)
Dim LST_FieldName As List(Of String) = New List(Of String)
Dim LST_Lines As List(Of strucData) = JsonConvert.DeserializeObject(Of List(Of strucData))(e.ExtraParams("data"))
Dim Line As String = ""
Dim FileName As String = ""

For i As Integer = 5 To ColumnModel.Columns.Count - 1
LST_Field.Add(ColumnModel.Columns(i).DataIndex)
LST_FieldName.Add(ColumnModel.Columns(i).Text)
Next


Select Case grd.ID
Case "grdOSA"
FileName = "OSA Data"
Case "grdMASK"
FileName = "Mask Data"
Case "grdVEN"
FileName = "Ventilation Data"
Case "grdTPP"
FileName = "Third Party Product Data"
Case "grdATS"
FileName = "Activity Time Split Data"
Case "grdTRS"
FileName = "Time Repair Split Data"
Case "grdPS"
FileName = "Product Split Data"
Case "grdFCC"
FileName = "Call Center Data"
Case "grdLO_OM"
FileName = "Logistic Order Metrics Data"
Case "grdLO_AVA"
FileName = "Logistic availability Data"
Case "grdFI"
FileName = "Financial Data"
End Select

'Collumns heading
Dim row As New TableRow
Dim cell(LST_Field.Count - 1) As TableCell
For i As Integer = 0 To LST_Field.Count - 1
cell(i) = New TableCell
cell(i).Text = LST_FieldName(i)
row.Cells.Add(cell(i))
Next
CType(Page.FindControl("tblExport"), Table).Rows.Add(row)

For i As Integer = 0 To LST_Lines.Count - 1
row = New TableRow
Line = JsonConvert.SerializeObject(LST_Lines(i))
For j As Integer = 0 To LST_Field.Count - 1
cell(j) = New TableCell
cell(j).Text = GlobalFunction.DecodeJSON(Line, LST_Field(j), LST_Field(j).Substring(0, 3))
row.Cells.Add(cell(j))
Next
CType(Page.FindControl("tblExport"), Table).Rows.Add(row)
Next

'renseignement de l'objet response / Response object filling
Response.Clear()
Response.Buffer = True
Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName + " Export.xls")
Response.ContentEncoding = System.Text.Encoding.Default
Response.ContentType = "application/vnd.ms-excel"
Response.Write(GenHtmlTable())
Response.End()
End Sub



e.ExtraParams("Grid")

is the grid name


(e.ExtraParams("data")

are the grid data

The call :


<ext:Button ID="btnExportOSA" runat="server" Text="To Excel" Icon="PageExcel">
<DirectEvents >
<Click OnEvent="ExportToEXcel" IsUpload="true">
<ExtraParams>
<ext:Parameter Name="data" Value="#{grdOSA}.getRowsValues({selectedOnly : false})" Mode="Raw" Encode="true"></ext:Parameter>
<ext:Parameter Name="Grid" Value="grdOSA" Mode="Value" Encode="False"></ext:Parameter>
</ExtraParams>
</Click>
</DirectEvents>
</ext:Button>



I didn't use the way you choose because it exports all the data from the grid even some I didn't want user to see, so I made my own routine.

I imagine by using the same method, you could choose to export several store.

Fahd
Jun 25, 2012, 1:33 PM
Hi,
Thank you for your reply.
I have no problem getting the data on the server. I just have problem figuring out how to start a new tab and putting data into that each tab.
Any suggestion in that area?

Thank you.



Hi,

There are two tasks:

1. Get the two Stores data on server.

You should call the getRecordsValues method for the second Store as well and combine two results of calling the getRecordsValues for the both Stores. This way you can get the Stores data on server.

Here is some example how to use the GridPanel getRowsValues for the same task.
http://examples2.ext.net/#/GridPanel/Miscellaneous/Submit_Two_Grids/

2. Convert these data to Excel format.

Generally, it is out of our competence. There are no any special things in Ext.NET to convert to Excel format. We just created the example to demonstrate some basics and the fact that it's possible. Unfortunately, we are not experts in this area. I would try to look it up on the internet or ask on some specialized forums like some Microsoft .NET forums or stackoverflow.

Fahd
Jun 25, 2012, 2:39 PM
Thank you Feanor91 for the great idea on getting the data.! Do you have any idea on putting the data into different tabs?


Hello

Here my own solution :



Public Sub ExportToExcel(sender As Object, e As Ext.Net.DirectEventArgs)
Dim grd As Ext.Net.GridPanel = Ext.Net.X.GetCmp(e.ExtraParams("Grid"))
Dim ColumnModel As GridHeaderContainer = grd.ColumnModel
Dim LST_Field As List(Of String) = New List(Of String)
Dim LST_FieldName As List(Of String) = New List(Of String)
Dim LST_Lines As List(Of strucData) = JsonConvert.DeserializeObject(Of List(Of strucData))(e.ExtraParams("data"))
Dim Line As String = ""
Dim FileName As String = ""

For i As Integer = 5 To ColumnModel.Columns.Count - 1
LST_Field.Add(ColumnModel.Columns(i).DataIndex)
LST_FieldName.Add(ColumnModel.Columns(i).Text)
Next


Select Case grd.ID
Case "grdOSA"
FileName = "OSA Data"
Case "grdMASK"
FileName = "Mask Data"
Case "grdVEN"
FileName = "Ventilation Data"
Case "grdTPP"
FileName = "Third Party Product Data"
Case "grdATS"
FileName = "Activity Time Split Data"
Case "grdTRS"
FileName = "Time Repair Split Data"
Case "grdPS"
FileName = "Product Split Data"
Case "grdFCC"
FileName = "Call Center Data"
Case "grdLO_OM"
FileName = "Logistic Order Metrics Data"
Case "grdLO_AVA"
FileName = "Logistic availability Data"
Case "grdFI"
FileName = "Financial Data"
End Select

'Collumns heading
Dim row As New TableRow
Dim cell(LST_Field.Count - 1) As TableCell
For i As Integer = 0 To LST_Field.Count - 1
cell(i) = New TableCell
cell(i).Text = LST_FieldName(i)
row.Cells.Add(cell(i))
Next
CType(Page.FindControl("tblExport"), Table).Rows.Add(row)

For i As Integer = 0 To LST_Lines.Count - 1
row = New TableRow
Line = JsonConvert.SerializeObject(LST_Lines(i))
For j As Integer = 0 To LST_Field.Count - 1
cell(j) = New TableCell
cell(j).Text = GlobalFunction.DecodeJSON(Line, LST_Field(j), LST_Field(j).Substring(0, 3))
row.Cells.Add(cell(j))
Next
CType(Page.FindControl("tblExport"), Table).Rows.Add(row)
Next

'renseignement de l'objet response / Response object filling
Response.Clear()
Response.Buffer = True
Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName + " Export.xls")
Response.ContentEncoding = System.Text.Encoding.Default
Response.ContentType = "application/vnd.ms-excel"
Response.Write(GenHtmlTable())
Response.End()
End Sub



e.ExtraParams("Grid")

is the grid name


(e.ExtraParams("data")

are the grid data

The call :


<ext:Button ID="btnExportOSA" runat="server" Text="To Excel" Icon="PageExcel">
<DirectEvents >
<Click OnEvent="ExportToEXcel" IsUpload="true">
<ExtraParams>
<ext:Parameter Name="data" Value="#{grdOSA}.getRowsValues({selectedOnly : false})" Mode="Raw" Encode="true"></ext:Parameter>
<ext:Parameter Name="Grid" Value="grdOSA" Mode="Value" Encode="False"></ext:Parameter>
</ExtraParams>
</Click>
</DirectEvents>
</ext:Button>



I didn't use the way you choose because it exports all the data from the grid even some I didn't want user to see, so I made my own routine.

I imagine by using the same method, you could choose to export several store.

feanor91
Jun 25, 2012, 3:01 PM
You'r welcome.

As you can see by reading the code, I generate an HTML table to export the sheet. Not sure you could make more than one sheet in that way. I do know play wirh excel to make several sheets, even charts, but by using ole automation, and it is very difficult to play with OLE automation on server side (it is prohibited by Microsoft itself for security reasons). It is possible but very difficult to put in place (I abandaon that way long time ago as I have only simple grid to export). Meanwhile, if you really need something complicated you could try to play this way, or you export on one sheet you grid one after the other...But it is up to you to know what you want.