导入excel时遇到的问题 我的EXCEL数据如下: ID aaaaa bbbbb 12345 23456 当导入后,发现12345及23456 跟本没有导入 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 在 excel里数据前加上“'”就好了,但是这样用户体验不好,还有办法吗 这个应该是你后台程序的问题,跟Excel是没关系的。 DataTable Excel_UserInfo = new DataTable();string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileInfo.FullName + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\""; string strExcel = "select * from [sheet1$]"; using (OleDbDataAdapter adaptor = new OleDbDataAdapter(strExcel, strConn)) { DataSet ds = new DataSet(); adaptor.Fill(ds); Excel_UserInfo = ds.Tables[0]; }数字前面加单引号 private DataSet importExcelToDataSet(string FilePath,string strSheet) { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM ["+strSheet+"$]", strConn); DataSet myDataSet = new DataSet(); try { myCommand.Fill(myDataSet); } catch (Exception ex) { // throw new InvalidFormatException("该Excel文件的工作表的名字不正确," + ex.Message); MessageBox.Show("该Excel文件的工作表的名字不正确," + ex.Message); } return myDataSet; }返回DataSet 然后批量插入数据库就好了啊。 怎么判断 要导入 的是xls还是xlsx string excelConnectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; OleDbConnection olconnection = new OleDbConnection(excelConnectionStr); olconnection.Open(); OleDbCommand cmd = new OleDbCommand("select * from [" + DropDownList2.SelectedItem.Text.ToString().Trim() + "]", olconnection); OleDbDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { SqlConnection conn = BuildConnection.Buildsqlconnection(); SqlBulkCopy bulkcopy = new SqlBulkCopy(conn); for (int i = 0; i < CheckBoxList1.Items.Count; i++) { if (CheckBoxList1.Items[i].Selected == true) { bulkcopy.ColumnMappings.Add(CheckBoxList1.Items[i].Text.ToString().Trim(), CheckBoxList1.Items[i].Text.ToString().Trim()); } } cmd.Connection.Close(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); bulkcopy.DestinationTableName = tableName; bulkcopy.WriteToServer(dt); 怎么判断 要导入 的是xls还是xlsx -----------------------------------------截取后缀名就可以了! 页面没有刷新,没有从新加载。可以用ispostback 判断一下。 //根据路径打开一个excel文件并将数据填充到dataset中 string strConn = @"Provider = Microsoft.Ace.OLEDB.12.0; Data Source = " + filePath + "; Extended Properties = 'Excel 12.0;HDR = NO; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; strExcel = "select * from [sheet1$]"; DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); string tableName = schemaTable.Rows[0][2].ToString().Trim(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn); DataSet ds = new DataSet(); myCommand.Fill(ds, "table1"); //根据DataGridView的列构造一个DataTable DataTable td = ds.Tables[0]; try { //MessageBox.Show(td.ToString()); 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; //MessageBox.Show(dc.ToString()); td.Columns.Add(dc); } } //根据excel的行逐一对上面的构造的datatable的列进行赋值 for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++) { //int j = 0; DataRow dr = td.NewRow(); DataRow excelRow = ds.Tables[0].Rows[i]; foreach (DataColumn dc in td.Columns) { dr[dc] = excelRow[i]; //MessageBox.Show(dr[dc].ToString()); i++; } td.Rows.Add(dr); } //在datagridview中显示导入的数据 dgv.DataSource = td; } catch (Exception) { MessageBox.Show("该表已存在你即将导入的excel文件...,请点击清空按钮重新导入..."); return; } finally { conn.Close(); } 为何message类的show报错,请高手赐教! 关于产品按照指定数字顺序排列的问题 文件上传下载问题? 怎么验证一个字符是不是二十六个字母之一呢? 学习.net时遇到几个概念不明白,请指教 怎样让DataGrid不显示头? 今天被这个问题拖了一天,急救呀~~~~~~~~ 大哥们帮忙 如何用vb.net操作Excel文件? 请问用C#如何动态替换一个embed标签?? authentication mode=Forms的问题 下拉框读取数据
这个应该是你后台程序的问题,跟Excel是没关系的。
string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileInfo.FullName + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"";
string strExcel = "select * from [sheet1$]"; using (OleDbDataAdapter adaptor = new OleDbDataAdapter(strExcel, strConn))
{
DataSet ds = new DataSet();
adaptor.Fill(ds);
Excel_UserInfo = ds.Tables[0];
}数字前面加单引号
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath +
";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM ["+strSheet+"$]", strConn);
DataSet myDataSet = new DataSet();
try
{
myCommand.Fill(myDataSet);
}
catch (Exception ex)
{
// throw new InvalidFormatException("该Excel文件的工作表的名字不正确," + ex.Message);
MessageBox.Show("该Excel文件的工作表的名字不正确," + ex.Message);
}
return myDataSet;
}
返回DataSet 然后批量插入数据库就好了啊。
string excelConnectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
OleDbConnection olconnection = new OleDbConnection(excelConnectionStr);
olconnection.Open();
OleDbCommand cmd = new OleDbCommand("select * from [" + DropDownList2.SelectedItem.Text.ToString().Trim() + "]", olconnection);
OleDbDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
SqlConnection conn = BuildConnection.Buildsqlconnection();
SqlBulkCopy bulkcopy = new SqlBulkCopy(conn);
for (int i = 0; i < CheckBoxList1.Items.Count; i++)
{
if (CheckBoxList1.Items[i].Selected == true)
{
bulkcopy.ColumnMappings.Add(CheckBoxList1.Items[i].Text.ToString().Trim(), CheckBoxList1.Items[i].Text.ToString().Trim());
}
}
cmd.Connection.Close();
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
bulkcopy.DestinationTableName = tableName;
bulkcopy.WriteToServer(dt);
-----------------------------------------
截取后缀名就可以了!
string strConn = @"Provider = Microsoft.Ace.OLEDB.12.0; Data Source = " + filePath + "; Extended Properties = 'Excel 12.0;HDR = NO; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
strExcel = "select * from [sheet1$]";
DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, "table1");
//根据DataGridView的列构造一个DataTable
DataTable td = ds.Tables[0];
try
{
//MessageBox.Show(td.ToString());
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;
//MessageBox.Show(dc.ToString());
td.Columns.Add(dc);
}
} //根据excel的行逐一对上面的构造的datatable的列进行赋值 for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
{
//int j = 0;
DataRow dr = td.NewRow();
DataRow excelRow = ds.Tables[0].Rows[i];
foreach (DataColumn dc in td.Columns)
{
dr[dc] = excelRow[i];
//MessageBox.Show(dr[dc].ToString());
i++;
}
td.Rows.Add(dr);
}
//在datagridview中显示导入的数据
dgv.DataSource = td;
}
catch (Exception)
{
MessageBox.Show("该表已存在你即将导入的excel文件...,请点击清空按钮重新导入...");
return;
}
finally
{
conn.Close();
}