public ExcelTable Read()
{
var application = new Application();
var workbook = application.Workbooks.Open(_path);
var worksheet = workbook.Worksheets[1] as Worksheet;
var range = worksheet.UsedRange;
var valueArray = (object[,])range.get_Value(XlRangeValueDataType.xlRangeValueDefault);
var table = new ExcelTable();
for (int header = 1; header <= valueArray.GetLength(1); header++)
{
table.Headers.Add(valueArray[1, header].ToString());
}
for (int row = 2; row <= valueArray.GetLength(0); row++)
{
var dataRow = new List<string>();
for (int col = 1; col <= valueArray.GetLength(1); col++)
{
var value = valueArray[row, col];
dataRow.Add(value == null ? null : value.ToString());
}
table.Data.Add(dataRow);
}
workbook.Close();
Marshal.ReleaseComObject(workbook);
return table;
}
static void test()
{
Excel xls = new Excel();
ExcelTable table = new ExcelTable();
table.TableName = "1";
xls.Tables.Add(table);
ExcelHelper.SaveExcel(xls, "/Users/junfeixi/Desktop/test111.xlsx");
}
public Excel(ExcelWorkbook wb)
{
for (int i = 1; i <= wb.Worksheets.Count; i++)
{
ExcelWorksheet sheet = wb.Worksheets[i];
ExcelTable table = new ExcelTable(sheet);
Tables.Add(table);
}
}
void OnGUI()
{
if (mExcel != null)
{
EditorDrawHelper.DrawTableTab(mExcel, ref selectIndex);
mTable = mExcel.Tables[selectIndex];
EditorDrawHelper.DrawTable(mTable);
DrawButton();
}
}
public static void EvaluateTableTest5()
{
ExcelTable table = SetupTable5();
table.Evaluate();
string expected = "#ERROR #CYCLE\n" +
"#CYCLE #CYCLE 42\n" +
"3";
Assert.AreEqual(expected, table.ToString());
}
private static ExcelTable SetupTable1()
{
string query = "1 3 4 Příliš žluťoučký kůň úpěl ďábelské ódy\n" +
"1 234 []\n" +
"10 0 [] 0";
ExcelTable table = new ExcelTable();
TestUtils.SaveToFile(query, "Main.sheet");
table.Load("Main.sheet");
return(table);
}
/// <summary>
/// Returns data bounds of the Excel table with regards to header and totals row visibility
/// </summary>
/// <param name="table">Extended object</param>
/// <returns>Address range</returns>
public static ExcelAddress GetDataBounds(this ExcelTable table)
{
var dataBounds = new ExcelAddress(
table.Address.Start.Row + (table.ShowHeader && !table.Address.IsEmptyRange(table.ShowHeader) ? 1 : 0),
table.Address.Start.Column,
table.Address.End.Row - (table.ShowTotal ? 1 : 0),
table.Address.End.Column
);
return(dataBounds);
}
static void WriteXls()
{
Excel xls = new Excel();
ExcelTable table = new ExcelTable();
table.TableName = "test";
string outputPath = Application.dataPath + "/Test/Test2.xlsx";
xls.Tables.Add(table);
xls.Tables[0].SetValue(1, 1, Random.Range(1000,100000).ToString());
xls.ShowLog();
ExcelHelper.SaveExcel(xls, outputPath);
}
private void CreateHeadRow(ExcelTable tbl)
{
var row = new ExcelRow();
foreach (var col in this._colums)
{
row.InitSell(col.Value.Index - 1, true, col.Value.Title);
}
tbl.easy_addRow(row);
}
private static void WrapSummaryInTable(ExcelWorksheet sheet, int rowCount)
{
ExcelRange range = sheet.Cells[5, 1, 5 + rowCount, 9];
ExcelTable table = sheet.Tables.Add(range, "SummaryTable");
table.ShowHeader = true;
for (int i = 1; i <= 9; i++)
{
sheet.Column(i).AutoFit();
}
}
public Table(ExcelTable excelTable)
{
dataTable = GetDataTableFromExcelTable(excelTable);
firstColumnIsLabels = HasLabelsFirstColumn();
lastRowIsTotal = HasTotalLastRow();
name = excelTable.Name;
startRow = excelTable.Address.Start.Row;
startColumn = excelTable.Address.Start.Column;
endRow = excelTable.Address.End.Row;
endColumn = excelTable.Address.End.Column;
}
/// <summary>
///
/// </summary>
/// <param name="excelTable"></param>
/// <param name="columnName"></param>
/// <param name="cellAdress"></param>
public static void UpdateTableValues(ExcelTable excelTable, string columnName, string cellAdress)
{
var columnNameAdresse = ExcelHelpers.GetAdressFromColumnName(excelTable, columnName);
for (int month = 1; month <= 12; month++)
{
var cellToUpdate = ExcelHelpers.AddRowAndColumnToCellAddress(columnNameAdresse, month, 0);
var totalCellValue = ExcelHelpers.AddRowAndColumnToCellAddress(cellAdress, month, 0);
excelTable.WorkSheet.Cells[cellToUpdate].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
excelTable.WorkSheet.Cells[cellToUpdate].Formula = totalCellValue;
}
}
public static void EvaluateTableTest2()
{
ExcelTable table = SetupTable2();
table.Evaluate();
string expected = "[] 3 57\n" +
"19 99 42\n" +
"#INVVAL\n" +
"#DIV0 #CYCLE #ERROR\n" +
"#MISSOP #FORMULA";
Assert.AreEqual(expected, table.ToString());
}
public void ExcelCellExtractionTest()
{
var document = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger);
var table = new ExcelTable(document.GetWorksheet(0));
var cell = table.GetCell(new CellPosition("B9"));
cell.Should().NotBeNull();
cell.StringValue.Should().Be("Template:RootTemplate:B10:D11");
cell = table.GetCell(new CellPosition("ABCD4234"));
cell.Should().BeNull();
}
private static ExcelTable SetupTable5()
{
//A B C D
string query = "=A2+A3243 =B2*A3\n" +
"=B1*B123 =A2*C3 42\n" +
"3";
ExcelTable table = new ExcelTable();
TestUtils.SaveToFile(query, "Main.sheet");
table.Load("Main.sheet");
return(table);
}
static void WriteXls()
{
Excel xls = new Excel();
ExcelTable table = new ExcelTable();
table.TableName = "test";
string outputPath = Application.dataPath + "/Test/Test2.xlsx";
xls.Tables.Add(table);
xls.Tables[0].SetValue(1, 1, Random.Range(1000, 100000).ToString());
xls.ShowLog();
ExcelHelper.SaveExcel(xls, outputPath);
}
public static void AddHeadersToExcelTable(ExcelTable table, JArray jsonArray)
{
var columnsList = GetListOfKeyFromJArray(jsonArray);
//Set Columns position & name
var i = 0;
foreach (var property in columnsList)
{
table.Columns[i].Name = string.Concat(property);
i++;
}
}
/// <summary>
/// Generic extension method yielding objects of specified type from table.
/// </summary>
/// <remarks>Exceptions are not catched. It works on all or nothing basis.
/// Only primitives and enums are supported as property.
/// Currently supports only tables with header.</remarks>
/// <typeparam name="T">Type to map to. Type should be a class and should have parameterless constructor.</typeparam>
/// <param name="table">Table object to fetch</param>
/// <param name="skipCastErrors">Determines how the method should handle exceptions when casting cell value to property type.
/// If this is true, invalid casts are silently skipped, otherwise any error will cause method to fail with exception.</param>
/// <returns>An enumerable of the generating type</returns>
public static IEnumerable <T> AsEnumerable <T>(this ExcelTable table, bool skipCastErrors = false) where T : class, new()
{
IList mapping = PrepareMappings <T>(table);
ExcelAddress bounds = table.GetDataBounds();
// Parse table
for (int row = bounds.Start.Row; row <= bounds.End.Row; row++)
{
var item = (T)Activator.CreateInstance(typeof(T));
foreach (KeyValuePair <int, PropertyInfo> map in mapping)
{
object cell = table.WorkSheet.Cells[row, map.Key + table.Address.Start.Column].Value;
PropertyInfo property = map.Value;
try
{
TrySetProperty(item, property, cell);
// Validate parsed object according to data annotations
EntityValidationResult validationResult = DataAnnotation.ValidateEntity(item);
if (validationResult.HasError)
{
throw new AggregateException(validationResult.ValidationErrors.Select(x => new ValidationException(x.ErrorMessage)));
}
}
catch (Exception ex)
{
if (!skipCastErrors)
{
throw new ExcelTableConvertException(
"An error occured while parsing the cell",
ex,
new ExcelTableConvertExceptionArgs
{
ColumnName = table.Columns[map.Key].Name,
ExpectedType = property.PropertyType,
PropertyName = property.Name,
CellValue = cell,
CellAddress = new ExcelCellAddress(row, map.Key + table.Address.Start.Column)
}
);
}
}
}
yield return(item);
}
}
/// <summary>
/// Prepares mapping using the type and the attributes decorating its properties
/// </summary>
/// <typeparam name="T">Type to parse</typeparam>
/// <param name="table">Table to get columns from</param>
/// <param name="configuration"></param>
/// <returns>A list of mappings from column index to property</returns>
private static IEnumerable <ExcelTableColumnDetails> PrepareMappings <T>(ExcelTable table, ExcelReadConfiguration <T> configuration)
{
// Get only the properties that have ExcelTableColumnAttribute
List <ExcelTableColumnDetails> propertyInfoAndColumnAttributes = typeof(T).GetExcelTableColumnAttributesWithPropertyInfo();
// Build property-table column mapping
foreach (var propertyInfoAndColumnAttribute in propertyInfoAndColumnAttributes)
{
PropertyInfo propertyInfo = propertyInfoAndColumnAttribute.PropertyInfo;
ExcelTableColumnAttribute columnAttribute = propertyInfoAndColumnAttribute.ColumnAttribute;
if (columnAttribute.ColumnIndex <= 0 && string.IsNullOrEmpty(columnAttribute.ColumnName))
{
columnAttribute.ColumnName = propertyInfo.Name;
}
int col = -1;
// There is no case when both column name and index is specified since this is excluded by the attribute
// Neither index, nor column name is specified, use property name
if (columnAttribute.ColumnIndex == 0 && string.IsNullOrWhiteSpace(columnAttribute.ColumnName) &&
CheckColumnByNameIfExists(table, propertyInfo.Name, columnAttribute.IsOptional))
{
col = table.Columns[propertyInfo.Name].Position;
}
else if (columnAttribute.ColumnIndex > 0 &&
CheckColumnByIndexIfExists(table, columnAttribute.ColumnIndex - 1, columnAttribute.IsOptional)) // Column index was specified
{
col = table.Columns[columnAttribute.ColumnIndex - 1].Position;
}
else if (!string.IsNullOrWhiteSpace(columnAttribute.ColumnName) && table.Columns.FirstOrDefault(x => x.Name.Equals(columnAttribute.ColumnName, StringComparison.InvariantCultureIgnoreCase)) != null) // Column name was specified
{
col = table.Columns.First(x => x.Name.Equals(columnAttribute.ColumnName, StringComparison.InvariantCultureIgnoreCase)).Position;
}
if (!columnAttribute.IsOptional && col == -1)
{
throw new ExcelValidationException(string.Format(configuration.ColumnValidationExceptionMessage, columnAttribute.ColumnName ?? propertyInfo.Name))
.WithArguments(new ExcelExceptionArgs
{
ColumnName = columnAttribute.ColumnName,
ExpectedType = propertyInfo.PropertyType,
PropertyName = propertyInfo.Name,
CellValue = table.WorkSheet.Cells[table.Address.Start.Row, columnAttribute.ColumnIndex + table.Address.Start.Column].Value,
CellAddress = new ExcelCellAddress(table.Address.Start.Row, columnAttribute.ColumnIndex + table.Address.Start.Column)
});
}
yield return(new ExcelTableColumnDetails(col, propertyInfo, columnAttribute));
}
}
public async Task <bool> Excel(IJSRuntime iJSRuntime)
{
byte[] fileContents;
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage())
{
var workSheet = package.Workbook.Worksheets.Add("Foglio1");
workSheet.Cells[1, 1].Value = "TIPOLOGIA";
workSheet.Cells[1, 2].Value = "AZIENDA";
workSheet.Cells[1, 3].Value = "COGNOME";
workSheet.Cells[1, 4].Value = "NOME";
workSheet.Cells[1, 5].Value = "INDIRIZZO";
workSheet.Cells[1, 6].Value = "COMUNE";
workSheet.Cells[1, 7].Value = "TELEFONO";
workSheet.Cells[1, 8].Value = "EMAIL";
int recordIndex = 2;
foreach (var clienti in ElencoClienti())
{
workSheet.Cells[recordIndex, 1].Value = clienti.Tipologia;
workSheet.Cells[recordIndex, 2].Value = clienti.Azienda;
workSheet.Cells[recordIndex, 3].Value = clienti.Cognome;
workSheet.Cells[recordIndex, 4].Value = clienti.Nome;
workSheet.Cells[recordIndex, 5].Value = clienti.Indirizzo;
workSheet.Cells[recordIndex, 6].Value = clienti.Comune;
workSheet.Cells[recordIndex, 7].Value = clienti.Telefono;
workSheet.Cells[recordIndex, 8].Value = clienti.Email;
recordIndex++;
}
int firstRow = 1;
int lastRow = workSheet.Dimension.End.Row;
int firstColumn = 1;
int lastColumn = workSheet.Dimension.End.Column;
ExcelRange rg = workSheet.Cells[firstRow, firstColumn, lastRow, lastColumn];
string tableName = "Table1";
ExcelTable tab = workSheet.Tables.Add(rg, tableName);
tab.TableStyle = TableStyles.Medium7;
workSheet.View.ShowGridLines = false;
fileContents = package.GetAsByteArray();
}
try
{
var NomeFile = "Elenco_Clienti-" + DateTime.Now.ToString() + ".xlsx";
await iJSRuntime.InvokeAsync <Esporta>("saveAsFile", NomeFile, Convert.ToBase64String(fileContents));
return(true);
}
catch (Exception)
{
return(false);
}
}
static void WriteXls()
{
Excel xls = new Excel();
ExcelTable table = new ExcelTable();
table.TableName = "test";
string outputPath = System.IO.Path.Combine(Application.streamingAssetsPath, "Test2.xlsx");
xls.Tables.Add(table);
xls.Tables[0].SetValue(1, 1, Random.Range(1000, 100000).ToString());
xls.ShowLog();
ExcelHelper.SaveExcel(xls, outputPath);
}
DataTable GetDataTableFromExcelTable(ExcelTable excelTable, bool getFirstColumnLevel = false)
{
DataTable dataTable = new DataTable();
var sheet = excelTable.WorkSheet;
var columnLevelName = "Level";
var fromRow = excelTable.Address.Start.Row;
var toRow = excelTable.Address.End.Row;
var fromColumn = excelTable.Address.Start.Column;
var toColumn = excelTable.Address.End.Column;
var headerRow = fromRow;
var bodyFromRow = fromRow + 1;
var headerRange = sheet.Cells[headerRow, fromColumn, headerRow, toColumn];
foreach (var cell in headerRange)
{
var column = dataTable.Columns.Add(cell.Text, typeof(TableCell));
}
if (getFirstColumnLevel)
{
var column = dataTable.Columns.Add(columnLevelName, typeof(TableCell));
}
for (var rowNum = bodyFromRow; rowNum <= toRow; rowNum++)
{
var row = dataTable.NewRow();
for (var column = fromColumn; column <= toColumn; column++)
{
var cell = sheet.Cells[rowNum, column];
int i = cell.Start.Column - fromColumn;
row[i] = new TableCell(cell.Text, cell.Formula);
}
var firstCellTextLength = sheet.Cells[rowNum, fromColumn].Text.Length;
if (firstCellTextLength > 0)
{
dataTable.Rows.Add(row);
if (getFirstColumnLevel)
{
row[columnLevelName] = new TableCell(sheet.Cells[rowNum, fromColumn].Style.Indent, "");
}
}
}
return(dataTable);
}
private static ExcelTable SetupTable2()
{
//A B C D
string query = " \n" + // 1
" [] \n" + // 2
" 3 4 \n" + // 3
" 5 =B23 "; // 4
ExcelTable table = new ExcelTable();
TestUtils.SaveToFile(query, "Main.sheet");
table.Load("Main.sheet");
return(table);
}
private static ExcelTable SetupTable1()
{
//A B C D
string query = " A11 3 5 [] \n" + // 1
" [] [] 43 B12*B3\n" + // 2
" [] [] [] [] \n" + // 3
" [] "; // 4
ExcelTable table = new ExcelTable();
TestUtils.SaveToFile(query, "Main.sheet");
table.Load("Main.sheet");
return(table);
}
public static void CreateTable(ExcelPackage excel)
{
//Defining the tables parameters
var worksheet = excel.Workbook.Worksheets["EmployeeTable"];
ExcelRange rg = worksheet.Cells[7, 5, 12, 9];
string tableName = "TableEmpolyee";
//Ading a table to a Range
ExcelTable tab = worksheet.Tables.Add(rg, tableName);
//Formating the table style
tab.TableStyle = TableStyles.Light8;
}
private void Cond2ColorFormat(string ColumnName, int dataCount, ref ExcelTable tbl, ref ExcelWorksheet ew, int LowValue, Color LowColor, int HighValue, Color HighColor)
{
ExcelAddress e = new ExcelAddress(2, tbl.Columns[ColumnName].Position + 1, dataCount + 1, tbl.Columns[ColumnName].Position + 1);
var cf = ew.ConditionalFormatting.AddTwoColorScale(e);
cf.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cf.LowValue.Value = LowValue;
cf.LowValue.Color = LowColor;
cf.HighValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cf.HighValue.Value = HighValue;
cf.HighValue.Color = HighColor;
cf.Style.Font.Bold = true;
}
static void test()
{
Excel xls = new Excel();
ExcelTable table = new ExcelTable();
table.TableName = "test";
string outputPath = Application.dataPath + "/Test/Test2.xlsx";
xls.Tables.Add(table);
xls.Tables[0].SetValue(1, 1, "1");
xls.Tables[0].SetValue(1, 2, "2");
xls.Tables[0].SetValue(2, 1, "3");
xls.Tables[0].SetValue(2, 2, "4");
xls.ShowLog();
ExcelHelper.SaveExcel(xls, outputPath);
}
private void DumpModels(ExcelWorksheet ws, IEnumerable <StatObject> stat_objs)
{
StatObject[] objs = stat_objs.ToArray();
if (objs.Length == 0)
{
ws.Cells["A1"].Value = "None found";
ws.Column(1).AutoFit();
return;
}
int col = 1;
foreach (string str in objs[0].GetPropertyNames())
{
ws.Cells[1, col].Value = str;
col++;
}
int maxcolumn = col - 1;
for (int i = 0; i < objs.Length; i++)
{
col = 1;
foreach (object oo in objs[i].GetPropertyValues())
{
ws.Cells[2 + i, col].Value = oo;
col++;
}
}
ExcelRow rng = ws.Row(1);
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
rng.Style.Font.Color.SetColor(Color.White);
}
ExcelRange range1 = ws.Cells[1, 1, objs.Length + 1, maxcolumn];
ExcelTable table1 = ws.Tables.Add(range1, "tbl_" + Guid.NewGuid().ToString("N"));
table1.TableStyle = OfficeOpenXml.Table.TableStyles.Light1;
for (int i = 1; i <= maxcolumn; i++)
{
ws.Column(i).AutoFit();
}
}
public static void WriteBinaryFile(string outputDir, ExcelTable et)
{
if (!Directory.Exists(outputDir))
{
Directory.CreateDirectory(outputDir);
}
ExcelField primaryField = et.GetPrimaryField();
string content = string.Empty;
int rowCount = et.rowCount;
int columnCount = et.fields.Count;
for (int j = 0; j < rowCount - 6; j++)
{
for (int k = 0; k < columnCount; k++)
{
ExcelField field = et.fields[k];
if (field.name == "KeyDes") //输出CS对应的.txt文本文件时，不输出KeyDes字段
{
continue;
}
content += (field.datas[j] + "\t");
}
content = content.Remove(content.Length - 1);//移除行尾的\t字符//注意\t在串里，只有1个字符长度
content += "\r\n";
}
string path = outputDir + @"\" + et.tableName + ".bin";
if (!Setting.IsEncrypt)
{
//File.WriteAllText(path, content.TrimEnd(), Encoding.UTF8);
File.WriteAllText(path, content.TrimEnd(), new UTF8Encoding(false));//必须用no-bom的格式，否则读取的时候数据头会影响
}
else
{
content = Encrypt.TextEncrypt(content.TrimEnd(), Setting.EncryptKey);
byte[] array = Encoding.UTF8.GetBytes(content.TrimEnd());
FileStream fs = new FileStream(path, FileMode.Create);
fs.Write(array, 0, array.Length);
fs.Close();
}
Debug.Log("write file " + et.tableName + ".bin");
}
private static ExcelTable SetupTable2()
{
//A B C D
string query = "[] 3 =B1*A2\n" +
"19 =C1+C2 42\n" +
"auto\n" +
"=B2/A1 =A1-B4 =C2+A4\n" +
"=chyba =A1+autobus";
ExcelTable table = new ExcelTable();
TestUtils.SaveToFile(query, "Main.sheet");
table.Load("Main.sheet");
return(table);
}
private void CreateResultsPivot(ExcelWorksheet ws, ExcelTable table)
{
var range = table.WorkSheet.Cells[table.Address.Address];
var pivotTable = ws.PivotTables.Add(ws.Cells["A1"], range, "ResultPivotTable");
pivotTable.RowFields.Add(pivotTable.Fields["RuleName"]);
pivotTable.RowFields.Add(pivotTable.Fields["Scope"]);
pivotTable.RowFields.Add(pivotTable.Fields["Parent"]);
pivotTable.RowFields.Add(pivotTable.Fields["Page"]);
pivotTable.RowFields.Add(pivotTable.Fields["Message"]);
var dataField = pivotTable.DataFields.Add(pivotTable.Fields["Message"]);
dataField.Name = "Count";
dataField.Function = DataFieldFunctions.Count;
}
// These functions are for manipulating Excel Tables and Columns.
#region ExcelHelpers
/// <summary>
/// Helper Function that creates conditionally formatted columns
/// </summary>
/// <param name="tbl1"></param>
/// <param name="ws1"></param>
/// <param name="tableCounts1"></param>
/// <param name="low2"></param>
/// <param name="high2"></param>
/// <param name="colorLow"></param>
/// <param name="colorHigh"></param>
/// <param name="icon5levels"></param>
/// <param name="column"></param>
/// <param name="comment"></param>
/// <param name="newName"></param>
public void conditionalColumn(ref ExcelTable tbl1, ref ExcelWorksheet ws1, int tableCounts1, int low2, int high2, Color colorLow, Color colorHigh, int[] icon5levels, string column, string comment, string newName = "")
{
Cond2ColorFormat(column, tableCounts1, ref tbl1, ref ws1, low2, colorLow, high2, colorHigh);
CommentColumn(column, comment, ref tbl1, ref ws1);
if (icon5levels.Length == 5)
{
Cond5IconFormat(column, tableCounts1, ref tbl1, ref ws1, icon5levels[0], icon5levels[1], icon5levels[2], icon5levels[3], icon5levels[4]);
}
;
if (newName != "")
{
RenameColumn(column, newName, ref tbl1);
}
;
}
private void InsertTableData(ExcelTable table, DataTable data)
{
// Insert new table data
var start = table.Address.Start;
var body = table.WorkSheet.Cells[start.Row + 1, start.Column];
var outRange = body.LoadFromDataTable(data, false);
// Refresh the table ranges so that Excel understands the current size of the table
var newRange = string.Format("{0}:{1}", start.Address, outRange.End.Address);
var tableElement = table.TableXml.DocumentElement;
tableElement.Attributes["ref"].Value = newRange;
tableElement["autoFilter"].Attributes["ref"].Value = newRange;
}
public static object GetBookPositions(
[ExcelArgument(AllowReference = true, Name = "Book")] string bookId = "",
[ExcelArgument(AllowReference = true, Name = "Position Date")] string businessDate = "")
{
var caller = AddinContext.Excel.Call(XlCall.xlfCaller);
ExcelFunc getData = () =>
{
if (!IsLoggedIn)
{
throw new ApplicationException($"Please login from the Argomi tab.");
}
if (AddinContext.AssumedAmid == 0)
{
throw new ApplicationException($"User {AddinContext.Username} does not have a valid asset manager relationship");
}
var bookIds = bookId.MatchAll() ? null : new List <string> {
bookId
};
var positionDate = !businessDate.MatchAll() &&
DateTime.TryParse(businessDate, CultureInfo.CurrentCulture, DateTimeStyles.AssumeLocal, out DateTime businessDateParsed)
? (DateTime?)businessDateParsed
: null;
var api = AddinContext.Container.Resolve <ITransactionsInterface>();
var positions = api.SearchPositions(
assetManagerId: AddinContext.AssumedAmid,
bookIds: bookIds,
positionDate: positionDate,
pageNo: 1,
pageSize: QueryConstants.DefaultPageSize).Result;
var assetsApi = AddinContext.Container.Resolve <IAssetsInterface>();
var assets = assetsApi.SearchAssets(
assetManagerId: AddinContext.AssumedAmid,
assetIds: positions.Select(p => p.AssetId).ToList(),
pageNo: 1,
pageSize: QueryConstants.DefaultPageSize).Result;
var models = positions.Select(p =>
new EnrichedModel <Position, Asset>(p, assets.FirstOrDefault(a => a.AssetId == p.AssetId)));
return(ExcelTable.Format(models, AddinContext.Container.Resolve <IFormatter <EnrichedModel <Position, Asset> > >(), caller));
};
var output = AddinContext.Excel.Run(
UdfNames.BookPositionSearch,
string.Join(",", bookId, businessDate),
getData);
return(output?.Equals(ExcelError.ExcelErrorNA) ?? true ? ExcelError.ExcelErrorGettingData : output);
}
public static IEnumerable <T> ConvertTableToObject <T>(this ExcelTable table) where T : new()
{
var tprops = (new T())
.GetType()
.GetProperties()
.ToList();
var start = table.Address.Start;
var end = table.Address.End;
var cells = new List <ExcelRangeBase>();
for (var r = start.Row; r < end.Row; r++)
{
for (var c = start.Column; c < end.Column; c++)
{
cells.Add(table.WorkSheet.Cells[r, c]);
}
}
var groups = cells
.GroupBy(cell => cell.Start.Row)
.ToList();
var colnames = groups
.First()
.Select((hcell, idx) => new { Name = hcell.Value.ToString(), index = idx })
.Where(o => tprops.Select(p => p.Name).Contains(o.Name))
.ToList();
var rowvalues = groups
.Skip(1) //Exclude header
.Select(cg => cg.Select(c => c.Value).ToList());
var collection = rowvalues.Select(row =>
{
var tNew = new T();
colnames.ForEach(colname =>
{
var val = row[colname.index];
var prop = tprops.First(p => p.Name == colname.Name);
prop.SetValue(tNew, val);
});
return(tNew);
});
return(collection);
}
public static void AddReport(ExcelPackage package, DataTable table)
{
const int EXCEL_MAX_ROWS = 1048575;
int nbTables = ((table.Rows.Count - 1) / EXCEL_MAX_ROWS) + 1;
for (int tableIndex = 0; tableIndex < nbTables; tableIndex++)
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(table.TableName + (tableIndex == 0 ? "" : " (" + tableIndex + ")"));
for (int indexColumn = 0; indexColumn < table.Columns.Count; indexColumn++)
{
DataColumn column = table.Columns[indexColumn];
ExcelColumn excelColumn = worksheet.Column(indexColumn + 1);
worksheet.Cells[1, indexColumn + 1].Value = column.ColumnName;
double?width = column.ExtendedProperties["Width"] as double?;
string format = column.ExtendedProperties["Format"] as string;
if (width.HasValue)
{
excelColumn.Width = width.Value;
}
if (format != null)
{
excelColumn.Style.Numberformat.Format = format;
}
}
int minRow = tableIndex * EXCEL_MAX_ROWS;
int countRows = Math.Min(table.Rows.Count - minRow, EXCEL_MAX_ROWS);
for (int indexRow = 0; indexRow < countRows; indexRow++)
{
DataRow row = table.Rows[minRow + indexRow];
for (int indexColumn = 0; indexColumn < table.Columns.Count; indexColumn++)
{
DataColumn column = table.Columns[indexColumn];
worksheet.Cells[indexRow + 2, indexColumn + 1].Value = row[column.ColumnName];
}
}
if (table.Columns.Count > 0 && table.Rows.Count > 0)
{
ExcelTable excelTable = worksheet.Tables.Add(worksheet.Cells[1, 1, countRows + 1, table.Columns.Count], table.TableName + "_Table_" + tableIndex);
excelTable.TableStyle = TableStyles.Medium2;
}
}
}
public static void DrawCell(ExcelTable table, int row, int column)
{
ExcelTableCell cell = table.GetCell(row, column);
if (cell != null)
{
switch (cell.Type)
{
case ExcelTableCellType.TextField:
{
string s = EditorGUILayout.TextField(cell.Value.ToString(), GUILayout.MaxWidth(cell.width));
cell.Value = s;
break;
}
case ExcelTableCellType.Label:
{
EditorGUILayout.LabelField(cell.Value.ToString(), GUILayout.MaxWidth(cell.width));
break;
}
case ExcelTableCellType.Popup:
{
int selectIndex = cell.ValueSelected.IndexOf(cell.Value);
if (selectIndex < 0)
{
selectIndex = 0;
}
selectIndex = EditorGUILayout.Popup(selectIndex, cell.ValueSelected.ToArray(), GUILayout.MaxWidth(cell.width));
cell.Value = cell.ValueSelected[selectIndex];
break;
}
default:
{
break;
}
}
}
else
{
string s = EditorGUILayout.TextField(table.GetValue(row, column).ToString());
table.SetValue(row, column, s);
}
}
public void AddTable(string name)
{
ExcelTable table = new ExcelTable();
table.TableName = name;
Tables.Add(table);
}
public ExcelRow(ExcelTable table)
{
_list = new SortedList();
_row = new ExcelObject("Row");
_table = table;
if (_row != null)
{
_row.IsNewLine = true;
};
}
public ExcelRow(int index, ExcelTable table)
{
_list = new SortedList();
_row = new ExcelObject("Row");
_table = table;
if ((table != null) && (_row != null))
{
_row.IsNewLine = true;
ExcelObject _table_object = _table.GetTableObject();
if (_table_object != null)
_table_object.AddObject(index, ref _row);
};
}
public ExcelTable CreateWorkSheet(string name, ref ExcelColumns columns)
{
ExcelTable result = null;
if (_root != null)
{
ExcelObject _worksheet = new ExcelObject("Worksheet");
if (_worksheet != null)
{
_worksheet.AddProperty("ss", "Name", name);
ExcelTable _table = new ExcelTable(ref columns);
if (_table != null)
{
ExcelObject _table_object = _table.GetTableObject();
if (_table_object != null)
{
_worksheet.AddObject(0, ref _table_object);
_table_object.IsNewLine = true;
};
result = _table;
};
//***_root.AddObject(0, ref _worksheet);
};
};
return result;
}
public ExcelRow AddRow(int index, ref ExcelTable excel_table)
{
ExcelRow result = null;
if (excel_table != null)
{
result = excel_table.AddRow(index + 1);
};
return result;
}
public ExcelRow AddHeader(ref ExcelTable excel_table, ref ExcelColumns exist_columns)
{
ExcelRow result = null;
if ((excel_table != null) && (exist_columns != null))
{
int column_count = exist_columns.Count();
if (column_count > 0)
{
result = AddRow(0, ref excel_table);
if (result != null)
{
string column_name = string.Empty;
string column_alias = string.Empty;
for (int index = 0; index < column_count; index++)
{
ExcelObject _object = exist_columns.GetColumnObject(index);
if (_object != null)
{
column_name = _object.GetPropertyValue("Name");
column_alias = _object.GetPropertyValue("Alias");
};
if (column_name.Length > 0)
result.SetHeaderValue(column_name, column_alias);
};
};
};
};
return result;
}
internal ExcelTableColumn(XmlNamespaceManager ns, XmlNode topNode, ExcelTable tbl, int pos) :
base(ns, topNode)
{
_tbl = tbl;
Position = pos;
}
public static void DrawTable(ExcelTable table)
{
if (table != null)
{
table.Position = EditorGUILayout.BeginScrollView(table.Position);
for (int i = 1; i <= table.NumberOfRows; i++)
{
bool continued = false;
if (continued)
{
continue;
}
EditorGUILayout.BeginHorizontal();
for (int j = 1; j <= table.NumberOfColumns; j++)
{
DrawCell(table, i, j);
}
EditorGUILayout.EndHorizontal();
}
EditorGUILayout.EndScrollView();
}
}
