Recently I was creating a function to resize an image. The function itself was resizing the image perfectly, however, my file size was always at least the same size…
I think I tried about 8 different pieces of DotNet code, always resulting in the same thing. The image was resized, but the file size was at least the same as the original.
If you want to display text that is stored in binary format in a report you would need to use looping and reading the content of the blob piece for piece.
With .net Interop we can do this a lot better. The StreamReader class is ideal to handle this. It is capable to read the text inside the blob, detects the correct charset and gives us back the whole content with one method call.
So here is the working solution for this, it enables you to read compressed & non-compressed BLOBs. I tested it with the Object & the Object Metadata Table.
Variables
ExcelApp Automation ‘Microsoft Excel 15.0 Object Library’.Application
ExcelBook Automation ‘Microsoft Excel 15.0 Object Library’.Workbook
ExcelSheet Automation ‘Microsoft Excel 15.0 Object Library’.Worksheet
ExcelRange Automation ‘Microsoft Excel 15.0 Object Library’.Range
ExcelChart Automation ‘Microsoft Excel 15.0 Object Library’.Chart
GraphFile File
MemStream InStream
OStream OutStream
Customer Record Customer
CREATE(ExcelApp, FALSE, TRUE);
ExcelBook := ExcelApp.Workbooks.Add(-4167);
ExcelSheet := ExcelApp.ActiveSheet;
ExcelSheet.Name := ‘Sales customer’;
Customer.SETFILTER(“Date Filter”,’%1..%2′,010112D,311212D);
Customer.CALCFIELDS(“Sales (LCY)”);
ExcelSheet.Range(‘A1’).Value := ‘2012’;
ExcelSheet.Range(‘A2′).Value := Customer.”Sales (LCY)”;
Customer.SETFILTER(“Date Filter”,’%1..%2’,010113D,311213D);
Customer.CALCFIELDS(“Sales (LCY)”);
ExcelSheet.Range(‘B1’).Value := ‘2013’;
ExcelSheet.Range(‘B2′).Value := Customer.”Sales (LCY)”;
Customer.SETFILTER(“Date Filter”,’%1..%2’,010114D,311214D);
Customer.CALCFIELDS(“Sales (LCY)”);
ExcelSheet.Range(‘C1’).Value := ‘2014’;
ExcelSheet.Range(‘C2’).Value := Customer.”Sales (LCY)”;
ExcelRange := ExcelSheet.Range(‘A1:C2’);
ExcelChart := ExcelBook.Charts.Add;
ExcelChart.Name := ‘Customergraph’;
ExcelChart.ChartWizard(ExcelRange,60,70,1,1,0,0,’Sales (LCY) Customer Pardaan Inc.’);
ExcelChart.Export(TEMPORARYPATH+’graph.png’);
GraphFile.OPEN(TEMPORARYPATH+’graph.png’);
GraphFile.CREATEINSTREAM(MemStream);
CALCFIELDS(Graph);
Graph.CREATEOUTSTREAM(OStream);
COPYSTREAM(OStream,MemStream);
MODIFY;
GraphFile.CLOSE;
Download objects (NAV 2009 R2) : [download id=”133″]
Parameters ChartWizard Method
ChartType enumeration
I hope you will find this article when you will need what I needed last days – to read anything from NAV Blob field from PowerShell. The functions could be used e.g. to read Object Metadata, Profile Metadata etc.
With .NET interoperability it is possible to draw inside a NAV 2013 BLOB field. Consider the following snippet of code. “Picture Blob” is a BLOB field. We create a OutStream so we can write information into the blob. We must calc a BLOB field, even if its empty before getting the OutStream.
As I was reading the list of published platform updates for NAV 2013 I saw that Microsoft was fixing a bug where the content of a BLOB field is not transferred to a new record with the TRANSFERFIELDS function. I must admit that I have never counted on the TRANSFERFIELDS function to move the BLOB data from one table to another. I have always created in- and outstream like this.