第一步: 获取计算机中的一个Excel文件利用File Field控件和Javascrip脚本
File Field控件ID:DocUpload
<
INPUT id
=
"
DocUpload
"
contentEditable
=
"
false
"
style
=
"
Z-INDEX: 101; LEFT: 32px; WIDTH: 624px; POSITION: absolute; TOP: 24px; HEIGHT: 20px
"
type = " file " onchange = " javascrip:HttpFileInputVale() " size = " 84 " name = " DocUpload " runat = " server " >
type = " file " onchange = " javascrip:HttpFileInputVale() " size = " 84 " name = " DocUpload " runat = " server " >
JavaScrip脚本:
<
script language
=
"
javascript
"
>
function HttpFileInputVale()
... {
var strbty;
var strArry;
var intLength;
var strDocName;
var strArr;
bty = document.all("DocUpload").value;
strArry=bty.split("/");
intLength=strArry.length;
strDocName = strArry[intLength - 1];
strArr = strDocName.split(".");
document.all("txtDocName").value = bty;
document.all("txtSheet").value = strArr[0];
}
</ script >
function HttpFileInputVale()
... {
var strbty;
var strArry;
var intLength;
var strDocName;
var strArr;
bty = document.all("DocUpload").value;
strArry=bty.split("/");
intLength=strArry.length;
strDocName = strArry[intLength - 1];
strArr = strDocName.split(".");
document.all("txtDocName").value = bty;
document.all("txtSheet").value = strArr[0];
}
</ script >
txtDocName 用于存放文件路径,txtSheet用于存放文件名称
第二步:导出Excel文件中一个工作表(Sheet)
ddlSheet用于存放Sheet
导出工作表
#region 导出工作表
/**//// <summary>
/// 功能:导出工作表
/// 日期:2006-11-22
/// 作者:杨义贤
/// </summary>
/// <param name="excelFile">文件路径</param>
/// <returns></returns>
private string[] GetExcelSheetNames(string strexcelFile)
...{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
...{
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strexcelFile + ";Extended Properties=Excel 8.0;";
objConn = new OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null)
...{
return null;
}
string[] excelSheets = new string[dt.Rows.Count];
int i = 0;
foreach(DataRow row in dt.Rows)
...{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
规矩的工作表#region 规矩的工作表
ListItem objListItem;
objListItem = new ListItem();
objListItem.Text = excelSheets[j].Replace("$","").Trim();
objListItem.Value = j.ToString();
ddlSheet.Items.Add(objListItem);
objListItem = null;
#endregion
return excelSheets;
}
catch(Exception ex)
...{
return null;
//throw ex;
}
finally
...{
if(objConn != null)
...{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
...{
dt.Dispose();
}
}
}
#endregion
/**//// <summary>
/// 功能:导出工作表
/// 日期:2006-11-22
/// 作者:杨义贤
/// </summary>
/// <param name="excelFile">文件路径</param>
/// <returns></returns>
private string[] GetExcelSheetNames(string strexcelFile)
...{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
...{
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strexcelFile + ";Extended Properties=Excel 8.0;";
objConn = new OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if(dt == null)
...{
return null;
}
string[] excelSheets = new string[dt.Rows.Count];
int i = 0;
foreach(DataRow row in dt.Rows)
...{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
规矩的工作表#region 规矩的工作表
ListItem objListItem;
objListItem = new ListItem();
objListItem.Text = excelSheets[j].Replace("$","").Trim();
objListItem.Value = j.ToString();
ddlSheet.Items.Add(objListItem);
objListItem = null;
#endregion
return excelSheets;
}
catch(Exception ex)
...{
return null;
//throw ex;
}
finally
...{
if(objConn != null)
...{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
...{
dt.Dispose();
}
}
}
#endregion
private
void
Button1_Click(
object
sender, System.EventArgs e)
... {
string strDataSource;
strDataSource = txtDocName.Text.ToString();
GetExcelSheetNames(strDataSource);
}
... {
string strDataSource;
strDataSource = txtDocName.Text.ToString();
GetExcelSheetNames(strDataSource);
}
第三步:将Excel数据导入DataGrid中
将Excel中数据放到DataGrid中
#region 将Excel中数据放到DataGrid中
/**//// <summary>
/// 功能:将Excel中数据放到DataGrid中
/// 日期:2006-11-21
/// 作者:杨义贤
/// </summary>
private void ExcelToDataGridShow(string strexcelFile)
...{
string strDataSource = strexcelFile;
// Excel文件连接字符串
string connString;
connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strDataSource+";"+"Extended Properties=Excel 8.0";
// 定义OleDb连接
OleDbConnection conn = new OleDbConnection(connString);
// 定义OleDb命令
string strTxt;//您选择的工作表
strTxt = ddlSheet.SelectedItem.Text.ToString();
string strCom;
//strCom = "SELECT * FROM ["+strTxt+"$A3:O78]";//取出一个矩形区域的数据
strCom = "SELECT * FROM ["+strTxt+"$]";
OleDbCommand comm = new OleDbCommand(strCom,conn);
// 定义OleDbDataAdapter
OleDbDataAdapter adapter = new OleDbDataAdapter(comm);
DataSet ds = new DataSet();
// 向DataSet中填充数据
adapter.Fill(ds);
//行数
int intItem = ds.Tables[0].Rows.Count;
int intColumns = ds.Tables[0].Columns.Count;
//行
Label2.Text = "行数:"+intItem;
//列
Label3.Text = "列数:"+intColumns;
// for(int i=0; i < intItem; i++)//行
// {
// ListItem objListItem = new ListItem();
// objListItem.Text = "A"+i.ToString();
// objListItem.Value = i.ToString();
// ddlRow.Items.Add(objListItem);
// objListItem = null;
// }
//设置数据源
DataGrid1.DataSource = ds;
//数据绑定
DataGrid1.DataBind();
}
#endregion
/**//// <summary>
/// 功能:将Excel中数据放到DataGrid中
/// 日期:2006-11-21
/// 作者:杨义贤
/// </summary>
private void ExcelToDataGridShow(string strexcelFile)
...{
string strDataSource = strexcelFile;
// Excel文件连接字符串
string connString;
connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strDataSource+";"+"Extended Properties=Excel 8.0";
// 定义OleDb连接
OleDbConnection conn = new OleDbConnection(connString);
// 定义OleDb命令
string strTxt;//您选择的工作表
strTxt = ddlSheet.SelectedItem.Text.ToString();
string strCom;
//strCom = "SELECT * FROM ["+strTxt+"$A3:O78]";//取出一个矩形区域的数据
strCom = "SELECT * FROM ["+strTxt+"$]";
OleDbCommand comm = new OleDbCommand(strCom,conn);
// 定义OleDbDataAdapter
OleDbDataAdapter adapter = new OleDbDataAdapter(comm);
DataSet ds = new DataSet();
// 向DataSet中填充数据
adapter.Fill(ds);
//行数
int intItem = ds.Tables[0].Rows.Count;
int intColumns = ds.Tables[0].Columns.Count;
//行
Label2.Text = "行数:"+intItem;
//列
Label3.Text = "列数:"+intColumns;
// for(int i=0; i < intItem; i++)//行
// {
// ListItem objListItem = new ListItem();
// objListItem.Text = "A"+i.ToString();
// objListItem.Value = i.ToString();
// ddlRow.Items.Add(objListItem);
// objListItem = null;
// }
//设置数据源
DataGrid1.DataSource = ds;
//数据绑定
DataGrid1.DataBind();
}
#endregion
private
void
btnDataGrid_Click(
object
sender, System.EventArgs e)
... {
string strDataSource;
strDataSource = txtDocName.Text.ToString();//文件路径
ExcelToDataGridShow(strDataSource);
}
... {
string strDataSource;
strDataSource = txtDocName.Text.ToString();//文件路径
ExcelToDataGridShow(strDataSource);
}