c#，datagridview的内容保存至excel表的特定工作表（sheet）中
excel中有25张sheet，怎么将datagridview的内容保存到特定的sheet中。
目前只能读取excel特定sheet的内容到datagridview上，
我希望只用一个excel文件，从这个excel上的某张工作表中读取内容，反应在datagridview上，在datagridview上修改数据后，其数据保存在这个工作表中，不会影响这个excel文件中的其他工作表。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
namespace PCRTest
{
public partial class Form2 : Form
{
public string fileName = "d:\\PCR1500.xls";//excel路径
string Sheet = "SELECT * FROM [Sheet1$]";//excel中的第一张工作表
int ComboNum = 1;
public string frequencylow = "";
public string frequencyhigh = "";
public Form2()
{
InitializeComponent();
}
private void Form2_Load(object sender, EventArgs e)
{
comboBox1.DisplayMember = "Name";
comboBox1.ValueMember = "Value";
for (int i = 1; i <= 25; i++)
{
comboBox1.Items.Add(new ComboxItem(i.ToString(), ("SELECT * FROM [Sheet1$]".Remove(20)).Insert(20,i.ToString())+"$]"));
}
comboBox1.SelectedIndex = 0;
ShowExcel();
}
private void ShowExcel()//读取到datagridview
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + " ;Extended Properties='Excel 8.0;IMEX=1';";
OleDbConnection myConn = new OleDbConnection(strCon);
string sqlCon = Sheet;
OleDbDataAdapter myCommand = new OleDbDataAdapter(sqlCon, myConn);
DataTable dt = new DataTable();
myCommand.Fill(dt);
dataGridView1.DataSource = dt;
int RowNumber = 0;
for (RowNumber = 0; RowNumber < dt.Rows.Count; RowNumber++)
{
dataGridView1.RowHeadersWidth = 50;
dataGridView1.Rows[RowNumber].HeaderCell.Value = (RowNumber + 1).ToString();
}
dataGridView1.Columns[0].HeaderText = "Name";
dataGridView1.Columns[1].HeaderText = "Freq Low";
dataGridView1.Columns[2].HeaderText = "Freq High";
dataGridView1.Columns[3].HeaderText = "ATT";
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
Sheet = ((ComboxItem)comboBox1.SelectedItem).Value;
ShowExcel();
}
public class ComboxItem
{
public string _Name = "";
public string _Value = "";
public ComboxItem(string name, string value)
{
_Name = name;
_Value = value;
}
public string Name
{
get { return _Name; }
set { _Name = value; }
}
public string Value
{
get { return _Value; }
set { _Value = value; }
}
}
private void button1_Click(object sender, EventArgs e)//导出excel
{
//object missing = Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
excel.Application.Workbooks.Add(true);
excel.Visible = false;
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText.ToString() ;
}
for (int i = 0; i < dataGridView1.RowCount - 1; i++) //行
{
for (int j = 0; j < dataGridView1.ColumnCount; j++) //列
{
excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
}
}
excel.DisplayAlerts = false;
excel.AlertBeforeOverwriting = false;
excel.Application.Workbooks.Add(true).Save();
//保存excel文件
//excel.Save("d:\\PCR1500_1.xls");
//确保Excel进程关闭
excel.Quit();
//excel = null;
}
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
int rowindex = e.RowIndex;
if (e.RowIndex >= 0)
{
label1.Text = dataGridView1.Rows[rowindex].HeaderCell.Value.ToString();
frequencylow = dataGridView1.Rows[rowindex].Cells[1].Value.ToString();
frequencyhigh = dataGridView1.Rows[rowindex].Cells[2].Value.ToString();
}
}
}
}
以上可用。 c# excel datagridview sheet --------------------编程问答-------------------- 用
UPDATE [Sheet1$] SET ..... WHERE ....也可以 --------------------编程问答--------------------
额。。能具体点吗？这样的话怎么配合datagridview使用咧？
--------------------编程问答--------------------
把你的excel当做数据源好了。
--------------------编程问答--------------------
public DataTable LoadExcel(string pPath){
//Driver={Driver do Microsoft Excel(*.xls)} 这种连接写法不需要创建一个数据源DSN，DRIVERID表示驱动ID，Excel2003后都使用790，FIL表示Excel文件类型，Excel2007用excel 8.0，MaxBufferSize表示缓存大小，DBQ表示读取Excel的文件名（全路径）
//string strCon = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + pPath + ";Extended Properties=Excel 8.0";
string strCon = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", pPath);
OleDbConnection myConn = new OleDbConnection(strCon);
//string strCom = " SELECT * FROM [Sheet1$] ";
//string connString = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2024;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5;";
//connString += "DBQ=" + pPath;
//OdbcConnection conn = new OdbcConnection(connString);
//OdbcCommand cmd = new OdbcCommand();
//cmd.Connection = conn;
//获取Excel中第一个Sheet名称，作为查询时的表名
string sheetName = this.GetExcelSheetName(pPath);
string sql = "select * from [" + sheetName.Replace('.', '#') + "$]";
//cmd.CommandText = sql;
myConn.Open();
OleDbDataAdapter da = new OleDbDataAdapter(sql, myConn);
//OdbcDataAdapter da = new OdbcDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
da.Fill(ds);
return ds.Tables[0];
}
catch (Exception x)
{
ds = null;
ErrorFrame eFrame = new ErrorFrame("从Excel文件中获取数据时发生错误！");
eFrame.ShowDialog();
//throw new Exception("从Excel文件中获取数据时发生错误！");
return new System.Data.DataTable();
}
finally
{
myConn.Close();
//cmd.Dispose();
//cmd = null;
//da.Dispose();
//da = null;
//if (conn.State == ConnectionState.Open)
//{
// conn.Close();
//}
//conn = null;
}
} --------------------编程问答--------------------
不太可能吧。。。void怎么可能返回这种ds.Tables[0]呢。。。
还有GetExcelSheetName在哪个引用里的啊？ --------------------编程问答-------------------- 其实要求就是只操作和保存excel表中的单个sheet，而不会影响到其他sheet啊- - --------------------编程问答-------------------- oh ~no~
补充：.NET技术 , C#
