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); }