Wednesday, June 22, 2011

Import Excel 2003 / 2007 into a Table and then display it in a GridView without any dependencies

The basic concept behind this is that you want to import a templated Excel Spreadsheet into a GridView so that you can then commit it to a database or other data warehouse at some point.  In order to do this we will need to have some mechanism to upload the spreadsheet into a Table (In my example we will import it into a temporary table in MSSQL and then display it, however you could take out the temporary table step and just read it into a dataTable and display it directly into the GridView).  In my GridView, I give the users the option of deleting a row (or record) from the set before committing it to a permanent Table in MSSQL.

The Setup:

In order to connect to the uploaded Excel Spreadsheet, we need to have connectionStrings that define the providers we wish to use.  We do this in the web.config file.
<add name ="Excel03"
connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};
Extended Properties='Excel 8.0;HDR={1}'"

<add name ="Excel07"
connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};
Extended Properties='Excel 8.0;HDR={1}'"

You will want to put your connectionString tags in the configuration section of your web.config.  Proper placement is directly above the system.web tag that defines items such as your debugMode, customErrorPages, assemblies, generic namespaces, authentication, handlers, and modules.
As you can see, we will use the Microsoft.Jet.OLEDB provider.  This offers two extended properties to us, the Source (In our case, the actual file) and HDR.  HDR stands for HeaDeR.  The property is wanting to know if the first row in the SpreadSheet contains header information (Such as column names).  This will assist us with mapping and as you will see in the code-behind, we set this property to the boolean value of “TRUE”.


<asp:Table runat="server" ID="tblImport">
Excel Version:
<asp:DropDownList ID="ddlExcelVersion" runat="server">
<asp:ListItem Text="Excel 2003" Value="Excel 8.0;" Enabled="true" />
<asp:ListItem Text="Excel 2007" Value="Excel 12.0;" Enabled="true" />
<asp:FileUpload ID="fuExcel" runat="server" />
<asp:TableCell><asp:Button ID="btnImport" runat="server" Text="Import" CssClass="BigButton" /></asp:TableCell>

Here we setup a Table that contains several different rows and columns.  In the Table we give the user the option of telling us which version of Excel he/she is using.  We could have also used the extension in the code-behind to determine this, however for visualization in this article, we’ll let the user notify us.
We have a fileUploadControl that allows the user to browse for the Excel file.  Finally, we have a button that will trigger the Import called btnImport.  This will allow us access to a trigger event when the button is clicked which will be the catalyst for the import.

<asp:GridView ID="gdvImportedEmployeeInfo" runat="server"
AutoGenerateColumns="False" AlternatingRowStyle-BackColor="#B1F58E"
AlternatingRowStyle-BorderWidth="1px" BackColor="#CBF8B4"
BorderColor="#4BA918" BorderWidth="1px" EmptyDataText="No Data"
FooterStyle-BorderColor="#4BA918" FooterStyle-BorderWidth="1px"
HeaderStyle-BorderColor="#4BA918" HeaderStyle-BorderWidth="1px"
AllowSorting="False" AllowPaging="True" ShowFooter="false"
DataKeyNames="FName, MName, LName" PageSize="20" Width="100%">
<FooterStyle BackColor="#CBF8B4" BorderColor="#4BA918" BorderWidth="1px"></FooterStyle>
<PagerStyle HorizontalAlign="Left" />
<PagerSettings Position="Bottom" FirstPageText="First Page"
Mode="NumericFirstLast" LastPageText="Last Page"/>
<asp:TemplateField HeaderText="Name" >
<%#Eval("LName")%> <%# Eval("MName")%>, <%# Eval("FName") %>
<asp:BoundField HeaderText="Position" DataField="Position" HeaderStyle-HorizontalAlign="Center"
ItemStyle-HorizontalAlign="Center" ItemStyle-Wrap="false" HeaderStyle-Wrap="false" />
<asp:BoundField HeaderText="Pay" DataField="Pay" HeaderStyle-HorizontalAlign="Center"
ItemStyle-HorizontalAlign="Center" ItemStyle-Wrap="false" HeaderStyle-Wrap="false" />
<asp:BoundField HeaderText="Duty City" DataField="City" HeaderStyle-HorizontalAlign="Center"
ItemStyle-HorizontalAlign="Center" ItemStyle-Wrap="false" HeaderStyle-Wrap="false" />
<asp:BoundField HeaderText="Duty State" DataField="State" HeaderStyle-HorizontalAlign="Center"
ItemStyle-HorizontalAlign="Center" ItemStyle-Wrap="false" HeaderStyle-Wrap="false" />

<SelectedRowStyle BackColor="#FFFFE1" />
<HeaderStyle BackColor="#F7F7FF" BorderColor="#A5CBEF" BorderWidth="1px"></HeaderStyle>
<AlternatingRowStyle BackColor="#F7F7FF" BorderColor="#A5CBEF" BorderWidth="1px"></AlternatingRowStyle>

The above code block is our GridView.  You will notice that we have set the DataKeyNames property to LName, MName, FName.  You do not specifically need to set it to that many Columns, just something that makes a unique key.  You will see that we created a TemplateField.  I did this so that I could concatenate the name into a single column.  From there you have basic employee information with some styling on the tail end to make our GridView “pretty”.

The Meat and Potatoes (The Code-Behind)

I am going to break this code block up into smaller pieces so that we can talk about the example in smaller, understandable segments.

Imports sd = System.Data
Imports sdc = System.Data.SqlClient
Imports sdo = System.Data.OleDb
Imports System.IO
Imports System.Web.Configuration.WebConfigurationManager

Of course we need reference System.Data and System.Data.SqlClient.  System.Data.OleDb is what we will use with our provider by way of our connectionString to actually process the Excel Spreadsheet.  System.IO is used to read specific information about the uploaded file (for example extension).  Our ever faithful WebConfigurationManager is used to get a handle on our connectionString located in the web.config.
IMPORTANT NOTE: You must reference System.Web in order to be able to Import WebConfigurationManager.  This article assumes that you know how to reference a namespace/class/module as it is beyond the scope of the article.

Protected Sub Page_Load(ByVal sender As Object, 
ByVal e As System.EventArgs) Handles Me.Load

End Sub

Yup nothing special there… Serves no purpose in this context…

Protected Sub btnImport_Click(ByVal sender As Object, 
ByVal e As System.EventArgs) Handles btnImport.Click
lblStatus.Text = "Beginning import process...."
If Not (fuExcel.HasFile) Then
lblStatus.Text = "No import file defined."
Exit Sub
End If
Dim ExcelFile As String = fuExcel.PostedFile.FileName
Dim ext As String = Path.GetExtension(ExcelFile)
Dim strConn As String = ""
Select Case ext
Case ".xls"
'Excel 97-03
strConn = ConfigurationManager.ConnectionStrings("Excel03").ConnectionString()
Exit Select
Case ".xlsx"
'Excel 07
strConn = ConfigurationManager.ConnectionStrings("Excel07").ConnectionString()
Exit Select
End Select
strConn = String.Format(strConn, ExcelFile, "Yes")
Dim connExcel As New sdo.OleDbConnection(strConn)
Dim cmdExcel As New sdo.OleDbCommand()
Dim oda As New sdo.OleDbDataAdapter()
cmdExcel.Connection = connExcel

So we enter through the button’s click event.  We send a string to the label control letting the user know that we are beginning the import.  So as to ensure we are not going to get a filenotfound exception, we check to make sure that there is a file in the control.  Then we get a handle on the file by calling the PostedFile.Filename method.  In the HTML we had it where the user selected the type of file.  Here we go ahead and get the extension so that we can automatically tell and use a case statement based on the extension to determine the version so that we know which connectionString to use.
This is where we start building the connection to the actual document that is in the FileUploadControl.  Remember those two properties from the web.config (source and HDR)? They come into play here.  We set the connectionString to our connectionString based on version and populate the two properties. Setup our connection with the newly formatted connectionString and then build the OLEdb Command.  Additionally, we create a DataAdapter that serves as the conduit for us to talk to the dataSource (The Excel Spreadsheet).  Finally, we build our connection.
Dim dtExcelSchema As sd.DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(sdo.OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()

cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
oda.SelectCommand = cmdExcel

'df.da = da
lblStatus.Text = "Clearing Temp Table..."
lblStatus.Text = "Writing Data To Temp Table..."
lblStatus.Text = "Getting Data from Temp Table..."
df.da = inf.getTempTable()

Catch ex As Exception
lblStatus.Text = ex.Message
Exit Sub
End Try

Now we enter a Try/Catch block.  We open the connection to the actual dataSource and create a dataTable hold the Schema so that we can extract the “table name” which is the name of the actual Sheet that we want to process.  In this case we are only reading the first sheet, however you could extend this code by using a loop to process all the other sheets.  Then we close the connection.
We once again open the connection and with our query select all that is in the sheet.  We don’t care about security at this point because the sheet is local to us in the buffer so inserting the select statement into the code-behind is not of concern.  We now are filling our DataSet (That I declared globally..) The command is simply:
Dim da As sd.DataSet = New sd.DataSet(Notice I am aliasing the System.Data namespace)
I am not going to post the SQL to update the table as it will be specific to your columns in the spreadsheet.  At this point you have a DataSet and just need to Insert the columns into SQL. 

To finish out the code:
lblStatus.Text = "Retrieved " & da.Tables(0).Rows.Count.ToString() & " rows of data."

gdvImportedEmployeeInfo.DataSource = df.da
pnlGrid.Visible = True
End Sub

We just let the user know how many rows we received from the spreadsheet and then updated the GridView with the data in a Bind() method call.

If you have a templated Excel spreadsheet (Meaning that the columns never change) you can do imports into .Net fairly easily.  Taking this into account you can import spreadsheets into a GridView directly or into SQL without using SQLs mass import tool.  Additionally, you give the user the function of importing to spreadsheet themselves and then presenting it back to the user in a graphical sense…..

Happy .Netting… Saleh

No comments:

Post a Comment