protected void lnkBuildExcelReport_Click(object sender, EventArgs e)
{
//---------------------------------------------------------------
//
//---------------------------------------------------------------
long claimId = long.Parse(this.lblClaimId.Text);
//---------------------------------------------------------------
//
//---------------------------------------------------------------
DataSet ds = new DataSet();
//---------------------------------------------------------------
//
//---------------------------------------------------------------
ClaimsBLL objClaimsBLL = new ClaimsBLL();
DataTable dt = objClaimsBLL.SelectByIdwithStyle(_id);
ds.Tables.Add(dt);
ds.Tables[ds.Tables.Count - 1].TableName = "Claim #" + this.lblClaimId.Text;
//---------------------------------------------------------------
//
//---------------------------------------------------------------
Claims_LogBLL objClaims_LogBLL = new Claims_LogBLL();
dt = objClaims_LogBLL.GetDataByClaimId(_id);
ds.Tables.Add(dt);
ds.Tables[ds.Tables.Count - 1].TableName = "Claim Log";
//---------------------------------------------------------------
//write dataset to excel
//---------------------------------------------------------------
DsToExcel objDsToExcel = new DsToExcel();
string path = Server.MapPath("~/Excel") + "\\";
string fileName = "Claim" + _id.ToString() + ".xls";
objDsToExcel.WriteDatasetToExcel(fileName, ds, path);
//---------------------------------------------------------------
//
//---------------------------------------------------------------
Response.Redirect("~/Excel/" + fileName);
}
using System;
using System.IO;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Excel;
using System.Runtime.InteropServices;
///<summary>
/// Summary description for DsToExcel
///</summary>
public class DsToExcel
{
public DsToExcel()
{
//
// TODO: Add constructor logic here
//
}
public void WriteDatasetToExcel(string fileName, DataSet ds, string strCurrentDir)
{
//---------------------------------------------------------------
//
//---------------------------------------------------------------
Application oXL;
_Workbook oWB;
_Worksheet oSheet;
Range oRng;
try
{
oXL = new Application();
oXL.Visible = false;
//Get a new workbook.
oWB = (_Workbook)(oXL.Workbooks.Add(strCurrentDir + "\\template.xls"));
oSheet = (_Worksheet)oWB.ActiveSheet;
//System.Data.DataTable dtGridData=ds.Tables[0];
int iRow = 2;
//---------------------------------------------------------------
//loop thru dataset tables
//---------------------------------------------------------------
for (int i = 0; i < ds.Tables.Count; i++)
{
//---------------------------------------------------------------
//wtite Table Name
//---------------------------------------------------------------
oSheet.Cells[iRow, 1] = ds.Tables[i].TableName;
Excel.Range xlRange = (Excel.Range)oSheet.Cells[iRow, 1];
// Bold the current row.
xlRange.EntireRow.Font.Bold = true;
//---------------------------------------------------------------
//any records?
//---------------------------------------------------------------
iRow++;
if (ds.Tables[i].Rows.Count < 1)
{
//---------------------------------------------------------------
//<no records>
//---------------------------------------------------------------
oSheet.Cells[iRow, 1] = "<no records>";
iRow++;
}
else
{
//---------------------------------------------------------------
//build column names row
//---------------------------------------------------------------
for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
{
oSheet.Cells[iRow, j + 1] = ds.Tables[i].Columns[j].ColumnName;
//oSheet.Cells[iRow, j + 1].Font.FontStyle = System.Drawing.FontStyle.Bold;
xlRange = (Excel.Range) oSheet.Cells[iRow, j + 1];
// Bold the current row.
xlRange.EntireRow.Font.Bold = true;
}
iRow++;
//---------------------------------------------------------------
//build data row
//loop thru dataset table rows
//---------------------------------------------------------------
for (int rowNo = 0; rowNo < ds.Tables[i].Rows.Count; rowNo++)
{
//---------------------------------------------------------------
//loop thru table columns
// print into cells the values of each column.
//---------------------------------------------------------------
for (int colNo = 0; colNo < ds.Tables[i].Columns.Count; colNo++)
{
oSheet.Cells[iRow, colNo + 1] = ds.Tables[i].Rows[rowNo][colNo].ToString();
}
iRow++;
}
}
//---------------------------------------------------------------
//skip a row for next dataset table
//---------------------------------------------------------------
iRow++;
}
//---------------------------------------------------------------
//style the sheet
//---------------------------------------------------------------
oRng = oSheet.get_Range("A1", "IV1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
string duhSaveFile = strCurrentDir + fileName;
//---------------------------------------------------------------
//delete older file version
//---------------------------------------------------------------
try
{
File.Delete(duhSaveFile);
}
catch { }
//---------------------------------------------------------------
//save newer file version
//---------------------------------------------------------------
oWB.SaveAs(duhSaveFile, XlFileFormat.xlWorkbookNormal, null, null, false, false, XlSaveAsAccessMode.xlShared, false, false, null, null, null);
// Need all following code to clean up and remove all references!!!
oWB.Close(null, null, null);
oXL.Workbooks.Close();
oXL.Quit();
//---------------------------------------------------------------
//ReleaseComObjects
//---------------------------------------------------------------
Marshal.ReleaseComObject(oRng);
Marshal.ReleaseComObject(oXL);
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
}
catch (Exception theException)
{
System.Diagnostics.Debug.WriteLine(theException.Message);
}
}
}