更改方法:
public void UpdateExcelFile(string filePath,string prjId, List<string> updateColNames,List<string> colValues)
string s = System.IO.Path.GetExtension(filePath);
string strConn = "";
if (s.ToLower() == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
}
else
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
//OleDbDataAdapter myCommand = null;
//DataSet ds = null;
StringBuilder sb = new StringBuilder();
sb.Append("update [sheet1$] set ");
sb.Append(updateColNames[0]);
sb.Append("='");
sb.Append(colValues[0]);
sb.Append("'");
for (int i = 1; i < updateColNames.Count; i++)
{
sb.Append(",");
sb.Append(updateColNames[i]);
sb.Append("='");
sb.Append(colValues[i]);
sb.Append("'");
}
sb.Append(" where ProjectID=" + prjId + "");
strExcel = sb.ToString();
OleDbCommand cmd = null;
cmd = new OleDbCommand(strExcel, conn);
int row = cmd.ExecuteNonQuery();
conn.Close();
}
插入方法:
public void InserDataExcelFile(string filePath,List<string> updateColNames, List<string> colValues)
{
string s = System.IO.Path.GetExtension(filePath);
string strConn = "";
if (s.ToLower() == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
}
else
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
StringBuilder sb = new StringBuilder();
sb.Append("insert into [sheet1$] ( ");
sb.Append(updateColNames[0]);
for (int i = 1; i < updateColNames.Count; i++)
{
sb.Append(",");
sb.Append(updateColNames[i]);
}
sb.Append(" ) values ( '");
sb.Append(colValues[0]);
sb.Append("'");
for (int i = 1; i < colValues.Count; i++)
{
sb.Append(",'");
sb.Append(colValues[i]);
sb.Append("'");
}
sb.Append(" )");
strExcel = sb.ToString();
OleDbCommand cmd = null;
cmd = new OleDbCommand(strExcel, conn);
cmd.ExecuteNonQuery();
conn.Close();
}
读方法:
public static System.Data.DataSet ExcelToDS(string filePath,string tableName)//读Excel方法
{
string s = System.IO.Path.GetExtension(filePath);
string strConn = "";
if (s.ToLower() == ".xls")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
}
else
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, tableName);
/*第二个参数 String是指定DataSet 里表的名字,例如 sqlDataAdapter.fill(DataSet,"学生表") 指定后,以后就可以这样调用这张表 DataSet.Tables["学生表"] 第二个参数可以不要的,如果不要第二个参数 String 那你调用这张表只能通过索引号,例如 DataSet.Tables[0] 如果填充的表比较多的话,用这个参数比较容易管理和调用。*/
return ds;
}
遍历dataset方法:
以MyDataset为例,其实与普通DataSet一样 foreach (DataTable dt in MyDataset.Tables) //MyDataSet是自已定义并已赋值的DataSet对象。 { foreach (DataRow dr in dt.Rows) ///遍历所有的行 { foreach (DataColumn dc in dt.Columns) //遍历所有的列 { Console.WriteLine(“{0}, {1}, {2}”, dt.TableName, dc.ColumnName, dr[dc]); //表名,列名,单元格数据 } } } //遍历DataSet中第一个表的多行多列 foreach(DataRow mDr in MyDataset.Tables[0].Rows ) { foreach(DataColumn mDc in MyDataset.Tables[0].Columns) { Console.WriteLine(mDr[mDc].ToString()); } }
利用ASpose的dll删除
using System;
using System.Collections.Generic;
using System.Text;
using Aspose.Cells;
namespace ConsoleApplication12
{
class Program
{
static void Main(string[] args)
{
if (DeleteRow("D:\\SS.XLS", "sheet1", "0"))
Console.WriteLine("true");
else
Console.WriteLine("false");
Console.Read();
}
public static bool DeleteRow(string ExcelPath, string SheetName, string LineSNumber)
{
bool isDelete = false;
try
{
Workbook workbook = new Workbook();
workbook.LoadData(ExcelPath);
Worksheet sheet = workbook.Worksheets[SheetName];
sheet.Cells.DeleteRow(Int32.Parse(LineSNumber));
workbook.Save(ExcelPath);
isDelete = true;
}
catch (Exception ex)
{
isDelete = false;
}
return isDelete;
}
}
}