// Create Excel Sheet
public void UpdateCells(string docName, IList <ExcelSheetInput> excelSheetInputs)
{
using (SpreadsheetDocument createdDocument = SpreadsheetDocument.Create(docName, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart createdWorkbookPart = createdDocument.AddWorkbookPart();
createdWorkbookPart.Workbook = new spd.Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart createdWorksheetPart = createdWorkbookPart.AddNewPart <WorksheetPart>();
createdWorksheetPart.Worksheet = new spd.Worksheet();
spd.Sheets createdSheets = createdWorkbookPart.Workbook.AppendChild(new spd.Sheets());
spd.Sheet createdSheet = new spd.Sheet()
{
Id = createdWorkbookPart.GetIdOfPart(createdWorksheetPart),
SheetId = 1,
Name = "Sheet 1"
};
createdSheets.Append(createdSheet);
createdWorkbookPart.Workbook.Save();
spd.SheetData sheetData = createdWorksheetPart.Worksheet.AppendChild(new spd.SheetData());
var rowsCount = excelSheetInputs.Last().RowNumber;
for (var rowsCounter = 1; rowsCounter <= rowsCount; rowsCounter++)
{
var rowItems = excelSheetInputs.Where(e => e.RowNumber == rowsCounter).ToList();
// Constructing header
spd.Row createdRow = new spd.Row();
foreach (ColumnName c in Enum.GetValues(typeof(ColumnName)))
{
try
{
var cellValue = rowItems.First(e => e.ColumnName.Equals(c.ToString()));
createdRow.Append(ConstructCell(cellValue.Text, spd.CellValues.String));
}
catch (Exception)
{
createdRow.Append(ConstructCell("", spd.CellValues.String));
}
}
sheetData.AppendChild(createdRow);
}
createdWorkbookPart.Workbook.Save();
}
}
private void SetActivityLogRows(SheetData sheetData, List <ActivityLogEntryData> activityLogEntry)
{
Row row;
foreach (var activityLog in activityLogEntry)
{
row = new Row();
row.Append(
CreateCell(activityLog.From, CellValues.String, 1),
CreateCell(activityLog.To, CellValues.String, 1),
CreateCell(activityLog.ElapsedTime, CellValues.String, 1),
CreateCell(activityLog.CumulativeTime, CellValues.String, 1),
CreateCell(activityLog.Depth, CellValues.String, 1),
CreateCell(activityLog.MudWeight, CellValues.String, 1),
CreateCell(activityLog.Activity, CellValues.String, 1),
CreateCell(activityLog.Milestone, CellValues.String, 1),
CreateCell(activityLog.Unplanned_Planned, CellValues.String, 1),
CreateCell(activityLog.HasNpt, CellValues.String, 1),
CreateCell(activityLog.NptReference, CellValues.String, 1),
CreateCell(activityLog.Description, CellValues.String, 1)
);
sheetData.Append(row);
}
}
protected void PopulateExcelTable(DataTable dataTable, excel.SheetData sheetData)
{
excel.Row excelRow = new excel.Row();
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelRow.Append(new excel.Cell()
{
CellValue = new excel.CellValue(GetColumnName(dataColumn))
});
}
sheetData.AppendChild(excelRow);
foreach (DataRow row in dataTable.Rows)
{
excelRow = new excel.Row();
foreach (object cellItem in row.ItemArray)
{
excelRow.Append(new excel.Cell()
{
CellValue = new excel.CellValue(cellItem.ToString())
});
}
sheetData.AppendChild(excelRow);
}
}
private void SetEmptyRow(SheetData sheetData)
{
Row row = new Row();
row.Append(
CreateCell("", CellValues.String)
);
sheetData.Append(row);
}
private void SetReportBudgetRows(SheetData sheetData, List <DailyReportBudgetData> reportBudgetData)
{
Row row;
List <CellDfn> cells;
foreach (var budgetData in reportBudgetData)
{
cells = new List <CellDfn>();
row = new Row();
row.Append(CreateCell(budgetData.LineItem, CellValues.String, 1));
foreach (var milestone in budgetData.Milestones)
{
row.Append(CreateCell(milestone, CellValues.String, 1));
}
sheetData.Append(row);
}
}
private void SetReportBudgetHeader(SheetData sheetData, List <string> reportBudgetHeaders)
{
Row row = new Row();
foreach (var header in reportBudgetHeaders)
{
row.Append(CreateCell(header, CellValues.String, 2));
}
sheetData.Append(row);
}
private void SetActivityLogHeader(SheetData sheetData, List <string> activityLogHeaders)
{
Row row = new Row();
List <CellDfn> cells = new List <CellDfn>();
foreach (var header in activityLogHeaders)
{
row.Append(CreateCell(header, CellValues.String, 2));
}
sheetData.Append(row);
}
private void SetColumnHeaders(SheetData sheetData)
{
Row row = new Row();
for (int i = 0; i < 12; i++)
{
Cell cell = null;
if (i == 6)
{
cell = CreateCell("DAILY REPORT SHEET", CellValues.String, 2);
row.Append(cell);
}
else
{
cell = CreateCell("", CellValues.String, 3);
row.Append(cell);
}
}
sheetData.Append(row);
}
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());
}
}
}
public void DescargarArchivos()
{
List <OrdenDTO> _ordenes = this.GetOrdenesConArchivos();
if (_ordenes.Count == 0)
{
Console.WriteLine("---------------------------------------------------------------------");
Console.WriteLine(String.Format("No hay ordenes en la tienda {0} para descargar PDFs", this._nombreTienda.ToUpper()));
Console.WriteLine("---------------------------------------------------------------------");
}
_ordenes.ForEach(orden =>
{
string _ordenFolder = $"{ this._workspace}\\{ orden.OrdenId}";
Console.WriteLine(_ordenFolder);
orden.Imprimir();
FileExcel cfile = new FileExcel();
try
{
orden.CustomData.ForEach(x => cfile.AgregarRow(x.Name, x.Value));
string urlLogoDistribuidor = orden.CustomData
.Where(x => x.Name.Contains("Logo"))
.Select(x => x.Value).FirstOrDefault();
string extension = Path.GetExtension(urlLogoDistribuidor);
this.DownloadFile(urlLogoDistribuidor, _ordenFolder, $"{orden.NombreArchivo}{extension}");
Row cabecera = new Row();
cabecera.Append(
cfile.ConstructCell("Valor", CellValues.String),
cfile.ConstructCell("Campo", CellValues.String)
);
cfile.SalvarExcel($"{_ordenFolder}\\{orden.NombreArchivo}.xlsx", "Michelin", cabecera);
}
catch (Exception e)
{
Console.WriteLine("No se pudo crear el archivo excel");
}
this.DownloadFile(orden.FilePdfURL, _ordenFolder, $"{orden.NombreArchivo}.pdf");
});
}
private void SetDailyReportInfo(SheetData sheetData, List <DailyReportInfo> dailyReportInfo)
{
Row row = new Row();
List <CellDfn> cells = new List <CellDfn>();
for (int i = 1; i <= dailyReportInfo.Count; i++)
{
row.Append(
CreateCell(dailyReportInfo[i - 1].Header, CellValues.String, 4),
CreateCell(dailyReportInfo[i - 1].Value, CellValues.String, 1),
CreateCell("", CellValues.String, 1)
);
if (i % 4 == 0)
{
sheetData.Append(row);
row = new Row();
}
}
}
static void SaveCell(Excel.Row exportedRow, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Cell cell)
{
Excel.Cell exportedCell = new Excel.Cell()
{
CellReference = CellIndex(cell.Column, cell.Row)
};
exportedCell.DataType = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.String);
switch (cell.Type)
{
case ExcelValueType.String:
exportedCell.CellValue = new Excel.CellValue(cell.StringValue);
exportedCell.DataType = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.String);
break;
case ExcelValueType.Number:
exportedCell.CellValue = new Excel.CellValue(cell.NumberValue.ToString());
exportedCell.DataType = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.Number);
break;
case ExcelValueType.Boolean:
exportedCell.CellValue = new Excel.CellValue((cell.BooleanValue ? 1 : 0).ToString());
exportedCell.DataType = new OpenXml.EnumValue <Excel.CellValues>(Excel.CellValues.Boolean);
break;
case ExcelValueType.Null:
break;
default:
throw new Exception("Unsupported type");
}
if (cell.CellFormat != null)
{
exportedCell.StyleIndex = cellFormatList[cell.CellFormat];
}
exportedRow.Append(exportedCell);
}
public static void Xlsx(string filepath, string printData)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row();
row.Append(new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellValue = new CellValue(printData), DataType = new EnumValue <CellValues>(CellValues.String)
});
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
worksheetPart.Worksheet.Save();
}
}
public IActionResult GetSaleListOnExcel([FromQuery] string searchObject)
{
dynamic ret = null;
try
{
SearchObject input = JsonConvert.DeserializeObject <SearchObject>(searchObject);
SaleList result = GetSaleList(input);
MemoryStream memoryStream = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Report"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row();
row.Append(
ConstructCell("From", CellValues.String),
ConstructCell(input.FromDate.ToString("dd/MM/yyyy"), CellValues.String),
ConstructCell("To", CellValues.String),
ConstructCell(input.ToDate.ToString("dd/MM/yyyy"), CellValues.String));
sheetData.AppendChild(row);
sheetData.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Row());
DocumentFormat.OpenXml.Spreadsheet.Row heading = new DocumentFormat.OpenXml.Spreadsheet.Row();
heading.Append(
ConstructCell("BillNo", CellValues.String),
ConstructCell("Customer", CellValues.String),
ConstructCell("Amount (Rs)", CellValues.String),
ConstructCell("Date(DD/MM/YYYY)", CellValues.String));
// Insert the header row to the Sheet Data
sheetData.AppendChild(heading);
// Inserting each employee
foreach (var eachSale in result.Sales)
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row();
row.Append(
ConstructCell(eachSale.BillId, CellValues.Number),
ConstructCell(eachSale.Customer.Name, CellValues.String),
ConstructCell(eachSale.RoundOffTotal.ToString(), CellValues.Number),
ConstructCell(eachSale.BillDate.ToString("dd/MM/yyyy"), CellValues.String));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
}
memoryStream.Position = 0;
ret = new FileStreamResult(memoryStream, "application/octet-stream");
}
catch (Exception ex)
{
}
return(ret);
}
/// <summary>
/// Write the content of the <see cref="ExcelMapCoOrdinateContainer"> to the worksheet</see>.
/// </summary>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="worksheetPart">The worksheet part.</param>
/// <param name="mapCoOrdinateContainer">The map co ordinate container.</param>
/// <param name="stylesManager">The styles manager.</param>
/// <param name="spreadsheetDocument">The spreadsheet document.</param>
public static void WriteMapToExcel(string sheetName,
WorksheetPart worksheetPart,
ExcelMapCoOrdinateContainer mapCoOrdinateContainer,
ExcelStylesManager stylesManager,
SpreadsheetDocument spreadsheetDocument)
{
TempDiagnostics.Output(string.Format("Writing map for ExcelMap[{0}] to worksheet '{1}'", mapCoOrdinateContainer.ContainerType, sheetName));
var dimensionConverter = new ExcelDimensionConverter("Calibri", 11.0f);
// Manages the writing to Excel.
var excelWriteManager = new OpenXmlExcelWriteManager(worksheetPart.Worksheet);
//** First we need to clear the destination worksheet down (rows, columns and merged areas)
excelWriteManager.EmptyWorksheet();
// Build up Columns models on all nested containers.
RowOrColumnsModel columnsModel = mapCoOrdinateContainer.BuildColumnsModel();
int colCount = columnsModel.Count();
// ==========================================================
// Traverse each column assigning start and end
// column indexes to the maps associated with that column.
// ==========================================================
RowOrColumnInfo columnInfo = columnsModel.First;
while (columnInfo != null)
{
double?width = columnInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.WidthToOpenXmlWidth(columnInfo.HeightOrWidth.Value) : null;
excelWriteManager.AddColumn(width, columnInfo.Hidden);
// Assign Excel start and end columns to maps for merge operations.
foreach (ExcelMapCoOrdinate map in columnInfo.Maps)
{
// Extend any cells that are merged across maps.
if (map is ExcelMapCoOrdinatePlaceholder)
{
var cell = map as ExcelMapCoOrdinatePlaceholder;
if (cell.MergeWith != null)
{
cell.ExcelColumnStart = cell.MergeWith.ExcelColumnStart;
cell.MergeWith.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol
}
}
if (map.ExcelColumnStart == 0)
{
map.ExcelColumnStart = columnInfo.ExcelIndex; //excelCol;
map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol;
}
else
{
map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol;
}
}
// Move on to next column in list
columnInfo = columnInfo.Next;
}
TempDiagnostics.Output(string.Format("Created ColumnsModel which contains '{0}' columns", colCount));
// Build up Rows models on all nested containers.
RowOrColumnsModel rowsModel = mapCoOrdinateContainer.BuildRowsModel();
int rowCount = rowsModel.Count();
// ==========================================================
// Traverse each row assigning start and end
// row indexes to the maps associated with that row.
// ==========================================================
RowOrColumnInfo rowInfo = rowsModel.First;
while (rowInfo != null)
{
double?height = rowInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.HeightToOpenXmlHeight(rowInfo.HeightOrWidth.Value) : null;
excelWriteManager.AddRow(height, rowInfo.Hidden);
// Assign Excel start and end rows to maps for merge operations.
foreach (ExcelMapCoOrdinate map in rowInfo.Maps)
{
if (map.ExcelRowStart == 0)
{
map.ExcelRowStart = rowInfo.ExcelIndex; //excelRow;
map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow;
}
else
{
map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow;
}
}
// Move on to next Row in list
rowInfo = rowInfo.Next;
}
TempDiagnostics.Output(string.Format("Created RowsModel which contains '{0}' rows", rowCount));
// Build a layered cells dictionary for all cells, keyed by Excel row and column index
var layeredCellsDictionary = new LayeredCellsDictionary();
mapCoOrdinateContainer.UpdateLayeredCells(ref layeredCellsDictionary);
TempDiagnostics.Output(string.Format("Updated Layered Cell Information for worksheet '{0}' = {1}", sheetName, layeredCellsDictionary.Count));
// Probe the Row, Column and Cell Layered maps, embellishing them with row, column and cell formatting information.
for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++)
{
for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++)
{
// We can now use the layeredCellsDictionary to build the
// excel workbook based on layered cell information
var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow);
LayeredCellInfo layeredCellInfo = layeredCellsDictionary[currentCoOrdinate];
// Work through the layered maps to determine what needs to be written to the Excel worksheet at that Row/Column
ProcessLayeredCellMaps(currentCoOrdinate, layeredCellInfo);
}
}
TempDiagnostics.Output(string.Format("Built Worksheet CellInfos[Cols={0},Rows={1}]", colCount, rowCount));
//** Write the 2D array of cell information to the Excel Worksheet
//** building a list of areas that are to be merged.
for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++)
{
OpenXmlSpreadsheet.Row row = excelWriteManager.GetRow(worksheetRow);
for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++)
{
// Pluck out information relating to the current cell
var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow);
ExcelCellInfo cellInfo = layeredCellsDictionary[currentCoOrdinate].CellInfo;
// Not sure if we need this as column letters are easily (quickly) translated using existing OpenXml helpers
string columnLetter = excelWriteManager.GetColumnLetter(worksheetCol);
// All merge cells should have the same style as the source merge cell.
if (cellInfo.MergeFrom != null)
{
// If MergeFrom is non-null, then this cell has been already been marked as a merge cell,
// whose style is to be the same as the source 'MergeFrom cell.
// Update the source (MergeFrom) cell so it ends up containing a reference to the last (MergeTo) cell to be merged.
cellInfo.MergeFrom.MergeTo = cellInfo;
// Create/lookup styles in the target workbook and return index of created style
uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.MergeFrom.StyleInfo);
// Write in to Excel (style information only)
var cell = new OpenXmlSpreadsheet.Cell();
cell.SetCellReference(columnLetter, worksheetRow);
cell.StyleIndex = cellStyleIndex;
row.Append(cell);
cellInfo.Cell = cell;
}
else
{
// Create/lookup styles in the target workbook and return index of created style
uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.StyleInfo);
// NB! If we write a Null value then we lose cell formatting information for some reason
object value = cellInfo.Value == null ? string.Empty : cellInfo.Value;
// Write in to Excel
var cell = new OpenXmlSpreadsheet.Cell();
cell.SetCellReference(columnLetter, worksheetRow);
excelWriteManager.SetCellValue(cell, value);
cell.StyleIndex = cellStyleIndex;
row.Append(cell);
cellInfo.Cell = cell;
}
// Span the cell accross it's parent columns if specified
if (cellInfo.LastSpanRow == 0)
{
cellInfo.LastSpanRow = worksheetRow;
}
if (cellInfo.LastSpanColumn == 0)
{
cellInfo.LastSpanColumn = worksheetCol;
}
// Merge cells if required (spanning)
for (uint rowIdx = worksheetRow; rowIdx <= cellInfo.LastSpanRow; rowIdx++)
{
for (uint colIdx = worksheetCol; colIdx <= cellInfo.LastSpanColumn; colIdx++)
{
// Mark processed (so we don't over-write)
var coOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx);
if (coOrdinate != currentCoOrdinate)
{
var mergeCoOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx);
ExcelCellInfo mergeCellInfo = layeredCellsDictionary[mergeCoOrdinate].CellInfo;
if (mergeCellInfo.MergeFrom == null)
{
mergeCellInfo.MergeFrom = cellInfo;
}
}
}
}
}
}
//#if DEBUG
// if (!skipMerge)
// {
//#endif
// Merge cells that have been marked to merge in the cellInfos dictionary.
MergeCells(worksheetPart.Worksheet, (uint)rowCount, (uint)colCount, layeredCellsDictionary);
//#if DEBUG
// }
//#endif
TempDiagnostics.Output(string.Format("Written CellInfos[Cols={0},Rows={1}] to Excel", colCount, rowCount));
// Create a list of all of the the defined names in the container.
var definedNameList = new List <ExcelDefinedNameInfo>();
mapCoOrdinateContainer.UpdateDefinedNameList(ref definedNameList, sheetName);
// And write into Excel workbook
foreach (var definedNameInfo in definedNameList)
{
uint rangeColumnCount = definedNameInfo.EndColumnIndex - definedNameInfo.StartColumnIndex + 1;
uint rangeRowCount = definedNameInfo.EndRowIndex - definedNameInfo.StartRowIndex + 1;
if (rangeRowCount > 0 && rangeColumnCount > 0)
{
spreadsheetDocument.WorkbookPart.Workbook.AddDefinedName(sheetName,
definedNameInfo.DefinedName,
(uint)definedNameInfo.StartColumnIndex,
(uint)definedNameInfo.StartRowIndex,
(int)rangeColumnCount,
(int)rangeRowCount);
}
}
TempDiagnostics.Output(string.Format("Defined Names added - write map for {0} complete...", mapCoOrdinateContainer.ContainerType));
}
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 void ExportList(List <ReportData> dueDates, string destination)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(destination, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Due Dates"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Create Header Row
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
headerRow.Append(
new Cell()
{
CellValue = new CellValue("FormMasterId"),
DataType = new EnumValue <CellValues>(CellValues.Number)
},
new Cell()
{
CellValue = new CellValue("Country"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("Region"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("TaxFormCode"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("Description"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("Domains"),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue("DueDate"),
DataType = new EnumValue <CellValues>(CellValues.Date)
}
);
sheetData.AppendChild(headerRow);
// Add Data Rows to SheetData
foreach (ReportData rd in dueDates)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
newRow.Append(
new Cell()
{
CellValue = new CellValue(rd.FormMasterId.ToString()),
DataType = new EnumValue <CellValues>(CellValues.Number)
},
new Cell()
{
CellValue = new CellValue(rd.Country),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.Region),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.TaxFormCode),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.Description),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.Domains),
DataType = new EnumValue <CellValues>(CellValues.String)
},
new Cell()
{
CellValue = new CellValue(rd.DueDate),
DataType = new EnumValue <CellValues>(CellValues.Date)
}
);
sheetData.AppendChild(newRow);
}
worksheetPart.Worksheet.Save();
}
}
/// <summary>
/// Updates a SpreadsheetDocument with new tabular data.
/// </summary>
/// <param name="ssdoc">
/// The excel document to update.</param>
/// <param name="json">
/// String in JSON format representing the tabular data for updating the Chart's cached data points.
/// The JSON object must contain a "fields" attribute as an array containing the field/column names.
/// The JSON object must contain a "rows" attribute as an array of arrays representing the rows and their values, with values matching the same order and cardinality of the field names.
/// This is the same data as the underlying Excel spreadsheet contents.</param>
/// <param name="sheetName">
/// The name of the Excel worksheet where the chart data originates from.
/// Used for updating the chart's cell references.</param>
/// <returns>
/// Returns the updated SpreadsheetDocument</returns>
public static SpreadsheetDocument Update(this SpreadsheetDocument ssdoc, string json, string sheetName)
{
if ((json == null) || (json == String.Empty))
{
json = "{\"fields\": [ \"No Results\" ], \"rows\": [[ \"No Results\" ]]}";
}
//Splunk JSON data is a series of objects consisting of multiple key(column)/value(row) pairs in the result attribute.
dynamic input = JsonConvert.DeserializeObject <dynamic>(json);
if (input["rows"].Count == 0)
{
json = "{\"fields\": [ \"No Results\" ], \"rows\": [[ \"No Results\" ]]}";
input = JsonConvert.DeserializeObject <dynamic>(json);
}
ss.Sheet sheet = ssdoc.WorkbookPart.Workbook.Descendants <ss.Sheet>().Where(s => s.Name.ToString() == sheetName).FirstOrDefault();
if (sheet == null)
{
sheet = ssdoc.WorkbookPart.Workbook.Descendants <ss.Sheet>().FirstOrDefault();
}
WorksheetPart worksheet = (WorksheetPart)ssdoc.WorkbookPart.GetPartById(sheet.Id);
ss.SheetData data = worksheet.Worksheet.GetFirstChild <ss.SheetData>();
//Remove all the rows after our column headers row. We'll replace them with new rows as the table is populated from the Splunk search results.
ss.Row firstRow = data.Elements <ss.Row>().First();
while (firstRow.NextSibling <ss.Row>() != null)
{
firstRow.NextSibling <ss.Row>().Remove();
}
ss.Row newHeader = new ss.Row();
newHeader.DyDescent = 0.25;
newHeader.RowIndex = 1;
var columnNames = input["fields"];
char startingHeaderColumn = 'A';
foreach (var column in columnNames)
{
string cellRef = startingHeaderColumn.ToString() + 1;
ss.Cell newCell = new ss.Cell();
ss.CellValue cv = new ss.CellValue(column.ToString());
newCell.CellReference = cellRef;
newCell.DataType = ss.CellValues.String;
newCell.Append(cv);
newHeader.Append(newCell);
startingHeaderColumn++;
}
data.InsertAfter(newHeader, data.Elements <ss.Row>().Last());
data.RemoveChild(firstRow);
for (int i = 0; i < input["rows"].Count; i++)
{
char startingColumn = 'A';
int startingColumnVal = 1;
int endingColumnVal = 0;
//Set the Excel row index (Excel index starts at 1, not zero and row 1 has headers so add 2 to the for index)
uint rowIndex = Convert.ToUInt32(i) + 2;
ss.Row newRow = new ss.Row();
newRow.DyDescent = 0.25;
newRow.RowIndex = rowIndex;
Debug.WriteLine(String.Format("Writing Excel Row {0}", i + 1));
var row = input["rows"][i];
foreach (var cell in row)
{
string cellRef = startingColumn.ToString() + rowIndex;
ss.Cell newCell = new ss.Cell();
ss.CellValue cv = new ss.CellValue(cell.ToString());
newCell.CellReference = cellRef;
if (startingColumn == 'A')
{
newCell.DataType = ss.CellValues.String;
}
newCell.Append(cv);
newRow.Append(newCell);
startingColumn++;
endingColumnVal++;
}
int numberOfCellsInRow = newRow.Descendants <ss.Cell>().Count();
ListValue <StringValue> spans = new ListValue <StringValue>();
spans.Items.Add(string.Format("{0}:{1}", startingColumnVal, endingColumnVal));
newRow.Spans = spans;
data.InsertAfter(newRow, data.Elements <ss.Row>().Last());
}
// Update Table Reference
Debug.WriteLine("Updating Table");
var table = worksheet.TableDefinitionParts.First().Table;
table.Reference = string.Format("A1:{0}{1}", GetExcelColumnName(input["fields"].Count), input["rows"].Count + 1);
table.TableColumns.RemoveAllChildren();
for (int i = 0; i < columnNames.Count; i++)
{
var newColumn = new ss.TableColumn();
newColumn.Id = new UInt32Value((uint)i + 1);
newColumn.Name = new StringValue(columnNames[i].ToString());
table.TableColumns.Append(newColumn);
}
return(ssdoc);
}