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 TestvikramFinal : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string sumtotal = string.Empty;
string sumeuro = string.Empty;
string sumevolass = string.Empty;
string tosplit = string.Empty;
System.Data.DataTable dt = new System.Data.DataTable();
DataColumn dc0 = new DataColumn("Company");
DataColumn dc1 = new DataColumn("ProjectNr");
DataColumn dc2 = new DataColumn("Customer");
DataColumn dc3 = new DataColumn("Project");
DataColumn dc4 = new DataColumn("Country");
DataColumn dc5 = new DataColumn("Quote");
DataColumn dc6 = new DataColumn("Product");
DataColumn dc7 = new DataColumn("Total");
DataColumn dc8 = new DataColumn("Duscount");
dt.Columns.Add(dc0);
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);
dt.Columns.Add(dc8);
DataRow dr = dt.NewRow();
dr["Company"] = "Gov. Systems";
dr["ProjectNr"] = "Gerlich";
dr["Customer"] = "Customer 1";
dr["Project"] = "SkyWan Network";
dr["Country"] = "US";
dr["Quote"] = "07-10135";
dr["Product"] = "SkyWAN";
dr["Total"] = "72,000";
dr["Duscount"] = "50%";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["Company"] = "Gov. Systems";
dr1["ProjectNr"] = "Vitorla";
dr1["Customer"] = "Customer 2";
dr1["Project"] = "Space Segment";
dr1["Country"] = "Angola";
dr1["Quote"] = "07-10136";
dr1["Product"] = "Space Segment";
dr1["Total"] = "72,000";
dr1["Duscount"] = "75%";
dt.Rows.Add(dr1);
DataRow dr2 = dt.NewRow();
dr2["Company"] = "Gov. Systems";
dr2["ProjectNr"] = "Sanchez";
dr2["Customer"] = "Customer 3";
dr2["Project"] = "Telco Intall";
dr2["Country"] = "Mexico";
dr2["Quote"] = "80-03627";
dr2["Product"] = "VOIP Network";
dr2["Total"] = "620,000";
dr2["Duscount"] = "50%";
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["Company"] = "Gov. MNW";
dr3["ProjectNr"] = "";
dr3["Customer"] = "";
dr3["Project"] = "";
dr3["Country"] = "";
dr3["Quote"] = "";
dr3["Product"] = "";
dr3["Total"] = "";
dr3["Duscount"] = "";
dt.Rows.Add(dr3);
DataRow dr4 = dt.NewRow();
dr4["Company"] = "Media Systems";
dr4["ProjectNr"] = "Sanchez";
dr4["Customer"] = "Customer 4";
dr4["Project"] = "Backup for San Francisco";
dr4["Country"] = "USA";
dr4["Quote"] = "07-10150";
dr4["Product"] = "TV fix";
dr4["Total"] = "120,000";
dr4["Duscount"] = "25%";
dt.Rows.Add(dr4);
DataRow dr5 = dt.NewRow();
dr5["Company"] = "Media Systems";
dr5["ProjectNr"] = "Gerlich";
dr5["Customer"] = "Customer 5";
dr5["Project"] = "4.9 Meter";
dr5["Country"] = "USA";
dr5["Quote"] = "08-10010";
dr5["Product"] = "TV fix";
dr5["Total"] = "85,000";
dr5["Duscount"] = "50%";
dt.Rows.Add(dr5);
DataRow dr6 = dt.NewRow();
dr6["Company"] = "Media MNW";
dr6["ProjectNr"] = "";
dr6["Customer"] = "";
dr6["Project"] = "";
dr6["Country"] = "";
dr6["Quote"] = "";
dr6["Product"] = "";
dr6["Total"] = "";
dr6["Duscount"] = "";
dt.Rows.Add(dr6);
DataRow dr7 = dt.NewRow();
dr7["Company"] = "Telecom/Ent. Systems";
dr7["ProjectNr"] = "";
dr7["Customer"] = "";
dr7["Project"] = "";
dr7["Country"] = "";
dr7["Quote"] = "";
dr7["Product"] = "";
dr7["Total"] = "";
dr7["Duscount"] = "";
dt.Rows.Add(dr7);
DataRow dr8 = dt.NewRow();
dr8["Company"] = "Telecom/Ent. MNW";
dr8["ProjectNr"] = "Vitorla";
dr8["Customer"] = "Customer 6";
dr8["Project"] = "4.9M Earth Station";
dr8["Country"] = "Russia";
dr8["Quote"] = "07-22234";
dr8["Product"] = "TV fix";
dr8["Total"] = "800,403";
dr8["Duscount"] = "50%";
dt.Rows.Add(dr8);
System.Data.DataTable dt1 = new System.Data.DataTable();
DataColumn dc11 = new DataColumn("Product");
dt1.Columns.Add(dc11);
DataRow p1 = dt1.NewRow();
p1["Product"] = "TV fix";
dt1.Rows.Add(p1);
DataRow p2 = dt1.NewRow();
p2["Product"] = "TV mobil";
dt1.Rows.Add(p2);
DataRow p3 = dt1.NewRow();
p3["Product"] = "SkyWAN";
dt1.Rows.Add(p3);
DataRow p4 = dt1.NewRow();
p4["Product"] = "SkyArcs";
dt1.Rows.Add(p4);
DataRow p5 = dt1.NewRow();
p5["Product"] = "System & Program";
dt1.Rows.Add(p5);
DataRow p6 = dt1.NewRow();
p6["Product"] = "Solution Defence";
dt1.Rows.Add(p6);
DataRow p7 = dt1.NewRow();
p7["Product"] = "Component";
dt1.Rows.Add(p7);
DataRow p8 = dt1.NewRow();
p8["Product"] = "Other";
dt1.Rows.Add(p8);
DataRow p9 = dt1.NewRow();
p9["Product"] = "Space Segment";
dt1.Rows.Add(p9);
DataRow p10 = dt1.NewRow();
p10["Product"] = "VOIP Network";
dt1.Rows.Add(p10);
DataRow p11 = dt1.NewRow();
p11["Product"] = "Hosting Service";
dt1.Rows.Add(p11);
DataRow p12 = dt1.NewRow();
p12["Product"] = "Other Managed NW Services";
dt1.Rows.Add(p12);
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();
xlWorkSheet.Cells[1, 2] = "XXX -Forecast 07 / Budget 08- in K USD";
xlWorkSheet.Cells[1, 21] = "Order Input -Commercial-";
xlWorkSheet.Cells[2, 2] = "version 1";
xlWorkSheet.Cells[3, 2] = "April 9th, 2008";
xlWorkSheet.Cells[4, 2] = " 1 EUR =";
xlWorkSheet.Cells[4, 3] = "1.45 USD";
xlWorkSheet.Cells[4, 8] = "Vol.";
xlWorkSheet.Cells[4, 9] = "Vol.";
xlWorkSheet.Cells[4, 10] = "Prob.";
xlWorkSheet.Cells[4, 11] = "Vol. Ass.";
xlWorkSheet.Cells[4, 12] = "Jun 07 YTD";
xlWorkSheet.Cells[4, 13] = "7/1/2007";
xlWorkSheet.Cells[4, 14] = "8/1/2007";
xlWorkSheet.Cells[4, 15] = "9/1/2007";
xlWorkSheet.Cells[4, 16] = "Q3 07";
xlWorkSheet.Cells[4, 17] = "10/1/2007";
xlWorkSheet.Cells[4, 18] = "11/1/2007";
xlWorkSheet.Cells[4, 19] = "12/1/2007";
xlWorkSheet.Cells[4, 20] = "Q4 / 07";
xlWorkSheet.Cells[4, 21] = "TOTAL 07";
xlWorkSheet.Cells[4, 22] = "1/1/2008";
xlWorkSheet.Cells[4, 23] = "2/1/2008";
xlWorkSheet.Cells[4, 24] = "3/1/2008";
xlWorkSheet.Cells[4, 25] = "Q1 / 08";
xlWorkSheet.Cells[4, 26] = "4/1/2008";
xlWorkSheet.Cells[4, 27] = "5/1/2008";
xlWorkSheet.Cells[4, 28] = "6/1/2008";
xlWorkSheet.Cells[4, 29] = "Q2 / 08";
xlWorkSheet.Cells[4, 30] = "7/1/2008";
xlWorkSheet.Cells[4, 31] = "8/1/2008";
xlWorkSheet.Cells[4, 32] = "9/1/2008";
xlWorkSheet.Cells[4, 33] = "Q3 / 08";
xlWorkSheet.Cells[4, 34] = "10/1/2008";
xlWorkSheet.Cells[4, 35] = "11/1/2008";
xlWorkSheet.Cells[4, 36] = "12/1/2008";
xlWorkSheet.Cells[4, 37] = "Q4 / 08";
xlWorkSheet.Cells[4, 38] = "TOTAL 08";
xlWorkSheet.Cells[4, 39] = "1/1/2009";
xlWorkSheet.Cells[4, 40] = "2/1/2009";
xlWorkSheet.Cells[4, 41] = "3/1/2009";
xlWorkSheet.Cells[4, 42] = "Q1 / 09";
xlWorkSheet.Cells[4, 43] = "4/1/2009";
xlWorkSheet.Cells[4, 44] = "5/1/2009";
xlWorkSheet.Cells[4, 45] = "6/1/2009";
xlWorkSheet.Cells[4, 46] = "Q2 / 09";
xlWorkSheet.Cells[4, 47] = "7/1/2009";
xlWorkSheet.Cells[4, 48] = "8/1/2009";
xlWorkSheet.Cells[4, 49] = "9/1/2009";
xlWorkSheet.Cells[4, 50] = "Q3 / 09";
xlWorkSheet.Cells[4, 51] = "10/1/2009";
xlWorkSheet.Cells[4, 52] = "11/1/2009";
xlWorkSheet.Cells[4, 53] = "12/1/2009";
xlWorkSheet.Cells[4, 54] = "Q4 / 09";
xlWorkSheet.Cells[4, 55] = "TOTAL 09";
xlWorkSheet.Cells[4, 56] = "check";
// xlWorkSheet.Cells[4, 12] = "USD";
xlWorkSheet.Cells[5, 2] = "Project Nr.";
xlWorkSheet.Cells[5, 3] = "Customer";
xlWorkSheet.Cells[5, 4] = "Project";
xlWorkSheet.Cells[5, 5] = "Country";
xlWorkSheet.Cells[5, 6] = "Quote #";
xlWorkSheet.Cells[5, 7] = "Product";
xlWorkSheet.Cells[5, 8] = "TOTAL";
xlWorkSheet.Cells[5, 9] = "EUR";
xlWorkSheet.Cells[5, 10] = "";
xlWorkSheet.Cells[5, 11] = "USD";
xlWorkSheet.Cells[6, 3] = "LoB Commercial";
xlWorkSheet.Cells[6, 4] = "All";
Excel.Range fillusd;
fillusd = xlWorkSheet.get_Range("L5", "BC5");
fillusd.Formula = "=K5";
fillusd = xlWorkSheet.get_Range("M4", "O4");
fillusd.NumberFormat = "mmm-yy";
fillusd = xlWorkSheet.get_Range("Q4", "S4");
fillusd.NumberFormat = "mmm-yy";
fillusd = xlWorkSheet.get_Range("V4", "X4");
fillusd.NumberFormat = "mmm-yy";
fillusd = xlWorkSheet.get_Range("Z4", "AB4");
fillusd.NumberFormat = "mmm-yy";
fillusd = xlWorkSheet.get_Range("AD4", "AF4");
fillusd.NumberFormat = "mmm-yy";
fillusd = xlWorkSheet.get_Range("AH4", "AJ4");
fillusd.NumberFormat = "mmm-yy";
fillusd = xlWorkSheet.get_Range("AM4", "AO4");
fillusd.NumberFormat = "mmm-yy";
fillusd = xlWorkSheet.get_Range("AQ4", "AS4");
fillusd.NumberFormat = "mmm-yy";
fillusd = xlWorkSheet.get_Range("AU4", "AW4");
fillusd.NumberFormat = "mmm-yy";
fillusd = xlWorkSheet.get_Range("AY4", "BA4");
fillusd.NumberFormat = "mmm-yy";
Excel.Range eurocol;
eurocol = xlWorkSheet.get_Range("B4" , "C4");
eurocol.Borders.Weight = 1;
eurocol.Interior.ColorIndex = 16;
eurocol = xlWorkSheet.get_Range("U1", "U1");
eurocol.Borders.Weight = 1;
eurocol.Font.Bold=true;
eurocol.Interior.ColorIndex = 36;
eurocol = xlWorkSheet.get_Range("C4", "C4");
eurocol.Borders.Weight = 1;
eurocol.Font.ColorIndex=3;
eurocol = xlWorkSheet.get_Range("K5", "K5");
eurocol.Borders.Weight = 1;
eurocol.Font.ColorIndex = 3;
int recs = 7;
string check = string.Empty;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (i < dt.Rows.Count - 1)
{
sumtotal = sumtotal + "H" + recs.ToString() + "+";
sumeuro = sumeuro + "I" + recs.ToString() + "+";
sumevolass = sumevolass + "K" + recs.ToString() + "+";
tosplit=tosplit+ "@" + recs.ToString() + "+";
}
else
{
sumtotal = sumtotal + "H" + recs.ToString();
sumeuro = sumeuro + "I" + recs.ToString();
sumevolass = sumevolass + "K" + recs.ToString();
tosplit = tosplit + "@" + recs.ToString();
}
if (check != dt.Rows[i][0].ToString())
{
check = dt.Rows[i][0].ToString();
xlWorkSheet.Cells[recs, 1] = dt.Rows[i][0].ToString();
xlWorkSheet.Cells[recs, 3] = dt.Rows[i][0].ToString();
xlWorkSheet.Cells[recs, 4] = "All";
int temp = recs + 1;
int tmpfill = temp;
for (int j = 0; j < dt.Rows.Count; j++)
{
if (check == dt.Rows[j][0].ToString())
{
if (dt.Rows[j][1].ToString() != "")
{
xlWorkSheet.Cells[tmpfill, 2] = dt.Rows[j][1].ToString();
xlWorkSheet.Cells[tmpfill, 3] = dt.Rows[j][2].ToString();
xlWorkSheet.Cells[tmpfill, 4] = dt.Rows[j][3].ToString();
xlWorkSheet.Cells[tmpfill, 5] = dt.Rows[j][4].ToString();
xlWorkSheet.Cells[tmpfill, 6] = dt.Rows[j][5].ToString();
xlWorkSheet.Cells[tmpfill, 7] = dt.Rows[j][6].ToString();// product
//FILL DROPDOWN OF PRODUCT
// object oMissing = System.Reflection.Missing.Value;
// Range xlsRange = xlWorkSheet.get_Range("G" + tmpfill.ToString(), "G" + tmpfill.ToString());
// Excel.DropDowns xlDropDowns;
// Excel.DropDown xlDropDown;
// xlDropDowns = ((Excel.DropDowns)(xlWorkSheet.DropDowns(oMissing)));
// xlDropDown = xlDropDowns.Add((double)xlsRange.Left, (double)xlsRange.Top, (double)xlsRange.Width, (double)xlsRange.Height, true);
//// Add item into drop down list
// int index = 0;
// for (int l = 0; l < dt1.Rows.Count; l++)
// {
// if (dt.Rows[j][6].ToString() == dt1.Rows[l][0].ToString())
// {
// index =l + 1;
// }
// xlDropDown.AddItem(dt1.Rows[l][0].ToString(), l + 1);
// }
// xlDropDown.Value = index;
//xlWorkSheet.Cells[tmpfill, 7]=dt.Rows[j][6].ToString();
xlWorkSheet.Cells[tmpfill, 8] = dt.Rows[j][7].ToString();
// xlWorkSheet.Cells[tmpfill, 9] = "";
xlWorkSheet.Cells[tmpfill, 10] = dt.Rows[j][8].ToString(); ;
Excel.Range oRng1;
oRng1 = xlWorkSheet.get_Range("I" + temp.ToString(), "I" + tmpfill.ToString());
oRng1.Formula = "=H" + temp.ToString() + " /(1.45)";
//companyeprob.NumberFormat ="0,000,000";
Excel.Range oRng2;
oRng2 = xlWorkSheet.get_Range("K" + temp.ToString(), "K" + tmpfill.ToString());
oRng2.Formula = "=H" + temp.ToString() + "*J" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("AE" + temp.ToString(), "AE" + tmpfill.ToString());
oRng2.Formula = "=K" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("P" + temp.ToString(), "P" + tmpfill.ToString());
oRng2.Formula = "=M" + temp.ToString() + "+N" + temp.ToString() + "+O" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("T" + temp.ToString(), "T" + tmpfill.ToString());
oRng2.Formula = "=Q" + temp.ToString() + "+R" + temp.ToString() + "+S" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("U" + temp.ToString(), "U" + tmpfill.ToString());
oRng2.Formula = "=+T" + temp.ToString() + "+P" + temp.ToString() + "+L" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("Y" + temp.ToString(), "Y" + tmpfill.ToString());
oRng2.Formula = "=V" + temp.ToString() + "+W" + temp.ToString() + "+X" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("AC" + temp.ToString(), "AC" + tmpfill.ToString());
oRng2.Formula = "=Z" + temp.ToString() + "+AA" + temp.ToString() + "+AB" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("AG" + temp.ToString(), "AG" + tmpfill.ToString());
oRng2.Formula = "=AD" + temp.ToString() + "+AE" + temp.ToString() + "+AF" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("AK" + temp.ToString(), "AK" + tmpfill.ToString());
oRng2.Formula = "=AH" + temp.ToString() + "+AI" + temp.ToString() + "+AJ" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("AL" + temp.ToString(), "AL" + tmpfill.ToString());
oRng2.Formula = "=+Y" + temp.ToString() + "+AC" + temp.ToString() + "+AG" + temp.ToString() + "+AK" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("AP" + temp.ToString(), "AP" + tmpfill.ToString());
oRng2.Formula = "=AM" + temp.ToString() + "+AN" + temp.ToString() + "+AO" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("AT" + temp.ToString(), "AT" + tmpfill.ToString());
oRng2.Formula = "=AQ" + temp.ToString() + "+AR" + temp.ToString() + "+AS" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("AX" + temp.ToString(), "AX" + tmpfill.ToString());
oRng2.Formula = "=AU" + temp.ToString() + "+AV" + temp.ToString() + "+AW" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("BB" + temp.ToString(), "BB" + tmpfill.ToString());
oRng2.Formula = "=AY" + temp.ToString() + "+AZ" + temp.ToString() + "+BA" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("BC" + temp.ToString(), "BC" + tmpfill.ToString());
oRng2.Formula = "=+AP" + temp.ToString() + "+AT" + temp.ToString() + "+AX" + temp.ToString() + "+BB" + temp.ToString();
oRng2 = xlWorkSheet.get_Range("BD" + temp.ToString(), "BD" + tmpfill.ToString());
oRng2.Formula = "=+K" + temp.ToString() + "-U" + temp.ToString() + "+-AL" + temp.ToString() + "-BC" + temp.ToString();
//Excel.Range oRng3;
//oRng3 = xlWorkSheet.get_Range("J" + temp.ToString(), "J" + tmpfill.ToString());
//oRng3.Formula = "=H" + temp.ToString() + "*J" + temp.ToString();
tmpfill = tmpfill + 1;
}
}
}
Excel.Range oResizeRange = xlWorkSheet.get_Range("B" + recs.ToString(), "BD" + recs.ToString());
oResizeRange.Font.Bold = true;
recs = recs + 27;
int temp1 = recs - 1;
oResizeRange = xlWorkSheet.get_Range("B" + temp.ToString(), "H" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("B" + temp.ToString(), "H" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
//SET COLOR OF SECOND PART UPPER
oResizeRange = xlWorkSheet.get_Range("L" + temp.ToString(), "O" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("Q" + temp.ToString(), "S" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("V" + temp.ToString(), "X" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("Z" + temp.ToString(), "AB" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("AD" + temp.ToString(), "AF" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("AH" + temp.ToString(), "AJ" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("AM" + temp.ToString(), "AO" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("AQ" + temp.ToString(), "AS" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("AU" + temp.ToString(), "AW" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("AY" + temp.ToString(), "BA" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
int trackheadercompany = recs - 27;
Excel.Range companytotal;
companytotal = xlWorkSheet.get_Range("H" + trackheadercompany.ToString(), "H" + trackheadercompany.ToString());
companytotal.Formula = "=SUM(H" + temp.ToString() + ":H" + temp1.ToString() + ")";
Excel.Range companyeuro;
companyeuro = xlWorkSheet.get_Range("I" + trackheadercompany.ToString(), "I" + trackheadercompany.ToString());
companyeuro.Formula = "=SUM(I" + temp.ToString() + ":I" + temp1.ToString() + ")";
Excel.Range companyevalass;
companyevalass = xlWorkSheet.get_Range("K" + trackheadercompany.ToString(), "K" + trackheadercompany.ToString());
companyevalass.Formula = "=SUM(K" + temp.ToString() + ":K" + temp1.ToString() + ")";
Excel.Range companyeprob;
companyeprob = xlWorkSheet.get_Range("J" + trackheadercompany.ToString(), "J" + trackheadercompany.ToString());
string t = "=+K" + trackheadercompany.ToString() + "/H" + trackheadercompany.ToString() + "";
companyeprob.Formula = t;
companyeprob.NumberFormat = "0%";
//SECOND PART FIELDS
//sum/total of second PART
Excel.Range sumsecond;
sumsecond = xlWorkSheet.get_Range("L" + trackheadercompany.ToString(), "L" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(L" + temp.ToString() + ":L" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("M" + trackheadercompany.ToString(), "M" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(M" + temp.ToString() + ":M" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("N" + trackheadercompany.ToString(), "N" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(N" + temp.ToString() + ":N" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("O" + trackheadercompany.ToString(), "O" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(O" + temp.ToString() + ":O" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("P" + trackheadercompany.ToString(), "P" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(P" + temp.ToString() + ":P" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("Q" + trackheadercompany.ToString(), "Q" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(Q" + temp.ToString() + ":Q" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("R" + trackheadercompany.ToString(), "R" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(R" + temp.ToString() + ":R" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("S" + trackheadercompany.ToString(), "S" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(S" + temp.ToString() + ":S" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("T" + trackheadercompany.ToString(), "T" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(T" + temp.ToString() + ":T" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("U" + trackheadercompany.ToString(), "U" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(U" + temp.ToString() + ":U" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("V" + trackheadercompany.ToString(), "V" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(V" + temp.ToString() + ":V" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("W" + trackheadercompany.ToString(), "W" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(W" + temp.ToString() + ":W" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("X" + trackheadercompany.ToString(), "X" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(X" + temp.ToString() + ":X" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("Y" + trackheadercompany.ToString(), "Y" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(Y" + temp.ToString() + ":Y" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("Z" + trackheadercompany.ToString(), "Z" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(Z" + temp.ToString() + ":Z" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AA" + trackheadercompany.ToString(), "AA" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AA" + temp.ToString() + ":AA" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AB" + trackheadercompany.ToString(), "AB" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AB" + temp.ToString() + ":AB" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AC" + trackheadercompany.ToString(), "AC" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AC" + temp.ToString() + ":AC" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AD" + trackheadercompany.ToString(), "AD" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AD" + temp.ToString() + ":AD" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AE" + trackheadercompany.ToString(), "AE" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AE" + temp.ToString() + ":AE" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AF" + trackheadercompany.ToString(), "AF" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AF" + temp.ToString() + ":AF" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AG" + trackheadercompany.ToString(), "AG" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AG" + temp.ToString() + ":AG" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AH" + trackheadercompany.ToString(), "AH" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AH" + temp.ToString() + ":AH" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AI" + trackheadercompany.ToString(), "AI" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AI" + temp.ToString() + ":AI" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AJ" + trackheadercompany.ToString(), "AJ" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AJ" + temp.ToString() + ":AJ" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AK" + trackheadercompany.ToString(), "AK" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AK" + temp.ToString() + ":AK" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AL" + trackheadercompany.ToString(), "AL" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AL" + temp.ToString() + ":AL" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AM" + trackheadercompany.ToString(), "AM" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AM" + temp.ToString() + ":AM" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AN" + trackheadercompany.ToString(), "AN" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AN" + temp.ToString() + ":AN" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AO" + trackheadercompany.ToString(), "AO" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AO" + temp.ToString() + ":AO" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AP" + trackheadercompany.ToString(), "AP" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AP" + temp.ToString() + ":AP" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AQ" + trackheadercompany.ToString(), "AQ" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AQ" + temp.ToString() + ":AQ" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AR" + trackheadercompany.ToString(), "AR" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AR" + temp.ToString() + ":AR" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AS" + trackheadercompany.ToString(), "AS" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AS" + temp.ToString() + ":AS" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AT" + trackheadercompany.ToString(), "AT" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AT" + temp.ToString() + ":AT" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AU" + trackheadercompany.ToString(), "AU" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AU" + temp.ToString() + ":AU" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AV" + trackheadercompany.ToString(), "AV" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AV" + temp.ToString() + ":AV" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AW" + trackheadercompany.ToString(), "AW" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AW" + temp.ToString() + ":AW" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AX" + trackheadercompany.ToString(), "AX" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AX" + temp.ToString() + ":AX" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AY" + trackheadercompany.ToString(), "AY" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AY" + temp.ToString() + ":AY" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("AZ" + trackheadercompany.ToString(), "AZ" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(AZ" + temp.ToString() + ":AZ" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("BA" + trackheadercompany.ToString(), "BA" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(BA" + temp.ToString() + ":BA" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("BB" + trackheadercompany.ToString(), "BB" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(BB" + temp.ToString() + ":BB" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("BC" + trackheadercompany.ToString(), "BC" + trackheadercompany.ToString());
sumsecond.Formula = "=SUM(BC" + temp.ToString() + ":BC" + temp1.ToString() + ")";
sumsecond = xlWorkSheet.get_Range("BD" + trackheadercompany.ToString(), "BD" + trackheadercompany.ToString());
sumsecond.Formula = "=+K" + trackheadercompany.ToString() + "-U" + trackheadercompany.ToString() + "+-AL" + trackheadercompany.ToString() + "-BC" + trackheadercompany.ToString();
oResizeRange = xlWorkSheet.get_Range("J" + temp.ToString(), "J" + temp1.ToString());
oResizeRange.Borders.Weight = 1;
oResizeRange.Interior.ColorIndex = 36;
oResizeRange = xlWorkSheet.get_Range("B1", "K6");
oResizeRange.Font.Bold = true;
oResizeRange = xlWorkSheet.get_Range("B4", "BD6");
oResizeRange.Borders.Weight = 2;
oResizeRange = xlWorkSheet.get_Range("L6", "BD7");
oResizeRange.Font.Bold = true;
string rng = temp.ToString() + ":" + temp1.ToString();
Excel.Range myRange = xlWorkSheet.Rows[rng, Missing.Value] as Excel.Range;
//myRange.OutlineLevel = 1;
myRange.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
}
Excel.Range companytotallab;
companytotallab = xlWorkSheet.get_Range("H6", "H6");
companytotallab.Formula = "=" + sumtotal;
companytotallab.NumberFormat = "0,000,000";
Excel.Range companyeurolab;
companyeurolab = xlWorkSheet.get_Range("I6", "I6");
companyeurolab.Formula = "=" + sumeuro;
companyeurolab.NumberFormat = "0,000,000";
Excel.Range companyevalasslab;
companyevalasslab = xlWorkSheet.get_Range("K6", "K6");
companyevalasslab.Formula = "=" + sumevolass;
companyevalasslab.NumberFormat = "0,000,000";
Excel.Range companyevaproblab;
companyevaproblab = xlWorkSheet.get_Range("J6", "J6");
companyevaproblab.Formula = "=+K6/H6";
companyevaproblab.NumberFormat = "0%";
Excel.Range connectstr;
string splitrange;
splitrange= tosplit.Replace("@","L");
connectstr = xlWorkSheet.get_Range("L6", "L6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "M");
connectstr = xlWorkSheet.get_Range("M6", "M6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "N");
connectstr = xlWorkSheet.get_Range("N6", "N6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "O");
connectstr = xlWorkSheet.get_Range("O6", "O6");
connectstr.Formula = "=" + splitrange;
connectstr = xlWorkSheet.get_Range("P6", "P6");
connectstr.Formula = "=L6" + "+M6" + "+N6";
splitrange = tosplit.Replace("@", "Q");
connectstr = xlWorkSheet.get_Range("Q6", "Q6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "R");
connectstr = xlWorkSheet.get_Range("R6", "R6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "S");
connectstr = xlWorkSheet.get_Range("S6", "S6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "T");
connectstr = xlWorkSheet.get_Range("T6", "T6");
connectstr.Formula = "=" + splitrange;
connectstr = xlWorkSheet.get_Range("U6", "U6");
connectstr.Formula = "=+T6" + "+P6" + "+L6" ;
// connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "V");
connectstr = xlWorkSheet.get_Range("V6", "V6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "S");
connectstr = xlWorkSheet.get_Range("W6", "W6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "X");
connectstr = xlWorkSheet.get_Range("X6", "X6");
connectstr.Formula = "=" + splitrange;
connectstr = xlWorkSheet.get_Range("Y6", "Y6");
connectstr.Formula = "=V6" + "+W6" + "+X6" ;
//connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "Z");
connectstr = xlWorkSheet.get_Range("Z6", "Z6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AA");
connectstr = xlWorkSheet.get_Range("AA6", "AA6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AB");
connectstr = xlWorkSheet.get_Range("AB6", "AB6");
connectstr.Formula = "=" + splitrange;
connectstr = xlWorkSheet.get_Range("AC6", "AC6");
connectstr.Formula = "=Z6" + "+AA6" + "+AB6" ;
//connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AD");
connectstr = xlWorkSheet.get_Range("AD6", "AD6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AE");
connectstr = xlWorkSheet.get_Range("AE6", "AE6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AF");
connectstr = xlWorkSheet.get_Range("AF6", "AF6");
connectstr.Formula = "=" + splitrange;
connectstr = xlWorkSheet.get_Range("AG6", "AG6");
connectstr.Formula = "=+AD6" + "+AE6" + "+AF6";
// connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AH");
connectstr = xlWorkSheet.get_Range("AH6", "AH6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AI");
connectstr = xlWorkSheet.get_Range("AI6", "AI6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AJ");
connectstr = xlWorkSheet.get_Range("AJ6", "AJ6");
connectstr.Formula = "=" + splitrange;
connectstr = xlWorkSheet.get_Range("AK6", "AK6");
connectstr.Formula = "=+AH6" + "+AI6" + "+AJ6";
connectstr = xlWorkSheet.get_Range("AL6", "AL6");
connectstr.Formula = "=+Y6" + "+AC6" + "+AG6" + "+AK6";
// connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AM");
connectstr = xlWorkSheet.get_Range("AM6", "AM6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AN");
connectstr = xlWorkSheet.get_Range("AN6", "AN6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AO");
connectstr = xlWorkSheet.get_Range("AO6", "AO6");
connectstr.Formula = "=" + splitrange;
connectstr = xlWorkSheet.get_Range("AP6", "AP6");
connectstr.Formula = "=+AM6" + "+AN6" + "+AO6";
// connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AQ");
connectstr = xlWorkSheet.get_Range("AQ6", "AQ6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AR");
connectstr = xlWorkSheet.get_Range("AR6", "AR6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AS");
connectstr = xlWorkSheet.get_Range("AS6", "AS6");
connectstr.Formula = "=" + splitrange;
connectstr = xlWorkSheet.get_Range("AT6", "AT6");
connectstr.Formula = "=+AQ6" + "+AR6" + "+AS6";
// connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AU");
connectstr = xlWorkSheet.get_Range("AU6", "AU6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AV");
connectstr = xlWorkSheet.get_Range("AV6", "AV6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AW");
connectstr = xlWorkSheet.get_Range("AW6", "AW6");
connectstr.Formula = "=" + splitrange;
connectstr = xlWorkSheet.get_Range("AX6", "AX6");
connectstr.Formula = "=+AU6" + "+AV6" + "+AW6";
// connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AY");
connectstr = xlWorkSheet.get_Range("AY6", "AY6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "AZ");
connectstr = xlWorkSheet.get_Range("AZ6", "AZ6");
connectstr.Formula = "=" + splitrange;
splitrange = tosplit.Replace("@", "BA");
connectstr = xlWorkSheet.get_Range("BA6", "BA6");
connectstr.Formula = "=" + splitrange;
connectstr = xlWorkSheet.get_Range("BB6", "BB6");
connectstr.Formula = "=+AY6" + "+AZ6" + "+BA6";
connectstr = xlWorkSheet.get_Range("BC6", "BC6");
connectstr.Formula = "=+AP6" + "+AT6" + "+AX6" + "+BB6";
connectstr = xlWorkSheet.get_Range("BD6", "BD6");
connectstr.Formula = "=+K6" + "-U6" + "-AL6" + "-BC6";
//xlWorkSheet.get_Range("A8", "J8").Select();
//xlsApp.ActiveWindow.FreezePanes = true;
xlWorkSheet.get_Range("L8", "L8").Select();
xlsApp.ActiveWindow.FreezePanes=true;
xlsApp.ActiveWindow.SmallScroll(-15, Missing.Value, Missing.Value, Missing.Value);
// Range("L7").Select
//ActiveWindow.FreezePanes = True
//ActiveWindow.SmallScroll Down:=-15
Excel.Range oResizeRange1 = xlWorkSheet.get_Range("B1", "H" + recs.ToString());
oResizeRange1.ColumnWidth = 12;
oResizeRange1 = xlWorkSheet.get_Range("A1", "A" + recs.ToString());
oResizeRange1.ColumnWidth = 2;
//Product part.
int recsproduct = recs + 2;
xlWorkSheet.Cells[recsproduct, 2] = "Product";
oResizeRange1 = xlWorkSheet.get_Range("B" + recsproduct.ToString(), "B" + recsproduct.ToString());
oResizeRange1.Font.Bold = true;
recsproduct = recsproduct +1;
int c = recsproduct;
for (int i = 0; i < dt1.Rows.Count; i++)
{
xlWorkSheet.Cells[recsproduct, 2] = dt1.Rows[i][0].ToString();
Excel.Range oRng2;
oRng2 = xlWorkSheet.get_Range("H" + recsproduct.ToString(), "H" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B"+recsproduct+",H$8:H$178)";
oRng2 = xlWorkSheet.get_Range("I" + recsproduct.ToString(), "I" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B"+recsproduct+",I$8:I$178)";
oRng2 = xlWorkSheet.get_Range("K" + recsproduct.ToString(), "K" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",K$8:K$178)";
oRng2 = xlWorkSheet.get_Range("L" + recsproduct.ToString(), "L" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",L$8:L$178)";
oRng2 = xlWorkSheet.get_Range("M" + recsproduct.ToString(), "M" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",M$8:M$178)";
oRng2 = xlWorkSheet.get_Range("N" + recsproduct.ToString(), "N" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",N$8:N$178)";
oRng2 = xlWorkSheet.get_Range("O" + recsproduct.ToString(), "O" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",O$8:O$178)";
oRng2 = xlWorkSheet.get_Range("P" + recsproduct.ToString(), "P" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",P$8:P$178)";
oRng2 = xlWorkSheet.get_Range("Q" + recsproduct.ToString(), "Q" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",Q$8:Q$178)";
oRng2 = xlWorkSheet.get_Range("R" + recsproduct.ToString(), "R" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",R$8:R$178)";
oRng2 = xlWorkSheet.get_Range("S" + recsproduct.ToString(), "S" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",S$8:S$178)";
oRng2 = xlWorkSheet.get_Range("T" + recsproduct.ToString(), "T" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",T$8:T$178)";
oRng2 = xlWorkSheet.get_Range("U" + recsproduct.ToString(), "U" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",U$8:U$178)";
oRng2 = xlWorkSheet.get_Range("V" + recsproduct.ToString(), "V" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",V$8:V$178)";
oRng2 = xlWorkSheet.get_Range("W" + recsproduct.ToString(), "W" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",W$8:W$178)";
oRng2 = xlWorkSheet.get_Range("X" + recsproduct.ToString(), "X" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",X$8:X$178)";
oRng2 = xlWorkSheet.get_Range("Y" + recsproduct.ToString(), "Y" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",Y$8:Y$178)";
oRng2 = xlWorkSheet.get_Range("Z" + recsproduct.ToString(), "Z" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",Z$8:Z$178)";
oRng2 = xlWorkSheet.get_Range("AA" + recsproduct.ToString(), "AA" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AA$8:AA$178)";
oRng2 = xlWorkSheet.get_Range("AB" + recsproduct.ToString(), "AB" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AB$8:AB$178)";
oRng2 = xlWorkSheet.get_Range("AC" + recsproduct.ToString(), "AC" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AC$8:AC$178)";
oRng2 = xlWorkSheet.get_Range("AD" + recsproduct.ToString(), "AD" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AD$8:AD$178)";
oRng2 = xlWorkSheet.get_Range("AE" + recsproduct.ToString(), "AE" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AE$8:AE$178)";
oRng2 = xlWorkSheet.get_Range("AF" + recsproduct.ToString(), "AF" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AF$8:AF$178)";
oRng2 = xlWorkSheet.get_Range("AG" + recsproduct.ToString(), "AG" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AG$8:AG$178)";
oRng2 = xlWorkSheet.get_Range("AH" + recsproduct.ToString(), "AH" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AH$8:AH$178)";
oRng2 = xlWorkSheet.get_Range("AI" + recsproduct.ToString(), "AI" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AI$8:AI$178)";
oRng2 = xlWorkSheet.get_Range("AJ" + recsproduct.ToString(), "AJ" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AJ$8:AJ$178)";
oRng2 = xlWorkSheet.get_Range("AK" + recsproduct.ToString(), "AK" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AK$8:AK$178)";
oRng2 = xlWorkSheet.get_Range("AL" + recsproduct.ToString(), "AL" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AL$8:AL$178)";
oRng2 = xlWorkSheet.get_Range("AM" + recsproduct.ToString(), "AM" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AM$8:AM$178)";
oRng2 = xlWorkSheet.get_Range("AN" + recsproduct.ToString(), "AN" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AN$8:AN$178)";
oRng2 = xlWorkSheet.get_Range("AO" + recsproduct.ToString(), "AO" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AO$8:AO$178)";
oRng2 = xlWorkSheet.get_Range("AP" + recsproduct.ToString(), "AP" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AP$8:AP$178)";
oRng2 = xlWorkSheet.get_Range("AQ" + recsproduct.ToString(), "AQ" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AQ$8:AQ$178)";
oRng2 = xlWorkSheet.get_Range("AR" + recsproduct.ToString(), "AR" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AR$8:AR$178)";
oRng2 = xlWorkSheet.get_Range("AS" + recsproduct.ToString(), "AS" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AS$8:AS$178)";
oRng2 = xlWorkSheet.get_Range("AT" + recsproduct.ToString(), "AT" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AT$8:AT$178)";
oRng2 = xlWorkSheet.get_Range("AU" + recsproduct.ToString(), "AU" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AU$8:AU$178)";
oRng2 = xlWorkSheet.get_Range("AV" + recsproduct.ToString(), "AV" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AV$8:AV$178)";
oRng2 = xlWorkSheet.get_Range("AW" + recsproduct.ToString(), "AW" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AW$8:AW$178)";
oRng2 = xlWorkSheet.get_Range("AX" + recsproduct.ToString(), "AX" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AX$8:AX$178)";
oRng2 = xlWorkSheet.get_Range("AY" + recsproduct.ToString(), "AY" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AY$8:AY$178)";
oRng2 = xlWorkSheet.get_Range("AZ" + recsproduct.ToString(), "AZ" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",AZ$8:AZ$178)";
oRng2 = xlWorkSheet.get_Range("BA" + recsproduct.ToString(), "BA" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",BA$8:BA$178)";
oRng2 = xlWorkSheet.get_Range("BB" + recsproduct.ToString(), "BB" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",BB$8:BB$178)";
oRng2 = xlWorkSheet.get_Range("BC" + recsproduct.ToString(), "BC" + recsproduct.ToString());
oRng2.Formula = "=SUMIF($G$8:$G$" + recs + ",$B" + recsproduct + ",BC$8:BC$178)";
recsproduct = recsproduct + 1;
}
int cc = recsproduct - 1;
oResizeRange1 = xlWorkSheet.get_Range("B" + c.ToString(), "K" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 40;
//SET COLOR OF SECOND PART LOWER
oResizeRange1 = xlWorkSheet.get_Range("L" + c.ToString(), "O" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 40;
oResizeRange1 = xlWorkSheet.get_Range("Q" + c.ToString(), "S" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 36;
oResizeRange1 = xlWorkSheet.get_Range("V" + c.ToString(), "X" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 40;
oResizeRange1 = xlWorkSheet.get_Range("Z" + c.ToString(), "AB" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 40;
oResizeRange1 = xlWorkSheet.get_Range("AD" + c.ToString(), "AF" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 40;
oResizeRange1 = xlWorkSheet.get_Range("AH" + c.ToString(), "AJ" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 40;
oResizeRange1 = xlWorkSheet.get_Range("AM" + c.ToString(), "AO" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 40;
oResizeRange1 = xlWorkSheet.get_Range("AQ" + c.ToString(), "AS" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 40;
oResizeRange1 = xlWorkSheet.get_Range("AU" + c.ToString(), "AW" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 40;
oResizeRange1 = xlWorkSheet.get_Range("AY" + c.ToString(), "BA" + cc.ToString());
oResizeRange1.Borders.Weight = 1;
oResizeRange1.Interior.ColorIndex = 40;
Excel.Range botborder;// xlWorkSheet.get_Range("A8", "J8").Select();
botborder = xlWorkSheet.get_Range("B" + c.ToString(), "BC" + cc.ToString());
botborder.Borders.Weight = 2;
botborder.Borders.LineStyle = 1;
//object linsestyle = "xlContinuous";
//Excel.XlBorderWeight width = Excel.XlBorderWeight.xlMedium;
//Excel.XlColorIndex color = Excel.XlColorIndex.xlColorIndexAutomatic;
//botborder.BorderAround(linsestyle, width, color, 30);
xlWorkSheet.Cells[recsproduct, 2] = "Total";
int checkcals = recsproduct + 1;
xlWorkSheet.Cells[checkcals, 2] = "Check";
int ccc = recsproduct + 1;
oResizeRange1 = xlWorkSheet.get_Range("B" + recsproduct.ToString(), "B" + ccc.ToString());
oResizeRange1.Font.Bold = true;
Excel.Range tot;
tot = xlWorkSheet.get_Range("H" + recsproduct.ToString(), "H" + recsproduct.ToString());
tot.Formula = "=SUM(H" + c.ToString() + ":H" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("I" + recsproduct.ToString(), "I" + recsproduct.ToString());
tot.Formula = "=SUM(I" + c.ToString() + ":I" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("J" + recsproduct.ToString(), "J" + recsproduct.ToString());
tot.Formula = "=SUM(J" + c.ToString() + ":J" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("K" + recsproduct.ToString(), "K" + recsproduct.ToString());
tot.Formula = "=SUM(K" + c.ToString() + ":K" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("L" + recsproduct.ToString(), "L" + recsproduct.ToString());
tot.Formula = "=SUM(L" + c.ToString() + ":L" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("M" + recsproduct.ToString(), "M" + recsproduct.ToString());
tot.Formula = "=SUM(M" + c.ToString() + ":M" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("N" + recsproduct.ToString(), "N" + recsproduct.ToString());
tot.Formula = "=SUM(N" + c.ToString() + ":N" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("O" + recsproduct.ToString(), "O" + recsproduct.ToString());
tot.Formula = "=SUM(O" + c.ToString() + ":O" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("P" + recsproduct.ToString(), "P" + recsproduct.ToString());
tot.Formula = "=SUM(P" + c.ToString() + ":P" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("Q" + recsproduct.ToString(), "Q" + recsproduct.ToString());
tot.Formula = "=SUM(Q" + c.ToString() + ":Q" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("R" + recsproduct.ToString(), "R" + recsproduct.ToString());
tot.Formula = "=SUM(R" + c.ToString() + ":R" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("S" + recsproduct.ToString(), "S" + recsproduct.ToString());
tot.Formula = "=SUM(S" + c.ToString() + ":S" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("T" + recsproduct.ToString(), "T" + recsproduct.ToString());
tot.Formula = "=SUM(T" + c.ToString() + ":T" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("U" + recsproduct.ToString(), "U" + recsproduct.ToString());
tot.Formula = "=SUM(U" + c.ToString() + ":U" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("V" + recsproduct.ToString(), "V" + recsproduct.ToString());
tot.Formula = "=SUM(V" + c.ToString() + ":V" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("W" + recsproduct.ToString(), "W" + recsproduct.ToString());
tot.Formula = "=SUM(W" + c.ToString() + ":W" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("X" + recsproduct.ToString(), "X" + recsproduct.ToString());
tot.Formula = "=SUM(X" + c.ToString() + ":X" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("Y" + recsproduct.ToString(), "Y" + recsproduct.ToString());
tot.Formula = "=SUM(Y" + c.ToString() + ":Y" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("Z" + recsproduct.ToString(), "Z" + recsproduct.ToString());
tot.Formula = "=SUM(Z" + c.ToString() + ":Z" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AA" + recsproduct.ToString(), "AA" + recsproduct.ToString());
tot.Formula = "=SUM(AA" + c.ToString() + ":AA" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AB" + recsproduct.ToString(), "AB" + recsproduct.ToString());
tot.Formula = "=SUM(AB" + c.ToString() + ":AB" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AC" + recsproduct.ToString(), "AC" + recsproduct.ToString());
tot.Formula = "=SUM(AC" + c.ToString() + ":AC" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AD" + recsproduct.ToString(), "AD" + recsproduct.ToString());
tot.Formula = "=SUM(AD" + c.ToString() + ":AD" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AE" + recsproduct.ToString(), "AE" + recsproduct.ToString());
tot.Formula = "=SUM(AE" + c.ToString() + ":AE" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AF" + recsproduct.ToString(), "AF" + recsproduct.ToString());
tot.Formula = "=SUM(AF" + c.ToString() + ":AF" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AG" + recsproduct.ToString(), "AG" + recsproduct.ToString());
tot.Formula = "=SUM(AG" + c.ToString() + ":AG" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AH" + recsproduct.ToString(), "AH" + recsproduct.ToString());
tot.Formula = "=SUM(AH" + c.ToString() + ":AH" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AI" + recsproduct.ToString(), "AI" + recsproduct.ToString());
tot.Formula = "=SUM(AI" + c.ToString() + ":AI" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AJ" + recsproduct.ToString(), "AJ" + recsproduct.ToString());
tot.Formula = "=SUM(AJ" + c.ToString() + ":AJ" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AK" + recsproduct.ToString(), "AK" + recsproduct.ToString());
tot.Formula = "=SUM(AK" + c.ToString() + ":AK" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AL" + recsproduct.ToString(), "AL" + recsproduct.ToString());
tot.Formula = "=SUM(AL" + c.ToString() + ":AL" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AM" + recsproduct.ToString(), "AM" + recsproduct.ToString());
tot.Formula = "=SUM(AM" + c.ToString() + ":AM" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AN" + recsproduct.ToString(), "AN" + recsproduct.ToString());
tot.Formula = "=SUM(AN" + c.ToString() + ":AN" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AO" + recsproduct.ToString(), "AO" + recsproduct.ToString());
tot.Formula = "=SUM(AO" + c.ToString() + ":AO" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AP" + recsproduct.ToString(), "AP" + recsproduct.ToString());
tot.Formula = "=SUM(AP" + c.ToString() + ":AP" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AQ" + recsproduct.ToString(), "AQ" + recsproduct.ToString());
tot.Formula = "=SUM(AQ" + c.ToString() + ":AQ" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AR" + recsproduct.ToString(), "AR" + recsproduct.ToString());
tot.Formula = "=SUM(AR" + c.ToString() + ":AR" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AS" + recsproduct.ToString(), "AS" + recsproduct.ToString());
tot.Formula = "=SUM(AS" + c.ToString() + ":AS" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AT" + recsproduct.ToString(), "AT" + recsproduct.ToString());
tot.Formula = "=SUM(AT" + c.ToString() + ":AT" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AU" + recsproduct.ToString(), "AU" + recsproduct.ToString());
tot.Formula = "=SUM(AU" + c.ToString() + ":AU" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AV" + recsproduct.ToString(), "AV" + recsproduct.ToString());
tot.Formula = "=SUM(AV" + c.ToString() + ":AV" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AW" + recsproduct.ToString(), "AW" + recsproduct.ToString());
tot.Formula = "=SUM(AW" + c.ToString() + ":AW" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AX" + recsproduct.ToString(), "AX" + recsproduct.ToString());
tot.Formula = "=SUM(AX" + c.ToString() + ":AX" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AY" + recsproduct.ToString(), "AY" + recsproduct.ToString());
tot.Formula = "=SUM(AY" + c.ToString() + ":AY" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("AZ" + recsproduct.ToString(), "AZ" + recsproduct.ToString());
tot.Formula = "=SUM(AZ" + c.ToString() + ":AZ" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("BA" + recsproduct.ToString(), "BA" + recsproduct.ToString());
tot.Formula = "=SUM(BA" + c.ToString() + ":BA" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("BB" + recsproduct.ToString(), "BB" + recsproduct.ToString());
tot.Formula = "=SUM(BB" + c.ToString() + ":BB" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("BC" + recsproduct.ToString(), "BC" + recsproduct.ToString());
tot.Formula = "=SUM(BC" + c.ToString() + ":BC" + cc.ToString() + ")";
tot = xlWorkSheet.get_Range("H" + checkcals.ToString(), "H" + checkcals.ToString());
tot.Formula = "=+H6-H" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("i" + checkcals.ToString(), "I" + checkcals.ToString());
tot.Formula = "=+I6-I" + recsproduct.ToString() + "";
//tot = xlWorkSheet.get_Range("J" + checkcals.ToString(), "J" + checkcals.ToString());
//tot.Formula = "=+J6-J" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("K" + checkcals.ToString(), "K" + checkcals.ToString());
tot.Formula = "=+K6-K" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("L" + checkcals.ToString(), "L" + checkcals.ToString());
tot.Formula = "=+L6-L" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("M" + checkcals.ToString(), "M" + checkcals.ToString());
tot.Formula = "=+M6-M" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("N" + checkcals.ToString(), "N" + checkcals.ToString());
tot.Formula = "=+N6-N" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("O" + checkcals.ToString(), "O" + checkcals.ToString());
tot.Formula = "=+O6-O" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("P" + checkcals.ToString(), "P" + checkcals.ToString());
tot.Formula = "=+P6-P" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("Q" + checkcals.ToString(), "Q" + checkcals.ToString());
tot.Formula = "=+Q6-Q" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("R" + checkcals.ToString(), "R" + checkcals.ToString());
tot.Formula = "=+R6-R" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("S" + checkcals.ToString(), "S" + checkcals.ToString());
tot.Formula = "=+S6-S" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("T" + checkcals.ToString(), "T" + checkcals.ToString());
tot.Formula = "=+T6-T" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("U" + checkcals.ToString(), "U" + checkcals.ToString());
tot.Formula = "=+U6-U" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("V" + checkcals.ToString(), "V" + checkcals.ToString());
tot.Formula = "=+V6-V" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("W" + checkcals.ToString(), "W" + checkcals.ToString());
tot.Formula = "=+W6-W" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("X" + checkcals.ToString(), "X" + checkcals.ToString());
tot.Formula = "=+X6-X" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("Y" + checkcals.ToString(), "Y" + checkcals.ToString());
tot.Formula = "=+Y6-Y" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("Z" + checkcals.ToString(), "Z" + checkcals.ToString());
tot.Formula = "=+Z6-Z" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AA" + checkcals.ToString(), "AA" + checkcals.ToString());
tot.Formula = "=+AA6-AA" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AB" + checkcals.ToString(), "AB" + checkcals.ToString());
tot.Formula = "=+AB6-AB" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AC" + checkcals.ToString(), "AC" + checkcals.ToString());
tot.Formula = "=+AC6-AC" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AD" + checkcals.ToString(), "AD" + checkcals.ToString());
tot.Formula = "=+AD6-AD" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AE" + checkcals.ToString(), "AE" + checkcals.ToString());
tot.Formula = "=+AE6-AE" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AF" + checkcals.ToString(), "AF" + checkcals.ToString());
tot.Formula = "=+AF6-AF" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AG" + checkcals.ToString(), "AG" + checkcals.ToString());
tot.Formula = "=+AG6-AG" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AH" + checkcals.ToString(), "AH" + checkcals.ToString());
tot.Formula = "=+AH6-AH" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AI" + checkcals.ToString(), "AI" + checkcals.ToString());
tot.Formula = "=+AI6-AI" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AJ" + checkcals.ToString(), "AJ" + checkcals.ToString());
tot.Formula = "=+AJ6-AJ" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AK" + checkcals.ToString(), "AK" + checkcals.ToString());
tot.Formula = "=+AK6-AK" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AL" + checkcals.ToString(), "AL" + checkcals.ToString());
tot.Formula = "=+AL6-AL" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AM" + checkcals.ToString(), "AM" + checkcals.ToString());
tot.Formula = "=+AM6-AM" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AN" + checkcals.ToString(), "AN" + checkcals.ToString());
tot.Formula = "=+AN6-AN" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AO" + checkcals.ToString(), "AO" + checkcals.ToString());
tot.Formula = "=+AO6-AO" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AP" + checkcals.ToString(), "AP" + checkcals.ToString());
tot.Formula = "=+AP6-AP" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AQ" + checkcals.ToString(), "AQ" + checkcals.ToString());
tot.Formula = "=+AQ6-AQ" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AR" + checkcals.ToString(), "AR" + checkcals.ToString());
tot.Formula = "=+AR6-AR" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AS" + checkcals.ToString(), "AS" + checkcals.ToString());
tot.Formula = "=+AS6-AS" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AT" + checkcals.ToString(), "AT" + checkcals.ToString());
tot.Formula = "=+AT6-AT" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AU" + checkcals.ToString(), "AU" + checkcals.ToString());
tot.Formula = "=+AU6-AU" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AV" + checkcals.ToString(), "AV" + checkcals.ToString());
tot.Formula = "=+AV6-AV" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AW" + checkcals.ToString(), "AW" + checkcals.ToString());
tot.Formula = "=+AW6-AW" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AX" + checkcals.ToString(), "AX" + checkcals.ToString());
tot.Formula = "=+AX6-AX" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AY" + checkcals.ToString(), "AY" + checkcals.ToString());
tot.Formula = "=+AY6-AY" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("AZ" + checkcals.ToString(), "AZ" + checkcals.ToString());
tot.Formula = "=+AZ6-AZ" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("BA" + checkcals.ToString(), "BA" + checkcals.ToString());
tot.Formula = "=+BA6-BA" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("BB" + checkcals.ToString(), "BB" + checkcals.ToString());
tot.Formula = "=+BB6-BB" + recsproduct.ToString() + "";
tot = xlWorkSheet.get_Range("BC" + checkcals.ToString(), "BC" + checkcals.ToString());
tot.Formula = "=+BC6-BC" + recsproduct.ToString() + "";
//GROUP CELLS
Excel.Range currentCell = xlWorkSheet.get_Range(string.Format("M{0}", ccc), string.Format("O{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("Q{0}", ccc), string.Format("S{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("V{0}", ccc), string.Format("X{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("Z{0}", ccc), string.Format("AB{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("AD{0}", ccc), string.Format("AF{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("AH{0}", ccc), string.Format("AJ{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("AM{0}", ccc), string.Format("BC{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("AM{0}", ccc), string.Format("AO{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("AQ{0}", ccc), string.Format("AS{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("AU{0}", ccc), string.Format("AW{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("AY{0}", ccc), string.Format("BA{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
currentCell = xlWorkSheet.get_Range(string.Format("C{0}", ccc), string.Format("J{0}", ccc));
currentCell.Group(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
}
Sunday, June 15, 2008
Wednesday, June 4, 2008
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();
}
}
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();
}
}
Subscribe to:
Posts (Atom)