OLEDB对EXCEL进行增删改查
Microsoft.Office.Interop.Excel.ApplicationClass 将DataTable中的数据插入EXCEL
/// <summary>
/// 将DataSet里所有数据导入Excel.
/// 需要添加COM: Microsoft Excel Object Library.
/// using Excel;
/// </summary>
/// <param name="filePath">Excel文件的路径</param>
/// <param name="ds">到导入Excel的数据源</param>
private void ExportToExcel(string filePath, DataSet ds)
{
object oMissing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
//打开EXCEL文件
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
// Excel.Workbook xlWorkbook=xlApp.Workbooks.只有Open属性，没有Write属性
Microsoft.Office.Interop.Excel.Worksheet xlWorksheet;
//循环所有DataTable
for (int i = 0; i < ds.Tables.Count; i++)
{
//添加入一个新的Sheel页
xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing, oMissing, 1, oMissing);
//以TableName作为新加的sheel页名
xlWorksheet.Name = ds.Tables[i].TableName;
//取出这个DataTable中的所有值，暂时存于stringBuffer中
StringBuilder stringBuffer =new StringBuilder();
for (int j = 0; j < ds.Tables[i].Rows.Count; j++)
{
for (int k = 0; k < ds.Tables[i].Columns.Count; k++)
{
stringBuffer.Append( ds.Tables[i].Rows[j][k].ToString());
if (k < ds.Tables[i].Columns.Count - 1)
stringBuffer.Append("\t");
}
stringBuffer.Append("\n");
}
//利用系统剪贴板
System.Windows.Forms.Clipboard.SetDataObject("");
//将stringBuffer放入剪贴板
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
//选中这个sheel页中的第一个单元格
((Excel.Range)xlWorksheet.Cells[1, 1]).Select();
//粘贴
xlWorksheet.Paste(oMissing, oMissing);
//清空系统剪贴板
System.Windows.Forms.Clipboard.SetDataObject("");
}
//保存并关闭这个工作薄
xlWorkbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
//释放...
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect();
}
}
OleDbConnection connection;
//打开数据库连接
public void OpenConnection(string xlsFils) {
if (!File.Exists(xlsFils))
{
MessageBox.Show("文件\"" + xlsFils + "\"不存在", "提示");
return;
}
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + xlsFils + ";Extended Properties=Excel 8.0";
connection = new OleDbConnection(conn);
connection.Open();
}
//查询数据
public DataTable Select()
{
DataTable dt = new DataTable();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, connection);
mycommand.Fill(dt);
return dt;
}
private void Form1_Load(object sender, EventArgs e)
{
string xlsFile = System.Windows.Forms.Application.StartupPath + "/" + "ExcelFiles/test.xls";
OpenConnection(xlsFile);
}
//插入数据
public void Insert()
{
string sql = string.Format("insert into [Sheet1$] values('{0}','{1}','{2}')", "陈太汉", "陈晓玲", "520");
OleDbCommand myCommand = new OleDbCommand(sql, connection);
myCommand.ExecuteNonQuery();
Select();
}
private void btAdd_Click(object sender, EventArgs e)
{
Insert();
}
//Excel不支持SQl语句的方式进行删除，可以用把每个字段的值设为空的方式进行删除
public void Delete()
{
string sql = string.Format("Update [Sheet1$] set col1=NULL,col2=NULL,col3=NULL where col1='{0}'", "陈太汉");
OleDbCommand myCommand = new OleDbCommand(sql, connection);
myCommand.ExecuteNonQuery();
Select();
}
private void btDelete_Click(object sender, EventArgs e)
{
Delete();
}
//更新数据
private new void Update() {
string sql = string.Format("update [Sheet1$] set col1='{0}' where col1='{1}'", "陈晓玲","陈太汉");
OleDbCommand myCommand = new OleDbCommand(sql, connection);
myCommand.ExecuteNonQuery();
Select();
}
private void btUpdate_Click(object sender, EventArgs e)
{
Update();
}
private void btSelect_Click(object sender, EventArgs e)
{
Select();
}
