Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
[D365FO] Excel, EPPlus, Download, Save on Azure Blob
using OfficeOpenXml;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
class GOA_ExcelEPPlus
{
ExcelPackage excelPkg;
System.IO.MemoryStream memoryStream;
BinData bin;
public static GOA_ExcelEPPlus constructFromResource(resourceNode _resourceNode)
{
Binary bin = Binary::constructFromContainer(SysResource::getResourceNodeData(_resourceNode));
System.IO.MemoryStream tmpMemoryStream = bin.getMemoryStream() as System.IO.MemoryStream;
GOA_ExcelEPPlus ret = new GOA_ExcelEPPlus();
ret.parmMemoryStream(new System.IO.MemoryStream());
ret.parmExcelPkg(new ExcelPackage(ret.parmMemoryStream(), tmpMemoryStream));
return ret;
}
public ExcelPackage parmExcelPkg(ExcelPackage _excelPkg = excelPkg)
{
excelPkg = _excelPkg;
return excelPkg;
}
public System.IO.MemoryStream parmMemoryStream(System.IO.MemoryStream _memoryStream = memoryStream)
{
memoryStream = _memoryStream;
return memoryStream;
}
public System.IO.Stream Save()
{
excelPkg.Save();
memoryStream.Seek(0, System.IO.SeekOrigin::Begin);
return memoryStream;
}
public void Download(Filename _Filename)
{
File::SendFileToUser(memoryStream, _Filename);
}
public void SaveToAzureBlob(Filename _Filename, str _storageConnectionString)
{
CloudStorageAccount storageAccount = CloudStorageAccount::Parse(_storageConnectionString);
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
CloudBlobContainer container1 = blobClient.GetContainerReference("d365fofiles");
CloudBlockBlob blockBlob = container1.GetBlockBlobReference(_Filename);
System.Byte[] fileStream = memoryStream.ToArray();
blockBlob.UploadFromByteArray(fileStream, 0, fileStream.length, null, null, null);
}
public ExcelWorksheet getWorksheet(str _Name)
{
ExcelWorkbook excelWorkbook = excelPkg.get_Workbook();
ExcelWorksheets excelWorksheets = excelWorkbook.get_Worksheets();
ExcelWorksheet ret;
ret = excelWorksheets.get_Item(_Name);
if(ret == null)
{
info(strFmt("The worksheet %1 not exists in the current workbook.",_Name));
}
return ret;
}
public void setCellValue(ExcelRange _ExcelRange, int _iCol, int _iRow, Anytype _Value)
{
Str bookmark;
ExcelRange Cell;
System.DateTime sysDate;
bookmark = GOA_ExcelEPPLus::numToNameCell(_iCol,_iRow);
Cell = _ExcelRange.get_Item( bookmark);
switch (typeOf(_Value))
{
case Types::Enum:
Cell.set_Value(enum2str(_Value));
break;
case Types::Date:
if(_Value == dateNull())
{
break;
}
sysDate = System.Convert::ToDateTime(_Value);
Cell.set_Value(sysDate);
break;
case Types::UtcDateTime:
sysDate = System.Convert::ToDateTime(_Value);
Cell.set_Value(sysDate);
break;
default :
Cell.set_Value(_Value);
break;
}
}
public static str numToNameCell(int _iCol, int _iRow)
{
#define.maxCol(256)
#define.A('A')
#define.Z('Z')
int iCharCodeA = char2num(#A, 1) - 1;
int iBase = char2num(#Z, 1) - char2num(#A, 1) + 1;
int iDiv, iMod;
str szRet = "";
;
if (_iCol > #maxCol)
throw error("@GLS105135");
iDiv = _iCol;
iMod = iDiv mod iBase;
iDiv = iDiv div iBase;
if (iDiv - 1 > 0 || (iDiv == 1 && iMod))
{
if (iMod)
{
szRet = num2char(iDiv + iCharCodeA);
}
else
{
szRet = num2char(iDiv - 1 + iCharCodeA);
}
}
if (iMod)
{
szRet += num2char(iMod + iCharCodeA);
}
else
{
szRet += num2char(iBase + iCharCodeA);
}
szRet += int2str(_iRow);
return szRet;
}
}
class goa_tests
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
resourceNode res = SysResource::getResourceNode(resourceStr(GOA_TestExcel));
GOA_ExcelEPPlus excelEPPlus = GOA_ExcelEPPlus::constructFromResource(res);
OfficeOpenXml.ExcelWorksheet excelWorksheet = excelEPPlus.getWorksheet("Sheet1");
OfficeOpenXml.ExcelRange excelRange = excelWorksheet.get_Cells();
excelEPPlus.setCellValue(excelRange, 1, 2, "d");
excelEPPlus.setCellValue(excelRange, 1, 3, "d");
excelEPPlus.setCellValue(excelRange, 1, 4, "a");
excelEPPlus.setCellValue(excelRange, 2, 2, 3);
excelEPPlus.setCellValue(excelRange, 2, 3, 2);
excelEPPlus.setCellValue(excelRange, 2, 4, 5);
excelEPPlus.setCellValue(excelRange, 3, 2, 9);
excelEPPlus.setCellValue(excelRange, 3, 3, 4);
excelEPPlus.setCellValue(excelRange, 3, 4, 2);
excelRange.get_Item('A1').AddComment("qqq","ag");
excelEPPlus.Save();
str storageConnectionString = "storageConnectionString";
excelEPPlus.SaveToAzureBlob("tmp.xlsx", storageConnectionString);
//excelEPPlus.Download("tmp.xlsx");
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.