How to get excel data into an array to GetBytes from openxml worksheetpart.worksheet c#

Question

I have two azure storage container Container A Container B Container A will have an excel file which I will need to download it on the fly & do some modifications for some business logic. Once modification is done ,the excel file should be uploaded in Container B. Example: Excel in Container A have a column named "ABC" which I will have to replace it with "XYZ" So a new excel with XYZ content should be saved in Container B.

Any help would be appreciated. Regards,

I would advice to include some code you have tried else your question will be flagged and removed. — acarlstein, Aug 04 '20 at 18:22
below is the code where I am trying to convert openxml excel data into bytes byte[] newdata = Encoding.UTF8.GetBytes(worksheetPart.Worksheet.ToString()); — MohammedAmir sayed, Aug 04 '20 at 18:33
below is the code where I am trying to convert openxml excel data into bytes byte[] newdata = Encoding.UTF8.GetBytes(worksheetPart.Worksheet.ToString()); this newdata byte is passed in another method which is used to upload file in container B. using (Stream stream = new MemoryStream(blobContent)) { await cloudBlockBlob.UploadFromStreamAsync(stream); } upload is working fine, but when I down excel in local & try to open it says the file is corrupt. — MohammedAmir sayed, Aug 04 '20 at 18:40
MemoryStream m = new MemoryStream(); worksheetPart.Worksheet.Save(m); byte[] newdata = m.ToArray(); above issue has been accomplish using memory stream, the file is successfully getting uploaded but when I down load the same file from blob storage & open, it gives error as "Excel cannot open the file, the file format or extension is invalid, verify the file is not corrupted & file extension matches the format of the file" — MohammedAmir sayed, Aug 05 '20 at 08:13

score 0 · Answer 1 · answered Aug 05 '20 at 08:52

Regarding the issue, please refer to the following code

Upload

           string accountName = "jimtestdiag924";
           string accountKey = "uxz4AtF0*********yDSZ7Q+A==";
           var credential = new StorageSharedKeyCredential(accountName, accountKey);
           string url = string.Format("https://{0}.blob.core.windows.net/", accountName);
           var blobServiceClient =new BlobServiceClient(new Uri(url), credential);
           var containerClient = blobServiceClient.GetBlobContainerClient("testupload");
           var blobClient =containerClient.GetBlobClient("test.xlsx");
           BlobDownloadInfo download = await blobClient.DownloadAsync();
            
           using (var ms = new MemoryStream()) {
                await download.Content.CopyToAsync(ms);
                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(ms, true))
                {
                    // Access the main Workbook part, which contains all references.
                    WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                    // get sheet by name
                    Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();

                    // get worksheetpart by sheet id
                    WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;
                    Cell cell = GetCell(worksheetPart.Worksheet, "B", 4);

                    cell.CellValue = new CellValue("10");
                    cell.DataType = new EnumValue<CellValues>(CellValues.Number);

                    // Save the worksheet.
                    worksheetPart.Worksheet.Save();

                    // for recacluation of formula
                    spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
                    spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
                    


                }
               // upload file to another contanier
                ms.Position = 0;
                containerClient = blobServiceClient.GetBlobContainerClient("test");
                await containerClient.CreateIfNotExistsAsync();
                blobClient = containerClient.GetBlobClient("test.xlsx");
                await blobClient.UploadAsync(ms);
                
               
                download.Content.Close();

            }

Download

           string accountName = "jimtestdiag924";
           string accountKey = "uxz4AtF0*********yDSZ7Q+A==";
           var credential = new StorageSharedKeyCredential(accountName, accountKey);
           string url = string.Format("https://{0}.blob.core.windows.net/", accountName);
           var blobServiceClient =new BlobServiceClient(new Uri(url), credential);
           var containerClient = blobServiceClient.GetBlobContainerClient("test");
            
           var blobClient = containerClient.GetBlobClient("test.xlsx");
           BlobDownloadInfo download = await blobClient.DownloadAsync();
           using (var file = File.Create(@"D:\test.xlsx")) {

                await download.Content.CopyToAsync(file);
                download.Content.Close();

            }

The original excel file

The new excel file

Hi Jim, Thank you for your reply, after implementation I am getting below stated error. Ex = {"The specified package is invalid. The main part is missing."} — MohammedAmir sayed, Aug 05 '20 at 12:19
@MohammedAmirsayedm Could you tell me where you get the error? — Jim Xu, Aug 05 '20 at 13:00
The error I gets in below line. using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(ms, true)) { — MohammedAmir sayed, Aug 05 '20 at 13:19
@MohammedAmirsayed please refer to https://stackoverflow.com/questions/54538982/openxmlsdk-cant-read-manualy-created-xlsx-file-the-specified-package-is-inval — Jim Xu, Aug 05 '20 at 13:24
Actually Jim I am very much new to .net C#, I don't understand if I upload the file from local it is having correct format & able to down load & open file but when I do using above above code while opening uploaded file it says that the file is corrupted. — MohammedAmir sayed, Aug 06 '20 at 10:43
The above code works fine local, but when I run this through Function App, it says the "the access is denied on @D:\" using (var file = File.Create(@"D:\test.xlsx")) { await download.Content.CopyToAsync(file); download.Content.Close(); } — MohammedAmir sayed, Aug 06 '20 at 10:46
@MohammedAmirsayed If you want to create file in azure function, you need to store file in `D:\home`·. For more details, please refer to https://github.com/projectkudu/kudu/wiki/File-structure-on-azure — Jim Xu, Aug 06 '20 at 13:02
@MohammedAmirsayed Do you have any other concerns? if you have no other concerns, could you please [accept it as an answer](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work)? — Jim Xu, Aug 10 '20 at 01:57
Yes Jim, above full code provided by you as worked for me, as I was new in C#, it took me long for me to implement. Thank you so much — MohammedAmir sayed, Aug 10 '20 at 09:50
@MohammedAmirsayedm Since it is useful for you, could you please mark it as a solution: https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work? It may help more people who have the similar issue. — Jim Xu, Aug 10 '20 at 13:02

How to get excel data into an array to GetBytes from openxml worksheetpart.worksheet c#

1 Answers1