Saturday, February 21, 2015

read html data

using HtmlAgilityPack;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.ServiceModel.Syndication;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Windows.Threading;
using System.Xml;
using System.Xml.Linq;


namespace CraiglistReader
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        BackgroundWorker worker = new BackgroundWorker();
        List<sites> sbtext = new List<sites>();
        List<Cities> cities = new List<Cities>();
        public MainWindow()
        {
            InitializeComponent();


        }
       
     

     

        static List<Data> FindRow(string url,string input)
        {
            List<Data> _data = new List<Data>();
            try
            {
                string pattern = "<p class=.*?>(.*?)<\\/p>";
                MatchCollection matches = Regex.Matches(input, pattern);
                Console.WriteLine("Matches found: {0}", matches.Count);

                if (matches.Count > 0)
                    foreach (Match m in matches)
                    {
                        string datapidpattern = "<a href=.*?>(.*?)<\\/a>";
                        MatchCollection matchesinner = Regex.Matches(m.ToString(), datapidpattern);

                        if (matchesinner.Count > 0)
                        {
                            foreach (Match mi in matchesinner)
                            {
                                if (mi.ToString().Contains("hdrlnk"))
                                {
                                    string title = mi.Value.Split(new char[] { '>', '<' })[2];
                                    string pid = mi.Value.Split(new char[] { '<', ' ' })[2].Remove(0, 6);
                                    pid = pid.Remove(pid.Length - 2, 2);
                                    //if (mi.Value.Split(new char[] { "<a href=\", 'html\' })[4].ToString() == "ca") pid = mi.Value.Split(new char[] { '/', '.' })[7];
                                    pid = pid.Replace(".htm", ".html");
                                    string tempurl = url;
                                    if (!pid.Contains("http://"))
                                    {
                                        tempurl = tempurl.Remove(url.Length - 5, 5);
                                        pid = tempurl + pid;
                                    }
                                    // pid = url + pid + ".html";
                                    _data.Add(new Data() { Title = title.ToString(), Link = pid.Replace("//", "/").Replace("http:/", "http://") });
                                }
                            }
                        }

                    }
            }catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            return _data;
        }
        private delegate void UpdateProgressBarDelegate(System.Windows.DependencyProperty dp, Object value);
        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            try
            {
             
                btnSearch.IsEnabled = false;
                LoadUSCities();
                //LoadCanadaCities();
                //Configure the ProgressBar
                ProgressBar1.Minimum = 0;
                ProgressBar1.Maximum = short.MaxValue;
                ProgressBar1.Value = 0;

                //Stores the value of the ProgressBar
                double value = 0;

                //Create a new instance of our ProgressBar Delegate that points
                //  to the ProgressBar's SetValue method.
                UpdateProgressBarDelegate updatePbDelegate = new UpdateProgressBarDelegate(ProgressBar1.SetValue);
                string search = "is_parttime=1";
                if (chktelecommute.IsChecked == true && chktelecommute.IsChecked == false) search = "is_telecommuting=1";
                if (chktelecommute.IsChecked == false && chktelecommute.IsChecked == true) search = "is_parttime=1";
                if (chkparttime.IsChecked == true && chktelecommute.IsChecked == true) search = "is_parttime=1&is_telecommuting=1";
                // search = "part-time";
                List<Data> ltsData = new List<Data>();
                Action workAction = delegate
                {
                    worker = new BackgroundWorker();
                    worker.DoWork += delegate
                    {
                        List<Data> lst = new List<Data>();
                        foreach (Cities str in cities)
                        {
                            try
                            {

                                readhtml(str.Url.ToString() + "/search/sof?" + search, search, str.Url.ToString()).ToList().ForEach(x =>
                                    {
                                        lst.Add(x);
                                    });
                             
                             
                            }
                            catch (Exception ex)
                            {
                                MessageBox.Show(ex.Message);
                            }
                        }
                        if (lst.Count > 0)
                        {
                            lst.ForEach(x =>
                            {
                                ltsData.Add(x);
                                // lblSearch.Content = x;
                            });
                        }
                    };
                    worker.RunWorkerCompleted += delegate
                    {

                        dg.ItemsSource = ltsData.GroupBy(p=>p.Title).ToList();
                        btnSearch.IsEnabled = true;
                    };

                    worker.RunWorkerAsync();
                };
                Dispatcher.Invoke(DispatcherPriority.Background, workAction);
             
         
            }catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                btnSearch.IsEnabled = true;
            }
         
        }

        private List<Data> ReadFeed( string url)
        {
            try
            {
                List<Data> lst = new List<Data>();
                XmlDocument doc = new XmlDocument();

                XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
                nsmgr.AddNamespace("rdf", "http://www.w3.org/1999/02/22-rdf-syntax-ns#");
                nsmgr.AddNamespace("rss", "http://purl.org/rss/1.0/");

                XmlTextReader reader = new XmlTextReader(url);

                doc.Load(reader);

                XmlNodeList nodes = doc.SelectNodes("/rdf:RDF//rss:item", nsmgr);

                foreach (XmlNode node in nodes)
                {
                    XmlNodeList titleNode = node.SelectNodes("rss:title", nsmgr);
                    XmlNodeList descNode = node.SelectNodes("rss:description", nsmgr);
                    XmlNodeList linkNode = node.SelectNodes("rss:link", nsmgr);

                    string title = titleNode.Count == 0 ? "" : titleNode[0].InnerText;
                    string desc = descNode.Count == 0 ? "" : descNode[0].InnerText;
                    string link = linkNode.Count == 0 ? "" : linkNode[0].InnerText;

                    // TODO

                    // read html page
                    List<string> results = new List<string>();
                    WebClient web = new WebClient();
                    System.IO.Stream stream = web.OpenRead(link);
                    using (System.IO.StreamReader reader1 = new System.IO.StreamReader(stream))
                    {
                        String text = reader1.ReadToEnd();
                        if (text.Contains("telecommuting"))
                        {
                            lst.Add(new Data() { Title = title, Link = link, Description = desc });
                        }
                    }
                }
                return lst;
            }
            catch (Exception ex)
            { }
            return null;
        }

        private void CheckBox_Checked(object sender, RoutedEventArgs e)
        {

        }
        void LoadUSCities()
        {
            cities.Add(new Cities() { Url = "http://abilene.craigslist.org" });
            cities.Add(new Cities() { Url = "http://akroncanton.craigslist.org" });
            cities.Add(new Cities() { Url = "http://albanyga.craigslist.org" });
            cities.Add(new Cities() { Url = "http://albany.craigslist.org" });
            cities.Add(new Cities() { Url = "http://albuquerque.craigslist.org" });
            cities.Add(new Cities() { Url = "http://altoona.craigslist.org" });
            cities.Add(new Cities() { Url = "http://amarillo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://ames.craigslist.org" });
            cities.Add(new Cities() { Url = "http://anchorage.craigslist.org" });
            cities.Add(new Cities() { Url = "http://annapolis.craigslist.org" });
            cities.Add(new Cities() { Url = "http://annarbor.craigslist.org" });
            cities.Add(new Cities() { Url = "http://appleton.craigslist.org" });
            cities.Add(new Cities() { Url = "http://asheville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://ashtabula.craigslist.org" });
            cities.Add(new Cities() { Url = "http://athensga.craigslist.org" });
            cities.Add(new Cities() { Url = "http://athensohio.craigslist.org" });
            cities.Add(new Cities() { Url = "http://atlanta.craigslist.org" });
            cities.Add(new Cities() { Url = "http://auburn.craigslist.org" });
            cities.Add(new Cities() { Url = "http://augusta.craigslist.org" });
            cities.Add(new Cities() { Url = "http://austin.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bakersfield.craigslist.org" });
            cities.Add(new Cities() { Url = "http://baltimore.craigslist.org" });
            cities.Add(new Cities() { Url = "http://batonrouge.craigslist.org" });
            cities.Add(new Cities() { Url = "http://battlecreek.craigslist.org" });
            cities.Add(new Cities() { Url = "http://beaumont.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bellingham.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bemidji.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bend.craigslist.org" });
            cities.Add(new Cities() { Url = "http://billings.craigslist.org" });
            cities.Add(new Cities() { Url = "http://binghamton.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bham.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bismarck.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bloomington.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bn.craigslist.org" });
            cities.Add(new Cities() { Url = "http://boise.craigslist.org" });
            cities.Add(new Cities() { Url = "http://boone.craigslist.org" });
            cities.Add(new Cities() { Url = "http://boston.craigslist.org" });
            cities.Add(new Cities() { Url = "http://boulder.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bgky.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bozeman.craigslist.org" });
            cities.Add(new Cities() { Url = "http://brainerd.craigslist.org" });
            cities.Add(new Cities() { Url = "http://brownsville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://brunswick.craigslist.org" });
            cities.Add(new Cities() { Url = "http://buffalo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://butte.craigslist.org" });
            cities.Add(new Cities() { Url = "http://capecod.craigslist.org" });
            cities.Add(new Cities() { Url = "http://catskills.craigslist.org" });
            cities.Add(new Cities() { Url = "http://cedarrapids.craigslist.org" });
            cities.Add(new Cities() { Url = "http://cenla.craigslist.org" });
            cities.Add(new Cities() { Url = "http://centralmich.craigslist.org" });
            cities.Add(new Cities() { Url = "http://cnj.craigslist.org" });
            cities.Add(new Cities() { Url = "http://chambana.craigslist.org" });
            cities.Add(new Cities() { Url = "http://charleston.craigslist.org" });
            cities.Add(new Cities() { Url = "http://charlestonwv.craigslist.org" });
            cities.Add(new Cities() { Url = "http://charlotte.craigslist.org" });
            cities.Add(new Cities() { Url = "http://charlottesville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://chattanooga.craigslist.org" });
            cities.Add(new Cities() { Url = "http://chautauqua.craigslist.org" });
            cities.Add(new Cities() { Url = "http://chicago.craigslist.org" });
            cities.Add(new Cities() { Url = "http://chico.craigslist.org" });
            cities.Add(new Cities() { Url = "http://chillicothe.craigslist.org" });
            cities.Add(new Cities() { Url = "http://cincinnati.craigslist.org" });
            cities.Add(new Cities() { Url = "http://clarksville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://cleveland.craigslist.org" });
            cities.Add(new Cities() { Url = "http://clovis.craigslist.org" });
            cities.Add(new Cities() { Url = "http://collegestation.craigslist.org" });
            cities.Add(new Cities() { Url = "http://cosprings.craigslist.org" });
            cities.Add(new Cities() { Url = "http://columbiamo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://columbia.craigslist.org" });
            cities.Add(new Cities() { Url = "http://columbusga.craigslist.org" });
            cities.Add(new Cities() { Url = "http://columbus.craigslist.org" });
            cities.Add(new Cities() { Url = "http://cookeville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://corpuschristi.craigslist.org" });
            cities.Add(new Cities() { Url = "http://corvallis.craigslist.org" });
            cities.Add(new Cities() { Url = "http://chambersburg.craigslist.org" });
            cities.Add(new Cities() { Url = "http://dallas.craigslist.org" });
            cities.Add(new Cities() { Url = "http://danville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://daytona.craigslist.org" });
            cities.Add(new Cities() { Url = "http://dayton.craigslist.org" });
            cities.Add(new Cities() { Url = "http://decatur.craigslist.org" });
            cities.Add(new Cities() { Url = "http://nacogdoches.craigslist.org" });
            cities.Add(new Cities() { Url = "http://delaware.craigslist.org" });
            cities.Add(new Cities() { Url = "http://delrio.craigslist.org" });
            cities.Add(new Cities() { Url = "http://denver.craigslist.org" });
            cities.Add(new Cities() { Url = "http://desmoines.craigslist.org" });
            cities.Add(new Cities() { Url = "http://detroit.craigslist.org" });
            cities.Add(new Cities() { Url = "http://dothan.craigslist.org" });
            cities.Add(new Cities() { Url = "http://dubuque.craigslist.org" });
            cities.Add(new Cities() { Url = "http://duluth.craigslist.org" });
            cities.Add(new Cities() { Url = "http://eastco.craigslist.org" });
            cities.Add(new Cities() { Url = "http://newlondon.craigslist.org" });
            cities.Add(new Cities() { Url = "http://eastky.craigslist.org" });
            cities.Add(new Cities() { Url = "http://montana.craigslist.org" });
            cities.Add(new Cities() { Url = "http://eastnc.craigslist.org" });
            cities.Add(new Cities() { Url = "http://martinsburg.craigslist.org" });
            cities.Add(new Cities() { Url = "http://easternshore.craigslist.org" });
            cities.Add(new Cities() { Url = "http://eastidaho.craigslist.org" });
            cities.Add(new Cities() { Url = "http://eastoregon.craigslist.org" });
            cities.Add(new Cities() { Url = "http://eauclaire.craigslist.org" });
            cities.Add(new Cities() { Url = "http://elko.craigslist.org" });
            cities.Add(new Cities() { Url = "http://elmira.craigslist.org" });
            cities.Add(new Cities() { Url = "http://elpaso.craigslist.org" });
            cities.Add(new Cities() { Url = "http://erie.craigslist.org" });
            cities.Add(new Cities() { Url = "http://eugene.craigslist.org" });
            cities.Add(new Cities() { Url = "http://evansville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fairbanks.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fargo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://farmington.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fayar.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fayetteville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fingerlakes.craigslist.org" });
            cities.Add(new Cities() { Url = "http://flagstaff.craigslist.org" });
            cities.Add(new Cities() { Url = "http://flint.craigslist.org" });
            cities.Add(new Cities() { Url = "http://shoals.craigslist.org" });
            cities.Add(new Cities() { Url = "http://florencesc.craigslist.org" });
            cities.Add(new Cities() { Url = "http://keys.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fortcollins.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fortdodge.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fortsmith.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fortwayne.craigslist.org" });
            cities.Add(new Cities() { Url = "http://frederick.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fredericksburg.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fresno.craigslist.org" });
            cities.Add(new Cities() { Url = "http://fortmyers.craigslist.org" });
            cities.Add(new Cities() { Url = "http://gadsden.craigslist.org" });
            cities.Add(new Cities() { Url = "http://gainesville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://galveston.craigslist.org" });
            cities.Add(new Cities() { Url = "http://glensfalls.craigslist.org" });
            cities.Add(new Cities() { Url = "http://goldcountry.craigslist.org" });
            cities.Add(new Cities() { Url = "http://grandforks.craigslist.org" });
            cities.Add(new Cities() { Url = "http://grandisland.craigslist.org" });
            cities.Add(new Cities() { Url = "http://grandrapids.craigslist.org" });
            cities.Add(new Cities() { Url = "http://greatfalls.craigslist.org" });
            cities.Add(new Cities() { Url = "http://greenbay.craigslist.org" });
            cities.Add(new Cities() { Url = "http://greensboro.craigslist.org" });
            cities.Add(new Cities() { Url = "http://greenville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://gulfport.craigslist.org" });
            cities.Add(new Cities() { Url = "http://norfolk.craigslist.org" });
            cities.Add(new Cities() { Url = "http://hanford.craigslist.org" });
            cities.Add(new Cities() { Url = "http://harrisburg.craigslist.org" });
            cities.Add(new Cities() { Url = "http://harrisonburg.craigslist.org" });
            cities.Add(new Cities() { Url = "http://hartford.craigslist.org" });
            cities.Add(new Cities() { Url = "http://hattiesburg.craigslist.org" });
            cities.Add(new Cities() { Url = "http://honolulu.craigslist.org" });
            cities.Add(new Cities() { Url = "http://cfl.craigslist.org" });
            cities.Add(new Cities() { Url = "http://helena.craigslist.org" });
            cities.Add(new Cities() { Url = "http://hickory.craigslist.org" });
            cities.Add(new Cities() { Url = "http://rockies.craigslist.org" });
            cities.Add(new Cities() { Url = "http://hiltonhead.craigslist.org" });
            cities.Add(new Cities() { Url = "http://holland.craigslist.org" });
            cities.Add(new Cities() { Url = "http://houma.craigslist.org" });
            cities.Add(new Cities() { Url = "http://houston.craigslist.org" });
            cities.Add(new Cities() { Url = "http://hudsonvalley.craigslist.org" });
            cities.Add(new Cities() { Url = "http://humboldt.craigslist.org" });
            cities.Add(new Cities() { Url = "http://huntington.craigslist.org" });
            cities.Add(new Cities() { Url = "http://huntsville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://imperial.craigslist.org" });
            cities.Add(new Cities() { Url = "http://indianapolis.craigslist.org" });
            cities.Add(new Cities() { Url = "http://inlandempire.craigslist.org" });
            cities.Add(new Cities() { Url = "http://iowacity.craigslist.org" });
            cities.Add(new Cities() { Url = "http://ithaca.craigslist.org" });
            cities.Add(new Cities() { Url = "http://jxn.craigslist.org" });
            cities.Add(new Cities() { Url = "http://jackson.craigslist.org" });
            cities.Add(new Cities() { Url = "http://jacksontn.craigslist.org" });
            cities.Add(new Cities() { Url = "http://jacksonville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://onslow.craigslist.org" });
            cities.Add(new Cities() { Url = "http://janesville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://jerseyshore.craigslist.org" });
            cities.Add(new Cities() { Url = "http://jonesboro.craigslist.org" });
            cities.Add(new Cities() { Url = "http://joplin.craigslist.org" });
            cities.Add(new Cities() { Url = "http://kalamazoo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://kalispell.craigslist.org" });
            cities.Add(new Cities() { Url = "http://kansascity.craigslist.org" });
            cities.Add(new Cities() { Url = "http://kenai.craigslist.org" });
            cities.Add(new Cities() { Url = "http://kpr.craigslist.org" });
            cities.Add(new Cities() { Url = "http://racine.craigslist.org" });
            cities.Add(new Cities() { Url = "http://killeen.craigslist.org" });
            cities.Add(new Cities() { Url = "http://kirksville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://klamath.craigslist.org" });
            cities.Add(new Cities() { Url = "http://knoxville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://kokomo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lacrosse.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lafayette.craigslist.org" });
            cities.Add(new Cities() { Url = "http://tippecanoe.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lakecharles.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lakeland.craigslist.org" });
            cities.Add(new Cities() { Url = "http://loz.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lancaster.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lansing.craigslist.org" });
            cities.Add(new Cities() { Url = "http://laredo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lasalle.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lascruces.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lasvegas.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lawrence.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lawton.craigslist.org" });
            cities.Add(new Cities() { Url = "http://allentown.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lewiston.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lexington.craigslist.org" });
            cities.Add(new Cities() { Url = "http://limaohio.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lincoln.craigslist.org" });
            cities.Add(new Cities() { Url = "http://littlerock.craigslist.org" });
            cities.Add(new Cities() { Url = "http://logan.craigslist.org" });
            cities.Add(new Cities() { Url = "http://longisland.craigslist.org" });
            cities.Add(new Cities() { Url = "http://losangeles.craigslist.org" });
            cities.Add(new Cities() { Url = "http://louisville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lubbock.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lynchburg.craigslist.org" });
            cities.Add(new Cities() { Url = "http://macon.craigslist.org" });
            cities.Add(new Cities() { Url = "http://madison.craigslist.org" });
            cities.Add(new Cities() { Url = "http://maine.craigslist.org" });
            cities.Add(new Cities() { Url = "http://ksu.craigslist.org" });
            cities.Add(new Cities() { Url = "http://mankato.craigslist.org" });
            cities.Add(new Cities() { Url = "http://mansfield.craigslist.org" });
            cities.Add(new Cities() { Url = "http://masoncity.craigslist.org" });
            cities.Add(new Cities() { Url = "http://mattoon.craigslist.org" });
            cities.Add(new Cities() { Url = "http://mcallen.craigslist.org" });
            cities.Add(new Cities() { Url = "http://meadville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://medford.craigslist.org" });
            cities.Add(new Cities() { Url = "http://memphis.craigslist.org" });
            cities.Add(new Cities() { Url = "http://mendocino.craigslist.org" });
            cities.Add(new Cities() { Url = "http://merced.craigslist.org" });
            cities.Add(new Cities() { Url = "http://meridian.craigslist.org" });
            cities.Add(new Cities() { Url = "http://milwaukee.craigslist.org" });
            cities.Add(new Cities() { Url = "http://minneapolis.craigslist.org" });
            cities.Add(new Cities() { Url = "http://missoula.craigslist.org" });
            cities.Add(new Cities() { Url = "http://mobile.craigslist.org" });
            cities.Add(new Cities() { Url = "http://modesto.craigslist.org" });
            cities.Add(new Cities() { Url = "http://mohave.craigslist.org" });
            cities.Add(new Cities() { Url = "http://monroe.craigslist.org" });
            cities.Add(new Cities() { Url = "http://monroemi.craigslist.org" });
            cities.Add(new Cities() { Url = "http://monterey.craigslist.org" });
            cities.Add(new Cities() { Url = "http://montgomery.craigslist.org" });
            cities.Add(new Cities() { Url = "http://morgantown.craigslist.org" });
            cities.Add(new Cities() { Url = "http://moseslake.craigslist.org" });
            cities.Add(new Cities() { Url = "http://muncie.craigslist.org" });
            cities.Add(new Cities() { Url = "http://muskegon.craigslist.org" });
            cities.Add(new Cities() { Url = "http://myrtlebeach.craigslist.org" });
            cities.Add(new Cities() { Url = "http://nashville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://nh.craigslist.org" });
            cities.Add(new Cities() { Url = "http://newhaven.craigslist.org" });
            cities.Add(new Cities() { Url = "http://neworleans.craigslist.org" });
            cities.Add(new Cities() { Url = "http://blacksburg.craigslist.org" });
            cities.Add(new Cities() { Url = "http://newyork.craigslist.org" });
            cities.Add(new Cities() { Url = "http://lakecity.craigslist.org" });
            cities.Add(new Cities() { Url = "http://nd.craigslist.org" });
            cities.Add(new Cities() { Url = "http://nesd.craigslist.org" });
            cities.Add(new Cities() { Url = "http://nmi.craigslist.org" });
            cities.Add(new Cities() { Url = "http://wheeling.craigslist.org" });
            cities.Add(new Cities() { Url = "http://northernwi.craigslist.org" });
            cities.Add(new Cities() { Url = "http://newjersey.craigslist.org" });
            cities.Add(new Cities() { Url = "http://northmiss.craigslist.org" });
            cities.Add(new Cities() { Url = "http://northplatte.craigslist.org" });
            cities.Add(new Cities() { Url = "http://nwct.craigslist.org" });
            cities.Add(new Cities() { Url = "http://nwga.craigslist.org" });
            cities.Add(new Cities() { Url = "http://nwks.craigslist.org" });
            cities.Add(new Cities() { Url = "http://enid.craigslist.org" });
            cities.Add(new Cities() { Url = "http://ocala.craigslist.org" });
            cities.Add(new Cities() { Url = "http://odessa.craigslist.org" });
            cities.Add(new Cities() { Url = "http://ogden.craigslist.org" });
            cities.Add(new Cities() { Url = "http://okaloosa.craigslist.org" });
            cities.Add(new Cities() { Url = "http://oklahomacity.craigslist.org" });
            cities.Add(new Cities() { Url = "http://olympic.craigslist.org" });
            cities.Add(new Cities() { Url = "http://omaha.craigslist.org" });
            cities.Add(new Cities() { Url = "http://oneonta.craigslist.org" });
            cities.Add(new Cities() { Url = "http://orangecounty.craigslist.org" });
            cities.Add(new Cities() { Url = "http://oregoncoast.craigslist.org" });
            cities.Add(new Cities() { Url = "http://orlando.craigslist.org" });
            cities.Add(new Cities() { Url = "http://outerbanks.craigslist.org" });
            cities.Add(new Cities() { Url = "http://owensboro.craigslist.org" });
            cities.Add(new Cities() { Url = "http://palmsprings.craigslist.org" });
            cities.Add(new Cities() { Url = "http://panamacity.craigslist.org" });
            cities.Add(new Cities() { Url = "http://parkersburg.craigslist.org" });
            cities.Add(new Cities() { Url = "http://pensacola.craigslist.org" });
            cities.Add(new Cities() { Url = "http://peoria.craigslist.org" });
            cities.Add(new Cities() { Url = "http://philadelphia.craigslist.org" });
            cities.Add(new Cities() { Url = "http://phoenix.craigslist.org" });
            cities.Add(new Cities() { Url = "http://csd.craigslist.org" });
            cities.Add(new Cities() { Url = "http://pittsburgh.craigslist.org" });
            cities.Add(new Cities() { Url = "http://plattsburgh.craigslist.org" });
            cities.Add(new Cities() { Url = "http://poconos.craigslist.org" });
            cities.Add(new Cities() { Url = "http://porthuron.craigslist.org" });
            cities.Add(new Cities() { Url = "http://portland.craigslist.org" });
            cities.Add(new Cities() { Url = "http://potsdam.craigslist.org" });
            cities.Add(new Cities() { Url = "http://prescott.craigslist.org" });
            cities.Add(new Cities() { Url = "http://provo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://pueblo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://pullman.craigslist.org" });
            cities.Add(new Cities() { Url = "http://quadcities.craigslist.org" });
            cities.Add(new Cities() { Url = "http://raleigh.craigslist.org" });
            cities.Add(new Cities() { Url = "http://rapidcity.craigslist.org" });
            cities.Add(new Cities() { Url = "http://reading.craigslist.org" });
            cities.Add(new Cities() { Url = "http://redding.craigslist.org" });
            cities.Add(new Cities() { Url = "http://reno.craigslist.org" });
            cities.Add(new Cities() { Url = "http://providence.craigslist.org" });
            cities.Add(new Cities() { Url = "http://richmondin.craigslist.org" });
            cities.Add(new Cities() { Url = "http://richmond.craigslist.org" });
            cities.Add(new Cities() { Url = "http://roanoke.craigslist.org" });
            cities.Add(new Cities() { Url = "http://rmn.craigslist.org" });
            cities.Add(new Cities() { Url = "http://rochester.craigslist.org" });
            cities.Add(new Cities() { Url = "http://rockford.craigslist.org" });
            cities.Add(new Cities() { Url = "http://roseburg.craigslist.org" });
            cities.Add(new Cities() { Url = "http://roswell.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sacramento.craigslist.org" });
            cities.Add(new Cities() { Url = "http://saginaw.craigslist.org" });
            cities.Add(new Cities() { Url = "http://salem.craigslist.org" });
            cities.Add(new Cities() { Url = "http://salina.craigslist.org" });
            cities.Add(new Cities() { Url = "http://saltlakecity.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sanangelo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sanantonio.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sandiego.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sandusky.craigslist.org" });
            cities.Add(new Cities() { Url = "http://slo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sanmarcos.craigslist.org" });
            cities.Add(new Cities() { Url = "http://santabarbara.craigslist.org" });
            cities.Add(new Cities() { Url = "http://santafe.craigslist.org" });
            cities.Add(new Cities() { Url = "http://santamaria.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sarasota.craigslist.org" });
            cities.Add(new Cities() { Url = "http://savannah.craigslist.org" });
            cities.Add(new Cities() { Url = "http://scottsbluff.craigslist.org" });
            cities.Add(new Cities() { Url = "http://scranton.craigslist.org" });
            cities.Add(new Cities() { Url = "http://seattle.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sfbay.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sheboygan.craigslist.org" });
            cities.Add(new Cities() { Url = "http://showlow.craigslist.org" });
            cities.Add(new Cities() { Url = "http://shreveport.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sierravista.craigslist.org" });
            cities.Add(new Cities() { Url = "http://siouxcity.craigslist.org" });
            cities.Add(new Cities() { Url = "http://siouxfalls.craigslist.org" });
            cities.Add(new Cities() { Url = "http://siskiyou.craigslist.org" });
            cities.Add(new Cities() { Url = "http://skagit.craigslist.org" });
            cities.Add(new Cities() { Url = "http://southbend.craigslist.org" });
            cities.Add(new Cities() { Url = "http://southcoast.craigslist.org" });
            cities.Add(new Cities() { Url = "http://sd.craigslist.org" });
            cities.Add(new Cities() { Url = "http://juneau.craigslist.org" });
            cities.Add(new Cities() { Url = "http://ottumwa.craigslist.org" });
            cities.Add(new Cities() { Url = "http://seks.craigslist.org" });
            cities.Add(new Cities() { Url = "http://semo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://carbondale.craigslist.org" });
            cities.Add(new Cities() { Url = "http://smd.craigslist.org" });
            cities.Add(new Cities() { Url = "http://swv.craigslist.org" });
            cities.Add(new Cities() { Url = "http://miami.craigslist.org" });
            cities.Add(new Cities() { Url = "http://southjersey.craigslist.org" });
            cities.Add(new Cities() { Url = "http://swks.craigslist.org" });
            cities.Add(new Cities() { Url = "http://swmi.craigslist.org" });
            cities.Add(new Cities() { Url = "http://marshall.craigslist.org" });
            cities.Add(new Cities() { Url = "http://natchez.craigslist.org" });
            cities.Add(new Cities() { Url = "http://bigbend.craigslist.org" });
            cities.Add(new Cities() { Url = "http://swva.craigslist.org" });
            cities.Add(new Cities() { Url = "http://spacecoast.craigslist.org" });
            cities.Add(new Cities() { Url = "http://spokane.craigslist.org" });
            cities.Add(new Cities() { Url = "http://springfieldil.craigslist.org" });
            cities.Add(new Cities() { Url = "http://springfield.craigslist.org" });
            cities.Add(new Cities() { Url = "http://pennstate.craigslist.org" });
            cities.Add(new Cities() { Url = "http://statesboro.craigslist.org" });
            cities.Add(new Cities() { Url = "http://staugustine.craigslist.org" });
            cities.Add(new Cities() { Url = "http://stcloud.craigslist.org" });
            cities.Add(new Cities() { Url = "http://stgeorge.craigslist.org" });
            cities.Add(new Cities() { Url = "http://stillwater.craigslist.org" });
            cities.Add(new Cities() { Url = "http://stjoseph.craigslist.org" });
            cities.Add(new Cities() { Url = "http://stlouis.craigslist.org" });
            cities.Add(new Cities() { Url = "http://stockton.craigslist.org" });
            cities.Add(new Cities() { Url = "http://susanville.craigslist.org" });
            cities.Add(new Cities() { Url = "http://syracuse.craigslist.org" });
            cities.Add(new Cities() { Url = "http://tallahassee.craigslist.org" });
            cities.Add(new Cities() { Url = "http://tampa.craigslist.org" });
            cities.Add(new Cities() { Url = "http://terrehaute.craigslist.org" });
            cities.Add(new Cities() { Url = "http://texarkana.craigslist.org" });
            cities.Add(new Cities() { Url = "http://texoma.craigslist.org" });
            cities.Add(new Cities() { Url = "http://thumb.craigslist.org" });
            cities.Add(new Cities() { Url = "http://toledo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://topeka.craigslist.org" });
            cities.Add(new Cities() { Url = "http://treasure.craigslist.org" });
            cities.Add(new Cities() { Url = "http://tricities.craigslist.org" });
            cities.Add(new Cities() { Url = "http://tucson.craigslist.org" });
            cities.Add(new Cities() { Url = "http://tulsa.craigslist.org" });
            cities.Add(new Cities() { Url = "http://tuscaloosa.craigslist.org" });
            cities.Add(new Cities() { Url = "http://tuscarawas.craigslist.org" });
            cities.Add(new Cities() { Url = "http://twinfalls.craigslist.org" });
            cities.Add(new Cities() { Url = "http://twintiers.craigslist.org" });
            cities.Add(new Cities() { Url = "http://easttexas.craigslist.org" });
            cities.Add(new Cities() { Url = "http://up.craigslist.org" });
            cities.Add(new Cities() { Url = "http://utica.craigslist.org" });
            cities.Add(new Cities() { Url = "http://valdosta.craigslist.org" });
            cities.Add(new Cities() { Url = "http://ventura.craigslist.org" });
            cities.Add(new Cities() { Url = "http://burlington.craigslist.org" });
            cities.Add(new Cities() { Url = "http://victoriatx.craigslist.org" });
            cities.Add(new Cities() { Url = "http://visalia.craigslist.org" });
            cities.Add(new Cities() { Url = "http://waco.craigslist.org" });
            cities.Add(new Cities() { Url = "http://washingtondc.craigslist.org" });
            cities.Add(new Cities() { Url = "http://waterloo.craigslist.org" });
            cities.Add(new Cities() { Url = "http://watertown.craigslist.org" });
            cities.Add(new Cities() { Url = "http://wausau.craigslist.org" });
            cities.Add(new Cities() { Url = "http://wenatchee.craigslist.org" });
            cities.Add(new Cities() { Url = "http://quincy.craigslist.org" });
            cities.Add(new Cities() { Url = "http://westky.craigslist.org" });
            cities.Add(new Cities() { Url = "http://westmd.craigslist.org" });
            cities.Add(new Cities() { Url = "http://westernmass.craigslist.org" });
            cities.Add(new Cities() { Url = "http://westslope.craigslist.org" });
            cities.Add(new Cities() { Url = "http://wv.craigslist.org" });
            cities.Add(new Cities() { Url = "http://wichita.craigslist.org" });
            cities.Add(new Cities() { Url = "http://wichitafalls.craigslist.org" });
            cities.Add(new Cities() { Url = "http://williamsport.craigslist.org" });
            cities.Add(new Cities() { Url = "http://wilmington.craigslist.org" });
            cities.Add(new Cities() { Url = "http://winchester.craigslist.org" });
            cities.Add(new Cities() { Url = "http://winstonsalem.craigslist.org" });
            cities.Add(new Cities() { Url = "http://worcester.craigslist.org" });
            cities.Add(new Cities() { Url = "http://wyoming.craigslist.org" });
            cities.Add(new Cities() { Url = "http://yakima.craigslist.org" });
            cities.Add(new Cities() { Url = "http://york.craigslist.org" });
            cities.Add(new Cities() { Url = "http://youngstown.craigslist.org" });
            cities.Add(new Cities() { Url = "http://yubasutter.craigslist.org" });
            cities.Add(new Cities() { Url = "http://yuma.craigslist.org" });
            cities.Add(new Cities() { Url = "http://zanesville.craigslist.org" });
        }

        void LoadCanadaCities()
        {
            cities.Add(new Cities() { Url = "http://barrie.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://belleville.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://brantford.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://calgary.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://cariboo.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://chatham.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://comoxvalley.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://cornwall.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://edmonton.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://abbotsford.craigslist.ca" });

            cities.Add(new Cities() { Url = "http://barrie.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://ftmcmurray.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://guelph.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://halifax.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://hamilton.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://kamloops.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://kelowna.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://kingston.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://kitchener.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://cranbrook.craigslist.ca" });

            cities.Add(new Cities() { Url = "http://lethbridge.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://londonon.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://hat.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://montreal.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://nanaimo.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://newbrunswick.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://niagara.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://ottawa.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://owensound.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://peace.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://peterborough.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://pei.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://princegeorge.craigslist.ca" });

            cities.Add(new Cities() { Url = "http://quebec.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://reddeer.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://regina.craigslist.ca" });


            cities.Add(new Cities() { Url = "http://saguenay.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://sarnia.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://saskatoon.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://soo.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://sherbrooke.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://skeena.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://newfoundland.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://sudbury.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://sunshine.craigslist.ca" });

            cities.Add(new Cities() { Url = "http://territories.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://thunderbay.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://toronto.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://troisrivieres.craigslist.ca" });

            cities.Add(new Cities() { Url = "http://vancouver.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://victoria.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://whistler.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://whitehorse.craigslist.ca" });

            cities.Add(new Cities() { Url = "http://windsor.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://winnipeg.craigslist.ca" });
            cities.Add(new Cities() { Url = "http://yellowknife.craigslist.ca" });
           
           
        }

        private void DG_Hyperlink_Click(object sender, RoutedEventArgs e)
        {
            Hyperlink link = (Hyperlink)e.OriginalSource;
            Process.Start(link.NavigateUri.ToString());
        }
        List<Data> readhtml(string urlAddress,string search,string rawurl)
        {
            List<Data> _listdata = new List<Data>();
            try
            {
               
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(urlAddress);
                HttpWebResponse response = (HttpWebResponse)request.GetResponse();

                if (response.StatusCode == HttpStatusCode.OK)
                {
                    Stream receiveStream = response.GetResponseStream();
                    StreamReader readStream = null;

                    if (response.CharacterSet == null)
                    {
                        readStream = new StreamReader(receiveStream);
                    }
                    else
                    {
                        readStream = new StreamReader(receiveStream, Encoding.GetEncoding(response.CharacterSet));
                    }

                    string data = readStream.ReadToEnd();
                    rawurl = rawurl + "/sof/";

                    _listdata = FindRow(rawurl, data);
                 
                 
                    response.Close();
                    readStream.Close();
                }
            }catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return _listdata;
        }

    }

    public class Data
    {
        public string Title { get; set; }
        public string Link { get; set; }
        public string Description { get; set; }
        public string Type { get; set; }
    }
    public class sites
    {
        public string link { get; set; }
    }
    public class Cities
    {
        public string Url { get; set; }
    }

    public class DataPid
    {
        public string Pid;
        public string title;
    }
}

Thursday, February 19, 2015

Export to Excel Localization

using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace WpfApplication1
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
        static DataTable GetTable()
        {
            // Here we create a DataTable with four columns.
            DataTable table = new DataTable();
            table.Columns.Add("Dosage", typeof(int));
            table.Columns.Add("Drug", typeof(string));
            table.Columns.Add("Patient", typeof(string));
            table.Columns.Add("Date", typeof(DateTime));
            table.Columns.Add("Data", typeof(double));

            // Here we add five DataRows.
            table.Rows.Add(25, "Indocin", "David", DateTime.Now,1212.222);
            table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now,555.555);
            table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now,55.34);
            table.Rows.Add(21, "Combivent", "Janet", DateTime.Now,4545.4);
            table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now,3333.3);
            return table;
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            try
            {
             
                ExportToExcel(GetTable(),@"E:\\RastPro\\test.xlsx");
             
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private  void ExportToExcel( DataTable Tbl, string ExcelFilePath = null)
        {
            try
            {
                if (Tbl == null || Tbl.Columns.Count == 0)
                    throw new Exception("ExportToExcel: Null or empty input table!\n");

                // load excel, and create a new workbook
                Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                excelApp.Workbooks.Add();
                excelApp.Visible = true;
           
                // single worksheet
                Microsoft.Office.Interop.Excel._Worksheet workSheet = excelApp.ActiveSheet;

                System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("es-CL");


                string dd = System.Threading.Thread.CurrentThread.CurrentCulture.ToString();

                CultureInfo cSystemCulture = Thread.CurrentThread.CurrentCulture;
                CultureInfo ci = new CultureInfo("es-CL");



                excelApp.DecimalSeparator = ci.NumberFormat.NumberDecimalSeparator;
               // excelApp.ThousandsSeparator = ci.NumberFormat.NumberGroupSeparator;
             
                // column headings
                for (int i = 0; i < Tbl.Columns.Count; i++)
                {
                    workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
                }

                // rows
                for (int i = 0; i < Tbl.Rows.Count; i++)
                {
                    // to do: format datetime values before printing
                    for (int j = 0; j < Tbl.Columns.Count; j++)
                    {
                        SetCellValue((Tbl.Rows[i][j]).ToString(), workSheet, i, j,ci);
                        //workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
                    }
                }

                // check fielpath
                if (ExcelFilePath != null && ExcelFilePath != "")
                {
                    try
                    {
                        workSheet.SaveAs(ExcelFilePath);
                        excelApp.Quit();
                        MessageBox.Show("Excel file saved!");
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                        + ex.Message);
                    }
                }
                else // no filepath is given
                {
                    excelApp.Visible = true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("ExportToExcel: \n" + ex.Message);
            }
        }

        private void SetCellValue(string data, Microsoft.Office.Interop.Excel._Worksheet ws, int row, int col, CultureInfo ci)
        {


            double val;
            try
            {
                val = Convert.ToDouble(data);
                Console.WriteLine(val);

            }
            catch (Exception e)
            {

                //Util.Log("Null Value ignored.", LogType.ERROR);
                return;
            }

            try
            {
                string s = val.ToString();
                ws.Cells[row + 2, col + 1] = s;

                //Util.Log("S:" + s, LogType.ERROR);
            }
            catch
            {
                //Util.Log("Null Value ignored.", LogType.ERROR);
            }
        }
    }
}

Friday, January 30, 2015

WPF Processbar

<Window x:Class="Exporting.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>

        <Grid.RowDefinitions>
            <RowDefinition Height="30"/>
            <RowDefinition Height="*"/>
        </Grid.RowDefinitions>
        <Button Content="Button" x:Name="btnApply" Grid.Row="0" VerticalAlignment="Top" Width="75" Click="Button_Click"/>
        <DataGrid x:Name="dg" Grid.Row="1"  ></DataGrid>
        <Popup Name="popup1" HorizontalAlignment="Left" Height="90"   Width="436" VerticalAlignment="Top"  Placement="Center" >
            <Border BorderBrush="Black" BorderThickness="2">
                <Grid Background="White"  >
                    <Grid.RowDefinitions>
                        <RowDefinition Height="35"/>
                        <RowDefinition Height="45"/>
                    </Grid.RowDefinitions>
                    <ProgressBar x:Name="pg" Grid.Row="0" HorizontalAlignment="Left"   IsIndeterminate="False"
                      Height="25"
                     VerticalAlignment="Center" Width="450"
                     Foreground="Green"
                     Maximum="100" ForceCursor="False" />
                    <Button Content="Cancel" x:Name="btnCancel" Click="btnCancel_Click" Width="120" Height="35" Grid.Row="1" HorizontalAlignment="Center" VerticalAlignment="Center"/>
                </Grid>
            </Border>
        </Popup>
        <Button Content="Export" x:Name="btnExport" HorizontalAlignment="Left" Margin="85,8,0,0" VerticalAlignment="Top" Width="75" Click="Button_Click_1"/>

    </Grid>
</Window>



public partial class MainWindow : Window
    {
        DataDR DrClass = new DataDR();
        ProgressBar prbar = new ProgressBar();
        private static string CONNECTION_STRING = "User Id=tpl_owner;Password=TPL_OWNER2;Data Source=(DESCRIPTION=" + "(ADDRESS=(PROTOCOL=TCP)(HOST=lntpld1.uk.db.com)(PORT=1801))" + "(CONNECT_DATA=(SID=LNTPLD1)));";
        DateTime dt1 = DateTime.Now;
        DataTable Dt = new DataTable();
        //http://devtoolshed.com/content/c-download-file-progress-bar
        public MainWindow()
        {
            InitializeComponent();
            worker.DoWork += worker_DoWork;
            worker.RunWorkerCompleted += worker_RunWorkerCompleted;
            worker.ProgressChanged += worker_ProgressChanged;
            worker.WorkerSupportsCancellation = true;
            dg.ItemsSource = GetTable().AsDataView();
         
        }

        #region
        private static readonly BackgroundWorker worker = new BackgroundWorker();
        private void worker_DoWork(object sender, DoWorkEventArgs e)
        {
            string btn = (string)e.Argument;
            // here is logic
            GetData(worker, btn);
            if (worker.CancellationPending)
            {
                e.Cancel = true;
                return;
            }

        }
        private void worker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            pg.Value = e.ProgressPercentage;
        }
        private void worker_RunWorkerCompleted(object sender,
                                               RunWorkerCompletedEventArgs e)
        {
            DateTime dt2 = DateTime.Now;
            TimeSpan sp = dt2.Subtract(dt1);
            popup1.IsOpen = false;
         
        }

        private static void mouseWaitTimer_Tick(object sender, EventArgs e)
        {
            worker.WorkerReportsProgress = true;
            if (progress == 0) progress = 100;
            else progress = 0;
            worker.ReportProgress(progress);
            // Handle Single Click Actions

        }
        private static DispatcherTimer myClickWaitTimer =
        new DispatcherTimer(
            new TimeSpan(0, 0, 0, 1),
            DispatcherPriority.Background, mouseWaitTimer_Tick,
            Dispatcher.CurrentDispatcher);
     
        static DataTable GetTable()
        {
            //
            // Here we create a DataTable with four columns.
            //
            DataTable table = new DataTable();
            table.Columns.Add("ID", typeof(int));
            table.Columns.Add("Name", typeof(string));
            table.Columns.Add("Sex", typeof(string));
            table.Columns.Add("CreatedDate", typeof(string));
            table.Columns.Add("City", typeof(string));

            //
            // Here we add five DataRows.
            //
            table.Rows.Add(25, "Devesh Omar", "M", DateTime.Now, "Noida");
            table.Rows.Add(50, "Nikhil Vats", "M", DateTime.Now, "Noida");
            table.Rows.Add(10, "Heena Sharma", "F", DateTime.Now, "Delhi");
            table.Rows.Add(21, "Nancy Sharma", "F", DateTime.Now, "Delhi");
            table.Rows.Add(100, "Avinash", "M", DateTime.Now, "Delhi");

            table.Rows.Add(25, "Devesh gupta", "M", DateTime.Now, "Delhi");
            table.Rows.Add(50, "Nikhil gupta", "M", DateTime.Now, "Noida");
            table.Rows.Add(10, "HS gupta", "F", DateTime.Now, "Delhi");
            table.Rows.Add(21, "VS gupta", "F", DateTime.Now, "Delhi");
            table.Rows.Add(100, "RJ gupta", "M", DateTime.Now, "Delhi");

            return table;
        }
        static int progress = 10;
        #endregion
        void GetData(BackgroundWorker worker,object sender)
        {
            try
            {

                //start Database call
             
                //OracleConnection conn = new OracleConnection();
                //conn.ConnectionString = CONNECTION_STRING;
                //conn.Open();
                //using (var cmd = new OracleCommand())
                //{

                //    cmd.Connection = conn;
                //    DataSet ds = new DataSet();
                //    OracleDataAdapter oDa = new OracleDataAdapter();
                //    cmd.CommandType = CommandType.Text;
                //    oDa.SelectCommand = cmd;
                //    cmd.CommandText = "SELECT * FROM UNDERLYING WHERE ROWNUM  between  0  and 5000";
                //    oDa = new OracleDataAdapter();
                //    oDa.SelectCommand = cmd;
                //    ds = new DataSet();
                //    oDa.Fill(ds, "dtMy");

                //    Dt = ds.Tables["dtMy"];

                 
                //    //txtStatus.Text = "Data retrived from Database.";
                //    DataTable dt12 = new DataTable();
                //    var missing = from table1 in Dt.AsEnumerable()
                //                  where !dt12.AsEnumerable().Contains(table1)
                //                  select table1;

               

                //}

                string str = (sender).ToString();
                if (str == "BtnApply")
                {
                    myClickWaitTimer.Start();
                    DrClass.GetSBData();
                    myClickWaitTimer.Stop();
                }
                if (str == "BtnExport")
                {
                    myClickWaitTimer.Start();
                    DrClass.GetSBData();
                    myClickWaitTimer.Stop();
                    if (DrClass.DV.Rows.Count > 0)
                    {
                        Excel.ExcelUtlity obj = new Excel.ExcelUtlity();
                        obj.WriteDataTableToExcel(DrClass.DV, "Person Details", "E:\\RastPro\\testPersonExceldata.xlsx", "Details", worker);
                    }
                }
             
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void Export(BackgroundWorker worker)
        {
            myClickWaitTimer.Start();
            DrClass.GetSBData();
            myClickWaitTimer.Start();
            if (DrClass.DV.Rows.Count > 0)
            {
                Excel.ExcelUtlity obj = new Excel.ExcelUtlity();
                obj.WriteDataTableToExcel(DrClass.DV, "Person Details", "E:\\RastPro\\testPersonExceldata.xlsx", "Details", worker);
            }
        }

        private void btnCancel_Click(object sender, RoutedEventArgs e)
        {
            worker.CancelAsync();
            popup1.IsOpen = false;
        }
        private void Button_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (!worker.IsBusy)
                {
                    popup1.IsOpen = true;
                    popup1.StaysOpen = true;
                    worker.WorkerReportsProgress = true;
                    worker.RunWorkerAsync("BtnApply");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            if (!worker.IsBusy)
            {
                popup1.IsOpen = true;
                popup1.StaysOpen = true;
                worker.WorkerReportsProgress = true;
                worker.RunWorkerAsync("BtnExport");
            }
         
        }
   


    }
}


namespace Excel
{
    public class ExcelUtlity
    {
        /// <summary>
        /// FUNCTION FOR EXPORT TO EXCEL
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="worksheetName"></param>
        /// <param name="saveAsLocation"></param>
        /// <returns></returns>
        public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType, BackgroundWorker worker)
        {
            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel.Workbook excelworkBook;
            Microsoft.Office.Interop.Excel.Worksheet excelSheet;
            Microsoft.Office.Interop.Excel.Range excelCellrange;

            try
            {
                //txtStatus.Text = "Report generating..";
                // Start Excel and get Application object.
                excel = new Microsoft.Office.Interop.Excel.Application();

                // for making Excel visible
                excel.Visible = false;
                excel.DisplayAlerts = false;

                // Creation a new Workbook
                excelworkBook = excel.Workbooks.Add(Type.Missing);

                // Workk sheet
                excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
                excelSheet.Name = worksheetName;


                excelSheet.Cells[1, 1] = ReporType;
                excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();
                int iProgressPercentage = 15;
                // loop through each row and add values to our sheet
                int rowcount = 2;
                int dTotal = dataTable.Rows.Count;
                foreach (DataRow datarow in dataTable.Rows)
                {
                   // txtStatus.Text = "Report generating.." + rowcount +"/" + dataTable.Rows.Count;
                    rowcount += 1;
                    double dIndex = (double)(rowcount);
                    // run all background tasks here
                    double dProgressPercentage = (dIndex / dTotal);
                    iProgressPercentage = (int)(dProgressPercentage * 100);
                    worker.ReportProgress(iProgressPercentage);
                   
                    for (int i = 1; i <= dataTable.Columns.Count; i++)
                    {
                        // on the first iteration we add the column headers
                        if (rowcount == 3)
                        {
                            excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
                            excelSheet.Cells.Font.Color = System.Drawing.Color.Black;

                        }

                        excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();

                        //for alternate rows
                        if (rowcount > 3)
                        {
                            if (i == dataTable.Columns.Count)
                            {
                                if (rowcount % 2 == 0)
                                {
                                    excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
                                    FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black,false);
                                }

                            }
                        }

                    }

                }

                // now we resize the columns
                excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
                excelCellrange.EntireColumn.AutoFit();
                Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
                border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                border.Weight = 2d;


                excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
                FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);


                //now save the workbook and exit Excel
               // txtStatus.Text = "Report generation done..";
                excel.Visible=true;
                excelworkBook.SaveAs(saveAsLocation);;
                excelworkBook.Close();
                excel.Quit();
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                excelSheet = null;
                excelCellrange = null;
                excelworkBook = null;
            }

        }

        /// <summary>
        /// FUNCTION FOR FORMATTING EXCEL CELLS
        /// </summary>
        /// <param name="range"></param>
        /// <param name="HTMLcolorCode"></param>
        /// <param name="fontColor"></param>
        /// <param name="IsFontbool"></param>
        public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool)
        {
            range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode);
            range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);
            if (IsFontbool == true)
            {
                range.Font.Bold = IsFontbool;
            }
        }

    }
}