.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> SharePoint >> Post New QuestionBookmark and Share Subscribe to Forum

Exporting sharepoint list to excel programatically

Posted By: Aruna     Posted Date: November 02, 2010    Points:2   Category :SharePoint
I created a custom webpart and added a button, and on the button click event wrote the following code:
using System;
using System.ComponentModel;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebPartPages;
using Microsoft.SharePoint.Utilities;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data;
namespace MyExcel
{
[DefaultProperty("Text")]
[ToolboxData("<{0}:Excel runat=server></{0}:Excel>")]
[XmlRoot(Namespace = "MyExcel")]
public class Excel : WebPart
{
private Button exportBtn;


protected override void CreateChildControls()
{

base.CreateChildControls();
exportBtn = new Button();
this.Controls.Add(exportBtn);
exportBtn.Text = "Click for excel export";
exportBtn.Click += new EventHandler(exportBtn_Click);
}

void exportBtn_Click(object sender, EventArgs e)
{
ListToExcelConvertor();

}
public void ListToExcelConvertor()
{
using (SPSite site = new SPSite("site url"))
{
using (SPWeb web = site.OpenWeb())
{
DirectoryInfo dir = new DirectoryInfo(@"C:\List Data");
dir.Create();

FileInfo file = new FileInfo(@"C:\List Data\ExcelFileName.xls");
StreamWriter streamWriter = file.CreateText();

StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);


Table tblData = GetListTableControl("http://.....(site url) ", "list name");
tblData.RenderControl(htmlTextWriter);
streamWriter.Write(stringWriter.ToString());

htmlTextWriter.Close();
streamWriter.Close();
stringWriter.Close();

}
}
}

private static Table GetListTableControl(string strListURL, string strListName)
{

Table tblListView = new Table();
tblListView.ID = "_tblListView";

tblListView.BorderStyle = BorderStyle.Solid;
tblListView.BorderWidth = Unit.Pixel(1);

//tblListView.BorderColor = Color.Silver;

using (SPSite site = new SPSite(strListURL.Trim()))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists[strListName.Trim()];

SPView wpView = list.Views["All Items"];
wpView.RowLimit = 2147483647;

SPQuery query = new SPQuery(wpView);
SPListItemCollection items = list.GetItems(query);

if (items != null && items.Count > 0)
{
DataTable tbListViewData = items.GetDataTable();
DataView dvListViewData = tbListViewData.DefaultView;
if (dvListViewData != null && dvListViewData.Count > 0)
{
tblListView.Rows.Add(new TableRow());
//tblListView.Rows[0].BackColor = Color.Gainsboro;
tblListView.Rows[0].Font.Bold = true;

for (int i = 0; i < wpView.ViewFields.Count; i++)
{
tblListView.Rows[0].Cells.Add(new TableCell());
tblListView.Rows[0].Cells[i].Text = list.Fields.GetFieldByInternalName(wpView.ViewFields[i].ToString()).Title;
}

for (int i = 0; i < dvListViewData.Count; i++)
{
tblListView.Rows.Add(new TableRow());

for (int j = 0; j < wpView.ViewFields.Count; j++)
{
tblListView.Rows[i + 1].Cells.Add(new TableCell());

if (tbListViewData.Columns.Contains(wpView.ViewFields[j].ToString()))
{
tblListView.Rows[i + 1].Cells[j].Text = dvListViewData[i][wpView.ViewFields[j].ToString()].ToString();
}

}
}
}
}
}
}

return GetListTableControl(strListURL, strListName);
}

}
}
on the click of the event getting an error
stackoverflow exception error at the line
if (items != null && items.Count > 0)
{
DataTable tbListViewData = items.GetDataTable();



ANy suggestions abt the cause of the error.










Responses
Author: Sadhuryan             
Posted Date: November 11, 2010     Points: 5   

can you write the Exception/Inner Exception message

or copy the error with stack trace info.



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend