Sub 添加记录access2007()
Dim cnn As New ADODB.Connection
Dim Cat As New ADOX.Catalog
Dim MyData$, MyFile, n%
MyData = ThisWorkbook.Path & "\数据库.accdb"
On Error Resume Next
If Dir(MyData) = "" Then
Cat.Create "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & MyData
Else
Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & MyData
Cat.Tables.Delete "数据表1"
End If
Set Cat = Nothing
On Error GoTo 0
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & MyData
Mypath = ThisWorkbook.Path & "\"
MyFile = Dir(Mypath & "*.xlsx")
Do While MyFile <> ""
n = n + 1
If n = 1 Then
cnn.Execute "select * into 数据表1 from [Excel 12.0;Database=" & Mypath & MyFile & "].[Sheet1$]"
Else
cnn.Execute "insert into 数据表1 select * from [Excel 12.0;Database=" & Mypath & MyFile & "].[Sheet1$]"
End If
MyFile = Dir()
Loop
cnn.Close
Set cnn = Nothing
MsgBox "ok"
End Sub

Sub setNewTable()
Dim Cat As New ADOX.Catalog
Dim tb1 As ADOX.Table
Dim cnn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim MyPath As String
Dim MyName As String
Dim sh As String
Dim i As Long
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\数据库.accdb"
MyPath = ThisWorkbook.Path & "\"
MyName = Dir(MyPath & "*.xlsx")
Do While MyName <> ""
      Cat.ActiveConnection = "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & MyPath & MyName
      For Each tb1 In Cat.Tables
         If tb1.Type = "TABLE" Then
            sh = Replace(tb1.Name, "'", "")
            If Right(sh, 1) = "$" Then
                  i = i + 1
                  myTable = Replace(sh, "$", "")
                  Set rs = cnn.OpenSchema(adSchemaTables, Array(Empty, Empty, myTable, Empty))
                  If Not rs.EOF Then cnn.Execute "DROP TABLE " & myTable
                  cnn.Execute "CREATE TABLE " & myTable & "(日期 date not null primary key,数量 long not null)"
                  cnn.Execute "INSERT INTO " & myTable & " SELECT f1 as 日期,f2 as 数量 FROM [Excel 12.0;Hdr=No;Database=" & MyPath & MyName & ";].[" & sh & "]"
            End If
         End If
      Next
      MyName = Dir
Loop
MsgBox "已成功生成" & i & "个数据表。", vbInformation
Set Cat = Nothing
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

公式转数字

1、将原公式整体*1。

2、在原公式整体前添加“--”符号。

Sub 合并目录所有工作簿全部工作表()

Dim MP, MN, AW, Wbn, wn

Dim Wb As Workbook

Dim i, a, b, d, c, e

Application.ScreenUpdating = False

MP = ActiveWorkbook.Path

MN = Dir(MP & "\" & "*.xls")

AW = ActiveWorkbook.Name

Num = 0

e = 1

Do While MN <> ""

If MN <> AW Then

Set Wb = Workbooks.Open(MP & "\" & MN)

a = a + 1

With Workbooks(1).ActiveSheet

For i = 1 To Sheets.Count

If Sheets(i).Range("a1") <> "" Then

Wb.Sheets(i).Range("a1").Resize(1, Sheets(i).UsedRange.Columns.Count).Copy .Cells(1, 1)

d = Wb.Sheets(i).UsedRange.Columns.Count

c = Wb.Sheets(i).UsedRange.Rows.Count - 1

wn = Wb.Sheets(i).Name

.Cells(1, d + 1) = "表名"

.Cells(e + 1, d + 1).Resize(c, 1) = MN & wn

e = e + c

Wb.Sheets(i).Range("a2").Resize(c,d).Copy .Cells(.Range("a1048576").End(xlUp).Row + 1, 1)

End If

Wbn = Wbn & Chr(13) & Wb.Name

Wb.Close False

End With

End If

MN = Dir

Loop

Range("a1").Select

Application.ScreenUpdating = True

MsgBox "共合并了" & a & "个工作薄下全部工作表。如下：" & Chr(13) & Wbn, vbInformation, "提示"

End Sub

使用以下语法设置“控件来源”：

=[Forms]![form name]![control name]

其中窗体名称是要显示其值的控件所在的窗体的名称，控件名称是控件的名称。

引用子窗体上的控件会稍微复杂一些。在这种情况下，请使用下面这样的语法：

=[Forms]![main form name]![name of the subform control on the main form].[Form]![control name on the subform]

其中“main form name”是主窗体的名称，“name of the subform control on the main form”是作为子窗体容器的主窗体上的控件名称，“control name on the subform”是子窗体上的控件名称，该子窗体包含要显示其值的控件。

1.AutoExec宏（如果向数据库中添加一个名为 AutoExec 的 Access 宏，通常情况下，每次打开数据库时，该宏都会先自动运行。）

2.备份模块（在Auto宏中运行该函数）

Public Function gf_Bakup()

Set fs = CreateObject("Scripting.FileSystemObject")

fs.copyfile CurrentProject.FullName, CurrentProject.Path & "\bak_" & Date & CurrentProject.Name

End Function

Getrows 方法

Dim oc As Object, rs As Object, a()

Set oc = CreateObject("adodb.connection")

oc.open "provider=Microsoft.Jet.OLEDB.4.0; Data Source=e:\tmp\db2.mdb"

Set rs = CreateObject("adodb.recordset")

rs.open "select * from tek", oc, 1, 1

a = rs.getrows

MsgBox "记录集rs已被读入数组a中" & vbCrLf & "共" & UBound(a, 1) + 1 & "列 " & UBound(a, 2) + 1 & "行"

rs.Close

Set rs = Nothing

oc.Close

Set oc = Nothing

Sub SQL2Arr()

Dim AdoCN As Object

Dim AdoRe As Object

Dim SQL As String

Dim Arr1, Arr2, Arr3

Set AdoCN = CreateObject("ADODB.Connection")

Set AdoRe = CreateObject("ADODB.Recordset")

SQL = "SELECT * FROM [Sheet1$A1:C11]"

AdoCN.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & ThisWorkbook.FullName & ";" & _

"Extended Properties=Excel 12.0;"

Set AdoRe = AdoCN.Execute(SQL)

Arr1 = AdoRe.GetRows(, , "姓名") '取得某一列

AdoRe.MoveFirst '取完一次，要将指针移动到最前

Arr2 = AdoRe.GetRows(, , Array("姓名", "班级")) '取得两列

AdoRe.MoveFirst '取完一次，要将指针移动到最前

Arr3 = AdoRe.GetRows '取得全部

AdoCN.Close

Set AdoRe = Nothing

Set AdoCN = Nothing

End Sub

'后期绑定：方便代码在其他电脑上运行，推荐。

dim dic as object

Set dic = CreateObject("scripting.dictionary")

'前期绑定：可以直接声明字典对象，有对象属性和方法的提示，但在其他没有勾选引用的电脑上无法正常运行。

'引用勾选：VBE窗体-工具-引用-勾选‘Microsoft Scripting Runtime’

dim dic as New dictionary

用access自动获取excel里的数据的方法就是通过代码的方式导入。如果是对手动导入比较清楚的话，这个实现起来不难。请参考下面的介绍来实现：
首先，新建一个工程，在工程中引用如下对象ADO对象（用于连接636f70797a686964616f31333337393631ACCESS数据库，可用其它方式）
添加一个窗体（from1），在窗体上添加如下控件：
两个文本框，用来显示EXCEL文件路径和ACCESS路径；
四个扭钮，两个用来游览，另两个是导入和退出；
两个通用对话框控件，用来打开ACCESS和EXCEL文件，一个进度条控件，用来显示导入进程。
该
实例的导入是将ACCESS数据库中表的字段名单独存放在另外一个表中,导入时按表中所存字段名的顺序进行导入,不是按EXCEL表的顺序导入,请大家注
意.这样在实际中是很实用的.因为好多时候EXCEL表中字段顺序和ACCESS中字段顺序有可能不是一致的.

代码如下：
Dim v
Option Explicit
Private Sub cmdLoad_Click()
Dim excel_app As Object
Dim excel_sheet As Object
If txtExcelFile.Text = "" Then
MsgBox "请选择EXCEL表"
Else
Dim new_value As String
Label2.Caption = "正在导入,请稍候..."
Screen.MousePointer = vbHourglass
DoEvents
'' Create the Excel application.
Set excel_app = CreateObject("Excel.Application")
'' Uncomment this line to make Excel visible.
excel_app.Visible = True
'' Open the Excel spreadsheet.
excel_app.Workbooks.open FileName:=txtExcelFile.Text
'' Check for later versions.
If Val(excel_app.Application.Version) >= 8 Then
Set excel_sheet = excel_app.ActiveSheet
Else
Set excel_sheet = excel_app

End If

Dim u ''求EXCEL表中记录的条数,以便控制进度条
u = 1
Do
If Trim$(excel_sheet.Cells(u, 1)) = "" Then Exit Do
u = u + 1
Loop
bar.Max = u - 1

strSQL = "select * from TestValues"
yourRecord.open strSQL, myConn, adOpenDynamic, adLockOptimistic ''打开记录集
Dim sql As String

sql = "select * from fields order by xue"
myRecord.open sql, myConn, adOpenDynamic, adLockBatchOptimistic ''打开字段记录集
myRecord.MoveFirst

'' Get data from the Excel spreadsheet and insert
'' it into the TestValues table.

Dim v ''导入记录,用了两层循环
v = 1
Do
If Trim$(excel_sheet.Cells(v, 1)) = ""

Then Exit Do ''外层,如果EXCEL表中读取到空行,结束

yourRecord.AddNew

Dim i
For i = 1 To myRecord.RecordCount
'' Get the next value.
new_value = Trim$(excel_sheet.Cells(v, i))
'' See if it''s blank.
''If Len(new_value) = 0 Then Exit Do

'' Insert the value into the database.

Dim bb As String

bb = myRecord("name")

yourRecord(bb) = new_value

myRecord.MoveNext

Next i
bar.Value = v
v = v + 1
myRecord.MoveFirst
Loop
yourRecord.Update

'' Comment the rest of the lines to keep
'' Excel running so you can see it.
'' Close the workbook without saving.
excel_app.ActiveWorkbook.Close False
'' Close Excel.
excel_app.Quit
Set excel_sheet = Nothing
Set excel_app = Nothing
myRecord.Close
yourRecord.Close
Set myRecord = Nothing
Set yourRecord = Nothing
Label2.Caption = "导入完毕"
Screen.MousePointer = vbDefault
MsgBox "共导入" & Format$(v - 1) & "条记录"

End If
End Sub
Private Sub Command1_Click()
Unload Me
End Sub
Private Sub Command2_Click(Index As Integer)
''寻找ACCESS数据库
CommonDialog1.Filter = "ACCESS 文件(*.mdb)|*.mdb"
CommonDialog1.CancelError = True
CommonDialog1.ShowOpen
txtAccessFile.Text = CommonDialog1.FileName
End Sub
Private Sub Command3_Click()
''寻找excel数据库
CommonDialog2.Filter = "excel 文件(*.xls)|*.xls"
CommonDialog2.CancelError = True
CommonDialog2.ShowOpen
txtExcelFile.Text = CommonDialog2.FileName
End Sub

Private Sub Form_Load()
Call Module1.lianjie
txtAccessFile.Text = datapath
End Sub
模块（module1）中的代码如下：
Public myConn As New ADODB.Connection ''定义连接字符串
Public myRecord As New ADODB.Recordset ''定义记录集(字段)
Public yourRecord As New ADODB.Recordset ''定义记录集(数据)
Public cntoad As Boolean ''是否正常连接
Public ml ''姓名字段所在列
Public strSQL ''查询字符串
Public MyDatabase As Database ''定义数据库名
Public MyTable As TableDef, MyField As Field ''定义表名和字段名
Public xuehao ''读取字段序号
Public goshiRecord As New ADODB.Recordset ''定义记录集(公式)
Public hxfyn As Boolean
Public hxfbds '' 公式或条件
Public duan ''要统计的字段
Public islinshi As Boolean ''是否为临时公式
Public leiRecord As New ADODB.Recordset ''定义记录集(工资类别)
Public datapath As String ''数据库路径及名
Public table As String ''工资表名
Public lei As String '' 工资类别
Public Sub lianjie() ''打开数据库
On Error Resume Next
myConn.Close
Dim mySQL As String
''设定连接字符串
mySQL = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
mySQL = mySQL + "Data Source=" & datapath

myConn.ConnectionString = mySQL ''设定连接

myConn.open ''打开连接
myRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection
myRecord.CursorLocation = adUseClient
goshiRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection
goshiRecord.CursorLocation = adUseClient
yourRecord.ActiveConnection = myConn ''设定RecordSeet的连接对象为Connection
yourRecord.CursorLocation = adUseClient
End Sub

表格字段a b c 与字段公式组成结果

计算结果: Eval(Replace(Replace(Replace([公式],"A",[A]),"B",[B]),"C",[C]))

using System;

using System.Collections.Generic;

using System.Text;

using System.Configuration;

using Microsoft.Office.Interop.Excel;

using System.Data;

using System.Reflection;

using System.Data.OleDb;

namespace SOSP.Common

{

/// <summary>

/// C#与Excel交互类

/// </summary>

public class ExcelHelper

{

#region 导出到Excel

#region ExportExcelForDataTable

/// <summary>

/// 从DataTable导出Excel,指定列别名,指定要排除的列

/// </summary>

/// <param name="dt">DataTable</param>

/// <param name="excelPathName">含Excel名称的保存路径在pathType＝1时有效，其它请赋值空字符串</param>

/// <param name="pathType">路径类型。只能取值：0客户自定义路径；1服务端定义路径，标识文件保存路径是服务端指定还是客户自定义路径及文件名</param>

/// <param name="colName">各列的列名List string </param>

/// <param name="excludeColumn">要显示/排除的列</param>

/// <param name="excludeType">显示/排除列方式 0为所有列 1指定的为要显示的列 2指定的为要排除的列</param>

/// <param name="sheetName">sheet1的名称为空字符串时保持默认名称</param>

/// <param name="TemplatePath">模版在项目服务器中路径例:tp.xlsx 为空字符串时表示无模版</param>

/// <param name="TemplateRow">模版中已存在数据的行数，无模版时请传入参数 0</param>

/// <param name="exDataTableList">扩展 DataTable List 用于当上下两个及以上DataTable数据类型不一至,但又都在同一列时使用,要求格式与参数第一个 DataTable的列名字段名一至,仅字段类型可不同</param>

/// <returns>bool</returns>

public static bool ToExcelForDataTable(System.Data.DataTable dt, string excelPathName, string pathType, List<string> colName, List<string> excludeColumn, string excludeType, string sheetName, string TemplatePath, int TemplateRow, List<System.Data.DataTable> exDataTableList)

{

try

{

if (dt == null || dt.Rows.Count == 0) return false;

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

if (xlApp == null)

{

return false;

}

System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;

System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

Microsoft.Office.Interop.Excel.Workbook workbook = null;

if (TemplatePath == "")

{

workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

}

else

{

workbook = workbooks.Add(TemplatePath); //加载模板

}

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

Microsoft.Office.Interop.Excel.Range range;

long totalCount = dt.Rows.Count;

if (exDataTableList != null && exDataTableList.Count > 0)

{

foreach (System.Data.DataTable item in exDataTableList)

{

totalCount += item.Rows.Count;

}

long rowRead = 0;

float percent = 0;

string exclStr = "";//要排除的列临时项

object exclType;//DataTable 列的类型,用于做

int colPosition = 0;//列位置

if (sheetName != null && sheetName != "")

{

worksheet.Name = sheetName;

}

#region 列别名判定

if (TemplatePath == "")

{

if (colName != null && colName.Count > 0)

{

#region 指定了列别名

for (int i = 0; i < colName.Count; i++)

{

worksheet.Cells[1, i + 1] = colName[i];

range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];

range.Interior.ColorIndex = 15;

range.Font.Bold = true;

exclType = dt.Columns[i].DataType.Name;

if (exclType.ToString() != "DateTime")

{

//range.EntireColumn.AutoFit();//全局自动调整列宽,不能再使用单独设置

((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Columns.AutoFit();

}

else

{

//规定列宽

((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Columns.ColumnWidth = 20;

}

//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Columns.ColumnWidth = 20;

}

#endregion

}

else

{

#region 未指定别名

for (int i = 0; i < dt.Columns.Count; i++)

{

worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;

range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];

range.Interior.ColorIndex = 15;

range.Font.Bold = true;

exclType = dt.Columns[i].DataType.Name;

if (exclType.ToString() != "DateTime")

{

//range.EntireColumn.AutoFit();//全局自动调整列宽,不能再使用单独设置

((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Columns.AutoFit();

}

else

{

//规定列宽

((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Columns.ColumnWidth = 20;

}

//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Columns.ColumnWidth = 20;

}

#endregion

}

else

{

//用了模版，不加载标题

}

#endregion

#region 显示/排除列判定

if (excludeColumn != null && excludeColumn.Count > 0)

{

switch (excludeType)

{

case "0":

{

#region 0为显示所有列

#region 常规项

int r = 0;

for (r = 0; r < dt.Rows.Count; r++)

{

colPosition = 0;

for (int i = 0; i < dt.Columns.Count; i++)

{

if (TemplatePath == "")

{

worksheet.Cells[r + 2, colPosition + 1] = dt.Rows[r][i].ToString();

}

else

{

worksheet.Cells[r + TemplateRow, colPosition + 1] = dt.Rows[r][i].ToString();

}

colPosition++;

}

rowRead++;

percent = ((float)(100 * rowRead)) / totalCount;

}

#endregion

#region 扩展项

if (exDataTableList != null && exDataTableList.Count > 0)

{

foreach (System.Data.DataTable item in exDataTableList)

{

for (int k = 0; k < item.Rows.Count; r++, k++)

{

colPosition = 0;

//生成扩展 DataTable 每行数据

for (int t = 0; t < item.Columns.Count; t++)

{

if (TemplatePath == "")

{

worksheet.Cells[r + 2, colPosition + 1] = item.Rows[k][t].ToString();

}

else

{

worksheet.Cells[r + TemplateRow, colPosition + 1] = item.Rows[k][t].ToString();

}

colPosition++;

}

rowRead++;

percent = ((float)(100 * rowRead)) / totalCount;

}

#endregion

}; break;

case "1":

{

#region 1指定的为要显示的列

#region 常规项

int r = 0;

for (r = 0; r < dt.Rows.Count; r++)

{

colPosition = 0;

for (int i = 0; i < dt.Columns.Count; i++)

{

exclStr = dt.Columns[i].ColumnName;

if (excludeColumn.Contains(exclStr))

{

if (TemplatePath == "")

{

worksheet.Cells[r + 2, colPosition + 1] = dt.Rows[r][i].ToString();

}

else

{

worksheet.Cells[r + TemplateRow, colPosition + 1] = dt.Rows[r][i].ToString();

}

colPosition++;

}

else

{

}

rowRead++;

percent = ((float)(100 * rowRead)) / totalCount;

}

#endregion

#region 扩展项

if (exDataTableList != null && exDataTableList.Count > 0)

{

foreach (System.Data.DataTable item in exDataTableList)

{

for (int k = 0; k < item.Rows.Count; r++, k++)

{

colPosition = 0;

//生成扩展 DataTable 每行数据

for (int t = 0; t < item.Columns.Count; t++)

{

exclStr = dt.Columns[t].ColumnName;

if (excludeColumn.Contains(exclStr))

{

if (TemplatePath == "")

{

worksheet.Cells[r + 2, colPosition + 1] = item.Rows[k][t].ToString();

}

else

{

worksheet.Cells[r + TemplateRow, colPosition + 1] = item.Rows[k][t].ToString();

}

colPosition++;

}

else

{

}

rowRead++;

percent = ((float)(100 * rowRead)) / totalCount;

}

#endregion

}; break;

case "2":

{

#region 2指定的为要排除的列

#region 常规项

int r = 0;

for (r = 0; r < dt.Rows.Count; r++)

{

colPosition = 0;

for (int i = 0; i < dt.Columns.Count; i++)

{

exclStr = dt.Columns[i].ColumnName;

if (excludeColumn.Contains(exclStr))

{

}

else

{

if (TemplatePath == "")

{

worksheet.Cells[r + 2, colPosition + 1] = dt.Rows[r][i].ToString();

}

else

{

worksheet.Cells[r + TemplateRow, colPosition + 1] = dt.Rows[r][i].ToString();

}

colPosition++;

}

rowRead++;

percent = ((float)(100 * rowRead)) / totalCount;

}

#endregion

#region 扩展项

if (exDataTableList != null && exDataTableList.Count > 0)

{

foreach (System.Data.DataTable item in exDataTableList)

{

for (int k = 0; k < item.Rows.Count; r++, k++)

{

colPosition = 0;

//生成扩展 DataTable 每行数据

for (int t = 0; t < item.Columns.Count; t++)

{

exclStr = dt.Columns[t].ColumnName;

if (excludeColumn.Contains(exclStr))

{

}

else

{

if (TemplatePath == "")

{

worksheet.Cells[r + 2, colPosition + 1] = item.Rows[k][t].ToString();

}

else

{

worksheet.Cells[r + TemplateRow, colPosition + 1] = item.Rows[k][t].ToString();

}

colPosition++;

}

rowRead++;

percent = ((float)(100 * rowRead)) / totalCount;

}

#endregion

}; break;

default:

break;

}

else

{

//生成每行数据

int r = 0;

for (r = 0; r < dt.Rows.Count; r++)

{

//生成每列数据

if (TemplatePath == "")

{

for (int i = 0; i < dt.Columns.Count; i++)

{

worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();

}

else

{

for (int i = 0; i < dt.Columns.Count; i++)

{

worksheet.Cells[r + 1 + TemplateRow, i + 1] = dt.Rows[r][i].ToString();

}

rowRead++;

percent = ((float)(100 * rowRead)) / totalCount;

}

#endregion

switch (pathType)

{

case "0": { workbook.Saved = false; }; break;

case "1": { workbook.Saved = true; workbook.SaveCopyAs(excelPathName); }; break;

default:

return false;

}

xlApp.Visible = false;//是否在服务器打开

workbook.Close(true, Type.Missing, Type.Missing);

workbook = null;

xlApp.Quit();

xlApp = null;

return true;

}

catch (Exception)

{

return false;

}

/// <summary>

/// 从DataTable导出Excel,指定列别名

/// </summary>

/// <param name="dt">DataTable</param>

/// <param name="excelPathName">含Excel名称的保存路径在pathType＝1时有效，其它请赋值空字符串</param>

/// <param name="pathType">路径类型。只能取值：0客户自定义路径；1服务端定义路径，标识文件保存路径是服务端指定还是客户自定义路径及文件名</param>

/// <param name="colName">各列的列名List string </param>

/// <param name="sheetName">sheet1的名称为空字符串时保持默认名称</param>

/// <param name="TemplatePath">模版在项目服务器中路径为空字符串时表示无模版</param>

/// <param name="TemplateRow">模版中已存在数据的行数，无模版时请传入参数 0</param>

/// <returns>bool</returns>

public static bool ToExcelForDataTableC(System.Data.DataTable dt, string excelPathName, string pathType, List<string> colName, string sheetName, string TemplatePath, int TemplateRow, List<System.Data.DataTable> exDataTableList)

{

List<string> excludeColumn = new List<string>();

string excludeType = "0";

return ToExcelForDataTable(dt, excelPathName, pathType, colName, excludeColumn, excludeType, sheetName, TemplatePath, TemplateRow, exDataTableList);

}

/// <summary>

/// 从DataTable导出Excel,指定要排除的列

/// </summary>

/// <param name="dt">DataTable</param>

/// <param name="excelPathName">含Excel名称的保存路径在pathType＝1时有效，其它请赋值空字符串</param>

/// <param name="pathType">路径类型。只能取值：0客户自定义路径；1服务端定义路径，标识文件保存路径是服务端指定还是客户自定义路径及文件名</param>

/// <param name="excludeColumn">要显示/排除的列</param>

/// <param name="excludeType">显示/排除列方式 0为所有列 1指定的为要显示的列 2指定的为要排除的列</param>

/// <param name="sheetName">sheet1的名称为空字符串时保持默认名称</param>

/// <param name="TemplatePath">模版在项目服务器中路径为空字符串时表示无模版</param>

/// <param name="TemplateRow">模版中已存在数据的行数，无模版时请传入参数 0</param>

/// <returns>bool</returns>

public static bool ToExcelForDataTableE(System.Data.DataTable dt, string excelPathName, string pathType, List<string> excludeColumn, string excludeType, string sheetName, string TemplatePath, int TemplateRow, List<System.Data.DataTable> exDataTableList)

{

List<string> colName = new List<string>();

return ToExcelForDataTable(dt, excelPathName, pathType, colName, excludeColumn, excludeType, sheetName, TemplatePath, TemplateRow, exDataTableList);

}

/// <summary>

/// 从DataTable导出Excel，使用默认列名，不排除导出任何列

/// </summary>

/// <param name="dt">DataTable</param>

/// <param name="excelPathName">含Excel名称的保存路径在pathType＝1时有效，其它请赋值空字符串</param>

/// <param name="pathType">路径类型。只能取值：0客户自定义路径；1服务端定义路径，标识文件保存路径是服务端指定还是客户自定义路径及文件名</param>

/// <param name="sheetName">sheet1的名称为空字符串时保持默认名称</param>

/// <param name="TemplatePath">模版在项目服务器中路径为空字符串时表示无模版</param>

/// <param name="TemplateRow">模版中已存在数据的行数，无模版时请传入参数 0</param>

/// <returns>bool</returns>

public static bool ToExcelForDataTableZ(System.Data.DataTable dt, string excelPathName, string pathType, string sheetName, string TemplatePath, int TemplateRow, List<System.Data.DataTable> exDataTableList)

{

List<string> colName = new List<string>();

List<string> excludeColumn = new List<string>();

string excludeType = "0";

return ToExcelForDataTable(dt, excelPathName, pathType, colName, excludeColumn, excludeType, sheetName, TemplatePath, TemplateRow, exDataTableList);

}

#endregion

#region ExportExcelForModelList

/// <summary>

/// 从DataTable导出Excel,指定列别名,指定要排除的列

/// </summary>

/// <param name="dt">DataTable</param>

/// <param name="excelPathName">含Excel名称的保存路径在pathType＝1时有效，其它请赋值空字符串</param>

/// <param name="pathType">路径类型。只能取值：0客户自定义路径；1服务端定义路径，标识文件保存路径是服务端指定还是客户自定义路径及文件名</param>

/// <param name="colName">各列的列名List string </param>

/// <<param name="excludeColumn">要显示/排除的列</param>

/// <param name="excludeType">显示/排除列方式 0为所有列 1指定的为要显示的列 2指定的为要排除的列</param>

/// <param name="sheetName">sheet1的名称为空字符串时保持默认名称</param>

/// <param name="TemplatePath">模版在项目服务器中路径为空字符串时表示无模版</param>

/// <param name="TemplateRow">模版中已存在数据的行数，无模版时请传入参数 0</param>

/// <returns>bool</returns>

public static bool ToExcelForModelList<T>(List<T> md, string excelPathName, string pathType, List<string> colName, List<string> excludeColumn, string excludeType, string sheetName, string TemplatePath, int TemplateRow, List<System.Data.DataTable> exDataTableList)

{

System.Data.DataTable dt = ModelListToDataTable(md);

return ToExcelForDataTable(dt, excelPathName, pathType, colName, excludeColumn, excludeType, sheetName, TemplatePath, TemplateRow, exDataTableList);

}

/// <summary>

/// 从DataTable导出Excel,指定列别名

/// </summary>

/// <param name="dt">DataTable</param>

/// <param name="excelPathName">含Excel名称的保存路径在pathType＝1时有效，其它请赋值空字符串</param>

/// <param name="pathType">路径类型。只能取值：0客户自定义路径；1服务端定义路径，标识文件保存路径是服务端指定还是客户自定义路径及文件名</param>

/// <param name="colName">各列的列名List string </param>

/// <param name="sheetName">sheet1的名称为空字符串时保持默认名称</param>

/// <param name="TemplatePath">模版在项目服务器中路径为空字符串时表示无模版</param>

/// <param name="TemplateRow">模版中已存在数据的行数，无模版时请传入参数 0</param>

/// <returns>bool</returns>

public static bool ToExcelForModelListC<T>(List<T> md, string excelPathName, string pathType, List<string> colName, string sheetName, string TemplatePath, int TemplateRow, List<System.Data.DataTable> exDataTableList)

{

List<string> excludeColumn = new List<string>();

string excludeType = "0";

return ToExcelForModelList(md, excelPathName, pathType, colName, excludeColumn, excludeType, sheetName, TemplatePath, TemplateRow, exDataTableList);

}

/// <summary>

/// 从DataTable导出Excel,指定要排除的列

/// </summary>

/// <param name="dt">DataTable</param>

/// <param name="excelPathName">含Excel名称的保存路径在pathType＝1时有效，其它请赋值空字符串</param>

/// <param name="pathType">路径类型。只能取值：0客户自定义路径；1服务端定义路径，标识文件保存路径是服务端指定还是客户自定义路径及文件名</param>

/// <param name="excludeColumn">要显示/排除的列</param>

/// <param name="excludeType">显示/排除列方式 0为所有列 1指定的为要显示的列 2指定的为要排除的列</param>

/// <param name="sheetName">sheet1的名称为空字符串时保持默认名称</param>

/// <param name="TemplatePath">模版在项目服务器中路径为空字符串时表示无模版</param>

/// <param name="TemplateRow">模版中已存在数据的行数，无模版时请传入参数 0</param>

/// <returns>bool</returns>

public static bool ToExcelForModelListE<T>(List<T> md, string excelPathName, string pathType, List<string> excludeColumn, string excludeType, string sheetName, string TemplatePath, int TemplateRow, List<System.Data.DataTable> exDataTableList)

{

List<string> colName = new List<string>();

return ToExcelForModelList(md, excelPathName, pathType, colName, excludeColumn, excludeType, sheetName, TemplatePath, TemplateRow, exDataTableList);

}

/// <summary>

/// 从DataTable导出Excel，使用默认列名，不排除导出任何列

/// </summary>

/// <param name="dt">DataTable</param>

/// <param name="excelPathName">含Excel名称的保存路径在pathType＝1时有效，其它请赋值空字符串</param>

/// <param name="pathType">路径类型。只能取值：0客户自定义路径；1服务端定义路径，标识文件保存路径是服务端指定还是客户自定义路径及文件名</param>

/// <param name="sheetName">sheet1的名称为空字符串时保持默认名称</param>

/// <param name="TemplatePath">模版在项目服务器中路径为空字符串时表示无模版</param>

/// <param name="TemplateRow">模版中已存在数据的行数，无模版时请传入参数 0</param>

/// <returns>bool</returns>

public static bool ToExcelForModelListZ<T>(List<T> md, string excelPathName, string pathType, string sheetName, string TemplatePath, int TemplateRow, List<System.Data.DataTable> exDataTableList)

{

List<string> colName = new List<string>();

List<string> excludeColumn = new List<string>();

string excludeType = "0";

return ToExcelForModelList(md, excelPathName, pathType, colName, excludeColumn, excludeType, sheetName, TemplatePath, TemplateRow, exDataTableList);

}

#endregion

#region 从DataTable导出Excel； ToExcelModel实体传参

/// <summary>

/// 从DataTable导出Excel； ToExcelModel实体传参

/// </summary>

/// <param name="tem">ExcelHelper.ToExcelModel</param>

/// <returns></returns>

public static bool ToExcelForDataTable(ToExcelModel tem)

{

if (tem != null)

{

return ToExcelForDataTable(tem.DataTable, tem.excelPathName, tem.pathType, tem.colNameList, tem.excludeColumn, tem.excludeType, tem.sheetName, tem.TemplatePath, tem.TemplateRow, tem.exDataTableList);

}

else

{

return false;

}

#endregion

#region Model To DataTable

/// <summary>

/// 实体类转换成DataTable

/// </summary>

/// <param name="modelList">实体类列表</param>

/// <returns></returns>

public static System.Data.DataTable ModelListToDataTable<T>(List<T> modelList)

{

System.Data.DataTable dtReturn = new System.Data.DataTable();

// column names

PropertyInfo[] oProps = null;

if (modelList == null) return dtReturn;

foreach (T rec in modelList)

{

if (oProps == null)

{

oProps = ((Type)rec.GetType()).GetProperties();

foreach (PropertyInfo pi in oProps)

{

Type colType = pi.PropertyType;

if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()

== typeof(Nullable<>)))

{

colType = colType.GetGenericArguments()[0];

}

dtReturn.Columns.Add(new DataColumn(pi.Name, colType));

}

DataRow dr = dtReturn.NewRow();

foreach (PropertyInfo pi in oProps)

{

dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue

(rec, null);

}

dtReturn.Rows.Add(dr);

}

return dtReturn;

}

#endregion

#region 说明如何使用

* 功能：

* 1、将System.Data.DataTable数据导出到Excel文件

* 2、将Model(Entity)数据实体导出到Excel文件

* 完整调用：

* 1、ExcelHelper.ToExcelForDataTable(DataTable,excelPathName,pathType,colName,excludeColumn,excludeType,sheetName,TemplatePath,TemplateRow,exDataTableList);

* 2、ExcelHelper.ToExcelForModelList(Model,excelPathName,pathType,colName,excludeColumn,excludeType,sheetName,TemplatePath,TemplateRow,exDataTableList);

* 参数说明：

* 1、DataTable：DataSet.DataTable[0];数据表

* 2、Model：Model.Users users = new Model.Users(){...};数据实体

* 3、excelPathName：含Excel名称的保存路径在pathType＝1时有效。用户自定义保存路径时请赋值空字符串 ""。格式："E://456.xlsx"

* 4、pathType：路径类型。只能取值：0用户自定义路径，弹出用户选择路径对话框；1服务端定义路径。标识文件保存路径是服务端指定还是客户自定义路径及文件名，与excelPathName参数合用

* 5、colName：各列的列别名List string，比如：字段名为userName，此处可指定为"用户名"，并以此显示

* 6、excludeColumn：要显示/排除的列，指定这些列用于显示，或指定这些列用于不显示。倒低这些列是显示还是不显示，由excludeType参数决定

* 7、excludeType：显示/排除列方式。 0为显示所有列 1指定的是要显示的列 2指定的是要排除的列，与excludeColumn合用

* 8、sheetName：sheet1的名称，要使期保持默认名称请指定为空字符串 ""

* 9、TemplatePath：模版在项目服务器中路径例:tp.xlsx 。当为空字符串 "" 时表示无模版

* 10、TemplateRow：模版中已存在数据的行数，与TemplatePath合用，无模版时请传入参数 0

* 11、exDataTableList：扩展 DataTable List 用于当上下两个及以上DataTable数据类型不一至,但又都在同一列时使用,要求格式与参数第一个 DataTable的列名字段名一至,仅字段类型可不同

* 注意：

* 1、exDataTableList参数为一个List<System.Data.DataTable> 集合，当数据为 Model 时，可先调用 ExcelHelper.ModelListToDataTable(System.Data.DataTable dt)将Model转为System.Data.DataTable

#endregion

#region 从Excel导入数据到 Ms Sql

/// <summary>

/// 从Excel导入数据到 Ms Sql

/// </summary>

/// <param name="excelFile">Excel文件路径(含文件名)</param>

/// <param name="sheetName">sheet名</param>

/// <param name="DbTableName">存储到数据库中的数据库表名称</param>

/// <param name="columnType">对应表格的数据类型，如果为null，则为默认类型：double,nvarchar(500),datetime</param>

/// <param name="connectionString">连接字符串</param>

/// <returns></returns>

public static bool FromExcel(string excelFile, string sheetName, string DbTableName, List<string> columnType, string connectionString)

{

DataSet ds = new DataSet();

try

{

//获取全部数据

//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";

string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"; //此连

#region 知识扩展

//HDR=Yes，代表第一行是标题，不做为数据使用。HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES

//IMEX=0 只读模式

//IMEX=1 写入模式

//IMEX=2 可读写模式

#endregion

#region 命名执行

using (OleDbConnection conn = new OleDbConnection(strConn))

{

conn.Open();

string strExcel = "";

OleDbDataAdapter myCommand = null;

strExcel = string.Format("select * from [{0}$]", sheetName);

myCommand = new OleDbDataAdapter(strExcel, strConn);

myCommand.Fill(ds, sheetName);

#region 数据库表是否存在的 T-SQL 检测语句准备

//如果目标表不存在则创建

string strSql = string.Format("if object_id('{0}') is null create table {0}(", DbTableName != "" ? DbTableName : sheetName);

if (columnType != null && columnType.Count > 0)

{

#region 手动指定定每个字段的数据类型

//指定数据格式,要求一一对应

for (int i = 0; i < ds.Tables[0].Columns.Count; i++)

{

System.Data.DataColumn c = ds.Tables[0].Columns[i];

strSql += string.Format("[{0}] {1},", c.ColumnName, columnType[i]);

}

#endregion

}

else

{

#region 使用默认数据类型

foreach (System.Data.DataColumn c in ds.Tables[0].Columns)

{

//使用默认格式：只有double,DateTime,String三种类型

switch (c.DataType.ToString())

{

case "DateTime":

{

strSql += string.Format("[{0}] DateTime,", c.ColumnName);

}; break;

case "Double":

{

strSql += string.Format("[{0}] double,", c.ColumnName);

}; break;

default:

strSql += string.Format("[{0}] nvarchar(500),", c.ColumnName);

break;

}

#endregion

}

strSql = strSql.Trim(',') + ")";

#endregion

#region 执行 T-SQL 如果数据库表不存在则新建表，如果存在则不新建

using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))

{

sqlconn.Open();

System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();

command.CommandText = strSql;

command.ExecuteNonQuery();

sqlconn.Close();

}

#endregion

#region 向数据库表插入数据

using (System.Data.SqlClient.SqlBulkCopy sbc = new System.Data.SqlClient.SqlBulkCopy(connectionString))

{

sbc.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);

sbc.BatchSize = 100;//每次传输的行数

sbc.NotifyAfter = 100;//进度提示的行数

sbc.DestinationTableName = DbTableName != "" ? DbTableName : sheetName;//数据库表名表名

sbc.WriteToServer(ds.Tables[0]);

}

#endregion

}

#endregion

}

catch (Exception ex)

{

return false;

}

return true;

}

#region 进度显示

/// <summary>

/// 进度显示

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

static void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)

{

e.RowsCopied.ToString();

}

#endregion

}

public class ToExcelModel

{

#region ToExcelModel自动属性

/// <summary>

/// 数据表

/// </summary>

public System.Data.DataTable DataTable { get; set; }

/// <summary>

/// 含Excel名称的保存路径在pathType＝1时有效。用户自定义保存路径时请赋值空字符串 ""。格式："E://456.xlsx"

/// </summary>

public string excelPathName { get; set; }

/// <summary>

/// 路径类型。只能取值：0用户自定义路径，弹出用户选择路径对话框；1服务端定义路径。标识文件保存路径是服务端指定还是客户自定义路径及文件名，与excelPathName参数合用

/// </summary>

public string pathType { get; set; }

/// <summary>

/// 各列的列别名List string，比如：字段名为userName，此处可指定为"用户名"，并以此显示

/// </summary>

public List<string> colNameList { get; set; }

/// <summary>

/// 要显示/排除的列，指定这些列用于显示，或指定这些列用于不显示。倒低这些列是显示还是不显示，由excludeType参数决定

/// </summary>

public List<string> excludeColumn { get; set; }

/// <summary>

/// 显示/排除列方式。 0为显示所有列 1指定的是要显示的列 2指定的是要排除的列，与excludeColumn合用

/// </summary>

public string excludeType { get; set; }

/// <summary>

/// sheet1的名称，要使期保持默认名称请指定为空字符串 ""

/// </summary>

public string sheetName { get; set; }

/// <summary>

/// 模版在项目服务器中路径例:tp.xlsx 。当为空字符串 "" 时表示无模版

/// </summary>

public string TemplatePath { get; set; }

/// <summary>

/// 模版中已存在数据的行数，与TemplatePath合用，无模版时请传入参数 0

/// </summary>

public int TemplateRow { get; set; }

/// <summary>

/// 扩展 DataTable List 用于当上下两个及以上DataTable数据类型不一至,但又都在同一列时使用,要求格式与参数第一个 DataTable的列名字段名一至,仅字段类型可不同

/// </summary>

public List<System.Data.DataTable> exDataTableList { get; set; }

#endregion

}

public class FromExcelModel

{

/// <summary>

/// Excel文件路径(含文件名)

/// </summary>

public string excelFile { get; set; }

/// <summary>

/// sheet名<

/// </summary>

public string sheetName { get; set; }

/// <summary>

/// 存储到数据库中的数据库表名称

/// </summary>

public string DbTableName { get; set; }

/// <summary>

/// 对应表格的数据类型，如果为null，则为默认类型：double,nvarchar(500),datetime

/// </summary>

public List<string> columnTypeList { get; set; }

/// <summary>

/// 连接字符串 server=serverip;database=databasename;uid=username;pwd=password;

/// </summary>

public string connectionString { get; set; }

}

access 笔记

使用以下语法设置“控件来源”：

推荐阅读