整理代码,.net上传文件,利用npoi读取文件到datatable里,使用了FileUpload控件,代码如下:
protected void Button1_Click(object sender, EventArgs e)
{
try
{
#region 上传文件
if (FileUpload1.HasFile)//判断是否存在上传文件
FileUpload1.SaveAs(Server.MapPath("~/") + FileUpload1.FileName);
else
{
ScriptManager.RegisterClientScriptBlock(this, GetType(), "aaa", "alert('上传文件不存在或上传文件为空,请检查文件');", true);
return;
}
#endregion
#region 读取文件
DataTable dt = null;
string fileEx = FileUpload1.FileName.Substring(FileUpload1.FileName.LastIndexOf(".") + );
if (fileEx == "xls")
{
dt = IO.ImportExcelFile(Server.MapPath("~/") + FileUpload1.FileName);
}
else if (fileEx == "xlsx")
{
dt = IO.ImportExcelFilexlsx(Server.MapPath("~/") + FileUpload1.FileName);
}
if (dt.Rows.Count < )
{
ScriptManager.RegisterClientScriptBlock(this, GetType(), "aaa", "alert('上传文件为空,请检查文件内容');", true);
return;
}
#endregion
}
catch (Exception ex)
{
ScriptManager.RegisterClientScriptBlock(this, GetType(), "aaa", "alert('系统异常," + ex + "');", true);
}
}
npoi读取文件的方法:
public static DataTable ImportExcelFile(string filePath)
{
HSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt();
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = ; j < (sheet.GetRow().LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow().GetCell(j).ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
if (row.RowNum != )
{
DataRow dr = dt.NewRow();
for (int i = ; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
public static DataTable ImportExcelFilexlsx(string filePath)
{
XSSFWorkbook hssfworkbook;
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
#endregion
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt();
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = ; j < (sheet.GetRow().LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow().GetCell(j).ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
if (row.RowNum != )
{
DataRow dr = dt.NewRow();
for (int i = ; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
}
return dt;
}