.net-Excel导入-OleDB方式（备注：表头在第二行/…

2018-06-22 07:54:36来源：未知阅读 ()

优点：将Excel直接当做数据源处理，通过SQL直接读取内容，读取速度较快。

缺点：读取数据方式不够灵活，无法直接读取某一个单元格，只有将整个Sheet页读取出来后（结果为Datatable）再在Datatable中根据行列数来获取指定的值。

当Excel数据量很大时。会非常占用内存，当内存不够时会抛出内存溢出的异常。

aspx代码：

<asp:FileUpload ID="FileUpload2" runat="server" Width="265px" Style=" float:left; height:25px;" />
<asp:Button ID="Button2" runat="server" Text="第2阶段：建议答复及评比推荐表-导入" CssClass="pl"
Style="margin: 10px 10px; width:250px; float:left " onclick="Button2_Click" />

cs代码：

protected void Button2_Click(object sender, EventArgs e)
{
if (!FileUpload2.PostedFile.FileName.Trim().ToLower().EndsWith(".xls"))
{
this.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('只能上传Excel文件(.xls).');", true);
return;
}
string strFullName = this.FileUpload2.PostedFile.FileName.ToString();
//先上传文件到服务器固定路径
string FileName = FileUpload2.PostedFile.FileName;
string FilePathName = "";
string strFileName = "";
if (FileName != "")
{
string dateStr = DateTime.Now.ToString("yyyyMMddhhmmssms");
string filename = Path.GetFileNameWithoutExtension(FileName);
string oldfilekz = Path.GetExtension(FileName);
FilePathName = Server.MapPath("~") + "File\\" + filename + dateStr + oldfilekz;
FileUpload2.PostedFile.SaveAs(FilePathName);
strFileName = Server.MapPath("~") + "File\\" + filename + dateStr + oldfilekz;
}
//以Excel做为数据库导入数据
string strCon = "Provider=Microsoft.Jet.OleDb.4.0;"
+ "data source=" + strFileName + ";"
+ "Extended Properties='Excel 8.0;HDR=NO; IMEX=1'";

ArrayList list = new ArrayList();
int updatecount = 0;
OleDbConnection con = new OleDbConnection(strCon);
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet0$]", con);
DataSet ds = new DataSet();
da.Fill(ds, "excel");

DataTable dt = new DataTable();
dt = GetExcelTable(ds.Tables["excel"]);
//string aa = ds.Tables["excel"].Rows[3][1].ToString();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
updatecount += 1;
StringBuilder Sql = new StringBuilder();
string TOPICID = dt.Rows[i]["序列号"].ToString();
string STATE = (dt.Rows[i]["是否采纳"].ToString() == "是") ? "5" : "5-1";
Sql.Append(" update bbs_topic set ZN_DEPT='" + ds.Tables["excel"].Rows[i]["答复部门"].ToString() + "',DFR='" + dt.Rows[i]["答复人"].ToString() + "',ZXJY='" + dt.Rows[i]["是否属于专项建议"].ToString() + "',STATE='" + STATE + "',DFYJ='" + dt.Rows[i]["答复意见"].ToString() + "',TA_YX='" + dt.Rows[i]["是否推荐为优秀建议"].ToString() + "',TJJX='" + dt.Rows[i]["推荐奖项"].ToString() + "' where TOPICID='" + TOPICID + "'");
list.Add(Sql);
}
if (list.Count != 0)
{
if (DbCommon.SqlTranList(list)) {
this.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('信息导入成功！');", true);
}
}
}
else
{
this.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('导入的文件没有记录！');", true);
return;
}
}

/// <summary>
/// 去除不必要的标题行
/// </summary>
/// <param name="table"></param>
/// <returns></returns>

public DataTable GetExcelTable(DataTable table)
{
foreach (DataRow row in table.Rows)
{
if (!(row[0].ToString()).Equals("序列号"))
{
row.Delete();
}
else
{
break;
}
}
table.AcceptChanges();
for (int i = 0; i < table.Columns.Count; i++)
{
table.Columns[i].ColumnName = table.Rows[0][i].ToString();
}
table.Rows[0].Delete();
table.AcceptChanges();
//table = table.GetChanges(DataRowState.Unchanged);
return table;
}