c# - 如何将excel数据从openxml worksheetpart.worksheet c#中获取到GetBytes的数组中

Question

我有两个 azure 存储容器 容器 A 容器 B 容器 A 将有一个 excel 文件，我需要动态下载它并对某些业务逻辑进行一些修改。修改完成后，应将 excel 文件上传到容器 B 中。示例：容器 A 中的 Excel 有一个名为“ABC”的列，我必须将其替换为“XYZ”因此应将具有 XYZ 内容的新 excel 保存在容器 B。

任何帮助，将不胜感激。问候，

标签： c#azureopenxml

Answer 1

关于这个问题，请参考以下代码

1. 上传

           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();

            }

           

2. 下载

           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();

            }

原始的excel文件

新的excel文件