The export to spreadsheet link was not visible in a particular list and we had an urgent request to export data from list to an excel.

So we created a windows application to take care of it.

Added web reference http://extranet/sites/sis-forum/_vti_bin/lists.asmx to the project and named it Sisforum.

Added a textbox txtSiteUrl  in the Windows form…and added the url of the site from where we wanted to export a list to excel e.g https://test-web.domain.org/sites/isago/_vti_bin/lists.asmx , in another textbox txtListName ,gave the name of the list we wanted to export,say “UserManagement Task” and a textbox txtFilepath  for putting the filepath like “c:\test.xls”.

 

            StringBuilder strExcel = new StringBuilder();
            StringBuilder strHtml = new StringBuilder();
            System.Net.NetworkCredential credential = new System.Net.NetworkCredential(“username”, “password”);
            XmlDocument xdoc = new XmlDocument();
            XmlNode allFields = xdoc.CreateElement(“ViewFields”);
            XmlNode QueryOpt = xdoc.CreateElement(“QueryOptions”);
            XmlNode Query = xdoc.CreateElement(“Query”);
            XmlNode xmlResults;
            try
            {
               SisForum.Lists lsts = new ExporttoExcelFile.SisForum.Lists();
                lsts.Credentials = credential;
                lsts.Url = txtSiteUrl.Text+”/_vti_bin/lists.asmx”;
                xmlResults = lsts.GetListItems(txtListName.Text, null, null, null, “1000000”, null, null);
                //Get Header Row
                foreach (XmlNode node in xmlResults)
                {
                    if (node.Name == “rs:data”)
                    {
                        for (int i = 0; i < 2; i++)
                        {
                            strExcel.Append(“\n”);
                           strHtml.Append(“”);
                           if (node.ChildNodes[i].Name == “z:row”)
                            {
              for (int j = 0; j < node.ChildNodes[i].Attributes.Count; j++)
                                {
                                    if (node.ChildNodes[i].Attributes[j] != null)
                                    {
                     string name = node.ChildNodes[i].Attributes[j].Name;
                                        name = StripHTML(name);
                   strExcel.Append(name.Substring(name.IndexOf(‘#’) + 1));
                                        strExcel.Append(“\t”);
                                    }
                                }
                            }
                        }
                    }
                }

                //Get List Items
                foreach (XmlNode node in xmlResults)
                {
                    if (node.Name == “rs:data”)
                    {
                        for (int i = 0; i < node.ChildNodes.Count; i++)
                        {
                            strExcel.Append(“\n”);
                            strHtml.Append(“”);
                            if (node.ChildNodes[i].Name == “z:row”)
                            {  
                                //get list items
          for (int j = 0; j < node.ChildNodes[i].Attributes.Count; j++)
                                {
                                    if (node.ChildNodes[i].Attributes[j] != null)
                                    {
        string name = node.ChildNodes[i].Attributes[j].Value;
                                        name = StripHTML(name);
                   strExcel.Append(name.Substring(name.IndexOf(‘#’) + 1));
                                        strExcel.Append(“\t”);
                                    }
                                }
                            }
                        }
                    }
                }
               WritetoFile(strExcel,”xls”);
               MessageBox.Show(“Completted”);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

public void WritetoFile(StringBuilder str,string Format)

        {

            string filename = txtFilepath.Text;

            TextWriter tw = new StreamWriter(filename);

            tw.Write(str);

            tw.Close();

        }

Now the string that we get from the xml by calling GetListItems needs to be formatted.We need to remove HTML development formatting, replace line breaks with space because browsers insert spaces,remove all scripts etc. The below functio takes care of that:

public string StripHTML(string source)

        {

            try

            {

                string result;

                // Remove HTML Development formatting

                // Replace line breaks with space

                // because browsers inserts space

                result = source.Replace(“\r”, ” “);

                // Replace line breaks with space

                // because browsers inserts space

                result = result.Replace(“\n”, ” “);

                // Remove step-formatting

                result = result.Replace(“\t”, string.Empty);

                // Remove repeating spaces because browsers ignore them

result = System.Text.RegularExpressions.Regex.Replace(result,  @”( )+”, ” “);

// Remove the header (prepare first by clearing attributes)

result = System.Text.RegularExpressions.Regex.Replace(result, @”<( )*head([^>])*>”, “<head>”,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                result = System.Text.RegularExpressions.Regex.Replace(result,  @”(<( )*(/)( )*head( )*>)”, “</head>”,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                result = System.Text.RegularExpressions.Regex.Replace(result,  “(<head>).*(</head>)”, string.Empty,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

// remove all scripts (prepare first by clearing attributes)

result = System.Text.RegularExpressions.Regex.Replace(result,    @”<( )*script([^>])*>”, “<script>”,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                result = System.Text.RegularExpressions.Regex.Replace(result,  @”(<( )*(/)( )*script( )*>)”, “</script>”,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);         

result = System.Text.RegularExpressions.Regex.Replace(result,   @”(<script>).*(</script>)”, string.Empty,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

// remove all styles (prepare first by clearing attributes)

result = System.Text.RegularExpressions.Regex.Replace(result,   @”<( )*style([^>])*>”, “<style>”,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result,    @”(<( )*(/)( )*style( )*>)”, “</style>”,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result, “(<style>).*(</style>)”, string.Empty,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // insert tabs in spaces of <td> tags

result = System.Text.RegularExpressions.Regex.Replace(result,  @”<( )*td([^>])*>”, “\t”,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // insert line breaks in places of <BR> and <LI> tags

result = System.Text.RegularExpressions.Regex.Replace(result,    @”<( )*br( )*>”, “\r”,                          System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result,   @”<( )*li( )*>”, “\r”,  System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // insert line paragraphs (double line breaks) in place

                // if <P>, <DIV> and <TR> tags

result = System.Text.RegularExpressions.Regex.Replace(result,  @”<( )*div([^>])*>”, string.Empty,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result,  @”<( )*tr([^>])*>”, string.Empty,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result,   @”<( )*p([^>])*>”, string.Empty,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // Remove remaining tags like <a>, links, images,

                // comments etc – anything that’s enclosed inside < >

result = System.Text.RegularExpressions.Regex.Replace(result, @”<[^>]*>”, string.Empty,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // replace special characters:

result = System.Text.RegularExpressions.Regex.Replace(result,   @” “, ” “,   System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result, @”&bull;”, ” * “,   System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result,   @”&lsaquo;”, “<“,  System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result,  @”&rsaquo;”, “>”,     System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result,  @”&trade;”, “(tm)”,   System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result, @”&frasl;”, “/”,   System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result, @”&lt;”, “<“,    System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result, @”&gt;”, “>”,  System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result,   @”&copy;”, “(c)”,    System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result,  @”&reg;”, “(r)”,   System.Text.RegularExpressions.RegexOptions.IgnoreCase);

result = System.Text.RegularExpressions.Regex.Replace(result,  @”&(.{2,6});”, string.Empty,                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                return result;

            }

            catch

            {

                return source;

            }

        }

Advertisements