asp.net上传EXCEL文件转DataTable(自用笔记)

江浩慨
2023-12-01

html:

<script type="text/javascript" src="@Url.Content("~/Scripts/ajaxupload-min.js")"></script>

$(function () {
        $("#uploadify3").uploadify({
            //指定swf文件
            'swf': '/Scripts/uploadify/uploadify.swf',
            //后台处理的页面
            'uploader': '/Teaching/CourseTimes/CourseTimesImport',
            //按钮显示的文字
            'buttonText': '选择Excel文件',
            //在浏览窗口底部的文件类型下拉菜单中显示的文本
            'fileTypeDesc': 'Excel Files',
            //允许上传的文件后缀
            'fileTypeExts': '*.xls; *.xlsx;',
            //选择文件后自动上传
            'auto': false,
            //设置为true将允许多文件上传
            'multi': false,
            'removeTimeout': 1,
            'onUploadStart': function (file) {
                //在onUploadStart事件中,也就是上传之前,把参数写好传递到后台。
                $("#uploadify3").uploadify("settings", "formData", { 'courseId': document.getElementById('hidCoursePlans').value });
                LoadEvent.ShowMask();
            },
            'onUploadSuccess': function (file, data, response) {
                if (data == "0") {
                    $.messager.alert('友情提示', '上传Excel文件失败!请选择有效的Excel文件!', 'error');
                }
                else if (data == "1") {
                    $('#importCoursePlansForm').form('clear');
                    $('#importCoursePlansDiv').dialog('close');
                    $.messager.alert('友情提示', '上传Excel并导入数据库成功!', 'info');
                    //$("#btnSerach").click();
                    $("#CoursePlans_DataGrid").datagrid('reload');
                }
                else if (data == "-1") {
                    $.messager.alert('友情提示', '导入数据失败!请检查Excel内数据!', 'error');
                }
                else {
                    $.messager.alert('友情提示', data, 'error');
                }
                LoadEvent.CloseMask();
            }
        });
    });

mvc:

        public string CourseTimesImport(string courseid)
        {
            //获取课程的信息
            var CoursePlans = CourseTimesBLL.GetCoursePlansFirstOrDefault(int.Parse(courseid));

            //上传Excel
            var file = Request.Files["Filedata"];
            var uploadPath = Server.MapPath("~/TempUpload" + "\\");
            if (file != null)
            {
                #region 第一步:上传文件
                string theFile = string.Empty;
                if (!Directory.Exists(uploadPath))
                    Directory.CreateDirectory(uploadPath);//生成文件夹                    
                theFile = uploadPath + file.FileName;//文件完整路径
                file.SaveAs(theFile);//保存文件
                #endregion
                return CourseTimesBLL.CourseTimesImport(theFile, UserInformation.UserName, CoursePlans);
            }
            return "0";//上传Excel失败
        }

public string CourseTimesImport(string theFile, string UserName, CoursePlansViewModel CoursePlans)
        {
            //读取Excel内容
            DataSet ds = ExcelHelper.ExcelToDataSet(theFile, true);
            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataTable dt = ds.Tables[0];
                    //if (CoursePlans.Times == ds.Tables[0].Rows.Count)
                    //{
                    if (ds.Tables[0].Rows.Count > 0)//至少要有1行数据
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            CourseTimes courseTimes = new CourseTimes();
                            CourseTimesCompositeID CompositeID = new CourseTimesCompositeID();
                            CompositeID.CourseID = CoursePlans.CourseId;
                            CompositeID.FID = (i + 1);
                            courseTimes.CourseTimesCompositeID = CompositeID;
                            courseTimes.Content = dt.Rows[i][1].ToString();//内容                            
                            courseTimes.Remark = dt.Rows[i][2].ToString();//备注                          
                            courseTimes.IsValid = true;
                            courseTimes.CreateBy = UserName;
                            courseTimes.CreateDate = DateTime.Now;
                            courseTimes.UpdateBy = UserName;
                            courseTimes.UpdateDate = DateTime.Now;
                            if (!string.IsNullOrEmpty(courseTimes.Content) && !string.IsNullOrWhiteSpace(courseTimes.Content))//至少要有个标题吧
                            {
                                CourseTimesService.ImportCourseTimes(courseTimes);//写入
                                Thread.Sleep(1);
                            }
                        }
                        CoursePlansService.FinishCoursePlans(CoursePlans.CourseId, true, UserName);//修改是否完成状态
                        return "1";
                    }
                    //}
                    //else
                    //{
                    //    return "本课程总次数为:" + CoursePlans.Times + "次,与EXCEL中的次数不符。";
                    //}
                }
            }
            return "0";
        }

ExcelHelper:

public static DataSet ExcelToDataSet(string filpath, bool hdr)
        {
            //返回的 DataSet 表
            DataSet ds = new DataSet();
            if (File.Exists(filpath))
            {
                //连接字符串
                string strConn = string.Format("Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source={0};Extended Properties='Excel 12.0;HDR={1};IMEX=1'", filpath, hdr.ToString());
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                //sheet表
                DataTable sheetTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                if (sheetTable != null && sheetTable.Rows.Count > 0)
                {
                    foreach (DataRow dr in sheetTable.Rows)
                    {
                        string sheetName = dr["TABLE_NAME"].ToString();
                        string strExcel = "select * from [" + sheetName + "]";
                        OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
                        myCommand.Fill(ds, sheetName.Substring(1, sheetName.Length - 3));
                    }
                }
                conn.Close();
            }
            return ds;
        }

 类似资料: