这几天正好用到上传Excel,并根据Excel中的数据做相应的处理,故整理以备用。
用到的资源:
(1)NOPI 2.2.0.0 可自己官网下载,也可点击:http://pan.baidu.com/s/1b1EMdg
(2)用到一些常见处理文件的公共方法类,可以添加到项目中:http://pan.baidu.com/s/1bJpHuQ
如过上述连接因故无法使用,可在评论留下邮箱,我打包发送过去,如有更好的建议,欢迎指导。
后台的提示方法ShowMsgHelper,根据自己的改写即可。
前台代码:
<!DOCTYPE html><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"><meta http-equiv="Content-Type" content="text/html;
charset=utf-8"/> <title>导入EXCEL,生成DataTable</title> <script src="../../Themes/scripts/jquery-1.8.2.min.js"></script> <link href="/Themes/Styles/Site.css" rel="external nofollow" rel="stylesheet" type="text/css" /> <script src="/Themes/scripts/FunctionJS.js" type="text/javascript"></script> <script type="text/javascript"> $(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;
}
);
}
);
</script></head><body> <form id="form1" runat="server"> <div> <asp:FileUpload ID="FileUpload1" runat="server" /><asp:Button ID="Import" runat="server" Text="导入" OnClick="ImpClick" /> </div> </form></body></html>
后台代码;
protected void ImpClick(object sender, 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/,根据自己配置选择 string path = Server.MapPath("~/") + "Upload\tmp\";
string retStr=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中的值 string test = dt.Rows[0]["name"].ToString();
string test2 = dt.Rows[1]["class"].ToString();
//删除临时文件 DirFileHelper.DeleteFile("Upload\tmp\" + fileName);
#endregion }
catch (Exception ex) {
throw ex;
}
}
/// <summary> /// 将excel导入到datatable /// </summary> /// <param name="filePath">excel路径</param> /// <param name="isColumnName">第一行是否是列名</param> /// <returns>返回datatable</returns> public DataTable ExcelToDataTable(string filePath, bool isColumnName) {
DataTable dataTable = null;
FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
int startRow = 0;
try {
using (fs = File.OpenRead(filePath)) {
// 2007版本 if (filePath.IndexOf(".xlsx") > 0) workbook = new XSSFWorkbook(fs);
// 2003版本 else if (filePath.IndexOf(".xls") > 0) workbook = new HSSFWorkbook(fs);
if (workbook != null) {
sheet = workbook.GetSheetAt(0);
//读取第一个sheet,当然也可以循环读取每个sheet dataTable = new DataTable();
if (sheet != null) {
int rowCount = sheet.LastRowNum;
//总行数 if (rowCount > 0) {
IRow firstRow = sheet.GetRow(0);
//第一行 int cellCount = firstRow.LastCellNum;
//列数 //构建datatable的列 if (isColumnName) {
startRow = 1;
//如果第一行是列名,则从第二行开始读取 for (int i = firstRow.FirstCellNum;
i < cellCount;
++i) {
cell = firstRow.GetCell(i);
if (cell != null) {
if (cell.StringCellValue != null) {
column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
}
}
else {
for (int i = firstRow.FirstCellNum;
i < cellCount;
++i) {
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
//填充行 for (int i = startRow;
i <= rowCount;
++i) {
row = sheet.GetRow(i);
if (row == null) continue;
dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum;
j < cellCount;
++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) {
case CellType.Blank: dataRow[j] = "";
break;
case CellType.Numeric: short format = 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;
case CellType.String:dataRow[j] = cell.StringCellValue;
break;
}
}
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
return dataTable;
}
catch (Exception) {
if (fs != null) {
fs.Close();
}
return null;
}
}
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持脚本之家!