如题,谢谢各位高手
解决方案 »
- Web.Config加入customErrors配置节引发了脚本错误
- 创建asp.net项时的问题
- 关于 System.Data.SqlClient
- gridview 怎么不显示呢?
- 高手请进,.net和asp系统共存,运行一段时间后.aspx的文件都不可以浏览,很是郁闷!
- response.redirect()在类文件中不能用。。。。
- 帮帮忙,高手进来帮我修改一下代码
- 为何我的“ASP.NET 快速入门”打开后是提示下载的信息?
- 发布.NET3.5网站
- 简单问题,怎么判断一个字符串string 类型,是否是数值或者可以转换为数值?
- 关于imagebuttom的问题
- 如何在页面加载page_load时,弹出一个js对话框:欢迎光临!
{
if (MyFile.Value.Trim().Length<1 || MyFile.PostedFile ==null)
{
page.RegisterStartupScript("key","<script>alert('请选择上传的文件!');</script>");
return ;
}
if (MyFile.PostedFile.ContentLength==0)
{
page.RegisterStartupScript("key","<script>alert('选择上传的文件无效!');</script>");
return ;
}
string filename = MyFile.PostedFile.FileName ;
FileInfo fs = new FileInfo(filename);
DirectoryInfo di = new DirectoryInfo("d:\\tmp\\");
FileInfo[] fi = di.GetFiles();
if (MyFile.PostedFile.ContentLength>10*1024*1024)
{
page.RegisterStartupScript("key","<script>alert('上传的文件大小不能超过10M!');</script>");
return ;
}
int filelength = filename.Length ;
int length = filename.LastIndexOf("\\");
filename = filename.Substring(length+1,filelength-length-1);
Byte[] size = new byte[MyFile.PostedFile.ContentLength ];
MyFile.PostedFile.SaveAs("d:\\tmp\\"+filename.ToString()+"");
}
//添加至部门人员表
public void ImportSales(System.Web.UI.HtmlControls.HtmlInputFile Myfile,System.Web.UI.Page page)
{
mDataPool.BeginTrans();
DataSet rs = new DataSet();
string filename = Myfile.Value;
int filelength = filename.Length ;
int length = filename.LastIndexOf("\\");
filename = filename.Substring(length+1,filelength-length-1);
string filepath="d:\\tmp\\"+filename+"";
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+
"Data Source=" + filepath + ";" +
"Extended Properties=\"Excel 8.0;\"");
OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [sheet1$]",conn);
OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
myData.Fill(rs);
if(!this.CheckExcelData(rs.Tables[0]))
{
page.RegisterStartupScript("alert","<script>javascript:alert('数据文件不符合要求的格式或没有包含要求的数据!')</script>");
return;
}
int sourceTotal = rs.Tables[0].Rows.Count ;
int okTotal = this.SyncTableData(this.GetTable(rs));
mDataPool.CommitTrans();
conn.Close();
page.RegisterStartupScript("alert","<script>javascript:alert('数据文件共有记录"+sourceTotal.ToString()+"条,成功导入" + okTotal.ToString() + "条记录.!')</script>");
}
private DataTable GetTable(DataSet rs)
{
DataSet syncDataSet = new DataSet();
syncDataSet.Tables.Add("ECC_SALES");
DataTable syncTab = syncDataSet.Tables[0];
syncTab.Columns.Add("SALERID");
syncTab.Columns.Add("SALER");
syncTab.Columns.Add("DEPTNAME");
syncTab.Columns.Add("O_DEPTNAME");
syncTab.Columns.Add("PUBLICVALUES");
syncTab.Columns.Add("PartDate");
int sourceTotal = rs.Tables[0].Rows.Count;
for(int i = 0; i < sourceTotal; ++i)
{
DataRow newRow = syncTab.NewRow();
if(this.AddRowData(rs.Tables[0].Rows[i],newRow))
{
syncTab.Rows.Add(newRow);
}
}
return syncTab;
}
private bool AddRowData(DataRow source,DataRow target)
{
if(source.IsNull("业务员编号")) return false;
if(source.IsNull("业务员名称")) return false;
if(source.IsNull("部门ID")) return false;
if (source.IsNull("原所在部门ID")) return false;
target["SALERID"] = source["业务员编号"];
target["SALER"] = source["业务员名称"];
target["DEPTNAME"] = source["部门ID"];
target["O_DEPTNAME"] = source["原所在部门ID"];
target["PUBLICVALUES"] = source["所占百分比"];
target["PartDate"] = source["加入时间"];
return true;
}
private bool CheckExcelData(DataTable tab)
{
bool result = true;
ArrayList colList = new ArrayList();
colList.Add("业务员编号");
colList.Add("业务员名称");
colList.Add("部门ID");
colList.Add("原所在部门ID");
colList.Add("所占百分比");
colList.Add("加入时间"); for(int i = 0; i < colList.Count; ++i)
{
string colName = colList[i].ToString();
bool found = false;
for(int k = 0; k < tab.Columns.Count; ++k)
{
if(tab.Columns[k].ColumnName == colName) found = true;
}
if(!found)
{
result = false;
break;
}
}
return result;
}
{
int cols = row.Table.Columns.Count;
for(int i = 0; i < cols; ++i)
{
if(row.Table.Columns[i].ColumnName == columnName) return true;
}
return false;
}
public int SyncTableData(System.Data.DataTable synTable)
{
int okNum = 0;
OleDbCommand mSynCmd=mDataPool.OleCmd ;
string fieldSql="",dataSql="",sql;
int cols=synTable.Columns.Count;
string typeName; for(int i=0;i<cols;++i)
{
System.Data.DataColumn dataColumn=synTable.Columns[i];
fieldSql+=","+dataColumn.ColumnName;
}
int rows=synTable.Rows.Count;
for(int i=0;i<rows;++i)
{
System.Data.DataRow dataRow=synTable.Rows[i];
dataSql="";
for(int j=0;j<cols;++j)
{
System.Data.DataColumn dataColumn=synTable.Columns[j];
typeName=dataColumn.DataType.Name;
switch(typeName)
{
case "Int16":
case "Int32":
case "Double":
if(dataRow.IsNull(j)) dataSql += ",NULL";
else dataSql += "," + dataRow[j].ToString();
break;
case "DateTime":
case "TimeSpan":
case "String":
default:
if(dataRow.IsNull(j)) dataSql += ",NULL";
else dataSql += ",'" + dataRow[j].ToString() + "'";
break;
}
}
sql="insert into "+synTable.TableName+" ("+fieldSql.Substring(1)+") values ("+dataSql.Substring(1)+")";
mSynCmd.CommandText=sql;
try
{
mSynCmd.ExecuteNonQuery();
++okNum;
}
catch(Exception e)
{
throw new Exception(sql + "##" + e.Message);
}
}
return okNum;
}
#region 预编译sql语句
public void ExecuteNonQueryPrepare(System.Data.DataTable synTable)
{
OleDbCommand mSynCmd=mDataPool.OleCmd;
string fieldSql="",dataSql="",sql;
int cols=synTable.Columns.Count;
string typeName; for(int i=0;i<cols;++i)
{
System.Data.DataColumn dataColumn=synTable.Columns[i];
fieldSql+=","+dataColumn.ColumnName;
}
int rows=synTable.Rows.Count;
for(int i=0;i<rows;++i)
{
System.Data.DataRow dataRow=synTable.Rows[i];
dataSql="";
for(int j=0;j<cols;++j)
{
System.Data.DataColumn dataColumn=synTable.Columns[j];
typeName=dataColumn.DataType.Name;
switch(typeName)
{
case "Int16":
case "Int32":
case "Double":
dataSql += "," + dataRow[j].ToString();
break;
case "DateTime":
case "TimeSpan":
case "String":
default:
dataSql += ",'" + dataRow[j].ToString() + "'";
break;
}
}
sql="insert into "+synTable.TableName+" ("+fieldSql.Substring(1)+") values ("+dataSql.Substring(1)+")";
mSynCmd.CommandText=sql;
try
{
mSynCmd.ExecuteNonQuery();
}
catch(Exception e)
{
throw new Exception(sql + "##" + e.Message);
}
}
}
#endregion
太长了,不过比较详细
1、上传EXCEL表(要限制表格的格式)
2、用ADO读上传的EXCEL表
3、用ADO写进SQL数据库
我想要的是2、3 “读取和写入”的。