You can create Excel file using
- OpenXML
- Microsoft.Office.Interop.Excel
1. OpenXML
For more information about OpenXML
http://excelpackage.codeplex.com/
- For this you can create One Template file and One Source file.
Here I create ErrorListtemplate.xlsx (Template file) and ErrorList.xlsx
- You must add
- ExcelPackage.dll
- ExcelPackage.pdb
- ExcelPackageXmlDocumentationFile.xml
- GacReg.bat
Your .aspx file like:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="OpenXml.aspx.cs" Inherits="OpenXml" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btn_Excel" runat="server" Text="Excel"
onclick="btn_Excel_Click" />
</div>
</form>
</body>
</html>
using
System;
using
System.Collections.Generic;
using
System.Linq;
using System.Web;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Data;
using
System.Reflection;
using System.IO;
using
OfficeOpenXml;
public partial class OpenXml : System.Web.UI.Page
{
DataTable Dt =
new DataTable();
object[] query;
protected void
Page_Load(object sender,
EventArgs e)
{
if (!IsPostBack)
{
}
}
private void
GetRecoredForExcelfile()
{
using (OpenXmlDataDataContext
db = new
OpenXmlDataDataContext())
{
var info =
from p in db.userinfos
select p;
if (info !=
null)
{
query = info.ToArray();
Dt = ConvertToDatatable(query);
}
}
}
/// <summary>
/// Convert
Object Array to DataTable
/// </summary>
/// <param name="array"></param>
/// <returns></returns>
public
static DataTable ConvertToDatatable(Object[]
array)
{
PropertyInfo[] properties =
array.GetType().GetElementType().GetProperties();
DataTable dt =
CreateDataTable(properties);
if (array.Length != 0)
{
foreach (object
o in array)
FillData(properties, dt, o);
}
return dt;
}
#region Private
Methods
/// <summary>
/// Creates
total column of datatable.
/// </summary>
/// <param name="properties"></param>
/// <returns></returns>
private
static DataTable CreateDataTable(PropertyInfo[]
properties)
{
DataTable dt =
new DataTable();
DataColumn dc =
null;
foreach (PropertyInfo
pi in properties)
{
dc = new
DataColumn();
dc.ColumnName = pi.Name;
dt.Columns.Add(dc);
}
return dt;
}
/// <summary>
/// Fills
data in Datatable
/// </summary>
/// <param name="properties"></param>
/// <param name="dt"></param>
private
static void FillData(PropertyInfo[]
properties, DataTable dt,
Object o)
{
DataRow dr = dt.NewRow();
foreach (PropertyInfo
pi in properties)
{
dr[pi.Name] = pi.GetValue(o, null);
}
dt.Rows.Add(dr);
}
#endregion
protected void
btn_Excel_Click(object sender,
EventArgs e)
{
GetRecoredForExcelfile();
string newFilePath = Server.MapPath("ExcelFile/ErrorList.xlsx");
string templateFilePath =
Server.MapPath("ExcelFile/ErrorListtemplate.xlsx");
FileInfo newFile =
new FileInfo(newFilePath);
FileInfo template =
new FileInfo(templateFilePath);
using (ExcelPackage
xlPackage = new
ExcelPackage(newFile, template))
{
foreach (ExcelWorksheet
aworksheet in xlPackage.Workbook.Worksheets)
{
aworksheet.Cell(1, 1).Value = aworksheet.Cell(1, 1).Value;
}
ExcelWorksheet worksheet =
xlPackage.Workbook.Worksheets["Sheet1"];
int startrow = 5;
int row = 0;
int col = 0;
for
(int j = 0; j < Dt.Columns.Count; j++)
{
col++;
for (int
i = 0; i < Dt.Rows.Count; i++)
{
row = startrow + i;
ExcelCell cell =
worksheet.Cell(row, col);
cell.Value = Dt.Rows[i][j].ToString();
xlPackage.Save();
}
}
}
}
}
2. Microsoft.Office.Interop.Excel
You must Add reference Microsoft Excel 12.0 Object Library from .NET COM .
Your .aspx code like:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MicrosoftOfficeIntrupt.aspx.cs" Inherits="MicrosoftOfficeIntrupt" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btn_Excel" runat="server" Text="Excel"
onclick="btn_Excel_Click" />
</div>
</form>
</body>
</html>
Your .cs file like:
using
System;
using
System.Collections.Generic;
using
System.Linq;
using System.Web;
using
System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Data;
using
System.Reflection;
using System.IO;
using
Microsoft.Office.Interop.Excel;
public partial class MicrosoftOfficeIntrupt : System.Web.UI.Page
{
System.Data.DataTable dtCustmer =
new System.Data.DataTable();
object[] query;
protected void
Page_Load(object sender,
EventArgs e)
{
if (!IsPostBack)
{
}
}
private void GetRecoredForExcelfile()
{
using (OpenXmlDataDataContext
db = new
OpenXmlDataDataContext())
{
var info =
from p in db.userinfos
select p;
if (info != null)
{
query = info.ToArray();
dtCustmer = ConvertToDatatable(query);
//Session["dtlist"] =Dt;
}
}
}
/// <summary>
/// Convert
Object Array to DataTable
/// </summary>
/// <param name="array"></param>
/// <returns></returns>
public
static System.Data.DataTable
ConvertToDatatable(Object[] array)
{
PropertyInfo[] properties =
array.GetType().GetElementType().GetProperties();
System.Data.DataTable dt =
CreateDataTable(properties);
if (array.Length != 0)
{
foreach (object
o in array)
FillData(properties, dt, o);
}
return dt;
}
#region
Private Methods
/// <summary>
/// Creates
total column of datatable.
/// </summary>
/// <param name="properties"></param>
/// <returns></returns>
private
static System.Data.DataTable
CreateDataTable(PropertyInfo[] properties)
{
System.Data.DataTable dt =
new System.Data.DataTable();
DataColumn dc =
null;
foreach (PropertyInfo
pi in properties)
{
dc = new
DataColumn();
dc.ColumnName = pi.Name;
//dc.DataType = pi.PropertyType;
dt.Columns.Add(dc);
}
return dt;
}
/// <summary>
/// Fills
data in Datatable
/// </summary>
/// <param name="properties"></param>
/// <param name="dt"></param>
private
static void FillData(PropertyInfo[]
properties, System.Data.DataTable dt,
Object o)
{
DataRow dr = dt.NewRow();
foreach (PropertyInfo
pi in properties)
{
dr[pi.Name] = pi.GetValue(o, null);
}
dt.Rows.Add(dr);
}
#endregion
protected void
btn_Excel_Click(object sender,
EventArgs e)
{
GetRecoredForExcelfile();
string newFilePath = Server.MapPath("ExcelFile/OfficeErrorList.xlsx");
ApplicationClass objExcel =
null;
Workbooks objBooks =
null;
_Workbook objBook =
null;
Sheets objSheets =
null;
_Worksheet objSheet =
null;
Range objRange =
null;
int row = 1, col = 1;
try
{
// System.Data.DataTable dtCustmer =
GetAllCustomers();
//System.Data.DataTable
dtCustmer = Dt.Clone();
objExcel = new ApplicationClass();
objBooks = objExcel.Workbooks;
objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);
//Print column heading in the
excel sheet
int j = col;
foreach (DataColumn
column in dtCustmer.Columns)
{
objSheets = objBook.Worksheets;
objSheet = (_Worksheet)objSheets.get_Item(1);
objRange = (Range)objSheet.Cells[row,
j];
objRange.Value2 = column.ColumnName;
// objRange.Font.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
//objRange.Interior.Color
= System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Maroon);
j++;
}
row++;
int count = dtCustmer.Columns.Count;
foreach (DataRow dataRow
in dtCustmer.Rows)
{
int k = col;
for (int
i = 0; i < count; i++)
{
objRange = (Range)objSheet.Cells[row,
k];
objRange.Value2 = dataRow[i].ToString();
k++;
}
row++;
}
//Save Excel document
objSheet.Name =
"Sample Sheet";
object objOpt = Missing.Value;
objBook.SaveAs(newFilePath, objOpt, objOpt, objOpt,
objOpt, objOpt, XlSaveAsAccessMode.xlNoChange,
objOpt, objOpt, objOpt, objOpt, objOpt);
objBook.Close(false,
objOpt, objOpt);
}
catch
{
}
finally
{
objExcel = null;
objBooks = null;
objBook = null;
objSheets = null;
objSheet = null;
objRange = null;
ReleaseComObject(objExcel);
ReleaseComObject(objBooks);
ReleaseComObject(objBook);
ReleaseComObject(objSheets);
ReleaseComObject(objSheet);
ReleaseComObject(objRange);
}
}
//Release COM objects from memory
public void
ReleaseComObject(object reference)
{
try
{
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(reference)
<= 0)
{
}
}
catch
{
}
}
}
If you have some Error like
Exception from HRESULT: 0x800A03EC - Excel, .Net, SQL and Windows Server
Then you have to give iis or server permission
-
Login to the server as a administrator.
-
Go to "Start" -> "Run" and enter "taskmgr"
-
Go to the process tab in task manager and check "Show Processes from all
users"
-
If there are any "Excel.exe" entries on the list, right click on the
entry and select "End Process"
-
Close task manager.
-
Go to "Start" -> "Run" and enter "services.msc"
-
Stop the service automating Excel if it is running.
-
Go to "Start" -> "Run" and enter "dcomcnfg"
-
This will bring up the component services window, expand out "Console
Root" -> "Computers" -> "DCOM Config"
-
Find "Microsoft Excel Application" in the list of components.
-
Right click on the entry and select "Properties"
-
Go to the "Identity" tab on the properties dialog.
-
Select "The interactive user."
-
Click the "OK" button.
-
Switch to the services console
-
Start the service automating Excel
-
Test you application again.
For more information
http://www.hagrin.com/319/exception-hresult-0x800a03ec-excel-net-sql-and-windows-server-2008
And Add in configuration
<identity
impersonate="true" userName="yourusername" password="yourpassword"/>
You must add assembly in your web.config file.
<compilation
debug="true" targetFramework="4.0">
<assemblies>
<add
assembly="microsoft.office.interop.excel,
version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c"/>
<add
assembly="DocumentFormat.OpenXml,
Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
</assemblies>
</compilation>