public HelperSheet(SettlementHistoryWorkbook parent, Doc.Worksheet worksheet, string sheetName)
{
_parent = parent;
_worksheet = worksheet;
this.SheetName = sheetName;
var rows = worksheet.GetFirstChild <Doc.SheetData>().Elements <Doc.Row>();
}
public IEnumerable <HelperRow> GetRows()
{
foreach (var row in _worksheet.GetFirstChild <Doc.SheetData>().Elements <Doc.Row>())
{
yield return(new HelperRow(_parent, SheetName, row));
}
}
private static void ReadExcelData(ClientContext clientContextobj, string FileName)
{
const string lstDocName = "Documents";
try
{
DataTable datatable = new DataTable("TempExcelDataTable");
List list = clientContextobj.Web.Lists.GetByTitle(lstDocName);
clientContextobj.Load(list.RootFolder);
clientContextobj.ExecuteQuery();
string fileServerRelativeUrl = list.RootFolder.ServerRelativeUrl + "/" + "xlsheet.xlsx";
SP.File fileobj = clientContextobj.Web.GetFileByServerRelativeUrl(fileServerRelativeUrl);
ClientResult <System.IO.Stream> clientresult = fileobj.OpenBinaryStream();
clientContextobj.Load(fileobj);
clientContextobj.ExecuteQuery();
using (System.IO.MemoryStream mstream = new System.IO.MemoryStream())
{
if (clientresult != null)
{
clientresult.Value.CopyTo(mstream);
using (SpreadsheetDocument document = SpreadsheetDocument.Open(mstream, false))
{
WorkbookPart WBPart = document.WorkbookPart;
IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart WBPart1 = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = WBPart1.Worksheet;
SheetData sheetdata = worksheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetdata.Descendants <Row>();
foreach (Cell cellvalue in rows.ElementAt(0))
{
string str = GetCellValue(document, cellvalue);
datatable.Columns.Add(str);
}
foreach (Row row in rows)
{
if (row != null)
{
DataRow datarow = datatable.NewRow();
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
datarow[i] = GetCellValue(document, row.Descendants <Cell>().ElementAt(i));
}
datatable.Rows.Add(datarow);
}
}
datatable.Rows.RemoveAt(0);
}
}
}
ReadData(datatable);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.ReadKey();
}
private static DocumentFormat.OpenXml.Spreadsheet.Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
string cellReference = columnName + rowIndex;
DocumentFormat.OpenXml.Spreadsheet.Row lastRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().LastOrDefault();
// If the worksheet does not contain a row with the specified row index, insert one.
DocumentFormat.OpenXml.Spreadsheet.Row row;
if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).First();
//set auto height -- don't know how this line is worked
sheetData.InsertAfter(new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = (lastRow.RowIndex + 1)
}, lastRow);
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return(row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == cellReference).First());
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellReference
};
row.InsertBefore(newCell, refCell);
worksheet.Save();
return(newCell);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="OpenXmlExcelWriteManager" /> class.
/// </summary>
public OpenXmlExcelWriteManager(OpenXmlSpreadsheet.Worksheet worksheet)
{
if (worksheet == null)
{
throw new ArgumentNullException("worksheet");
}
this.worksheet = worksheet;
this.sheetData = worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>();
this.columnLetterStore = new Dictionary <uint, string>();
this.excelColumns = new Dictionary <uint, OpenXmlSpreadsheet.Column>();
this.excelRows = new Dictionary <uint, OpenXmlSpreadsheet.Row>();
}
public List <List <string> > Sheet(string sheetName, bool IgnoreEmptyRows)
{
GetSheetPart(sheetName, out SharedStringTablePart stringTable, out WorksheetPart wsPart);
W worksheet = wsPart.Worksheet;
IEnumerable <Row> rows = worksheet.GetFirstChild <SheetData>().Elements <Row>();
uint firstRowNum = rows.Select(r => r.RowIndex).Min();
uint lastRowNum = rows.Select(r => r.RowIndex).Max();
var columns = rows.SelectMany(r => r.Elements <Cell>()).Distinct().OrderBy(c => ColumnComparer.GetColumnName(c.CellReference), new ColumnComparer()).Select(c => ColumnComparer.GetColumnName(c.CellReference));
string firstColumn = columns.First();
string lastColumn = columns.Last();
return(SheetRange(sheetName, firstRowNum, lastRowNum, firstColumn, lastColumn, IgnoreEmptyRows, stringTable, wsPart));
}
/// <summary>
/// Merges all cells that have been marked to be merged in the cellinfos dictionary.
/// </summary>
/// <param name="worksheet">The worksheet.</param>
/// <param name="totalRowCount">The total number of rows in the worksheet.</param>
/// <param name="totalColCount">The total number of columns in the worksheet.</param>
/// <param name="cellInfos">A dictionary of cell information, keyed by row and column index.</param>
private static void MergeCells(OpenXmlSpreadsheet.Worksheet worksheet, uint totalRowCount, uint totalColCount, LayeredCellsDictionary cellInfos)
{
OpenXmlSpreadsheet.SheetData sheetData = worksheet.GetFirstChild <OpenXmlSpreadsheet.SheetData>();
// Process the collection of merged cells.
for (uint worksheetRow = 1; worksheetRow <= totalRowCount; worksheetRow++)
{
for (uint worksheetCol = 1; worksheetCol <= totalColCount; worksheetCol++)
{
// Get cellInfo, if it has a MergeTo then merge it.
var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow);
ExcelCellInfo cellInfo = cellInfos[currentCoOrdinate].CellInfo;
if (cellInfo.MergeTo != null)
{
sheetData.MergeCells(cellInfo.Cell, cellInfo.MergeTo.Cell);
}
}
}
}
public static xdr.WorksheetDrawing GetWorksheetDrawing(this x.Worksheet ws)
{
var drawingsPart = ws.WorksheetPart.GetPartsOfType <DrawingsPart>().FirstOrDefault();
if (drawingsPart == null)
{
var count = ws.WorksheetPart.Parts.Count();
drawingsPart = ws.WorksheetPart.AddNewPart <DrawingsPart>("rId" + (count + 1));
}
var drawingsPartId = ws.WorksheetPart.GetIdOfPart(drawingsPart);
var worksheetDrawing = drawingsPart.WorksheetDrawing;
if (worksheetDrawing == null)
{
worksheetDrawing = new xdr.WorksheetDrawing();
worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
drawingsPart.WorksheetDrawing = worksheetDrawing;
}
var drawing = ws.GetFirstChild <x.Drawing>();
if (drawing == null)
{
drawing = new x.Drawing()
{
Id = drawingsPartId
};
ws.Append(drawing);
}
var wbPart = ws.WorksheetPart.ParentPartOfType <WorkbookPart>();
wbPart.Workbook.Save();
return(worksheetDrawing);
}
/// <summary>
/// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="valueType">Type of the value</param>
/// <param name="value">The actual value</param>
/// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param>
/// <param name="save">Save the worksheet?</param>
/// <returns>True if succesful</returns>
private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Row row;
DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null;
DocumentFormat.OpenXml.Spreadsheet.Cell cell;
DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null;
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
string cellAddress = ExcelProc.ColumnNameFromIndex(columnIndex) + rowIndex;
// Check if the row exists, create if necessary
if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First();
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowIndex
};
//sheetData.Append(row);
for (uint counter = rowIndex - 1; counter > 0; counter--)
{
previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
if (previousRow != null)
{
break;
}
}
sheetData.InsertAfter(row, previousRow);
}
// Check if the cell exists, create if necessary
if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
{
cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First();
}
else
{
// Find the previous existing cell in the row
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == ExcelProc.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
if (previousCell != null)
{
break;
}
}
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellAddress
};
row.InsertAfter(cell, previousCell);
}
// Check if the column collection exists
columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
if (columns == null)
{
columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
}
// Check if the column exists
if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
// Find the previous existing column in the columns
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(
new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
// Add the value
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value);
if (styleIndex != null)
{
cell.StyleIndex = styleIndex;
}
if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date)
{
cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType);
}
if (save)
{
worksheet.Save();
}
return(true);
}
//Get Excel file Details
public static void GetExcelFileDetails(ClientContext ClientContext)
{
List Empoyeelist = ClientContext.Web.Lists.GetByTitle("UserDocuments");
CamlQuery CamlQuery1 = new CamlQuery();
CamlQuery1.ViewXml = "<View><RowLimit></RowLimit></View>";
ListItemCollection EmpCollection = Empoyeelist.GetItems(CamlQuery1);
ClientContext.Load(EmpCollection);
ClientContext.ExecuteQuery();
Microsoft.SharePoint.Client.File ExcelFile = EmpCollection[0].File;
ClientContext.Load(ExcelFile);
ClientContext.ExecuteQuery();
var FilePath1 = EmpCollection[0].File.ServerRelativeUrl;
var FileInfo1 = Microsoft.SharePoint.Client.File.OpenBinaryDirect(ClientContext, FilePath1);
if (ExcelFile != null)
{
// DiskFilePath = LocalFilePath + EmpCollection[0].File.Name;
try
{
var fileName = Path.Combine(ApplicationConfiguration.LocalFilePath + DiskFilePath, (string)EmpCollection[0].File.Name);
DiskFilePath = EmpCollection[0].File.Name;
if (System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
/****************Creates File in the specified path*****************/
using (var FileStream1 = System.IO.File.Create(fileName))
{
FileInfo1.Stream.CopyTo(FileStream1);
FileInfo1.Stream.Close();
FileStream1.Dispose();
}
}
catch (Exception exc)
{
Console.WriteLine("Exception exc : " + exc.Message);
ErrorWriteToLog.WriteToLogFile(exc);
}
}
string StrErrorMsg = string.Empty;
/*************************DataSet Different Approcach***************************************/
/*************************DataSet Different Approcach***************************************/
try
{
ExcelDataTable = new System.Data.DataTable("ExcelFileDataTable");
ClientResult <System.IO.Stream> Data = ExcelFile.OpenBinaryStream();
ClientContext.Load(ExcelFile);
ClientContext.ExecuteQuery();
using (System.IO.MemoryStream mStream = new System.IO.MemoryStream())
{
if (Data != null)
{
Data.Value.CopyTo(mStream);
using (SpreadsheetDocument Document1 = SpreadsheetDocument.Open(mStream, false))
{
IEnumerable <Sheet> Sheets1 = Document1.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string RelationshipId = Sheets1.First().Id.Value;
WorksheetPart WorksheetPart1 = (WorksheetPart)Document1.WorkbookPart.GetPartById(RelationshipId);
Worksheet WorkSheet1 = WorksheetPart1.Worksheet;
SheetData SheetData1 = WorkSheet1.GetFirstChild <SheetData>();
IEnumerable <Row> Rows = SheetData1.Descendants <Row>();
foreach (Cell Cell1 in Rows.ElementAt(0))
{
string StrCellValue = GetCellValue(ClientContext, Document1, Cell1);
ExcelDataTable.Columns.Add(StrCellValue);
}
foreach (Row RowLoop in Rows)
{
if (RowLoop != null)
{
DataRow DataRow1 = ExcelDataTable.NewRow();
for (int iterator = 0; iterator < RowLoop.Descendants <Cell>().Count(); iterator++)
{
DataRow1[iterator] = GetCellValue(ClientContext, Document1, RowLoop.Descendants <Cell>().ElementAt(iterator));
}
ExcelDataTable.Rows.Add(DataRow1);
}
}
ExcelDataTable.Rows.RemoveAt(0);
}
}
}
}
catch (Exception e)
{
Console.WriteLine("Exception exx " + e);
ErrorWriteToLog.WriteToLogFile(e);
}
}
public override string Parse(Stream stream, string fileName, ExecuteArgs param)
{
// bool flag = false;
stream.Position = 0;
using (var xl = SpreadsheetDocument.Open(stream, true))
{
//IEnumerable<DocumentFormat.OpenXml.Packaging.SharedStringTablePart> sp = xl.WorkbookPart.GetPartsOfType<DocumentFormat.OpenXml.Packaging.SharedStringTablePart>();
foreach (WorksheetPart part in xl.WorkbookPart.WorksheetParts)
{
var sharedStrings = ReadStringTable(xl.WorkbookPart.SharedStringTablePart);
Excel.Worksheet worksheet = part.Worksheet;
Excel.SheetData sd = worksheet.GetFirstChild <Excel.SheetData>();
var results = FindParsedCells(sharedStrings, sd);
foreach (Excel.Cell cell in results)
{
string val = ReadCell(cell, sharedStrings);
Regex re = new Regex("#.[^#]*#", RegexOptions.IgnoreCase);
MatchCollection mc = re.Matches(val);
foreach (Match m in mc)
{
object res = ParseString(param, m.Value.Trim("#<>".ToCharArray()));
if (res != null)
{
//flag = true;
Excel.Row newRow = null;
if (res is QResult query)
{
var sref = CellReference.Parse(cell.CellReference.Value);
int count = 0;
foreach (object[] dataRow in query.Values)
{
count++;
int col = sref.Col;
newRow = GetRow(sd, sref.Row, newRow == null, cell.Parent as Excel.Row);
foreach (object kvp in dataRow)
{
Excel.Cell ncell = GetCell(newRow, kvp, col, sref.Row, 0, sharedStrings);
if (ncell.Parent == null)
{
newRow.Append(ncell);
}
col++;
}
sref.Row++;
}
if (newRow != null)
{
uint rcount = newRow.RowIndex.Value;
foreach (var item in newRow.ElementsAfter())
{
if (item is Excel.Row)
{
rcount++;
((Excel.Row)item).RowIndex = rcount;
foreach (var itemCell in item.ChildElements)
{
if (itemCell is Excel.Cell)
{
var reference = CellReference.Parse(((Excel.Cell)itemCell).CellReference);
reference.Row = (int)rcount;
((Excel.Cell)itemCell).CellReference = reference.ToString();
}
}
}
}
}
}
else
{
val = val.Replace(m.Value, res.ToString());
WriteCell(cell, val, sharedStrings);
}
}
}
}
WriteStringTable(xl.WorkbookPart.SharedStringTablePart, sharedStrings);
}
}
stream.Flush();
return(stream is FileStream fileStream ? fileStream.Name : null);
}
private static spd.Row GetRow(spd.Worksheet worksheet, uint rowIndex)
{
return(worksheet.GetFirstChild <spd.SheetData>().
Elements <spd.Row>().ElementAt((int)rowIndex));
}
//Print Excel file Details
public static void printExcelFileDetails(ClientContext clientContext)
{
List emplist = clientContext.Web.Lists.GetByTitle("UserDocuments");
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View><RowLimit></RowLimit></View>";
ListItemCollection empcoll = emplist.GetItems(camlQuery);
clientContext.Load(empcoll);
clientContext.ExecuteQuery();
Microsoft.SharePoint.Client.File excelFile = empcoll[0].File;
clientContext.Load(excelFile);
clientContext.ExecuteQuery();
var filepath = empcoll[0].File.ServerRelativeUrl;
var fileInfo = Microsoft.SharePoint.Client.File.OpenBinaryDirect(clientContext, filepath);
Console.WriteLine("file path :" + filepath);
Console.WriteLine("file info :" + fileInfo);
Console.WriteLine("file name :" + excelFile.Name);
/***MyMyMy*****/
Microsoft.SharePoint.Client.File createfileinvs = empcoll[0].File;
if (createfileinvs != null)
{
FileInformation fileInfor = Microsoft.SharePoint.Client.File.OpenBinaryDirect(clientContext, createfileinvs.ServerRelativeUrl);
var fileName = Path.Combine(@"D:\DharanendraAssessment13-oct-2018\SharePointCSOMAssessment\SharePointCSOMAssessment", (string)empcoll[0].File.Name);
using (var fileStream = System.IO.File.Create(fileName))
{
fileInfo.Stream.CopyTo(fileStream);
}
}
/***MyMyMy*****/
/***************************************************************************************/
bool isError = true;
string strErrorMsg = string.Empty;
const string lstDocName = "Documents";
try
{
dataTable = new System.Data.DataTable("EmployeeExcelDataTable");
//List list = clientContext.Web.Lists.GetByTitle(lstDocName);
//clientContext.Load(list.RootFolder);
//clientContext.ExecuteQuery();
//string fileServerRelativeUrl = list.RootFolder.ServerRelativeUrl + "/" + fileName;
//Microsoft.SharePoint.Client.File file = clientContext.Web.GetFileByServerRelativeUrl(fileServerRelativeUrl);
ClientResult <System.IO.Stream> data = excelFile.OpenBinaryStream();
clientContext.Load(excelFile);
clientContext.ExecuteQuery();
using (System.IO.MemoryStream mStream = new System.IO.MemoryStream())
{
if (data != null)
{
data.Value.CopyTo(mStream);
using (SpreadsheetDocument document = SpreadsheetDocument.Open(mStream, false))
{
WorkbookPart workbookPart = document.WorkbookPart;
IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
string str = GetCellValue(clientContext, document, cell);
dataTable.Columns.Add(str);
}
foreach (Row row in rows)
{
if (row != null)
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < row.Descendants <Cell>().Count(); i++)
{
dataRow[i] = GetCellValue(clientContext, document, row.Descendants <Cell>().ElementAt(i));
Console.WriteLine("Cell data :" + GetCellValue(clientContext, document, row.Descendants <Cell>().ElementAt(i)));
}
dataTable.Rows.Add(dataRow);
}
}
dataTable.Rows.RemoveAt(0);
}
}
}
// UpdateSPList(clientContext, dataTable, fileName);
isError = false;
}
catch (Exception e)
{
throw;
}
finally
{
if (isError)
{
//Logging
}
}
/***************************************************************************************/
}
public void LoadSpreadsheet(int worksheetId, string fileName)
{
// Create new stopwatch
System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
// Begin timing
stopwatch.Start();
string cellAddress = "";
string sheetName = Path.GetFileName(fileName).Replace(".xlsx", "");
sheetName = Path.GetFileName(fileName).Replace(".xls", "");
savedCells = new Dictionary <string, Cell>();
allCells = new Dictionary <string, Cell>();
string workbookPath = fileName;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants <Sheet>();
int?colIndex = 0;
foreach (Sheet sheet in sheets)
{
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;
SharedStringTablePart sharedStringTablePart1;
if (spreadsheetDocument.WorkbookPart.GetPartsCountOfType
<SharedStringTablePart>() > 0)
{
sharedStringTablePart1 =
spreadsheetDocument.WorkbookPart.GetPartsOfType
<SharedStringTablePart>().First();
}
else//This is only needed to compile. If we don't have string table that means there are no strings
{
sharedStringTablePart1 =
spreadsheetDocument.WorkbookPart.AddNewPart
<SharedStringTablePart>();
}
var rows = worksheet.GetFirstChild <SheetData>().Elements <Row>();
foreach (var r in rows)
{
if (r.RowIndex <= 30)//30 rows
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell rCell in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
colIndex = GetColumnIndex(rCell.CellReference.ToString());
if (colIndex <= 12)
{
Cell c = new Cell();
cellAddress = rCell.CellReference.ToString().Replace("$", "");
c.readOnly = true;//until updated
c.isDerived = false;
c.row = int.Parse(r.RowIndex.ToString());
c.column = (int)colIndex;
c.cellAddress = cellAddress;
c.value = rCell.CellValue.Text;
if (cellAddress == "A3")
{
Console.WriteLine("A3");
}
//Add Formulas
if (rCell.CellFormula != null)
{
if (rCell.CellFormula.SharedIndex != null)
{
c.sharedForumlaIndex =
int.Parse(rCell.CellFormula.SharedIndex.ToString());
if (rCell.CellFormula.Text != "")
{
c.isSharedForumla = true;
}
}
c.fullFormula = rCell.CellFormula.Text;
c.formula = "=" + //Add =
(string)rCell.CellFormula.Text.Replace("$", ""); //Remove any $
string regex = @"\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?";
Match match = Regex.Match(c.formula, regex,
RegexOptions.IgnoreCase);
while (match.Success && c.formula.IndexOf(":") != -1)
{
if (match.Groups[0].Value.IndexOf(":") != -1)
{
//// Finally, we get the Group value and display it.
string key = match.Groups[0].Value;
string cellRef = key.Replace("$", "");
string newCells = RangeToSeries(key);
c.formula = c.formula.Replace(key, newCells);
}
match = Regex.Match(c.formula, regex,
RegexOptions.IgnoreCase);
}
}
else //String (use shared string table)
{
c.value = sharedStringTablePart1.SharedStringTable.ChildElements[int.Parse(c.value)].InnerText;
}
allCells.Add(cellAddress, c);
}
}
}
}
}
Console.WriteLine();
}
ReplaceSharedFormulas(ref allCells);
Console.WriteLine("Loaded from Excel:" + stopwatch.Elapsed);
//while (savedCells.Count < 360)
//{
foreach (Cell rCell in allCells.Values)
{
if (!savedCells.ContainsKey(rCell.cellAddress))
{
if (rCell.value == null) //Blank?
{
savedCells.Add(rCell.cellAddress, rCell);
}
else if (rCell.formula == null) //Input
{
rCell.readOnly = true; //until updated (May be label, May be input)
rCell.isDerived = false;
savedCells.Add(rCell.cellAddress, rCell);
}
else //Formula
{
bool allCellsHere = true;
foreach (string s in GetCellReferences((string)rCell.formula))
{
if (!savedCells.ContainsKey(s))
{
allCellsHere = false;
break;
}
else
{
if (savedCells[s].formula == null) //Is an input
{
savedCells[s].empty = false;
savedCells[s].readOnly = false;
savedCells[s].hasDependent = true;
}
//c.empty = false;//this cell impacts others
}
}
if (allCellsHere)
{
rCell.formula = rCell.formula.Replace("$", "");
rCell.readOnly = true;
rCell.isDerived = true;
savedCells.Add(rCell.cellAddress, rCell);
}
}
}
}
//}
Console.WriteLine("Organized Cells:" + stopwatch.Elapsed);
Dictionary <string, Cell> savedCellsTrimmed = new Dictionary <string, Cell>();
List <int> rowsToRemove = new List <int>();
bool includeRow = false;
for (int k = 30; k > 0; k--)
{
foreach (Cell c in savedCells.Values)
{
if (c.row == k)
{
if (c.value != "" || c.hasDependent)
{
includeRow = true;
break; //include row;
}
}
}
if (includeRow)
{
break;
}
else
{
rowsToRemove.Add(k);
}
}
foreach (Cell c in savedCells.Values)
{
if (!rowsToRemove.Contains(c.row))
{
savedCellsTrimmed.Add(c.cellAddress, c);
}
}
savedCells = savedCellsTrimmed;
DataTable dt = new DataTable();
dt.Columns.Add("worksheet_id");
dt.Columns.Add("row");
dt.Columns.Add("column");
dt.Columns.Add("cell_address");
dt.Columns.Add("value");
dt.Columns.Add("formula");
dt.Columns.Add("data_type");
dt.Columns.Add("format");
dt.Columns.Add("read_only");
dt.Columns.Add("is_derived");
dt.Columns.Add("has_dependent");
dt.Columns.Add("fore_color");
dt.Columns.Add("back_color");
dt.Columns.Add("font_family");
dt.Columns.Add("italics");
dt.Columns.Add("bold");
dt.Columns.Add("underline");
dt.Columns.Add("font_size");
foreach (Cell c in savedCells.Values)
{
DataRow row = dt.NewRow();
row[0] = worksheetId;
row[1] = c.row;
row[2] = c.column;
row[3] = c.cellAddress;
row[4] = c.value;
row[5] = c.formula;
row[6] = c.dataType;
row[7] = c.format;
row[8] = c.readOnly;
row[9] = c.isDerived;
row[10] = c.hasDependent;
row[11] = c.ForeColor;
row[12] = c.BackColor;
row[13] = c.FontFamily;
row[14] = c.Italics;
row[15] = c.Bold;
row[16] = c.Underline;
row[17] = c.FontSize;
dt.Rows.Add(row);
}
SqlConnection cn = new SqlConnection(ConnectionString);
cn.Open();
using (SqlBulkCopy copy = new SqlBulkCopy(cn))
{
copy.ColumnMappings.Add(0, 0);
copy.ColumnMappings.Add(1, 1);
copy.ColumnMappings.Add(2, 2);
copy.ColumnMappings.Add(3, 3);
copy.ColumnMappings.Add(4, 4);
copy.ColumnMappings.Add(5, 5);
copy.ColumnMappings.Add(6, 6);
copy.ColumnMappings.Add(7, 7);
copy.ColumnMappings.Add(8, 8);
copy.ColumnMappings.Add(9, 9);
copy.ColumnMappings.Add(10, 10);
copy.ColumnMappings.Add(11, 11);
copy.ColumnMappings.Add(12, 12);
copy.ColumnMappings.Add(13, 13);
copy.ColumnMappings.Add(14, 14);
copy.ColumnMappings.Add(15, 15);
copy.ColumnMappings.Add(16, 16);
copy.ColumnMappings.Add(17, 17);
copy.DestinationTableName = "worksheet_cell";
copy.WriteToServer(dt);
}
Console.WriteLine("Loaded DB:" + stopwatch.Elapsed);
}
private static OpenXmlSpread.Row GetRow(DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint rowIndex)
{
return(worksheet.GetFirstChild <OpenXmlSpread.SheetData>().Elements <OpenXmlSpread.Row>()
.Where(r => r.RowIndex == rowIndex).First());
}