asp.net 导入excel报外部表不是预期的格式 如何解决

Question

如题所述

Answer 1

添加引用导入命名空间mshtml; 在写方法 string msg = "";
if (this.fuLoad.HasFile)
{
string file = this.fuLoad.FileName;
string fileName = this.fuLoad.PostedFile.FileName;
string kmdm = ViewState["kmdm"].ToString();
string mc = ViewState["mc"].ToString();
string[] strs = file.Split('_');
string mc2 = strs[1].ToString();
User user = Session["user"] as User; string xd = RoomBll.getXd(user.Dwdm);//查出学校拥有的学段
string[] xds = xd.Split(',');
string xdm = xds[xds.Length - 1].ToString();//学段码
if (mc == mc2)
{
try
{
int RecCount = 0;
int ImportedRecCount = 0;
List<RPT_ASSET> lstEntity = new List<RPT_ASSET>(); FileStream fs = new FileStream(fileName, FileMode.Open);//获取导入Excel的文件流
byte[] b = new byte[fs.Length];
fs.Read(b, 0, Convert.ToInt32(fs.Length));
string strDoc = System.Text.Encoding.UTF8.GetString(b, 0, b.Length);
IHTMLDocument2 doc = new HTMLDocumentClass();
doc.write(strDoc);
doc.close();
fs.Close();
fs.Dispose();
IHTMLElementCollection tb = ((HTMLBody)doc.body).getElementsByTagName("table"); foreach (HTMLTable el in tb)
{
int len = ((mshtml.IHTMLTableRow)el.rows.item(0, 0)).cells.length;

int t = 0;
foreach (IHTMLTableRow tr in el.rows)
{
t++;
if (t <= 2) continue;//跳过第一行 RecCount++;//总记录数 RPT_ASSET model = new RPT_ASSET();
model.Sl = ((mshtml.HTMLTableCell)tr.cells.item(9, 0)).innerText.ToString().Trim();
if (model.Sl != ""&&model!=null)
{
model.Dwdm = user.Dwdm;
model.Fldm = kmdm;
model.AssetId = ((mshtml.HTMLTableCell)tr.cells.item(0, 0)).innerText; model.Xdm = AssetBll.getMaxXdm(kmdm, model.AssetId);
model.Dj = ((mshtml.HTMLTableCell)tr.cells.item(6, 0)).innerText; model.Je = (Convert.ToInt32(model.Sl) * Convert.ToDouble(model.Dj)).ToString();
model.Bpsl = ((mshtml.HTMLTableCell)tr.cells.item(7, 0)).innerText;
model.Bpje = (Convert.ToInt32(model.Bpsl) * Convert.ToDouble(model.Dj)).ToString();
if (model.Bpsl.ToString() != "0")
{
model.Qksl = (Convert.ToInt32(model.Bpsl) - Convert.ToInt32(model.Sl)).ToString();
model.Qkje = (Convert.ToInt32(model.Bpje) - Convert.ToInt32(model.Je)).ToString();
}
else
{
model.Qksl = "0";
model.Qkje = "0";
}
lstEntity.Add(model);
}
}
}
if (lstEntity != null && lstEntity.Count > 0)
{
string table = AssetBll.getSjDwdm(user.Dwdm);
string tableName = "RPT_ASSET_" + table;
ImportedRecCount = AssetBll.addAssets(lstEntity, tableName);
// msg = "总记录数：[" + RecCount.ToString() + "] 条，成功导入：[" + RecCount.ToString() + "]条.";
msg = "成功导入：[" + lstEntity.Count.ToString() + "]条.";
}
else
{
msg = "无有效数据。";
} }
catch (Exception ex)
{
msg = "导入出错：" + ex.Message;
}
}
ClientScript.RegisterStartupScript(this.GetType(), "aa", "<script>alert('" + msg + "');</script>");
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "aa", "<script>alert('请选择要上传的文件！');</script>");
}

Answer 2

你是excel 吧,问题也不写清楚用ADO.NET 读取 Excel然后再用ADO。NET Update 回Db 如： Excel文件的读取 public DataSet ExcelToDS(string Path) 对于EXCEL中的表即sheet([sheet1$])如果不是固定的可以使用下面的方法得到 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null); string tableName=schemaTable.Rows[0][2].ToString().Trim(); Excel文件的写入 public void DSToExcel(string Path,DataSet oldds) newds.Tables["Table1"].Rows.Add(nrow); } myCommand.Update(newds,"Table1"); myConn.Close(); } 答案补充 然那段复制上去,然后strExcel="select * from ["+tableName+"]"; 这个函数执行完就返回一个dataset了,你循环遍历插入你的数据库就可以了 答案补充 我那个excel不会是.net导出来的吧,导出来的好像不能直接再导进去