欧美激情一区二区三区成人,国产精品成人久久电影,亚洲国产天堂久久综合226114

關于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.codeproject.com/KB/office/OutputDatasetToExcel.aspx

我將這段代碼和上一篇《關于ASP.NET 將數據導出成Excel 的總結[上]》中的代碼結合起來，做了一個比較完善的Demo，使用起來還是很不錯的。

Demo下載

posted on 2011-09-26 14:09 鋼鋼閱讀(761) 評論(0) 收藏舉報

刷新頁面返回頂部

XuGang

關于ASP.NET 將數據導出成Excel 的總結[中]

Introduction

Background

Using the Code

導航

公告