动态生成Excel文件并下载

在现代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(); } }
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485