问题描述
我正在寻找在ColdFusion 10中编写此报告的最佳方式是什么。
基本上，它包括读取数据从MSSQL数据库中的两个表中，根据在特定列上找到的条件应用一些条件，将数据填充到excel中并通过电子邮件发送出去。我之前写过报告，就像从表中读取数据一样简单，使用POIUTILITY创建电子表格并通过电子邮件发送，但由于条件，这一点有所不同。我已经阅读了关于使用cfspreadsheet而不是poiutility，但是在这种情况下我不太确定。
这里有一个例子，表布局看起来像我可以把它：
Table1
ID |名称|地址
表2
ID | AppsInfo
在excel报告中，我将有一个数据布局类似于以下：
ID |名称|地址| AppAlpha | AppBravo | AppDelta |
12345 | John | 123 Ave |是|是|否|我的问题是，table2上的AppsInfo列包含每个ID的xml格式：
b
$ b < start>
< id =12345>
< AppName =AppDelta>
< AppName =AppBravo
< / id>
< / start>
在我的Excel表格的每一行中，数据将被写出，如果appsinfo列为具体ID包含一个应用程序，然后在相应的行上将其列为yes，如果它不包含该应用程序，则否。
从上面的布局示例，最终格式的excel将以这种方式显示：
ID |名称|地址| AppAlpha | AppBravo | AppDelta |
12345 | John | 123 Ave |否|是|是|
等等。
这是开发这个最好的方法，所以如果它包含table2 appsinfo列上的特定应用程序，它会将其写为Yes，如果没有，对相应行上的每个ID写入否？
解决方案 Leigh打败了我的答案，但我有一个类似的解决方案使用SQL的XML能力。 XML可能是一个痛苦的工作，但SQL Server有一个使用XML的语法。
SELECT t1.ID
，t1.Name
，t1.Address
- ，t2.AppsInfo
，CASE WHEN t2.AppsInfo.exist（'// start / id / AppName [@ value =AppAlpha]'）= 1 THEN'Yes'ELSE'No'END As AppAlpha
，CASE WHEN t2.AppsInfo.exist（'// start / id / AppName [@ value =AppBravo]'）= 1 THEN'Yes'ELSE'No'END ASBravo
，CASE .AppsInfo.exist（'// start / id / AppName [@ value =AppDelta]'）= 1 THEN'Yes'ELSE'No'End as AppDelta
FROM＃table1 t1
INNER JOIN ＃table2 t2 ON t1.ID = t2.ID
我的设置为：
创建TABLE＃table1（ID int，name varchar（100），Address varchar（200））
创建TABLE＃ AppsInfo xml）
INSERT INTO＃table1（ID，Name，Address）
SELECT 1，'John'，'123 Sesame St'
UNION ALL
SELECT 2 ，'Jim'，'42 Douglas Ln'
UNION ALL
SELECT 3，'Jack'，'1 Elm St'
UNION ALL
SELECT 4，'Joe' 21 Jump St'
INSERT INTO＃table2（ID，AppsInfo）
SELECT 1，'< start>< id value =1>< AppName value =AppDelta >< AppName value =AppBravo/>< / id>< / start>'
UNION ALL
SELECT 2，'< start>< id value = 2"< AppName value =AppAlpha/>< AppName value =AppDelta/>< AppName value =AppBravo/>< / id>< / start> $ b UNION ALL
SELECT 3，'< start>< id value =3>< AppName value =AppBravo/>< / id>< / start& $ b UNION ALL
SELECT 4，'< start>< id value =4>< AppName />< / id>< / start>'
我还必须稍微修改XML以使其有效。我的解决方案和Leigh之间唯一的真正的区别是，我使用SQL CASE输出是/否，而不是在Excel中执行。任何一种方式都可以工作。
您使用的实际XML的结构可能会影响这些查询。你能在这里放一个样本块吗？和你使用什么版本的SQL Server？ SQL 2000的XML语法与SQL 2005+有点不同，并且在SQL 2000中没有XML数据类型。
要从查询构建电子表格，的Adobe文档。
I'm looking to see what the best way in writing this report which I'm developing in ColdFusion 10 would be.
Basically, it consists of reading data from two tables in a MSSQL database, applying some conditions based on what it finds on a specific column, populating the data into excel and sending it out via email. I've written reports before where it's as simple as reading data from the table, using POIUTILITY to create the spreadsheet and sending it via email, but this one is a little different because of the conditions. I've read about using cfspreadsheet instead of poiutility, but I'm not so sure in this case.
Here’s an example of what the tables layout looks like as easy as I can put it:
Table1
ID | Name | Address
Table2
ID | AppsInfo
In the excel report, I’ll have one sheet where the data is laid out similar to the following:
ID | Name | Address | AppAlpha | AppBravo | AppDelta |
12345 | John | 123 Ave | Yes | Yes | No |
My problem is, the AppsInfo column on table2 contains xml formatting for each ID :
<start>
<id="12345">
<AppName="AppDelta">
<AppName="AppBravo"
</id>
</start>
In each row on my excel sheet, the data would be written out such that if appsinfo column for a specific ID contains an app, then list it as yes on the corresponding row, if it does not contain that app, then No.
So going from layout example above, the final format of excel would display this way:
ID | Name | Address | AppAlpha | AppBravo | AppDelta |
12345 | John | 123 Ave | No | Yes | Yes |
and so on and so on for each ID….
What would be the best way in developing this so if it contains specific apps on table2 appsinfo column, it will write it as Yes and if it does not, write no for each ID on the corresponding row?
解决方案 Leigh beat me to the answer, but I had a similar solution using the XML abilities of SQL. XML can be a pain to work with, but SQL Server has a syntax for working with XML.
SELECT t1.ID
, t1.Name
, t1.Address
--, t2.AppsInfo
, CASE WHEN t2.AppsInfo.exist('//start/id/AppName[@value="AppAlpha"]') = 1 THEN 'Yes' ELSE 'No' END AS AppAlpha
, CASE WHEN t2.AppsInfo.exist('//start/id/AppName[@value="AppBravo"]') = 1 THEN 'Yes' ELSE 'No' END AS AppBravo
, CASE WHEN t2.AppsInfo.exist('//start/id/AppName[@value="AppDelta"]') = 1 THEN 'Yes' ELSE 'No' END AS AppDelta
FROM #table1 t1
INNER JOIN #table2 t2 ON t1.ID = t2.ID
My setup was:
Create TABLE #table1 ( ID int, Name varchar(100), Address varchar(200) )
Create TABLE #table2 ( ID int, AppsInfo xml )
INSERT INTO #table1 (ID, Name, Address)
SELECT 1, 'John', '123 Sesame St'
UNION ALL
SELECT 2, 'Jim', '42 Douglas Ln'
UNION ALL
SELECT 3, 'Jack', '1 Elm St'
UNION ALL
SELECT 4, 'Joe', '21 Jump St'
INSERT INTO #table2 (ID, AppsInfo)
SELECT 1, '<start><id value="1"><AppName value="AppDelta"/><AppName value="AppBravo"/></id></start>'
UNION ALL
SELECT 2, '<start><id value="2"><AppName value="AppAlpha"/><AppName value="AppDelta"/><AppName value="AppBravo"/></id></start>'
UNION ALL
SELECT 3, '<start><id value="3"><AppName value="AppBravo"/></id></start>'
UNION ALL
SELECT 4, '<start><id value="4"><AppName/></id></start>'
I also had to modify the XML slightly to make it valid. The only real difference between my solution and Leigh's is that I'm using SQL CASEs to output the Yes/No instead of doing that in Excel. Either way would work just as well.
The structure of the actual XML you are using could have an effect on these queries, though. Are you able to put a sample block of it here? And what version of SQL Server are you using? The XML syntax for SQL 2000 is a bit different than SQL 2005+ and there's not an XML datatype in SQL 2000.
To build the spreadsheet from the query, Ray Camden has a good article. And there's also the Adobe docs for cfspreadsheet.
这篇关于写这个基于ColdFusion的报表的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！