上传下载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附件代码
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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]; } } }
凡事预则立,不预则废。