Question

C# 我导入一个Excel文件到sql 其中我想获取Excel中第一个表名 并用这个表名作为sql表名

我有两个excel文件 其中一个可以读到表名并导入 另外一个就提示'_xlnm#_FilterDatabase$'不是一个有效的名称。

这里给出相关的部分代码：
private void button1_Click(object sender, EventArgs e)
{
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
fd.Title = "请选择文件";
fd.Filter = "EXCEL文件(*.xls)|*.xls|EXCEL文件(*.xlsx)|*.xlsx";
fd.FilterIndex = 2;
fd.Multiselect = false;
string connString = "server = (local); uid = sa; pwd = UE@fuhua; database = test";
if (fd.ShowDialog() == DialogResult.OK)
{
textBox1.Text = fd.FileName;
TransferData(fd.FileName, GetExcelFirstTableName(fd.FileName), connString);
}

}

public string GetExcelFirstTableName(string excelFileName)
{
string tableName = null;
if (File.Exists(excelFileName))
{
using (OleDbConnection conn = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFileName + ";" + "Extended Properties = Excel 12.0;"))
{
conn.Open();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
tableName += dt.Rows[0][2].ToString().Trim().TrimEnd('$');
}
}
return tableName;
}

public void TransferData(string excelFile,string sheetName,string connectionString)
{
DataSet ds = new DataSet();
try
{
//获取全部数据
string strConn = "Provider = Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";" + "Extended Properties = Excel 12.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);

Answer 1

#号应该是非法的，sqlserver有自带的存储过程可以实现导入excel不妨试试