Export metadata from SharePoint site

List could be easily export via Actions->Export to Spreadsheet, this article talks about exporting document libraries' metadata.

• Get all libraries (names) from the site:

        public static void GetLibraries()
        {
            foreach (XmlNode li in m_list.GetListCollection().ChildNodes)
            {
                //Check whether list is document library
                if (Convert.ToInt32(li.Attributes["ServerTemplate"].Value) != 0x65)
                {
                    continue;
                }
                string title = li.Attributes["Title"].Value;
                m_docLibs.Add(title);
            }
        }

• Fetch the interested fields from the document library

        XmlNode list = m_list.GetListAndView(title, string.Empty);
        XmlNamespaceManager nsmgr = new XmlNamespaceManager(list.OwnerDocument.NameTable);
        nsmgr.AddNamespace("sp", "http://schemas.microsoft.com/sharepoint/soap/");
        string xpathQuery = "sp:View/sp:ViewFields/sp:FieldRef";
        XmlNodeList nodes = list.SelectNodes(xpathQuery, nsmgr);
        m_tempDocLibViewFields.Clear();
        for (int i = 0; i < nodes.Count; i++)
        {
              m_tempDocLibViewFields.Add(nodes[i].Attributes["Name"].Value);
        }

          Not know why, but these codes do not work for me.

list.SelectNodes("//Fields/Field[@Hidden='False']) 

• Get the specified columns and specified rows

        public static void GetData(string docLibName)
        {
            XmlDocument camlDocument = new XmlDocument();
            XmlNode queryNode = camlDocument.CreateElement("Query");
            
            // create ViewFields CAML
            XmlDocument viewFieldsDoc = new XmlDocument();
            XmlNode ViewFields = AddXmlElement(viewFieldsDoc, "ViewFields", "");
            foreach (string fieldName in m_tempDocLibViewFields)
            {
                AddFieldRef(ViewFields, fieldName);
            }
            AddFieldRef(ViewFields, "FileRef");
       
            // create QueryOptions CAML
            XmlDocument queryOptionsDoc = new XmlDocument();
            XmlNode QueryOptions = AddXmlElement(queryOptionsDoc, "QueryOptions", "");
            AddXmlElement(QueryOptions, "IncludeMandatoryColumns", "FALSE");

            QueryOptions.InnerXml = "<ViewAttributes Scope=\"Recursive\" />";
            XmlNode ResultListItems = m_list.GetListItems(docLibName, null, queryNode,
                ViewFields, "100000", QueryOptions, null);   // use default view of the document library 
        }

        The "100000" is important if you have more than 200 items live in your document library and you do not want to get 200 results every time.

        Set the "IncludeMandatoryColumns" to be "FALSE" if you do not want a bunch of unknown fields.

        Still do not know why, I could not get exactly what I want. I ask for 5 columns and he give me 10 or 15, so I have to delete some of them later manually.

• Save the results into a DataSet

            NameTable nt = new System.Xml.NameTable();
            XmlNamespaceManager nsMgr = new XmlNamespaceManager(nt);
            nsMgr.AddNamespace("w", "http://schemas.microsoft.com/office/word/2003/2/wordml");
            XmlNode y = ResultListItems.SelectSingleNode("*", nsMgr);
            DataSet ds = new DataSet();
            if (y != null)
            {
                XmlReader xmlReader = new XmlTextReader(y.InnerXml, XmlNodeType.Element, null);
                ds.ReadXml(xmlReader);
            }

• Normalize the DataTable

            if (ds.Tables.Count == 0)
                return;

            //modify all field name, delete the "ows_" prefix
            foreach (DataColumn col in ds.Tables[0].Columns)
            {
                if (col.ColumnName.Substring(0, 4) == "ows_")
                    col.ColumnName = col.ColumnName.Substring(4);
            }

            // remove the meaningless column
            ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["DocIcon"]);
            ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["MetaInfo"]);
            ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["_ModerationStatus"]);
            ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["owshiddenversion"]);
            ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["UniqueId"]);
            ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["FSObjType"]);
            ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["FileLeafRef"]);
            ds.Tables[0].Columns.Remove(ds.Tables[0].Columns["Last Modified"]);

            // modify the table content, delete the existing ";#"
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    string str = ds.Tables[0].Rows[i][j].ToString();
                    int nIndex = str.IndexOf(";#");
                    if (nIndex != -1)   //exist
                        ds.Tables[0].Rows[i][j] = str.Substring(nIndex + 2);
                }
            }

• Use myxls to translate DataTable to Excel sheet file (comes from http://www.mzwu.com/article.asp?id=2187)

        public static bool CreateXLS(DataSet ds, string path, bool overwrite)
        {
            if (File.Exists(path) && !overwrite)
                return false;

            try
            {
                XlsDocument xlsDoc = new XlsDocument();
                xlsDoc.FileName = Path.GetFileName(path);

                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    string sheetName = string.IsNullOrEmpty(ds.Tables[i].TableName) ? 
                                                            "Sheet" + i.ToString() : 
                                                            ds.Tables[i].TableName;
                    Worksheet sheet = xlsDoc.Workbook.Worksheets.Add(sheetName);

                    Cells cells = sheet.Cells;

                    for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
                    {
                        Cell cell = cells.Add(1, col + 1, ds.Tables[i].Columns[col].ColumnName);
                        cell.Font.Weight = FontWeight.Bold;
                    }

                    for (int row = 0; row < ds.Tables[i].Rows.Count; row++)
                    {
                        for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
                        {
                            cells.Add(row + 2, col + 1, 
                                      string.IsNullOrEmpty(ds.Tables[i].Rows[row][col].ToString()) ? 
                                      "-" : 
                                      ds.Tables[i].Rows[row][col].ToString());
                        }
                    }
                }

                if (!Directory.Exists(Path.GetDirectoryName(path)))
                    Directory.CreateDirectory(Path.GetDirectoryName(path));

                xlsDoc.Save(Path.GetDirectoryName(path), overwrite);
            }
            catch
            {
                return false;
            }
            return true;
        }
    }

• Xml operations (comes from http://sqlblogcasts.com/blogs/drjohn)

        public static XmlNode AddXmlElement(XmlNode parent, string elementName, string elementValue)
        {

            XmlNode element = parent.AppendChild(
                              parent.OwnerDocument.CreateNode(XmlNodeType.Element, elementName, "")
                              );
            if (elementValue != "")
                element.InnerText = elementValue;
            return (element);
        }

        public static XmlNode AddXmlElement(XmlDocument parent, string elementName, string elementValue)
        {
            XmlNode element = parent.AppendChild(parent.CreateNode(XmlNodeType.Element, elementName, ""));
            if (elementValue != "")
                element.InnerText = elementValue;
            return (element);
        }

        public static XmlNode AddXmlAttribute(XmlNode element, string attrName, string attrValue)
        {

            XmlNode attr = element.Attributes.Append(
                           (XmlAttribute)element.OwnerDocument.CreateNode(XmlNodeType.Attribute, attrName, "")
                           );
            if (attrValue != "")
                attr.Value = attrValue;
            return (attr);
        }

        public static void AddFieldRef(XmlNode viewFields, string fieldName)
        {
            XmlNode fieldRef = AddXmlElement(viewFields, "FieldRef", "");
            AddXmlAttribute(fieldRef, "Name", fieldName);
        }