oledb excel java_C# 通过 oledb 操作Excel实例代码

public string GetConnectionString()

{

Dictionary props = new Dictionary();

// XLSX - Excel 2007, 2010, 2012, 2013

props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";

props["Extended Properties"] = "Excel 12.0 XML";

props["Data Source"] = @"C:\tools\MyExcel.xlsx";

// XLS - Excel 2003 and Older

//props["Provider"] = "Microsoft.Jet.OLEDB.4.0";

//props["Extended Properties"] = "Excel 8.0";

//props["Data Source"] = "C:\\MyExcel.xls";

var sb = new StringBuilder();

foreach (KeyValuePair prop in props)

{

sb.Append(prop.Key);

sb.Append('=');

sb.Append(prop.Value);

sb.Append(';');

}

return sb.ToString();

}

public void WriteExcelFile()

{

string connectionString = GetConnectionString();

using (OleDbConnection conn = new OleDbConnection(connectionString))

{

conn.Open();

OleDbCommand cmd = new OleDbCommand();

cmd.Connection = conn;

cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";

cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";

cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(2, 'BBBB','2014-01-03');";

cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(3, 'CCCC','2014-01-03');";

cmd.ExecuteNonQuery();

cmd.CommandText = "UPDATE [table1] SET name = 'DDDD' WHERE id = 3;";

cmd.ExecuteNonQuery();

conn.Close();

}

}

public DataSet ReadExcelFile()

{

DataSet ds = new DataSet();

string connectionString = GetConnectionString();

using (OleDbConnection conn = new OleDbConnection(connectionString))

{

conn.Open();

OleDbCommand cmd = new OleDbCommand();

cmd.Connection = conn;

// Get all Sheets in Excel File

DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

// Loop through all Sheets to get data

foreach (DataRow dr in dtSheet.Rows)

{

string sheetName = dr["TABLE_NAME"].ToString();

if (!sheetName.EndsWith("$"))

continue;

// Get all rows from the Sheet

cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

DataTable dt = new DataTable();

dt.TableName = sheetName;

OleDbDataAdapter da = new OleDbDataAdapter(cmd);

da.Fill(dt);

ds.Tables.Add(dt);

}

cmd = null;

conn.Close();

}

return ds;

}