Excel的连接中,由两个值需要注意。
首先是HDR值,该值指示是否将表中的第一行有效(第一个行数据不为空的行)数据当作标题列处理。如果选择是YES,那么通过C#读取出来的数据表中,表的列标题则是对应的第一行有效数据;否则,将所有数据都当作数据处理,此时以F1、F2……Fn为列标题。默认的是YES,见后续的使用。
其次是IMEX模式, IMEX 有三种模式,不同的模式代表著不同的读写行为:
- 0 is Export mode:为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。 ---输出模式;
- 1 is Import mode:为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。---输入模式,始终将“互混”数据列作为文本读取;
- 2 is Linked mode (full update capabilities):为“连結模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。---链接模式(完全更新能力,效率不高)
IMEX=1的作用是,当读取Excel中每个单元格的值到DataTable中的时候,不管其在Excel单元格时候是什么数据类型,赋值到DataTable中都强制转化为字符串类型。
在没有IMEX=1这个属性的时候,默认的是根据Excel中对应Column的数据类型来决定DataTable中Column的数据类型。这种情况在Excel中某一列的数据类型都是一致的情况下没有问题,是什么类型,就会在DataTable中的对应列设置相应的类型。但是如果Excel中这一列的类型混乱的话,比如说既包括数值型又有字符串型,在运行时创建DataTable的时候,会去先判断Excel中这一列哪种类型的数据占主体,然后给DataTable的列设置为这种类型。比如说,如果一列中既有整数型又有字符型,而整数型单元格占主体,这时DataTable中的列就是整数型。
这时又出现另外一个问题,当要把值写入到DataTable的时候,如果该单元格符合DataTable中要求的类型,就会写入,如果不符合的话,系统会去强制转换。比如,如果Excel中是字符串的5,而该单元格所在的列整数型占主体,DataTable中这一列是数值型,这时,系统会把字符串的5强制转为数值型的5然后赋给DataTable相应的地方。但是,此时,如果转换有问题的话,比如,此列中有一单元格中是“NO5”,这时强制转换就会有问题,系统就会给DataTable相应的地方赋值DBNull,现在如果你用DataGridView来显示那个DataTable的时候,这个地方显示出来就是一个空白的格,但要注意的是,并不是DataTable中的这一行这一列是null,而是DBNull.其实,我觉得,跟null的作用是一样的,反正在DataGridView中是不会显示出来。只是我们在写程序如果需要对DataTable的null元素筛选的话,需要注意这个问题。
如果Excel中,某一行字符串类型占主体的话,那么DataTable中这一列就会设置为字符串型,而且任何类型都能顺利转换成字符串类型,所以,Excel的类会完整的显示出来,不管这一列中的字符串类型的单元格,还是整数型的单元格,都能完整的显示出来。这是一很特别的地方。但这仅仅是一个特例。
所以,如果为了处理的时候数据类型的一致性,如果Excel中数据类型混乱的话,可以使用IMEX=1使DataTable中的所有列都转为字符型。
CSV的文件,它的连接字符串会读取该文件所在的文件夹下所有的文本文件数据。
ExcelReader代码:
/// <summary>
/// 连接Excel的模式
/// </summary>
public enum IMEX
{
/// <summary>
/// 汇出(输出)模式
/// </summary>
Export = 0,
/// <summary>
/// 汇入(输入)模式
/// </summary>
Import = 1,
/// <summary>
/// 链接模式(完全更新能力)
/// </summary>
Linked = 2,
}
public sealed class ExcelReader
{
/// <summary>
/// 是否将第一行数据作为标题列。如果选择no, 则将第一行数据作为数据读取,则默认F1~开始为标题
/// </summary>
public bool IsFirstRowAsColumnNames { get; set; } = true;
/// <summary>
/// 连接Excel的模式
/// </summary>
public IMEX IMEX { get; set; } = IMEX.Import;
/// <summary>
/// 文件的绝对路径
/// </summary>
string filePath = string.Empty;
/// <summary>
/// 构造对象
/// </summary>
/// <param name="hdr">第一行的读取模式:默认是Yes</param>
/// <param name="imex"></param>
public ExcelReader(IMEX imex = IMEX.Import)
{
IMEX = imex;
}
FileType fileType = FileType.noset;
/// <summary>
/// 获取连接字符串
/// </summary>
/// <param name="fileFullPath">文件的绝对路径</param>
/// <returns>连接字符串</returns>
public string GetConnectString(string fileFullPath)
{
fileType = FileType.noset;
if (!File.Exists(fileFullPath))//判断文件是否存在
{
throw new FileNotFoundException(fileFullPath);
}
string hdr = IsFirstRowAsColumnNames ? "YES" : "NO";
string connString = string.Empty;
filePath = fileFullPath;
switch (Path.GetExtension(filePath))
{
case ".xls":
connString = $"Provider=;Data Source={ filePath }; Extended Properties='Excel 8.0;HDR={hdr};IMEX={(int)IMEX};'";
fileType = FileType.xls;
break;
case ".xlsx":
connString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={ filePath };Extended Properties='Excel 12.0;HDR={hdr};IMEX={(int)IMEX};'";
fileType = FileType.xlsx;
break;
case ".csv"://filePath.Remove(filePath.LastIndexOf("\\") + 1)
connString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={Path.GetDirectoryName(filePath)};Extended Properties='Text;FMT=Delimited;HDR={hdr};FMT=Delimited;'";
fileType = FileType.csv;
break;
}
return connString;
}
/// <summary>
/// 打开连接
/// </summary>
/// <param name="fileFullPath">文件的绝对路径</param>
/// <returns>连接对象</returns>
public OleDbConnection GetConnection(string fileFullPath)
{
string connectString = GetConnectString(fileFullPath);//获取文件连接字符串
if (fileType == FileType.noset)
{
throw new ArgumentException("Incorrect file type.");
}
OleDbConnection oleDb = new OleDbConnection();
try
{
oleDb.ConnectionString = connectString;
}
catch (Exception ex)
{
oleDb.Close();
throw ex;
}
return oleDb;
}
/// <summary>
/// 获取所有表单的名称;注意,表单名顺序不一定是Excel中显示的顺序,是添加表时的先后顺序
/// </summary>
/// <param name="fileFullName">文件全名</param>
/// <returns>表单名称</returns>
public List<string> GetSheetsName(string fileFullName)
{
OleDbConnection connection = GetConnection(fileFullName);//获取连接对象
List<string> lstSheets = GetSheetsName(connection);
return lstSheets;
}
/// <summary>
/// 获取所有表单的名称
/// </summary>
/// <param name="connection">连接对象</param>
/// <returns>表单名称</returns>
public List<string> GetSheetsName(OleDbConnection connection)
{
List<string> lstSheets = new List<string>();
try
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//获取表单
for (int i = 0; i < dt.Rows.Count; i++)
{
lstSheets.Add(dt.Rows[i]["Table_Name"].ToString());
}
}
finally
{
connection.Close();
}
return lstSheets;
}
/// <summary>
/// 获取文件内的所有Table数据集合
/// </summary>
/// <param name="fileFullName">文件全名</param>
/// <returns>数据集合</returns>
public DataSet GetDataSet(string fileFullName)
{
OleDbConnection connection = GetConnection(fileFullName);//获取连接对象
return GetDataSet(connection);
}
/// <summary>
/// 获取文件内的所有Table数据集合
/// </summary>
/// <param name="connection">连接对象</param>
/// <returns>数据集合</returns>
public DataSet GetDataSet(OleDbConnection connection)
{
DataSet ds = new DataSet();
try
{
List<string> lstSheets = GetSheetsName(connection);
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
OleDbDataAdapter adapter = new OleDbDataAdapter(connection.CreateCommand());//创建读取数据
for (int i = 0; i < lstSheets.Count; i++)
{
adapter.SelectCommand.CommandText = string.Format("SELECT * FROM [{0}]", lstSheets[i].Trim('/'));//查询字符串
adapter.Fill(ds);//填充数据
ds.Tables[i].TableName = lstSheets[i];//赋值表名
}
adapter.Dispose();
}
finally
{
connection.Close();
}
return ds;
}
/// <summary>
/// 获取文件内的所有Table数据集合
/// </summary>
/// <param name="fileFullName">文件全名</param>
/// <param name="tableName">表名</param>
/// <returns>数据集合</returns>
public DataTable GetDataTable(string fileFullName, string tableName)
{
OleDbConnection connection = GetConnection(fileFullName);//获取连接对象
return GetDataTable(connection, tableName);
}
/// <summary>
/// 获取文件内的所有Table数据集合
/// </summary>
/// <param name="connection">连接对象</param>
/// <param name="tableName">表名</param>
/// <returns>数据集合</returns>
public DataTable GetDataTable(OleDbConnection connection, string tableName)
{
DataSet ds = new DataSet();
try
{
if (!tableName.EndsWith("$"))//检查sheet名称是否是以'$'结尾的,必须以'$'结尾
{
tableName += '$';
}
connection.Open();
OleDbCommand cmd = connection.CreateCommand();
cmd.CommandText = $"Select * from [{tableName}]";//必须加方括号
using (OleDbDataReader reader = cmd.ExecuteReader())
{
ds.Load(reader, LoadOption.OverwriteChanges, tableName);//这儿使用的是DataSet的加载方法
}
}
finally
{
connection.Close();
}
if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return null;
}
}
/// <summary>
/// 通过sql语句获取表单
/// </summary>
/// <param name="fileFullPath">文件全路径名称</param>
/// <param name="sql">sql语句</param>
/// <returns>通过sql查询到的表单</returns>
/// <remarks>
/// 如果HDR使用的是NO, 则默认F1类型开始为列标题(相当于数据库的字段名);
/// 如果HDR使用的是Yes,则第一行的数据值当作数据库的字段名类型
/// string sql="select F1,F2 from [Sheet1$] ";//注意表名称[] 和 $ 都不能少了
/// </remarks>
public DataTable GetDataTableBySql(string fileFullPath, string sql)
{
OleDbConnection connection = GetConnection(fileFullPath);
return GetDataTableBySql(connection, sql);
}
/// <summary>
/// 通过sql语句查询表单
/// </summary>
/// <param name="connection">连接对象</param>
/// <param name="sql">查询语句</param>
/// <returns>通过sql查询到的表单</returns>
/// <remarks>
/// 如果HDR使用的是NO, 则默认F1类型开始为列标题(相当于数据库的字段名)
/// string sql="select F1,F2 from [Sheet1$] ";//注意表名称[] 和 $ 都不能少了
/// </remarks>
public DataTable GetDataTableBySql(OleDbConnection connection, string sql)
{
DataSet ds = new DataSet();
try
{
connection.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);
adapter.Fill(ds);
}
finally
{
connection.Close();
}
if (ds.Tables.Count > 0)
{
return ds.Tables[0];
}
else
{
return null;
}
}
/// <summary>
/// 文件类型
/// </summary>
private enum FileType
{
noset,
xls,
xlsx,
csv
}
}
ExcelReader的使用:
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Execl files (*.xlsx)|*.xlsx|(*.xls)|*.xls*";
if(ofd.ShowDialog()== DialogResult.OK)
{
ExcelReader excel = new ExcelReader();
List<string> lstSheets = excel.GetSheetsName(ofd.FileName);//获取所有表名
dataGridView1.DataSource = lstSheets;
DataSet ds0= excel.GetDataSet(ofd.FileName);//获取Excel文件中的所有数据
DataTable table = excel.GetDataTable(ofd.FileName,lstSheets.First());//读取Excel中第一张Sheet的值
//excel.IsFirstRowAsColumnNames =true;
//string sql = $"select 学号,姓名,性别,备注 from [{lstSheets[0]}] where 姓名='张三'";
excel.IsFirstRowAsColumnNames = false;
//string sql =$"select F1,F2,F3,F6 from [{lstSheets[0]}] where F2='张三'";//查找张三
string sql = $"select * from [{lstSheets[0]}]";//查找张三
dataGridView1.DataSource = excel.GetDataTableBySql(ofd.FileName, sql);
}
}
凡所有相,皆是虚妄。