使用OLEDB读取Excel

        public DataSet ExcelToDataSet(string filename)
        {
           try   
           {       
               string strConn;        // IMEX=1 可把混合型作为文本型读取，避免null值        
               strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename                
                   + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";       
               OleDbConnection OleConn = new OleDbConnection(strConn);       
               OleConn.Open();

　　　　　　　　// DIM是Sheet名
               String sql = "SELECT * FROM  [DIM$]"; 
               //String sql = "SELECT * FROM [TEMP$] WHERE Trim(列名)='TEMP'";
               // 可更改 Sheet 名称            
               OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
               DataSet ds = new DataSet();
               OleDaExcel.Fill(ds, "DIM");       

 

　　　　　　　//  如果要循环读取sheet页

            //DataTable dtSheetName = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });            

　　　　　　//var sheetNames = (from record in dtSheetName.AsEnumerable()

　　　　　　//                  where record.Field<string>("TABLE_NAME").Substring(record.Field<string>            

　　　　　　//                  ("TABLE_NAME").Length - 1, 1).Equals("$")            

　　　　　　//                  select record.Field<string>("TABLE_NAME")).ToArray<string>();

            //foreach (var item in sheetNames)            

　　　　　　//{            

　　　　　　//    String sql = "SELECT * FROM " + item;            

　　　　　　//    OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);            

　　　　　　//    DataSet ds = new DataSet();            

　　　　　　//    OleDaExcel.Fill(ds, item);                   

　　　　　　//}
               OleConn.Close();       
               return ds;   
           }   
           catch (Exception ex)   
           {       
               //MessageBox.Show("数据绑定Excel失败!失败原因：" + ex.Message, "提示信息", MessageBoxButtons.OK,            
               //    MessageBoxIcon.Information);       
               return null;   
           }
        }

A：HDR( HeaDer Row )设置

若指定值为Yes，代表Excel档中的工作表第一行是栏位名称

若指定值为 No，代表Excel档中的工作表第一行就是资料了，没有栏位名称

B：IMEX ( IMport EXport mode )设置

IMEX 有三种模式，各自引起的读写行为也不同，容后再述：

0 is Export mode

1 is Import mode

2 is Linked mode (full update capabilities)

我这里特别要说明的就是 IMEX 参数了，因为不同的模式代表著不同的读写行为：

当 IMEX=0 时为“汇出模式”，这个模式开启的Excel档案只能用来做“写入”用途。

当 IMEX=1 时为“汇入模式”，这个模式开启的Excel档案只能用来做“读取”用途。

当 IMEX=2 时为“连结模式”，这个模式开启的Excel档案可同时支援“读取”与“写入”用途。

意义如下:

0 ---输出模式;
1---输入模式;
2----链接模式(完全更新能力)

IMEX=1,如果excel中某一列里面既有数字类型，也有字符类型，通常会按照第一行的类型进行读取，如果类型不匹配就显示DBNULL,设置IMEX=1可以最大可能避免这个问题的发生。

如果OleConn.Open()打开失败，有可能是OLEDB版本号，和Excel版本号设置的有问题，一般设12就行，也有设4和8的，这个应该跟OFFICE版本相关。

"SELECT * FROM [TEMP$] WHERE Trim(列名)='TEMP'" ACCESS的函数较少，具体应用可以查询以下内容，如果列名是数字或者其他类型，只需要使用任意文本函数转化下，就变成了文字类型了。sheet名可以多个"select * from [sheet1$],[sheet2$],[sheet3$]",就可以查出多个sheet放到DataSet中了。

 

这是读取CSV的，除了连接信息有点变化外，就是CSV没有sheet，直接写文件名就可以

 public abstract class CSV
    {
        private static readonly string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= {0};Extended Properties='text;HDR={1};FMT=Delimited'";

        //获取完整的路径
        private static string FileFullPath()
        {
            return System.Web.HttpContext.Current.Request.PhysicalApplicationPath + "UpLoad\\";   //完整的路径不需要文件名
        }

        /// <summary>
        /// GetExcelToDataTableBySheet方法的重载，默认hdr参数为YES
        /// </summary>
        public static DataTable GetCsvToDataTable(string FillName)
        {
            return GetCsvToDataTable(FillName, "YES");
        }
        /// <summary>
        /// 获取CSV中的表数据
        /// </summary>
        /// <remarks>
        ///  DataTable dt = new DataTable();
        ///  dt = CSV.GetExcelToDataTableBySheet(fillName, hdr);
        /// </remarks>
        /// <param name="FillName">文件名</param>
        /// <param name="hdr">第一行是否作为字段名,可选值为yer或者no</param>
        /// <returns></returns>
        public static DataTable GetCsvToDataTable(string FillName,string hdr)
        {
            string strconn = string.Format(strConn, FileFullPath(),hdr);
            OleDbConnection conn = new OleDbConnection(strconn);
            conn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", FillName), conn);  //这里的表名参数，就是 CSV的完整文件名
            odda.Fill(ds, FillName);
            conn.Close();
            return ds.Tables[0];
        }
    }

 

ACCESS高级操作

一.Access函数大全

1.1日期/时间

CDate  将字符串转化成为日期 select CDate("2005/4/5")

Date  返回当前日期

DateAdd  将指定日期加上某个日期select dateAdd("d",30,Date())将当前日期加上30天,其中d可以换为yyyy或H等

DateDiff 判断两个日期之间的间隔 select DateDiff("d","2006-5-1","2006-6-1")返回31,其中d可以换为yyyy,m,H等

DatePart 返回日期的某个部分 select DatePart("d","2006-5-1")返回1,即1号，d也可以换为yyyy（年）或q（季），m（月），d（日），w（工作日），ww（周），h（时），n（分），s（秒）

Day  返回日期的d部分,等同于datepart的d部分

Dateserial(年，月，日) 组合年月日构造日期

Hour  返回日期的小时

IsDate  判断是否是日期,是日期返回-1,不是日期返回0

Minute  返回日期的分钟部分

Month  返回日期的月份部分

Now  返回当前时间(完整时间，包括年月日 小时分秒)

Second  返回日期的秒部分

Time  返回当前的时间部分(即除去年/月/日的部分)

Weekday  返回某个日期的当前星期(星期天为1,星期一为2,星期二为3...)，例如select weekday(now());

Year  返回某个日期的年份

1.2检查

IsEmpty  检测是否为空(不过经测试，不管什么情况，都返回0)

IsNull  检测是否为Null值，null值返回0，非null值返回-1

IsNumeric 检测是否为数字,是数字返回-1，否则返回0

1.3算术

Abs   绝对值

Atn   返正切值。

Cos   余弦值

Exp   返回 e 的给定次幂。

Fix   返回数字的整数部分(即小数部分完全截掉)

Int  将数字向下取整到最接近的整数。(其实等同于Fix)

Log  返回以E为底的对数值

Rnd  返回一个0到1之间的随机数值

Sgn  返回数字的正负符号(正数返回1,负数返回-1,0值返回0)

Sin  正弦值

Sqr  返回平方根值

Tan   正切值

▲程序流程

Choose  根据第一参数，返回后面字符串组的值,Select Choose(1,"a","b","c")返回a,将1改成2后，返回b,改成3后，返回c(第一个参数也可以是某个字段)

IIF  根据表达式返回特定的值 Select IIF("3>1","OK","False")，返回OK

▲Sql合计（聚合）函数

Avg  取字段平均值

Count  统计记录条数

Max  取字段最大值

Min  取字段最小值

StDev  估算样本的标准差（忽略样本中的逻辑值和文本）。

StDevP  计算以参数形式（忽略逻辑值和文本）给出的整个样本总体的标准偏差。

Sum  计算字段的总合

Var  估算样本方差（忽略样本中的逻辑值和文本）。

VarP  计算整个样本总体的方差（忽略样本总体中的逻辑值和文本）。

1.4文本

Asc   返回字母的Acsii值，select Asc("A")返回65

Chr   将ascii值转换到字符 select chr(65)返回"A"

Format   格式化字符串，Select Format(now(),'yyyy-mm-dd')返回类似于"2005-04-03" ,Select Format(3/9,"0.00")返回0.33

InStr   查询子串在字符串中的位置 select Instr("abc","a")返回1,select Instr("abc","f")返回0

LCase  返回字符串的小写形式

Left  左截取字符串

Len  返回字符串长度

LTrim  左截取空格

Mid    取得子字符串 select mid("123",1,2) as midDemo 返回12

Right  右截取字符串

RTrim  右截取空格

Space  产生空格 select Space(4)返回4个空格

StrComp比较两个字符串是否内容一致(不区分大小写)select StrComp("abc","ABC")返回0,select StrComp("abc","123")返回-1

Trim  截取字符串两头的空格

UCase  将字符串转大写

 

 

二、access中有效性规则的写法

有效性规则示例

下表提供了字段级和记录级有效性规则的示例，以及说明性有效性文本。可以针对您的内容对这些示例进行相应的改编。

 

有效性规则 有效性文本

<>0 输入非零值。

>=0 值不得小于零。 - 或 -必须输入正数。

0 or >100 值必须为 0 或者大于 100。

BETWEEN 0 AND 1 输入带百分号的值。（用于将数值存储为百分数的字段。）

<#01/01/2007# 输入 2007 年之前的日期。

>=#01/01/2007# AND <#01/01/2008# 必须输入 2007 年的日期。

<Date() 出生日期不能是将来的日期。

StrComp(UCase([姓氏]),

[姓氏],0) = 0 “姓氏”字段中的数据必须大写。

>=Int(Now()) 输入当天的日期。

M Or F 输入 M（代表男性）或 F（代表女性）。

LIKE "[A-Z]*@[A-Z].com" OR "[A-Z]*@[A-Z].net" OR "[A-Z]*@[A-Z].org" 输入有效的 .com、.net 或 .org 电子邮件地址。

[要求日期]<=[订购日期]+30 输入在订单日期之后的 30 天内的要求日期。

[结束日期]>=[开始日期] 输入不早于开始日期的结束日期。

 

常见有效性规则的语法

有效性规则中的表达式不使用任何特殊语法。本节中的信息说明某些较常见类型的有效性规则的语法。在执行操作时，请记住：表达式和函数可能会非常复杂，全面的讨论不在本文讨论范围之内。

 

有关表达式的详细信息，请参阅创建表达式一文。有关函数的详细信息，请参阅函数（按类别排列）一文。

 

创建表达式时，请牢记下列规则：

 

将表字段的名称用方括号括起来，例如：[要求日期]<=[订购日期]+30。

将日期用井号 (#) 括起来，例如：<#01/01/2007#

将文本值用双引号括起来，例如：IN ("东京","巴黎","莫斯科")。另外，请注意用逗号分隔项目，并将列表放在圆括号内。

除了上述规则之外，下表显示了常见的算术运算符并提供了使用方法示例。

 

运算符 函数 示例

NOT 测试相反值。在除 IS NOT NULL 之外的任何比较运算符之前使用。 NOT > 10（与 <=10 相同）。

IN 测试值是否等于列表中的现有成员。比较值必须是括在圆括号中的逗号分隔列表。 IN ("东京","巴黎","莫斯科")

BETWEEN 测试值范围。必须使用两个比较值（低和高），并且必须使用 AND 分隔符来分隔这两个值。   BETWEEN 100 AND 1000（与 >=100 AND <=1000相同）

LIKE 匹配文本和备注字段中的模式字符串。 LIKE "Geo*"

IS NOT NULL 强制用户在字段中输入值。此设置与将“必填”字段属性设置为“是”具有同样的效果。但是，如果启用了“必填”属性但用户没有输入值，则 Access 会显示令人稍感不快的错误消息。通常，如果使用 IS NOT NULL 并在“有效性文本”属性中输入友好的消息，则您的数据库会更易于使用。 IS NOT NULL

AND 指定您输入的所有数据必须为 True 或在指定的范围内。 >= #01/01/2007# AND <=#03/06/2008#

 注释   您还可以使用 AND 来组合有效性规则。例如：NOT "英国" AND LIKE "英*"。

 Len([联系电话])=11

OR 指定可以有一段或多段数据为 True。 一月 OR 二月  "男" Or "女"

替换函数：Replace([款式编号],"E11EJ5120a"," 1E11EJ5120a ")；

四舍五入：Round(([实际成本]+[材料成本])/[报价成本]*100,2) & "%"

< 小于。 

<= 小于或等于。 

> 大于。 

>= 大于或等于。 

= 等于。 

<> 不等于。 

在有效性规则中使用通配符

在有效性规则中，可以使用 Access 提供的通配符。请记住，Access 支持两个通配符字符集，这是因为对于用于创建和管理数据库的结构化查询语言 (SQL)，Access 支持两种标准（ANSI-89 和 ANSI-92）。这两种标准使用不同的通配符字符集。

默认情况下，所有 .accdb 和 .mdb 文件都使用 ANSI-89 标准，而 Access 项目使用 ANSI-92 标准。如果您是 Access 新手，应注意在 Access 项目中，数据库中的表驻留在运行 Microsoft SQL Server 的计算机上，而窗体、报表和其他对象驻留在其他计算机上。如果需要，可以将 .accdb 和 .mdb 文件的 ANSI 标准更改为 ANSI-92。

 

三、设置表中数据的格式

 “文本”和“备注”数据类型的自定义格式

文本 （“文本”数据类型:Microsoft Access 数据库中的一种字段数据类型。文本数据类型最多可以包含 255 个字符，或者是由 FieldSize 属性指定的一个小一些的字符数。）、备注 （“备注”数据类型:在 Microsoft Access 数据库中的一种字段数据类型。“备注”字段最多可以包含 65,535 个字符。）和超链接 （“超链接”数据类型:存储超链接地址的 Access 数据库字段的数据类型。地址最多可以包含四部分，用以下语法格式编写:displaytext#address#subaddress#。）数据类型不接受预定义格式。“文本”数据类型仅接受自定义格式，“备注”数据类型接受自定义格式和格式文本格式，而“超链接”数据类型接受自定义格式（可以设置控制链接颜色的程序选项）。

 

通常会将自定义格式应用于“文本”和“备注”数据类型，以便能更轻松地读取表数据。例如，如果使用 Web 窗体收集信用卡号码，并在存储这些号码时不添加任何空格，则可以使用自定义格式添加适当的空格，以便能更轻松地读取信用卡号码。

 

“文本”和“备注”数据类型的自定义格式仅允许一个字符串中包含两个格式部分。格式字符串的第一个部分将控制文本的外观，而第二部分将显示空值或零长度的字符串。如果不指定格式，则 Access 会将数据表中的所有文本对齐。若要了解有关对“文本”或“备注”字段应用自定义格式的详细信息，请参阅应用自定义格式。

 

下表列出并说明了可应用于“文本”和“备注”字段的自定义格式。

字符	说明
@	如果格式字符串中的任何字符位置有可用的字符，则显示这些字符。如果 Access 将所有字符置于基础数据中，则任何剩余的占位符将显示为空格。 例如，如果格式字符串为 @@@@@ 而基础文本为 ABC，则该文本将添加两个前导空格以便向左对齐。
&	如果格式字符串中的任何字符位置有可用的字符，则显示这些字符。如果 Access 将所有字符置于基础数据中，则任何剩余的占位符将不会显示任何内容。 例如，如果格式字符串为 &&&&& 且文本为 ABC，则仅显示向左对齐的文本。
!	用于强制从左至右而不是从右至左填充占位符。必须在任何格式字符串的开头使用此字符。
<	用于将所有文本强制为小写。必须在格式字符串的开头使用此字符，但可以在其前面放置一个感叹号 (!)。
>	用于将所有文本强制为大写。必须在格式字符串的开头使用此字符，但可以在其前面放置一个感叹号 (!)。
*	使用星号时，紧随其后的字符将变成填充字符（用来填充空格的字符）。Access 一般会以左对齐方式显示文本，并用空格填充该值右侧的任何区域。可以在格式字符串中的任何位置添加填充字符。当您这样做时，Access 会用指定的字符填充任何空格。
空格 + - $ ()	用于根据需要在格式字符串中的任何位置插入空格、数学字符（+ 和 -）、财务符号（$、¥ 和 £）和圆括号。如果您想使用其他常见的数学符号，如斜杠（\ 或 /）和星号 (*) 等，请用双引号引起它们（请注意，可以将这些字符置于格式字符串中的任何位置）。
"文本"	用双引号引起您希望显示给用户的任何文本。
\	用于强制 Access 显示紧随其后的字符，这与用双引号引起一个字符具有相同的效果。
[颜色]	用于向格式中某个部分的所有值应用颜色。必须用方括号括起颜色的名称并使用下列名称之一：黑色、蓝色、蓝绿色、绿色、洋红、

 

其他格式详见：（http://office.microsoft.com/zh-cn/access-help/HA001233061.aspx）