本文内容如下：
- 上传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; }
private static string ContainerName { get; set; }
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;
ContainerName = setting.Value.ContainerName;
}
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
方式二：设置Blob Containers中某个容器的属性
选中Blob Containers中某个容器-------------->鼠标右键，选择设置容器公共访问级别---------------------->选择第二个“容器和Blob的公共读取访问权限”
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;
ContainerName = setting.Value.ContainerName;
}
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];
}
}
}