Hello all,
In this article, I will explain how to write data into an Excel template.
Step 1. First, Create one Page with one button.
Aspx Page
<asp:Button Text="Excel" ID="btnExcel" OnClick="btnExcel_Click" CssClass="btn btn-success button-62" runat="server" />
Step 2. With the click of this button, we will write the data into an Excel file.
Code Behind
protected void btnExcel_Click(object sender, EventArgs e)
{
string[] sheetName = { "Sheet1" }; //Name of the sheet
strQuery = @"select COl1,COl2,COl3,COl4 from yourTable"; // this is the query of table
DataSet DS = dbContext.GetDataSet(strQuery); //here we are getting dataset values
if (DS.Tables[0].Rows.Count > 0) //here we check dataset count
{
//This function is used to write the data into template
csExcel.XlsGenerateByTemplete_OnExistingTemplate(sheetName, "ReportName.xls", "TemplateName.xls", DS, "1", 1);
//This function is used to open the file from location in pop-up format
FileInfo file = new FileInfo(Server.MapPath(@"~\Excel\ReportName.xls"));
if (file.Exists)
{
Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=ReportName.xls");
Response.AddHeader("Content-Type", "application/Excel");
Response.ContentType = "application/vnd.xls";
Response.AddHeader("Content-Length", file.Length.ToString());
Response.WriteFile(file.FullName);
}
else { }
}
else
{
// display the message you want to...
}
}
Step 3. Calling the Excel write function. Create a separate class file.
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
using System.Text;
using System.Web;
using System.Web.UI;
using Excel = Microsoft.Office.Interop.Excel;
public class Utility
{
Excel.Application ObjExcel;
Excel._Workbook ObjBook;
Excel._Worksheet ObjSheet;
public bool XlsGenerateByTemplete_OnExistingTemplate(string[] Sheetname, string TempleteName, string ReportName, DataSet ObjDset, string first, int sheetnumber)
{
Int32 RowID = 1, ColumnID = 0;
// Report Store Path
string workbookPath = System.Web.HttpContext.Current.Server.MapPath(@"~\Template\" + TempleteName);
string FileStorePath = System.Web.HttpContext.Current.Server.MapPath(@"~\Excel\" + ReportName);
if (File.Exists(FileStorePath))
{
if (first == "1")
{
File.Delete(FileStorePath);
File.Copy(workbookPath, FileStorePath);
}
else
{
}
}
else
{
File.Copy(workbookPath, FileStorePath);
}
try
{
//Start Excel and get Application object.
ObjExcel = new Excel.ApplicationClass();
//Get a new workbook.
//ObjBook = (Excel.Workbook)ObjExcel.Workbooks.Open(FileStorePath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
ObjExcel = new Excel.ApplicationClass();
ObjBook = (Excel.Workbook)ObjExcel.Workbooks.Add(FileStorePath);
ObjSheet = (Excel.Worksheet)ObjBook.Sheets[1];
for (int i = 0; i < ObjDset.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < ObjDset.Tables[0].Columns.Count; j++)
{
//RowID = Convert.ToInt32(ObjDset.Tables[0].Rows[i][0].ToString());
//ColumnID = Convert.ToInt32(ObjDset.Tables[0].Rows[i][1].ToString());
RowID = i + 2;
ColumnID = j + 1;
ObjSheet.Cells[RowID, ColumnID] = Convert.ToString(ObjDset.Tables[0].Rows[i][j]);
}
}
ObjBook.SaveCopyAs(FileStorePath);
return true;
}
catch (Exception ex)
{
ex.Message.ToString();
return false;
}
finally
{
RemoveProcess_Instance(ObjExcel);
releaseObject(ObjSheet);
releaseObject(ObjBook);
releaseObject(ObjExcel);
}
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
Console.WriteLine("Exception Occured while releasing object " + ex.ToString());
}
finally { GC.Collect(); }
}
[DllImport("user32.dll")]
private static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);
public void RemoveProcess_Instance(Excel.Application objExcel)
{
GC.Collect();
GC.WaitForPendingFinalizers();
if (objExcel != null)
{
objExcel.Quit();
int hWnd = objExcel.Application.Hwnd;
uint processID; GetWindowThreadProcessId((IntPtr)hWnd, out processID);
System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName("EXCEL");
foreach (System.Diagnostics.Process p in procs)
{
if (p.Id == processID)
p.Kill();
}
Marshal.FinalReleaseComObject(objExcel);
}
}
}
// end of code
Please note. You have to add the.dll files
microsoft.office.interop.excel.dll
microsoft.vbe.interop.dll
office.dll
Also, please create one .xls template and store it in the Template folder and create an Excel folder for report storage.