大数据导出Excel方法

  #region DataSet匯出Excel 文本方法支持大数据
        protected void DsTotxt(DataSet ds)
        {
            string guid = Guid.NewGuid().ToString();
            string path = Server.MapPath(string.Format("~/Files/Excel/{0}Excel.txt", guid));

            string header =System.IO.File.ReadAllText(Server.MapPath("~/Files/Excel/header.txt"), Encoding.Default);//頭部文件
            using (FileStream fs = new FileStream(path, FileMode.Create))
            {
                StreamWriter sw = new StreamWriter(fs);
                sw.WriteLine(header);
                foreach (System.Data.DataTable data in ds.Tables)
                {
                    if (data != null && data.Rows.Count > 0)
                    {
                        int cloumnNum = 200;
                        //如果表格的列大于cloumnNum分割成多個表
                        if (data.Columns.Count > cloumnNum)
                        {
                            int column = data.Columns.Count / cloumnNum;
                            if ((data.Columns.Count % cloumnNum) > 0) column++;
                            for (int i = 0; i < column; i++)
                            {
                                string worksheetname = data.TableName.Replace("\r\n", "") + (i + 1);
                                sw.WriteLine(string.Format(@"<Worksheet ss:Name='{0}'>", worksheetname));
                                sw.WriteLine(@"<Table x:FullColumns='1' x:FullRows='1'>");
                                sw.WriteLine(@"\r\n<Row ss:AutoFitHeight='1'>");
                                int startcolumn = i * cloumnNum;//列开始
                                int endcolumn = (i + 1) * cloumnNum;//列结束
                                if (endcolumn > data.Columns.Count) endcolumn = data.Columns.Count;

                                for (int j = startcolumn; j < endcolumn; j++)
                                {
                                    if (data.Columns.Count > j)
                                    {
                                        sw.WriteLine(string.Format(@"<Cell ss:StyleID='Header'><Data ss:Type='String'>{0}</Data></Cell>", data.Columns[j].ColumnName));
                                    }
                                }
                                sw.WriteLine(@"\r\n</Row>");
                                // 輸出表格內容
                                foreach (System.Data.DataRow row in data.Rows)
                                {
                                    sw.WriteLine("<Row>");
                                    for (int b = startcolumn; b < endcolumn; b++)
                                    {
                                        // 对于数字，采用Number数字类型
                                        string v = String.Format("{0}", row[data.Columns[b].ColumnName]);
                                        string t = "String";
                                        if (data.Columns[b].DataType == typeof(int))
                                        {
                                            t = "Number";
                                        }
                                        sw.WriteLine(string.Format("<Cell ss:StyleID='border'><Data ss:Type='{0}'>{1}</Data></Cell>", t, v));
                                    }
                                    sw.WriteLine("</Row>");
                                }
                                sw.WriteLine("</Table></Worksheet>");
                            }
                        }
                        else
                        {
                            sw.WriteLine(string.Format(@"<Worksheet ss:Name='{0}'>", data.TableName.Replace("\r\n", "")));
                            sw.WriteLine(@"<Table x:FullColumns='1' x:FullRows='1'>");
                            sw.WriteLine(@"\r\n<Row ss:AutoFitHeight='1'>");
                            cloumnNum = data.Columns.Count;
                            int startcolumn = 0;//列开始
                            int endcolumn = 1 * cloumnNum;//列结束
                            if (endcolumn > data.Columns.Count) endcolumn = data.Columns.Count;

                            for (int j = startcolumn; j < endcolumn; j++)
                            {
                                if (data.Columns.Count > j)
                                {
                                    sw.WriteLine(string.Format(@"<Cell ss:StyleID='Header'><Data ss:Type='String'>{0}</Data></Cell>", data.Columns[j].ColumnName));
                                }
                            }
                            sw.WriteLine(@"\r\n</Row>");
                            // 輸出表格內容
                            foreach (System.Data.DataRow row in data.Rows)
                            {
                                sw.WriteLine("<Row>");
                                for (int b = startcolumn; b < endcolumn; b++)
                                {
                                    // 对于数字，采用Number数字类型
                                    string v = String.Format("{0}", row[data.Columns[b].ColumnName]);
                                    string t = "String";
                                    if (data.Columns[b].DataType == typeof(int))
                                    {
                                        t = "Number";
                                    }
                                    sw.WriteLine(string.Format("<Cell ss:StyleID='border'><Data ss:Type='{0}'>{1}</Data></Cell>", t, v));
                                }
                                sw.WriteLine("</Row>");
                            }
                            sw.WriteLine("</Table></Worksheet>");
                        }
                    }
                }
                sw.WriteLine("</Workbook>");
                sw.Close();
            }
            string excelPath = Getpath();
            System.IO.File.Move(path, Server.MapPath(excelPath));
            Response.Redirect(excelPath, false);
            Response.End();
        }
        //是否存在表名
        private int nameCount = 1;
        protected string ReName(DataSet ds, string name)
        {
            foreach (System.Data.DataTable item in ds.Tables)
            {
                if (item.TableName == name)
                {
                    if (nameCount > 1)
                    {
                        name = name.Substring(0, name.Length - 1);
                    }
                    name = name + nameCount;
                    nameCount++;
                    name = ReName(ds, name);
                }
            }
            return name;
        }
        //獲取路徑
        private int fileCount = 0;
        public string Getpath()
        {
            string _path = "~/Files/Excel/問卷訪問內容.xls";
            if (fileCount != 0)
            {
                _path = string.Format("Files/Excel/問卷訪問內容{0}.xls", fileCount);
            }
            string FullPath = Server.MapPath(_path);
            //存在刪除
            if (System.IO.File.Exists(FullPath))
            {
                try
                {
                    System.IO.File.Delete(FullPath);
                }
                catch (Exception e)
                {
                    fileCount++;
                    return Getpath();
                }
            }
            return _path;
        }
        #endregion

 

 

 

以下是header.txt內容

---------------------------------------------------------------------------------------------------------------------------------------------

 

 

 

<?xml version='1.0'?>
<?mso-application progid='Excel.Sheet'?>
<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>
    <DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>
        <Author>limin_he</Author><LastAuthor>limin_he</LastAuthor><Created><%=DateTime.Now.ToString() %></Created><Company>Maxense</Company><Version>2013</Version>
    </DocumentProperties>
    <Styles>
        <Style ss:ID='Default' ss:Name='Normal'>
                <Alignment ss:Vertical='Center'/>
                <Borders/>
                <Font ss:FontName='新細明體' x:CharSet='134' ss:Size='12'/>
                <Interior/>
                <NumberFormat/>
                <Protection/>
        </Style>
        <Style ss:ID='Header'>
            <Alignment ss:Horizontal="Center" ss:Vertical="Center" />
            <Borders>
                <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
                <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
                <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
                <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>
            </Borders>
            <Font ss:FontName='新細明體' x:CharSet='134' ss:Size='12' ss:Color='#000000'/>
        </Style>
        <Style ss:ID='border'>
            <NumberFormat ss:Format='@'/>
            <Borders>
                <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
                <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
                <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
                <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/>
            </Borders>
        </Style>
    </Styles>