CF9 Question: Do Spreadsheet Functions Support "Blank Cells"?

I saw a question about spreadsheets this week that had me scratching my head. When you create a new worksheet in Excel, all of the cells are blank. So if you enter a long string in any cell, the text will overflow into the adjacent cell. (If the adjacent cell is blank.)

However, if you do something similar in ColdFusion 9, the text does not overflow into the next cell. Notice how the extra text in cell A1 is hidden, even though the adjacent cell is technically empty.

<cfscript>
    cfSheet = SpreadsheetNew("foo");
    SpreadsheetAddRow(cfSheet, "this text should overflow,,short text", 1);
    SpreadsheetSetColumnWidth(cfSheet, 1, 15); 
    SpreadsheetSetColumnWidth(cfSheet, 2, 15); 
    SpreadsheetSetColumnWidth(cfSheet, 3, 15); 
    
    saveToFile = ExpandPath("test.xls");
    SpreadsheetWrite(cfsheet, saveToFile, true);
    
    // how about a WriteLine() function folks ... ;) ?
    WriteOutput("Saved file: "& saveToFile &"<hr>");
</cfscript>

<cfheader name="Content-Disposition" value="inline; filename=#saveToFile#">
<cfcontent type="application/vnd.ms-excel" file="#saveToFile#" deleteFile="true" />

In the underlying POI library there is a special cell type for blank cells: CELL_TYPE_BLANK. In loose terms, it represents a raw cell that has never had a value. This is different from a cell whose value is set to an empty string. The value may be an empty string, but the cell still has a value. So it has a type of CELL_TYPE_STRING.

If you loop over the test file created earlier, you will see all of the cells are CELL_TYPE_STRING. Which at least explains why the first cell's text does not overflow into the next cell.

<cfscript>
    // open the workbook
    source = ExpandPath("test.xls");
    input  = createObject("java", "java.io.FileInputStream").init( source );
    wb     = createObject("java", "org.apache.poi.ss.usermodel.WorkbookFactory").create( input );
    // get the first sheet
    sheet  = wb.getSheetAt(0);

    // loop over each row in the sheet
    rows = sheet.rowIterator();
    while(rows.hasNext()) {
        // get the current row
        r = rows.next();

        // loop over each cell in the sheet
        cells = r.cellIterator();
        while (cells.hasNext()) {
            // get the current cell
            c = cells.next();

            // check the cell type (ingore error handling for brevity)
            if (c.getCellType() == c.CELL_TYPE_BLANK) {
                type = "CELL_TYPE_BLANK";
            }
            else if (c.getCellType() == c.CELL_TYPE_STRING) {
                type = "CELL_TYPE_STRING";
            }
            else {
                type = "other";
            }

            // display the position, cell type / value
            // note: Adding +1 because POI indexes are 0-based
            WriteOutput("["& r.getRowNum()+1 &"]["& c.getColumnIndex()+1 &"]=");
            WriteOutput(type &" / "& c.toString() &"<hr>");
        }
    }
    // clean up 
    input.close();
</cfscript>

But this raises the question, does ColdFusion 9 support "blank" cells, and if so how do you create one? I am honestly not sure. You could work around it by tapping into the undocumented getWorkBook() method of ColdFusion spreadsheet objects. It returns a reference to the underlying POI workbook. Using the workbook, you can then grab the desired rows and cells and change the cell type to CELL_TYPE_BLANK.

<cfscript>
    cfSheet = SpreadsheetNew("foo");
    SpreadsheetAddRow(cfSheet, "this text should overflow,,short text", 1);
    SpreadsheetSetColumnWidth(cfSheet, 1, 15); 
    SpreadsheetSetColumnWidth(cfSheet, 2, 15); 
    SpreadsheetSetColumnWidth(cfSheet, 3, 15); 
    
    // work around to make cell B1 a "blank" cell   
    // POI row/cell indexes are 0-based !!
    poiSheet = cfSheet.getWorkBook().getSheet("foo");
    poiCell = poiSheet.getRow( 0 ).getCell( 1 ); 
    poiCell.setCellType( poiCell.CELL_TYPE_BLANK );
    
       
    saveToFile = ExpandPath("newTest.xls");
    SpreadsheetWrite(cfsheet, saveToFile, true);
    WriteOutput("Saved file: "& saveToFile &"<br>");
</cfscript>
                 
<cfheader name="Content-Disposition" value="inline; filename=#saveToFile#">
<cfcontent type="application/vnd.ms-excel" file="#saveToFile#" deleteFile="true" />

However I have not found a documented way to do this. So does anyone know the answer to the million dollar question: does ColdFusion 9 provide a documented method for creating blank cells? My guess would be no. But I would be very happy to be proven wrong. Any spreadsheet function gurus out there?

...Read More

Experiment Extracting Windows Thumbnails (XP and Windows 2003 only)

Windows thumbnails have existed for eons. But in all that time I never really used the windows explorer thumbnail view. Yes, pictures are nice. But invariably I found myself needing the detail view with its listing of file types, sizes and dates.

Well, this week I came across an old stackoverflow thread which mentioned a C# tool for reading and extracting images from the windows thumbnail cache (ie thumbs.db). At least on older XP and Windows 2003 systems. (Vista and later use a slightly different format.) While there are tools galore in this category, the idea of a small DLL that could be called from CF was appealing. So I decided to give it a quick whirl with CF8, under XP.

After compiling the source with Visual C# Express, I changed my explorer settings so I actually had a thumbnails file to test. Next, I created an instance of the ThumbDB class and initialized it by passing in the path my thumbnails database. Once initialized, I used the GetThumbFiles() method to grab an array of all file names within that database.

<cfset util = createObject(".net", "ThumbDBLib.ThumbDB", "c:/test/ThumbDBLib.dll")>
<cfset util.init( "C:/docs/thumbs.db" )>
<cfset fileNames = util.GetThumbFiles()>
<cfdump var="#fileNames#" label="Top 25 Files in Thumbs.db" top="25">

Next, I selected one of the file names and used the GetThumbData() method to retrieve the image bytes. I was hoping to create a CF image object from the bytes and display it in my browser. But every time I called ImageNew(), ColdFusion kept complaining about my argument type.

<!--- this does NOT work --->
<!---  grabbing an arbitrary file from the listing for testing ...--->
<cfset thumbnailName = fileNames[1] />
<cfset bytes = util.GetThumbData( thumbnailName )>
<cfset img = ImageNew(bytes) />

That is when it hit me. A byte in C# is not the same as byte in Java. Unlike Java, C# has signed and unsigned types. So where C# has two data types, sbyte (-128 to 127) and byte (0 to 255), java has only one, byte (range -128 to 127). So according to the data type conversion matrix, the C# byte array was being converted to a short array.

Thanks to a tip from BlackWasp.com, I added a new method called GetJavaThumbData(), which converts the C# byte array into one that is compatible with Java. Using the new method, I was then able to display the thumbnail perfectly.

C# Code:

public sbyte[] GetJavaThumbData(string fileName)
    {
        byte[] data = GetThumbData(fileName);
        if (data != null)
        {
        sbyte[] jvData  = Array.ConvertAll(data, delegate(byte b) { return unchecked((sbyte)b); });
        return jvData;
        }
        return null;
    }

ColdFusion Code:

<!---  grabbing an arbitrary file from the listing for testing ...--->
<cfset thumbnailName = fileNames[1] />
<cfset bytes = util.GetJavaThumbData( thumbnailName )>
<cfset img = ImageNew( bytes )>
<cfimage action="writeToBrowser" source="#img#">

It is worth noting the ThumbDB class also has a method named GetThumbnailImage(), which returns a C# image object. You can use its properties to display things like height, width, resolution. The class also has several methods for saving the image to a file. Out of curiosity, I tried several of the overloaded save() methods. But I only had success with the simplest form: Image.save(filepath). I am not sure why. Though with CF's own image functions, they are not really needed anyway.

<cfset img = util.GetThumbnailImage( thumbnailName ) />
<cfoutput>
    <strong>Thumbnail: #thumbnailName#</strong> <hr />
    Height      = #img.Get_Height()#
    Width       = #img.Get_Width()# 
    PixelFormat = #img.Get_PixelFormat()#
    Resolution  = #img.Get_HorizontalResolution()# /
                  #img.Get_VerticalResolution()# 
</cfoutput>

Supposedly the Windows API Code pack can extract thumbnails on later systems like Windows 7. If anyone has used it, or something similar, on Windows 7, let me know. I would be interested in hearing your experiences with it.

...Read More

MS SQL 2005 Tip: Display Estimated Sizes of All Tables in a Database

Today I needed to get a quick estimate of tables sizes in an MS SQL 2005 database. So I ended up using the sp_spaceused stored procedure, which displays the space used for either a database or a single object (table, etcetera).

As I was interested in a more detailed listing, I threw together a quick cursor that pulls all table names from the information_schema views, and inserts the results for each item into a table variable.

Updated: Updated to incorporate improvements from comments

USE MyDatabaseName
GO

--- suppress extraneous row counts
SET NOCOUNT ON
GO

---    Temporary table used to store results
DECLARE @spaceUsed TABLE 
(
    RecordID int IDENTITY(1,1),
    TableName nvarchar(128),
    Rows varchar(20),
    Reserved varchar(20),
    Data varchar(20),
    Index_size varchar(20),
    Unused varchar(20),
    SchemaName nvarchar(128)
)


---  get the names of all tables in the current database 
DEClARE @id int
DECLARE @tableName VARCHAR(260)
DECLARE @tableSchema VARCHAR(128)
DECLARE @tables CURSOR
SET @tables = CURSOR FOR SELECT    TABLE_SCHEMA, TABLE_SCHEMA +'.'+ TABLE_NAME 
                         FROM      INFORMATION_SCHEMA.TABLES 
                         WHERE     TABLE_TYPE = 'Base Table'

OPEN @tables 
FETCH NEXT FROM @tables INTO @TableSchema, @TableName
--- For each table ...
WHILE @@FETCH_STATUS = 0 -- lazy check
BEGIN
    BEGIN TRY
        --- insert the estimated size into the work table
        INSERT INTO @spaceUsed (TableName, Rows, Reserved, Data, Index_Size, Unused)
        EXEC sp_spaceused @TableName
        
        --- update the schema information
        UPDATE    @spaceUsed
        SET       SchemaName = @TableSchema
        WHERE     RecordID = SCOPE_IDENTITY()

    END TRY
    BEGIN CATCH
        --- ignore any errors
        PRINT 'Unable to calculate space for table ['+ @TableName +'] '+ char(10) + ERROR_MESSAGE()
    END CATCH

    FETCH NEXT FROM @tables INTO @TableSchema, @TableName
END

CLOSE @tables
DEALLOCATE @tables

Since the resulting sizes are formatted as strings (ie xxx KB), I had to run a quick update to remove the non-numeric values. Then I was able to calculate the total size for each table, and sort the results numerically. So I could get an idea of which were the largest tables in my database.

It is not at all pretty. But it did get the job done.

--- remove KB prefix so values can be cast to numeric type ...
UPDATE @spaceUsed
SET  Reserved  = REPLACE(Reserved, ' KB', '')
    , Data   = REPLACE(Data, ' KB', '')
    , Index_size = REPLACE(Index_size, ' KB', '')
    , Unused  = REPLACE(Unused, ' KB', '')
    , Rows   = REPLACE(Rows, ' KB', '')


--- display estimates for each table (order by largest amount used)
SELECT SchemaName 
    ,  TableName 
    , CAST(Rows AS decimal) AS Rows
    , CAST(Reserved AS decimal) AS ReservedKB
    , CAST(Data AS decimal) AS DataKB
    , CAST(Index_size AS decimal) AS IndexKB
    , CAST(Data AS decimal) + CAST(Index_size AS decimal) AS TotalUsedKB
    , CAST(Unused AS decimal) AS UnusedKB
FROM @spaceUsed
ORDER BY TotalUsedKB DESC

--- display overall estimates for database
EXEC sp_spaceused
GO

SET NOCOUNT OFF
GO

If you are interested in listing the overall sizes for a series of MS SQL datasources, there is good example in an older entry on John Whish's blog.

...Read More

Please do not use the database examples for CFGRID!

Looking at the documentation for CFGRID today, I was reminded of something that really bugs me. If you look over some of the CFGRID examples they use dynamic sql, which is totally unsafe and vulnerable to sql injection. If forum posts are any indication, that same code is being used in live applications (unfortunately). So in case you were tempted, do not be lazy and copy straight out of the examples. If you want safe sql, you have to put in some work. Validate, scrub, lather, rinse, repeat.

...Read More

PING with ColdFusion and .NET

I was curious if were possible to ping a server using something other than the usual cfexecute and ping.exe. method. My first thought was surely java can do it. Feeling quite confident, I found a neat method in the InetAddress class called isReachable() that seemed to fit the bill. I happily wrote up a small test, ran the code, and quickly realized something was wrong.

<cfscript>
    InetAddress = createObject("java", "java.net.InetAddress");
    address     = InetAddress.getByName("www.google.com");
    isReachable = address.isReachable( 500 ); //timeout
    WriteOutput(  address.getHostName() &" ["& address.getHostAddress() &"] "&
                  "IsReachable = "& isReachable
                );
</cfscript>

Apparently, google was unreachable. At least to java.  Of course it is technically possible, yes. Probable ... no. Especially when a command line ping reported google was alive and kicking just moments prior to that.

After re-checking the API, I realized isReachable() might not be as good as it sounded. Aside from the usual disclaimers about firewalls and blocking, the API states:

A typical implementation will use ICMP ECHO REQUESTs if the privilege can be obtained, otherwise it will try to establish a TCP connection on port 7 (Echo) of the destination host.

Since I had no idea what a typical implementation was, I fired up Wireshark to see what was really happening. Sure enough, Wireshark showed isReachable() was using a TCP connection, not ICMP. Since TCP Echo is often disabled, that explained why isReachable() returned false.

 Command Line Ping

InetAddress.isReachable(..)

So I thought to myself, either I do not have a typical implementation, or the API is wrong. (My money is on the latter). Originally I thought the jvm might be able to use ICMP through JNI perhaps. But apparently not. So back to the drawing board I went.

That is when I found .NET's simple Ping class. Its Send(..) method has four parameters: target host, timeout, sample data packet and options (time to live and discard fragmented packets).  Host and options are complex objects. So you have to create an instance of those classes first.  Once you have all of the necessary objects, simply call Send(..) to execute the ping and get the response.

<cfscript>
    ping        = createObject(".net", "System.Net.NetworkInformation.Ping");
    dns         = createObject(".net", "System.Net.Dns");
    ips         = dns.GetHostAddresses( "www.google.com" );

    // arbitrary data to be transmitted  
    data        = listToArray(repeatString("1,", 32));
    // set the ttl (time to live) = 64 and disable fragmenting  
    options     = createObject(".net", "System.Net.NetworkInformation.PingOptions").init(64, true);
    
    reply       = ping.Send ( ips[1], 100, javacast("short[]", data), options);
    status      = createObject(".net", "System.Net.NetworkInformation.IPStatus");
</cfscript>

Finally, check the response status to determine if the ping was successful. The example below displays all of the options used for debugging purposes. But all you really need to do is check the response status.

One oddity you may notice. For whatever reason, I do not believe you can access the class properties by name alone, as you can in .NET (or with java objects). So you must use the method reply.Get_Address() instead of the shorter form reply.Address.

<!--- using HR tags because of a blogger bug --->
<cfif reply.Get_Status() eq status.Success>
    <cfoutput>
    Get_Address = #reply.Get_Address().ToString()# <hr />
    Get_RoundTripTime = #reply.Get_RoundtripTime().ToString()# <hr />
    Get_Options.Ttl (Time to Live) = #reply.Get_Options().Get_Ttl()# <hr />
    Get_Options.DontFragment  = #reply.Get_Options().Get_DontFragment()# <hr />
    Get_Buffer (Length) = #ArrayLen(reply.Get_Buffer())# <hr />
    </cfoutput>
<cfelse>
    Drat. Something went wrong ...
</cfif>

Anyway, I thought this was an interesting alternative to ping.exe. Plus, it was a good opportunity to brush up on my CF / .NET skills ;)

Update: {Sigh} Darn, I do not know why this one did not came up
in my searches. Usually anything from cflib.org is pretty highly ranked.
Well, in any event, it was still a good learning experience about why not to use java for ping ;). http://www.cflib.org/udf/Ping

...Read More