正文
Execl导入系统
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
文件导入功能
前台代码：
Content\JS\jquery.ajaxfileupload.js
<script src="~/Content/JS/jquery.ajaxfileupload.js"></script>
附件在博客文档中可下载
<input type="file" id="files2" name="files2" />
<a href="javascript:BingXiangUploadFile();">上传</a>
function BingXiangUploadFile() {
if ($("#files2").val() != "") {
$.ajaxFileUpload
({
url: '/Import/BingXiangUploadFile',
secureuri: false,
fileElementId: 'files2',
uploadFiles: false,
dataType: 'json',
success: function (arr) {
var n = arr.split('!');
if (n[] == 'True') {
alert(arr);
location.reload();
}
else {
alert(arr);
location.reload();
}
},
error: function (data, status, e)//服务器响应失败处理函数
{
alert(e);
}
})
}
else {
alert("请选择要上传的文件!");
}
}
JS代码
后台代码：
public bool UserInfoUploadFile()
{
bool h = true;
try
{
if (Request.Files != null && Request.Files.Count != 0)
{
HttpPostedFileBase file = Request.Files[0];
string path = file.FileName;//获取Execle文件名
string oldfilename = System.IO.Path.GetFileNameWithoutExtension(path);
string IsXls = System.IO.Path.GetExtension(path).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
string fileName = DateTime.Now.ToString("yyyyMMddhh") + IsXls;
string savePath = Server.MapPath(("~\\Upload\\") + oldfilename + fileName);//Server.MapPath 获得虚拟服务器相对路径
file.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上
DataTable dt = Common.ExcelToTable(savePath, oldfilename + fileName);//调用自定义方法
DataRow[] dr = dt.Select();//定义一个DataRow数组
int rowsnum = dt.Rows.Count;//统计共有多少数据
if (rowsnum == 0)//判断数据为空时
{
Response.Write("Excel表为空表,无数据!");//当Excel表为空时,对用户进行提示
h = false;
return h;
}
else
{
string Str = "";
string StrV = "";
#region 添加
for (int i = 0; i < dr.Length; i++)//不为空时，用for循环
{
//try
//{
string StdName = dr[i]["标准或规范名称"].ToString();
string StdNo = dr[i]["标准或规范编号"].ToString();
string UserNo = dr[i]["需求人"].ToString();
//}
//catch { }
}
h = true;
Response.Write("数据上传成功!");
#endregion
}
}
}
catch (Exception kl)
{
Response.Write("部分数据未能导入成功");
h = false;
}
return h;
}
ExcelToTable 方法：
/// <summary>
/// Excel导入成Datable
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file, string table)
{
try
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheetAt(0); //表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据 for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
try
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
var A = i;
}
catch (Exception ex)
{
return null;
}
} }
return dt;
}
catch (Exception ex)
{
return null;
}
} /// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
short format = cell.CellStyle.DataFormat;
if (format != 0) { return cell.DateCellValue; } else { return cell.NumericCellValue; }
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
