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>();
}
protected internal Sheet(ref DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, ref DocumentFormat.OpenXml.Spreadsheet.Sheet sheet)
{
this.worksheetPart = worksheetPart;
this.worksheet = this.worksheetPart.Worksheet;
this.sheetData = this.worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
this.sheet = sheet;
}
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);
}
}
private static OpenXmlSpread.Cell GetCell(DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet,
string columnName, uint rowIndex)
{
OpenXmlSpread.Row row = GetRow(worksheet, rowIndex);
if (row == null)
{
return(null);
}
return(row.Elements <OpenXmlSpread.Cell>()
.Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0).First());
}
private void AutoFit(MSOpenXML.Worksheet worksheet)
{
MSOpenXML.MergeCells mergeCells = worksheet.Elements <MSOpenXML.MergeCells>().Count() > 0 ? mergeCells = worksheet.Elements <MSOpenXML.MergeCells>().First() : null;
List <MergeCell> cells = mergeCells != null?mergeCells.Elements <MergeCell>().ToList() : new List <MergeCell>();
Dictionary <string, int> d = new Dictionary <string, int>();
foreach (var row in worksheet.Descendants <Row>())
{
foreach (var cell in row.Elements <Cell>())
{
if (cell.CellValue == null)
{
continue;
}
if (cells.Exists(x => Contains(x.Reference.Value, cell.CellReference.Value)))
{
continue;
}
int s = cell.CellValue.Text.Length;
if (cell.StyleIndex != null)
{
if (cell.StyleIndex.Value == 1)
{
s = 10;
}
if (cell.StyleIndex.Value == 3)
{
s = decimal.Parse(cell.CellValue.Text, _en_us_ci.NumberFormat).ToString("n2").Length;
}
}
string c = GetColumnName(cell.CellReference);
if (d.ContainsKey(c))
{
d[c] = Math.Max(d[c], s);
}
else
{
d[c] = s;
}
}
}
Columns columns = new Columns();
foreach (var item in d)
{
columns.Append(CreateColumnData(GetColumnIndex(item.Key) + 1, GetColumnIndex(item.Key) + 1, item.Value * 1.2));
}
worksheet.InsertBefore(columns, worksheet.Elements <MSOpenXML.SheetData>().First());
}
private HelperSheet GetSheetByName(string sheetName)
{
var sheets = wbPart.Workbook.Descendants <Doc.Sheet>();
Doc.Sheet sheet = sheets.Where(s => s.Name == sheetName).FirstOrDefault();
if (sheet == null)
{
throw new ApplicationException($"Cannot find a sheet named {sheetName}");
}
WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
Doc.Worksheet worksheet = worksheetPart.Worksheet;
return(new HelperSheet(this, worksheet, sheetName));
}
/// <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));
}
private List <List <string> > SheetRange(string sheetName, uint firstRowNum, uint lastRowNum, string firstColumn, string lastColumn, bool IgnoreEmptyRows, SharedStringTablePart sharedStringTablePart = null, WorksheetPart worksheetPart = null)
{
SharedStringTablePart stringTable;
WorksheetPart wsPart;
if (sharedStringTablePart == null || worksheetPart == null)
{
GetSheetPart(sheetName, out stringTable, out wsPart);
}
else
{
stringTable = sharedStringTablePart;
wsPart = worksheetPart;
}
W worksheet = wsPart.Worksheet;
uint rowCount = lastRowNum - firstRowNum;
var lastColNum = GetExcelColumnNumber(lastColumn);
var firstColNum = GetExcelColumnNumber(firstColumn);
uint colCount = lastColNum - firstColNum;
var result = new List <List <string> >((int)rowCount);
// Iterate through the cells within the range and do whatever.
foreach (int row in Enumerable.Range((int)firstRowNum - 1, (int)rowCount))
{
var newRow = new List <string>((int)colCount);
if (result.Count % 100 == 0)
{
On100thRow.Invoke(this, new EventArgs());
}
foreach (var columnID in Enumerable.Range((int)firstColNum - 1, (int)colCount).Select(e => GetExcelColumnName(e)))
{
var cellRef = String.Format("{0}{1}", columnID, row);
var cell = wsPart.Worksheet.Descendants <Cell>().Where(c => c.CellReference == cellRef).FirstOrDefault();
newRow.Add(DecodedCell(cell, stringTable)?.Trim());
}
if (
(!IgnoreEmptyRows) || (!newRow.All(c => string.IsNullOrEmpty(c) || c.Equals("undef")))
)
{
result.Add(newRow);
}
}
return(result);
}
public ExcelHelper(string fileName, bool editable = true, int header = 1)
{
if (string.IsNullOrEmpty(fileName))
{
throw new Exception("Check if current filename is empty!");
}
if (!File.Exists(fileName))
{
throw new Exception("Please make sure current file exists!");
}
document = SpreadsheetDocument.Open(fileName, editable);
worksheet = document.GetWorksheet(0);
sheetData = worksheet.GetSheetData();
styleSheet = document.GetStylesheet();
this.Header = header;
this.cellFormatOption = CellFormatOption.OverWrite;
}
private MSOpenXML.Cell CreateCellIfNotExist(MSOpenXML.Worksheet worksheet, string cellName)
{
string columnName = GetColumnName(cellName);
uint rowIndex = GetRowIndex(cellName);
IEnumerable <MSOpenXML.Row> rows = worksheet.Descendants <MSOpenXML.Row>().Where(r => r.RowIndex.Value == rowIndex);
// If the Worksheet does not contain the specified row, create the specified row.
// Create the specified cell in that row, and insert the row into the Worksheet.
if (rows.Count() == 0)
{
MSOpenXML.Row row = new MSOpenXML.Row()
{
RowIndex = rowIndex
};
MSOpenXML.Cell cell = new MSOpenXML.Cell()
{
CellReference = cellName
};
row.Append(cell);
worksheet.Descendants <MSOpenXML.SheetData>().First().Append(row);
return(cell);
}
else
{
MSOpenXML.Row row = rows.First();
IEnumerable <MSOpenXML.Cell> cells = row.Elements <MSOpenXML.Cell>().Where(c => c.CellReference.Value == cellName);
// If the row does not contain the specified cell, create the specified cell.
if (cells.Count() == 0)
{
MSOpenXML.Cell cell = new MSOpenXML.Cell()
{
CellReference = cellName
};
row.Append(cell);
return(cell);
}
else
{
return(cells.First());
}
}
}
static void ReadSheet(Spreadsheet document, Package.SpreadsheetDocument importDocument, Excel.Sheet importSheet,
Dictionary <string, string> sharedStringTable, Dictionary <int, CellFormat> cellFormats)
{
Sheet sheet = document.Sheets.Sheet(importSheet.Name);
var importWorksheet = new Excel.Worksheet();
importWorksheet.Load((Package.WorksheetPart)importDocument.WorkbookPart.GetPartById(importSheet.Id));
var sheetData = (Excel.SheetData)importWorksheet.Elements <Excel.SheetData>().First();
foreach (var importColumn in sheetData.Elements <Excel.Column>())
{
ReadColumn();
}
foreach (var importRow in sheetData.Elements <Excel.Row>())
{
ReadRow(sheet, importRow, sharedStringTable, cellFormats);
}
}
/// <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);
}
}
}
}
private static spd.Cell GetCell(spd.Worksheet worksheet, string columnName, uint rowIndex)
{
var row = GetRow(worksheet, rowIndex);
if (row == null)
{
return(null);
}
// if (row.Hidden!=null)
// return null;
spd.Cell cell = null;
try
{
cell = row.Elements <spd.Cell>().ElementAt((int)(ColumnName)Enum.Parse(typeof(ColumnName), columnName));
}
catch (Exception)
{
// ignored
}
return(cell);
}
/*
* static Excel.SharedStringTable SaveSharedStringTable()
* {
* var exportedSharedStringTable = new Excel.SharedStringTable();
*
* return exportedSharedStringTable;
* }
*/
static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId)
{
var exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>();
string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart);
var exportedWorksheet = new Excel.Worksheet();
exportedWorksheetPart.Worksheet = exportedWorksheet;
var exportedColumns = new Excel.Columns();
exportedWorksheet.Append(exportedColumns);
var exportedSheetData = new Excel.SheetData();
exportedWorksheet.Append(exportedSheetData);
var exportedSheet = new Excel.Sheet()
{
Name = sheet.Name, Id = relId, SheetId = sheetId
};
if (sheet.Hidden)
{
exportedSheet.State = Excel.SheetStateValues.Hidden;
}
exportedSheets.Append(exportedSheet);
foreach (var column in sheet.Columns.OrderBy(r => r.Index))
{
SaveColumn(exportedColumns, column);
}
foreach (var row in sheet.Rows.OrderBy(r => r.Index))
{
SaveRow(exportedSheetData, styleSheet, cellFormatList, row);
}
exportedWorksheetPart.Worksheet.Save();
}
/// <summary>
/// Sets the column width
/// </summary>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="width">Width to set</param>
/// <returns>True if succesful</returns>
public static bool SetColumnWidth(DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, int columnIndex, int width)
{
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column column;
// Get the column collection exists
columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
if (columns == null)
{
return(false);
}
// Get the column
column = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).FirstOrDefault();
if (column == null)
{
return(false);
}
column.Width = width;
column.CustomWidth = true;
worksheet.Save();
return(true);
}
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);
}
public static xdr.Picture AddPicture(this x.Worksheet ws, string imagePath)
{
var worksheetDrawing = ws.GetWorksheetDrawing();
return(worksheetDrawing.AddPicture(imagePath));
}
//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);
}
}
/// <summary>
/// Sets a cell value with boolean value
/// </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="boolValue">Boolean value</param>
/// <param name="styleIndex">Style to use</param>
/// <param name="save">Save the worksheet</param>
/// <returns>True if succesful</returns>
public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, bool boolValue, uint?styleIndex, bool save = true)
{
string columnValue = boolValue ? "1" : "0";
return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean, columnValue, styleIndex, save));
}
/// <summary>
/// Sets a string value to a cell
/// </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="stringValue">String value to set</param>
/// <param name="useSharedString">Use shared strings? If true and the string isn't found in shared strings, it will be added</param>
/// <param name="save">Save the worksheet</param>
/// <returns>True if succesful</returns>
public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, string stringValue, uint?styleIndex, bool useSharedString = false, bool save = true)
{
string columnValue = stringValue;
DocumentFormat.OpenXml.Spreadsheet.CellValues cellValueType;
// Add the shared string if necessary
if (useSharedString)
{
if (ExcelProc.IndexOfSharedString(spreadsheet, stringValue) == -1)
{
ExcelProc.AddSharedString(spreadsheet, stringValue, true);
}
columnValue = ExcelProc.IndexOfSharedString(spreadsheet, stringValue).ToString();
cellValueType = DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString;
}
else
{
cellValueType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, cellValueType, columnValue, styleIndex, save));
}
//This function is created by Apt to append header cell with corresponding column
private static void AppendHeaderCell(string cellReference, string cellStringValue, Row excelRow, uint columnIndex, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet)
{
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
// Add a new Excel Cell to our Row
Cell cell = new Cell()
{
CellReference = cellReference, DataType = CellValues.String
};
CellValue cellValue = new CellValue();
cellValue.Text = cellStringValue;
cell.Append(cellValue);
excelRow.Append(cell);
columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
// Check if the column collection exists
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);
}
}
public void SetActiveSheet(int index)
{
this.Save();
this.worksheet = document.GetWorksheet(index);
this.sheetData = worksheet.GetSheetData();
}
public void SetActiveSheet(string sheetName)
{
this.Save();
this.worksheet = document.GetWorksheet(sheetName);
this.sheetData = worksheet.GetSheetData();
}
public dynamic CreateJsonFromCSV(string filePath)
{
int recordNumber = 1;
Resource resource = new Resource();
List <dynamic> ResourcesList = new List <dynamic>();
List <dynamic> organizationsList = new List <dynamic>();
List <dynamic> articlesList = new List <dynamic>();
List <dynamic> organizationReviewsList = new List <dynamic>();
List <dynamic> Resources = new List <dynamic>();
List <string> sheetNames = new List <string>()
{
Constants.ArticleSheetName, Constants.ArticleSectionSheetName, Constants.VideoSheetName, Constants.AdditionalReadingSheetName, Constants.FormSheetName, Constants.OrganizationSheetName, Constants.OrganizationReviewSheetName, Constants.RelatedLinkSheetName
};
try
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(filePath, true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
Spreadsheet.Sheets sheets = workbookPart.Workbook.GetFirstChild <Spreadsheet.Sheets>();
foreach (Spreadsheet.Sheet sheet in sheets)
{
if (sheet.Name.HasValue && sheetNames.Find(a => a == sheet.Name.Value) != null)
{
Spreadsheet.Worksheet worksheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
Spreadsheet.SheetData sheetData = worksheet.Elements <Spreadsheet.SheetData>().First();
Spreadsheet.SharedStringTable sharedStringTable = spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable;
Dictionary <string, string> keyValuePairs = new Dictionary <string, string>();
string cellValue;
int counter = 0;
bool isValidated = false;
ClearVariableData();
topicTagIds = new List <TopicTag>();
locations = new List <Shared.Models.Location>();
string resourceIdCell = string.Empty;
string resourceType = GetResourceType(sheet.Name.Value);
foreach (Spreadsheet.Row row in sheetData.Elements <Spreadsheet.Row>())
{
if (counter == 1)
{
var resourceIdColumn = from a in keyValuePairs where a.Key == "Id" select a.Value.First().ToString();
if (resourceIdColumn.Count() > 0)
{
resourceIdCell = resourceIdColumn.First();
}
}
foreach (Spreadsheet.Cell cell in row.Elements <Spreadsheet.Cell>())
{
cellValue = cell.InnerText;
if (string.IsNullOrEmpty(cellValue))
{
if (!string.IsNullOrEmpty(resourceIdCell) && cell.CellReference == string.Concat(resourceIdCell + row.RowIndex))
{
cell.CellValue = new CellValue(Guid.NewGuid().ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.String);
workbookPart.Workbook.Save();
}
}
else if (!string.IsNullOrEmpty(cellValue))
{
string cellActualValue = string.Empty;
if (cell.DataType != null && cell.DataType == Spreadsheet.CellValues.SharedString)
{
cellActualValue = sharedStringTable.ElementAt(Int32.Parse(cellValue, CultureInfo.InvariantCulture)).InnerText;
}
else
{
cellActualValue = cellValue;
}
if (counter == 0)
{
keyValuePairs.Add(cellActualValue, cell.CellReference);
}
else
{
var headerValues = from a in keyValuePairs select a.Key;
if (!isValidated)
{
if (!ValidateHeader(headerValues.ToArray <string>(), recordNumber, resourceType))
{
break;
}
else
{
isValidated = true;
}
}
IEnumerable <string> keyValue = null;
if (cell.CellReference.Value.Length == 2)
{
keyValue = from a in keyValuePairs where a.Value.Take(1).First() == cell.CellReference.Value.Take(1).First() select a.Key;
}
else if (cell.CellReference.Value.Length == 3)
{
keyValue = from a in keyValuePairs where a.Value.Take(2).First() == cell.CellReference.Value.Take(2).First() select a.Key;
}
else if (cell.CellReference.Value.Length == 4)
{
keyValue = from a in keyValuePairs where a.Value.Take(3).First() == cell.CellReference.Value.Take(3).First() select a.Key;
}
if (keyValue.Count() > 0)
{
UpdateFormData(keyValue, cellActualValue, resourceType);
}
}
}
}
if (counter > 0)
{
InsertTopics topic = new InsertTopics();
locations = topic.GetLocations(state, county, city, zipcode);
if (resourceType == Constants.FormResourceType)
{
Form form = new Form()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
form.Validate();
ResourcesList.Add(form);
ClearVariableData();
}
if (resourceType == Constants.OrganizationResourceType)
{
Organization organization = new Organization()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceCategory = resourceCategory,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Address = address,
Telephone = telephone,
Overview = overview,
Specialties = specialties,
EligibilityInformation = eligibilityInformation,
Qualifications = qualifications,
BusinessHours = businessHours,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
organization.Validate();
organizationsList.Add(organization);
ClearVariableData();
}
if (resourceType == Constants.OrganizationReview)
{
orgNameList.Add(organizationName);
orgFullNameList.Add(reviewerFullName);
orgTitleList.Add(reviewerTitle);
orgReviewTextList.Add(reviewText);
orgReviewerImageList.Add(reviewerImage);
ClearVariableData();
}
if (resourceType == Constants.ArticleResourceType)
{
Article article = new Article()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Overview = overview,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
article.Validate();
articlesList.Add(article);
ClearVariableData();
}
if (resourceType == Constants.ArticleContent)
{
articleNameList.Add(articleName);
headlineList.Add(headline);
contentList.Add(content);
ClearVariableData();
}
if (resourceType == Constants.VideoResourceType)
{
Video video = new Video()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceCategory = resourceCategory,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Overview = overview,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
video.Validate();
ResourcesList.Add(video);
ClearVariableData();
}
if (resourceType == Constants.AdditionalReadingResourceType)
{
AdditionalReading additionalReading = new AdditionalReading()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
additionalReading.Validate();
ResourcesList.Add(additionalReading);
ClearVariableData();
}
if (resourceType == Constants.RelatedLinkResourceType)
{
RelatedLink relatedLink = new RelatedLink()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
relatedLink.Validate();
ResourcesList.Add(relatedLink);
ClearVariableData();
}
}
counter++;
recordNumber++;
}
}
}
}
foreach (var resourceList in organizationsList)
{
List <OrganizationReviewer> organizationReviewer = new List <OrganizationReviewer>();
OrganizationReviewer orgReviewer = new OrganizationReviewer();
for (int iterator = 0; iterator < orgNameList.Count; iterator++)
{
var na = orgNameList[iterator];
if (resourceList.Name == orgNameList[iterator])
{
orgReviewer = new OrganizationReviewer
{
ReviewerFullName = orgFullNameList[iterator],
ReviewerTitle = orgTitleList[iterator],
ReviewText = orgReviewTextList[iterator],
ReviewerImage = orgReviewerImageList[iterator]
};
organizationReviewer.Add(orgReviewer);
}
}
var serializedResult = JsonConvert.SerializeObject(organizationReviewer);
var orgReviewData = JsonConvert.DeserializeObject(serializedResult);
resourceList.Reviewer = organizationReviewer;
ResourcesList.Add(resourceList);
}
foreach (var articleList in articlesList)
{
List <ArticleContent> articleContentList = new List <ArticleContent>();
ArticleContent articleContents = new ArticleContent();
for (int iterator = 0; iterator < articleNameList.Count; iterator++)
{
var na = articleNameList[iterator];
if (articleList.Name == articleNameList[iterator])
{
articleContents = new ArticleContent
{
Headline = headlineList[iterator],
Content = contentList[iterator],
};
articleContentList.Add(articleContents);
}
}
var serializedResult = JsonConvert.SerializeObject(articleContentList);
var articleContentData = JsonConvert.DeserializeObject(serializedResult);
articleList.Contents = articleContentList;
ResourcesList.Add(articleList);
}
}
catch (Exception ex)
{
InsertTopics.ErrorLogging(ex, recordNumber);
Resources = null;
}
Resources = ResourcesList;
return(Resources);
}
/// <summary>
/// Sets a cell value with double number
/// </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="doubleValue">Double value</param>
/// <param name="styleIndex">Style to use</param>
/// <param name="save">Save the worksheet</param>
/// <returns>True if succesful</returns>
public static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, double doubleValue, uint?styleIndex, bool save = true)
{
#if EN_US_CULTURE
string columnValue = doubleValue.ToString();
#else
string columnValue = doubleValue.ToString().Replace(",", ".");
#endif
return(SetCellValue(spreadsheet, worksheet, columnIndex, rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues.Number, columnValue, styleIndex, save));
}
public static xdr.Shape AddShape(this x.Worksheet ws, a.ShapeTypeValues shapeType, string name = null)
{
var worksheetDrawing = ws.GetWorksheetDrawing();
return(worksheetDrawing.AddShape(shapeType, name));;
}
/// <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);
}
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);
}
