主题:求助关于用C#将数据从Excel导入SQL中的问题?
我在网上找到一段将数据从Excel导入SQL的代码,但运行时始终提示有错误,说当前上下文中不存在名称“fp2”,不知为什么?请大家指导一下
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
namespace WindowsApplication10
{
public partial class Form2 : Form
{
/// <summary>
/// 读取Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
/// <returns>返回一个数据集</returns>
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [Recovered_Sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds);
return ds;
}
/// <summary>
/// 写入Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
public bool SaveFP2toExcel(string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
//cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
//cmd.ExecuteNonQuery ();
for (int i = 0; i < fp2.Sheets[0].RowCount - 1; i++)
{
if (fp2.Sheets[0].Cells[i, 0].Text != "")
{
cmd.CommandText = "INSERT INTO [sheet1$] (省,区站号,站名,经度,纬度,水银槽海高) VALUES('" + fp2.Sheets[0].Cells[i, 0].Text + "','" +
fp2.Sheets[0].Cells[i, 1].Text + "','" + fp2.Sheets[0].Cells[i, 2].Text + "','" + fp2.Sheets[0].Cells[i, 3].Text +
"','" + fp2.Sheets[0].Cells[i, 4].Text + "','" + fp2.Sheets[0].Cells[i, 5].Text + "')";
cmd.ExecuteNonQuery();
}
}
conn.Close();
return true;
}
catch
{
Console.WriteLine("Caught exception #2.");
}
finally
{
Console.WriteLine("Executing finally block.");
}
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
namespace WindowsApplication10
{
public partial class Form2 : Form
{
/// <summary>
/// 读取Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
/// <returns>返回一个数据集</returns>
public DataSet ExcelToDS(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [Recovered_Sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds);
return ds;
}
/// <summary>
/// 写入Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
public bool SaveFP2toExcel(string Path)
{
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
//cmd.CommandText ="UPDATE [sheet1$] SET 姓名='2005-01-01' WHERE 工号='日期'";
//cmd.ExecuteNonQuery ();
for (int i = 0; i < fp2.Sheets[0].RowCount - 1; i++)
{
if (fp2.Sheets[0].Cells[i, 0].Text != "")
{
cmd.CommandText = "INSERT INTO [sheet1$] (省,区站号,站名,经度,纬度,水银槽海高) VALUES('" + fp2.Sheets[0].Cells[i, 0].Text + "','" +
fp2.Sheets[0].Cells[i, 1].Text + "','" + fp2.Sheets[0].Cells[i, 2].Text + "','" + fp2.Sheets[0].Cells[i, 3].Text +
"','" + fp2.Sheets[0].Cells[i, 4].Text + "','" + fp2.Sheets[0].Cells[i, 5].Text + "')";
cmd.ExecuteNonQuery();
}
}
conn.Close();
return true;
}
catch
{
Console.WriteLine("Caught exception #2.");
}
finally
{
Console.WriteLine("Executing finally block.");
}
}
}
}