/// <summary>
/// 将Excel读取为DataTable
/// </summary>
/// <param name="filePath">服务器文件绝对路径,包括文件名</param>
/// <param name="fileName">文件名</param>
/// <param name="needHead">是否将第一行作为表头</param>
/// <param name="allTypeText">是否将所有数据读为文本格式(该选项将固定读取65535行数据)</param>
/// <returns></returns>
public static DataTable ReadExcelToDataTable(string filePath, string fileName, bool needHead, bool allTypeText)
{
DataTable dt = new DataTable();
OleDbConnection conn = null;
OleDbDataAdapter myCommand = null;
string type = "";
if (needHead)
type += "HDR=YES;";
if (allTypeText)
type += "IMEX=1;";
try
{
string strConn = string.Empty;
if (fileName.EndsWith(".xlsx"))
strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;{0}';data source={1}", type, filePath);
else if (fileName.EndsWith(".xls"))
strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;{0}';Data Source={1};", type, filePath);
conn = new OleDbConnection(strConn);
conn.Open();
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
if (dtSheetName == null || dtSheetName.Rows.Count < 1)
throw new Exception("请上传正确的Excel!");
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (myCommand != null)
myCommand.Dispose();
conn.Close();
conn.Dispose();
}
return dt;
}
/// 将Excel读取为DataTable
/// </summary>
/// <param name="filePath">服务器文件绝对路径,包括文件名</param>
/// <param name="fileName">文件名</param>
/// <param name="needHead">是否将第一行作为表头</param>
/// <param name="allTypeText">是否将所有数据读为文本格式(该选项将固定读取65535行数据)</param>
/// <returns></returns>
public static DataTable ReadExcelToDataTable(string filePath, string fileName, bool needHead, bool allTypeText)
{
DataTable dt = new DataTable();
OleDbConnection conn = null;
OleDbDataAdapter myCommand = null;
string type = "";
if (needHead)
type += "HDR=YES;";
if (allTypeText)
type += "IMEX=1;";
try
{
string strConn = string.Empty;
if (fileName.EndsWith(".xlsx"))
strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;{0}';data source={1}", type, filePath);
else if (fileName.EndsWith(".xls"))
strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;{0}';Data Source={1};", type, filePath);
conn = new OleDbConnection(strConn);
conn.Open();
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
if (dtSheetName == null || dtSheetName.Rows.Count < 1)
throw new Exception("请上传正确的Excel!");
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (myCommand != null)
myCommand.Dispose();
conn.Close();
conn.Dispose();
}
return dt;
}