I have experienced numerous cases where I wanted to dump database information into an Excel sheet for the users. Usually, the application that the data is entered through is a ColdFusion web application, so it makes sense to dump the data directly from the web-based application. To do this I have used two basic techniques. The first technique is to generate a ColdFusion page which returns an Excel file as the result so that users can save the file. The second technique I use is to dump the data to a web page that can be accessed inside of Excel using the import data from a web query method. Below I will give some examples of both methods.
Technique #1, Method #1
Christopher
<cfsetting enablecfoutputonly="yes">
<cfset tab="CHR(9)">
<cfquery name="get_Data" datasource="Deg">
SELECT username,EMail,
FROM Tbl_Data
</cfquery>
<cfheader name="Content-Disposition" value="inline; filename=export.xls">
<cfcontent type="application/msexcel">
<cfoutput query="get_Data">
#Currentrow##TAB##username##TAB##EMail##chr(13)#
</cfoutput>
When you need to perform a SQL select and do substitution at the same time (for example if you would like to remove nulls from the selection) you can use the CASE command. Here is an example.
SELECT
RTRIM(LTRIM(AssemblyNumber)) AS AssNum,
RTRIM(LTRIM(PartNumber)) AS PrtNum,
CASE WHEN ItemNotes IS NULL THEN '' ELSE RTRIM(LTRIM(ItemNotes)) END AS ItemNotes,
CASE WHEN PATINDEX('%[^0-9]%', ISNULL(ItemQuantity, '*')) > 0 THEN 1 ELSE RTRIM(LTRIM(ItemQuantity)) END AS ItemQuantity,
CASE WHEN ItemHeight IS NULL THEN '' ELSE RTRIM(LTRIM(ItemHeight)) END AS ItemHeight,
CASE WHEN ItemWidth IS NULL THEN '' ELSE RTRIM(LTRIM(ItemWidth)) END AS ItemWidth,
CASE WHEN ItemLength IS NULL THEN '' ELSE RTRIM(LTRIM(ItemLength)) END AS ItemLength,
CASE WHEN ItemMaterial IS NULL THEN '' ELSE RTRIM(LTRIM(ItemMaterial)) END AS ItemMaterial,
CASE WHEN ShapeIH IS NULL THEN '' ELSE RTRIM(LTRIM(ShapeIH)) END AS ShapeIH,
CASE WHEN ShapeIW IS NULL THEN '' ELSE RTRIM(LTRIM(ShapeIW)) END AS ShapeIW,
CASE WHEN ShapeIL IS NULL THEN '' ELSE RTRIM(LTRIM(ShapeIL)) END AS ShapeIL
FROM zzzImportManualBOMs
ORDER BY
RTRIM(LTRIM(REPLACE(REPLACE(AssemblyNumber, '.', ''), '_', ''))),
RTRIM(LTRIM(REPLACE(REPLACE(PartNumber, '.', ''), '_', '')))
I have a small problem with many of the web-based, database-driven apps that I make for my company. The problem is that the users want to have a drop-down box with all the possible user names (or person’s names) when they need to select a persons’ name, which is reasonable until there are multiple selections like this on one page. Currently there are around 1000 users at my company, and we have some pages with up 40 select boxes for user names. That’s over 40,000 lines of code just to display the user select boxes, which means the page size would be well in excess of 4 megabytes.
This page has been moved …