public static TResult ParseXlsx <TResource, TResult>(this HttpRequestMessage request,
IProvideUrl urlHelper,
Stream xlsx,
Func <TResource, KeyValuePair <string, string>[], Task <HttpResponseMessage> > executePost,
Func <TResource, KeyValuePair <string, string>[], Task <HttpResponseMessage> > executePut,
Func <HttpResponseMessage, TResult> onComplete)
where TResource : IReferenceable
{
return(OpenXmlWorkbook.Read(xlsx,
(workbook) =>
{
return workbook.ReadCustomValues(
(customValues) =>
{
var rowsFromAllSheets = workbook.ReadSheets()
.SelectMany(
sheet =>
{
var rows = sheet
.ReadRows()
.ToArray();
if (!rows.Any())
{
return rows;
}
return rows.Skip(1);
}).ToArray();
return request.ParseXlsxBackground(urlHelper, customValues, rowsFromAllSheets,
executePost, executePut, onComplete);
});
}));
}
public void ReadExcel()
{
// C:\Users\!hpfr\Desktop\BookTest.xlsx
OpenXmlWorkbook book = new OpenXmlWorkbook(@"C:\Users\!hpfr\Desktop\Template.xlsx", SourceContent.Local);
OpenXmlSheet sheet = (OpenXmlSheet)book.GetSheet(0);
sheet.Read();
sheet.StartRow = 2;
Random rnd = new Random();
for (int i = 0; i < 10; i++)
{
sheet.CreateRow(i);
sheet.CreateCell(i, 0, "value");
//for (int column = 0; column < 4; column++)
//{
// sheet.CreateCell(i, column, "value");
//sheet.CreateRow(sheet.StartRow + i, column, new OpenXmlSheet.WriteDataCellCallback(GetCurrentCell), new DataCell()
// {
// Value = string.Format("hai-{0}", rnd.Next()),
// Type = DataCellType.String,
// ColumnIndex = columnIndex
// });
//}
}
byte[] readAllBytes = book.Write();
File.WriteAllBytes(@"C:\Users\!hpfr\Desktop\Output.xlsx", readAllBytes);
//sheet.GetRows();
//DataRow row = sheet.Rows[3];
}
private static TResult ConvertToXlsxStreamAsync <TResource, TResult>(
IDictionary <string, string> properties, IEnumerable <TResource> resources,
Func <byte[], TResult> callback)
{
var guidReferences = resources
.Select(
(obj, index) =>
{
if (typeof(IReferenceable).IsInstanceOfType(obj))
{
var resourceId = (obj as IReferenceable).id;
return(resourceId.PairWithValue($"A{index}"));
}
return(default(KeyValuePair <Guid, string>?));
})
.SelectWhereHasValue()
.ToDictionary();
using (var stream = new MemoryStream())
{
OpenXmlWorkbook.Create(stream,
(workbook) =>
{
#region Custom properties
workbook.WriteCustomProperties(
(writeProp) =>
{
properties.Select(
prop =>
{
writeProp(prop.Key, prop.Value);
return(true);
}).ToArray();
return(true);
});
#endregion
workbook.WriteSheetByRow(
(writeRow) =>
{
var propertyOrder = typeof(TResource).GetProperties();
if (!propertyOrder.Any() && resources.Any())
{
propertyOrder = resources.First().GetType().GetProperties();
}
#region Header
var headers = propertyOrder
.Select(
propInfo => propInfo.GetCustomAttribute <JsonPropertyAttribute, string>(
attr => attr.PropertyName,
() => propInfo.Name))
.ToArray();
writeRow(headers);
#endregion
#region Body
var rows = resources.Select(
(result, index) =>
{
var values = propertyOrder
.Select(
property => property.GetValue(result).CastToXlsSerialization(property, guidReferences))
.ToArray();
writeRow(values);
return(true);
}).ToArray();
#endregion
return(true);
});
return(true);
});
var buffer = stream.ToArray();
return(callback(buffer));
}
}
public static TResult ParseXlsx <TResult>(this HttpRequestMessage request,
Stream xlsx, Type[] sheetTypes,
Func <KeyValuePair <string, string>[], KeyValuePair <string, IReferenceable[]>[], TResult> execute)
{
var result = OpenXmlWorkbook.Read(xlsx,
(workbook) =>
{
var propertyOrders = sheetTypes.Select(type => type.PairWithValue(type.GetProperties().Reverse().ToArray())).ToArray();
var x = workbook.ReadCustomValues(
(customValues) =>
{
var resourceList = workbook.ReadSheets()
.SelectReduce(
(sheet, next, skip) =>
{
var rows = sheet
.ReadRows()
.ToArray();
if (!rows.Any())
{
return(skip());
}
var resources = rows
.Skip(1)
.Select(
row =>
{
var propertyOrderOptions = propertyOrders
.Where(
kvp =>
{
var ids = kvp.Value
.Select((po, index) => index.PairWithValue(po))
.Where(poKvp => poKvp.Value.Name == "Id");
if (!ids.Any())
{
return(false);
}
var id = ids.First();
if (row.Length <= id.Key)
{
return(false);
}
if (!Uri.TryCreate(row[id.Key], UriKind.RelativeOrAbsolute, out Uri urn))
{
return(false);
}
if (!urn.TryParseUrnNamespaceString(out string[] nss, out string nid))
{
var sections = row[id.Key].Split(new char[] { '/' });
if (!sections.Any())
{
return(false);
}
nid = sections[0];
}
return(nid == kvp.Key.Name);
})
.ToArray();
var propertyOrder = propertyOrderOptions.Any() ?
propertyOrderOptions.First()
:
typeof(IReferenceable).PairWithValue(typeof(IReferenceable).GetProperties().Reverse().ToArray());
var resource = (IReferenceable)propertyOrder.Value
.Aggregate(Activator.CreateInstance(propertyOrder.Key),
(aggr, property, index) =>
{
var value = row.Length > index ?
row[index] : default(string);
TryCastFromXlsSerialization(property, value,
(valueCasted) =>
{
property.SetValue(aggr, valueCasted);
return(true);
},
() => false);
return(aggr);
});
return(resource);
})
.ToArray();
return(next(sheet.Name.PairWithValue(resources)));
},
private static TResult ConvertToMultisheetXlsxStreamAsync <TResource, TResult>(
IDictionary <string, string> properties, IEnumerable <TResource> resources,
Func <byte[], TResult> callback)
where TResource : IReferenceable
{
var resourceGroups = resources
.GroupBy(
(res) =>
{
var resourceId = res.id;
return(typeof(TResource).Name);
});
var guidReferences = resourceGroups
.SelectMany(
grp =>
grp
.Select(
(res, index) =>
{
var resourceId = res.id;
return(resourceId.PairWithValue($"{grp.Key}!A{index + 2}"));
}))
.ToDictionary();
using (var stream = new MemoryStream())
{
bool wroteRows = OpenXmlWorkbook.Create(stream,
(workbook) =>
{
#region Custom properties
workbook.WriteCustomProperties(
(writeProp) =>
{
properties.Select(
prop =>
{
writeProp(prop.Key, prop.Value);
return(true);
}).ToArray();
return(true);
});
#endregion
foreach (var resourceGrp in resourceGroups)
{
bool wroteRow = workbook.WriteSheetByRow(
(writeRow) =>
{
var resourcesForSheet = resourceGrp.ToArray();
if (!resourcesForSheet.Any())
{
return(false);
}
var resource = resourcesForSheet.First();
if (resource.IsDefault())
{
return(false);
}
var propertyOrder = resource.GetType().GetProperties().Reverse().ToArray();
#region Header
var headers = propertyOrder
.Select(
propInfo => propInfo.GetCustomAttribute <JsonPropertyAttribute, string>(
attr => attr.PropertyName,
() => propInfo.Name))
.ToArray();
writeRow(headers);
#endregion
#region Body
var rows = resourcesForSheet.Select(
(result, index) =>
{
var values = propertyOrder
.Select(
property => property.GetValue(result).CastToXlsSerialization(property, guidReferences))
.ToArray();
writeRow(values);
return(true);
}).ToArray();
#endregion
return(true);
},
resourceGrp.Key);
}
return(true);
});
stream.Flush();
var buffer = stream.ToArray();
return(callback(buffer));
}
}