导入Excel至SQL Server

来源：互联网发布：世界卫生组织官网数据编辑：程序博客网时间：2024/06/04 23:24
导入Excel至SQL Server 需要ExcelDataHelper操作类的帮助
           string root = context.Server.MapPath("~/UploadFiles/");            //找到目标文件对象            HttpPostedFile uploadFile = context.Request.Files["Fdata"];            //获取文件后缀            string ext = uploadFile.FileName.Substring(uploadFile.FileName.LastIndexOf("."));            string file = string.Format("{0}{1}{2}", root, DateTime.Now.ToString("yyMMddhhmmssffff"), ext);            uploadFile.SaveAs(file);            if (File.Exists(file))            {                ExcelDataHelper excelDataHelper = new ExcelDataHelper(file, uploadFile.FileName);                string[] sheets = excelDataHelper.GetExcelSheetNames(file, uploadFile.FileName);                DataTable dt = excelDataHelper.GetAllData(sheets[0]);  //获取excel数据                DataColumnCollection col = dt.Columns;                if (dt == null)                {                    return "没有需要导入的数据";                }                else                {                    #region 填充数据                    DataTable riskindex = dt as DataTable;                    //构建新的结果集                    DataTable newdt = new DataTable();                    newdt.Columns.Add("Columns1");                    newdt.Columns.Add("Columns2");                    for (int i = 0; i < dt.Columns.Count; i++)                    {                        newdt.Columns.Add(dt.Columns[i].ColumnName);                    }                    for (int i = 0; i < dt.Rows.Count; i++)                    {                        List<DataRow> rows = riskindex.AsEnumerable().ToList();                        if (rows.Count > 0)                        {                            try                            {                                DataRow dr = newdt.NewRow();                                dr["Columns1"] = rows[i]["Value1"].ToString();                                dr["Columns2"] = rows[i]["Value2"].ToString();                                newdt.Rows.Add(dr);                            }                            catch (Exception ex)                            {                                return "导入出现问题！ " + ex;                            }                        }                    }                    if (newdt.Rows.Count == 0)                    {                        return "没有需要导入的数据";                    }                    //导入到数据库                    testbll bll = new testbll();                    rObj = bll.ImportEconomyDatas(newdt);                    if (!rObj.IsSuccess)                    {                        return "导入出现问题！" + rObj.SystemMessage;                    }                    else                    {                        try                        {                            //删除临时文件                            System.IO.File.Delete(file);                        }                        catch                        {                            return "导入失败";                        }                    }                    #endregion                }            }            else            {                return "导入的文件不存在";            }
菜鸟总结多存疏漏感谢指正
0 0