c# - 可以打开上传到blob容器中的excel文件吗？

Question

我正在尝试将数据表的内容以 Excel 格式上传到 Blob 容器中。这是我到目前为止的代码

System.IO.MemoryStream stream = new System.IO.MemoryStream();
System.Runtime.Serialization.IFormatter formatter = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
formatter.Serialize(stream, dt); 

var blobContainer = new BlobStorageUtility().GetCloudBlobContainer(StorageConnectionString, SourceContainerName);
var blockBlob = blobContainer.GetBlockBlobReference(ExcelFileName);

blockBlob.Properties.ContentType = "application/vnd.ms-excel";    
blockBlob.UploadFromStream(stream);    
blockBlob.SetProperties(); 

运行代码后，我在 blob 容器中找到了它

下载文件后，我无法打开它。错误如下

错误是什么？

Answer 1

在上传到blob存储之前，您应该将数据表转换为excel(可以将excel存储在内存中)，否则会导致一些错误。

我使用 nuget 包 DocumentFormat.OpenXml 进行转换。

主要方法:

        static void Main(string[] args)
        {
            DataTable dt = new DataTable("table1");
            dt.Columns.Add("name",typeof(string));
            dt.Columns.Add("city",typeof(string));
            dt.Columns.Add("gender",typeof(string));

            dt.Rows.Add("jack", "bj", "male");
            dt.Rows.Add("jacky", "sh", "male");
            dt.Rows.Add("iva", "bj", "female");
            dt.Rows.Add("nancy", "wx", "female");
            dt.Rows.Add("ali", "sz", "male");
            dt.Rows.Add("andy", "sz", "male");

            System.IO.MemoryStream stream = new System.IO.MemoryStream();
            using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
            {
                WriteExcelFile(document, dt);
            }

            CloudStorageAccount storageAccount = new CloudStorageAccount(new StorageCredentials("account name", "account key"), true);
            CloudBlobClient cloudBlobClient = storageAccount.CreateCloudBlobClient();
            var cloudBlobContainer = cloudBlobClient.GetContainerReference("test-1");

            var blockBlob = cloudBlobContainer.GetBlockBlobReference("data115.xlsx");
            stream.Position = 0;
            blockBlob.UploadFromStream(stream);

            Console.WriteLine("completed.");
            Console.ReadLine();
        }

转换方法:

       private static void WriteExcelFile(SpreadsheetDocument document, DataTable table)
        {

                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                worksheetPart.Worksheet = new Worksheet(sheetData);

                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };

                sheets.Append(sheet);

                Row headerRow = new Row();

                List<String> columns = new List<string>();
                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);

                    Cell cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }

                sheetData.AppendChild(headerRow);

                foreach (DataRow dsrow in table.Rows)
                {
                    Row newRow = new Row();
                    foreach (String col in columns)
                    {
                        Cell cell = new Cell();
                        cell.DataType = CellValues.String;
                        cell.CellValue = new CellValue(dsrow[col].ToString());
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }

                workbookPart.Workbook.Save();

        }

经过测试，下载的excel可以正确打开: