上传一个Excel并解析,将解析结果存到DataTable中,然后再页面显示
1. 后台上传和解析代码
public class ExcelController : Controller
{
public ActionResult ExcelUpload()
{
return View();
}
public ActionResult ExcelUploadSubmit(string excelTitle)
{
HttpPostedFileBase excelFile = Request.Files["excelFile"]; //取到上传域
if (null != excelFile)
{
string fileName = Path.GetFileName(excelFile.FileName); //取到文件的名称
if(fileName.Equals("")||null == fileName){ //没有选择文件就上传的话,则跳回到上传页面
return View("ExcelUpload");
}
string serverpath = Server.MapPath("/");
excelFile.SaveAs(serverpath + @"\Upload\" + fileName); //保存上传文件
//parse the excel
//save the excel content in the datatable
DataTable dt = new DataTable();
dt.Columns.Add("Dept", Type.GetType("System.String"));
dt.Columns.Add("FlowName", Type.GetType("System.String"));
dt.Columns.Add("CurrUser", Type.GetType("System.String"));
dt.Columns.Add("ApplyUser", Type.GetType("System.String")); //新建一个DataTable,并指定列和列的类型
FileStream inputStream = new FileStream(serverpath + @"\Upload\" + fileName, FileMode.Open);
HSSFWorkbook workbook = new HSSFWorkbook(inputStream); //解析上传的Excel
HSSFSheet sheet = workbook.GetSheetAt(0) as HSSFSheet;
int rowNum = sheet.PhysicalNumberOfRows;
for (int i = 0; i < rowNum;i++ )
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
int cellNum = row.PhysicalNumberOfCells;
DataRow newRow = dt.NewRow(); //DataTable创建新行
for (int j = 0; j < cellNum;j++ )
{
HSSFCell cell = row.GetCell(j) as HSSFCell;
if (cell.CellType == CellType.Numeric)
{
newRow[j] = cell.NumericCellValue; //给新建的行加列
}
else {
newRow[j] = cell.StringCellValue;
}
}
dt.Rows.Add(newRow); //新建的行加入到DataTable中
}
ViewData["excelTitle"] = excelTitle;
ViewData["dt"] = dt; //存起来以便在前台显示
}
return View("ExcelUpload");
}
}
2. 前台的页面ExcelUpload.aspx
<form action="/Excel/ExcelUploadSubmit" method="post" enctype="multipart/form-data">
<p>
Title: <input id="excelTitle" name="excelTitle" type="text" />
</p>
<p>
File: <input id="excelFile" name="excelFile" type="file" />
</p>
<p>
<input id="Submit1" type="submit" value="submit"/></p>
</form>
<table id="dataTable">
<tr>
<td colspan="4" align="center">
<%=ViewData["excelTitle"]%>
</td>
</tr>
<%
DataTable dt = (DataTable)ViewData["dt"];
if(null != dt){
for (int i = 0; i < dt.Rows.Count;i++ )
{
%>
<tr>
<td><%=dt.Rows[i]["Dept"]%></td>
<td><%=dt.Rows[i]["FlowName"]%></td>
<td><%=dt.Rows[i]["CurrUser"]%></td>
<td><%=dt.Rows[i]["ApplyUser"]%></td>
</tr>
<%
}
%>
<%
}
%>
</table>