Export text along with image into MS Excel file

In order to export the GridView data or any text data to Ms Excel, this article can be read http://www.dotnetfunda.com/articles/article10.aspx

However to export the text along with Image in Ms Excel, above article solution doesn't work. In this article, lets see how to export text along with some image into Ms Excel file.

Pre-requisite

This article uses the COM component that can be referenced in the solution by right clicking and selecting Add Reference ... After that select the COM tab and select the Component name as "Microsoft Excel 11.0 object library".

Exporting the text and Image in MS Excel File

Below is the method (you can call this method in the Page_Load method) in which first I am framing the name of the file to export in the fileName variable. Then I am creating the instance of the Excel.Application object as excelApp. This object takes parameter as an object so you may find that there are many object variables in this method.

Namespace to be used

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

Most of the above namespaces are already placed by default when you create a .aspx page, but you need to explicitely use the System.IO namespace in order to work with Stream objects.

I have first created the workBooks, workBook, workSheets and workSheet object with the help of excelApp. In order to add the image in the Excel sheet, first lets frame the path of the image, I have kept the path of the image in the path variable.

/// <summary>
/// Writes the image in excel using COM.
/// </summary>
private void WriteImageInExcelUsingCom()
{
string fileName = Server.MapPath("~/Sample" + DateTime.Now.Ticks.ToString() + ".xls");
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
try
{
object fleName = fileName;
object missing = System.Reflection.Missing.Value;
 
Microsoft.Office.Interop.Excel.Workbooks workBooks = excelApp.Workbooks;
Microsoft.Office.Interop.Excel._Workbook workBook = workBooks.Add(missing);
Microsoft.Office.Interop.Excel.Sheets workSheets = workBook.Worksheets;
Microsoft.Office.Interop.Excel._Worksheet workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workSheets.get_Item(1);
 
object boolTrue = true;
object boolFalse = false;
 
// write text to a particular cell 
workSheet.Cells[30, 9] = "Some more text"; 
 
// the same can be achieved using excelApp variable also, this will take the default sheet and write the text at the specified cell
excelApp.Cells[30, 10] = "Some Text";
 
// write a hyperlink
workSheet.Hyperlinks.Add(excelApp.Cells[20, 10], "http://www.dotnetfunda.com", Type.Missing, "Go to DNF", "DNF");
 
string path = string.Format("http://localhost:3366{0}", base.ResolveUrl("~/Images/dotnetlogo.gif"));
 
// place an image at a particular position
workSheet.Shapes.AddPicture(path, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 0, 0, 150, 100);
 
// save as this file now
workSheet.SaveAs(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
 
workBook.Close(boolTrue, fleName, boolFalse);
 
if (excelApp != null)
{
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
}
catch (Exception ee)
{
throw;
}
finally
{
excelApp = null;
}
MakeTheFileDownloadable(fileName);
}

In order to place any text, the Cells property can be set using the excelApp object. In the above example, I have placed "Some Text" in the 10th column of the 30th row.

In order to add the hyperlink, workSheet object exposes Hyperlinks collection that has Add method. This method accepts several parameters to specify at which cell the hyperlink should be placed and what would be the url, text and tooltip will be for this.

In order to place the image, workSheet object exposes Shapes collection that has AddPicture method. It accepts several parameter to specify the path (I have hard coded the host name as http:// localhost: 3366 /, you can change this as per your need), left indent, right indent, width, height and few other properties of the image that can be used to place the image at the desired location.

Now, we can save the file using the SaveAs method of the workSheet object. This will create an MS Excel file in the server.

As we are using COM component to generate the MS Excel file, so ensure that you are handling the object created properly. To do that use the Quit method and release the object. 

Downloading the generated Excel File

Below method takes the fileName as input and force the file to be downloadable by the end user. This method uses FileStream and Stream objects to read the content and write it as output stream respectively.

/// <summary>
/// Makes the file downloadable.
/// </summary>
/// <param name="fileName">Name of the file.</param>
private void MakeTheFileDownloadable(string fileName)
{
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
using (FileStream sourceFile = new FileStream(fileName, FileMode.Open))
{
long FileSize;
FileSize = sourceFile.Length;
byte[] getContent = new byte[(int)FileSize];
sourceFile.Read(getContent, 0, (int)getContent.Length);
 
using (Stream outputStream = Response.OutputStream)
{
outputStream.Write(getContent, 0, getContent.Length);
}
}
Response.End();
}

Generally, you may need to delete the exported file from the server, as a separate file will be created for each request for this page. So you can use File.Delete method to do that.

File.Delete(path);

Conclusion

Hope this article will be of use for someone who is looking for exporting the text and image in the MS Excel file. Keep reading and happy learning!

RSS Feed