导入excel时遇到的问题

[zzc_king]

我的EXCEL数据如下:

ID

aaaaa
bbbbb
12345
23456

当导入后,发现12345及23456 跟本没有导入

[zzc_king]

在 excel里数据前加上“'”就好了，但是这样用户体验不好，还有办法吗

[coxie007]

[Quote=引用 1 楼 zzc_king 的回复:]
在 excel里数据前加上“'”就好了，但是这样用户体验不好，还有办法吗
[/Quote]

这个应该是你后台程序的问题，跟Excel是没关系的。

[zzc_king]

那 怎么写 能行呢

[子夜__]

 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];
                }

数字前面加单引号

[wubing1111]

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 然后批量插入数据库就好了啊。

[zzc_king]

怎么判断 要导入 的是xls还是xlsx

[YnSky]

 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);

[YnSky]

怎么判断 要导入 的是xls还是xlsx
-----------------------------------------
截取后缀名就可以了!

[zzc_king]

哦了， 最后一个消问题 我F5调试 怎么没反应啊，第一次加载也不行，断点就没停
怎么回事

[tzweilai]

页面没有刷新，没有从新加载。可以用ispostback 判断一下。

[zzc_king]

还是不行

[decher]

不可能讀取不到吧，上面的都讀取出來了。

[yrf741676206]

//根据路径打开一个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();
}