Hi Rick, I'm trying to use the .NET function: Convert.ToBase64String(fileByteArray)

In .NET the orginal code is:

byte[] fileByteArray = System.IO.File.ReadAllBytes("file.xlsx");
string file = Convert.ToBase64String(fileByteArray);
return "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + file;

In foxpro webconnection code I'm trying to mimic the above as follows:

lcFile = FILETOSTR("file.xlsx")  
loBridge = getwwDotnetBridge()
lcNewFile = loBridge.Invokestaticmethod("System","Convert.ToBase64String", lcFile)		&&requires a byte[] inArray ...

...lcNewFile is returned as null.

Thinking I should be converting this foxpro string file to a byte[] array file before making the above call...wondering how best to do this and whether this is why Convert.ToBase64String is returning null?

Any thoughts you have on this would sure be helpful. Thanks,

Greg

PS.

As an alternative and aside, I tried using wwEncryption which worked but was not a suitable encryption for the code receiving the encrypted file:

loEncrypt = CREATEOBJECT("wwEncryption")
lcNewFile = loEncrypt.EncryptString(lcFile)

For the .NET Code you need to cast the to BLOB to turn the file into a SAFEARRAY.

But I don't think you need to use .NET for this - I think you can just use STRCONV() on the lcXls value to get the base64 encoded string.

+++ Rick ---

Hi Rick,

I tried strconv(lcXlsFile, 13) and returned it as the 64bit encrypted file to the browser from a webconnection REST service as follows:

lcEncryptedFile = STRCONV(lcExcelFile,13)
return "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + lcEncryptedFile

Everything seemed to work except when it was received at the browser the receiving program throws an error 'can't find end of central directory'. So I'm thinking maybe the STRCONV() function may be causing this? (details of error: "Uncaught Error: Can't find end of central directory : is this a zip file ?" at d.readEndOfCentral (jszip.min.js:12)

So I tried using the .NET version for encrypting the file instead:

lcFile = CAST(lcFile as blob)
lcNewFile = loBridge.Invokestaticmethod("System","Convert.ToBase64String",lcFile)

The Microsoft docs for the function indicate a call as follows:

public static string ToBase64String (byte[] inArray, int offset, int length, Base64FormattingOptions options);

So I tried using all the parameters instead, still with no success:

lcNewFile = loBridge.Invokestaticmethod("System","Convert.ToBase64String",lcFile, 0, LEN(lcFile))

...which returns a null file. Any ideas on what I should try next? Thanks

Greg

Not sure but you probably have something else going on with the file.

Running the following code on a CSV file I get the same result for all 4 ways of retrieving and converting the file:

CLEAR
DO wwdotnetbridge
LOCAL loBridge as wwDotNetBridge
loBridge = GetwwDotnetBridge()

lcFilename = "c:\temp\test.csv"

? "*** Plain String Convert"
lcCsv = FILETOSTR(lcFilename)
lcB64 = STRCONV(lcCsv,13)
? lcB64

? "*** String Convert on a Blob"
lcB64 = STRCONV(CAST(lcCsv as BLOB),13) 
? lcB64  && same


? "*** ToBase64String - from String"
lcB64 = loBridge.InvokeStaticMethod("System.Convert","ToBase64String", CAST(lcCsv as BLOB))
? lcB64

? "*** ToBase64String - from binary directly read from file"
lvFileData = loBridge.InvokeStaticMethod("System.IO.File","ReadAllBytes",lcFilename)
? VARTYPE(lvFileData)   && Q  (blob)
lcB64 = loBridge.InvokeStaticMethod("System.Convert","ToBase64String", lvFileData)
? lcB64

Also tried this with some binary files and got the same results.

+++ Rick ---

Like you say it could be on the client side... Is it possible that it would have something to do with the ResponseType setting? I tried the following but still had no luck:

response.ContentType  ="data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64"
return "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + lcEncryptedFile 

Thanks a lot Rick.

What are you trying to do exactly? Serve this to a client?

The content type you have is not valid and you need to serve the raw data not base64 content. It should be:

Response.ContentType = "vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.Write(lvRawFileData)

Or better yet:

Response.DownloadFile(lcFilePath,"vnd.openxmlformats-officedocument.spreadsheetml.sheet","MyFile.xslx")

This would download and open the file in configured viewer (Excel most likely).

And if you're serving this from a Web Connection application you should be serving the raw data, not Base64 encoded data.

+++ Rick ---

Hi Rick,

I am trying to serve it to a browser with the the Telerik spreadsheet control. They generally do this with .net but there was an example using the code I posted. The problem appears to be with some internal code they use that relies on jszip.js that had some errors and now is deprecated for what I can tell.

I got the syncfusion spreadsheet working before using the call method approach you're suggesting. But the Telerik spreadsheet seems to require a different way of loading and to be encrypted/decrypted.

With regards to using excel online, I looked at using that but found that getting azure authorization setup was not trivial for me. Looked like the easiest way to get an authentication key was to setup singlepage javascript page. Ideally I was trying to control onedrive with http calls as well as direct the browser to and refresh online excel with http calls. Have you got any suggestions on using webconnection in the azure environment for controlling onedrive and excel viewing?

My immediate goal is to use the online spreadsheet and a 2-way browser control that's served up by webconnection...thus the jquery base for the spreadsheet. In the meantime, I think I will try to by-pass the encrypt/decrypt process like you suggest and see if that solves the problem.

Greg

But what are you calling this FoxPro code from?

+++ Rick ---

From an ajax method that's in the webpage that gets called when I reload the page for now. If I take out the encryption part of this and just return the cast() excel file which is received in the AJAX data parameter, I get back errors:

Method 1: Foxpro code used in the restservice (without data encryption): browser error: FileReader': parameter 1 is not of type 'Blob'

lcExcelFile  = CAST(lcExcelFile as blob)
response.ContentType  ="data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64"
 		return "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + lcExcelFile

Method 2: Foxpro code used in the restservice (without data encryption): foxpro error: Ole idispatch error from Newtonsoft.json

lcExcelFile = CAST(lcExcelFile as blob)
RETURN lcExcelFile

Method 3: foxpro error: operator/operand type mismatch

Response.ContentType = "vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.Write(lcExcelFile)
RETURN 

browser code:

$.ajax({
    url: "https://example.com/restservice/KendoExcelFileSourceEndPoint.sim?ID=sample.xlsx",
    method: "get",
    success:function(data){
var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
       //spreadsheet.fromFile(b64toBlob(data));
         spreadsheet.fromFile(data);  

    }
})

//following function is no longer called without data encryption
function b64toBlob(dataURI) {
		
		var byteString = atob(dataURI.split(',')[1]);		
		var ab = new ArrayBuffer(byteString.length);
		var ia = new Uint8Array(ab);
		for (var i = 0; i < dataURI.length; i++) {
			ia[i] = dataURI.charCodeAt(i);
		}
		return new Blob([ab], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
	}

Thinking about it more...it looks like the xlsx file needs to get sent to the webpage and in javascript convert the xlsx file to a blob to avoid the blob error and avoid the 64bit enryption approach which is done as follows.

sent from server as:

lcExcelFile = FILETOSTR(Config.cHtmlPagePath +  "\temp\" + lcExcelFile)
Response.ContentType = "vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Return lcExcelFile

	function xlsxtoBlob(dataURI) {
		//var byteString = atob(dataURI.split(',')[1]);		//original -- no longer needed as is not encrypted
		var ab = new ArrayBuffer(dataURI.length);
		var ia = new Uint8Array(ab);
		for (var i = 0; i < dataURI.length; i++) {
			ia[i] = dataURI.charCodeAt(i);
		}
		return new Blob([ab], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
	}

Which now gives slightly different error in the jszip.js file similar to the 64bitencryption error but with more info:

Uncaught Error: Corrupted zip: missing 596 bytes.
    at d.readEndOfCentral (jszip.min.js:12)
    at d.load (jszip.min.js:12)
    at new d (jszip.min.js:12)
    at d.b.exports [as load] (jszip.min.js:12)
    at new d (jszip.min.js:12)
    at FileReader.o.onload (kendo.all.js:144098)

Wondering if there is something fundamentally wrong with this xlsxtoBlob() function as the problem occurs both using raw xlsx file and encrypted xlsx file? Checked out Stackoverflow and solutions take exact same form. Or if it's the jszip.js file?

Weird thing is that a simple file upload works fine using the spreadsheet.fromFile() function in the following code:

<input id="file" type="file" />
<div id="spreadsheet"></div>
<script>
    $("#spreadsheet").kendoSpreadsheet();

    $("#file").on("change", function() {
        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.fromFile(this.files[0]);
    });
</script>

Take the application out of the loop and just put the XSLX file into your Web site as a plain file link and load that from your loading code to make sure that the file is getting served correctly. If it still fails you know the problem is on the client end. I think this will give you the same result if you served it using DownloadFile or Response.Write(lcFileText) but this will make sure.

I'm not sure what you're doing in your code. It looks to me you're confusing local file management with files loaded from a server. dataUri and the files collection on the client are all set locally. DataUri is meant for embedded data in a page (ie. <img src="data:image/png,base64Text" /> style resource links) and the files collection holds files that you pick from your local file system.

If you need to capture server side data you first need to download it and then move the data into one of those containers, or you can embed the data right into the page as a data: link that you can read.

+++ Rick ---

Hi Rick,

Thanks for your suggestions - they helped. Ended up using the encrypted data approach and pretty much the same client code Telerik provided and eventually tweaked it enough that it worked via the rest service.

Greg