sharepoint

Upload from excel sheet to sharepoint List :

Method 1 Excel Interop dlls:

using Microsoft.Office.Interop.Excel;

#region excel interop

import to sharepoint from excel with excel interop dll.
try
{
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(path);
MySheet = (Excel.Worksheet)MyBook.Sheets[1];
var lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
var lastcolmn = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column;
// lastcolmn = 26;
var kk = lastRow;
char[] alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
var reminder = lastcolmn / 27;
var quotient = lastcolmn % 27;
string upperlimit = null;
if (reminder > 0)
upperlimit = alphabet[reminder - 1].ToString() + alphabet[quotient].ToString();
else
{
upperlimit = alphabet[quotient - 1].ToString();
}

//excel columns
System.Array ExcelColumnNames = (System.Array)MySheet.get_Range("A" + 3.ToString(), upperlimit + 3.ToString()).Cells.Value;

string[,] ColoumnTemp = new string[2, lastcolmn];

for (int i = 0; i < lastcolmn; i++)
{
ColoumnTemp[0, i] = ExcelColumnNames.GetValue(1, i + 1).ToString().Replace(" ", "_x0020_");
}

// Mapping Coloumns to Sharepoint List Coloumns
//coloumns of Sharepoint list

string siteUrl = ConfigurationManager.AppSettings["SiteUrl"].ToString();

ClientContext clientContext = new ClientContext(siteUrl);
SP.List oList = clientContext.Web.Lists.GetByTitle("Process Budget");

for (int index = 4; index <= lastRow; index++)
{
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
ListItem oListItem = oList.AddItem(itemCreateInfo);

System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), upperlimit + index.ToString()).Cells.Value;

var bb = MyValues;
for (int i = 0; i < lastcolmn; i++)
{
if (MyValues.GetValue(1, i + 1) != null)
{
//if (MyValues.GetValue(1, i + 1).ToString()==@"yourcustomcolumn Name:")
//{
// ColoumnTemp[1, i] = "Changes_x0020_to_x0020_Headcount";
//}
//else if (MyValues.GetValue(1, i + 1).ToString() == @"Select Reason for Change:")
//{
// ColoumnTemp[1, i] = "Select_x0020_Reason_x0020_for_x0";
//}
ColoumnTemp[1, i] = MyValues.GetValue(1, i + 1).ToString();
}
else
{
ColoumnTemp[1, i] = null;
}
}
for (int j = 0; j < lastcolmn - 1; j++)
{
oListItem[ColoumnTemp[0, j]] = ColoumnTemp[1, j];
}
oListItem.Update();
clientContext.ExecuteQuery();
}

MyApp.Workbooks.Close();
deletefile(filename);
return true;
}
catch (Exception ex)
{
return false;
}

Method 2 :(Oledb):

public string UploadDataToSharepoint(string filename, string path)
{
var ds = new DataSet();
try
{

var fileName = path;
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
using (var conn = new OleDbConnection(connectionString))
{
conn.Open();

var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [" + sheets.Rows[3]["TABLE_NAME"].ToString() + "] ";

var adapter = new OleDbDataAdapter(cmd);

adapter.Fill(ds);
}
}

try
{
var dt = ds.Tables["TABLE"];
var columnNames = dt.Rows[0];
var columnName = columnNames.ItemArray;
//as excel is reading 103 we specified mannuly to read 66 columns
var NumberOfColumns = 67;
string[,] ColoumnTemp = new string[2, NumberOfColumns];
for (int i = 0; i < NumberOfColumns; i++)
{
if (columnName[i].ToString() == "MRD Cost Center")
{
columnName[i] = "Title";
}
//used to replace sepcial caharecters from sheet to SharePoint understandable code
var spformatColumnname =
columnName[i].ToString()
.Replace(" ", "_x0020_")
.Replace("/", "_x002f_")
.Replace("-", "_x002d_")
.Replace("(", "_x0028_")
.Replace(")", "_x0029_")
.Replace(",", "_x002c_")
.Replace("#", "_x0023_")
.Replace("@", "_x0040_")
.Replace("&", "_x0026_");

//because each column has only 32 char in its field name i.e internal name
if (spformatColumnname.Length > 32)
{
spformatColumnname = spformatColumnname.Substring(0, 32);
}

ColoumnTemp[0, i] = spformatColumnname;
}

string siteUrl = _constants.SourceURl;

var clientContext = new ClientContext(siteUrl);
List oList = clientContext.Web.Lists.GetByTitle(_constants.BudgetList);
var length = dt.Rows.Count - 1;

var Response = ValidateExcelSheetData(dt,filename,NumberOfColumns,ColoumnTemp);

if (Response == "Success")
{
for (int index = 1; index <= length - 1; index++)
{
var currentrow = dt.Rows[index].ItemArray;

var itemCreateInfo = new ListItemCreationInformation();
ListItem oListItem = oList.AddItem(itemCreateInfo);
for (int i = 0; i < NumberOfColumns; i++)
{
if (currentrow[i] != null)
{
if (i > 54)
{
var temp = true;
try
{
var converted = Convert.ToDouble(currentrow[i].ToString());
temp = false;
}
catch (Exception e)
{

}
if (temp)
{
deletefile(filename);
return "Invalid Value at row number " + index + 2 + " and " +
ColoumnTemp[0, i] + "coloumn ";
}
}
ColoumnTemp[1, i] = currentrow[i].ToString();
}
else
{
if (i > 54)
{
return "Invalid Value at row number " + index + 2 + " and " +
ColoumnTemp[0, i] + " coloumn ";
}
ColoumnTemp[1, i] = null;
}
}
for (int j = 0; j < NumberOfColumns - 1; j++)
{
oListItem[ColoumnTemp[0, j]] = ColoumnTemp[1, j];
}
oListItem.Update();
clientContext.ExecuteQuery();
}
}
else
{
return Response;
}

deletefile(filename);
return "Success";
}
catch (Exception e)
{

}

}
catch (Exception e)
{

}

#region excel interop

//import to sharepoint from excel with excel interop dll.
//try
//{
//// MyApp = new Excel.Application();
//// MyApp.Visible = false;
//// MyBook = MyApp.Workbooks.Open(path);
//// MySheet = (Excel.Worksheet)MyBook.Sheets[1];
//// var lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
//// var lastcolmn = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column;
//// // lastcolmn = 26;
//// var kk = lastRow;
//// char[] alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
//// var reminder = lastcolmn / 27;
//// var quotient = lastcolmn % 27;
//// string upperlimit = null;
//// if (reminder > 0)
//// upperlimit = alphabet[reminder - 1].ToString() + alphabet[quotient].ToString();
//// else
//// {
//// upperlimit = alphabet[quotient - 1].ToString();
//// }

//// //excel columns
//// System.Array ExcelColumnNames = (System.Array)MySheet.get_Range("A" + 3.ToString(), upperlimit + 3.ToString()).Cells.Value;

//// string[,] ColoumnTemp = new string[2, lastcolmn];

//// for (int i = 0; i < lastcolmn; i++)
//// {
//// ColoumnTemp[0, i] = ExcelColumnNames.GetValue(1, i + 1).ToString().Replace(" ", "_x0020_");
//// }

//// // Mapping Coloumns to Sharepoint List Coloumns
//// //coloumns of Sharepoint list

//// string siteUrl = ConfigurationManager.AppSettings["SiteUrl"].ToString();

//// ClientContext clientContext = new ClientContext(siteUrl);
//// SP.List oList = clientContext.Web.Lists.GetByTitle("Process Budget");

//// for (int index = 4; index <= lastRow; index++)
//// {
//// ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
//// ListItem oListItem = oList.AddItem(itemCreateInfo);

//// System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), upperlimit + index.ToString()).Cells.Value;

//// var bb = MyValues;
//// for (int i = 0; i < lastcolmn; i++)
//// {
//// if (MyValues.GetValue(1, i + 1) != null)
//// {
//// //if (MyValues.GetValue(1, i + 1).ToString()==@"Changes to HeadcountSelect ""Yes/NO"":")
//// //{
//// // ColoumnTemp[1, i] = "Changes_x0020_to_x0020_Headcount";
//// //}
//// //else if (MyValues.GetValue(1, i + 1).ToString() == @"Select Reason for Change:")
//// //{
//// // ColoumnTemp[1, i] = "Select_x0020_Reason_x0020_for_x0";
//// //}
//// //else if (MyValues.GetValue(1, i + 1).ToString() == @"Specify when Change will occur:")
//// //{
//// // ColoumnTemp[1, i] = "Specify_x0020_when_x0020_Change_";
//// //}
//// //else if (MyValues.GetValue(1, i + 1).ToString() == @"Key in Additional Comments")
//// //{
//// // ColoumnTemp[1, i] = "Select_x0020_Reason_x0020_for_x0";
//// //}
//// //else if (MyValues.GetValue(1, i + 1).ToString() == @"Benefits Excluding FICA")
//// //{
//// // ColoumnTemp[1, i] = "Select_x0020_Reason_x0020_for_x0";
//// //}
//// ColoumnTemp[1, i] = MyValues.GetValue(1, i + 1).ToString();
//// }
//// else
//// {
//// ColoumnTemp[1, i] = null;
//// }
//// }
//// for (int j = 0; j < lastcolmn - 1; j++)
//// {
//// oListItem[ColoumnTemp[0, j]] = ColoumnTemp[1, j];
//// }
//// oListItem.Update();
//// clientContext.ExecuteQuery();
//// }

//// MyApp.Workbooks.Close();
//// deletefile(filename);
// return true;
//}
//catch (Exception ex)
//{
// return false;
//}

#endregion

return "Upload Failed";
}

Method 3:(Open XML):

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace CompAndBen2013Web.Helper_Classes
{
public class ExcelUploadOpenXMl
{
private static char[] Letters =
{
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'
};

public bool UploadExcel(String filePath)
{

SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false);
SharedStringTable sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
string cellValue = null;
DataTable table = new DataTable();
int i = 0;
try
{
foreach (WorksheetPart worksheetPart in document.WorkbookPart.WorksheetParts)
{
foreach (SheetData sheetData in worksheetPart.Worksheet.Elements<SheetData>())
{
if (sheetData.HasChildren)
{
foreach (Row row in sheetData.Elements<Row>())
{
Hashtable hashtable = new Hashtable();

int CellIndex = 0;
int columnIndex = 0;
foreach (Cell cell in row.Elements<Cell>())
{
var g = cell.CellReference;
try
{

cellValue = cell.InnerText;

string compareVal = "";
if (CellIndex<26)
compareVal= "" + Letters[CellIndex] + "" + (i+1);
else
{

var lastRow = 0;
var lastcolmn = CellIndex;
char[] alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
var reminder = lastcolmn / 26;
var quotient = lastcolmn % 26;
string upperlimit = null;
if (reminder > 0)
upperlimit = alphabet[reminder - 1].ToString() + alphabet[quotient].ToString() + "" + (i + 1);
else
{
upperlimit = alphabet[quotient - 1].ToString()+ "" + (i + 1);
}

compareVal = upperlimit;
}

if (g == compareVal)
{
if (cell.DataType == CellValues.SharedString)
{
var vv = (sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);

if (i == 1)
{
table.Columns.Add(vv);
}
else if (i != 0 && i > 1)
{
hashtable[CellIndex] = NullToString(vv);
}

CellIndex++;
}
else
{
Console.WriteLine("cell val: " + cellValue);
}
}
else
{
char[] array = g.ToString().ToCharArray();

char currentExpectedIndexLetter = Letters[CellIndex];
char providedIndexLetter= array[0];
int FirstletterIndex = Array.IndexOf(Letters, array[0]);
int SecondLetterIndex = Array.IndexOf(Letters, array[1]);
int endindex = 0;
if (SecondLetterIndex >= 0)
{
endindex=(FirstletterIndex * 26) + SecondLetterIndex;
}
else
{
endindex = FirstletterIndex;
}

var diffrnc = endindex - CellIndex;
for (int p = 0; p < diffrnc; p++)
{
hashtable[CellIndex] = "";
CellIndex++;
}
var vv = "";

if (cell.DataType == CellValues.SharedString)
{
vv=(sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
}
if(vv=="")
{
vv = cellValue.ToString();
}

hashtable[CellIndex] = NullToString(vv);
CellIndex++;
}

}
catch (Exception ert)
{

var vv = cellValue.ToString();

hashtable[CellIndex] = NullToString(vv);
CellIndex++;
}
}

//testin code

var plt = hashtable[65];
plt = plt;
//==============
if (i != 0 && i>1)
{
DataRow workRow = table.NewRow();
//i have 65 columsn in excel so i added 65 of hastags you can use any number depends on your //requirment
table.Rows.Add(hashtable[0], hashtable[1], hashtable[2], hashtable[3], hashtable[4], hashtable[5],
hashtable[6], hashtable[7], hashtable[8], hashtable[9], hashtable[10],
hashtable[11], hashtable[12], hashtable[13], hashtable[14], hashtable[15],
hashtable[16], hashtable[17], hashtable[18], hashtable[19], hashtable[20],
hashtable[21], hashtable[22], hashtable[23], hashtable[24], hashtable[25],
hashtable[26], hashtable[27], hashtable[28], hashtable[29], hashtable[30],
hashtable[31], hashtable[32], hashtable[33], hashtable[34], hashtable[35],
hashtable[36], hashtable[37], hashtable[38], hashtable[39], hashtable[40],
hashtable[41], hashtable[42], hashtable[43], hashtable[44], NullToString(hashtable[45]),
NullToString(hashtable[46]), NullToString(hashtable[47]), NullToString(hashtable[48]), NullToString(hashtable[49]), NullToString(hashtable[50]),
NullToString(hashtable[51]), NullToString(hashtable[52]), NullToString(hashtable[53]), NullToString(hashtable[54]), NullToString(hashtable[55]),
NullToString(hashtable[56]), NullToString(hashtable[57]), NullToString(hashtable[58]), NullToString(hashtable[59]), NullToString(hashtable[60]),
NullToString( hashtable[61]),NullToString( hashtable[62]), NullToString(hashtable[63]), NullToString(hashtable[64]), NullToString(hashtable[65])
);
}

i++;
}
}
}
}
}
catch (Exception exc)
{

}
document.Close();
return true;
}

public static int? GetColumnIndexFromName(string columnName)
{
int? columnIndex = null;

string[] colLetters = Regex.Split(columnName, "([A-Z]+)");
colLetters = colLetters.Where(s => !string.IsNullOrEmpty(s)).ToArray();

if (colLetters.Count() <= 2)
{
int index = 0;
foreach (string col in colLetters)
{
List<char> col1 = colLetters.ElementAt(index).ToCharArray().ToList();
int? indexValue = Array.IndexOf(Letters,col1.ElementAt(index));

if (indexValue != -1)
{
// The first letter of a two digit column needs some extra calculations
if (index == 0 && colLetters.Count() == 2)
{
columnIndex = columnIndex == null ? (indexValue + 1) * 26 : columnIndex + ((indexValue + 1) * 26);
}
else
{
columnIndex = columnIndex == null ? indexValue : columnIndex + indexValue;
}
}

index++;
}
}

return columnIndex;
}

public static string GetColumnName(string cellReference)
{
// Create a regular expression to match the column name portion of the cell name.
Regex regex = new Regex("[A-Za-z]+");
Match match = regex.Match(cellReference);

return match.Value;
}

static string NullToString(object value)
{

// Value.ToString() allows for Value being DBNull, but will also convert int, double, etc.
return value == null ? " " : value.ToString();

// If this is not what you want then this form may suit you better, handles 'Null' and DBNull otherwise tries a straight cast
// which will throw if Value isn't actually a string object.
//return Value == null || Value == DBNull.Value ? "" : (string)Value;

}

}
}

Search This Blog

sharepoint

Comments

Post a Comment