public void WriteListToExcel(string fileName, SharepointList list)
{
var newFile = new FileInfo(fileName);
if (newFile.Exists)
{
newFile.Delete();
newFile = new FileInfo(fileName);
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");
//Add the headers
for (var i = 0; i < list.Columns.Count; i++)
{
var column = list.Columns[i];
worksheet.Cells[1, i+1].Value = column.Name;
}
//Add the items
for (var i = 0; i < list.Rows.Count; i++)
{
var row = list.Rows[i];
for (var j = 0; j < row.Values.Count; j++)
{
var value = row.Values[j] != null ? row.Values[j].ToString() : row.Values[j];
worksheet.Cells[i+2, j+1].Value = value;
}
}
package.Save();
}
}
public SharepointList ReadListFromExcel(string filename)
{
var newFile = new FileInfo(filename);
var list = new SharepointList();
if (!newFile.Exists)
{
return null;
}
using (ExcelPackage package = new ExcelPackage(newFile))
{
// Get worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
// Get columns
list.Columns = new List<SharepointList.Column>();
var col = 1;
while (worksheet.Cells[1, col].Value != null)
{
list.Columns.Add(new SharepointList.Column() { Name = worksheet.Cells[1, col].Value.ToString() });
col++;
}
var totalCols = col;
// Get rows
list.Rows = new List<SharepointList.Row>();
var row = 2;
while (worksheet.Cells[row, 1].Value != null)
{
var spRow = new SharepointList.Row() { Values = new List<object>() };
for (col = 1; col < totalCols; col++)
{
Debug.WriteLine("Add " + col);
spRow.Values.Add(worksheet.Cells[row, col].Value);
}
list.Rows.Add(spRow);
row++;
}
}
return list;
}
public void ImportObjectsToList(string siteUrl, string listName, SharepointList list)
{
var site = new SPSite(siteUrl);
site.AllowUnsafeUpdates = true;
var spList = site.OpenWeb().Lists[listName];
foreach (var row in list.Rows)
{
var listItem = spList.Items.Add();
var col = 0;
foreach (var value in row.Values)
{
try
{
listItem[list.Columns[col].Name] = value;
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
col++;
}
listItem.Update();
}
}
