关于ASP.NET 将数据导出成Excel 的总结[中]

简介:


直接将DataSet 输出成 Excel,这样解决了网格控件只显示分页的部分数据的问题。

Introduction

I did this when I wanted to do a quick export of an entire DataSet (multiple tables) to Excel. I didn't add any additional customization to the fields, but I did want to make sure that dates, boolean, numbers, and text were all formatted correctly.

This code does that.

At some point, I'd like to make a GridView type component that would allow me to detail more about each item. For example, my latest project required me to make a column formatted with a given barcode font ("Free 3 of 9") that required that I put an * before and after the item number. The solution below doesn't make this easy to do, though... So yeah, not perfect. If anyone else has done something like this, let me know :)

For importing Excel to XML, see this post.

NOTE: This method does NOT require Excel to be installed on the Server.

Background

I prefer to see each table in the DataSet to be named.

ds.Tables[ 0].TableName = " Colors ";
ds.Tables[ 1].TableName = " Shapes ";

I changed it to allow you to pass in a List<Table> in case you don't put them in a DataSet. No big deal either way.

Why did I use an XmlTextWriter when I seem to be only using the WriteRaw? I wanted to be able to have it fix any special characters with the "x.WriteString(row[i].ToString());". Note, this still may have problems with certain characters, since I haven't tested it much.

Using the Code

复制代码
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Xml;

public void Convert(DataSet ds, string fileName) {
Convert(ds.Tables, fileName);
}
public void Convert(IEnumerable tables, string fileName) {
Response.ClearContent();
Response.ClearHeaders();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = " application/vnd.ms-excel ";
Response.AddHeader( " content-disposition ",
" attachment; filename= " + fileName + " .xls ");

using (XmlTextWriter x = new XmlTextWriter(Response.OutputStream, Encoding.UTF8)) {
int sheetNumber = 0;
x.WriteRaw( " <?xml version=\"1.0\"?><?mso-application progid=\"Excel.Sheet\"?> ");
x.WriteRaw( " <Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
x.WriteRaw( " xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
x.WriteRaw( " xmlns:x=\"urn:schemas-microsoft-com:office:excel\"> ");
x.WriteRaw( " <Styles><Style ss:ID='sText'> " +
" <NumberFormat ss:Format='@'/></Style> ");
x.WriteRaw( " <Style ss:ID='sDate'><NumberFormat " +
" ss:Format='[$-409]m/d/yy\\ h:mm\\ AM/PM;@'/> ");
x.WriteRaw( " </Style></Styles> ");
foreach (DataTable dt in tables) {
sheetNumber++;
string sheetName = ! string.IsNullOrEmpty(dt.TableName) ?
dt.TableName : " Sheet " + sheetNumber.ToString();
x.WriteRaw( " <Worksheet ss:Name=' " + sheetName + " '> ");
x.WriteRaw( " <Table> ");
string[] columnTypes = new string[dt.Columns.Count];

for ( int i = 0; i < dt.Columns.Count; i++) {
string colType = dt.Columns[i].DataType.ToString().ToLower();

if (colType.Contains( " datetime ")) {
columnTypes[i] = " DateTime ";
x.WriteRaw( " <Column ss:StyleID='sDate'/> ");

} else if (colType.Contains( " string ")) {
columnTypes[i] = " String ";
x.WriteRaw( " <Column ss:StyleID='sText'/> ");

} else {
x.WriteRaw( " <Column /> ");

if (colType.Contains( " boolean ")) {
columnTypes[i] = " Boolean ";
} else {
// default is some kind of number.
columnTypes[i] = " Number ";
}

}
}
// column headers
x.WriteRaw( " <Row> ");
foreach (DataColumn col in dt.Columns) {
x.WriteRaw( " <Cell ss:StyleID='sText'><Data ss:Type='String'> ");
x.WriteRaw(col.ColumnName);
x.WriteRaw( " </Data></Cell> ");
}
x.WriteRaw( " </Row> ");
// data
bool missedNullColumn = false;
foreach (DataRow row in dt.Rows) {
x.WriteRaw( " <Row> ");
for ( int i = 0; i < dt.Columns.Count; i++) {
if (!row.IsNull(i)) {
if (missedNullColumn) {
int displayIndex = i + 1;
x.WriteRaw( " <Cell ss:Index=' " + displayIndex.ToString() +
" '><Data ss:Type=' " +
columnTypes[i] + " '> ");
missedNullColumn = false;
} else {
x.WriteRaw( " <Cell><Data ss:Type=' " +
columnTypes[i] + " '> ");
}

switch (columnTypes[i]) {
case " DateTime ":
x.WriteRaw(((DateTime)row[i]).ToString( " s "));
break;
case " Boolean ":
x.WriteRaw((( bool)row[i]) ? " 1 " : " 0 ");
break;
case " String ":
x.WriteString(row[i].ToString());
break;
default:
x.WriteString(row[i].ToString());
break;
}

x.WriteRaw( " </Data></Cell> ");
} else {
missedNullColumn = true;
}
}
x.WriteRaw( " </Row> ");
}
x.WriteRaw( " </Table></Worksheet> ");
}
x.WriteRaw( " </Workbook> ");
}
Response.End();
}



本文转自钢钢博客园博客,原文链接:http://www.cnblogs.com/xugang/archive/2011/09/26/2191649.html,如需转载请自行联系原作者
相关文章
|
关系型数据库 MySQL Shell
不通过navicat工具怎么把查询数据导出到excel表中
不通过navicat工具怎么把查询数据导出到excel表中
341 0
Excel中如何批量重命名工作表与将每个工作表导出到单独Excel文件
本文介绍了如何在Excel中使用VBA批量重命名工作表、根据单元格内容修改颜色,以及将工作表导出为独立文件的方法。同时提供了Python实现导出工作表的代码示例,适用于自动化处理Excel文档。
|
网络协议 定位技术 网络安全
IPIP.NET-IP地理位置数据
IPIP.NET 是一家专注于 IP 地理位置数据的提供商,基于 BGP/ASN 数据与全球 800+ 网络监测点技术,提供高精度的 IPv4 和 IPv6 定位服务。其核心服务包括地理位置查询、详细地理信息和网络工具等,广泛应用于网络安全、广告营销、CDN 优化等领域。数据覆盖全球,支持多语言,每日更新确保实时性。IPIP.NET 提供 API 接口、离线数据库及多种语言 SDK,方便开发者集成使用。
3894 0
|
SQL C# 数据库
EPPlus库的安装和使用 C# 中 Excel的导入和导出
本文介绍了如何使用EPPlus库在C#中实现Excel的导入和导出功能。首先,通过NuGet包管理器安装EPPlus库,然后提供了将DataGridView数据导出到Excel的步骤和代码示例,包括将DataGridView转换为DataTable和使用EPPlus将DataTable导出为Excel文件。接着,介绍了如何将Excel数据导入到数据库中,包括读取Excel文件、解析数据、执行SQL插入操作。
EPPlus库的安装和使用 C# 中 Excel的导入和导出
|
数据格式 UED
记录一次NPOI库导出Excel遇到的小问题解决方案
【11月更文挑战第16天】本文记录了使用 NPOI 库导出 Excel 过程中遇到的三个主要问题及其解决方案:单元格数据格式错误、日期格式不正确以及合并单元格边框缺失。通过自定义单元格样式、设置数据格式和手动添加边框,有效解决了这些问题,提升了导出文件的质量和用户体验。
1310 3
|
前端开发
实现Excel文件和其他文件导出为压缩包,并导入
实现Excel文件和其他文件导出为压缩包,并导入
552 1
|
JavaScript 前端开发 数据处理
Vue导出el-table表格为Excel文件的两种方式
Vue导出el-table表格为Excel文件的两种方式
1404 6
|
存储 Java API
Java实现导出多个excel表打包到zip文件中,供客户端另存为窗口下载
Java实现导出多个excel表打包到zip文件中,供客户端另存为窗口下载
1310 4
|
Java API Apache
|
easyexcel Java UED
SpringBoot中大量数据导出方案:使用EasyExcel并行导出多个excel文件并压缩zip后下载
在SpringBoot环境中,为了优化大量数据的Excel导出体验,可采用异步方式处理。具体做法是将数据拆分后利用`CompletableFuture`与`ThreadPoolTaskExecutor`并行导出,并使用EasyExcel生成多个Excel文件,最终将其压缩成ZIP文件供下载。此方案提升了导出效率,改善了用户体验。代码示例展示了如何实现这一过程,包括多线程处理、模板导出及资源清理等关键步骤。

热门文章

最新文章