如何自动将数据发送到Excel

I've been doing some research on how to have sql server create invoices or reports automatically given a specific time, similar to a scheduled job. I want to be able to use an excel sheet which has the invoice template and update my sql server update the fields with the given data (Customer Name, account Id, sales tax, etc). I came across SSRS which does reports, but I dont know if that will do data processing for me. The type of processing I need is simple, gallons used * price/gallon * sales tax. I have very little experience with sql server, from what I know, its not very convenient to these algorithms in sql so I rather have excel do it for me. So again, is there a way to have sql run an automated query that updates an excel spread sheet? I have imported data to excel via csv file and vice versa, but the thing that is confusing me in this case is that my excel spreadsheet itself is an invoice template with a company logo so I can't just import data to the template. Does anyone have any suggestions? Thanks.

我一直在研究如何让sql server在给定特定时间内自动创建发票或报告,类似于预定作业。我希望能够使用具有发票模板的excel表并更新我的sql server更新具有给定数据的字段(客户名称,帐户ID,销售税等)。我遇到了报告的SSRS,但我不知道是否会为我做数据处理。我需要的加工类型很简单,加仑使用*价格/加仑*销售税。我对sql server的经验很少,据我所知,在sql中对这些算法不太方便所以我宁愿为我做excel。那么,有没有办法让sql运行一个更新excel电子表格的自动查询?我已经通过csv文件将数据导入excel,反之亦然,但在这种情况下令我困惑的是我的Excel电子表格本身是一个带有公司徽标的发票模板,所以我不能只将数据导入到模板中。有没有人有什么建议?谢谢。

3 个解决方案

#1

I think you should go with SSRS reports . Report server in SSRS can handle the report processing and Scheduling and delivering of files .U can get the reports in most of the formats(Excel ,Word ,pdf etc ) .You can go through the below link to understand how to schedule and deliver your reports http://msdn.microsoft.com/en-us/library/ms156297(v=sql.105).aspx

我认为你应该使用SSRS报告。 SSRS中的报表服务器可以处理报表处理和文件的调度和交付。大多数格式(Excel,Word,pdf等)都可以获取报表。您可以通过以下链接了解如何安排和交付您的报表。报告http://msdn.microsoft.com/en-us/library/ms156297(v=sql.105).aspx

Regarding the logic such as gallons=*price/gallon*sales . These logics can easily be applied to the columns in ssrs reports using expressions such as

关于加仑= *价格/加仑*销售等逻辑。使用诸如表达式之类的表达式,可以轻松地将这些逻辑应用于ssrs报告中的列

For the column gallons used, the expression will be

对于使用的列加仑,表达式将是

=(Fields!price.Value/Fields!gallons.Value)*Fields!Sales.Value

#2

you could use this application tier:

你可以使用这个应用程序层:

DATABASE MANAGEMENT SYSTEM
           |
           V
     PROVIDE DATA TO
           |
           V
    YOUR APPLICATION
           |
           V
       WOULD USE
           |
           V
[OPEN XML SDK 2.0 For excel] -  http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/06/02/4730.aspx
           |
           V
  TO GENERATE EXCEL FILES 
that contains data that was provided 
     by your DBMS to
    your application

[1]:

#3

The easiest method would be to re-create the template within an SSRS report (embedding the company logo in the report, for example), create a subscription for the report, and specify Excel as the output format for the subscription.

最简单的方法是在SSRS报告中重新创建模板(例如,在报告中嵌入公司徽标),为报告创建订阅,并指定Excel作为订阅的输出格式。

The calculations you describe are very simple. If your resultsets are small, embedding the calculations in the report may prove less challenging. However, if you're working with large datasets, it's best to include the calculations in the query executing on the database server. If you can figure out how to write the query for your report to begin with, figuring out simple calculations won't be difficult.

您描述的计算非常简单。如果您的结果集很小,那么在报告中嵌入计算可能不那么具有挑战性。但是,如果您正在处理大型数据集,则最好将计算包含在数据库服务器上执行的查询中。如果您可以弄清楚如何为报告开始编写查询,那么计算简单的计算并不困难。

#1