using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.OleDb; using System.IO; namespace Utils { /// <summary> /// Excel 辅助类 /// </summary> public class ExcelHelper { public const string Excel2003 = ".xls"; public const string Excel2007 = ".xlsx"; public enum ConnectionMode { Read, Write } public static OleDbConnection GetConnection(string file, ConnectionMode mode) { if (File.Exists(file)) { var extension = Path.GetExtension(file); var connectionString = ""; if (ExcelHelper.Excel2003.Equals(extension, StringComparison.CurrentCultureIgnoreCase)) { connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"Excel 8.0;HDR=yes;{1}\";", file, (mode == ConnectionMode.Read ? "IMEX=1" : "IMEX=2") ); } else if (ExcelHelper.Excel2007.Equals(extension, StringComparison.CurrentCultureIgnoreCase)) { connectionString = String.Format("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=\"{0}\";Extended Properties=\"Excel 12.0;HDR=yes;{1}\";", file, (mode == ConnectionMode.Read ? "IMEX=1" : "")); } return new OleDbConnection(connectionString); } else { throw new FileNotFoundException(); } } /// <summary> ///ConnectionMode is read /// </summary> /// <param name="file"></param> /// <param name="commandText"></param> /// <param name="cmdParameters"></param> /// <returns></returns> public static DataTable ExecuteDatatable(string file, string commandText, params OleDbParameter[] cmdParameters) { return ExecuteDatatable(file, commandText, ConnectionMode.Read, cmdParameters); } public static DataTable ExecuteDatatable(string file, string commandText, ConnectionMode mode, params OleDbParameter[] cmdParameters) { using (OleDbConnection conn = GetConnection(file, mode)) using (OleDbCommand cmd = new OleDbCommand()) { cmd.Connection = conn; cmd.CommandText = commandText; if (cmdParameters != null && cmdParameters.Length > 0) { foreach (OleDbParameter param in cmdParameters) { cmd.Parameters.Add(param); } } OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt); return dt; } } /// <summary> /// ConnectionMode is write /// </summary> /// <param name="file"></param> /// <param name="commandText"></param> /// <param name="cmdParameters"></param> public static void ExecuteNonQuery(string file, string commandText, params OleDbParameter[] cmdParameters) { ExecuteNonQuery(file, commandText, ConnectionMode.Write, cmdParameters); } public static void ExecuteNonQuery(string file, string commandText, ConnectionMode mode, params OleDbParameter[] cmdParameters) { using (OleDbConnection conn = GetConnection(file, mode)) using (OleDbCommand cmd = new OleDbCommand()) { conn.Open(); cmd.Connection = conn; cmd.CommandText = commandText; if (cmdParameters != null && cmdParameters.Length > 0) { foreach (OleDbParameter param in cmdParameters) { cmd.Parameters.Add(param); } } cmd.ExecuteNonQuery(); conn.Close(); } } } }