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

本文内容如下：

上传Excel文件到Azure Blob
在Azure Blob中下载Excel文件
在Azure Blob中下载Excel文件直接保存到DataTable
AzureHelp.cs附件

1. 上传Excel文件到Azure-Blob

添加引用：Microsoft.WindowsAzure.Storage.dll

前台代码：

@{
    Layout = null;
}
 
<!DOCTYPE html>
 
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <script src="~/lib/jquery/dist/jquery.min.js"></script>
    <form class="form-horizontal" method="post" id="ExcelFile" action="File/UploadFile" enctype="multipart/form-data">
        <label class="margin-top:8px">Excel上传:</label>   
        <input class="form-control" id="excelfile" name="excelfile" type="file">  
        <input id="BtnUpload" class="btn btn-primary" type="submit" value="上传" style="text-align:center" />
        <div id="box-footer" class="box-footer">@ViewBag.Result</div>
    </form>
 
 
    <script type="text/javascript">
        $("#BtnUpload").click(function () {
            App.blockUI();
            $("#ExcelFile").submit();
            return false;
        });
    </script>
</body>
</html>

后台代码:

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; }  //Azure Blob的连接字符串
        private static string ContainerName { get; set; }//Azure Blob的容器名称
        public FileController(IOptions<AppSettings> setting)
        {
            StorageConnectionstring = setting.Value.StorageConnectionstring;
            ContainerName = setting.Value.ContainerName;
        }

        public IActionResult UploadFile()
        {
            return View();
        }

        [HttpPost]
        public async Task<IActionResult> UploadFile(IFormFile excelFile)
        {
            try
            {
                string responMsg = "";
                string oFileName = Path.GetFileName(excelFile.FileName);
                string fileExtension = Path.GetExtension(oFileName).ToLower();//获取文件扩展名
                string AzureFilePath = $"{DateTime.Now.ToString("yyyyMMddHHmmssfff")}" + fileExtension;
                responMsg = await AzureHelp.FileUploadAsync(AzureFilePath, excelFile.OpenReadStream(), StorageConnectionstring, ContainerName);
                if (!string.IsNullOrEmpty(responMsg))
                {
                    throw new Exception("上传成功!");
                }
                else
                {
                    throw new Exception("上传失败!");
                }
            }
            catch (Exception ex)
            {
                ViewBag.Result = ex.Message;
            }
            return View();
        }
    }
}

运行效果

上传名为Test的Excel文件

上传成功

在Azure Blob中能看见上传的Excel文件

2. 在Azure Blob中下载Excel文件

添加引用：Microsoft.WindowsAzure.Storage.dll

前台代码：

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>ExportExcel</title>
</head>
<body>
    <script src="~/lib/jquery/dist/jquery.min.js"></script>
    <input type="button" onclick="ExportExcel()" value="下载" />

    <script type="text/javascript">
        function ExportExcel() {
            var url = "File/DownExcel";
            var form = $("<form>");//定义一个form表单
            form.attr("style", "display:none");
            form.attr("target", "");
            form.attr("method", "post");//请求类型
            form.attr("action", url);//请求地址
            $("body").append(form);//将表单放置在web中
            form.submit();
        }
    </script>
</body>
</html>

后台代码：

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

运行结果：

点击下载便可下载Excel

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>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>ExportExcelToDataTable</title>
</head>
<body>
    <input type="button" onclick="ExportExcel()" value="下载Excel保存到DataTable" />
    <br />
    <table id="table" style="display:none;">
        <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()
        {
            try
            {
                //要下载文件的路径
                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; }
        }

    }
}

运行效果：

4. AzureHelp.cs附件代码

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

posted @ 2020-09-28 16:53 EnjoyToday 阅读(505) 评论(0) 编辑收藏举报

刷新页面返回顶部

流浪的向日葵

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

公告