把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文件看上去也是比较规范的。

相关文章推荐

清除系统垃圾win7.bat【在前人基础上修改而成】

color 00 @ECHO 欢迎使用“老秋工作室”制作的清除系统垃圾文件工具。 @ECHO 本工具将在极短的时间内把您系统中的所有的垃圾清除干净! @ECHO 让您的系统更加快速运行。 @ECHO ...
  • gflrlm
  • gflrlm
  • 2013年10月17日 00:03
  • 1048

java中使用jxl导出excel表格的工具类(全网唯一亲测可用,在原来基础上扩展)

java中后台导出excel的话,有两种方案,一是使用poi(不过由于是windows版本的,存在不兼容,但功能更多,更强大),而是使用jxl(纯java编写,不过兼容,简单一些),由于只需要简单的导...

【HDU】5141 LIS again 在普通nlogn求LIS的基础上稍作修改

传送门:【HDU】5141 LIS again 题目分析:我们枚举所有点R作为终点,求【1,R】内有多少个符合条件的L使得【L,R】的lis等于【1,n】的LIS。为每个终点R,找到能得到L...

C#编码中,打印预览DataGridView的一段代码,稍作了修改,可以实现。

///     /// 打印预览DataGridView公共类     /// Coin     /// 传递参数DataGridView的实体对象     ///     public...

在前人代码的基础上写的JS函数

//处理点击事件后台数据的交互 function useAjax(url,dea,onsuccess){ //判断浏览器版本并创建浏览器对象 function createAjax(){ va...

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

using System; using System.IO; using System.Data; using System.Reflection; using System.Diagnost...

整理的 C# DataSet导出至Excel文件(2)

整理的 C# DataSet导出至Excel文件                //方法1         public bool SavetoExcel(string Path, DataSet...

基于Proguard软件基础上,针对Web项目xml配置文件修改的java项目。

基于Proguard软件基础上,根据Proguard的映射map文件内容,对Web项目xml、jsp、js文件修改的java项目。...
  • t_y_c
  • t_y_c
  • 2015年04月07日 08:34
  • 532

C#导出数据表为Excel文件

博文以导出Access数据库中的数据表为例,只要修改一下同样可以用于SQL Server数据库。 string filePath = "F:\\Book.xls"; string OleDbConn...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:把DataSet所有数据表导出到Excel表格文件(在前人基础上只是稍作了一下修改)
举报原因:
原因补充:

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