I want to achieve the following
1. I want to upload an excel file to Server through web page.
2. the uploaded file will not be written to physical storage,
instead the code behind will read the data from a stream(httppostedfile) before uploading it to MSSQL server.
3. Writing the xls file to server might encounter a permission error. Incase if it is allowed, the files will occupy space in the storage without any use anymore.
I have not seen an approach like direct reading from stream.
All i found is to write it to phsycal file (c:\xxx\xx.xls) and then read the content.
How can I read the columns without writing the file to a folder?
解决方案Here''s an example which shows how to create a DataTable from the IO.Stream of a HttpPostedFile using 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.";
}
}
I have checked for this and it is working for me.