编译没有错误,就是datagrid中什么显示都没有??? private void button1_Click(object sender, EventArgs e)
{
//打开一个文件选择框
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Excel文件";
ofd.FileName = "";
ofd.InitialDirectory =
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//为了获取特定的系统文件夹 ofd.Filter = "Excel文件(*.xls)|*.xls";
ofd.ValidateNames = true; //文件有效性验证ValidateNames,验证用户输入是否是一个有效的Windows文件名
ofd.CheckFileExists = true; //验证路径有效性
ofd.CheckPathExists = true; //验证文件有效性
string strName = string.Empty;
if (ofd.ShowDialog() == DialogResult.OK)
{
strName = ofd.FileName;
} if (strName == "")
{
MessageBox.Show("没有选择Excel文件!无法进行数据导入");
return;
}
//调用导入数据方法
EcxelToDataGridView(strName, this.dataGridView1);
} public void EcxelToDataGridView(string filePath,DataGridView dgv)
{
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath +
";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1"); //根据DataGridView的列构造一个新的DataTable
DataTable tb = new DataTable();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
//dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释,前提是DataGridView必须先绑定一个数据源那怕是空的DataTable
tb.Columns.Add(dc);
}
} //根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach (DataRow excelRow in ds.Tables[0].Rows)
{
int i = 0;
DataRow dr = tb.NewRow();
foreach (DataColumn dc in tb.Columns)
{
dr[dc] = excelRow[i];
i++;
}
tb.Rows.Add(dr);
}
//在DataGridView中显示导入的数据
dgv.DataSource = tb;
} private void WenBen_Load(object sender, EventArgs e)
{
// TODO: 这行代码将数据加载到表“aaaDataSet.a”中。您可以根据需要移动或移除它。
this.aTableAdapter.Fill(this.aaaDataSet.a); } }
}
{
//打开一个文件选择框
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Excel文件";
ofd.FileName = "";
ofd.InitialDirectory =
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//为了获取特定的系统文件夹 ofd.Filter = "Excel文件(*.xls)|*.xls";
ofd.ValidateNames = true; //文件有效性验证ValidateNames,验证用户输入是否是一个有效的Windows文件名
ofd.CheckFileExists = true; //验证路径有效性
ofd.CheckPathExists = true; //验证文件有效性
string strName = string.Empty;
if (ofd.ShowDialog() == DialogResult.OK)
{
strName = ofd.FileName;
} if (strName == "")
{
MessageBox.Show("没有选择Excel文件!无法进行数据导入");
return;
}
//调用导入数据方法
EcxelToDataGridView(strName, this.dataGridView1);
} public void EcxelToDataGridView(string filePath,DataGridView dgv)
{
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath +
";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1"); //根据DataGridView的列构造一个新的DataTable
DataTable tb = new DataTable();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
//dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释,前提是DataGridView必须先绑定一个数据源那怕是空的DataTable
tb.Columns.Add(dc);
}
} //根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach (DataRow excelRow in ds.Tables[0].Rows)
{
int i = 0;
DataRow dr = tb.NewRow();
foreach (DataColumn dc in tb.Columns)
{
dr[dc] = excelRow[i];
i++;
}
tb.Rows.Add(dr);
}
//在DataGridView中显示导入的数据
dgv.DataSource = tb;
} private void WenBen_Load(object sender, EventArgs e)
{
// TODO: 这行代码将数据加载到表“aaaDataSet.a”中。您可以根据需要移动或移除它。
this.aTableAdapter.Fill(this.aaaDataSet.a); } }
}
public static DataSet ImportExcelXML(Stream inputFileStream,
bool hasHeaders, bool autoDetectColumnType) {
XmlDocument doc = new XmlDocument();
doc.Load(new XmlTextReader(inputFileStream));
XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable); nsmgr.AddNamespace("o", "urn:schemas-microsoft-com:office:office");
nsmgr.AddNamespace("x", "urn:schemas-microsoft-com:office:excel");
nsmgr.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet"); DataSet ds = new DataSet(); foreach (XmlNode node in
doc.DocumentElement.SelectNodes("//ss:Worksheet", nsmgr)) {
DataTable dt = new DataTable(node.Attributes["ss:Name"].Value);
ds.Tables.Add(dt);
XmlNodeList rows = node.SelectNodes("ss:Table/ss:Row", nsmgr);
if (rows.Count > 0) { //*************************
//Add Columns To Table from header row
//*************************
List<ColumnType> columns = new List<ColumnType>();
int startIndex = 0;
if (hasHeaders) {
foreach (XmlNode data in rows[0].SelectNodes("ss:Cell/ss:Data", nsmgr)) {
columns.Add(new ColumnType(typeof(string)));//default to text
dt.Columns.Add(data.InnerText, typeof(string));
}
startIndex++;
}
//*************************
//Update Data-Types of columns if Auto-Detecting
//*************************
if (autoDetectColumnType && rows.Count > 0) {
XmlNodeList cells = rows[startIndex].SelectNodes("ss:Cell", nsmgr);
int actualCellIndex = 0;
for (int cellIndex = 0; cellIndex < cells.Count; cellIndex++) {
XmlNode cell = cells[cellIndex];
if (cell.Attributes["ss:Index"] != null)
actualCellIndex =
int.Parse(cell.Attributes["ss:Index"].Value) - 1; ColumnType autoDetectType =
getType(cell.SelectSingleNode("ss:Data", nsmgr)); if (actualCellIndex >= dt.Columns.Count) {
dt.Columns.Add("Column" +
actualCellIndex.ToString(), autoDetectType.type);
columns.Add(autoDetectType);
} else {
dt.Columns[actualCellIndex].DataType = autoDetectType.type;
columns[actualCellIndex] = autoDetectType;
} actualCellIndex++;
}
}
//*************************
//Load Data
//*************************
for (int i = startIndex; i < rows.Count; i++) {
DataRow row = dt.NewRow();
XmlNodeList cells = rows[i].SelectNodes("ss:Cell", nsmgr);
int actualCellIndex = 0;
for (int cellIndex = 0; cellIndex < cells.Count; cellIndex++) {
XmlNode cell = cells[cellIndex];
if (cell.Attributes["ss:Index"] != null)
actualCellIndex = int.Parse(cell.Attributes["ss:Index"].Value) - 1; XmlNode data = cell.SelectSingleNode("ss:Data", nsmgr); if (actualCellIndex >= dt.Columns.Count) {
for (int i = dt.Columns.Count; i < actualCellIndex; i++) {
dt.Columns.Add("Column" +
actualCellIndex.ToString(), typeof(string));
columns.Add(getDefaultType());
}
ColumnType autoDetectType =
getType(cell.SelectSingleNode("ss:Data", nsmgr));
dt.Columns.Add("Column" + actualCellIndex.ToString(),
typeof(string));
columns.Add(autoDetectType);
}
if (data != null)
row[actualCellIndex] = data.InnerText; actualCellIndex++;
} dt.Rows.Add(row);
}
}
}
return ds;
}
private void button1_Click(object sender, EventArgs e)
{
//打开一个文件选择框
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Excel文件";
ofd.FileName = "";
ofd.InitialDirectory =
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//为了获取特定的系统文件夹 ofd.Filter = "Excel文件(*.xls)|*.xls";
ofd.ValidateNames = true; //文件有效性验证ValidateNames,验证用户输入是否是一个有效的Windows文件名
ofd.CheckFileExists = true; //验证路径有效性
ofd.CheckPathExists = true; //验证文件有效性
string strName = string.Empty;
if (ofd.ShowDialog() == DialogResult.OK)
{
strName = ofd.FileName;
} if (strName == "")
{
MessageBox.Show("没有选择Excel文件!无法进行数据导入");
return;
}
//调用导入数据方法
DataSet dy=EcxelToDataGridView(strName);
this.dataGridView1.DataSource = dy.Tables[0].DefaultView;
} public DataSet EcxelToDataGridView(string filePath)
{
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1' '";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
return ds; }
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1"); this.dataGridView1.AutoGenerateColumns = false;
this.Column1.DataPropertyName = "F1";
this.Column2.DataPropertyName = "F2";
dgv.DataSource = ds.Tables[0]; //不知道你的excel和dataGridView1 是不是固定格式的,如果是固定的话就可以这样写
//定好没列的DataPropertyName 就可以了
改成myCommand = new OleDbDataAdapter(strExcel, conn);即可。
改成myCommand = new OleDbDataAdapter(strExcel, conn);即可。