public ExcelResult BiOpAnnualReportGridExcelDownload()
{
var biOpAnnualReportGridSpec = BiOpAnnualReportGridSpec(GridOutputFormat.Csv, out var rows, false);
var excel = OpenXmlSpreadSheetDocument.ObjectListToExcelWorksheet(rows, biOpAnnualReportGridSpec, "MainSheet");
return(new ExcelResult(excel, $"BiOpAnnualReport"));
}
public static FbmsInvoiceStageImports LoadFromXlsFile(Stream stream, int headerRowOffset)
{
var dataTable = OpenXmlSpreadSheetDocument.ExcelWorksheetToDataTable(stream, FbmsInvoiceStageImports.SheetName, FbmsInvoiceStageImports.UseExistingSheetNameIfSingleSheetFound, headerRowOffset);
return(FbmsInvoiceStageImports.LoadFromXlsFile(dataTable));
}
private void AddPersonSettingGridFilterMetaDataToExcelFileStream(MemoryStream stream, string gridName)
{
var session = HttpRequestStorage.FirmaSession;
if (session.IsAnonymousOrUnassigned())
{
return;
}
var personSettingGridTable = HttpRequestStorage.DatabaseEntities.PersonSettingGridTables.FirstOrDefault(x => x.GridName == gridName);
var tableColumns = personSettingGridTable?.PersonSettingGridColumns.OrderBy(x => x.SortOrder).ToList();
var tableColumnFiltersDictionary = tableColumns?.ToDictionary(x => x.ColumnName, x => x.PersonSettingGridColumnSettings.FirstOrDefault(y => y.PersonID == session.PersonID)?.PersonSettingGridColumnSettingFilters.Select(z => z.FilterText).ToList());
if (tableColumnFiltersDictionary != null)
{
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(stream, true))
{
// Add a blank WorksheetPart.
WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Any())
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Get the sheetData cell table.
SheetData sheetData = newWorksheetPart.Worksheet.GetFirstChild <SheetData>();
uint rowIndex = 0;
foreach (var tableColumn in tableColumnFiltersDictionary)
{
rowIndex++;
// Add a row to the cell table.
var row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
var cell = OpenXmlSpreadSheetDocument.InsertCellInWorksheet("A", rowIndex, newWorksheetPart);
cell.CellValue = new CellValue(tableColumn.Key);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
var bCellValue = string.Join(", ", tableColumnFiltersDictionary[tableColumn.Key]);
var cell2 = OpenXmlSpreadSheetDocument.InsertCellInWorksheet("B", rowIndex, newWorksheetPart);
cell2.CellValue = new CellValue(bCellValue);
cell2.DataType = new EnumValue <CellValues>(CellValues.String);
}
// Give the new worksheet a name.
string sheetName = $"Grid Filters";
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
sheets.Append(sheet);
}
}
}
public static FbmsBudgetStageImportPayrecV3UnexpendedBalances LoadFbmsBudgetStageImportPayrecV3UnexpendedBalancesFromXlsFile(Stream stream, int headerRowOffset)
{
var dataTable = OpenXmlSpreadSheetDocument.ExcelWorksheetToDataTable(stream, FbmsBudgetStageImportPayrecV3UnexpendedBalances.FbmsUnexpendedPayrecV3SheetName, FbmsBudgetStageImportPayrecV3UnexpendedBalances.UseExistingSheetNameIfSingleSheetFound, headerRowOffset);
return(FbmsBudgetStageImportPayrecV3UnexpendedBalances.LoadFromXlsFile(dataTable));
}
public static LoaStageImports LoadLoaStagesFromXlsFile(Stream stream, int headerRowOffset, List <string> errorList)
{
var dataTable = OpenXmlSpreadSheetDocument.ExcelWorksheetToDataTable(stream, LoaStageImports.FbmsUnexpendedPayrecV3SheetName, LoaStageImports.UseExistingSheetNameIfSingleSheetFound, headerRowOffset);
return(LoaStageImports.LoadFromXlsFile(dataTable, errorList));
}