Report in formatted Excel file
#region GenerateExcel
private void Generate_Excel_Report()
{
//update month var to prev month
Prev_rMonth = rMonth - 1;
Prev_rYear = rYear;
if ( Prev_rMonth == 0 )
{
Prev_rMonth = 12;
Prev_rYear = rYear - 1;
}
string fileName = System.IO.Path.GetFileNameWithoutExtension(System.IO.Path.GetTempFileName()) + ".xls";
//Directory to save report into on the server
string Dir = ConfigurationSettings.AppSettings[ "ReportSuiteDownloadDirectory" ].ToString();
// Workbook
Workbook book = new Workbook();
try
{
//Add Styles
#region worksheet styles
//Worksheet header
WorksheetStyle style = book.Styles.Add("HeaderStyle");
style.Font.FontName = "Arial";
style.Font.Size = 14;
style.Font.Bold = true;
style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
style.Font.Color = "Black";
style.Interior.Color = "White";
//Red content
WorksheetStyle HighlightStyle = book.Styles.Add("HighlightStyle");
HighlightStyle.Font.FontName = "Arial";
HighlightStyle.Font.Size = 14;
HighlightStyle.Font.Bold = true;
HighlightStyle.Alignment.Horizontal = StyleHorizontalAlignment.Left;
HighlightStyle.Font.Color = "Black";
HighlightStyle.Interior.Color = "Yellow";
HighlightStyle.Interior.Pattern = StyleInteriorPattern.Solid;
//Group
WorksheetStyle GroupStyle = book.Styles.Add("GroupStyle");
GroupStyle.Font.FontName = "Arial";
GroupStyle.Font.Size = 14;
GroupStyle.Font.Bold = true;
GroupStyle.Alignment.Horizontal = StyleHorizontalAlignment.Left;
GroupStyle.Font.Color = "Black";
GroupStyle.Interior.Color = "#ffcc99";
GroupStyle.Interior.Pattern = StyleInteriorPattern.Solid;
//HongKong
WorksheetStyle HongKongStyle = book.Styles.Add("HongKongStyle");
HongKongStyle.Font.FontName = "Arial";
HongKongStyle.Font.Size = 14;
HongKongStyle.Font.Bold = true;
HongKongStyle.Alignment.Horizontal = StyleHorizontalAlignment.Left;
HongKongStyle.Font.Color = "Black";
HongKongStyle.Interior.Color = "Pink";
HongKongStyle.Interior.Pattern = StyleInteriorPattern.Solid;
//Singapore
WorksheetStyle SingaporeStyle = book.Styles.Add("SingaporeStyle");
SingaporeStyle.Font.FontName = "Arial";
SingaporeStyle.Font.Size = 14;
SingaporeStyle.Font.Bold = true;
SingaporeStyle.Alignment.Horizontal = StyleHorizontalAlignment.Left;
SingaporeStyle.Font.Color = "Black";
SingaporeStyle.Interior.Color = "#cc99ff";
SingaporeStyle.Interior.Pattern = StyleInteriorPattern.Solid;
//Europe
WorksheetStyle EuropeStyle = book.Styles.Add("EuropeStyle");
EuropeStyle.Font.FontName = "Arial";
EuropeStyle.Font.Size = 14;
EuropeStyle.Font.Bold = true;
EuropeStyle.Alignment.Horizontal = StyleHorizontalAlignment.Left;
EuropeStyle.Font.Color = "Black";
EuropeStyle.Interior.Color = "#ccffcc";
EuropeStyle.Interior.Pattern = StyleInteriorPattern.Solid;
//UK
WorksheetStyle UKStyle = book.Styles.Add("UKStyle");
UKStyle.Font.FontName = "Arial";
UKStyle.Font.Size = 14;
UKStyle.Font.Bold = true;
UKStyle.Alignment.Horizontal = StyleHorizontalAlignment.Left;
UKStyle.Font.Color = "Black";
UKStyle.Interior.Color = "#99ccff";
UKStyle.Interior.Pattern = StyleInteriorPattern.Solid;
//Australia
WorksheetStyle AustraliaStyle = book.Styles.Add("AustraliaStyle");
AustraliaStyle.Font.FontName = "Arial";
AustraliaStyle.Font.Size = 14;
AustraliaStyle.Font.Bold = true;
AustraliaStyle.Alignment.Horizontal = StyleHorizontalAlignment.Left;
AustraliaStyle.Font.Color = "Black";
AustraliaStyle.Interior.Color = "#ccffff";
AustraliaStyle.Interior.Pattern = StyleInteriorPattern.Solid;
//Rest of the world
WorksheetStyle ROWStyle = book.Styles.Add("ROWStyle");
ROWStyle.Font.FontName = "Arial";
ROWStyle.Font.Size = 14;
ROWStyle.Font.Bold = true;
ROWStyle.Alignment.Horizontal = StyleHorizontalAlignment.Left;
ROWStyle.Font.Color = "Black";
ROWStyle.Interior.Color = "#b9b7b7";
ROWStyle.Interior.Pattern = StyleInteriorPattern.Solid;
// Create the Default Style to use for everyone
WorksheetStyle Defaultstyle = book.Styles.Add("Default");
Defaultstyle.Font.FontName = "Arial";
Defaultstyle.Font.Size = 12;
Defaultstyle.Font.Bold = true;
// Create a copy of the default style but not bold
WorksheetStyle DefaultNotBold = book.Styles.Add("DefaultNotBold");
DefaultNotBold.Font.FontName = "Arial";
DefaultNotBold.Font.Size = 12;
DefaultNotBold.Font.Bold = false;
#endregion worksheet styles
//Add Worksheets
#region front worksheet
//Worksheet0
Worksheet sheet00 = book.Worksheets.Add("Front Page");
WorksheetRow row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00 = sheet00.Table.Rows.Add();
row00.Cells.Add("");
row00.Cells.Add("");
row00.Cells.Add(@"ADVANTAGE REPORT SUITE FOR " + Request.QueryString["mn"].ToString() + " " + rYear.ToString());
#endregion front worksheet
#region Worksheet - Regional Summary
//Get Data for worksheet
GetReport_Name_and_Sproc(1);
//Update name of sproc variable
ReportSproc = dsReportName_and_Sproc.Tables[0].Rows[0]["report_sproc_vch"].ToString();
GetReportData_GenericSingleTable(rMonth, rYear, ReportSproc, 0, 0);
//Worksheet1
Worksheet sheet0 = book.Worksheets.Add("Regional Summary");
WorksheetRow row0 = sheet0.Table.Rows.Add();
row0.Cells.Add(@"Regional Summary Report");
//define column width for sheet1
//THE BIG 4
sheet0.Table.Columns.Add(new WorksheetColumn(100)); //left header column
sheet0.Table.Columns.Add(new WorksheetColumn(75)); //Reporting retail centres
sheet0.Table.Columns.Add(new WorksheetColumn(50)); //access
sheet0.Table.Columns.Add(new WorksheetColumn(50)); //welcome
sheet0.Table.Columns.Add(new WorksheetColumn(100)); //acknowledgement
sheet0.Table.Columns.Add(new WorksheetColumn(75)); //test drive
sheet0.Table.Columns.Add(new WorksheetColumn(50)); //total
sheet0.Table.Columns.Add(new WorksheetColumn(15)); //spacer
//NPS
sheet0.Table.Columns.Add(new WorksheetColumn(50)); //buyer nps
sheet0.Table.Columns.Add(new WorksheetColumn(75)); //non-buyer nps
sheet0.Table.Columns.Add(new WorksheetColumn(75)); //total nps
sheet0.Table.Columns.Add(new WorksheetColumn(15)); //spacer
//SALES FUNNEL
sheet0.Table.Columns.Add(new WorksheetColumn(50)); //Total traffic
sheet0.Table.Columns.Add(new WorksheetColumn(100)); //traffic per rpt site
sheet0.Table.Columns.Add(new WorksheetColumn(50)); //leads %
sheet0.Table.Columns.Add(new WorksheetColumn(75)); //test drive %
sheet0.Table.Columns.Add(new WorksheetColumn(75)); //conversion from leads %
sheet0.Table.Columns.Add(new WorksheetColumn(75)); //order capture from traffic %
//add new row
WorksheetRow row = sheet0.Table.Rows.Add();
//Main Header row
row = sheet0.Table.Rows.Add();
row.Index = 3;
row.Cells.Add(""); //blank 1st column
WorksheetCell cell = row.Cells.Add("");// + new DateTime(2000,rMonth,1,1,1,1,1).ToString("MMMM") + " ";// + new DateTime(2000,rYear,1,1,1,1,1).ToString("YYYY"));
cell.MergeAcross = 16; // Merge 17 cells together
cell.StyleID = "HeaderStyle";
//Row
//Loop thru results returned by sproc
int total_rows;
int group_count_int;
group_count_int = 0;
total_rows = dsReportDetails.Tables["Table1"].Rows.Count; //get total number of rows in our table
total_rows = total_rows - 1;
for( int current_count = 0; current_count <= total_rows;current_count++ )
{
//if new date period add header columns
if ( dsReportDetails.Tables["Table1"].Rows[current_count][1].ToString() == "Group" )
{
//increment group counter
group_count_int += 1;
//not first (current month) so add some spacer rows
if ( current_count != 0 )
{
row = sheet0.Table.Rows.Add();
row = sheet0.Table.Rows.Add();
}
//Add relevant title
if ( group_count_int == 1 )
{
row = sheet0.Table.Rows.Add();
row.Cells.Add(""); //blank 1st column
WorksheetCell cellz = row.Cells.Add("Regional Summary " + Request.QueryString["mn"].ToString());// + " " + new DateTime(2000,rMonth,1,1,1,1,1).ToString("MMMM") + " ");// + new DateTime(2000,rYear,1,1,1,1,1).ToString("YYYY"));
cellz.MergeAcross = 16; // Merge 17 cells together
cellz.StyleID = "HeaderStyle";
}
if ( group_count_int == 2 )
{
row = sheet0.Table.Rows.Add();
row.Cells.Add(""); //blank 1st column
WorksheetCell celly = row.Cells.Add("Regional Summary " + Request.QueryString["mn"].ToString() + " variance to " + new DateTime(2000,Prev_rMonth,1,1,1,1,1).ToString("MMMM") + " " + Prev_rYear);// + new DateTime(2000,rMonth,1,1,1,1,1).ToString("MMMM") + " ";// + new DateTime(2000,rYear,1,1,1,1,1).ToString("YYYY"));
celly.MergeAcross = 16; // Merge 17 cells together
celly.StyleID = "HeaderStyle";
}
if ( group_count_int == 3 )
{
row = sheet0.Table.Rows.Add();
row.Cells.Add(""); //blank 1st column
WorksheetCell cellx = row.Cells.Add("Regional Summary " + new DateTime(2000,Prev_rMonth,1,1,1,1,1).ToString("MMMM") + " " + Prev_rYear);// + new DateTime(2000,rMonth,1,1,1,1,1).ToString("MMMM") + " ";// + new DateTime(2000,rYear,1,1,1,1,1).ToString("YYYY"));
cellx.MergeAcross = 16; // Merge 17 cells together
cellx.StyleID = "HeaderStyle";
}
//Second Header row
#region second header row
row = sheet0.Table.Rows.Add();
row.Cells.Add(""); //blank 1st column
row.Cells.Add(""); //blank 2nd column
WorksheetCell cella = row.Cells.Add("THE BIG 4");
cella.MergeAcross = 4; // Merge 5 cells together
cella.StyleID = "HeaderStyle";
row.Cells.Add(""); //Spacer
WorksheetCell cellb = row.Cells.Add("NPS");
cellb.MergeAcross = 2;
cellb.StyleID = "HeaderStyle";
row.Cells.Add(""); //Spacer
WorksheetCell cellc = row.Cells.Add("SALES FUNNEL");
cellc.MergeAcross = 5;
cellc.StyleID = "HeaderStyle";
//Third Header row
row = sheet0.Table.Rows.Add();
row.Cells.Add(""); //blank 1st column
row.Cells.Add("Reporting Retail Centres");
row.Cells.Add("Access");
row.Cells.Add("Welcome");
row.Cells.Add("Acknowledgement");
row.Cells.Add("Test Drive");
row.Cells.Add("Total");
row.Cells.Add(""); //Spacer
row.Cells.Add("Buyer");
row.Cells.Add("Non-Buyer");
row.Cells.Add("Total");
row.Cells.Add(""); //Spacer
row.Cells.Add("Total Traffic");
row.Cells.Add("Traffic Per R'pt Site");
row.Cells.Add("Leads %");
row.Cells.Add("Test Drive %");
row.Cells.Add("Conversion From Leads %");
row.Cells.Add("Order Capture From Traffic");
cell.MergeAcross = 4; // Merge 5 cells together
cell.StyleID = "HeaderStyle";
#endregion second header row
}
#region add columns
row = sheet0.Table.Rows.Add();
WorksheetCell cellA1 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][1].ToString()); //group
WorksheetCell cellA2 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][2].ToString()); //centres
WorksheetCell cellA3 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][3].ToString()); //access
WorksheetCell cellA4 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][4].ToString()); //welcome
WorksheetCell cellA5 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][5].ToString()); //acknowledgement
WorksheetCell cellA6 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][6].ToString()); //test drive
WorksheetCell cellA7 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][7].ToString()); //total
WorksheetCell cellA8 = row.Cells.Add(""); //spacer
WorksheetCell cellA9 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][8].ToString()); //buyer
WorksheetCell cellA10 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][9].ToString()); //non-buyer
WorksheetCell cellA11 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][10].ToString()); //total
WorksheetCell cellA12 = row.Cells.Add(""); //spacer
WorksheetCell cellA13 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][11].ToString()); //total traffic
WorksheetCell cellA14 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][12].ToString()); //traffic per site
WorksheetCell cellA15 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][13].ToString()); //leads %
WorksheetCell cellA16 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][14].ToString()); //test drive %
WorksheetCell cellA17 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][15].ToString()); //conversion from leads %
WorksheetCell cellA18 = row.Cells.Add(dsReportDetails.Tables["Table1"].Rows[current_count][16].ToString()); //order capture traffic %
#endregion add columns
//Row Styles
#region apply style
if ( dsReportDetails.Tables["Table1"].Rows[current_count][1].ToString() == "Group" )
{
cellA1.StyleID = "GroupStyle";
cellA2.StyleID = "GroupStyle";
cellA3.StyleID = "GroupStyle";
cellA4.StyleID = "GroupStyle";
cellA5.StyleID = "GroupStyle";
cellA6.StyleID = "GroupStyle";
cellA7.StyleID = "GroupStyle";
cellA8.StyleID = "GroupStyle";
cellA9.StyleID = "GroupStyle";
cellA10.StyleID = "GroupStyle";
cellA11.StyleID = "GroupStyle";
cellA12.StyleID = "GroupStyle";
cellA13.StyleID = "GroupStyle";
cellA14.StyleID = "GroupStyle";
cellA15.StyleID = "GroupStyle";
cellA16.StyleID = "GroupStyle";
cellA17.StyleID = "GroupStyle";
cellA18.StyleID = "GroupStyle";
}
if ( dsReportDetails.Tables["Table1"].Rows[current_count][1].ToString() == "Other Markets" )
{
cellA1.StyleID = "ROWStyle";
cellA2.StyleID = "ROWStyle";
cellA3.StyleID = "ROWStyle";
cellA4.StyleID = "ROWStyle";
cellA5.StyleID = "ROWStyle";
cellA6.StyleID = "ROWStyle";
cellA7.StyleID = "ROWStyle";
cellA8.StyleID = "ROWStyle";
cellA9.StyleID = "ROWStyle";
cellA10.StyleID = "ROWStyle";
cellA11.StyleID = "ROWStyle";
cellA12.StyleID = "ROWStyle";
cellA13.StyleID = "ROWStyle";
cellA14.StyleID = "ROWStyle";
cellA15.StyleID = "ROWStyle";
cellA16.StyleID = "ROWStyle";
cellA17.StyleID = "ROWStyle";
cellA18.StyleID = "ROWStyle";
}
if ( dsReportDetails.Tables["Table1"].Rows[current_count][1].ToString() == "Hong Kong" )
{
cellA1.StyleID = "HongKongStyle";
cellA2.StyleID = "HongKongStyle";
cellA3.StyleID = "HongKongStyle";
cellA4.StyleID = "HongKongStyle";
cellA5.StyleID = "HongKongStyle";
cellA6.StyleID = "HongKongStyle";
cellA7.StyleID = "HongKongStyle";
cellA8.StyleID = "HongKongStyle";
cellA9.StyleID = "HongKongStyle";
cellA10.StyleID = "HongKongStyle";
cellA11.StyleID = "HongKongStyle";
cellA12.StyleID = "HongKongStyle";
cellA13.StyleID = "HongKongStyle";
cellA14.StyleID = "HongKongStyle";
cellA15.StyleID = "HongKongStyle";
cellA16.StyleID = "HongKongStyle";
cellA17.StyleID = "HongKongStyle";
cellA18.StyleID = "HongKongStyle";
}
if ( dsReportDetails.Tables["Table1"].Rows[current_count][1].ToString() == "Singapore" )
{
cellA1.StyleID = "SingaporeStyle";
cellA2.StyleID = "SingaporeStyle";
cellA3.StyleID = "SingaporeStyle";
cellA4.StyleID = "SingaporeStyle";
cellA5.StyleID = "SingaporeStyle";
cellA6.StyleID = "SingaporeStyle";
cellA7.StyleID = "SingaporeStyle";
cellA8.StyleID = "SingaporeStyle";
cellA9.StyleID = "SingaporeStyle";
cellA10.StyleID = "SingaporeStyle";
cellA11.StyleID = "SingaporeStyle";
cellA12.StyleID = "SingaporeStyle";
cellA13.StyleID = "SingaporeStyle";
cellA14.StyleID = "SingaporeStyle";
cellA15.StyleID = "SingaporeStyle";
cellA16.StyleID = "SingaporeStyle";
cellA17.StyleID = "SingaporeStyle";
cellA18.StyleID = "SingaporeStyle";
}
if ( dsReportDetails.Tables["Table1"].Rows[current_count][1].ToString() == "Europe" )
{
cellA1.StyleID = "EuropeStyle";
cellA2.StyleID = "EuropeStyle";
cellA3.StyleID = "EuropeStyle";
cellA4.StyleID = "EuropeStyle";
cellA5.StyleID = "EuropeStyle";
cellA6.StyleID = "EuropeStyle";
cellA7.StyleID = "EuropeStyle";
cellA8.StyleID = "EuropeStyle";
cellA9.StyleID = "EuropeStyle";
cellA10.StyleID = "EuropeStyle";
cellA11.StyleID = "EuropeStyle";
cellA12.StyleID = "EuropeStyle";
cellA13.StyleID = "EuropeStyle";
cellA14.StyleID = "EuropeStyle";
cellA15.StyleID = "EuropeStyle";
cellA16.StyleID = "EuropeStyle";
cellA17.StyleID = "EuropeStyle";
cellA18.StyleID = "EuropeStyle";
}
if ( dsReportDetails.Tables["Table1"].Rows[current_count][1].ToString() == "UK" )
{
cellA1.StyleID = "UKStyle";
cellA2.StyleID = "UKStyle";
cellA3.StyleID = "UKStyle";
cellA4.StyleID = "UKStyle";
cellA5.StyleID = "UKStyle";
cellA6.StyleID = "UKStyle";
cellA7.StyleID = "UKStyle";
cellA8.StyleID = "UKStyle";
cellA9.StyleID = "UKStyle";
cellA10.StyleID = "UKStyle";
cellA11.StyleID = "UKStyle";
cellA12.StyleID = "UKStyle";
cellA13.StyleID = "UKStyle";
cellA14.StyleID = "UKStyle";
cellA15.StyleID = "UKStyle";
cellA16.StyleID = "UKStyle";
cellA17.StyleID = "UKStyle";
cellA18.StyleID = "UKStyle";
}
if ( dsReportDetails.Tables["Table1"].Rows[current_count][1].ToString() == "Australia" )
{
cellA1.StyleID = "AustraliaStyle";
cellA2.StyleID = "AustraliaStyle";
cellA3.StyleID = "AustraliaStyle";
cellA4.StyleID = "AustraliaStyle";
cellA5.StyleID = "AustraliaStyle";
cellA6.StyleID = "AustraliaStyle";
cellA7.StyleID = "AustraliaStyle";
cellA8.StyleID = "AustraliaStyle";
cellA9.StyleID = "AustraliaStyle";
cellA10.StyleID = "AustraliaStyle";
cellA11.StyleID = "AustraliaStyle";
cellA12.StyleID = "AustraliaStyle";
cellA13.StyleID = "AustraliaStyle";
cellA14.StyleID = "AustraliaStyle";
cellA15.StyleID = "AustraliaStyle";
cellA16.StyleID = "AustraliaStyle";
cellA17.StyleID = "AustraliaStyle";
cellA18.StyleID = "AustraliaStyle";
}
#endregion apply style
//apply spacer row
row = sheet0.Table.Rows.Add();
}
#endregion Worksheet - Regional Summary
(...)
}
catch(Exception ex)
{
lblReportTitle.Text = "Excel Report Generation failed: " + ex.Message;
}
//Save the file
try
{
book.Save(Dir + fileName);
//Download generated file
string redirMe = ConfigurationSettings.AppSettings[ "ReportSuiteRedirectURL" ].ToString();
Response.Redirect(redirMe + fileName);
}
catch (Exception ex)
{
lblReportTitle.Text = "Excel Report Save failed: " + ex.Message;
}
//Delete the file
finally
{
//System.IO.File.Delete(Dir + fileName);
}
}
#endregion GenerateExcel