关于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,如需转载请自行联系原作者
相关文章
|
6月前
|
关系型数据库 MySQL Shell
不通过navicat工具怎么把查询数据导出到excel表中
不通过navicat工具怎么把查询数据导出到excel表中
67 0
|
4月前
|
SQL XML 关系型数据库
入门指南:利用NHibernate简化.NET应用程序的数据访问
【10月更文挑战第13天】NHibernate是一个面向.NET的开源对象关系映射(ORM)工具,它提供了从数据库表到应用程序中的对象之间的映射。通过使用NHibernate,开发者可以专注于业务逻辑和领域模型的设计,而无需直接编写复杂的SQL语句来处理数据持久化问题。NHibernate支持多种数据库,并且具有高度的灵活性和可扩展性。
70 2
|
4月前
|
easyexcel Java UED
SpringBoot中大量数据导出方案:使用EasyExcel并行导出多个excel文件并压缩zip后下载
在SpringBoot环境中,为了优化大量数据的Excel导出体验,可采用异步方式处理。具体做法是将数据拆分后利用`CompletableFuture`与`ThreadPoolTaskExecutor`并行导出,并使用EasyExcel生成多个Excel文件,最终将其压缩成ZIP文件供下载。此方案提升了导出效率,改善了用户体验。代码示例展示了如何实现这一过程,包括多线程处理、模板导出及资源清理等关键步骤。
|
4月前
|
前端开发 JavaScript API
前端基于XLSX实现数据导出到Excel表格,以及提示“文件已经被损坏,无法打开”的解决方法
前端基于XLSX实现数据导出到Excel表格,以及提示“文件已经被损坏,无法打开”的解决方法
331 0
|
6月前
|
开发框架 .NET 数据库连接
闲话 Asp.Net Core 数据校验(三)EF Core 集成 FluentValidation 校验数据例子
闲话 Asp.Net Core 数据校验(三)EF Core 集成 FluentValidation 校验数据例子
114 1
|
7月前
|
开发框架 JSON 前端开发
利用查询条件对象,在Asp.net Web API中实现对业务数据的分页查询处理
利用查询条件对象,在Asp.net Web API中实现对业务数据的分页查询处理
|
6月前
|
开发框架 前端开发 算法
分享 .NET EF6 查询并返回树形结构数据的 2 个思路和具体实现方法
分享 .NET EF6 查询并返回树形结构数据的 2 个思路和具体实现方法
110 0
|
6月前
|
开发框架 .NET API
分享一个 ASP.NET Web Api 上传和读取 Excel的方案
分享一个 ASP.NET Web Api 上传和读取 Excel的方案
170 0
|
5月前
|
开发框架 前端开发 JavaScript
ASP.NET MVC 教程
ASP.NET 是一个使用 HTML、CSS、JavaScript 和服务器脚本创建网页和网站的开发框架。
67 7

热门文章

最新文章