Native XLS File from Query

Most web programs which output a so-called XLS format don't really output XLS, they output CSV, which has it's limitation. This script creates REAL XLS spreadsheets from any database query.

Start Script:
// put your authentication stuff here. don't leave this one open to public yo!

// Put your db connect stuff here.

// i pass a query to this from a form.
$query = stripslashes($_POST['Sql']);
// what i want the filename to appear as when the user is prompted to save
// the file. Agian, posted from another page
$filename = stripslashes($_POST['Filename']);
$result = mysql_query($query) or die(mysql_error());;
$totalRowsResult = mysql_num_rows($result);

// Excel begin of file header
function xlsBOF() {
print(pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0));
return;
}

// Excel end of file footer
function xlsEOF() {
print(pack("ss", 0x0A, 0x00));
return;
}

// Function to write a Number (double) into Row, Col
function xlsWriteNumber($Row, $Col, $Value) {
print(pack("sssss", 0x203, 14, $Row, $Col, 0x0));
print(pack("d", $Value));
return;
}

// Function to write a label (text) into Row, Col
function xlsWriteLabel($Row, $Col, $Value) {
$L = strlen($Value);
print(pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L));
print($Value);
return;
}

// tell browser that a xls file is on it's way.
Header("Content-Type: application/vnd.ms-excel");
Header("Content-Disposition: attachment; filename=$filename");

// start the file
xlsBOF();

// generate first row with field names
$row = 0;
$col = 0;
$headersResult = mysql_fetch_assoc($result);
while(list($key,$val) = each($headersResult)){
xlsWriteLabel($row,$col,$key);
$col++;
}
$row ++;

// now spit out the data.
mysql_data_seek($result,0);
while ($rowsResult = mysql_fetch_assoc($result)){
// reset($rowsResult);
$col = 0;
while(list($key,$val) = each($rowsResult)){
if(is_numeric($val)){ // data in integer
xlsWriteNumber($row,$col,$val);
} else
if(is_string($val)){ // data is char
xlsWriteLabel($row,$col,$val);
}
$col++;
}
$row ++;
}

// end the file
xlsEOF();

End Script:

Thumbnails in ASP.NET C#

Ahh.. the fun of learning a new programming language! I come from a PHP background, have dabbled in ASP, and now, I am writing my first program in ASP.Net. One of the biggest challenges has been to create a good quality thumbnail from a very large source file. I have a client with thousands of large (20 mb and larger) files, and when I tried to use the examples I saw on the net to generate thumbnails, the results were very poor. Blocky pixelation, poor quality, it sucked. The teqnique worked on smaller source files, but not the big boys. So here goes my version which does a very good job of resizing images.

First of all, the asp.net web page which presents the thumbnail is a .ashx file. This special asp.net file is called a web handler. Basically, you call in the resources you need to accomplish a specific task, without loading up the page with all the stuff you need in an .aspx file. Think, aspx = general purpose, ashx = specific purpose.

I called my file, view_image.ashx. Below is the code:

< % @ WebHandler Language="C#" Class="ThumbnailHandler" % >
using System;
using System.IO;
using System.Web;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Drawing.Imaging;
using System.Configuration;

public class ThumbnailHandler : IHttpHandler {
public void ProcessRequest (HttpContext context){
int _width=0;
int _height=0;
// in my case, i wanted the source files outside the web space.
// I don't want users to access the images directly in the browser
string siteRoot = "D:/mywebpath/";
string imageDir = "db_images/_hi/";
// i pass the image id i want to view, this corrosponds to a file name
string imageID = context.Request["ImageID"] + "_hi.jpg";
// my _path is the full path to the file
string _path = siteRoot + imageDir + imageID;

// Create image from original file.
Image originalImage = Image.FromFile(_path);
// how wide do we want to scale to? passed in url
_width=Int32.Parse(context.Request["Width"]);
// calculate height and maintain aspect ratio
_height= originalImage.Height * _width / originalImage.Width;
// the new bitmap is going to be our resized image
// we send back out to the browser
Bitmap tmpImage = new Bitmap(_width,_height);

Graphics thumbNail = Graphics.FromImage(tmpImage);
// this is where we choose the method for resizing the image
// click here for more methods
thumbNail.InterpolationMode = InterpolationMode.HighQualityBicubic;
thumbNail.DrawImage(originalImage, 0, 0,_width, _height);
// what are we sending back? a jpeg!
context.Response.ContentType = "image/Jpeg";
tmpImage.Save (context.Response.OutputStream, ImageFormat.Jpeg);
// don't forget to close your original image!
originalImage.Dispose();
}

public bool DrawImageCallback() { return false; }

public bool IsReusable{
get { return true; }
}
}


I hope this example is useful to you. Other ways to extend this would be to write some code to put the generated image in the file system somewhere. you could also stamp on a watermark or add some text. The possibilities are limited by your imagination!