在现代Web应用中,经常需要根据数据库中的数据动态生成Excel文件,并允许用户下载。这可以通过编写一个处理程序来实现,该处理程序接收一个键作为请求,根据键从数据库中获取数据,写入Excel文件,然后以分块的方式下载到客户端。幸运的是,在Code Project上找到了一篇优秀的文章,它帮助生成了不使用Microsoft Excel的Excel文件。
下面是一个处理程序类的示例,它展示了如何实现上述功能。这个类使用C#编写,它创建了一个内存流,然后使用自定义的ExcelWriter类将数据写入Excel文件,并通过HTTP响应将其发送给客户端。
public void ProcessRequest(HttpContext context)
{
HttpRequest request = context.Request;
System.IO.MemoryStream currentStream = null;
byte[] buffer = new Byte[10000];
int length;
long dataToRead;
string fileName = request["FileName"];
System.Collections.Generic.List<Test> lstTest = "Your List of Test Object";
try
{
currentStream = new System.IO.MemoryStream();
ExcelWriter writer = new ExcelWriter(currentStream);
writer.BeginWrite();
// 写入标题行
writer.WriteCell(0, 0, "Title");
writer.WriteCell(0, 1, "FirstName");
writer.WriteCell(0, 2, "Surname");
writer.WriteCell(0, 3, "Email");
writer.WriteCell(0, 4, "TelePhoneNumber");
writer.WriteCell(0, 5, "OrderNumber");
writer.WriteCell(0, 6, "SubmissionDate");
// 写入数据行
if (lstTest != null)
{
for (int row = 0; row < lstTest.Count; row++)
{
writer.WriteCell(row + 1, 0, lstTest[row].Title);
writer.WriteCell(row + 1, 1, lstTest[row].FirstName);
writer.WriteCell(row + 1, 2, lstTest[row].SurName);
writer.WriteCell(row + 1, 3, lstTest[row].Email);
writer.WriteCell(row + 1, 4, lstTest[row].TelePhoneNumber);
writer.WriteCell(row + 1, 5, lstTest[row].OrderNumber);
writer.WriteCell(row + 1, 6, lstTest[row].SubmissionDate);
}
}
writer.EndWrite();
currentStream.Position = 0;
context.Response.AddHeader("Content-Length", currentStream.Length.ToString());
context.Response.AddHeader("Accept-Ranges", "bytes");
context.Response.Buffer = false;
context.Response.AddHeader("Connection", "Keep-Alive");
context.Response.ContentType = "application/octet-stream";
context.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
dataToRead = currentStream.Length;
context.Response.ContentType = "application/octet-stream";
while (dataToRead > 0)
{
if (context.Response.IsClientConnected)
{
length = currentStream.Read(buffer, 0, 10000);
context.Response.OutputStream.Write(buffer, 0, length);
context.Response.Flush();
buffer = new Byte[10000];
dataToRead = dataToRead - length;
}
else
{
dataToRead = -1;
}
}
}
catch (Exception ex)
{
context.Response.Write(ex);
}
finally
{
if (currentStream != null)
{
currentStream.Close();
currentStream.Dispose();
}
}
}
测试类用于存储要写入Excel文件的数据。
public class Test
{
public string Title { get; set; }
public string FirstName { get; set; }
public string SurName { get; set; }
public string Email { get; set; }
public string TelePhoneNumber { get; set; }
public string OrderNumber { get; set; }
public string SubmissionDate { get; set; }
}
public class ExcelWriter
{
private Stream stream;
private BinaryWriter writer;
private ushort[] clBegin = { 0x0809, 8, 0, 0x10, 0, 0 };
private ushort[] clEnd = { 0x0A, 00 };
private void WriteUshortArray(ushort[] value)
{
for (int i = 0; i < value.Length; i++)
writer.Write(value[i]);
}
public ExcelWriter(Stream stream)
{
this.stream = stream;
writer = new BinaryWriter(stream);
}
public void WriteCell(int row, int col, string value)
{
ushort[] clData = { 0x0204, 0, 0, 0, 0, 0 };
int iLen = value.Length;
byte[] plainText = Encoding.ASCII.GetBytes(value);
clData[1] = (ushort)(8 + iLen);
clData[2] = (ushort)row;
clData[3] = (ushort)col;
clData[5] = (ushort)iLen;
WriteUshortArray(clData);
writer.Write(plainText);
}
public void WriteCell(int row, int col, int value)
{
ushort[] clData = { 0x027E, 10, 0, 0, 0 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
int iValue = (value << 2) | 2;
writer.Write(iValue);
}
public void WriteCell(int row, int col, double value)
{
ushort[] clData = { 0x0203, 14, 0, 0, 0 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
writer.Write(value);
}
public void WriteCell(int row, int col)
{
ushort[] clData = { 0x0201, 6, 0, 0, 0x17 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
}
public void BeginWrite()
{
WriteUshortArray(clBegin);
}
public void EndWrite()
{
WriteUshortArray(clEnd);
writer.Flush();
}
}