C# 读取使用 FileUpload Control 上传的 Excel 文件而不将其保存在服务器上
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12420310/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read an Excel file uploaded using FileUpload Control without saving it on the server
提问by Chandra
Need to be able to read an Excel file uploaded using FileUploadControl in ASP.NET. The solution will be hosted on a server. I do not want to store the Excel file on the server. I would like to directly convert the excel content into a dataset or a datatable and utilize.
需要能够读取在 ASP.NET 中使用 FileUploadControl 上传的 Excel 文件。该解决方案将托管在服务器上。我不想将 Excel 文件存储在服务器上。我想直接将excel内容转换为数据集或数据表并使用。
Below are the two solutions I already found but would not work for me.
以下是我已经找到但对我不起作用的两个解决方案。
LINQTOEXCEL - This method works when you have an excel file on your local machine and you are running your code on the local machine. In my case, the user is trying to upload an excel file from his local machine using a webpage hosted on a server.
ExcelDataReader - I am currently using this one, but this is a third party tool. I cannot move this to our customer. Also if a row/column intersection is carrying a formula, then that row/column intersection's data is not being read into the dataset.
LINQTOEXCEL - 当您在本地机器上有一个 excel 文件并且您在本地机器上运行您的代码时，此方法有效。在我的例子中，用户试图使用服务器上托管的网页从他的本地机器上传一个 excel 文件。
ExcelDataReader - 我目前正在使用这个，但这是第三方工具。我无法将其转移给我们的客户。此外，如果行/列交叉点携带公式，则该行/列交叉点的数据不会被读入数据集。
Most of the suggestions i found on google and StackOverflow work when both the excel and the .NET solution are on the same machine. But in mine, I need it to work when the solution is hosted on a server, and users are trying to upload excel using the hosted webpage on their local machine. If you have any other suggestions, could you please let me know?
当 excel 和 .NET 解决方案都在同一台机器上时，我在 google 和 StackOverflow 上找到的大多数建议都有效。但是在我的情况下，当解决方案托管在服务器上并且用户尝试使用本地计算机上的托管网页上传 excel 时，我需要它才能工作。如果您有任何其他建议，可以告诉我吗？
采纳答案by Tim Schmelter
You can use the InputStreamproperty of the HttpPostedFileto read the file into memory.
您可以使用 的InputStream属性HttpPostedFile将文件读入内存。
Here's an example which shows how to create a DataTablefrom the IO.Streamof a HttpPostedFileusing EPPlus:
这是一个示例，展示了如何DataTable从IO.Streama HttpPostedFileusing创建 a EPPlus：
protected void UploadButton_Click(Object sender, EventArgs e)
{
if (FileUpload1.HasFile && Path.GetExtension(FileUpload1.FileName) == ".xlsx")
{
using (var excel = new ExcelPackage(FileUpload1.PostedFile.InputStream))
{
var tbl = new DataTable();
var ws = excel.Workbook.Worksheets.First();
var hasHeader = true; // adjust accordingly
// add DataColumns to DataTable
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
tbl.Columns.Add(hasHeader ? firstRowCell.Text
: String.Format("Column {0}", firstRowCell.Start.Column));
// add DataRows to DataTable
int startRow = hasHeader ? 2 : 1;
for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
DataRow row = tbl.NewRow();
foreach (var cell in wsRow)
row[cell.Start.Column - 1] = cell.Text;
tbl.Rows.Add(row);
}
var msg = String.Format("DataTable successfully created from excel-file. Colum-count:{0} Row-count:{1}",
tbl.Columns.Count, tbl.Rows.Count);
UploadStatusLabel.Text = msg;
}
}
else
{
UploadStatusLabel.Text = "You did not specify a file to upload.";
}
}
Here's the VB.NET version:
这是VB.NET 版本：
Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
If (FileUpload1.HasFile AndAlso IO.Path.GetExtension(FileUpload1.FileName) = ".xlsx") Then
Using excel = New ExcelPackage(FileUpload1.PostedFile.InputStream)
Dim tbl = New DataTable()
Dim ws = excel.Workbook.Worksheets.First()
Dim hasHeader = True ' change it if required '
' create DataColumns '
For Each firstRowCell In ws.Cells(1, 1, 1, ws.Dimension.End.Column)
tbl.Columns.Add(If(hasHeader,
firstRowCell.Text,
String.Format("Column {0}", firstRowCell.Start.Column)))
Next
' add rows to DataTable '
Dim startRow = If(hasHeader, 2, 1)
For rowNum = startRow To ws.Dimension.End.Row
Dim wsRow = ws.Cells(rowNum, 1, rowNum, ws.Dimension.End.Column)
Dim row = tbl.NewRow()
For Each cell In wsRow
row(cell.Start.Column - 1) = cell.Text
Next
tbl.Rows.Add(row)
Next
Dim msg = String.Format("DataTable successfully created from excel-file Colum-count:{0} Row-count:{1}",
tbl.Columns.Count, tbl.Rows.Count)
UploadStatusLabel.Text = msg
End Using
Else
UploadStatusLabel.Text = "You did not specify an excel-file to upload."
End If
End Sub
For the sake of completeness, here's the aspx:
为了完整起见，这里是aspx：
<div>
<h4>Select a file to upload:</h4>
<asp:FileUpload id="FileUpload1"
runat="server">
</asp:FileUpload>
<br /><br />
<asp:Button id="UploadButton"
Text="Upload file"
OnClick="UploadButton_Click"
runat="server">
</asp:Button>
<hr />
<asp:Label id="UploadStatusLabel"
runat="server">
</asp:Label>
</div>
回答by Jordy van Eijk
Maybe you can take a look at Koograthis is an Open Source excel reader (readonly no writer) I think you will get a stream back from the client. Then you can do all your things like you're doing now Read from memorystream and write to Database.
也许你可以看看Koogra这是一个开源的 excel 阅读器（只读没有作者）我想你会从客户端得到一个流。然后你可以做你现在做的所有事情，从内存流中读取并写入数据库。
I hope this helps.
我希望这有帮助。
回答by Nilesh Nikumbh
//Best Way To read file direct from stream
IExcelDataReader excelReader = null;
//file.InputStream is the file stream stored in memeory by any ways like by upload file control or from database
int excelFlag = 1; //this flag us used for execl file format .xls or .xlsx
if (excelFlag == 1)
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(file.InputStream);
}
else if(excelFlag == 2)
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(file.InputStream);
}
if (excelReader != null)
{
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
ds = excelReader.AsDataSet();
//...
////4. DataSet - Create column names from first row
//excelReader.IsFirstRowAsColumnNames = true;
//DataSet result = excelReader.AsDataSet();
////5. Data Reader methods
//while (excelReader.Read())
//{
// //excelReader.GetInt32(0);
//}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
}
回答by Shah Aadil
This is how to do this in MVC using ClosedXML.Excel. I do know this answer is too late. I just wanted to put this answer for all those who land on this page after googling problem. In Visual Studio click on tools menu and expand NuGet Package Manager and then run Package manager console.Type the following command:
这是使用 ClosedXML.Excel 在 MVC 中执行此操作的方法。我知道这个答案为时已晚。我只是想为所有在谷歌搜索问题后登陆此页面的人提供这个答案。在 Visual Studio 中单击工具菜单并展开 NuGet 包管理器，然后运行包管理器控制台。键入以下命令：
Install-Package ClosedXML
The Model:
该模型：
namespace ExcelUploadFileDemo.Models
{
public class UploadFile
{
[Required]
public HttpPostedFileBase ExcelFile { get; set; }
}
}
The Controller:
控制器：
namespace ExcelUploadFileDemo.Controllers
{
public class HomeController : Controller
{
public ActionResult Index()
{
UploadFile UploadFile = new UploadFile();
return View(UploadFile);
}
[HttpPost]
public ActionResult Index(UploadFile UploadFile)
{
if (ModelState.IsValid)
{
if (UploadFile.ExcelFile.ContentLength > 0)
{
if (UploadFile.ExcelFile.FileName.EndsWith(".xlsx") || UploadFile.ExcelFile.FileName.EndsWith(".xls"))
{
XLWorkbook Workbook;
Try//incase if the file is corrupt
{
Workbook = new XLWorkbook(UploadFile.ExcelFile.InputStream);
}
catch (Exception ex)
{
ModelState.AddModelError(String.Empty, $"Check your file. {ex.Message}");
return View();
}
IXLWorksheet WorkSheet = null;
Try//incase if the sheet you are looking for is not found
{
WorkSheet = Workbook.Worksheet("sheet1");
}
catch
{
ModelState.AddModelError(String.Empty, "sheet1 not found!");
return View();
}
WorkSheet.FirstRow().Delete();//if you want to remove ist row
foreach (var row in WorkSheet.RowsUsed())
{
//do something here
row.Cell(1).Value.ToString();//Get ist cell. 1 represent column number
}
}
else
{
ModelState.AddModelError(String.Empty, "Only .xlsx and .xls files are allowed");
return View();
}
}
else
{
ModelState.AddModelError(String.Empty, "Not a valid file");
return View();
}
}
return View();
}
}
}
This link has many examples showing different ways of handling diverse stuff of excel.
此链接有许多示例，展示了处理各种 excel 内容的不同方法。
https://github.com/ClosedXML/ClosedXML/tree/9ac4d868a313f308b82e94617b9cc2d28baeb1c3/ClosedXML
https://github.com/ClosedXML/ClosedXML/tree/9ac4d868a313f308b82e94617b9cc2d28baeb1c3/ClosedXML
The View
风景
@model ExcelUploadFileDemo.Models.UploadFile
@{
ViewBag.Title = "Upload Excel File";
}
<h2>Upload an Excel File</h2>
@using (Html.BeginForm(null, null, FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div class="form-horizontal">
@Html.ValidationSummary("", new { @class = "text-danger" });
<div class="form-group">
@Html.LabelFor(model => model.ExcelFile, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.TextBoxFor(model => model.ExcelFile, new { type = "file", @class = "form-control" })
@Html.ValidationMessageFor(model => model.ExcelFile, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type = "submit" value="Submit" class="btn btn-default" />
</div>
</div>
</div>
}
