问题源码如下:运行后提示错误009,也就是说,出错代码是comm.Fill(ds, "table1");这条。
求解决
public void getExcel(DataGridView dgv, System.Windows.Forms.Label lbl)
{
OpenFileDialog open = new OpenFileDialog();
open.Title = "请选择要导入的Excel文件";
open.Filter = "Excel文件(*.xls)|*.xls";
if (open.ShowDialog() == DialogResult.OK)
{
string fileName = open.FileName;
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strConn = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName + "; extended properties = 'Excel 8.0;HDR=NO;IMEX=1;'";//导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "select * from [sheet1$]";
OleDbDataAdapter comm = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();
try
{
comm.Fill(ds, "table1");
}
catch
{
MessageBox.Show("错误信息:009", "错误");
return;
}
comm.Fill(ds, "table1");
//根据DataGridView的列构造一个新的DataTable
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataTable dt2 = new System.Data.DataTable();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible)
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
dt.Columns.Add(dc);
DataColumn dc2 = new DataColumn();
dc2.ColumnName = dgvc.DataPropertyName;
dt2.Columns.Add(dc2);
if (dgvc.CellType == typeof(DataGridViewCheckBoxCell))
{
dc2.DataType = Type.GetType("System.Boolean");
}
}
}
//根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach (DataRow excelRow in ds.Tables[0].Rows)
{
int i = 0;
DataRow dr = dt.NewRow();
foreach (DataColumn dc in dt.Columns)
{
dr[dc] = excelRow[i];
i++;
}
dt.Rows.Add(dr);
}
//判断Excel的格式是否正确
int n = 0;
for (int j = 0; j < dt.Columns.Count; j++)
{
bool flag = false;
for (int k = n; k < dgv.ColumnCount; k++)
{
if (dgv.Columns[k].Visible) //隐藏的列
{
if (dgv.Columns[k].HeaderText.Trim().ToString() == dt.Rows[0][j].ToString())
{
if (dgv.Columns[k].CellType == typeof(DataGridViewCheckBoxCell))
{
//list.Add(j);
//num++;
}
flag = true;
n = k + 1;
break;
}
}
}
if (flag == false)
{
MessageBox.Show("导入的Excel的格式错误", "提示");
return;
}
}
//删除多余的行
int rowCount = (dt.Rows.Count) / 2;
for (int i = 0; i <= rowCount; i++)
{
dt.Rows.RemoveAt(0);
}
//处理Boolean类型的数据
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt2.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
try
{
dr[j] = dt.Rows[i][j];
}
catch
{
dr[j] = false;
}
}
dt2.Rows.Add(dr);
}
//导入到dataGridView
dgv.DataSource = dt2;
lbl.Text = dgv.RowCount.ToString();
}
else
{
MessageBox.Show("导入excel文件失败");
return;
}
MessageBox.Show("导入excel文件成功。");