using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
#region Microsoft Excel幫助類
/// <summary>
/// Microsoft Excel幫助類
/// </summary>
public class ExcelHelper
{
string strMessage = "";
/// <summary>
/// 構造函數
/// </summary>
public ExcelHelper()
{
}
/// <summary>
/// 信息屬性
/// </summary>
public string Message
{
set { strMessage = value; }
get { return strMessage; }
}
/// <summary>
/// 返回一張表,默認為Sheet1
/// </summary>
/// <param name="strFileFullName"></param>
/// <returns></returns>
public DataTable GetTableByExcel2003(string strFileFullName)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileFullName + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, conn);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
mycommand.Dispose();
conn.Close();
conn.Dispose();
return ds.Tables[0];
}
/// <summary>
/// 返回一張表,默認為Sheet1
/// </summary>
/// <param name="strFileFullName"></param>
/// <returns></returns>
public DataTable GetTableByExcel2007(string strFileFullName)
{
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileFullName + ";Extended Properties=Excel 8.0;";
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strFileFullName + ";" + "Extended ProPerties= \"Excel 12.0;HDR=Yes;\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, conn);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
mycommand.Dispose();
conn.Close();
conn.Dispose();
return ds.Tables[0];
}
/// <summary>
/// 根据Excel驱动类型来读取EXcel文件
/// </summary>
/// <param name="strDrive">例如 Microsoft.ACE.OLEDB.12.0 </param>
/// <param name="strFileFullName">文件全路径,包括文件名</param>
/// <returns></returns>
public DataTable GetTableByExcel(string strDrive, string strFileFullName)
{
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileFullName + ";Extended Properties=Excel 8.0;";
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strFileFullName + ";" + "Extended ProPerties= \"Excel 12.0;HDR=Yes;\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, conn);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
mycommand.Dispose();
conn.Close();
conn.Dispose();
return ds.Tables[0];
}
}
#endregion
================================================================================================
時間截轉化
=(((((B2+28800000)/60)/60)/1000)/24)+DATE(1970,1,1)
B2 = 時間欄位
再把Excel欄位格式改成"日期"