上传下载Azure Blob里的Excel文件。

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

沉稳的手链 · 心理与认知科学学院· 1 月前 ·

迷茫的煎鸡蛋 · HTML DOM Textarea 对象 ...· 1 年前 ·

烦恼的上铺 · kafka知识总结 - 简书· 1 年前 ·

酒量小的手电筒 · python+selenium ...· 1 年前 ·

鼻子大的日记本 · Android进阶之路 - ...· 1 年前 ·

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
namespace WebApplication1.Controllers
    public class FileController : Controller
        private static string StorageConnectionstring { get; set; }
        private static string ContainerName { get; set; }
        public FileController(IOptions<AppSettings> setting)
            StorageConnectionstring = setting.Value.StorageConnectionstring;//Azure Blob的链接字符串
            ContainerName = setting.Value.ContainerName;//Azure Blob的容器名
public IActionResult ExportExcel()
            return View();
        [HttpPost]
        public async Task<IActionResult> DownExcel()
            //要下载文件的路径
            string AzureFilePath = "https://accazchannel.blob.core.chinacloudapi.cn/test/20200901172136443.xlsx";
            string fileName = "Test" + DateTime.Now.ToFileTime().ToString() + ".xlsx";
            string filePath = AzureFilePath.Replace("https://accazchannel.blob.core.chinacloudapi.cn/test/", "");
            MemoryStream stream = (MemoryStream)AzureHelp.FileDownloadAsync(filePath, StorageConnectionstring, ContainerName).Result;
            return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
 3.  在Azure Blob中下载Excel文件直接保存到DataTable




    

     添加引用 ExcelDataReader.dll
                    ExcelDataReader.DataSet.dll
                    Microsoft.WindowsAzure.Storage.dll
                    System.Text.Encoding.CodePages.dll
.NET Core 项目在默认情况下是没有注册EncodeProvider，需要我们们手动自己去注册。
在NuGet包添加System.Text.Encoding.CodePages
     前台代码：
    Layout = null;
<!DOCTYPE html>
    <meta name="viewport" content="width=device-width" />
    <title>ExportExcelToDataTable</title>
</head>
    <input type="button" onclick="ExportExcel()" value="下载Excel保存到DataTable" />
    <table id="table" style=";">
        <thead><tr style="border: 1px solid grey;"><td>A</td><td>B</td><td>C</td><td>D</td></tr></thead>
        <tbody id="tableBody"></tbody>
    </table>
    <script src="~/lib/jquery/dist/jquery.min.js"></script>
    <script type="text/javascript">
        function ExportExcel() {
            $.ajax({
                type: "Get",
                async: false,//同步请求
                url: "File/DownExcelToDataTable", //获取数据的ajax请求地址
                success: function (data) {
                    if (data.result == "T") {
                        alert("Excel读取成功");
                        $("#table").css({ "display": "block" });
                        $.each(data.rows, function (i, item) {
                            var tr = "<tr style='border: 1px solid grey;'>";
                            tr += "<td>" + item.cloumn1 + "</td>";
                            tr += "<td>" + item.cloumn2 + "</td>";
                            tr += "<td>" + item.cloumn3 + "</td>";
                            tr += "<td>" + item.cloumn4 + "</td>";
                            tr += "</tr>";
                            $("#tableBody").append(tr);
                    } else {
                        alert("失败。");
    </script>
</body>
</html>

后台代码：

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Options;
namespace WebApplication1.Controllers
    public class FileController : Controller
        private static string StorageConnectionstring { get; set; }
        private static string ContainerName { get; set; }
        public FileController(IOptions<AppSettings> setting)
            StorageConnectionstring = setting.Value.StorageConnectionstring;//Azure Blob的连接字符串
            ContainerName = setting.Value.ContainerName;//Azure Blob的容器名
public IActionResult ExportExcelToDataTable()
            return View();
        public IActionResult DownExcelToDataTable()
                //要下载文件的路径
                string AzureFilePath = "https://accazchannel.blob.core.chinacloudapi.cn/test/20200901172136443.xlsx";
                string filePath = AzureFilePath.Replace("https://accazchannel.blob.core.chinacloudapi.cn/test/", "");
                DataTable dt = AzureHelp.GetExcelBlobData(filePath, StorageConnectionstring, ContainerName);
                List<ExcelColumn> lexcel = new List<ExcelColumn>();
                if (dt != null && dt.Rows.Count > 0)
                    for (int i = 1; i < dt.Rows.Count; i++)
                        ExcelColumn excel = new ExcelColumn();
                        excel.Cloumn1 = dt.Rows[i][0].ToString();
                        excel.Cloumn2 = dt.Rows[i][1].ToString();
                        excel.Cloumn3 = dt.Rows[i][2].ToString();
                        excel.Cloumn4 = dt.Rows[i][3].ToString();
                        lexcel.Add(excel);
                return Json(new
                    result ="T",
                    rows = lexcel
            catch (Exception ex)
                return null;
        public class ExcelColumn
            public string Cloumn1 { set; get; }
            public string Cloumn2 { set; get; }
            public string Cloumn3 { set; get; }
            public string Cloumn4 { set; get; }
using ExcelDataReader;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
namespace WebApplication1
    public class AzureHelp
        /// <summary>
        /// 上传文件到Azure
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="stream">文件流</param>
        /// <returns></returns>
        public static async Task<string> FileUploadAsync(string fileName, Stream stream,string StorageConnectionstring,string containerName)
                CloudBlobContainer cloudBlobContainer = null;
                CloudStorageAccount storageAccount = null;
            if (CloudStorageAccount.TryParse(StorageConnectionstring, out storageAccount))
                CloudBlobClient cloudBlobClient = storageAccount.CreateCloudBlobClient();
                cloudBlobContainer = cloudBlobClient.GetContainerReference(containerName);
                CloudBlockBlob blob = cloudBlobContainer.GetBlockBlobReference(fileName);
                await blob.UploadFromStreamAsync(stream);
                return blob.Uri.AbsoluteUri;
            return string.Empty;
        /// <summary>
        ///  上传文件到Azure
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="bytes"></param>
        /// <returns></returns>
        public static async Task<string> FileUploadAsync(string fileName, byte[] bytes,string StorageConnectionstring, string ContainerName)
            CloudBlobContainer cloudBlobContainer = null;
            CloudStorageAccount storageAccount = null;
            if (CloudStorageAccount.TryParse(StorageConnectionstring, out storageAccount))
                CloudBlobClient cloudBlobClient = storageAccount.CreateCloudBlobClient();
                cloudBlobContainer = cloudBlobClient.GetContainerReference(ContainerName);
                CloudBlockBlob blob = cloudBlobContainer.GetBlockBlobReference(fileName);
                await blob.UploadFromByteArrayAsync(bytes, 0, bytes.Length);
                return blob.Uri.AbsoluteUri;
            return string.Empty;
        /// <summary>
        /// 从Azure Blob中下载Excel
        /// </summary>
        /// <param name="filePath">下载文件名</param>
        /// <param name="StorageConnectionstring">Azure Blob的连接字符串</param>
        /// <param name="containerName">容器名</param>
        /// <returns></returns>
        public static async Task<Stream> FileDownloadAsync(string filename, string StorageConnectionstring, string ContainerName)
            CloudBlobContainer cloudBlobContainer = null;
            CloudStorageAccount storageAccount = null;
            var memoryStream = new MemoryStream();
            if (CloudStorageAccount.TryParse(StorageConnectionstring, out storageAccount))
                CloudBlobClient cloudBlobClient = storageAccount.CreateCloudBlobClient();
                cloudBlobContainer = cloudBlobClient.GetContainerReference(ContainerName);
                CloudBlockBlob blob = cloudBlobContainer.GetBlockBlobReference(filename);
                await blob.DownloadToStreamAsync(memoryStream);
            return memoryStream;
        /// <summary>
        /// 下载Azure Blob中的Excel，保存到DataSet中
        /// </summary>
        /// <param name="filename">文件名</param>
        /// <param name="connectionString">Azure Blob的连接字符串</param>
        /// <param name="containerName">容器名</param>
        /// <returns></returns>
        public static DataTable  GetExcelBlobData(string filename, string connectionString, string containerName)
            CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);
            CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
            CloudBlobContainer container = blobClient.GetContainerReference(containerName);
            CloudBlockBlob blockBlobReference = container.GetBlockBlobReference(filename);
            DataSet ds;
            using (var memoryStream = new MemoryStream())
                blockBlobReference.DownloadToStream(memoryStream);
                var excelReader = ExcelReaderFactory.CreateOpenXmlReader(memoryStream);
                ds = excelReader.AsDataSet();
                excelReader.Close();
            return ds.Tables[0];
View Code
    凡事预则立，不预则废。