This post is a continuation of a series. Please look at the end of this post for more information.
I had blogged some post previously for exporting the data to Excel sheet from ASP.NET page. Even though the basic concept is same, each example can be useful for specific situation.
This post concentrates on another aspect of exporting the data to excel with image where the image stored in database or path of the file. In this example, the image will be loaded and exported to the excel file. So, the excel file can be transferred to any place and not required web server (internet) connection to show the image - the image will show without any issue.
Note :
- I am using Northwind database in this example for implementation. So please make sure to have Northwind database and update the configuration in the Web.Config.
- This code uses ApplicationClass class in Microsoft.Office.Interop.Excel namespace. So to implement this code, we must add reference to Microsoft.Office.Interop.Excel assembly in the project.
- As all the image files are exported to excel, the file size of the excel file will be big.
Preparing the Database:
The
Products table in
Northwind database has list of Product details. I am planning to use the same table to show list of products with the image in the grid and to export to excel. But the
Products table does not contain column for storing the
Image. So, I am altering the table to add a new column
ProductImage for storing image.
Below is the script for adding
ProductImage column in
Products table.
alter table Products add ProductImage Image
To update the Image in the table, I added a separate page (
UpdateImage.aspx in the source code) in the project which will accept an image and update in the table.The page contains a textbox (ID :
txtProductID) and a FileUpload (ID :
fuImage) control for getting the
ProductID and the
image of the product. There is a button control on the page (ID :
btnSave) for saving the selected image for the product id entered in the text box.
protected void btnSave_Click(object sender, EventArgs e)
{
if ((fuImage.PostedFile.FileName.Trim().Length > 0) &&
(fuImage.PostedFile != null))
{
byte[] image = new byte[fuImage.PostedFile.ContentLength];
fuImage.PostedFile.InputStream.Read(image, 0, (int)fuImage.PostedFile.ContentLength);
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString);
SqlCommand command = new SqlCommand();
command.CommandText = "Update Products set ProductImage = @Image where ProductID = '" + txtProductID.Text + "'";
command.CommandType = CommandType.Text;
command.Connection = connection;
PrepareSQLParameter(command, "@Image", SqlDbType.Image, image.Length, image);
connection.Open();
int result = command.ExecuteNonQuery();
connection.Close();
txtProductID.Text = "";
}
}
private SqlParameter PrepareSQLParameter(SqlCommand command, string parameterName, SqlDbType parameterType, int parameterLength, object parameterValue)
{
SqlParameter parameter = new SqlParameter(parameterName, parameterType, parameterLength);
parameter.Value = parameterValue;
command.Parameters.Add(parameter);
return parameter;
}By running this code, I have updated some image for each product. So the table contains image for some products. Below is the query output of the
Products table -
Preparing the ASP.NET page:
Now we are ready to show the image in the GridView on the page. Below script and code used for displaying list of products with image in the ASP.NET page. (The code present in
Default.aspx in source code)
<asp:Button ID="btnExport" runat="server" Text="Export" onclick="btnExport_Click" />
<asp:GridView ID="grdViewProducts" runat="server"
AutoGenerateColumns="False" GridLines="None"
AllowPaging="True" PageSize="8"
DataKeyNames="ProductID" Width="100%" CellPadding="4" ForeColor="#333333">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="Product ID" />
<asp:BoundField DataField="ProductName" HeaderText="Product" />
<asp:BoundField DataField="CompanyName" HeaderText="Supplier" />
<asp:BoundField DataField="CategoryName" HeaderText="Category" />
<asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity Per Unit"/>
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" />
<asp:TemplateField HeaderText="Photo">
<ItemStyle Width="10%" HorizontalAlign="Center" />
<ItemTemplate>
<img id="imgPhoto" src='<%# "GetImageHandler.ashx?ProductID=" + Eval("ProductID") %>'
alt="<%# Eval("ProductName") %>" width="125px" height="125px"
title="<%# Eval("ProductName") %>"/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>The Code behind
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
BindGrid();
}
private void BindGrid()
{
grdViewProducts.DataSource = ProductList();
grdViewProducts.DataBind();
}
public IList<ProductView> ProductList()
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand(
"SELECT Top 20 ProductID, ProductName, CompanyName, CategoryName, ProductImage, " +
//"QuantityPerUnit, UnitPrice, 'GetImageHandler.ashx?ProductID=' + CAST(ProductID AS VARCHAR) ProductImagePath " +
"QuantityPerUnit, UnitPrice, ProductImagePath " +
"FROM Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID " +
"JOIN Categories ON Products.CategoryID = Categories.CategoryID " +
"Order by ProductID", connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
IList<ProductView> productViewList = new List<ProductView>();
while (dr.Read())
{
ProductView productView = new ProductView();
productView.ProductID = dr["ProductID"].ToString();
productView.ProductName = dr["ProductName"].ToString();
productView.CompanyName = dr["CompanyName"].ToString();
productView.CategoryName = dr["CategoryName"].ToString();
productView.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
productView.UnitPrice = Convert.ToDouble(dr["UnitPrice"].ToString());
productView.ProductImagePath = dr["ProductImagePath"].ToString();
//productView.ProductImagePath = HttpContext.Current.Request.Url.AbsoluteUri.Substring(0, HttpContext.Current.Request.Url.AbsoluteUri.LastIndexOf("/")) + "/" + dr["ProductImagePath"].ToString();
productView.IsImageAvailable = ((dr["ProductImage"].ToString().Length > 0) ? true : false);
productViewList.Add(productView);
}
return productViewList;
}
}I am using a
GenericHandler (
GetImageHandler.ashx) for getting the image content to assign in the image control.
public class GetImageHandler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString))
{
SqlCommand command = new SqlCommand("Select ProductImage from Products where ProductID = '" + context.Request.QueryString["ProductID"].ToString() + "'", connection);
connection.Open();
SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
if (dr["ProductImage"].ToString().Length > 0)
{
context.Response.BinaryWrite((byte[])dr["ProductImage"]);
}
}
}
}
public bool IsReusable
{
get
{
return false;
}
}
}As shown in the code, I used an Entity class
ProductView for storing Product Information. The code for
ProductView would be:
public class ProductView
{
public string ProductID { get; set; }
public string ProductName { get; set; }
public string CompanyName { get; set; }
public string CategoryName { get; set; }
public string QuantityPerUnit { get; set; }
public double UnitPrice { get; set; }
public string ProductImagePath { get; set; }
public bool IsImageAvailable { get; set; }
}Below figure shows the output of the code execution -
Exporting to Excel sheet with image:
I am using a reusable class which can be used for exporting the data with image to an excel sheet. But only requirement is to provide the input as IList collection for defining the data with image. The c# code for the class will be:
/// <summary>
/// Class to Export the Data to Excel sheet. Input must be a IList colection
/// </summary>
/// <typeparam name="T">Entity</typeparam>
public class ExcelAppExportor<T>
{
/// <summary>
/// Constructor
/// </summary>
public ExcelAppExportor()
{
TemplateFileName = string.Empty;
IsExportIncludesHeader = true;
ExportSheetName = "Export";
}
/// <summary>
/// Holds the Data
/// </summary>
private IList<T> ListData;
/// <summary>
/// Holds the Export Columns
/// </summary>
private IList<ExportColumn> ExportColumns;
/// <summary>
/// Add the export column
/// </summary>
/// <param name="ExportColumn">ExportColumn</param>
public void AddExportColumn(ExportColumn ExportColumn)
{
if (ExportColumns == null) ExportColumns = new List<ExportColumn>();
ExportColumns.Add(ExportColumn);
}
/// <summary>
/// List contains the list of entity object which are export to excel
/// </summary>
public IList<T> BindDataList
{
get { return ListData; }
set { ListData = value; }
}
/// <summary>
/// File Name of the Export output file
/// </summary>
public string ExportFileName { get; set; }
/// <summary>
/// Template File Name - Using Template file to Export
/// </summary>
public string TemplateFileName { get; set; }
/// <summary>
/// Sheet name to Export the data
/// </summary>
public string ExportSheetName { get; set; }
/// <summary>
/// Is the header data needs to be exported
/// </summary>
public bool IsExportIncludesHeader { get; set; }
public void Export()
{
try
{
#region Filling Export Columns
// Check the columns to export is mentioned, if not
if (ExportColumns == null)
{
// Create an entity object. If the list count == 0 ??? - needs to be handled from client
T tEntity = ListData[0];
// Export the columns to export from the property name
ExportColumns = new List<ExportColumn>();
foreach (System.Reflection.PropertyInfo propertyInfo in tEntity.GetType().GetProperties())
ExportColumns.Add(new ExportColumn(propertyInfo.Name, propertyInfo.Name));
}
#endregion
// Create excel application
Application ExcelApp = new ApplicationClass();
Workbook workbook;
Sheets sheets;
Worksheet worksheet;
// Is Export needs to be exported to a Template file
if (TemplateFileName != string.Empty)
{
#region Load the Template file
// Load the work book
workbook = ExcelApp.Workbooks.Open(TemplateFileName, 0, false, 5, "", "", false,
XlPlatform.xlWindows, "", true, false, 0, true, false, false);
sheets = workbook.Sheets;
worksheet = (Worksheet)sheets.get_Item(1); // To avoid unassigned variable error
bool IsWorkSheetFound = false;
//Check is there any worksheet with the name provided. If yes, clear all data inside to fill new data
for (int intSheetIndex = 1; intSheetIndex <= sheets.Count; intSheetIndex++)
{
worksheet = (Worksheet)sheets.get_Item(intSheetIndex);
if (worksheet.Name.ToString().Equals(ExportSheetName))
{
IsWorkSheetFound = true;
break;
}
}
// If No work sheet found, add it at the last
if (!IsWorkSheetFound)
{
worksheet = (Worksheet)workbook.Sheets.Add(
Type.Missing, (Worksheet)sheets.get_Item(sheets.Count),
Type.Missing, Type.Missing);
worksheet.Name = ExportSheetName;
}
#endregion
}
else
{
#region Crate the Template File
// Adding new work book
workbook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
sheets = workbook.Sheets;
worksheet = (Worksheet)sheets.get_Item(1);
worksheet.Name = ExportSheetName;
#endregion
}
int intCol = 0;
#region Populating the Header
bool IsImageColumnPresent = false;
// If the header needs to exported. In templated files, normally we will have the headings
if (IsExportIncludesHeader == true)
{
// Exporting Header
foreach (ExportColumn exportColumn in ExportColumns)
{
Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
range.Select();
range.Value2 = exportColumn.HeaderText.ToString();
if (exportColumn.ValueFormat == "Image")
{
IsImageColumnPresent = true;
range.ColumnWidth = exportColumn.ImageColumnWidth;
}
else
range.Columns.EntireColumn.AutoFit();
range.Font.Bold = true;
}
}
#endregion
string strTempImagePath = HttpContext.Current.Request.PhysicalApplicationPath + Guid.NewGuid() + @"\";
if (IsImageColumnPresent == true)
System.IO.Directory.CreateDirectory(strTempImagePath);
// Exporting Data
foreach (T tEntity in BindDataList)
{
intCol = 0;
foreach (ExportColumn exportColumn in ExportColumns)
{
Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
range.Select();
if (exportColumn.ValueFormat == "Image")
{
string strImagePath = tEntity.GetType().GetProperty(exportColumn.ColumnName.ToString()).GetValue(tEntity, null).ToString();
Image image = null;
if (strImagePath.StartsWith("http"))
image = DownloadImage(strImagePath);
else
image = (strImagePath.Trim().Length > 0) ? (Image)Image.FromFile(strImagePath, true) : null;
if (image != null)
{
strImagePath = strTempImagePath + Guid.NewGuid() + @".jpg";
if (image != null)
image.Save(strImagePath);
range.RowHeight = exportColumn.ImageHeight;
range.ColumnWidth = exportColumn.ImageColumnWidth;
worksheet.Shapes.AddPicture(strImagePath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, ((float)Convert.ToDecimal(range.Left)) + 1, ((float)Convert.ToDecimal(range.Top)) + 1, exportColumn.ImageWidth - 2, exportColumn.ImageHeight - 2);
}
}
else
{
range.Value2 = tEntity.GetType().GetProperty(exportColumn.ColumnName.ToString()).GetValue(tEntity, null).ToString();
range.Columns.EntireColumn.AutoFit();
}
}
}
intCol = 0;
foreach (ExportColumn exportColumn in ExportColumns)
{
Range range = (Range)worksheet.Cells[exportColumn.StartRowIndex++, ((exportColumn.ExcelColumnIndex == 0) ? ++intCol : exportColumn.ExcelColumnIndex)];
if (exportColumn.ValueFormat != string.Empty)
range.Columns.EntireColumn.NumberFormat = exportColumn.ValueFormat;
}
try
{
Range range = (Range)worksheet.Cells[1, 1];
range.Select();
// Save the workbook with saving option
workbook.Close(true, ExportFileName, Type.Missing);
ExcelApp.UserControl = false;
ExcelApp.Quit();
ExcelApp = null;
System.IO.Directory.Delete(strTempImagePath, true);
}
catch (Exception ex)
{
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
Process[] ps = Process.GetProcesses();
foreach (Process p in ps)
{
if (p.ProcessName.ToLower().Equals("excel"))
p.Kill();
}
}
}
/// <summary>
/// Function to download Image from website
/// Method from http://www.digitalcoding.com/Code-Snippets/C-Sharp/C-Code-Snippet-Download-Image-from-URL.html
/// Thanks to digitialcoding.com
/// </summary>
/// <param name="_URL">URL address to download image</param>
/// <returns>Image</returns>
public Image DownloadImage(string _URL)
{
Image _tmpImage = null;
try
{
// Open a connection
System.Net.HttpWebRequest _HttpWebRequest = (System.Net.HttpWebRequest)System.Net.HttpWebRequest.Create(_URL);
_HttpWebRequest.AllowWriteStreamBuffering = true;
// You can also specify additional header values like the user agent or the referer: (Optional)
_HttpWebRequest.UserAgent = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1)";
_HttpWebRequest.Referer = "http://www.google.com/";
// set timeout for 20 seconds (Optional)
_HttpWebRequest.Timeout = 20000;
// Request response:
System.Net.WebResponse _WebResponse = _HttpWebRequest.GetResponse();
// Open data stream:
System.IO.Stream _WebStream = _WebResponse.GetResponseStream();
// convert webstream to image
_tmpImage = Image.FromStream(_WebStream);
// Cleanup
_WebResponse.Close();
_WebResponse.Close();
}
catch (Exception _Exception)
{
// Error
Console.WriteLine("Exception caught in process: {0}", _Exception.ToString());
return null;
}
return _tmpImage;
}
}I have a button in the page which will trigger the exporting functionality. In this event, I have code for getting the data as IList and calling the Export class object by passing the IList collection as input.
protected void btnExport_Click(object sender, EventArgs e)
{
try
{
ExcelAppExportor<ProductView> exportor = new ExcelAppExportor<ProductView>();
exportor.BindDataList = ProductList();
exportor.AddExportColumn(new ExportColumn("ProductID", "Product ID", 1, 1));
exportor.AddExportColumn(new ExportColumn("ProductName", "Product Name", 1, 2));
exportor.AddExportColumn(new ExportColumn("CompanyName", "Company Name", 1, 3));
exportor.AddExportColumn(new ExportColumn("CategoryName", "Category Name", 1, 4));
exportor.AddExportColumn(new ExportColumn("QuantityPerUnit", "Quantity Per Unit", 1, 5));
exportor.AddExportColumn(new ExportColumn("UnitPrice", "Unit Price", 1, 6));
exportor.AddExportColumn(new ExportColumn("IsImageAvailable", "Is Image Available", 1, 7));
exportor.AddExportColumn(new ExportColumn("ProductImagePath", "Image", "Image", 1, 8, 100, 80, 20));
string strExportFileName = Path.Combine(@"C:\Temp", Guid.NewGuid().ToString("N")) + ".xls";
exportor.ExportFileName = strExportFileName;
exportor.IsExportIncludesHeader = true;
exportor.ExportSheetName = "Orders";
exportor.Export();
byte[] ExcelStream = File.ReadAllBytes(strExportFileName);
Context.Response.ClearContent();
Context.Response.ContentType = "application/ms-excel";
Context.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls", "Product List"));
Context.Response.Charset = "";
Context.Response.BinaryWrite(ExcelStream);
Context.Response.End();
}
catch (Exception ex)
{
}
}The code for adding the export column -
/// <summary>
/// Holds the details of te columns to be exported to Excel from IList
/// </summary>
public class ExportColumn
{
/// <summary>
/// Entity object(class) property name, mandatory
/// </summary>
public string ColumnName { get; set; }
/// <summary>
/// What needs to be shows in the Excel sheet for that col, mandatory
/// </summary>
public string HeaderText { get; set; }
/// <summary>
/// The format of the value. To get the format string, from the excel sheet -> Column -> Format Cells -> Number -> Custom -> Take the Type
/// </summary>
public string ValueFormat { get; set; }
/// <summary>
/// From which row the data needs to be exported, for template file it will be useful
/// </summary>
public int StartRowIndex { get; set; }
/// <summary>
/// On which column the data needs to be exports, So it can be any column. Useful for Templated file
/// Needs to assign the Index of the column starting by 1
/// A - 1, B - 2, C - 3 etc., Counting will include the hidden column
/// </summary>
public int ExcelColumnIndex { get; set; }
/// <summary>
/// Height of the Image
/// </summary>
public int ImageHeight { get; set; }
/// <summary>
/// Width of the Image
/// </summary>
public int ImageWidth { get; set; }
/// <summary>
/// Width of the image column
/// </summary>
public int ImageColumnWidth { get; set; }
public ExportColumn()
{
StartRowIndex = 1;
this.ValueFormat = string.Empty;
this.StartRowIndex = 1;
this.ExcelColumnIndex = 0;
}
public ExportColumn(string ColumnName, string HeaderText)
{
this.ColumnName = ColumnName;
this.HeaderText = HeaderText;
this.ValueFormat = string.Empty;
this.StartRowIndex = 1;
this.ExcelColumnIndex = 0;
}
public ExportColumn(string ColumnName, string HeaderText, int StartRowIndex, int ExcelColumnIndex)
{
this.ColumnName = ColumnName;
this.HeaderText = HeaderText;
this.ValueFormat = string.Empty;
this.StartRowIndex = StartRowIndex;
this.ExcelColumnIndex = ExcelColumnIndex;
}
public ExportColumn(string ColumnName, string HeaderText, string ValueFormat)
{
this.ColumnName = ColumnName;
this.HeaderText = HeaderText;
this.ValueFormat = ValueFormat;
this.StartRowIndex = 1;
this.ExcelColumnIndex = 0;
}
public ExportColumn(string ColumnName, string HeaderText, string ValueFormat, int StartRowIndex, int ExcelColumnIndex)
{
this.ColumnName = ColumnName;
this.HeaderText = HeaderText;
this.ValueFormat = ValueFormat;
this.StartRowIndex = StartRowIndex;
this.ExcelColumnIndex = ExcelColumnIndex;
}
public ExportColumn(string ColumnName, string HeaderText, string ValueFormat, int StartRowIndex, int ExcelColumnIndex, int ImageWidth, int ImageHeight, int ImageColumnWidth)
{
this.ColumnName = ColumnName;
this.HeaderText = HeaderText;
this.ValueFormat = ValueFormat;
this.StartRowIndex = StartRowIndex;
this.ExcelColumnIndex = ExcelColumnIndex;
this.ImageHeight = ImageHeight;
this.ImageWidth = ImageWidth;
this.ImageColumnWidth = ImageColumnWidth;
}
}Adding the ExportColumn to ExcelAppExportor object
The
ExportColumn class is used for adding the exporting column name, header text and format of the data. The format of the data should as per the format defined in excel sheet (Select the column in Excel Sheet -> right click the column -> select
Format Cells... -> Define any format -> Select the
Custom -> tab -> Copy the
Type value).
- ExportColumn(string ColumnName, string HeaderText) - Defining column name of the database table/view/query and the column header text on the Excel Sheet.
- ExportColumn(string ColumnName, string HeaderText, int StartRowIndex, int ExcelColumnIndex) - Defining column name, column header text, row index where the data should start to export, column index where the data should start to export.
- ExportColumn(string ColumnName, string HeaderText, string ValueFormat) - Defining column name, column header text, value format of the data.
- ExportColumn(string ColumnName, string HeaderText, string ValueFormat, int StartRowIndex, int ExcelColumnIndex) - Defining column name, column header text, value format of the data, row index where the data should start to export, column index where the data should start to export.
- ExportColumn(string ColumnName, string HeaderText, string ValueFormat, int StartRowIndex, int ExcelColumnIndex, int ImageWidth, int ImageHeight, int ImageColumnWidth) - Defining column name, column header text, value format of the data, row index where the data should start to export, column index where the data should start to export, image width, image height, image column width.
Here, the last
ExportColumn constructor is used for adding the information about the image column.The image url path can be defined in two ways:
- The physical path of the file. - For Ex: D:\ImageExportTest\WebApp\Images\Image01.jpg. So the code assign the physical path as defined below
productView.ProductImagePath = @"D:\ImageExportTest\WebApp\Images\Image01.jpg"
Note: The image files must be readable from the application.
- The http/https url of the path of the file - For Ex: http://localhost/images/world.jpg. So the code assign the value as defined below
productView.ProductImagePath = @"http://localhost/images/world.jpg"
Download the working example in C#
here and in VB
here.
The screen shot of the exported excel sheet for the example defined above:
