Create excel worksheet from .net with freez column / rows ,Group Column /rows and use of formula and formating cells.

//First add refrence to ms excel 10.0 library in ur toolbar.
Testvikram.aspx

_/%@ Page Language="C#" AutoEventWireup="true" CodeFile="Testvikram.aspx.cs" Inherits="Testvikram" %_/

_/!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_/Untitled Page_//title_/
_//head_/
_/body_/
_/form id="form1" runat="server"_/
_/div_/
_/fieldset_/

_/legend_/DataToExcel_//legend_/
_/asp:Button id="Button1" runat="server"
Text="ToExcel" OnClick="Button1_Click"_/_//asp:Button_/
Save as filename:_/asp:TextBox ID="TextBox1" runat="server"_/_//asp:TextBox_/
_/asp:datagrid id="DataGrid1" runat="server" width="100%"_/
_/ItemStyle HorizontalAlign="Center"_/_//ItemStyle_/
_/HeaderStyle HorizontalAlign="Center"_/_//HeaderStyle_/
_/FooterStyle HorizontalAlign="Center"_/_//FooterStyle_/
_/PagerStyle PageButtonCount="15" Mode="NumericPages"_/_//PagerStyle_/
_//asp:datagrid_/
_//fieldset_/

_//div_/
_//form_/
_//body_/
_//html_/


Testvikram.aspx.cs


using System;
using System.Data;
using System.Configuration;
using System.Collections;
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 CrmSdk;
using System.Runtime.InteropServices;
using Excel;// = Microsoft.Office.Interop.Excel;
using System.Reflection;

public partial class Testvikram : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}

protected void Button1_Click(object sender, EventArgs e)
{
System.Data.DataTable dt = new System.Data.DataTable();
DataColumn dc1 = new DataColumn("FName");
DataColumn dc2 = new DataColumn("LName");
DataColumn dc3 = new DataColumn("Name");
DataColumn dc4 = new DataColumn("CName");
DataColumn dc5 = new DataColumn("Topic");
DataColumn dc6 = new DataColumn("Salary");
DataColumn dc7 = new DataColumn("PT");
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc3);
dt.Columns.Add(dc4);
dt.Columns.Add(dc5);
dt.Columns.Add(dc6);
dt.Columns.Add(dc7);
for (int i = 0; i _/ 10; i++)
{
DataRow dr = dt.NewRow();
dr["FName"] = "Vikram";
dr["LName"] = "Tupe";
dr["Name"] = "Vikram A Tupe";
dr["CName"] = "Sysprotech";
dr["Topic"] = "HI testing app";
dr["Salary"] = "1000";
dr["PT"] = "800";
dt.Rows.Add(dr);
}

//First worksheet first table

Excel.Application xlsApp = new Excel.ApplicationClass();
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
xlWorkBook = xlsApp.Workbooks.Add(Missing.Value);



xlWorkBook.Application.Visible = true;
xlWorkSheet = (Excel.Worksheet)(xlWorkBook.Worksheets[1]);

xlWorkSheet.Name = DateTime.Now.Year.ToString() + " FirstWorksheet";

// Activate current worksheet
xlWorkSheet.Activate();

DataSet ds = new DataSet();
ds.Tables.Add(dt);
int j = 2;
xlWorkSheet.Cells[1, 1] = "First Name";
xlWorkSheet.Cells[1, 2] = "Last Name";
xlWorkSheet.Cells[1, 3] = "Full Name";
xlWorkSheet.Cells[1, 4] = "Company Name";
xlWorkSheet.Cells[1, 5] = "Topic";
xlWorkSheet.Cells[1, 6] = "Salary";
xlWorkSheet.Cells[1, 7] = "PT";
xlWorkSheet.Cells[1, 8] = "Total";
xlWorkSheet.Cells[1, 9] = "Full Name";



xlWorkSheet.get_Range("A1", "I1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "I1").Locked = true;
xlWorkSheet.get_Range("A1", "I1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;


Excel.Range oResizeRange = xlWorkSheet.get_Range("A1", "I1");

oResizeRange.Orientation = 38;
oResizeRange.Locked = true;
oResizeRange.Borders.Weight = Excel.XlBorderWeight.xlThick;

// oResizeRange.Orientation = 0;
oResizeRange.WrapText = true;
oResizeRange.Borders.Weight = 2;
oResizeRange.Interior.ColorIndex = 36;


foreach (DataRow dr in ds.Tables[0].Rows)
{
xlWorkSheet.Cells[j, 1] = dr[0];
xlWorkSheet.Cells[j, 2] = dr[1];
xlWorkSheet.Cells[j, 3] = dr[2];
xlWorkSheet.Cells[j, 4] = dr[3];
xlWorkSheet.Cells[j, 5] = dr[4];
xlWorkSheet.Cells[j, 6] = dr[5];
xlWorkSheet.Cells[j, 7] = dr[6];

// Excel.Range oRng;
//oRng = xlWorkSheet.get_Range("F2", "F4");//works
//oRng.Formula = "=RAND()*100000";
//oRng.NumberFormat = "$0.00";


Excel.Range oRng1;
oRng1 = xlWorkSheet.get_Range("I2", "I11");
oRng1.Formula = "=A2 & \" \" & B2";

oRng1 = xlWorkSheet.get_Range("H2", "H11");
oRng1.Formula = "=SUM(F2:G2)";

j = j + 1;
}

xlWorkSheet.Cells[j, 5] = "Total";
string st = "F" + j.ToString();


Excel.Range oRng2 = xlWorkSheet.get_Range(st, st);
oRng2.Formula = "=SUM(F2:F11)";
oRng2.Interior.ColorIndex = 3;


xlWorkSheet.get_Range("A2", "I2").Select();
xlsApp.ActiveWindow.FreezePanes = true;

Excel.Range currentCell = xlWorkSheet.get_Range(string.Format("A{0}", j), string.Format("I{0}", j));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Excel.Range currentCell0 = xlWorkSheet.get_Range(string.Format("A{0}", j), Missing.Value);
currentCell0.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Excel.Range currentCell5 = xlWorkSheet.get_Range(string.Format("C{0}", j), string.Format("E{0}", j));
currentCell5.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Excel.Range currentCell6 = xlWorkSheet.get_Range(string.Format("G{0}", j), Missing.Value);
currentCell6.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

xlWorkSheet.Columns.AutoFit();


//First worksheet Second table

int m = 2;
xlWorkSheet.Cells[1, 11] = "First Name";
xlWorkSheet.Cells[1, 12] = "Last Name";
xlWorkSheet.Cells[1, 13] = "Full Name";
xlWorkSheet.Cells[1, 14] = "Company Name";
xlWorkSheet.Cells[1, 15] = "Topic";
xlWorkSheet.Cells[1, 16] = "Salary";
xlWorkSheet.Cells[1, 17] = "PT";
xlWorkSheet.Cells[1, 18] = "Total";
xlWorkSheet.Cells[1, 19] = "Full Name";



xlWorkSheet.get_Range("K1", "S1").Font.Bold = true;
xlWorkSheet.get_Range("K1", "S1").Locked = true;
xlWorkSheet.get_Range("K1", "S1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;


Excel.Range oResizeRangenew = xlWorkSheet.get_Range("K1", "S1");

//oResizeRangenew.Orientation = 38;
oResizeRangenew.Locked = true;
oResizeRangenew.Borders.Weight = Excel.XlBorderWeight.xlThick;

oResizeRangenew.Orientation = 0;
oResizeRangenew.WrapText = true;
oResizeRangenew.Borders.Weight = 2;
oResizeRangenew.Interior.ColorIndex = 36;


foreach (DataRow dr in ds.Tables[0].Rows)
{
xlWorkSheet.Cells[m, 11] = dr[0];
xlWorkSheet.Cells[m, 12] = dr[1];
xlWorkSheet.Cells[m, 13] = dr[2];
xlWorkSheet.Cells[m, 14] = dr[3];
xlWorkSheet.Cells[m, 15] = dr[4];
xlWorkSheet.Cells[m, 16] = dr[5];
xlWorkSheet.Cells[m, 17] = dr[6];


Excel.Range oRng111;
oRng111 = xlWorkSheet.get_Range("S2", "S11");
oRng111.Formula = "=K2 & \" \" & L2";

oRng111 = xlWorkSheet.get_Range("R2", "R11");
oRng111.Formula = "=SUM(P2:Q2)";

m = m + 1;
}

xlWorkSheet.Cells[m, 5] = "Total";
string st1 = "P" + m.ToString();

Excel.Range oRng9 = xlWorkSheet.get_Range(st1, st1);
oRng9.Formula = "=SUM(P2:P11)";
oRng9.Interior.ColorIndex = 3;


xlWorkSheet.get_Range("K2", "S2").Select();
xlsApp.ActiveWindow.FreezePanes = true;

Excel.Range currentCell45 = xlWorkSheet.get_Range(string.Format("K{0}", m), string.Format("S{0}", m));
currentCell45.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Excel.Range currentCell46 = xlWorkSheet.get_Range(string.Format("K{0}", m), Missing.Value);
currentCell46.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Excel.Range currentCell47 = xlWorkSheet.get_Range(string.Format("M{0}", m), string.Format("O{0}", m));
currentCell47.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Excel.Range currentCell48 = xlWorkSheet.get_Range(string.Format("R{0}", m), Missing.Value);
currentCell48.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

xlWorkSheet.Columns.AutoFit();



//First worksheet Third table


int z = 14;

foreach (DataRow dr in ds.Tables[0].Rows)
{
xlWorkSheet.Cells[z, 1] = dr[0];
xlWorkSheet.Cells[z, 2] = dr[1];
xlWorkSheet.Cells[z, 3] = dr[2];
xlWorkSheet.Cells[z, 4] = dr[3];
xlWorkSheet.Cells[z, 5] = dr[4];
xlWorkSheet.Cells[z, 6] = dr[5];
xlWorkSheet.Cells[z, 7] = dr[6];


// Fill C2:C6 with a relative formula (=A2 & " " & B2).
Excel.Range oRng14;
oRng14 = xlWorkSheet.get_Range("I13", "I22");
oRng14.Formula = "=A13 & \" \" & B13";

oRng14 = xlWorkSheet.get_Range("H13", "H22");
oRng14.Formula = "=SUM(F13:G13)";

z = z + 1;
}

xlWorkSheet.Cells[z, 5] = "Total";
string st2 = "F" + z.ToString();

Excel.Range oRng5 = xlWorkSheet.get_Range(st2, st2);
oRng5.Formula = "=SUM(F2:F11)";
oRng5.Interior.ColorIndex = 3;

Excel.Range frees = xlWorkSheet.get_Range("A2", "I9");
frees.Application.ActiveWindow.FreezePanes = true;


Excel.Range myRange = xlWorkSheet.Rows["14:30", Missing.Value] as Excel.Range;

myRange.OutlineLevel = 1;

myRange.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);


myRange = xlWorkSheet.Rows["2:12", Missing.Value] as Excel.Range;

myRange.OutlineLevel = 2;

myRange.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);



myRange = xlWorkSheet.Rows["14:16", Missing.Value] as Excel.Range;

myRange.OutlineLevel = 2;

myRange.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);



myRange = xlWorkSheet.Rows["18:20", Missing.Value] as Excel.Range;

myRange.OutlineLevel = 2;

myRange.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);


myRange = xlWorkSheet.Rows["22:24", Missing.Value] as Excel.Range;

myRange.OutlineLevel = 2;

myRange.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);




xlWorkSheet.Columns.AutoFit();






// Worksheet Two.

Excel.Worksheet xlWorkSheet1;
xlWorkSheet1 = (Excel.Worksheet)(xlWorkBook.Worksheets[2]);


xlWorkSheet1.Name = DateTime.Now.Year.ToString() + " Second Worksheet";
xlWorkSheet1.Activate();

int k = 2;
xlWorkSheet1.Cells[1, 1] = "First Name";
xlWorkSheet1.Cells[1, 2] = "Last Name";
xlWorkSheet1.Cells[1, 3] = "Full Name";
xlWorkSheet1.Cells[1, 4] = "Company Name";
xlWorkSheet1.Cells[1, 5] = "Topic";
xlWorkSheet1.Cells[1, 6] = "Salary";
xlWorkSheet1.Cells[1, 7] = "PT";
xlWorkSheet1.Cells[1, 8] = "Total";
xlWorkSheet1.Cells[1, 9] = "Full Name";

xlWorkSheet1.get_Range("A1", "I1").Font.Bold = true;
xlWorkSheet1.get_Range("A1", "I1").Locked = true;
xlWorkSheet1.get_Range("A1", "I1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;


Excel.Range oResizeRange1 = xlWorkSheet1.get_Range("A1", "I1");

// oResizeRange1.Orientation = 38;
oResizeRange1.Locked = true;
oResizeRange1.Borders.Weight = Excel.XlBorderWeight.xlThick;

oResizeRange1.Orientation = 0;
oResizeRange1.WrapText = true;
oResizeRange1.Borders.Weight = 2;
oResizeRange1.Interior.ColorIndex = 36;


foreach (DataRow dr in ds.Tables[0].Rows)
{
xlWorkSheet1.Cells[k, 1] = dr[0];
xlWorkSheet1.Cells[k, 2] = dr[1];
xlWorkSheet1.Cells[k, 3] = dr[2];
xlWorkSheet1.Cells[k, 4] = dr[3];
xlWorkSheet1.Cells[k, 5] = dr[4];
xlWorkSheet1.Cells[k, 6] = dr[5];
xlWorkSheet1.Cells[k, 7] = dr[6];

Excel.Range oRng4;
oRng4 = xlWorkSheet1.get_Range("I2", "I11");
oRng4.Formula = "=A2 & \" \" & B2";

oRng4 = xlWorkSheet1.get_Range("H2", "H11");
oRng4.Formula = "=SUM(F2:G2)";

k = k + 1;
}

xlWorkSheet1.Cells[j, 5] = "Total";
string st0 = "F" + k.ToString();

Excel.Range oRng15 = xlWorkSheet1.get_Range(st0, st0);
oRng15.Formula = "=SUM(F2:F11)";
oRng15.Interior.ColorIndex = 3;




Excel.Range myRange1 = xlWorkSheet1.Rows["2:30", Missing.Value] as Excel.Range;
myRange1.Application.ActiveWindow.FreezePanes = true;





xlWorkSheet1.Columns.AutoFit();

xlWorkSheet.Activate();



}
}