Category: VB

.Net, C#, HTML, Office, Programming, VB, Web

Exporting asp:GridView Results To A Microsoft Excel Spreadsheet in VB/C#

Jared Meredith

I have received this requirement on more than one occasion so I thought it would benefit others if I posted these snippets. So here we go, let’s export a gridview as an excel file.

For starters let’s add a couple controls to the front-end aspx page:

 
<asp:Button ID="btnExport" runat="server" Text="Export Results To Excel" /> &amp;nbsp;&amp;nbsp;<br /><br />

<asp:GridView ID="grdSearch" runat="server" CellPadding="3" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px">
 <FooterStyle BackColor="White" ForeColor="#000066" />
 <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
 <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
 <RowStyle ForeColor="#000066" />
 <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
 <SortedAscendingCellStyle BackColor="#F1F1F1" />
 <SortedAscendingHeaderStyle BackColor="#007DBB" />
 <SortedDescendingCellStyle BackColor="#CAC9C9" />
 <SortedDescendingHeaderStyle BackColor="#00547E" />
 </asp:GridView>

I’m going to assume you know how to wire in your gridview to return results.

With that assumption in place here is the click event that performs the export (in VB):

You will need: Imports System.IO

Protected Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
        Try
            Response.Clear()
            Response.Buffer = True
            Response.ClearContent()
            Response.ClearHeaders()
            Response.Charset = ""
            Dim FileName As String = "filename" + DateTime.Now + ".xls"
            Dim strwritter As New StringWriter()
            Dim htmltextwrtter As New HtmlTextWriter(strwritter)
            Response.Cache.SetCacheability(HttpCacheability.NoCache)
            Response.ContentType = "application/vnd.ms-excel"
            Response.AddHeader("Content-Disposition", Convert.ToString("attachment;filename=") &amp; FileName)
            grdSearch.GridLines = GridLines.Both
            grdSearch.HeaderStyle.Font.Bold = True
            grdSearch.RenderControl(htmltextwrtter)
            Response.Write(strwritter.ToString())
            Response.[End]()
        Catch ex As Exception
            ' Do something important here if you expect strange results
        End Try
    End Sub

Now in C#:

You will need: using System.IO;

try {
	Response.Clear();
	Response.Buffer = true;
	Response.ClearContent();
	Response.ClearHeaders();
	Response.Charset = "";
	string FileName = "filename" + DateTime.Now + ".xls";
	StringWriter strwritter = new StringWriter();
	HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
	Response.Cache.SetCacheability(HttpCacheability.NoCache);
	Response.ContentType = "application/vnd.ms-excel";
	Response.AddHeader("Content-Disposition", Convert.ToString("attachment;filename=") + FileName);
	grdSearch.GridLines = GridLines.Both;
	grdSearch.HeaderStyle.Font.Bold = true;
	grdSearch.RenderControl(htmltextwrtter);
	Response.Write(strwritter.ToString());
	Response.End();
} catch (Exception ex) {
	// Do something important here if you expect strange results
}

I realize you may not need some of the formatting that I used in this example so remove the Gridview related property assignments in the export snippet. Also, depending on how you format your gridview on the aspx page will dictate some of the formatting you have on the spreadsheet. Hope this helps, questions are welcome.

IIS, Programming, VB

Need To Clean Your IIS Log Files (Older Than x Number of Days) For All Sites? Here’s a Script

Jared Meredith

You may have the classic issue of needing logs cleared but you do not have time to log onto each server and delete files for space. Here’s a script that will do it for all IIS sites and will let you select how many days back you wish to keep. For this example this script is wrote for 30 days and would be run on a task scheduler monthly.

  • Open notepad and create a file called IISLogMonthlyCleanup.vbs
  • Put in the following snippet and adjust your settings to apply to what you need:
sLogFolder = "c:\inetpub\logs\LogFiles"
iMaxAge = 30 ' # of days
Set objFSO = CreateObject("Scripting.FileSystemObject")
set colFolder = objFSO.GetFolder(sLogFolder)
For Each colSubfolder in colFolder.SubFolders
 Set objFolder = objFSO.GetFolder(colSubfolder.Path)
 Set colFiles = objFolder.Files
 For Each objFile in colFiles
 iFileAge = now-objFile.DateCreated
 if iFileAge &gt; (iMaxAge+1) then
 objFSO.deletefile objFile, True
 end if
 Next
Next

Save and run. *Poof* the old logs are gone. Set it up in a task scheduler and move on to more important things.

TIP: You can run the scheduled task as SYSTEM since it has privileges to run whether logged on or not. It also has access to the file system.

Hope this helps.