代码以及遇到的问题:
(1)前台页面:
选择Excel文件:<input id="FileExcel" style="WIDTH: 300px" type="file" size="42" name="FilePhoto" runat="server">
<asp:Button ID="BtnImport" class="btn btn-primary" Text="保 存" runat="server" OnClick="BtnImport_Click" />
<input id="btnBack" class="btn btn-primary" type="button" value="返 回" οnclick="location.href = 'List.aspx?adId=<%=adId%>'"/>
<div runat="server" id="divmes" style="color:red;display: inline-block; margin-left:5px;"></div>
(2)后端代码:
#region Excel导入数据库
protected void BtnImport_Click(object sender, EventArgs e)
{
#region
string mes = string.Empty;
string fileName = string.Empty;
try
{
int code = UpLoadXls(FileExcel, out fileName);//上传Excel文件
if (String.IsNullOrEmpty(fileName))
{
divmes.InnerHtml = "请先上传Excel文件";
return;
}
int retCode = ImportExcelToData(fileName);//将Excel导入数据库
if (retCode > 0)
{
mes = "导入成功!";
}
else
{
#region 错误提示
switch (retCode)
{
case -1:
mes = "异常错误!";
break;
case -2:
mes = "Excel格式错误!";
break;
case -3:
mes = "导入数据为空!";
break;
default:
mes = "异常错误!";
break;
}
#endregion
}
//删除上传的XLS文件
if (!String.IsNullOrEmpty(fileName) && System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}
}
catch
{
mes = "导入失败!";
}
divmes.InnerHtml = mes;
#endregion
}
/// <summary>
/// 上传Excel文件
/// </summary>
/// <param name="inputfile">上传的控件名</param>
/// <returns></returns>
public int UpLoadXls(System.Web.UI.HtmlControls.HtmlInputFile inputfile, out string lastFileName)
{
#region
int code = 0;
lastFileName = string.Empty;
string uploadfilepath = string.Empty;
string newFileName = string.Empty;
string fileExtend = "";
try
{
if (!String.IsNullOrEmpty(inputfile.Value))
{
int fileSize = inputfile.PostedFile.ContentLength;
if (fileSize == 0)
{
code = -1;//导入的Excel文件大小为0,请检查是否正确!
}
#region 获取扩展名
int isTrue = 0;
List<string> listExtend = new List<string>();
listExtend.Add("xls");
listExtend.Add("xlsx");
fileExtend = FileHelper.GetFileExtension(inputfile.Value);
foreach (var ext in listExtend)
{
if (fileExtend.ToLower() == ext)
{
isTrue = 1;
break;
}
}
if (isTrue == 0)
{
code = -2;//你选择的文件格式不正确,只能导入EXCEL文件!
}
#endregion
#region 上传excel
//新文件名
newFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + fileExtend;
//路径
uploadfilepath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "\\Upload\\AdservingExel\\";
//判断是否有该目录
System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(uploadfilepath);
if (!dir.Exists)
{
dir.Create();
}
lastFileName = uploadfilepath + "\\" + newFileName;
//如果文件存在,删除文件
if (File.Exists(lastFileName))
{
File.Delete(lastFileName);
}
// 上传文件
inputfile.PostedFile.SaveAs(lastFileName);
if (!String.IsNullOrEmpty(lastFileName))
{
code = 1;
}
else
{
code = -1;
}
#endregion
}
else
{
code = -3;//请选择要导入的Excel文件!
}
}
catch
{
code = 0;
}
return code;
#endregion
}
//读取Excel数据
public int ImportExcelToData(string fileName)
{
#region
int code = 0;
try
{
string oleDBConnString = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + fileName + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
if (fileName.Contains("xls") || fileName.Contains("xlsx"))
{
#region 读取
oleDBConnString = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + fileName + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
OleDbConnection oleDBConn = null;
OleDbDataAdapter oleAdMaster = null;
DataTable m_tableName = new DataTable();
DataSet ds = new DataSet();
oleDBConn = new OleDbConnection(oleDBConnString);
oleDBConn.Open();
m_tableName = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
if (m_tableName != null && m_tableName.Rows.Count > 0)
{
m_tableName.TableName = m_tableName.Rows[0]["TABLE_NAME"].ToString();
}
string sqlMaster;
sqlMaster = " SELECT * FROM [" + m_tableName.TableName + "]";
oleAdMaster = new OleDbDataAdapter(sqlMaster, oleDBConn);
oleAdMaster.Fill(ds, "m_tableName");
oleAdMaster.Dispose();
oleDBConn.Close();
oleDBConn.Dispose();
#endregion
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
code = AddDatasetToSQL(ds, 9);
}
}
}
catch
{
code = -1;
}
return code;
#endregion
}
/// <summary>
/// 将Dataset的数据导入数据库
/// </summary>
/// <param name="pds">数据集</param>
/// <param name="Cols">数据集列数</param>
/// <returns></returns>
private int AddDatasetToSQL(DataSet pds, int Cols)
{
#region
int code = 0;
int columnsCount, rowsCount;
try
{
columnsCount = pds.Tables[0].Columns.Count;
if (columnsCount < Cols)
{
code = -2;
}
rowsCount = pds.Tables[0].Rows.Count;
if (pds != null && rowsCount > 0)
{
DataTable dtData = pds.Tables[0];
for (int i = 0; i < dtData.Rows.Count; i++)
{
#region 导入数据
try
{
//加入数据库代码
}
catch
{
continue;
}
#endregion
}
code = 1;
}
else
{
code = -3;//导入数据为空
}
}
catch
{
code = -1;
}
return code;
#endregion
}
#endregion
.Microsoft.Jet.OLEDB.4.0在64位系统上不支持,需要修改架构,从x64改为x86,无论是WinForm还是ASP.NET;
或者修改连接字符串为Microsoft.ACE.OLEDB.12.0并且安装AccessDatabaseEngine x64数据访问组件;
在asp.net网站中导出Excel文件后,再把文件导入到数据库中。 读取Excel文件时,打开连接出错。
错误为:外部表不是预期的格式
解决:检查了一下,导出的Excel是标准文件不是html,没错,Excel文件正常。
调试代码,创建连接对象oleDbConnection也正常,但在conn.Open()打开链接时出错。
仔细看了下链接字符串,检查出了错误,Excel版本问题,Exce连接字符串版本是office2003的 ,更改为Excel2007版本则正常导入。
(3).csv格式数据文件导入数据库出现问题:
这段出错,'G:\zxb\test\Test\create20091225.csv'不是一个有效的路径。 确定路径名称拼写是否正确,以及是否连接到文件存放的服务器。
解决办法:
将filepath改为:
Directory.GetParent(filepath)