Welcome to plsql4all.blogspot.com SQL, MYSQL, ORACLE, TERADATA, MONGODB, MARIADB, GREENPLUM, DB2, POSTGRESQL.

Sunday, 22 June 2014

Export SharePoint List Data to Excel Sheet Programmatically

private void ExportToExcel(SPWeb _web)
        {
            if (_web != null)
            {

                SPList list = _web.Lists["InvoiceDetails"];
                if (list != null)
                {

                    DataTable dataTable = new DataTable();
                    //Adds Columns to SpreadSheet

                    InitializeExcel(list, dataTable);
                    string _schemaXML = list.DefaultView.ViewFields.SchemaXml;
                    if (list.Items != null && list.ItemCount > 0)
                    {

                        foreach (SPListItem _item in list.Items)
                        {

                            DataRow dr = dataTable.NewRow();

                            foreach (DataColumn _column in dataTable.Columns)
                            {

                                if (dataTable.Columns[_column.ColumnName] != null && _item[_column.ColumnName] != null)
                                {

                                    dr[_column.ColumnName] = _item[_column.ColumnName].ToString();

                                }

                            }

                            dataTable.Rows.Add(dr);
                        }

                        System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
                        grid.HeaderStyle.Font.Bold = true;
                        grid.DataSource = dataTable;
                        grid.DataBind();
                        using (StreamWriter streamWriter = new StreamWriter("C:\\" + list.Title+ ".xls", false, Encoding.UTF8))
                        {

                            using (HtmlTextWriter htmlTextWriter = new HtmlTextWriter(streamWriter))
                            {

                                grid.RenderControl(htmlTextWriter);

                            }

                        }
                        ScriptManager.RegisterClientScriptBlock(Page, this.GetType(), "Alert", "alert('Excel file Created');", true);

                    }

                }

            }
        }

        public void InitializeExcel(SPList list, DataTable _datatable)
        {

            if (list != null)
            {
               
                string _schemaXML = list.DefaultView.ViewFields.SchemaXml;
               
                if (list.Items != null && list.ItemCount > 0)
                {

                   
                    foreach (SPListItem _item in list.Items)
                    {

                        foreach (SPField _itemField in _item.Fields)
                        {

                            if (_schemaXML.Contains(_itemField.InternalName))
                            {

                                if (_item[_itemField.InternalName] != null)
                                {

                                    if (!_datatable.Columns.Contains(_itemField.InternalName))
                                    {

                                        _datatable.Columns.Add(new DataColumn(_itemField.StaticName, Type.GetType("System.String")));

                                    }

                                }

                            }

                        }

                    }

                }

            }

        }
Please provide your feedback in the comments section above. Please don't forget to follow.