Archives

Archives / 2008 / February
  • AJAX AutoComplete with DataSet

    /// <summary>

    /// This webservice is used with AJAX AutoComplete Extender

    /// to populate possible car makes for textbox on Sell Your Car form

    /// </summary>

    [ScriptService()]

    [WebService(Namespace = "http://tempuri.org/")]

    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

    [ToolboxItem(false)]

    public class MakeAutoComplete : System.Web.Services.WebService

    {

     

        [WebMethod]

        [ScriptMethod()]

        public string[] GetNames(string prefixText, int count)

        {

            ArrayList sampleList = new ArrayList();

            DataSet ds = null;

            SAS sas = new SAS();

            DataFactory factory = new DataFactory();

     

            ds = sas.GetUsedMakes(factory.dbConnection);

     

     

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

            {

                sampleList.Add(ds.Tables[0].Rows[i]["make"]);

     

            }           

     

     

            ArrayList filteredList = new ArrayList();

            foreach (string s in sampleList)

            {

     

                if (s.ToLower().StartsWith(prefixText.ToLower()))

     

                    filteredList.Add(s);

     

            }

            return (string[])filteredList.ToArray(typeof(string));

     

        }

    }

     

    <asp:TextBox ID="txtMake" runat="server" CssClass="textbox"></asp:TextBox>

    <cc1:AutoCompleteExtender

    ID="ACEMake"

    runat="server"

    TargetControlID="txtMake" 

    Enabled="true"

    EnableCaching="true"

    MinimumPrefixLength="1"

    ServiceMethod="GetNames"

    ServicePath="../MakeAutoComplete.asmx" />

    Read more...

  • 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

     

    Read more...

  • Sort a ListBox

    I believe this method is available in .NET 3.5, but in earlier framework versions we have to implement this ourselves..

     

    #region Helper functions

     

        /// <summary>

        /// Sorts the list box in descending order

        /// </summary>

        /// <param name="pList">the list to sort</param>

        /// <param name="pByValue">Sort the list by values or text</param>

    private void sortListBox(ref ListBox pList, bool pByValue)

    {

        SortedList lListItems = new SortedList();

     

        //add listbox items to SortedList

        foreach (ListItem lItem in pList.Items)

        {

            if (pByValue) lListItems.Add(lItem.Value, lItem);

            else lListItems.Add(lItem.Text, lItem);

        }

     

        //clear list box

        pList.Items.Clear();

     

        //add sorted items to listbox

        for (int i = 0; i < lListItems.Count; i++)

        {

            pList.Items.Add((ListItem)lListItems[lListItems.GetKey(i)]);

        }

    }

     

        #endregion

    }

    Read more...

  • CSV to TextReader

    /// <summary>

    /// Take a file path and return a TextReader

    /// </summary>

    /// <param name="file_path"></param>

    /// <returns></returns>

    private TextReader OpenFile (string file_path)

    {

        try

        {

            // Read the CSV file in to a TextReader

            TextReader _rdr = File.OpenText(file_path);

            // Set file attributes

            File.SetAttributes(file_path, FileAttributes.Normal);

     

            return _rdr;

        }

        catch (Exception)

        {

            throw new Exception(String.Format(

                    "Error trying to open file {0}. Check file exists and is accessible<br /><br />" +

                    "Possible Issues:<br/><br />Files cannot be accessed via mapped network drives" +

                    "<br />The file is open<br />The file has been viewed and then saved in Excel",file_path));

        }

    }

    Read more...

  • AJAX UpdatePanel triggers, Conditional update mode

    First of all I'd like to thank kowalskec for pointing me out the CopySourceasHTML VS add-in that will help me copy the code snippets from Visual Studio here without losing any text formatting.

    Cheers buddy! =)

     

    And now the task:

    • The BugsGridView GridView control and BugsListTimeLabel Label in the Default.aspx page are encapsulated inside an UpdatePanel with an ID of BugsListUpdatePanel. The UpdatePanel does not update when other UpdatePanels on the page generate postbacks.
    • The ActivityLabel Label control in the Default.aspx page is encapsulated inside an UpdatePanel with an ID of ActivityUpdatePanel. The UpdatePanel does not update when other UpdatePanels on the page generate postbacks. This UpdatePanel does not contain any other controls.
    • The ActivityUpdatePanel UpdatePanel control in the Default.aspx page defines a trigger which causes the UpdatePanel to asynchronously refresh when the Click event of the ClearButton Button is raised.
    • The BugsGridView_RowUpdated event handler in the _Default partial class refreshes the ActivityUpdatePanel UpdatePanel if changes have been made to the BugsGridView GridView control.

    This will help me remember how to make the UpdatePanel do a postback by clicking a button outside of the panel, as well as call the UpdatePanel Update() method from code behind.

    <!--

    Done:

    The Default.aspx page contains a ScriptManager control with an ID of

    BugsPageScriptManager.

    -->

    <asp:ScriptManager ID="BugsPageScriptManager" runat="server" EnablePartialRendering="true" />

    <!--

    Done:

    The BugsGridView GridView control and BugsListTimeLabel Label in the

    Default.aspx page are encapsulated inside an UpdatePanel with an ID of

    BugsListUpdatePanel. The UpdatePanel does not update when other

    UpdatePanels on the page generate postbacks.

    -->

    <asp:UpdatePanel ID="BugsListUpdatePanel" runat="server" UpdateMode="Conditional">

    <ContentTemplate>

    Bugs as of:

    <asp:Label ID="BugsListTimeLabel" runat="server" Text="" />

    <asp:GridView ID="BugsGridView" runat="server" AutoGenerateColumns="False" AutoGenerateEditButton="True"

    DataSourceID="BugsDataSource" OnRowUpdated="BugsGridView_RowUpdated">

    <Columns>...</Columns>

    </asp:GridView>

    </ContentTemplate>

    </asp:UpdatePanel>

    <!--

    Done:

    The ActivityLabel Label control in the Default.aspx page is encapsulated

    inside an UpdatePanel with an ID of ActivityUpdatePanel. The UpdatePanel

    does not update when other UpdatePanels on the page generate postbacks.

    This UpdatePanel does not contain any other controls.

    -->

    <!--

    Done:

    The ActivityUpdatePanel UpdatePanel control in the Default.aspx page

    defines a trigger which causes the UpdatePanel to asynchronously refresh

    when the Click event of the ClearButton Button is raised.

    -->

    <asp:UpdatePanel ID="ActivityUpdatePanel" runat="server" UpdateMode="Conditional">

    <ContentTemplate>

    <asp:Label ID="ActivityLabel" runat="server" />

    </ContentTemplate>

    <Triggers>

    <asp:AsyncPostBackTrigger ControlID="ClearButton" EventName="Click" />

    </Triggers>

    </asp:UpdatePanel>

     

    <asp:Button ID="ClearButton" runat="server" Text="Clear"

    OnClick="ClearButton_Click" />

     

        protected void BugsGridView_RowUpdated(object sender, GridViewUpdatedEventArgs e)

        {

            if (CheckForChanges(e, "Description", "Status", "AssignedTo"))

            {

                // Done:

                // The BugsGridView_RowUpdated event handler in the _Default partial class

                //  refreshes the ActivityUpdatePanel UpdatePanel if changes have been made

                //  to the BugsGridView GridView control.

     

                ActivityUpdatePanel.Update();

            }

        }

    Read more...

  • Add new Blog Post WYSIWYG horrors

    First of all, I am not even talking about FireFox or Opera, it's  IE6 what I try to use to make a new post. I copy-paste the code from Visual Studion or even do it through Word, but there's no way I can get the same look of code snippets:

     This is copied straight from VS:

    /// <summary>

    /// Initialises webpage.

    /// </summary>

    /// <param name="sender"></param>

    /// <param name="e"></param>

    protected void Page_Load(object sender, System.EventArgs e)

    {

    ...

    {

    }

    else

    {

    ...

    }

    }

     

    And this - VS -> Word -> Here:

                /// <summary>            /// Initialises webpage.            /// </summary>            /// <param name="sender"></param>            /// <param name="e"></param>            protected void Page_Load(object sender, System.EventArgs e)            {            DateTime theDate = DateTime.Parse("1 January 2007");                  if( !IsPostBack )                  {      }                  else                  {                                          }

                }

    The 1st is too much spacing between lines, 2nd looks OK even when I do a Preview here, I do get what I see, but, once I post it, it would wrap everything horribly, so in fact What You See Is Not What You Get (WYSINWYG)

     =(

    Read more...

  • .NET ArrayList of Objects

    /// <summary>

    /// public class.

    /// </summary>

    public class DealerShip

    {

     ...

    /// <summary>

    /// Franchise object

    /// </summary>

    public class objFranchise

    {

    public string Name;

    public bool IsSales;

    public bool IsService;

    }

    /// <summary>

    /// public ArrayList

    /// </summary>

    public ArrayList FranchiseListDetailed

    {

    get

    {

    if (m_franchies_functions == null)

    m_franchies_functions = new ArrayList();

    return this.m_franchies_functions;

    }

    }

     

    /// <summary>

    /// private ArrayList

    /// </summary>

    private ArrayList m_franchies;

     ...

    }

    objFranchise Franchise = null;

    //Update franchise

    for (int i = 0; i < gvFranchises.Rows.Count; i++)

    {

    // a new instance of class

    Franchise = new objFranchise();

    Franchise.Name = ((Label)gvFranchises.Rows[i].FindControl("Franchise")).Text;

    Franchise.IsSales = ((CheckBox)gvFranchises.Rows[i].FindControl("Sales")).Checked; Franchise.IsService = ((CheckBox)gvFranchises.Rows[i].FindControl("Service")).Checked;

    //add to old franchise list - it might be used used elsewhere

    dealership.FranchiseList.Add(Franchise.Name);

    //add to local array list

    dealership.FranchiseListDetailed.Add(Franchise);

    }

    Read more...