将数据导出成Excel文件是相当常用的一个功能，我们经常在开发中用到。不过我们通常会借助.net的Office组件进行开发，在这里我教大家一种依赖任何外部环境的导出方法。

原理很简单﹐excel 2003及以上版本支持一种xml电子表格的格式﹐也只是说我们只要输出一种指定格式的XML，即可被excel视别。文本非常简单，以DataSet为例，全文代码如下：

        protected void Page_Load(object sender, EventArgs e)
        {
            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=123456.xls");
            WriteExcelHead(Response.Output);
            WriteDataSet(Response.Output, new DAL.SqlHelper().ExecuteTable("select * from zkxy_Category").DataSet);
            WriteExcelFoot(Response.Output);
        }

        private void WriteExcelHead(System.IO.TextWriter writer)
        {
            writer.WriteLine("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
            writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
            //Excel工作薄开始
            writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
            writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
            writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
            writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
            writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40//\">");
            writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
            writer.WriteLine(" <Author>www.jiniannet.com</Author>");
            writer.WriteLine(string.Format("  <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
            writer.WriteLine(" <Company>www.jiniannet.com</Company>");
            writer.WriteLine(" <Version>11.6408</Version>");
            writer.WriteLine(" </DocumentProperties>");
            writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
            writer.WriteLine("  <WindowHeight>8955</WindowHeight>");
            writer.WriteLine("  <WindowWidth>11355</WindowWidth>");
            writer.WriteLine("  <WindowTopX>480</WindowTopX>");
            writer.WriteLine("  <WindowTopY>15</WindowTopY>");
            writer.WriteLine("  <ProtectStructure>False</ProtectStructure>");
            writer.WriteLine("  <ProtectWindows>False</ProtectWindows>");
            writer.WriteLine(" </ExcelWorkbook>");
            //Excel工作薄结束
            //工作薄样式
            writer.WriteLine("<Styles>");
            writer.WriteLine("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
            writer.WriteLine("  <Alignment/>");
            writer.WriteLine("  <Borders/>");
            writer.WriteLine("  <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>");
            writer.WriteLine("  <Interior/>");
            writer.WriteLine("  <Protection/>");
            writer.WriteLine("</Style>");

            //文本样式
            writer.WriteLine("<Style ss:ID=\"StringLiteral\">");
            writer.WriteLine("  <NumberFormat ss:Format=\"@\"/>");
            writer.WriteLine("</Style>");
            //浮点型样式
            writer.WriteLine("<Style ss:ID=\"Decimal\">");
            writer.WriteLine("  <NumberFormat ss:Format=\"0.00\"/>");
            writer.WriteLine("</Style>");
            //整型样式
            writer.WriteLine("<Style ss:ID=\"Integer\">");
            writer.WriteLine("  <NumberFormat ss:Format=\"0\"/>");
            writer.WriteLine("</Style>");
            //日期样式
            writer.WriteLine("<Style ss:ID=\"DateLiteral\">");
            writer.WriteLine("  <NumberFormat ss:Format=\"mm/dd/yyyy;@\"/>");
            writer.WriteLine("</Style>");
            writer.WriteLine(" </Styles>");
        }

        private void WriteExcelFoot(System.IO.TextWriter writer)
        {
            writer.WriteLine("</Workbook>");
        }

        private void WriteDataSet(System.IO.TextWriter writer, DataSet ds)
        {
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                int rows = ds.Tables[i].Rows.Count + 1;
                int cols = ds.Tables[i].Columns.Count;
                //第i个工作表
                writer.WriteLine(string.Format("<Worksheet ss:Name=\"{0}\">", ds.Tables[i].TableName));
                writer.WriteLine(string.Format("    <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString()));
                writer.WriteLine("   x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">");
                //<Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="4" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
                //ExpandedColumnCount：代表Excel文档中的列数
                //ExpandedRowCount：代表Excel文档中的行数

                //指定每一列的宽度
                for (int c = 0; c < ds.Tables[i].Columns.Count; c++)
                {
                    writer.WriteLine(string.Format("<Column ss:Index=\"{0}\" ss:AutoFitWidth=\"{1}\" ss:Width=\"{2}\"/> ", c + 1, 1, 80));
                }
                //生成标题
                //writer.WriteLine(string.Format("<Row ss:AutoFitHeight=\"{0}\" ss:Height=\"{1}\">", 0, 28.5));
                writer.WriteLine("<Row>");
                foreach (DataColumn eachCloumn in ds.Tables[i].Columns)
                {
                    writer.Write("<Cell ss:StyleID=\"Default\"><Data ss:Type=\"String\">");
                    writer.Write(eachCloumn.ColumnName.ToString());
                    writer.WriteLine("</Data></Cell>");
                }
                writer.WriteLine("</Row>");

                //生成数据记录
                foreach (DataRow eachRow in ds.Tables[i].Rows)
                {
                    //writer.WriteLine("<Row ss:AutoFitHeight=\"0\">");
                    writer.WriteLine("<Row>");
                    for (int currentRow = 0; currentRow != cols; currentRow++)
                    {
                        object[] getValue = ExcelContent(eachRow[currentRow]);

                        writer.Write(string.Format("<Cell ss:StyleID=\"{0}\"><Data ss:Type=\"{1}\">", getValue[0], getValue[1]));
                        writer.Write(getValue[2]);
                        writer.WriteLine("</Data></Cell>");
                    }
                    writer.WriteLine("</Row>");
                }
                writer.WriteLine("</Table>");
                writer.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                writer.WriteLine("<Selected/>");
                writer.WriteLine("<Panes>");
                writer.WriteLine("<Pane>");
                writer.WriteLine("  <Number>3</Number>");
                writer.WriteLine("  <ActiveRow>1</ActiveRow>");
                writer.WriteLine("</Pane>");
                writer.WriteLine("</Panes>");
                writer.WriteLine("<ProtectObjects>False</ProtectObjects>");
                writer.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
                writer.WriteLine("</WorksheetOptions>");
                writer.WriteLine("</Worksheet>");
            }

        }

        private static object[] ExcelContent(object Value)
        {
            object[] strValue = new object[3];
            System.Type rowType = Value.GetType();
            switch (rowType.ToString())
            {
                case "System.String":
                case "System.Guid":
                    string XMLstring = Value.ToString();
                    XMLstring = XMLstring.Trim();
                    XMLstring = XMLstring.Replace("&", "&");
                    XMLstring = XMLstring.Replace(">", ">");
                    XMLstring = XMLstring.Replace("<", "<");
                    strValue[0] = "StringLiteral";
                    strValue[1] = "String";
                    strValue[2] = XMLstring;
                    break;
                case "System.DateTime":
                    DateTime XMLDate = (DateTime)Value;
                    string XMLDatetoString = ""; //Excel Converted Date
                    //把日期时间转化为：“yyyy-MM-ddTHH:mm:ss”这种Excel中的格式
                    XMLDatetoString = XMLDate.ToString(System.Globalization.DateTimeFormatInfo.CurrentInfo.SortableDateTimePattern);
                    strValue[0] = "DateLiteral";
                    strValue[1] = "DateTime";
                    if (XMLDate < Convert.ToDateTime("1900-1-1"))
                    {
                        strValue[0] = "StringLiteral";
                        strValue[1] = "String";
                        XMLDatetoString = string.Empty;
                    }
                    strValue[2] = XMLDatetoString;
                    break;
                case "System.Boolean":
                    strValue[0] = "StringLiteral";
                    strValue[1] = "String";
                    strValue[2] = Value.ToString();
                    break;
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    strValue[0] = "Integer";
                    strValue[1] = "Number";
                    strValue[2] = Value.ToString();
                    break;
                case "System.Byte[]":
                    strValue[0] = "StringLiteral";
                    strValue[1] = "String";
                    strValue[2] = (byte[])Value;
                    break;
                case "System.Decimal":
                case "System.Double":
                    strValue[0] = "Decimal";
                    strValue[1] = "Number";
                    strValue[2] = Value.ToString();
                    break;
                case "System.DBNull":
                    strValue[0] = "StringLiteral";
                    strValue[1] = "String";
                    strValue[2] = "";
                    break;
                default:
                    throw (new Exception(rowType.ToString() + " not handled."));
            }
            return strValue;
        }
       

过段时间我把代理整理一下，做成通用的类库。希望大家喜欢！