Execl导入系统

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;

}

}