Introduction Sometimes users ask interesting question in the message board. last time one valuable user asked question how he can look uploaded excel sheet to server. It's excellent question, i try to get it work around in my pc with asp.net , finally get good result to that.here i'm going share my code and words. Technologies
ASP.NET 2.0 or later
Language
C#
Prerequisite
Visual Studio 2008,MS Office .NET Runtime.
Implementation
First we need design simple web page to upload excel file to server, then we need get that excel file and display structure in the web page like excel sheet. 
Look above figure there is two steps we need to the complete the operation. in the first steps we need pick up the excel sheet from the client PC , then click to upload button to save that file in the server. Before that this Web page build using a file upload control , button,checkbox and lables controls. Above figure html source like followings, {codecitation class="brush:html; gutter: true;" width="600px"} <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExcelSheetViewer.aspx.cs"
Inherits="Convertors_ExcelSheetViewer" %>
{/codecitation}
Now start to write code in the code behind file, first Click the browe button to pick up the file.then click the Upload button to save.Upload button click event code like followings. {codecitation class="brush: csharp; gutter: true;" width="500px"} protected void btnUpload_Click(object sender, EventArgs e)
{
if (fleUpload.HasFile)
{
string fileName = Path.GetFileName(fleUpload.PostedFile.FileName);
string fileextension = Path.GetExtension(fileName);
if (!Directory.Exists(Server.MapPath("Files")))
{
Directory.CreateDirectory(Server.MapPath("Files"));
}
fleUpload.SaveAs(Path.Combine(Server.MapPath("Files"), fileName));
lblMessage.Text = "The file uploaded to server successfully and you view the file using preview Button;";
Session["ExcelFilePath"] = Path.Combine(Server.MapPath("Files"), fileName);
}
}
{/codecitation}
In above method code lines typical codes. before processed save to server we need verify upload control has file, then we need access posted file properties in file upload control. And here we are creating a folder to save excel files it's called as "Files". in the next line checking folder exist in the server, if doesn't create folder and then save excel sheet with in that.And when we are save th file to server need give this file have header as first row, is it then check the check box.if its sucessfully upload server prince messge to client file has successfully upload to server and click the preview to look stucture.
And finally save the excel sheet server path to preview in the next steps(step 2).Now we have file in the server.Next we need write code to look stucture of the uploaded excel sheet. Within the Preview button click event write code smilar like followings. {codecitation class="brush: csharp; gutter: true;" width="500px"} protected void btnViewFile_Click(object sender, EventArgs e)
{
if (Session["ExcelFilePath"] != null)
{
List collectionOfOrders = ReadExcel(Session["ExcelFilePath"].ToString());
BuilExcelSheetInPage(collectionOfOrders, chkHeader.Checked);
}
else
{
lblMessage.Text = "You haven't uplaod any file to server for the moment";
}
}
{/codecitation} Here we have to supporting method to complete this step and render the preview in the web page.first one is read excel file and second is the Build file structure in html format.first write code for the ReadExcel method. {codecitation class="brush: csharp; gutter: true;" width="500px"} private List ReadExcel(string filePath)
{
List collectionOfData = new List();
Microsoft.Office.Interop.Excel.Worksheet worksheetdetail = null;
ApplicationClass excelApplication = new ApplicationClass();
try
{
Microsoft.Office.Interop.Excel.Workbook workbooks = excelApplication.Workbooks.Open(filePath,
0,
true,
5,
"",
"",
false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
Microsoft.Office.Interop.Excel.Sheets sheets = workbooks.Worksheets;
worksheetdetail = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
int rowindex = 1;
bool IsHeader = true;
do
{
int userColumnCount = 0;
string[] formatedColelction = null;
if (IsHeader)
{
Microsoft.Office.Interop.Excel.Range excelCell2 = (Microsoft.Office.Interop.Excel.Range)worksheetdetail.UsedRange;
System.Array myvalues = (System.Array)excelCell2.Cells.Value2;
userColumnCount = excelCell2.Columns.Count;
formatedColelction = ConvertToStringArray(myvalues, excelCell2.Columns.Count);
collectionOfData.Add(formatedColelction);
IsHeader = false;
}
else
{
Microsoft.Office.Interop.Excel.Range excelCell2 = (Microsoft.Office.Interop.Excel.Range)worksheetdetail.UsedRange;
userColumnCount = excelCell2.Columns.Count;
formatedColelction = new string[userColumnCount];
int arrarIndexPositon = 0;
for (int i = 1; i <= userColumnCount; i++)
{
formatedColelction.SetValue(((Microsoft.Office.Interop.Excel.Range)excelCell2.Cells[rowindex, i]).Value2.ToString(), arrarIndexPositon);
arrarIndexPositon++;
}
collectionOfData.Add(formatedColelction);
}
rowindex++;
} while (((Microsoft.Office.Interop.Excel.Range)worksheetdetail.Cells[rowindex, 1]).Value2 != null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (excelApplication != null)
{
excelApplication.DisplayAlerts = false;
excelApplication.Workbooks.Close();
Marshal.ReleaseComObject(worksheetdetail);
worksheetdetail = null;
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
excelApplication = null;
}
}
return collectionOfData;
}
private string[] ConvertToStringArray(System.Array values, int columncount)
{
// create a new string array
string[] theArray = new string[columncount];
// loop through the 2-D System.Array and populate the 1-D String Array
for (int i = 1; i <= columncount; i++)
{
if (values.GetValue(1, i) == null)
theArray[i - 1] = "";
else
theArray[i - 1] = (string)values.GetValue(1, i).ToString();
}
return theArray;
}
{/codecitation}
Note:When are access Excel COM object in asp.net we may get error like followings. " Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 " Solution :http://blog.crowe.co.nz/archive/2006/03/02/589.aspx Now we have read file and fill in the List object, then we need create HTML Element to render the page. to that implement the second method. {codecitation class="brush: csharp; gutter: true;" width="500px"} private void BuilExcelSheetInPage(List collectionOfData, bool IsFirstRowHeader)
{
if (collectionOfData == null)
{
Response.Write("The excel haven't records");
}
Table mailTable = new Table();
mailTable.Width = new Unit(100, UnitType.Percentage);
mailTable.CellPadding = 0;
mailTable.CellSpacing = 0;
mailTable.Style.Add(HtmlTextWriterStyle.BorderColor, "Silver");
mailTable.Style.Add(HtmlTextWriterStyle.BorderStyle, "solid");
mailTable.Style.Add(HtmlTextWriterStyle.BorderWidth, "1px");
TableCell commonlCellObject = null;
TableRow commonRowObject = null;
int startPosition = 1;
if (IsFirstRowHeader)
{
startPosition = 2;
string[] headerRow = collectionOfData[0];
commonRowObject = new TableRow();
foreach (string item in headerRow)
{
commonlCellObject = new TableCell();
commonlCellObject.Text = item;
commonlCellObject.Style.Add(HtmlTextWriterStyle.Color, "Green");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BackgroundColor, "Yellow");
commonlCellObject.Style.Add(HtmlTextWriterStyle.FontFamily, "Thoma");
commonlCellObject.Style.Add(HtmlTextWriterStyle.FontWeight, "Bold");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderColor, "Silver");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderStyle, "solid");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderWidth, "1px");
commonRowObject.Cells.Add(commonlCellObject);
}
mailTable.Rows.Add(commonRowObject);
}
for (int i = startPosition; i < collectionOfData.Count; i++)
{
string[] datacollection = collectionOfData[i];
commonRowObject = new TableRow();
foreach (string item in datacollection)
{
commonlCellObject = new TableCell();
commonlCellObject.Text = item;
commonlCellObject.Style.Add(HtmlTextWriterStyle.FontFamily, "Thoma");
commonlCellObject.Style.Add(HtmlTextWriterStyle.FontWeight, "normal");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderColor, "Silver");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderStyle, "solid");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderWidth, "1px");
commonRowObject.Cells.Add(commonlCellObject);
}
mailTable.Rows.Add(commonRowObject);
}
placeHolderExcel.Controls.Add(mailTable);
}
{/codecitation}
Above method create html table,row and cells dynamically and build excel sheet file format in html way then add to place holder to rende the in the web page. Complete Code {codecitation class="brush: csharp; gutter: true;" width="650px"} //Code By RRaveen (vrrave[at]codegain.com)
//Offical site: http://codegain.com
//This program is free software: you can redistribute it and/or modify
//it under the terms of the GNU Lesser General Public License as published by
//the Free Software Foundation, either version 3 of the License, or
//(at your option) any later version.
//This program is distributed in the hope that it will be useful,
//but WITHOUT ANY WARRANTY; without even the implied warranty of
//MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
//GNU Lesser General Public License for more details.
//You should have received a copy of the GNU Lesser General Public License
//along with this program. If not, see .
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
public partial class Convertors_ExcelSheetViewer : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
//http://blog.crowe.co.nz/archive/2006/03/02/589.aspx
private void BuilExcelSheetInPage(List collectionOfData, bool IsFirstRowHeader)
{
if (collectionOfData == null)
{
Response.Write("The excel haven't records");
}
Table mailTable = new Table();
mailTable.Width = new Unit(100, UnitType.Percentage);
mailTable.CellPadding = 0;
mailTable.CellSpacing = 0;
mailTable.Style.Add(HtmlTextWriterStyle.BorderColor, "Silver");
mailTable.Style.Add(HtmlTextWriterStyle.BorderStyle, "solid");
mailTable.Style.Add(HtmlTextWriterStyle.BorderWidth, "1px");
TableCell commonlCellObject = null;
TableRow commonRowObject = null;
int startPosition = 1;
if (IsFirstRowHeader)
{
startPosition = 2;
string[] headerRow = collectionOfData[0];
commonRowObject = new TableRow();
foreach (string item in headerRow)
{
commonlCellObject = new TableCell();
commonlCellObject.Text = item;
commonlCellObject.Style.Add(HtmlTextWriterStyle.Color, "Green");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BackgroundColor, "Yellow");
commonlCellObject.Style.Add(HtmlTextWriterStyle.FontFamily, "Thoma");
commonlCellObject.Style.Add(HtmlTextWriterStyle.FontWeight, "Bold");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderColor, "Silver");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderStyle, "solid");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderWidth, "1px");
commonRowObject.Cells.Add(commonlCellObject);
}
mailTable.Rows.Add(commonRowObject);
}
for (int i = startPosition; i < collectionOfData.Count; i++)
{
string[] datacollection = collectionOfData[i];
commonRowObject = new TableRow();
foreach (string item in datacollection)
{
commonlCellObject = new TableCell();
commonlCellObject.Text = item;
commonlCellObject.Style.Add(HtmlTextWriterStyle.FontFamily, "Thoma");
commonlCellObject.Style.Add(HtmlTextWriterStyle.FontWeight, "normal");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderColor, "Silver");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderStyle, "solid");
commonlCellObject.Style.Add(HtmlTextWriterStyle.BorderWidth, "1px");
commonRowObject.Cells.Add(commonlCellObject);
}
mailTable.Rows.Add(commonRowObject);
}
placeHolderExcel.Controls.Add(mailTable);
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (fleUpload.HasFile)
{
string fileName = Path.GetFileName(fleUpload.PostedFile.FileName);
string fileextension = Path.GetExtension(fileName);
if (!Directory.Exists(Server.MapPath("Files")))
{
Directory.CreateDirectory(Server.MapPath("Files"));
}
fleUpload.SaveAs(Path.Combine(Server.MapPath("Files"), fileName));
lblMessage.Text = "The file uploaded to server successfully and you view the file using preview Button;";
Session["ExcelFilePath"] = Path.Combine(Server.MapPath("Files"), fileName);
}
}
protected void btnViewFile_Click(object sender, EventArgs e)
{
if (Session["ExcelFilePath"] != null)
{
List collectionOfOrders = ReadExcel(Session["ExcelFilePath"].ToString());
BuilExcelSheetInPage(collectionOfOrders, chkHeader.Checked);
}
else
{
lblMessage.Text = "You haven't uplaod any file to server for the moment";
}
}
private List ReadExcel(string filePath)
{
List collectionOfData = new List();
Microsoft.Office.Interop.Excel.Worksheet worksheetdetail = null;
ApplicationClass excelApplication = new ApplicationClass();
try
{
Microsoft.Office.Interop.Excel.Workbook workbooks = excelApplication.Workbooks.Open(filePath,
0,
true,
5,
"",
"",
false,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
Microsoft.Office.Interop.Excel.Sheets sheets = workbooks.Worksheets;
worksheetdetail = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);
int rowindex = 1;
bool IsHeader = true;
do
{
int userColumnCount = 0;
string[] formatedColelction = null;
if (IsHeader)
{
Microsoft.Office.Interop.Excel.Range excelCell2 = (Microsoft.Office.Interop.Excel.Range)worksheetdetail.UsedRange;
System.Array myvalues = (System.Array)excelCell2.Cells.Value2;
userColumnCount = excelCell2.Columns.Count;
formatedColelction = ConvertToStringArray(myvalues, excelCell2.Columns.Count);
collectionOfData.Add(formatedColelction);
IsHeader = false;
}
else
{
Microsoft.Office.Interop.Excel.Range excelCell2 = (Microsoft.Office.Interop.Excel.Range)worksheetdetail.UsedRange;
userColumnCount = excelCell2.Columns.Count;
formatedColelction = new string[userColumnCount];
int arrarIndexPositon = 0;
for (int i = 1; i <= userColumnCount; i++)
{
formatedColelction.SetValue(((Microsoft.Office.Interop.Excel.Range)excelCell2.Cells[rowindex, i]).Value2.ToString(), arrarIndexPositon);
arrarIndexPositon++;
}
collectionOfData.Add(formatedColelction);
}
rowindex++;
} while (((Microsoft.Office.Interop.Excel.Range)worksheetdetail.Cells[rowindex, 1]).Value2 != null);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (excelApplication != null)
{
excelApplication.DisplayAlerts = false;
excelApplication.Workbooks.Close();
Marshal.ReleaseComObject(worksheetdetail);
worksheetdetail = null;
excelApplication.Quit();
Marshal.ReleaseComObject(excelApplication);
excelApplication = null;
}
}
return collectionOfData;
}
private string[] ConvertToStringArray(System.Array values, int columncount)
{
// create a new string array
string[] theArray = new string[columncount];
// loop through the 2-D System.Array and populate the 1-D String Array
for (int i = 1; i <= columncount; i++)
{
if (values.GetValue(1, i) == null)
theArray[i - 1] = "";
else
theArray[i - 1] = (string)values.GetValue(1, i).ToString();
}
return theArray;
}
}
{/codecitation}
Everything fine in code level and design level. now build web page and run. then upload a excel sheet to server and click the preview. output look like smilar.
That's all. and it's cool Conclusion I hope this article to help to lots of asp.net developer who are working excel sheet. and this completely using .net framework 3.5 and MicroSoft office excel libarary 12.0. Thank you RRaveen
|