Here is one of the ways to get all SPOL list items with all columns:
Method to fetch items:
private void btnGetListItems_Click(object sender, EventArgs e)
{
progressBar.Minimum = 0;
progressBar.Maximum = 10;
progressBar.Value = 1;
var targetSite = new Uri(txtSiteUrl.Text.Trim());
var login = txtUserID.Text.Trim();
var password = txtPassword.Text;
var securePassword = new SecureString();
progressBar.Value = 3;
foreach (char c in password)
{
securePassword.AppendChar(c);
}
var onlineCredentials = new SharePointOnlineCredentials(login, securePassword);
using (ClientContext clientContext = new ClientContext(targetSite))
{
try
{
clientContext.Credentials = onlineCredentials;
Web web = clientContext.Web;
var list = clientContext.Web.Lists.GetByTitle(drpLists.SelectedItem.ToString());
var view = list.Views.GetByTitle("All Items");
clientContext.Load(view, v => v.ViewFields, v => v.ListViewXml);
clientContext.ExecuteQuery();
progressBar.Value = 4;
var query = new CamlQuery();
query.ViewXml = "<View><Query>" + view.ListViewXml + "</Query></View>";
var items = list.GetItems(query);
clientContext.Load(items);
clientContext.ExecuteQuery();
progressBar.Value = 5;
dtResults = new DataTable();
columnNames = new List<string>();
string clmn = string.Empty;
foreach (var listColumn in view.ViewFields)
{
if (listColumn.ToString().ToLower().Contains("linktitle"))
{
columnNames.Add("Title");
dtResults.Columns.Add("Title");
continue;
}
if (listColumn.ToString().ToLower().Contains("attachments"))
continue;
columnNames.Add(listColumn.ToString());
dtResults.Columns.Add(listColumn.ToString());
}
progressBar.Value = 6;
FillGridView(items);
btnExportToExcel.Enabled = true;
progressBar.Value = 10;
}
catch (Exception ex)
{
progressBar.Value = 10;
SWF.MessageBox.Show("Error: " + ex.Message);
}
}
}
Method to fill items in DataTable/GridView:
protected void FillGridView(ListItemCollection itemsColl)
{
try
{
progressBar.Value = 7;
DataRow row;
foreach (ListItem item in itemsColl)
{
row = dtResults.NewRow();
foreach (string listColumn in columnNames)
{
try
{
row[listColumn] = item.FieldValues[listColumn].ToString();
}
catch (Exception ex)
{
row[listColumn] = string.Empty;
}
}
dtResults.Rows.Add(row);
}
progressBar.Value = 8;
dataGridResults.DataSource = dtResults;
try
{
for (int col = 0; col < dtResults.Columns.Count; col++)
{
dtResults.Columns[col].ColumnName = dtResults.Columns[col].ColumnName.Replace("_x0020_", " ");
dtResults.Columns[col].ColumnName = dtResults.Columns[col].ColumnName.Replace("_x00", string.Empty);
}
}
catch (Exception ex) { }
}
catch (Exception ex)
{
progressBar.Value = 10;
SWF.MessageBox.Show("Error while generating report: " + ex.Message);
}
}
