使用C#以编程方式将查询表从MS Access导出到Excel [英] Programmatically export query table from MS Access to Excel using C#

问题描述

今天，我有一个软件，可以从Web上下载xml数据并将其导出到相应表中的MS Access DB.

Today i have a software which downloads xml data from the web and exports it to a MS Access DB in appropriate tables.

在MS Access DB中，我使用表创建了一个查询，以使列和行符合我希望在Excel中显示的样子.

In MS Access DB i have created a query using the tables to make columns and rows as i want it to look like in Excel.

当我右键单击新查询表并选择导出到Excel"时，我能够从该查询创建Excel文件.

When i right-click on my new query table, and chose Export to Excel, i'm able to create an Excel file from that query.

基本上我想做的是扩展我的软件，以便可以使用C#以编程方式将查询导出到Excel.

Basically what i want to do is extend my software so that i can export the query to Excel programmatically with C#.

我该怎么做?

---------------------------

我也想解决的其他有关方面.

other side things related i also would like to solve.

我在左侧数字上方得到绿色三角形，请检查图像 postimg.org/image/t6tvfw2cz 如何从c#中删除.

是否可以使用C#代码格式化表格外观并进行设计?

使用C#代码在标头中添加过滤器是否可行? – 15小时前的法力值

I'm getting green triangles above the numbers on left side, check the image postimg.org/image/t6tvfw2cz how can i remove from c#.

Is it possible to format the table look and design with c# code?

Is it poosible to add filters to the headers with c# code? – Mana 15 hours ago

推荐答案

应该执行以下操作:

private static void ExportQuery(string databaseLocation, string queryNameToExport, string locationToExportTo)
{
    var application = new Application();
    application.OpenCurrentDatabase(databaseLocation);
    application.DoCmd.TransferSpreadsheet(AcDataTransferType.acExport, AcSpreadSheetType.acSpreadsheetTypeExcel12,
                                          queryNameToExport, locationToExportTo, true);
    application.CloseCurrentDatabase();
    application.Quit();
    Marshal.ReleaseComObject(application);
}

您会这样称呼它:

ExportQuery(@"C:\blah\blah.accdb", "myQuery", @"C:\blah\blah.xlsx");

请确保添加以下using语句:

Be sure to add these using statements:

using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Access;

这篇关于使用C#以编程方式将查询表从MS Access导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！