对.NET中导出数据到EXCEL的几种方法探讨

最新推荐文章于 2023-03-02 18:26:25 发布

simonllf

最新推荐文章于 2023-03-02 18:26:25 发布

阅读量1.1w

点赞数 2

分类专栏： .NET技术文章标签： excel .net dataset office string exception

本文链接：https://blog.csdn.net/simonllf/article/details/1441672

版权

.NET技术专栏收录该内容

53 篇文章 0 订阅

订阅专栏

最近在做一个报表系统的时候，需要把DATASET中的数据导到EXCEL当中，于是在网上找了一遍，发现了好几种方法，本来以为应该差不多，但后来经过一一试用后，发现在性能上真的差别很大，现在就介绍一下，同时有不对的希望可以指正：

1．原理：利用office组件把dataset中的数据填充到excel文件当中。

这里我不贴出全部代码了，只把关键部分贴出来：

///<summary>

/// 方法，导出C1TrueDBGrid中的数据到Excel文件

///</summary>

///<param name="c1grid">C1TrueDBGrid</param>

///<param name="FileName">Excel 文件名 </param>

public void ExportToExcel(C1.Win.C1TrueDBGrid.C1TrueDBGrid c1grid,string FileName)

{

if(FileName.Trim() == "") return; // 验证strFileName是否为空或值无效

int rowscount = c1grid.Splits[0].Rows.Count; // 定义表格内数据的行数

int colscount = c1grid.Columns.Count; // 定义表格内数据的列数

// 行数必须大于0

if (rowscount <= 0)

{

MessageBox.Show(" 没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);

return;

}

// 列数必须大于0

if (colscount <= 0)

{

MessageBox.Show(" 没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);

return;

}

// 行数不可以大于65536

if (rowscount > 65536)

{

MessageBox.Show(" 数据记录数太多(最多不能超过65536条)，不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);

return;

}

// 列数不可以大于255

if (colscount > 255)

{

MessageBox.Show(" 数据记录行数太多，不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);

return;

}

// 将文件保存到工作路径的子目录“/Excel”下，如果路径不存在创建它

string n_path = Directory.GetCurrentDirectory() + "//Excel";

if (Directory.Exists(n_path) == false)

{

Directory.CreateDirectory(n_path);

}

// 验证以strFileName命名的文件是否存在，如果存在删除它

FileInfo fi = new FileInfo(n_path + "//" + FileName + ".xls");

if(fi.Exists)

{

try

{

fi.Delete();

}

catch(Exception fie)

{

MessageBox.Show(fie.Message," 删除失败", MessageBoxButtons.OK, MessageBoxIcon.Warning);

return;

}

Excel.ApplicationClass excel = null;

try

{

// 显示进度条

KP.PublicForm.ProgressBar pb = new PublicForm.ProgressBar(" 导出进度");

pb.Show();

pb.Refresh();

// 新建Excel应用，新建Workbook文件

excel = new Excel.ApplicationClass ( ) ;

Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();

excel.Application.Workbooks.Add (true) ;

// 向Excel中写入表格的表头

int i = 1;

for(int c = 0;c < colscount; c++)

{

if(c1grid.Splits[0].DisplayColumns[c].Visible)

{

excel.Cells[1,i] = c1grid.Columns[c].Caption;

i++;

}

// 向Excel中逐行逐列写入表格中的数据

for(int r = 0; r < rowscount; r++)

{

Application.DoEvents();

pb.SetProgressBarValue(r+1, rowscount);

if(pb.Cancel)

{

break;

}

int j = 1;

for(int c = 0;c < colscount; c++)

{

if(c1grid.Splits[0].DisplayColumns[c].Visible)

{

excel.Cells[r + 2,j] = c1grid.Columns[c].CellText(r);

j++;

}

// 向Excel中写入表格的脚

if(c1grid.ColumnFooters)

{

int col = 1;

for(int c = 0;c < colscount; c++)

{

if(c1grid.Splits[0].DisplayColumns[c].Visible)

{

if(c1grid.Columns[c].FooterText != null && c1grid.Columns[c].FooterText.Trim() != "")

{

excel.Cells[rowscount + 2,col] = c1grid.Columns[c].FooterText;

}

col++;

}

// 关闭进度条

pb.Close();

// 设置Excel的默认保存路径为当前路径下的Excel子文件夹

excel.DefaultFilePath = n_path;

// 保存文件

excel.ActiveWorkbook.SaveAs(FileName + ".xls",excel.ActiveWorkbook.FileFormat,"","",excel.ActiveWorkbook.ReadOnlyRecommended,excel.ActiveWorkbook.CreateBackup,savemode,excel.ActiveWorkbook.ConflictResolution,false,"","");

}

catch(Exception e1)

{

MessageBox.Show(e1.Message, " 警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);

return;

}

finally

{

// 关闭Excel应用

if(excel != null) excel.Quit();

}

MessageBox.Show(FileName + " 导出完毕，在" + Application.StartupPath + "//Excel文件夹下","提示", MessageBoxButtons.OK,MessageBoxIcon.Information);

// }

}

总结：这个方法是可以解决问题，但效率最差，3000条长点的record就用了6分钟，晕~~~~

2．原理：利用office组件，同时把dataset的数据导到 Clipboard 中，然后通过粘贴到excel中。

Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();

xlApp.Application.Workbooks.Add (true) ;

xlApp.DefaultFilePath = @"c:/";

xlApp.ActiveWorkbook.SaveAs("exportExcel.xls",xlApp.ActiveWorkbook.FileFormat,"","",xlApp.ActiveWorkbook.ReadOnlyRecommended,xlApp.ActiveWorkbook.CreateBackup,savemode,xlApp.ActiveWorkbook.ConflictResolution,false,"","","");

Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing,

oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,

oMissing,oMissing,oMissing);

Excel.Worksheet xlWorksheet;

// 循环所有DataTable

for( int i=0; i<ds.Tables.Count; i++ )

{

xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);

// 以TableName作为新加的Sheet页名。

xlWorksheet.Name = ds.Tables[i].TableName;

// 取出这个DataTable中的所有值,暂存于stringBuffer中。

string stringBuffer = "";

// 向Excel中写入表格的表头

if(node != null)

{

XmlNode nodec=node.SelectSingleNode("./Method/ShowField");

int ii = 1;

foreach(XmlNode xnode in nodec.ChildNodes )

{

xlApp.Cells[1,ii] =xnode.Attributes["displayname"].Value;

ii++;

}

for( int j=0; j<ds.Tables[i].Rows.Count; j++ )

{

for( int k=0; k<ds.Tables[i].Columns.Count; k++ )

{

stringBuffer += ds.Tables[i].Rows[j][k].ToString();

if( k < ds.Tables[i].Columns.Count - 1 )

stringBuffer += "/t";

}

stringBuffer += "/n";

}

else

{

int ii = 1;

for(int c = 0;c<ds.Tables[i].Columns.Count; c++)

{

xlApp.Cells[1,ii] = ds.Tables[i].Columns[c].Caption;

ii++;

}

for( int j=0; j<ds.Tables[i].Rows.Count; j++ )

{

for( int k=0; k<ds.Tables[i].Columns.Count; k++ )

{

stringBuffer += ds.Tables[i].Rows[j][k].ToString();

if( k < ds.Tables[i].Columns.Count - 1 )

stringBuffer += "/t";

}

stringBuffer += "/n";

}

System.Windows.Forms.Clipboard.SetDataObject("");

// 将stringBuffer放入剪切板。

System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);

// 选中这个sheet页中的第一个单元格

((Excel.Range)xlWorksheet.Cells[2,1]).Select();

// 粘贴！

xlWorksheet.Paste(oMissing,oMissing);

// 清空系统剪切板。

System.Windows.Forms.Clipboard.SetDataObject("");

}

// 保存并关闭这个工作簿。

xlApp.ActiveWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );

// xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);

xlWorkbook = null;

MessageBox.Show(@"Excel 文件：C:/exportExcel.xls 导出成功！");

}

catch(Exception ex)

{

MessageBox.Show(ex.Message);

}

finally

{

// 释放...

xlApp.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

xlApp = null;

GC.Collect();

}

总结：这个方法比上面的方法性能好点，但还是很不好用，比原来的提高了2倍左右。

3．原理：利用OLEDB，以excel为数据库，把dataset中的数据导入到excel文件中

 public static void exportToExcelByDataset(string filePath, DataSet ds,XmlNode node)

  {

   string sqlstr;







   if(fi.Exists)

   {

    fi.Delete();

    //     throw new Exception("文件删除失败");

   }

   else

   {

    fi.Create();

   }

   

   string sqlcon=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended ProPerties=Excel 8.0;";

   OleDbConnection olecon = new OleDbConnection(sqlcon);

   OleDbCommand olecmd = new OleDbCommand();

   olecmd.Connection = olecon;

   olecmd.CommandType = CommandType.Text;

 



   try

   {

    olecon.Open();

            



    XmlNode nodec=node.SelectSingleNode("./Method/ShowField");

    int ii = 0;

    sqlstr = "CREATE TABLE sheet1(";

    foreach(XmlNode xnode in nodec.ChildNodes )

    {

     if(ii == nodec.ChildNodes.Count - 1)

     {

      if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")

      {

       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number)";       

      }

      else

      {

       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";

      }

      //      sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";

     }

     else

     {

      if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")

      {

       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number,";       

      }

      else

      {

       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text,";

      }

       

     }

     //     sqlstr =sqlstr + xnode.Attributes["displayname"].Value + " text";       

     ii++;

    }

    olecmd.CommandText = sqlstr;

    olecmd.ExecuteNonQuery();

    for(int i=0;i<ds.Tables[0].Rows.Count;i++)

    {

     sqlstr = "INSERT INTO sheet1 VALUES(";

     int jj=0;



     foreach(XmlNode inode in nodec.ChildNodes )

     {

      if(jj == nodec.ChildNodes.Count-1)

      {

       if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")

       {

        sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ")" ;

        

       }

       else

       {

        sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "')" ;

       }

      }

      else

      {

       if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")

       {

        sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + "," ;

        

       }

       else

       {

        sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "'," ;

       }

      }

      jj++;

     }

     olecmd.CommandText = sqlstr;

     olecmd.ExecuteNonQuery();

     

    }  

    MessageBox.Show(@"Excel文件：" + filePath + " 导出成功！");

   }

   catch(Exception ex)

   {

    MessageBox.Show(ex.Message);

   }

   finally

   {

    olecmd.Dispose();

    olecon.Close();

    olecon.Dispose();

                   

   }

  }

/// <summary>

/// change to string "null" if input is null

/// </summary>

/// <param name="obj"></param>

/// <returns></returns>

 

  private static string isnull(string obj)

  {

   if(obj.Length >0)

   {

    return obj;

   }

   else

   {

    return "null";

   }

  }

总结：这个方法是最好的，速度飞快，比上面两种提高不止10倍，而且关键是不需要用到office组件，所以我正在用着这种方法，客户也满意。当然这个也有它不好的地方，有时候会受到导入的数据不符的异常困扰，而且为了赶时间，代码写的不好，一句话，能用但要改进的地方很多：）

///2007-03-02

最近发现几个导出到EXCEL的方法,这里先记录下来

4.本示例是用于将ListView中的内容倒入到Excel 与常用的逐单元格写不同的是，本例子采用数据写入到range的方法。该方法效率明显较高

Excel.Application app = new Excel.ApplicationClass();    

if( app == null)    

{

     MessageBox.Show("Excel无法启动");

     return;    

}

    app.Visible = true;

    Excel.Workbooks wbs = app.Workbooks;

    Excel.Workbook wb = wbs.Add(Missing.Value);

    Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];

    Excel.Range r = ws.get_Range("A1","H1");

    object [] objHeader = {"标题1","标题2","标题3","标题4","标题5","标题6","标题7","标题8"};

    r.Value = objHeader;

    if (lv.Items.Count >0)

    {

     r = ws.get_Range("A2",Missing.Value);

       object [,] objData = new Object[this.lv.Items.Count,8];

     foreach(ListViewItem lvi in lv.Items)

     {

      objData[lvi.Index,0] = lvi.Text;

      objData[lvi.Index,1] = lvi.SubItems[1].Text;

      objData[lvi.Index,2] = lvi.SubItems[2].Text;

      objData[lvi.Index,3] = lvi.SubItems[3].Text;

      objData[lvi.Index,4] = lvi.SubItems[4].Text;

      objData[lvi.Index,5] = lvi.SubItems[5].Text;

      objData[lvi.Index,6] = lvi.SubItems[6].Text;

      objData[lvi.Index,7] = lvi.SubItems[7].Text;

     }

     r = r.get_Resize(lv.Items.Count,8);

     r.Value = objData;

     r.EntireColumn.AutoFit();

    }

    app = null;

5.由XML文件导出为EXCEL文件

目录下kfcccer.xml为原始数据XML文件，点击生成后会在同级目录下生成kfcccer.xls文件

页面代码如下:

@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">



<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>XML转换Excel演示</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <div>

            <asp:Button ID="btnChange" runat="server" Font-Bold="True" Font-Size="18pt" ForeColor="Black"

                Height="38px" OnClick="btnChange_Click" Text="开始转换" Width="203px" /></div>

    

    </div>

    </form>

</body>

</html>

后台代码:

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.IO;



public partial class _Default : System.Web.UI.Page 

{

    protected void Page_Load(object sender, EventArgs e)

    {



    }

    protected void btnChange_Click(object sender, EventArgs e)

    {

        try

        {

            //要转换的XML文件

            string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xml");

            DataSet dsBook = new DataSet();

            dsBook.ReadXml(XMLFileName);

            int rows = dsBook.Tables[0].Rows.Count + 1;

            int cols = dsBook.Tables[0].Columns.Count;



            //将要生成的Excel文件

            string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xls");

            if (File.Exists(ExcelFileName))

            {

                File.Delete(ExcelFileName);

            }

            StreamWriter writer = new StreamWriter(ExcelFileName, false);

            writer.WriteLine("<?xml version="1.0"?>");

            writer.WriteLine("<?mso-application progid="Excel.Sheet"?>");

            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>Automated Report Generator Example</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>51aspx.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>");

            writer.WriteLine(" <Styles>");

            writer.WriteLine("  <Style ss:ID="Default" ss:Name="Normal">");

            writer.WriteLine("   <Alignment ss:Vertical="Bottom"/>");

            writer.WriteLine("   <Borders/>");

            writer.WriteLine("   <Font/>");

            writer.WriteLine("   <Interior/>");

            writer.WriteLine("   <Protection/>");

            writer.WriteLine("  </Style>");

            writer.WriteLine("  <Style ss:ID="s21">");

            writer.WriteLine("   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>");

            writer.WriteLine("  </Style>");

            writer.WriteLine(" </Styles>");

            writer.WriteLine(" <Worksheet ss:Name="MyReport">");

            writer.WriteLine(string.Format("  <Table ss:ExpandedColumnCount="{0}" ss:ExpandedRowCount="{1}" x:FullColumns="1"", cols.ToString(), rows.ToString()));

            writer.WriteLine("   x:FullRows="1">");



            //生成标题

            writer.WriteLine("<Row>");

            foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)

            {

                writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");

                writer.Write(eachCloumn.ColumnName.ToString());

                writer.WriteLine("</Data></Cell>");

            }

            writer.WriteLine("</Row>");



            //生成数据记录

            foreach (DataRow eachRow in dsBook.Tables[0].Rows)

            {

                writer.WriteLine("<Row>");

                for (int currentRow = 0; currentRow != cols; currentRow++)

                {

                    writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");

                    writer.Write(eachRow[currentRow].ToString());

                    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>");

            writer.WriteLine(" <Worksheet ss:Name="Sheet2">");

            writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");

            writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");

            writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");

            writer.WriteLine("  </WorksheetOptions>");

            writer.WriteLine(" </Worksheet>");

            writer.WriteLine(" <Worksheet ss:Name="Sheet3">");

            writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");

            writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");

            writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");

            writer.WriteLine("  </WorksheetOptions>");

            writer.WriteLine(" </Worksheet>");

            writer.WriteLine("</Workbook>");

            writer.Close();

            Response.Write("<script language="javascript">" + "alert('" + "转换成功! 转换后的Excel文件名为: kfcccer.xls')" + "</script>");

        }

        catch (Exception ex)

        {

            Response.Write("<script language="javascript">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "</script>");

        }

    }

}

6.以模版形式导出为EXCEL文件最近在项目中看到同事的一个导出的解决方案，感觉很不错，以前我如果碰到有些客户要按某些格式把数据导出为EXCEL时，就会感觉很麻烦，有段时间会用水晶报表导出EXCEL的形式来做，但效果很不好，有些控件会提供对应的方法，但这不是我们这些人所喜欢的，我喜欢能自己编，而不喜欢给人已经封装好的东西，除非他提供原代码也可以，呵呵。其实这个方案说起来很简单，就是先做好一个EXCEL模版，然后用代码把对应的数据填进去，这样就可以很好的满足客户的按格式导出的需求，而且还可以让客户来提供模版，这样效果更佳，下面就贴些关键代码：

      ApplicationClass oExcel = null;

        Workbook wb = null;

        Sheets sheets = null;

        _Worksheet worksheet = null;

        Range range = null;



 。。。。

  public void showBookingData(DataSet ds)

        {

            showBrHeard(ds.Tables["BR_HEAD"]);

            showReference(ds.Tables["BR_REFERENCE"]);

            showBrConta(ds.Tables["BR_CONTAINER"]);

            showBrCargo(ds.Tables["BR_CARGO"]);

            showBrParties(ds.Tables["BR_PARTIES"]);

            showBrLocation(ds.Tables["BR_LOCATION"]);

            showBrDoor(ds.Tables["BR_DOOR"]);

        }



        #region 显示Booking数据

        private void showBrHeard(System.Data.DataTable dt)

        {

            if (dt.Rows.Count > 0)

            {

                DataRow dRow = dt.Rows[0];

                if (bkType != "temp")

                {

                    this.range = this.worksheet.get_Range("A2", Type.Missing);

                    this.range.Value2 = "'"+dRow["CMC_BR_NUMBER"].ToString();

                }

                this.range = this.worksheet.get_Range("G2", Type.Missing);

                this.range.Value2 = Utility.GetCarrier(dRow["SCAC_CODE"].ToString());

                this.range = this.worksheet.get_Range("F11", Type.Missing);

                String tfc = dRow["TRAFFIC_MODE"].ToString();

                this.range.Value2 = Utility.GetTrafficById(Convert.ToDecimal(tfc));

                String drm = dRow["PICKUP_DELIVERY"].ToString();

                this.range = this.worksheet.get_Range("H11", Type.Missing);

                this.range.Value2 = Utility.GetDragModeById(Convert.ToDecimal(drm)).ToUpper();



                if (dRow["VESSEL_NAME"].ToString().Trim().Length != 0)

                {

                    this.range = this.worksheet.get_Range("A23", Type.Missing);

                    this.range.Value2 = dRow["VESSEL_NAME"].ToString() + "/" + dRow["VESSEL_VOYAGE"].ToString();

                }

                else

                {

                    if (dRow["EST_DEPARTDATE"].ToString().Trim().Length != 0)

                    {

                        this.range = this.worksheet.get_Range("D23", Type.Missing);

                        this.range.Value2 = Convert.ToDateTime(dRow["EST_DEPARTDATE"]).ToString("yyyy-MM-dd");

                    }

                    else if (dRow["EST_ARRIVDATE"].ToString().Trim().Length != 0)

                    {

                        this.range = this.worksheet.get_Range("C23", Type.Missing);

                        this.range.Value2 = Convert.ToDateTime(dRow["EST_ARRIVDATE"]).ToString("yyyy-MM-dd");

                    }

                }

                writeStrDate("A", 41, dRow["REMARK"].ToString() + "/n");

                if (bkType != "temp")

                {

                    if (dRow["BR_NUMBER"] != null && dRow["BR_NUMBER"].ToString() != "")

                    {

                        strRN += "Booking No." + dRow["BR_NUMBER"].ToString() + "/n";

                    }

                }

                if (dRow["RATE_REFNO"] != null && dRow["RATE_REFNO"].ToString() != "")

                {

                    strRN += "Contact No." + dRow["RATE_REFNO"].ToString() + "/n";

                }

            }

        }



 。。。。。。。

做法简单，但效果很好。唯一缺点就是速度比较慢，而且受限制比较多。

参考地址:http://www.cnblogs.com/kfcccer/archive/2007/05/20/753441.html

7.按XML定义格式把DATASET导出EXCEL 这个方法我没用过,是在CSDN上看到的,主要需求是说在DATESET导出为EXCEL时,需要对其中的数据进行定义,不然导出后与现有格式对不上,比如时间的,导出后会和原来的数据有出入等等一类的问题. 我感觉其实和第五个比较相近,但因为最近比较忙.我也没时间要验证,如果谁用了希望可以反馈给我,谢谢了.下面是代码:

private static void DataSetExportToExcel(DataSet source, string fileName)

{

System.IO.StreamWriter excelDoc;

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

const string startExcelXML = @"<xml version> <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:Vertical=/"Bottom/"/> <Borders/>" +

" <Font/> <Interior/> <NumberFormat/>" +

" <Protection/> </Style> " +

"<Style ss:ID=/"BoldColumn/"> <Font " +

"x:Family=/"Swiss/" ss:Bold=/"1/"/> </Style> " +

"<Style ss:ID=/"StringLiteral/"> <NumberFormat" +

" ss:Format=/"@/"/> </Style> <Style " +

"ss:ID=/"Decimal/"> <NumberFormat " +

"ss:Format=/"0.0000/"/> </Style> " +

"<Style ss:ID=/"Integer/"> <NumberFormat " +

"ss:Format=/"0/"/> </Style> <Style " +

"ss:ID=/"DateLiteral/"> <NumberFormat " +

"ss:Format=/"mm/dd/yyyy;@/"/> </Style> " +

"</Styles> ";

const string endExcelXML = "</Workbook>";

int rowCount = 0;

int sheetCount = 1;

excelDoc.Write(startExcelXML);

excelDoc.Write("<Worksheet ss:Name=/"Sheet" + sheetCount + "/">");

excelDoc.Write("<Table>");

excelDoc.Write("<Row>");

for (int x = 0; x < source.Tables[0].Columns.Count; x++)

{

excelDoc.Write("<Cell ss:StyleID=/"BoldColumn/"><Data ss:Type=/"String/">");

excelDoc.Write(source.Tables[0].Columns[x].ColumnName);

excelDoc.Write("</Data></Cell>");

}

excelDoc.Write("</Row>");

foreach (DataRow x in source.Tables[0].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=/"Sheet" + sheetCount + "/">");

excelDoc.Write("<Table>");

}

excelDoc.Write("<Row>"); //ID=" + rowCount + "

for (int y = 0; y < source.Tables[0].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();

}

simonllf

关注

2
点赞
踩
14

收藏

觉得还不错? 一键收藏
8
评论
对.NET中导出数据到EXCEL的几种方法探讨

最近在做一个报表系统的时候，需要把DATASET中的数据导到EXCEL当中，于是在网上找了一遍，发现了好几种方法，本来以为应该差不多，但后来经过一一试用后，发现在性能上真的差别很大，现在就介绍一下，同时有不对的希望可以指正： 1．原理：利用office组件把dataset中的数据填充到excel文件当中。这里我不贴出全部代码了，只把关键部分贴出来： ///
复制链接

扫一扫