Question

求asp.net(c#)将execl文件数据导入sql server数据库中的源程序

最好加点注释，要不然小弟看不明白啊！

Answer 1

if (excelhelper.DownLoadFile(FilePath, excelhelper.GetExcelDownLoadPath(this) + FileName, out error))
{
Business.Module.CM.BLL.tblcmbase_Server_Temp tblcmbase_Server = new Business.Module.CM.BLL.tblcmbase_Server_Temp();

if (tblcmbase_Server.ImportExcelData("信息表", excelhelper.GetExcelDownLoadPath(this) + FileName,fileupload1.DocIDValue , out error))
{
Utinity.ClientScriptHelper.WriteAlertSaveSuccess(this);

}
else
{
Utinity.ClientScriptHelper.WriteAlert(this, "导入信息出错！错误信息：" + error);
}
}
public bool ImportExcelData(string TableName, string Path, string FileAttID, out string error)
{
error = "";

try
{
Utinity.ExcelRWHelper _excel = new Utinity.ExcelRWHelper();

Microsoft.Office.Interop.Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
app.Visible = false;

Microsoft.Office.Interop.Excel.WorkbookClass workbook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(Path, //Environment.CurrentDirectory+
Missing.Value, true, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet datasheet = null;
bool isExcel = true;//判断导入文件是否正确
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets) //取出指定的sheet
{
if (sheet.Name == TableName)
{
datasheet = sheet;
isExcel = false;
break;
}
}
if (isExcel)
{
error = "导入Excel文件有误,请重新导入!";
return false;
}

app.Quit();
app = null;

System.Data.DataTable tbServer = _excel.ReadExcelData(Path, TableName);

for (int i = 0; i < tbServer.Rows.Count; i++)
{
if (tbServer.Rows[i][2].ToString() != "" && !tbServer.Rows[i][2].ToString().Equals("{}"))
{
decimal tempdecimal = decimal.Parse("0.00");

if (tbServer.Rows[i][6] == DBNull.Value || tbServer.Rows[i][6].ToString() == "")
{
error = "第" + (i + 2).ToString() + "行设备序列号不能为空！请检查excel文件数据格式！";
return false;
}

if (tbServer.Rows[i][7] == DBNull.Value || tbServer.Rows[i][7].ToString() == "")
{
error = "第" + (i + 2).ToString() + "行主机名(hostname)不能为空！请检查excel文件数据格式！";
return false;
}

}
}

DAL.tblcmbase_Server_Temp tblcmbase_Server = new DAL.tblcmbase_Server_Temp();

//导入临时表

tblcmbase_Server.InsertTempData(tbServer);

//数据对比
tblcmbase_Server.CompTempData(Framework.Assistant.Utility.GetGUID(), FileAttID, Utinity.User.CurrentUserName());

return true;
}
catch (Exception ex)
{
error = ex.ToString();
return false;
}
}

public string GetExcelDownLoadPath(System.Web.UI.Page page)
{
return ConfigurationManager.AppSettings["ExcelDownLoadPath"] == null ? page.Request.PhysicalApplicationPath : ConfigurationManager.AppSettings["ExcelDownLoadPath"];
}

public bool DownLoadFile(string SourceFilePath, string TargetFilePath, out string error)
{
error = "";

WebClient client = new WebClient();
client.Credentials = CredentialCache.DefaultCredentials;

try
{
client.DownloadFile(SourceFilePath, TargetFilePath);

return true;
}
catch (Exception ex)
{
error = ex.ToString();

return false;
}
}

Answer 2

private void BrowseSpecialtyl_Load(object sender, System.EventArgs e)
{

oleDbConnection1.Open();
string sql="select specialtyid as 编号,specialtyname as 专业名称,remark as 专业描述 from specialtyinfo";
OleDbDataAdapter adp=new OleDbDataAdapter(sql,oleDbConnection1);
ds=new DataSet();
adp.Fill(ds,"specialty");
dataGrid1.DataSource=ds.Tables[0].DefaultView;

}

Answer 3

C#中操作Excel建议使用NPOI，百度 NPOI 第一个，有教程，有实例

Answer 4

因为你在前面参数指定了参数类型，所以在后面必然会进行数据类型的检查，解决办法是先判断文本框是否是=“”；如果是就用dbnull.value来赋值
还有一个东西就是parameters.add这个方法，这个方法是要求指定类型的，还有一个好像是addbyvalue，这个不要求指定类型，你可以试试