ASP.NET实现上传Excel功能

$(document).ready(function () {

$("#Import").click(function () {

var filename = $("#FileUpload1").val();

if (filename == '') {

alert('请选择上传的EXCEL文件');

return false;

}

else {

var exec = (/[.]/.exec(filename)) ? /[^.]+$/.exec(filename.toLowerCase()) : '';

if (!(exec == "xlsx" || exec == "xls")) {

alert("文件格式不对，请上传Excel文件!");

return false;

}

}

return true;

});

});

protectedvoidImpClick(objectsender, EventArgs e)

{

try

{

#region 校验

var fileName =this.FileUpload1.FileName;

if(string.IsNullOrWhiteSpace(fileName))

{

//提示信息

ShowMsgHelper.Alert("请选择上传Excel文件");

return;

}

//获取上传文件扩展名称

if(!(fileName.IndexOf(".xlsx") > 0 || fileName.IndexOf(".xls") > 0))

{

ShowMsgHelper.Alert("上传文件格式不正确，请核对！");

return;

}

#endregion

#region 将Excel文件上传到服务器上临时文件夹中

//临时文件夹，根目录下/Upload/tmp/，根据自己配置选择

stringpath = Server.MapPath("~/") +"Upload\tmp\";

stringretStr=UploadHelper.FileUpload(path,this.FileUpload1);

if(!retStr.Equals("上传成功")) {

ShowMsgHelper.Alert(retStr);

return;

}

#endregion

#region 读取Excel文件第一个表获取内容并转换成DataTable,删除临时文件，也可以自己加时间戳，维护处理

DataTable dt =this.ExcelToDataTable(path +this.FileUpload1.FileName,true);

if(dt ==null) {

ShowMsgHelper.Alert_Error("获取失败");

return;

}

//示例：获取dt中的值

stringtest = dt.Rows[0]["name"].ToString();

stringtest2 = dt.Rows[1]["class"].ToString();

//删除临时文件

DirFileHelper.DeleteFile("Upload\tmp\"+ fileName);

#endregion

}

catch(Exception ex) {

throwex;

}

}

///

/// 将excel导入到datatable

///

/// excel路径

/// 第一行是否是列名

/// 返回datatable

publicDataTable ExcelToDataTable(stringfilePath,boolisColumnName)

{

DataTable dataTable =null;

FileStream fs =null;

DataColumn column =null;

DataRow dataRow =null;

IWorkbook workbook =null;

ISheet sheet =null;

IRow row =null;

ICell cell =null;

intstartRow = 0;

try

{

using(fs = File.OpenRead(filePath))

{

// 2007版本

if(filePath.IndexOf(".xlsx") > 0)

workbook =newXSSFWorkbook(fs);

// 2003版本

elseif(filePath.IndexOf(".xls") > 0)

workbook =newHSSFWorkbook(fs);

if(workbook !=null)

{

sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet

dataTable =newDataTable();

if(sheet !=null)

{

introwCount = sheet.LastRowNum;//总行数

if(rowCount > 0)

{

IRow firstRow = sheet.GetRow(0);//第一行

intcellCount = firstRow.LastCellNum;//列数

//构建datatable的列

if(isColumnName)

{

startRow = 1;//如果第一行是列名，则从第二行开始读取

for(inti = firstRow.FirstCellNum; i

{

cell = firstRow.GetCell(i);

if(cell !=null)

{

if(cell.StringCellValue !=null)

{

column =newDataColumn(cell.StringCellValue);

dataTable.Columns.Add(column);

}

}

}

}

else

{

for(inti = firstRow.FirstCellNum; i

{

column =newDataColumn("column"+ (i + 1));

dataTable.Columns.Add(column);

}

}

//填充行

for(inti = startRow; i

{

row = sheet.GetRow(i);

if(row ==null)continue;

dataRow = dataTable.NewRow();

for(intj = row.FirstCellNum; j

{

cell = row.GetCell(j);

if(cell ==null)

{

dataRow[j] ="";

}

else

{

//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)

switch(cell.CellType)

{

caseCellType.Blank:

dataRow[j] ="";

break;

caseCellType.Numeric:

shortformat = cell.CellStyle.DataFormat;

//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理

if(format == 14 || format == 31 || format == 57 || format == 58)

dataRow[j] = cell.DateCellValue;

else dataRow[j] = cell.NumericCellValue;

break;

caseCellType.String:

dataRow[j] = cell.StringCellValue;

break;

}

}

}

dataTable.Rows.Add(dataRow);

}

}

}

}

}

returndataTable;

}

catch(Exception)

{

if(fs !=null)

{

fs.Close();

}

returnnull;

}

}