Server Side Office Integration -2: How to Import Excel Data into Database with ASP.NET?
Intro:
In this sample yet robust web-based office integration solution, I have 3 objectives to cover:
-
How to read excel data
-
How to update & display excel data
-
How to import excel data into database
Specifications -briefly:
-
User selects an excel file to be imported
-
The application first validates the data against the business rules
-
If fails on validation, the system will display the errors back to the user with a failure message.
-
If succeeds,
-
it transforms the excel data into an XML Document
-
Imports XML data into the database table
-
Update the excel data with the updated records from the database call
-
Pipes new excel file back to the user.
How To Read/Write Excel Data:
In my prior post, I have mentioned different ways of doing CRU (Create, Update, Read) operations on an Office document and recommended using Open XML for the purpose. Please see for more details. This samples uses "Open XML" and "Excel Package" API's along with core .NET libraries. Let me tell you this, the application also exposes some best practices such as using Enterprise Library (v 3.1) for data access and logging purposes.
What you need:
-
.NET 3.0 -minimum, used v3.5
-
SQL Server (I am using SQL Express) and Adventure Works database installed.
-
Excel Package (Wrapper library for OfficeOpenXML API)
-
Microsoft Enterprise Library 3.1
-
IDE; used Visual Studio 2008.
-
Browser, used IE 7.0 for testing
-
Excel file
Alright, let's start the show:
-
User Interface:
<asp:FileUpload ID="fileUpload" runat="server" />
<asp:Button ID="btnImportExcel" runat="server" Text="Import Excel Data"
OnClick="btnImportExcel_Click" />
<br />
<asp:Literal ID="litMsg" runat="server"></asp:Literal>
<br /><br />
<asp:GridView ID="gvResults" runat="server" AutoGenerateColumns="false"
Caption="Errors Found" Visible="false">
<Columns>
<asp:TemplateField HeaderText="#">
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>'
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Description">
<ItemTemplate>
<%# Container.DataItem %>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
|
#region Members
int _sheetIndex = 1; //index of the worksheet (ws) to be imported
int _headerIndex = 1; //index of the headers in the ws.
int _columnCount = 8;
#endregion
#region Public/Protected Methods
protected void btnImportExcel_Click(object sender, EventArgs e)
{
if (fileUpload.HasFile)
{
List<string> errors;
string fileName = fileUpload.PostedFile.FileName;
try
{
ExcelImporter xlImporter = new ExcelImporter(fileName, _sheetIndex, _headerIndex,
_columnCount);
byte[] bytes = xlImporter.ImportExcelData(out errors);
if ((errors == null || errors.Count == 0) && bytes != null)
{
DisplayNewExcelFile(bytes);
}
else
{
litMsg.Text = "Importing excel data failed.";
gvResults.DataSource = errors;
gvResults.DataBind();
gvResults.Visible = true;
}
}
catch (Exception ex)
{
litMsg.Text = "An unexpected error occurred";
LogError(ex);
}
}
}
#endregion
|
Ouch, time for bed. Let's stop here and we will look into business and data access layers in the next sessions. Bye now.
Technorati Tags:
.NET 3.0/3.5,
Enterprise Library,
Logging & Data Application Blocks,
Best Practices & Design,
Open XML,
Excel Package,
C#,
Codeplex,
XML,
XPATH,
SQL,
Open Source,
AJAX,
JavaScript,
Design literature,
Fun stuff,
Reflections on life,
Pictures