private void btn_Export_Click(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value; //声明object缺省值
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(txt_Path.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet; //声明工作表
Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application(); //实例化Access对象
worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[cbox_SheetName.Text]); //获取选择的工作表
worksheet.Move(workbook.Sheets[1], missing); //将选择的工作表作为第一个工作表
object P_obj_Name = (object)worksheet.Name; //获取工作表名称
excel.DisplayAlerts = false; //设置Excel保存时不显示对话框
workbook.Save(); //保存工作簿
CloseProcess("EXCEL"); //关闭所有Excel进程
object P_obj_Excel = (object)txt_Path.Text; //记录Excel文件路径
try
{
access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库
//将Excel指定工作表中的数据导入到Access中
access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_obj_Excel, true, missing, missing);
access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll); //关闭并保存Access数据库文件
CloseProcess("MSACCESS"); //关闭所有Access数据库进程
MessageBox.Show("已经将Excel的" + cbox_SheetName.Text + "工作表中的数据导入到Access数据库中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show("Access数据库中已经存在该表！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
private void btn_Export_Click(object sender, EventArgs e)
{
object missing = System.Reflection.Missing.Value;//声明object缺省值
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(txt_Path.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet;//声明工作表
Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application();//实例化Access对象
worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[cbox_SheetName.Text]);//获取选择的工作表
worksheet.Move(workbook.Sheets[1], missing);//将选择的工作表作为第一个工作表
object P_obj_Name = (object)worksheet.Name;//获取工作表名称
excel.DisplayAlerts = false;//设置Excel保存时不显示对话框
workbook.Save();//保存工作簿
CloseProcess("EXCEL");//关闭所有Excel进程
object P_obj_Excel = (object)txt_Path.Text;//记录Excel文件路径
try
{
access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库
//将Excel指定工作表中的数据导入到Access中
access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_obj_Excel, true, missing, missing);
access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);//关闭并保存Access数据库文件
CloseProcess("MSACCESS");//关闭所有Access数据库进程
MessageBox.Show("已经将Excel的" + cbox_SheetName.Text + "工作表中的数据导入到Access数据库中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
{
MessageBox.Show("Access数据库中已经存在该表！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
public void GetProcessId_Test()
{
var microsoftAccess = new Microsoft.Office.Interop.Access.Application();
microsoftAccess.OpenCurrentDatabase(@"D:\MSAccessDatabase\MSAccessDatabase.accdb");
var myName = microsoftAccess.Run("GetName");
int id;
GetWindowThreadProcessId(microsoftAccess.hWndAccessApp(), out id);
var result = Process.GetProcessById(id);
Assert.IsNotNull(result);
Console.WriteLine(result.Id);
if (microsoftAccess != null)
{
microsoftAccess.Quit();
Marshal.ReleaseComObject(microsoftAccess);
microsoftAccess = null;
}
}
private bool ExportToExtrato()
{
// https://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx
var retorno = true;
using (var ctx = new MoneyBinEntities()) {
ctx.spExportToAccessExtrato(_saveAs);
}
MoneyBinDB.ExportToExtrato(_saveAs);
var tempFile = Path.Combine(Path.GetDirectoryName(_saveAs),
Path.GetRandomFileName() + Path.GetExtension(_saveAs));
var app = new Microsoft.Office.Interop.Access.Application {
Visible = false
};
try {
app.CompactRepair(_saveAs, tempFile, false);
var temp = new FileInfo(tempFile);
temp.CopyTo(_saveAs, true);
temp.Delete();
}
catch (Exception e) {
MessageBox.Show(e.Message, Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
retorno = false;
//throw;
}
finally {
app.Quit();
}
return(retorno);
}
private void ImportDataToAccess(string P_str_Excel, string P_str_SheetName)
{
object missing = System.Reflection.Missing.Value; //声明object缺省值
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(P_str_Excel, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet; //声明工作表
Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application(); //实例化Access对象
worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[P_str_SheetName]); //获取选择的工作表
worksheet.Move(workbook.Sheets[1], missing); //将选择的工作表作为第一个工作表
object P_obj_Name = (object)worksheet.Name; //获取工作表名称
excel.DisplayAlerts = false; //设置Excel保存时不显示对话框
workbook.Save(); //保存工作簿
CloseProcess("EXCEL"); //关闭所有Excel进程
try
{
access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库
//将Excel指定工作表中的数据导入到Access中
access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_str_Excel, true, missing, missing);
access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll); //关闭并保存Access数据库文件
CloseProcess("MSACCESS"); //关闭所有Access数据库进程
}
catch
{
MessageBox.Show("Access数据库中已经存在" + P_str_SheetName + "表！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
CloseProcess("MSACCESS");//关闭所有Access数据库进程
}
}
static void Main(string[] args)
{
// this code requires the following COM reference in the project:
// Microsoft Access 14.0 Object Library
//
var objAccess = new Microsoft.Office.Interop.Access.Application();
objAccess.Visible = false;
objAccess.OpenCurrentDatabase(@"C:\Users\Public\Database1.accdb");
string formName = "MembersForm";
Console.WriteLine(String.Format("The form [{0}] contains the following controls:", formName));
objAccess.DoCmd.OpenForm(formName, Microsoft.Office.Interop.Access.AcFormView.acDesign);
Microsoft.Office.Interop.Access.Form frm = objAccess.Forms[formName];
foreach (Microsoft.Office.Interop.Access.Control ctl in frm.Controls)
{
Console.WriteLine();
Console.WriteLine(String.Format(" [{0}]", ctl.Name));
Console.WriteLine(String.Format(" {0}", ctl.GetType()));
}
objAccess.DoCmd.Close(Microsoft.Office.Interop.Access.AcObjectType.acForm, formName);
objAccess.CloseCurrentDatabase();
objAccess.Quit();
Console.WriteLine();
Console.WriteLine("Done.");
}
static void Main(string[] args)
{
string reportName = "Report1";
string controlName = "Label0";
//
// Note: This project has the following COM reference defined:
//
// Microsoft Access 14.0 Object Library
//
var accessApp = new Microsoft.Office.Interop.Access.Application();
accessApp.OpenCurrentDatabase(@"C:\__tmp\testData.accdb");
accessApp.DoCmd.OpenReport(reportName, Microsoft.Office.Interop.Access.AcView.acViewDesign);
Microsoft.Office.Interop.Access.Report rpt = accessApp.Reports[reportName];
int ctlWidth = rpt.Controls[controlName].Width;
Console.WriteLine("control \"" + controlName + "\" in report \"" + reportName + "\" has a .Width value of " + ctlWidth);
accessApp.DoCmd.Close(Microsoft.Office.Interop.Access.AcObjectType.acReport, reportName, Microsoft.Office.Interop.Access.AcCloseSave.acSaveNo);
accessApp.Quit();
// wait for a keypress before terminating
Console.ReadKey();
}
public static void Close()
{
if (_oAccess != null)
{
_oAccess.CloseCurrentDatabase();
_oAccess.Quit();
}
}
public void CloseDatabase()
{
if (null != oAccess.CurrentDb())
{
oAccess.CloseCurrentDatabase();
oAccess.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
GC.Collect();
foreach (Process pr in Process.GetProcessesByName("MSACCESS"))
{
pr.Kill();
}
}
}
public void GetName()
{
var microsoftAccess = new Microsoft.Office.Interop.Access.Application();
microsoftAccess.Visible = true;
microsoftAccess.OpenCurrentDatabase(@"D:\MSAccessDatabase.accdb", false);
var myName = microsoftAccess.Run("GetName");
Console.WriteLine($"My Name: {myName}");
Thread.Sleep(3000);
microsoftAccess.CloseCurrentDatabase();
microsoftAccess.Quit();
}
private static void CloseAccess()
{
if (_oAccess != null)
{
try
{
_oAccess.Application.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
_oAccess.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone); //in case Access is still alive
_oAccess = null;
}
catch
{
try //in case Access is still alive} }
{ _oAccess.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone); }
catch { }
}
finally
{ _oAccess = null; }
}
}
public void runApp(string databaseName, string function)
{
VBA.VBComponent f = null;
VBA.VBComponent f2 = null;
Microsoft.Office.Interop.Access.Application app = null;
object Missing = System.Reflection.Missing.Value;
Object tempObject = null;
try
{
app = new Microsoft.Office.Interop.Access.Application();
app.Visible = true;
app.OpenCurrentDatabase(databaseName, false, "");
//Step 1: Programatically create a new temporary class module in the target Access file, with which to call the target function in the Access database
//Create a Guid to append to the object name, so that in case the temporary class and module somehow get "stuck",
//the temp objects won't interfere with other objects each other (if there are multiples).
string tempGuid = Guid.NewGuid().ToString("N");
f = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_ClassModule);
//We must set the Instancing to 2-PublicNotCreatable
f.Properties.Item("Instancing").Value = 2;
f.Name = "TEMP_CLASS_" + tempGuid;
f.CodeModule.AddFromString(
"Public Sub TempClassCall()\r\n" +
" Call " + function + "\r\n" +
"End Sub\r\n");
//Step 2: Append a new standard module to the target Access file, and create a public function to instantiate the class and return it.
f2 = app.VBE.ActiveVBProject.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
f2.Name = "TEMP_MODULE";
f2.CodeModule.AddFromString(string.Format(
"Public Function instantiateTempClass_{0}() As Object\r\n" +
" Set instantiateTempClass_{0} = New TEMP_CLASS_{0}\r\n" +
"End Function"
, tempGuid));
//Step 3: Get a reference to a new TEMP_CLASS_* object
tempObject = app.Run("instantiateTempClass_" + tempGuid, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing, ref Missing);
//Step 4: Call the method on the TEMP_CLASS_* object.
Microsoft.VisualBasic.Interaction.CallByName(tempObject, "TempClassCall", Microsoft.VisualBasic.CallType.Method);
}
catch (COMException e)
{
MessageBox.Show("A VBA Exception occurred in file:" + e.Message);
}
catch (Exception e)
{
MessageBox.Show("A general exception has occurred: " + e.StackTrace.ToString());
}
finally
{
//Clean up
if (f != null)
{
app.VBE.ActiveVBProject.VBComponents.Remove(f);
Marshal.FinalReleaseComObject(f);
}
if (f2 != null)
{
app.VBE.ActiveVBProject.VBComponents.Remove(f2);
Marshal.FinalReleaseComObject(f2);
}
if (tempObject != null)
{
Marshal.FinalReleaseComObject(tempObject);
}
if (app != null)
{
//Step 5: When you close the database, you call Application.Quit() with acQuitSaveNone, so none of the VBA code you just created gets saved.
app.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveNone);
Marshal.FinalReleaseComObject(app);
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
public void CopyDataTable(string target,string newName)
{
Microsoft.Office.Interop.Access.Application app =
new Microsoft.Office.Interop.Access.Application();
string path = AppDomain.CurrentDomain.BaseDirectory;
app.OpenCurrentDatabase(path+"optimization_db.accdb",false,"");
app.DoCmd.CopyObject(
Type.Missing,
target +newName,
Microsoft.Office.Interop.Access.AcObjectType.acTable,
target+"data");
app.CloseCurrentDatabase();
app.Quit();
}
private void ImportDataToAccess(string P_str_Excel, string P_str_SheetName)
{
object missing = System.Reflection.Missing.Value;//声明object缺省值
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
//打开Excel文件
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(P_str_Excel, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet;//声明工作表
Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application();//实例化Access对象
worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[P_str_SheetName]);//获取选择的工作表
worksheet.Move(workbook.Sheets[1], missing);//将选择的工作表作为第一个工作表
object P_obj_Name = (object)worksheet.Name;//获取工作表名称
excel.DisplayAlerts = false;//设置Excel保存时不显示对话框
workbook.Save();//保存工作簿
CloseProcess("EXCEL");//关闭所有Excel进程
try
{
access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库
//将Excel指定工作表中的数据导入到Access中
access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_str_Excel, true, missing, missing);
access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);//关闭并保存Access数据库文件
CloseProcess("MSACCESS");//关闭所有Access数据库进程
}
catch
{
MessageBox.Show("Access数据库中已经存在" + P_str_SheetName + "表！", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
CloseProcess("MSACCESS");//关闭所有Access数据库进程
}
}
