/// <summary>
/// 方法名称:ExcelInportData
/// 内容摘要:读取EXCEL表格中的数据
/// 完成日期:2006-09-08
/// </summary>
/// <param name="source">读取EXCEL文件的路径</param>
/// <returns>数据集</returns>
private DataSet ExcelInportData(string source)
{
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + source + ";"
+ "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
string query = "SELECT * FROM [Sheet1$]";
// 创建SqlCommand,SqlDataAdapter对象
OleDbCommand oleCommand = new OleDbCommand(query,new OleDbConnection(connStr));
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
DataSet myDataSet = new DataSet();
oleAdapter.Fill(myDataSet,"[Sheet1$]");
return myDataSet;
}
请高手帮忙,为什么我导入后的数据是整个Excel的Sheet里的数据呢?行和列都会多出一些空行,请问这是为什么?
/// 方法名称:ExcelInportData
/// 内容摘要:读取EXCEL表格中的数据
/// 完成日期:2006-09-08
/// </summary>
/// <param name="source">读取EXCEL文件的路径</param>
/// <returns>数据集</returns>
private DataSet ExcelInportData(string source)
{
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " + source + ";"
+ "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
string query = "SELECT * FROM [Sheet1$]";
// 创建SqlCommand,SqlDataAdapter对象
OleDbCommand oleCommand = new OleDbCommand(query,new OleDbConnection(connStr));
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
DataSet myDataSet = new DataSet();
oleAdapter.Fill(myDataSet,"[Sheet1$]");
return myDataSet;
}
请高手帮忙,为什么我导入后的数据是整个Excel的Sheet里的数据呢?行和列都会多出一些空行,请问这是为什么?
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient ;
using System.Text;
using System.Configuration;namespace DataGrid_import_WordExcel
{ public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button BtnImportWord;
protected System.Web.UI.WebControls.Button Btn_Import_Excel;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
public DataRow dr;
private DataSet myDS =new DataSet();private void Page_Load(object sender, System.EventArgs e)
{
//CreateDataSet();
Data_Load();
if(!IsPostBack)
{
//DataBind();
}
} #region Web 敦极扢數?汜傖腔測鎢
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
} private void InitializeComponent()
{
this.Btn_Import_Excel.Click += new System.EventHandler(this.Btn_Import_Excel_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion private void ExportDataGrid(string FileType, string FileName) //植DataGrid絳堤
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); Response.AppendHeader("Content-Disposition", "attachment;filename=" +HttpUtility.UrlEncode(FileName,Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState =false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw =new HtmlTextWriter(tw);
DataGrid1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
private void Btn_Import_Excel_Click(object sender, System.EventArgs e)
{
ExportDataGrid("application/ms-excel", "Excel.xls"); //絳善Excel
} private void Data_Load()
{ SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["data"]);
SqlCommand cmd=new SqlCommand("excel",myConnection);
cmd.CommandType=CommandType.StoredProcedure; if(Session["status"]!="")
{
cmd.Parameters.Add("@sql1",SqlDbType.NVarChar,50);
cmd.Parameters["@sql1"].Value = Session["status"];
}
else
{
cmd.Parameters.Add("@sql1",SqlDbType.NVarChar,50);
cmd.Parameters["@sql1"].Value = "";
} if(Session["date11"]!="")
{
cmd.Parameters.Add("@sql2",SqlDbType.NVarChar,50);
cmd.Parameters["@sql2"].Value = Session["date11"];
}
else
{
cmd.Parameters.Add("@sql2",SqlDbType.NVarChar,50);
cmd.Parameters["@sql2"].Value = "";
} if(Session["date12"]!="")
{
cmd.Parameters.Add("@sql3",SqlDbType.NVarChar,50);
cmd.Parameters["@sql3"].Value = Session["date12"];
}
else
{
cmd.Parameters.Add("@sql3",SqlDbType.NVarChar,50);
cmd.Parameters["@sql3"].Value = "";
} myConnection.Open(); DataSet ds=new DataSet();
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=cmd;
da.Fill(ds);
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
} DataView CreateDataSource()
{
string nowDSN=ConfigurationSettings.AppSettings["data"];
SqlConnection myConnection=new SqlConnection(nowDSN); SqlCommand cmd=new SqlCommand("excel",myConnection);
cmd.CommandType=CommandType.StoredProcedure; if(Session["status"]!="")
{
cmd.Parameters.Add("@sql1",SqlDbType.NVarChar,50);
cmd.Parameters["@sql1"].Value = Session["status"];
}
else
{
cmd.Parameters.Add("@sql1",SqlDbType.NVarChar,50);
cmd.Parameters["@sql1"].Value = "";
} DataSet ds=new DataSet();
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=cmd;
da.Fill(ds);
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
return ds.Tables["pur"].DefaultView;
myConnection.Close();
Page.DataBind();
}
void DataBind()
{
DataView source=CreateDataSource();
if(!IsPostBack)
{
}
DataGrid1.DataSource = source;
DataGrid1.DataBind();
} private void CreateDataSet()
{
DataSet myDataSet = new DataSet("aNewDataSet");
DataTable table1 = MakeTable("采購單號","供應商","料號", "料品名稱規格","單位","數量", "驗收總數","單价","交貨日期","訂單號碼");
myDataSet.Tables.Add(table1);
DataGrid1.DataSource=myDataSet;
DataGrid1.DataBind();
} private DataTable MakeTable(String c1Name,String c2Name,String c3Name,String c4Name,String c5Name,String c6Name,String c7Name,String c8Name,String c9Name,String c10Name)
{
int i;
DataTable myTable=new DataTable();
DataColumn myColumn; myColumn = new DataColumn(c1Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c2Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c3Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c4Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c5Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c6Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c7Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c8Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c9Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c10Name,typeof(string));
myTable.Columns.Add(myColumn); DataRow Dr; string mySQLstr=Session["str3"].ToString();
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["data"]);
SqlDataAdapter myDataAdapter = new SqlDataAdapter(mySQLstr,myConnection);
myConnection.Open();
DataSet ds=new DataSet();
myDataAdapter.Fill(ds,"pur");
dr = ds.Tables["pur"].Rows[0];
int num=ds.Tables[0].Rows.Count;for(i=1;i<num;i++)
{
Dr=myTable.NewRow(); Dr[0]=dr["pono"].ToString();
Dr[1]=dr["part"].ToString();
Dr[2]=dr["specification"].ToString();
Dr[3]=dr["unit"].ToString();
Dr[4]=dr["qty"].ToString();
Dr[5]=dr["recqty"].ToString();
Dr[6]=dr["unprc"].ToString();
Dr[7]=dr["precdate"].ToString();
Dr[8]=dr["orderno"].ToString();
Dr[9]=dr["indate"].ToString(); myTable.Rows.Add(Dr);
}
return myTable;
}
private void BtnImportWord_Click(object sender, System.EventArgs e)
{
ExportDataGrid("application/ms-word", "Word.doc");
} private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
e.Item.Cells[0].Attributes.Add("style","vnd.ms-excel.numberformat:@");
//e.Item.Cells[3].Attributes.Add("style","vnd.ms-excel.numberformat:¥#,###.00");
}
}}
}