Exporting DataGrids to Excel ...
Ken Walker's article on exporting DataSets to Excel has proved very popular. This article by Mike Dolan presents a different technique for getting a DataGrid rather than the DataSet into Excel. We believe you will find it very useful.
BY:
Mike Dolan Date: August 13, 2003 Download The Code.
Printer Friendly Version
I have a Datagrid with a Dataset as its datasource that I want to export to Excel. Using Ken Walker's awesome tutorial (http://www.dotnetjohn.com/articles/articleid36.aspx) on how to pass Datasets to via Excel a component , I was able to easily send a dataset to excel from anywhere in my application. However, a few things did not end up working out for my application. First, I needed to clean up the formatting. Second, many of my datagrids had columns that were calculated from data in the datasets. Third, the header row in Excel always contained the database column names which were sometimes unintelligible to an ordinary user. My final problem was that often the Datasets contained data necessary for the datagrid generation, but that We Didn't want the end user to see.
To remedy all these issues I came up with the a simpler and more adaptable way to export the datagrid itself to Excel. I kept the concept to a component class so that it could easily be used throughout an application.
I created the following component I used VS.Net and left out the "Component Designer Generated Code Region" What you will find is that the class has one method that takes in two arguments:... A datagrid and a response It works by simply .
I also included sections on how to change formatting. In the application we built, we wanted our Excel exports to have a standardized look. The middle-end section shows different types of formatting changes that can be made at the class level.cmpDataGridToExcel.vb
Public Class cmpDataGridToExcel Inherits System.ComponentModel.ComponentPublic Shared Sub DataGridToExcel (ByVal dgExport As DataGrid, ByVal response As HttpResponse) 'clean up the response.object response.Clear () response.Charset = ""' set the response mime type for excel response .ContentType = "application / vnd.ms-excel" 'create a string writer Dim stringWrite As New System.IO.StringWriter ()' create an htmltextwriter which uses the stringwriter Dim htmlWrite As New System.Web.UI.HtmlTextWriter (stringWrite) 'instantiate a datagrid Dim dg As New DataGrid ()' just set the input datagrid = to the new dg grid dg = dgExport 'I want to make sure there are no annoying gridlines dg.GridLines = GridLines.None' Make the header text bold dg.HeaderStyle.Font.Bold = True 'If needed, here's how to change colors / formatting at the component level' dg.HeaderStyle.ForeColor = System.Drawing.Color.Black 'dg.ItemStyle.ForeColor = System.Drawing.Color .Bleck 'bind the modified datagr ID DG.DATABIND () 'Tell The DataGrid To Render Itself to Our HTMLTextWriter Dg.RenderControl (HTMLWRITE)' Output The Html Response.write (StringWrite.toString) Response.end () End Subend Class
After the component above was created / compiled, we then moved to the actual page that generated the Datagrid we would pass to the component One of our datagrids looked like the following:. As you can see we have totals, discounts, and Boolean approval checkboxes . ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
The following in based on a dataset taken from our SQL server. Just like in Ken's article, it does not matter how you generate the dataset or datagrid. Just create a datagrid and format it the way you want. Our datagrid above was generated with The Following:
For Security and Copyright Reasons, I Will Only include A Small Portion of Our Aspx Pages Mostly Template Column Generated In this fashion:
InvoiceApproval.aspx Code Snippet
<% # GetStore (DataBinder.Eval ( Container.DataItem, "intCustomerID"))%> <% # GetLabor (DataBinder.Eval (Container.DataItem, "dblLabor" ), DataBinder.eval (Container.DataItem, "DBLService"))%>
GetStore and GetLabor are simply functions we called passing the database data for the customer id and labor / service charges respectively. You can create your datagrid however you want, this simply shows why a dataset export with intCustomerID and not a Store # as we needed would NOT Work.a Northwind Database Example
Every tutorial needs a full example from the Northwind or Pubs database. The following is the Example.aspx file that displays the datagrid. We are still using the same component as you see above for the export.
.........................
DataGridexport.aspx
! <% @ Page Language = "vb" AutoEventWireup = "false" Codebehind = "DataGridExport.aspx.vb" Inherits = "ExportExample.DataGridExport"%> DataGridexport </ title> <meta name =" generator "content =" Microsoft Visual Studio.Net 7.0> <meta name = "code_language" content = "Visual Basic 7.0"> <meta name = "vs_defaultclientscript" content = "javascript"> <meta name = "vs_targetschema" content = "http://schemas.microsoft.com/intellisense/ie5"> </ head> <body> <div align = " Center "> <form id =" form1 "method =" post "runat =" server "> <p> <ask: DataGrid id =" DGTOEXPORT "runat =" server "bordercolor =" # 999999 "BorderStyle =" none "Borderwidth = "1px" BackColor = "White" CellPadding = "3" GridLines = "Vertical" AutoGenerateColumns = "False"> <SelectedItemStyle Font-Bold = "True" ForeColor = "White" BackColor = "# 008A8C"> </ SelectedItemStyle> <AlternatingItemStyle BackColor = "Gainsboro"> </ alternatingItemStyle> <itemStyle Forecolor = "Black" backcolor = "# eeeeee"> </ itemstyle> <headerstyle fo NT-bold = "true" forcolor = "White" backcolor = "# 000084"> </ headersty> <footerStyle Forecolor = "Black" backcolor = "# cccccc"> </ footerStyle> <columns></p>
<p><Asp: BoundColumn DataField = "EmployeeID" ReadOnly = "True" HeaderText = "ID"> </ asp: BoundColumn> <asp: TemplateColumn HeaderText = "Name"> <ItemTemplate> <% # ReturnName (DataBinder.Eval (Container. DataItem, "LastName"), DataBinder.Eval (Container.DataItem, "FirstName"))%> </ ItemTemplate> </ asp: TemplateColumn> <asp: BoundColumn DataField = "Title" ReadOnly = "True" HeaderText = "Title "> </ asp: boundcolumn> </ columns> <Pagersty Horizontalalign =" center "forecolor =" black "backcolor =" # 999999 "Mode =" numericpages "> </ pagerstyle> </ asp: dataGrid> </ p> <P> <asp: button id = "btnexport" runat = "server" text = "export to excel"> </ asp: button> </ p> </ form> </ div> </ body> </ html > And now to the codebehind where everything happens. Essentially I have the Northwind database table Employees coming in via the SqlDataAdapter1 to fill DataSet1. Then the datagrid is bound to the dataset. The btnExport handles the export to excel when a user clicks it. You Will Also See The Simple Function To Combine Names. Once The Grid IS Expo RTED, The names will be together in one cell. I Left out The design.</p>
<p>DataGridexport.aspx.vb</p>
<p>Public Class DataGridExport Inherits System.Web.UI.Page Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand Protected WithEvents SqlDeleteCommand1 As System. Data.SqlClient.SqlCommand Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter Protected WithEvents dgToExport As System.Web.UI.WebControls.DataGrid Protected WithEvents btnExport As System.Web.UI. WebControls.Button Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm Protected WithEvents DataSet1 As System.Data.DataSet 'Web Form Designer Generated Code left out Private Sub Page_Load (ByVal sender As System.Object, ByVal e As System.EventArgs ) Handles mybase.load 'put user code to initialize the page head bindgrid () End sub bin dGrid () 'Fill our dataset SqlDataAdapter1.Fill (DataSet1)' Assign the dataset to our Datagrid called dgToExport dgToExport.DataSource = DataSet1 'Finally bind the datagrid dgToExport.DataBind () End Sub Function ReturnName (ByVal strLastName, ByVal strFirstName)' This is the function I'm calling in the aspx page to show the difference 'between exporting a dataset versus exporting a datagrid. This function is' simply going to combine the first and last names and and return the' full name to the datagrid template column For "name". Dim strreturn as string strreturn = strfirstname & "</p>
<p>strLastName Return strReturn End Function Private Sub btnExport_Click (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click 'One line handles all of the export. We're simply calling the component (cmpDataGridToExcel),' then we 're using it's only method (DataGridToExcel), and we're passing our DataGrid (dgToExport) and the value reponse Note:. If you're using VS.Net, once you' build your solution after creating the component, Intellisense will now include your 'component. Just remember you have to build it first.' 'you could also modify your datagrid here before exporting it. For instance in my' invoice example we had a checkbox in our datagrid. If you have one of those the export 'will generate an error so we simply removed the column first like this before exporting:' dgToExport.Columns.Remove (dgToExport.Columns.Item (11)) cmpDataGridToExcel.DataGridToExcel (dgToExport, Response) End SubEnd ClassAnd that's all there is to it. Just create the component, compile it, and then once you have your datagrid setup one line exports it nice and cleanly to Excel at the click of a button. I hope this works out and possibly improves upon Ken Walker's great article That Helped Me Understand How this Whole string Writing.</p>
<p>You May Run The Program Here.you May Download The Code Here.</p></div><div class="text-center mt-3 text-grey">
转载请注明原文地址:https://www.9cbs.com/read-110075.html</div><div class="plugin d-flex justify-content-center mt-3"></div><hr><div class="row"><div class="col-lg-12 text-muted mt-2"><i class="icon-tags mr-2"></i><span class="badge border border-secondary mr-2"><h2 class="h6 mb-0 small"><a class="text-secondary" href="tag-2.html">9cbs</a></h2></span></div></div></div></div><div class="card card-postlist border-white shadow"><div class="card-body"><div class="card-title"><div class="d-flex justify-content-between"><div><b>New Post</b>(<span class="posts">0</span>)
</div><div></div></div></div><ul class="postlist list-unstyled">
</ul></div></div><div class="d-none threadlist"><input type="checkbox" name="modtid" value="110075" checked /></div></div></div></div></div><footer class="text-muted small bg-dark py-4 mt-3" id="footer"><div class="container"><div class="row"><div class="col">CopyRight © 2020 All Rights Reserved
</div><div class="col text-right">Processed:
<b>0.046</b>, SQL:
<b>9</b></div></div></div></footer><script src="./lang/en-us/lang.js?2.2.0"></script><script src="view/js/jquery.min.js?2.2.0"></script><script src="view/js/popper.min.js?2.2.0"></script><script src="view/js/bootstrap.min.js?2.2.0"></script><script src="view/js/xiuno.js?2.2.0"></script><script src="view/js/bootstrap-plugin.js?2.2.0"></script><script src="view/js/async.min.js?2.2.0"></script><script src="view/js/form.js?2.2.0"></script><script>
var debug = DEBUG = 0;
var url_rewrite_on = 1;
var url_path = './';
var forumarr = {"1":"Tech"};
var fid = 1;
var uid = 0;
var gid = 0;
xn.options.water_image_url = 'view/img/water-small.png';
</script><script src="view/js/wellcms.js?2.2.0"></script><a class="scroll-to-top rounded" href="javascript:void(0);"><i class="icon-angle-up"></i></a><a class="scroll-to-bottom rounded" href="javascript:void(0);" style="display: inline;"><i class="icon-angle-down"></i></a></body></html><script>
var forum_url = 'list-1.html';
var safe_token = 'wWUxtFxML303_2FPDrktA5AvYyCzNvM0FTEHtNsUtpBEQUFoMSkSXOLafcKak6nPM_2B4Yx_2BrlC1fNhbfIpsTHtmhw_3D_3D';
var body = $('body');
body.on('submit', '#form', function() {
var jthis = $(this);
var jsubmit = jthis.find('#submit');
jthis.reset();
jsubmit.button('loading');
var postdata = jthis.serializeObject();
$.xpost(jthis.attr('action'), postdata, function(code, message) {
if(code == 0) {
location.reload();
} else {
$.alert(message);
jsubmit.button('reset');
}
});
return false;
});
function resize_image() {
var jmessagelist = $('div.message');
var first_width = jmessagelist.width();
jmessagelist.each(function() {
var jdiv = $(this);
var maxwidth = jdiv.attr('isfirst') ? first_width : jdiv.width();
var jmessage_width = Math.min(jdiv.width(), maxwidth);
jdiv.find('img, embed, iframe, video').each(function() {
var jimg = $(this);
var img_width = this.org_width;
var img_height = this.org_height;
if(!img_width) {
var img_width = jimg.attr('width');
var img_height = jimg.attr('height');
this.org_width = img_width;
this.org_height = img_height;
}
if(img_width > jmessage_width) {
if(this.tagName == 'IMG') {
jimg.width(jmessage_width);
jimg.css('height', 'auto');
jimg.css('cursor', 'pointer');
jimg.on('click', function() {
});
} else {
jimg.width(jmessage_width);
var height = (img_height / img_width) * jimg.width();
jimg.height(height);
}
}
});
});
}
function resize_table() {
$('div.message').each(function() {
var jdiv = $(this);
jdiv.find('table').addClass('table').wrap('<div class="table-responsive"></div>');
});
}
$(function() {
resize_image();
resize_table();
$(window).on('resize', resize_image);
});
var jmessage = $('#message');
jmessage.on('focus', function() {if(jmessage.t) { clearTimeout(jmessage.t); jmessage.t = null; } jmessage.css('height', '6rem'); });
jmessage.on('blur', function() {jmessage.t = setTimeout(function() { jmessage.css('height', '2.5rem');}, 1000); });
$('#nav li[data-active="fid-1"]').addClass('active');
</script>
