/// <summary>
/// Add row from a Function's dynamic input
/// </summary>
/// <param name="client">MS Graph client used to send request</param>
/// <param name="attr">Excel Attribute with necessary data (workbook name, table name) to build request</param>
/// <param name="jsonContent">JObject with the data to be added to the table</param>
/// <returns>WorkbookTableRow that was just added</returns>
internal async Task AddRow(ExcelAttribute attr, JObject jsonContent)
{
/*
* Two options:
* 1. JObject created from POCO representing strongly typed table -- indicated by "Microsoft.O365Bindings.POCO" being set
* 2. JObject "values" set to object[][], so simply post an update to specified table -- indicated by "Microsoft.O365Bindings.values" being set
*/
JToken newRow;
if (jsonContent[O365Constants.POCOKey] != null)
{
string[] headerRow = await _client.GetTableHeaderRowAsync(attr.Path, attr.TableName);
jsonContent.Remove(O365Constants.POCOKey); // Remove now unnecessary flag
newRow = JArray.FromObject(POCOToStringArray(jsonContent, headerRow));
}
else if (jsonContent[O365Constants.ValuesKey] != null)
{
newRow = jsonContent[O365Constants.ValuesKey];
}
else
{
throw new KeyNotFoundException($"When appending a row, the '{O365Constants.ValuesKey}' or '{O365Constants.POCOKey}' key must be set");
}
await _client.PostTableRowAsync(attr.Path, attr.TableName, newRow);
}
/// <summary>
/// Update an existing Excel worksheet
/// Starting at first row, insert the given rows
/// Overwrites existing data
/// </summary>
/// <param name="attr">ExcelAttribute with workbook & worksheet names, starting row & column</param>
/// <param name="jsonContent">Values with which to update worksheet plus metadata</param>
/// <returns>WorkbookRange containing updated worksheet</returns>
internal async Task <WorkbookRange> UpdateWorksheet(ExcelAttribute attr, JObject jsonContent)
{
// Retrieve current range of worksheet
var currentRange = await _client.GetWorksheetWorkbookAsync(attr.Path, attr.WorksheetName);
var rowsToBeChanged = int.Parse(jsonContent[O365Constants.RowsKey].ToString());
var fromTable = !string.IsNullOrEmpty(attr.TableName);
string newRange = FindNewRange(currentRange.Address, rowsToBeChanged, fromTable);
// Retrieve old workbook
WorkbookRange workbook = await _client.GetWorkSheetWorkbookInRangeAsync(attr.Path, attr.WorksheetName, newRange);
JToken newRowArray;
if (jsonContent[O365Constants.POCOKey] != null)
{
string[] header = await _client.GetTableHeaderRowAsync(attr.Path, attr.TableName);
jsonContent.Remove(O365Constants.POCOKey); // Remove now unnecessary flag
var newRows = POCOToStringArray(jsonContent, header);
newRowArray = JArray.FromObject(newRows);
}
else
{
newRowArray = jsonContent[O365Constants.ValuesKey];
}
// Update necessary fields
PopulateWorkbookWithNewValue(workbook, newRowArray);
return(await _client.PatchWorksheetAsync(attr.Path, attr.WorksheetName, newRange, workbook));
}
public void ExcelAttribute_CreateNew()
{
// Arrange
// Act
var attr = new ExcelAttribute("key");
// Assert
Assert.IsNotNull(attr);
}
public void ExcelAttribute_GetValue()
{
// Arrange
var attr = new ExcelAttribute("name", "value");
// Act
var value = attr.Value;
// Assert
Assert.AreEqual("value", value);
}
public void ExcelAttribute_SetValue()
{
// Arrange
var attr = new ExcelAttribute("name", "value");
// Act
attr.Value = "newvalue";
// Assert
Assert.AreEqual("newvalue", attr.Value);
}
public void ExcelAttribute_SetValue_EncodeText()
{
// Arrange
var expected = "<>"\n\r&#;";
var attr = new ExcelAttribute("name", "value");
// Act
attr.Value = "<>\"\n\r&#;";
// Assert
Assert.AreEqual(expected, attr.Value);
}
public void ExcelAttribute_CreateNew_WithParams()
{
// Arrange
var key = "attrKey";
var value = "attribute value";
// Act
var attr = new ExcelAttribute(key, value);
// Assert
Assert.IsNotNull(attr);
Assert.AreEqual(key, attr.Key);
Assert.AreEqual(value, attr.Value);
}
/// <summary>
/// Returns either an Excel table or entire worksheet depending on user settings
/// </summary>
/// <param name="client">GraphServiceClient that makes request</param>
/// <param name="attr">Contains metadata (path, tablename, worksheet name) </param>
/// <returns>string [][] containing table contents</returns>
internal async Task <string[][]> GetExcelRangeAsync(ExcelAttribute attr)
{
WorkbookRange range;
// If TableName is set, then retrieve the contents of a table
if (attr.TableName != null)
{
range = await _client.GetTableWorkbookRangeAsync(attr.Path, attr.TableName);
}
else
{
// If TableName is NOT set, then retrieve either the contents or the formulas of the worksheet
range = await _client.GetWorksheetWorkbookAsync(attr.Path, attr.WorksheetName);
}
return(range.Values.ToObject <string[][]>());
}
public static byte[] EppListToExcel(List <T> data, string seetName = "Seet1")
{
using (ExcelPackage excel = new ExcelPackage())
{
ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(seetName);
PropertyInfo[] props = typeof(T).GetProperties();
for (var i = 0; i < props.Length; ++i)
{
Object obj = props[i].GetCustomAttribute(typeof(ExcelAttribute));
if (obj != null)
{
ExcelAttribute head = (ExcelAttribute)obj;
sheet.Cells[1, i + 1].Value = head.Header;
if (!string.IsNullOrWhiteSpace(head.DateTime))
{
sheet.Cells[1, i + 1, data.Count + 1, i + 1].Style.Numberformat.Format = head.DateTime;
}
}
else
{
sheet.Cells[1, i + 1].Value = props[i].Name;
}
}
for (var i = 0; i < data.Count; ++i)
{
for (var j = 0; j < props.Length; ++j)
{
sheet.Cells[i + 2, j + 1].Value = props[j].GetValue(data[i]);
//WebClient wb = new WebClient();
//byte[] bt = wb.DownloadData("http://8000.bitcoding.top:8888/upload/images/41a38133-559f-43f8-be7d-51e4b11c32fd.png");
//Image photo = null;
//using (MemoryStream ms = new MemoryStream(bt))
//{
// ms.Write(bt, 0, bt.Length);
// photo = Image.FromStream(ms, true);
//}
//sheet.Drawings.AddPicture(System.Guid.NewGuid().ToString(), photo);
}
}
MemoryStream stream = new MemoryStream();
excel.SaveAs(stream);
stream.Seek(0, SeekOrigin.Begin);
stream.Close();
stream.Dispose();
return(stream.ToArray());
}
}
/// <summary>
/// Returns either an Excel table or entire worksheet depending on user settings
/// </summary>
/// <param name="client">GraphServiceClient that makes request</param>
/// <param name="attr">Contains metadata (path, tablename, worksheet name) </param>
/// <returns>string [][] containing table contents</returns>
internal async Task <string[][]> GetExcelRangeAsync(ExcelAttribute attr, CancellationToken token)
{
WorkbookRange range;
IGraphServiceClient client = await _clientProvider.GetMSGraphClientFromTokenAttributeAsync(attr, token);
// If TableName is set, then retrieve the contents of a table
if (attr.TableName != null)
{
range = await client.GetTableWorkbookRangeAsync(attr.Path, attr.TableName, token);
}
else
{
// If TableName is NOT set, then retrieve either the contents or the formulas of the worksheet
range = await client.GetWorksheetWorkbookAsync(attr.Path, attr.WorksheetName, token);
}
return(range.Values.ToObject <string[][]>());
}
/// <summary>
/// Returns either an Excel table or entire worksheet depending on user settings
/// </summary>
/// <param name="client">GraphServiceClient that makes request</param>
/// <param name="attr">Contains metadata (path, tablename, worksheet name) </param>
/// <returns>POCO Array of worksheet or table data</returns>
internal async Task <T[]> GetExcelRangePOCOAsync <T>(ExcelAttribute attr)
{
// If TableName is set, then retrieve the contents of a table
string[][] output = await GetExcelRangeAsync(attr);
string[] header = output[0];
Dictionary <string, int> dict = new Dictionary <string, int>(); // Map string header value to its index
// Initialize dictionary
foreach (var heading in header.Select((value, index) => new { index, value }))
{
dict.Add(heading.value, heading.index);
}
T[] POCOArray = new T[output.GetLength(0) - 1]; // Init POCO Array size to size of output - header
// Create array of POCOs from output array; skip header
foreach (var row in output.Skip(1).Select((value, index) => new { index, value }))
{
var POCORow = Activator.CreateInstance(typeof(T), new object[] { });
var fields = typeof(T).GetProperties(); // Retrieve all of T's fields
foreach (var field in fields)
{
int indexOfFieldValue;
// For each field, find the corresponding index in the output array
try
{
indexOfFieldValue = dict[field.Name];
// Then set POCORow's field to the value at that index
field.SetValue(POCORow, row.value[indexOfFieldValue]);
}
catch (KeyNotFoundException)
{
// If key isn't found in dictionary corresponding to Table's column names, then let user know
throw new KeyNotFoundException($"POCO type [{typeof(T)}] contains field [{field.Name}] that was not found in header of Excel table [{attr.TableName}]");
}
}
POCOArray[row.index] = (T)POCORow;
}
return(POCOArray);
}
private Dictionary <PropertyInfo, ExcelAttribute> GetExcelAttrs <T>()
{
Dictionary <PropertyInfo, ExcelAttribute> dic = new Dictionary <PropertyInfo, ExcelAttribute>();
Type type = typeof(T);
PropertyInfo[] propertyInfo = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
foreach (PropertyInfo item in propertyInfo)
{
object[] objAttrs = item.GetCustomAttributes(typeof(ExcelAttribute), true);
if (objAttrs.Length > 0)
{
ExcelAttribute attr = objAttrs[0] as ExcelAttribute;
if (attr != null)
{
dic.Add(item, attr);
}
}
}
return(dic);
}
/// <summary>
/// Update a specified column with a specified value
/// </summary>
/// <param name="client">GraphServiceClient used to make calls</param>
/// <param name="attr">ExcelAttribute with path, table name, and worksheet name</param>
/// <param name="job">JObject with two keys: 'column' and 'value'</param>
/// <returns>Workbook range containing updated column</returns>
internal async Task <WorkbookRange> UpdateColumnAsync(ExcelAttribute attr, JObject job, CancellationToken token)
{
IGraphServiceClient client = await _clientProvider.GetMSGraphClientFromTokenAttributeAsync(attr, token);
// The table API only allows updating or adding one row at a time.
// Instead we update the worksheet range corresponding to the table
string currentTableRange = (await client.GetTableWorkbookRangeAsync(attr.Path, attr.TableName, token)).Address;
// Retrieve current worksheet rows
WorkbookRange currentTableWorkbook = await client.GetWorkSheetWorkbookInRangeAsync(attr.Path, attr.WorksheetName, currentTableRange, token);
// Update specified column with specified value
IEnumerable <string[]> values = currentTableWorkbook.Values.ToObject <IEnumerable <string[]> >();
var enumer = values.GetEnumerator();
enumer.MoveNext();
// Try converting column key to digit
if (int.TryParse(job["column"].ToString(), out int column))
{
column = int.Parse(job["column"].ToString());
}
else
{
// If it's a column heading, need to find corresponding column index
var headers = enumer.Current;
column = Array.FindIndex(headers, x => x == job["column"].ToString());
}
while (enumer.MoveNext())
{
// Update column of this row to specified value
enumer.Current[column] = job["value"].ToString();
}
var updateValues = JArray.FromObject(values);
PopulateWorkbookWithNewValue(currentTableWorkbook, updateValues);
return(await client.PatchWorksheetAsync(attr.Path, attr.WorksheetName, currentTableRange, currentTableWorkbook, token));
}
protected List <Dictionary <string, object> > CreateAttributeJson(Type type, Dictionary <string, object> join = null)
{
ExcelAttribute excel = type.GetAttributeValue <ExcelAttribute> ();
AttributeMappingConfig <ColumnAttribute>[] configs = Reflection.FieldAMCRetrieve <ColumnAttribute> (type);
if (configs.Length > 0)
{
if (excel == null)
{
return(null);
}
ExcelData excelData = listExcel.Find(x => x.sheetName == excel.SheetName);
if (excelData == null)
{
Debug.LogWarning(type.Name + " sheet name is incorrect.");
return(null);
}
List <Dictionary <string, object> > list = new List <Dictionary <string, object> > ();
List <Dictionary <string, object> > data = excelData.Join(join);
for (int i = 0; i < data.Count; i++)
{
Dictionary <string, object> dic = CreateAttributeJson(data [i], configs);
if (dic.Count > 0)
{
list.Add(dic);
}
}
return(list);
}
else
{
return(null);
}
}
public static Dictionary <PropertyInfo, ExcelAttribute> GetExportAttrDict <T>()
{
var dict = new Dictionary <PropertyInfo, ExcelAttribute>();
foreach (var propertyInfo in typeof(T).GetProperties())
{
var attr = propertyInfo.GetCustomAttributes(true).FirstOrDefault(c => c is ExcelAttribute || c is DisplayAttribute);
if (attr != null)
{
var attr1 = attr;
if (attr is DisplayAttribute)
{
var display = attr as DisplayAttribute;
attr1 = new ExcelAttribute(display.Name)
{
Order = display.Order
};
}
dict.Add(propertyInfo, attr1 as ExcelAttribute);
}
}
return(dict);
}
/// <summary>
/// Initializes a new instance of the <see cref="ExcelAsyncCollector"/> class.
/// </summary>
/// <param name="client">GraphServiceClient used to make calls to MS Graph</param>
/// <param name="attribute">ExcelAttribute containing necessary info about workbook, etc.</param>
public ExcelAsyncCollector(ExcelService manager, ExcelAttribute attribute)
{
_manager = manager;
_attribute = attribute;
_rows = new List <JObject>();
}
/// <summary>
/// 集合导出Excel
/// </summary>
/// <param name="list">集合</param>
/// <param name="columnNames">列名转换</param>
/// <param name="dicOnly">部分转换</param>
/// <returns></returns>
public static byte[] NpoiListToExcel(List <T> list, string sheetName = "Sheet1")
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
var headRow = sheet.CreateRow(0);
PropertyInfo[] props = typeof(T).GetProperties();
for (var i = 0; i < props.Length; ++i)
{
Object obj = props[i].GetCustomAttribute(typeof(ExcelAttribute));
if (obj != null)
{
ExcelAttribute head = (ExcelAttribute)obj;
headRow.CreateCell(i).SetCellValue(head.Header);
}
else
{
headRow.CreateCell(i).SetCellValue(props[i].Name);
}
}
for (var i = 0; i < list.Count; ++i)
{
var row = sheet.CreateRow(i + 1);
for (var j = 0; j < props.Length; ++j)
{
Object obj = props[j].GetCustomAttribute(typeof(ExcelAttribute));
var key = props[j];
if (obj != null)
{
ExcelAttribute head = (ExcelAttribute)obj;
if (head.Picture)
{
string url = props[j].GetValue(list[i]).ToString().Split(",")[0];
if (!string.IsNullOrWhiteSpace(url))
{
row.Height = 80 * 20;
WebClient temp = new WebClient();
byte[] bytes = temp.DownloadData(url);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 1, j + 1, i + 2);
XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx);
bytes = null;
}
continue;
}
else
{
var drValue = props[j].GetValue(list[i]) == null ? "" : props[j].GetValue(list[i]).ToString();
var type = props[j].PropertyType;
if (head.Obj != null)
{
type = head.Obj.GetType();
}
if (type == typeof(Int32) || type == typeof(Int32?))
{
int intV = 0;
int.TryParse(drValue, out intV);
row.CreateCell(j).SetCellValue(intV);
}
else if (type == typeof(System.Decimal) || type == typeof(System.Decimal?) ||
type == typeof(System.Double) || type == typeof(System.Double?))
{
double doubV = 0;
double.TryParse(drValue, out doubV);
if (string.IsNullOrWhiteSpace(drValue))
{
row.CreateCell(j).SetCellValue("");
}
else
{
row.CreateCell(j).SetCellValue(doubV);
}
}
else if (type == typeof(System.Byte) || type == typeof(System.Byte?))
{
Byte doubV = 0;
Byte.TryParse(drValue, out doubV);
row.CreateCell(j).SetCellValue(doubV);
}
else if (type == typeof(System.Boolean) || type == typeof(System.Boolean?))
{
Boolean doubV = false;
Boolean.TryParse(drValue, out doubV);
row.CreateCell(j).SetCellValue(doubV);
}
else
{
row.CreateCell(j).SetCellValue(drValue);
}
}
}
}
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= props.Length; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length + 1;
}
}
if (columnWidth > 40)
{
columnWidth = 40;
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
workbook.Close();
stream.Close();
stream.Dispose();
return(buf);
}
/// <summary>
/// Create the specified type, ignores and joinType.
/// </summary>
/// <param name="type">Type.</param>
/// <param name="ignores">Ignores.</param>
/// <param name="joinType">Join type.</param>
public void Create(JsonImportType type, string[] ignores = null, TResultDelegate <Type> joinType = null)
{
book = FileStream();
if (book == null)
{
return;
}
if (book.NumberOfSheets <= 0)
{
Debug.LogWarning("No sheet.");
return;
}
index = 0;
listExcel = new List <ExcelData> ();
if (type == JsonImportType.NORMAL)
{
EditorUtil.StartCoroutine(CreateExcelData(delegate() {
if (listExcel.Count <= 0)
{
Debug.LogWarning("No data on the sheet.");
ClearProgressBar();
return;
}
if (ignores != null)
{
listExcel.RemoveAll(x => ignores.Contains(x.sheetName));
}
index = 0;
EditorUtil.StartCoroutine(CreateNormalJson());
}));
}
else
{
this.joinType = joinType;
EditorUtil.StartCoroutine(CreateExcelData(delegate() {
if (listExcel.Count <= 0)
{
Debug.LogWarning("No data on the sheet.");
ClearProgressBar();
return;
}
listType = new List <Type> ();
Type[] types = Reflection.GetExecutingAssembly();
for (int i = 0; i < types.Length; i++)
{
ExcelAttribute excel = types [i].GetAttributeValue <ExcelAttribute> ();
if (excel == null || excel.CreateFileName == "")
{
continue;
}
listType.Add(types [i]);
}
if (listType.Count <= 0)
{
Debug.LogWarning("No class is set to Excel Attribute.");
ClearProgressBar();
return;
}
index = 0;
EditorUtil.StartCoroutine(CreateAttributeJson());
}));
}
}
async Task <T[]> IAsyncConverter <ExcelAttribute, T[]> .ConvertAsync(ExcelAttribute input, CancellationToken cancellationToken)
{
return(await _service.GetExcelRangePOCOAsync <T>(input, cancellationToken));
}
internal Task <WorkbookTable> GetExcelTable(ExcelAttribute attr)
{
return(_client.GetTableWorkbookAsync(attr.Path, attr.TableName));
}
public async Task <ExcelService> GetExcelService(ExcelAttribute attribute)
{
return(new ExcelService(await GetMSGraphClientAsync(attribute)));
}
async Task <T[]> IAsyncConverter <ExcelAttribute, T[]> .ConvertAsync(ExcelAttribute input, CancellationToken cancellationToken)
{
var manager = await _serviceManager.GetExcelService(input);
return(await manager.GetExcelRangePOCOAsync <T>(input));
}
public IAsyncCollector <JObject> CreateCollector(ExcelAttribute attr)
{
var manager = Task.Run(() => _serviceManager.GetExcelService(attr)).GetAwaiter().GetResult();
return(new ExcelAsyncCollector(manager, attr));
}
/// <summary>
/// Returns either an Excel table or entire worksheet depending on user settings
/// </summary>
/// <param name="client">GraphServiceClient that makes request</param>
/// <param name="attr">Contains metadata (path, tablename, worksheet name) </param>
/// <returns>List<POCO> where a single POCO represents the values of one row</returns>
internal async Task <List <T> > GetExcelRangePOCOListAsync <T>(ExcelAttribute attr)
{
return(new List <T>(await GetExcelRangePOCOAsync <T>(attr)));
}
internal async Task <WorkbookTable> GetExcelTableAsync(ExcelAttribute attr, CancellationToken token)
{
IGraphServiceClient client = await _clientProvider.GetMSGraphClientFromTokenAttributeAsync(attr, token);
return(await client.GetTableWorkbookAsync(attr.Path, attr.TableName, token));
}
protected void CreateAttributeJson(int index)
{
Type type = listType [index];
ExcelAttribute excel = type.GetAttributeValue <ExcelAttribute> ();
List <Dictionary <string, object> > list = CreateAttributeJson(type);
if (list == null || list.Count <= 0)
{
return;
}
if (excel.IndexFlag)
{
AttributeMappingConfig <ColumnAttribute>[] configs = Reflection.FieldAMCRetrieve <ColumnAttribute> (type);
List <string> pks = new List <string> ();
List <string> fks = new List <string> ();
foreach (AttributeMappingConfig <ColumnAttribute> config in configs)
{
if (config.t != null)
{
ColumnAttribute column = config.t as ColumnAttribute;
if (column != null)
{
if (column.CheckConstraints(DataConstraints.PK))
{
pks.Add(config.name);
}
if (column.CheckConstraints(DataConstraints.FK))
{
fks.Add(config.name);
}
}
}
}
if (pks.Count > 0)
{
foreach (Dictionary <string, object> dic in list)
{
StringBuilder stringBuilder = Append(pks, dic);
string createFileName = string.Format("{0}{1}", excel.CreateFileName, stringBuilder.ToString());
EditorUtil.CreateJsonFile(createFileName, JsonUtil.ToJson(dic), outputJsonPath, false);
}
}
if (fks.Count > 0)
{
List <string> fk = new List <string> ();
foreach (Dictionary <string, object> dic in list)
{
StringBuilder stringBuilder = Append(fks, dic);
fk.Add(stringBuilder.ToString());
}
fk = Util.GetDistinctValues <string> (fk);
foreach (string s in fk)
{
List <Dictionary <string, object> > fkList = new List <Dictionary <string, object> > ();
foreach (Dictionary <string, object> dic in list)
{
StringBuilder stringBuilder = Append(fks, dic);
if (s == stringBuilder.ToString())
{
fkList.Add(dic);
}
}
string createFileName = string.Format("{0}{1}", excel.CreateFileName, s);
EditorUtil.CreateJsonFile(createFileName, JsonUtil.ToJson(fkList), outputJsonPath, false);
}
}
}
else
{
EditorUtil.CreateJsonFile(excel.CreateFileName, JsonUtil.ToJson(list), outputJsonPath, false);
}
}
