把DataSet所有数据表导出到Excel表格文件(在前人基础上只是稍作了一下修改）

第一种方法：使用XML 

    第二种方法：

  /// <summary>
        /// 将Dataset中的表导出到Excel中
        /// </summary>
        /// <param name="source">源数据集</param>
        /// <param name="fileName">保存的文件名</param>
        public static void ExportToExcel(DataSet source, string fileName)
        {

            System.IO.StreamWriter excelDoc;

            excelDoc = new System.IO.StreamWriter(fileName);

            const string startExcelXML = @"<?xml version=""1.0""?>
                    <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"">
                     <Styles>
                     <Style ss:ID=""Default"" ss:Name=""Normal"">
                     <Alignment ss:Horizontal=""Center"" ss:Vertical=""Center""/>

                     <Font/>
                     <Interior/>
                     <NumberFormat/>
                     <Protection/>
                     </Style>
                     <Style ss:ID=""BoldColumn"">
                     <Font x:Family=""宋体"" ss:Bold=""1""/>
                       <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>
                     <Style     ss:ID=""StringLiteral"">
                     <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>
                     <Style ss:ID=""Decimal"">
                     <NumberFormat ss:Format=""0.00""/>
                      <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>
                     <Style ss:ID=""Integer"">
                     <NumberFormat ss:Format=""0""/>
                       <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>
                     <Style ss:ID=""DateLiteral"">
                     <NumberFormat ss:Format=""mm/dd/yyyy;@""/>
                      <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>";
            const string endExcelXML = "</Workbook>";

            int rowCount = 0;
            int sheetCount = 0;

 

            excelDoc.Write(startExcelXML);
            for (int i = 0; i < source.Tables.Count; i++)
            {
                sheetCount++;
                excelDoc.Write("<Worksheet ss:Name=/"" + source.Tables[i].TableName + "/">");
                excelDoc.Write("<Table>");
                excelDoc.Write("<Row>");

                for (int x = 0; x < source.Tables[i].Columns.Count; x++)
                {
                    excelDoc.Write("<Cell ss:StyleID=/"BoldColumn/"><Data ss:Type=/"String/">");
                    excelDoc.Write(source.Tables[i].Columns[x].ColumnName);
                    excelDoc.Write("</Data></Cell>");
                }
                excelDoc.Write("</Row>");
                foreach (DataRow x in source.Tables[i].Rows)
                {
                    rowCount++;
                    //if the number of rows is > 64000 create a new page to continue output
                    if (rowCount == 64000)
                    {
                        rowCount = 0;
                        sheetCount++;
                        excelDoc.Write("</Table>");
                        excelDoc.Write(" </Worksheet>");
                        excelDoc.Write("<Worksheet ss:Name=/"" + source.Tables[i].TableName + sheetCount + "/">");
                        excelDoc.Write("<Table>");
                    }
                    excelDoc.Write("<Row>"); //ID=" + rowCount + "
                    for (int y = 0; y < source.Tables[i].Columns.Count; y++)
                    {
                        System.Type rowType;
                        rowType = x[y].GetType();
                        switch (rowType.ToString())
                        {
                            case "System.String":
                                string XMLstring = x[y].ToString();
                                XMLstring = XMLstring.Trim();
                                XMLstring = XMLstring.Replace("&", "&");
                                XMLstring = XMLstring.Replace(">", ">");
                                XMLstring = XMLstring.Replace("<", "<");
                                excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +
                                               "<Data ss:Type=/"String/">");
                                excelDoc.Write(XMLstring);
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.DateTime":
                                //Excel has a specific Date Format of YYYY-MM-DD followed by 
                                //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
                                //The Following Code puts the date stored in XMLDate
                                //to the format above
                                DateTime XMLDate = (DateTime)x[y];
                                string XMLDatetoString = ""; //Excel Converted Date
                                XMLDatetoString = XMLDate.Year.ToString() +
                                     "-" +
                                     (XMLDate.Month < 10 ? "0" +
                                     XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                     "-" +
                                     (XMLDate.Day < 10 ? "0" +
                                     XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                     "T" +
                                     (XMLDate.Hour < 10 ? "0" +
                                     XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                     ":" +
                                     (XMLDate.Minute < 10 ? "0" +
                                     XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                     ":" +
                                     (XMLDate.Second < 10 ? "0" +
                                     XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                     ".000";
                                excelDoc.Write("<Cell ss:StyleID=/"DateLiteral/">" +
                                             "<Data ss:Type=/"DateTime/">");
                                excelDoc.Write(XMLDatetoString);
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.Boolean":
                                excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +
                                            "<Data ss:Type=/"String/">");
                                excelDoc.Write(x[y].ToString());
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.Int16":
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                excelDoc.Write("<Cell ss:StyleID=/"Integer/">" +
                                        "<Data ss:Type=/"Number/">");
                                excelDoc.Write(x[y].ToString());
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.Decimal":
                            case "System.Double":
                                excelDoc.Write("<Cell ss:StyleID=/"Decimal/">" +
                                      "<Data ss:Type=/"Number/">");
                                excelDoc.Write(x[y].ToString());
                                excelDoc.Write("</Data></Cell>");
                                break;
                            case "System.DBNull":
                                excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +
                                      "<Data ss:Type=/"String/">");
                                excelDoc.Write("");
                                excelDoc.Write("</Data></Cell>");
                                break;
                            default:
                                throw (new Exception(rowType.ToString() + " not handled."));
                        }
                    }
                    excelDoc.Write("</Row>");
                }
                excelDoc.Write("</Table>");
                excelDoc.Write(" </Worksheet>");
            }
            excelDoc.Write(endExcelXML);

            excelDoc.Close();
        }

 

  第一种方法倚赖性性较少，不需要装有excel，但生成Excel文件较大(XML格式的Excel,可以使用文本编辑器察看)

///<summary>
        /// 将DataSet里所有表格数据导入Excel.
        ///需要添加COM: Microsoft Excel Object Library.
        /// using Excel = Microsoft.Office.Interop.Excel;
        ///<param name="fileName"></param>
        ///<param name="source"></param>
        ///</summary>
        public bool ExportExcel(DataSet source, string fileName)
        {
            bool fileSaved = false; //是否保存成功

            int rowIndex = 1;//行起始坐标
            int colIndex = 1;//列起始坐标

            if (source == null) return false;

            Excel.Application xlApp = new Excel.Application();

            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return false;
            }

            Excel.Workbooks workbooks = xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet;
            Excel.Range range;

            for (int TabIndex = 0; TabIndex < source.Tables.Count; TabIndex++)
            {
                worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);//取得sheet1
                worksheet.Name = source.Tables[TabIndex].TableName;
                //写入字段
                for (int i = 0; i < source.Tables[TabIndex].Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = source.Tables[TabIndex].Columns[i].ColumnName;
                }

                //设置标题格式
                range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, source.Tables[TabIndex].Columns.Count]);
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;

                //写入数值
                rowIndex = 1;
                foreach (DataRow row in source.Tables[TabIndex].Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in source.Tables[TabIndex].Columns)
                    {
                        colIndex++;

                        if (col.DataType == System.Type.GetType("System.String"))//数据是否是字符串
                        {
                            worksheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                        }
                        else if (col.DataType == System.Type.GetType("System.DateTime"))//数据是否是时间日期
                        {
                            worksheet.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            worksheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                    }
                }

                //设置数据区域格式
                range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[source.Tables[TabIndex].Rows.Count + 1, source.Tables[TabIndex].Columns.Count]);
                range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
                range.Borders.ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                range.Borders.Weight = Excel.XlBorderWeight.xlThin;
                range.Select();//自动适应大小
                range.AutoFit();
            }
            try
            {
                workbook.Saved = true;
                workbook.SaveCopyAs(fileName);
                fileSaved = true;
            }
            catch (Exception ex)
            {
                fileSaved = false;
                MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + ex.Message);
            }

            xlApp.Quit();
            GC.Collect();//强行销毁
            return fileSaved;
        }

 

地二种方法需要装有Excel,生成文件较小，但是数据量稍大，生成速度就受到影响。

个人推荐使用第一种，生成速度快，通过自己的设置，生成的EXCEl文件看上去也是比较规范的。