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

原创 2006年06月15日 11:58:00

第一种方法:使用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文件看上去也是比较规范的。

DataSet导出到Excel,并生成文件(C#实现,可合并行和列)

using System; using System.IO; using System.Data; using System.Reflection; using System.Diagnost...
  • xujunheng
  • xujunheng
  • 2014年09月09日 19:06
  • 993

利用php在mysql里导出excel数据表的几种方法

直接说重点,大概有三种方法1、使用PHPExcel这个插件做就不说了,网上很多列子,而且确实很方便,很NB,不过用这个有几个地方要注意,第一,php版本最好5.3以上,第二,服务器需要支持ZipArc...
  • likeni1314
  • likeni1314
  • 2016年12月28日 17:57
  • 2988

html页面表格导出到excel总结

最近一个项目需要把报表的表格导入excel,在网上找了一些方法,比较研究了一下,记在这里,备忘。 表格例子如下:table id="tableExcel" width="100%" border="1...
  • xjbx
  • xjbx
  • 2008年06月23日 14:38
  • 11812

将 GridView、DataTable,DataSet 中的内容导出到 Excel 中多个工作表(Sheet) 的方法

该方法服务器端可以不用安装EXCEL。 Excel 可以保存成 xml 格式,并且支持Sheet功能,因此,我们就可以利用这个功能将 Gridview 导出到多个 Sheet  中去。而且可以很好地...
  • Trassion
  • Trassion
  • 2012年02月20日 11:37
  • 2304

DataTable 导出到 Excel 类

底层类: #region DataTable 导出到 Excel /// /// DataTable 导出到 Excel /// ...
  • u013816709
  • u013816709
  • 2015年05月27日 08:59
  • 2088

把数据库的表从sqlserver转移到mysql

sql server 2005数据导出到mysql 总体方案:从sql server 中导出建立数据库的sql文件,在powerdesigner中生成PDM,再将PDM转化为MySql的版本...
  • longyuan20102011
  • longyuan20102011
  • 2012年09月02日 23:03
  • 2119

数据库表格数据导出到excel方法总结

写在前面 之前开发的时候需要实现excel表格文件的共享工恩给你,并且是同一张大表格,不同的人只能看里面的一部分数据。由于数据每天更新,且每次都要手动筛选出给不同的人看的数据。很是繁琐。希望能有一个...
  • xianda9133
  • xianda9133
  • 2016年03月26日 20:08
  • 1036

C#中数据库数据如何导出至Excel表格

C#中数据库数据如何导出至Excel表格 标签: excel数据库c#insertstringnull 2011-12-13 16:21 17693人阅读 评论(9) 收藏 举报 ...
  • kasama1953
  • kasama1953
  • 2016年06月16日 23:28
  • 4687

C#利用NOPI导出到Excel

想说点什么,但是那句话,不好说
  • wangjinlong_
  • wangjinlong_
  • 2017年07月31日 21:13
  • 391

JAVA导出项目的所有源代码到一个文件

最近要将一个工程的所有源代码导入到一个文件中,要如下图效果。 于是写了如下代码:最后用Notepad上色,paste到word中即可。 import java.io.*; public class...
  • yibcs
  • yibcs
  • 2014年12月08日 10:19
  • 863
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:把DataSet所有数据表导出到Excel表格文件(在前人基础上只是稍作了一下修改)
举报原因:
原因补充:

(最多只允许输入30个字)