|
先引入：using System.Runtime.InteropServices;
using System.Data.OracleClient;
读取代码
 DataTable mytable =new System.Data.DataTable();
DataSet DS = new System.Data.DataSet();
string path="",f_folder,f_name,strConn,sql;
try
  ...{
 path=File1.Value.ToString();
if( path =="")
  ...{
Response.Write("<script>alert('请选择Excel文件路径!')</script>");
return;
 }
f_folder = Server.MapPath("upfile\") ;
f_name = System.DateTime.Now.ToString() ;
f_name = f_name.Replace(" ", "");
f_name = f_name.Replace("-", "");
f_name = f_name.Replace(":", "");
f_name = f_folder + f_name + ".xls";
File1.PostedFile.SaveAs(f_name);
strConn = "Provider=Microsoft.Jet.OleDb.4.0;data source=" + f_name + ";Extended Properties=Excel 8.0";
sql = "Select * FROM [Sheet1$]";
System.Data.OleDb.OleDbConnection objConn= new System.Data.OleDb.OleDbConnection(strConn);
objConn.Open();
System.Data.OleDb.OleDbDataAdapter obj= new System.Data.OleDb.OleDbDataAdapter(sql, objConn);
obj.Fill(DS, "EXCELDATA");
obj.Dispose();
objConn.Close();
mytable = DS.Tables["EXCELDATA"];
int s=mytable.Rows.Count;
DataGrid1.DataSource = mytable.DefaultView;
DataGrid1.DataBind();
导出Excel：
string sData= DateTime.Today.ToString("yyyy-MM-dd");
Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8";
Response.AppendHeader("Content-Disposition","attachment;filename=WDExcel_"+sData+".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
Response.ContentType = "application/ms-excel";
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
Datagrid1.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
写入Excel文档：
public bool SaveFP2toExcel(string Path)
...{
try
...{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
cmd.Connection =conn;
//cmd.CommandText ="UPDATE [sheet1$] SET 姓名=′2005-01-01′ WHERE 工号=′日期′";
//cmd.ExecuteNonQuery ();
for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)
...{
if(fp2.Sheets [0].Cells[i,0].Text!="")
...{
cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES(′"+fp2.Sheets [0].Cells[i,0].Text+ "′,′"+
fp2.Sheets [0].Cells[i,1].Text+"′,′"+fp2.Sheets [0].Cells[i,2].Text+"′,′"+fp2.Sheets [0].Cells[i,3].Text+
"′,′"+fp2.Sheets [0].Cells[i,4].Text+"′,′"+fp2.Sheets [0].Cells[i,5].Text+"′)";
cmd.ExecuteNonQuery ();
}
}
conn.Close ();
return true;
}
catch(System.Data.OleDb.OleDbException ex)
...{
System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );
}
return false;
 }
|
