用excel的QueryTable来直接查询数据库，但是必须引用com组件

最新推荐文章于 2020-07-24 07:13:06 发布

zaj

最新推荐文章于 2020-07-24 07:13:06 发布

阅读量1.1k

点赞数

文章标签： excel 数据库 database sql string c

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
namespace ConsoleApplication18
{
class Program
{
static void Main(string[] args)
{
ExportDataToExcel("Provider=SQLOLEDB.1;sever=localhost;uid=sa;password=***;database=master;",
"select * from sysobjects",@"c:/testOle.xls","sysobjects");
}
/// <summary>
/// 直接导出数据到excel
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="sql">查询语句</param>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">表名</param>
static void ExportDataToExcel(string connectionString,string sql,string fileName,string sheetName)
{
Excel.Application app = new Excel.ApplicationClass();
Excel.Workbook wb = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
Excel.Worksheet ws = wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
ws.Name = sheetName;
try
{
Excel.QueryTable qt = ws.QueryTables.Add("OLEDB;" + connectionString,
ws.get_Range("A1", Missing.Value), sql);
qt.Refresh(false);//是否异步查询
}
catch (Exception ex)
{
string str = ex.Message;
}
finally
{
wb.Saved = true;
wb.SaveCopyAs(fileName);//保存
app.Quit();//关闭进程
}
}
}
}