C#数据导出器

来源：互联网发布：白乔丹弹跳数据编辑：程序博客网时间：2024/05/19 22:02

看到网上不少介绍有关数据导出的方法，但是总显得凌乱、琐碎，用起来也很不爽。索性一起收集起来，整合到一块。下面介绍一个整合导出到word、excel、text的导出控件，具体做法是用一个扩展的按钮固化上一个菜单及其事件，程序员需要做的仅仅是赋予此控件一个DataTable类型的属性实例。

以下初始资源均来自网上，本人代为整合和优化，如有错误，欢迎来信批评。

using System;

using System.Windows.Forms;

using System.ComponentModel;

using System.Data;

using System.Drawing;

namespace JueJue2007

{

/**

* Author: Liu Juejue

* Date: 2007-3-9

* Description: Export the data to word or excel or txt file.

* Copyleft: Liu Juejue

**/

/// <summary>

/// 导出数据到Word、Excel、Text

/// </summary>

public class ExporterButton : MyXPButton

{

public ExporterButton()

{

this.InitializeComponent();

}

#region 字段

//菜单

private ContextMenu contextMenu1;

private MenuItem menuItem2;

private MenuItem menuItem3;

private MenuItem menuItem4;

private YuanFeiSoftware.YuanFeiXPControls.DNetMenu dNetMenu1;

string category = string.Empty;

/// <summary>

/// 数据源

/// </summary>

private DataTable dataSource = null;

/// <summary>

/// 存放路径

/// </summary>

private string path = string.Empty;

/// <summary>

/// 标题

/// </summary>

private string caption = "朗远软件";

#endregion

#region 属性

/// <summary>

/// 存放路径

/// </summary>

public string Path

{

get

{

return this.path;

}

set

{

path = value;

}

/// <summary>

/// 要导出的数据

/// </summary>

public DataTable DataSource

{

get

{

return this.dataSource;

}

set

{

this.dataSource = value;

}

[ Category( "导出项" ), Description( "指定导出内容的标题" ), DefaultValue( "朗远软件" ) ]

public string Caption

{

get

{

return this.caption;

}

set

{

this.caption = value;

}

#endregion

#region 实例化控件变量

private void InitializeComponent()

{

this.contextMenu1 = new System.Windows.Forms.ContextMenu();

this.menuItem2 = new System.Windows.Forms.MenuItem();

this.menuItem3 = new System.Windows.Forms.MenuItem();

this.menuItem4 = new System.Windows.Forms.MenuItem();

this.dNetMenu1 = new YuanFeiSoftware.YuanFeiXPControls.DNetMenu();

// contextMenu1

this.contextMenu1.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {

this.menuItem2,

this.menuItem3,

this.menuItem4});

// menuItem2

this.menuItem2.Index = 0;

this.dNetMenu1.SetSideBarForeColor(this.menuItem2, System.Drawing.Color.Cornsilk);

this.dNetMenu1.SetSideBarGradientColorOne(this.menuItem2, System.Drawing.SystemColors.InactiveCaption);

this.dNetMenu1.SetSideBarGradientColorTwo(this.menuItem2, System.Drawing.SystemColors.ActiveCaption);

this.dNetMenu1.SetSideBarText(this.menuItem2, null);

this.menuItem2.Text = "Word文档";

this.menuItem2.Click += new EventHandler( ExportData );

// menuItem3

this.menuItem3.Index = 1;

this.dNetMenu1.SetSideBarForeColor(this.menuItem3, System.Drawing.Color.Cornsilk);

this.dNetMenu1.SetSideBarGradientColorOne(this.menuItem3, System.Drawing.SystemColors.InactiveCaption);

this.dNetMenu1.SetSideBarGradientColorTwo(this.menuItem3, System.Drawing.SystemColors.ActiveCaption);

this.dNetMenu1.SetSideBarText(this.menuItem3, null);

this.menuItem3.Text = "文本文件";

this.menuItem3.Click += new EventHandler( ExportData );

// menuItem4

this.menuItem4.Index = 2;

this.dNetMenu1.SetSideBarForeColor(this.menuItem4, System.Drawing.Color.Cornsilk);

this.dNetMenu1.SetSideBarGradientColorOne(this.menuItem4, System.Drawing.SystemColors.InactiveCaption);

this.dNetMenu1.SetSideBarGradientColorTwo(this.menuItem4, System.Drawing.SystemColors.ActiveCaption);

this.dNetMenu1.SetSideBarText(this.menuItem4, null);

this.menuItem4.Text = "Excel文件";

this.menuItem4.Click += new EventHandler( ExportData );

// dNetMenu1

this.dNetMenu1.CheckBorderColor = System.Drawing.SystemColors.Highlight;

this.dNetMenu1.CheckBoxColor = System.Drawing.Color.White;

this.dNetMenu1.GradientColorOne = System.Drawing.Color.White;

this.dNetMenu1.GradientColorTwo = System.Drawing.Color.LightBlue;

this.dNetMenu1.Location = new System.Drawing.Point(46, 78);

this.dNetMenu1.MenuBorderColor = System.Drawing.SystemColors.ControlDark;

this.dNetMenu1.Name = "dNetMenu1";

this.dNetMenu1.SelectionBorderColor = System.Drawing.Color.FromArgb(((System.Byte)(10)), ((System.Byte)(36)), ((System.Byte)(106)));

this.dNetMenu1.SelectionColor = System.Drawing.Color.FromArgb(((System.Byte)(182)), ((System.Byte)(189)), ((System.Byte)(210)));

this.dNetMenu1.Size = new System.Drawing.Size(32, 32);

this.dNetMenu1.StripeColor = System.Drawing.Color.FromArgb(((System.Byte)(219)), ((System.Byte)(216)), ((System.Byte)(209)));

this.dNetMenu1.TabIndex = 3;

this.dNetMenu1.TopMenuBackColor = System.Drawing.SystemColors.Control;

this.dNetMenu1.TopMenuHotBorder = System.Drawing.Color.FromArgb(((System.Byte)(10)), ((System.Byte)(36)), ((System.Byte)(106)));

this.dNetMenu1.TopMenuHotColor = System.Drawing.Color.FromArgb(((System.Byte)(182)), ((System.Byte)(189)), ((System.Byte)(210)));

this.dNetMenu1.TopMenuSelectedColor = System.Drawing.Color.FromArgb(((System.Byte)(182)), ((System.Byte)(189)), ((System.Byte)(210)));

this.Controls.Add(this.dNetMenu1);

this.Name = "JueJue2007";

this.Text = "导出到……";

this.Size = new Size( 138, 23);

this.ContextMenu = this.contextMenu1;

this.SuspendLayout();

}

#endregion

/// <summary>

/// 覆盖单击事件

/// </summary>

/// <param name="e">数据</param>

protected override void OnClick(EventArgs e)

{

this.contextMenu1.Show( this, new Point( 0, this.Height + 1) );

}

private void ExportData( object sender, System.EventArgs e )

{

MenuItem m = sender as MenuItem;

category = m.Text;

//多线程处理

System.Threading.Thread t = new System.Threading.Thread( new System.Threading.ThreadStart( this.ExportDataThread ) );

t.Start();

}

private void ExportDataThread( )

{

if( this.DataSource == null || this.DataSource.Rows.Count == 0 )

{

MessageBox.Show( "没有找到需要导出的数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation );

return;

}

// 1 WORD

if( category == "Word文档" )

{

SaveFileDialog of = new SaveFileDialog();

of.Filter = "Word文档|*.doc";

if( of.ShowDialog() == DialogResult.OK )

{

string path = of.FileName;

//DataTable dt = Oracle.GetDataTable( "select t.id as ID, t.code 代码, t.py_code 拼音码, t.wb_code 五笔码, t.ITEM_NAME 项目名称, t.COMMENTS 备注 from base_zykj_xm t " );

Manipulate.ExportToWord( this, DataSource, path, Caption );

}

// 2 EXCEL

else if( category == "Excel文件" )

{

SaveFileDialog of = new SaveFileDialog();

of.Filter = "Excel文件|*.xls";

if( of.ShowDialog() == DialogResult.OK )

{

string path = of.FileName;

//DataTable dt = Oracle.GetDataTable( "select t.id as ID, t.code 代码, t.py_code 拼音码, t.wb_code 五笔码, t.ITEM_NAME 项目名称, t.COMMENTS 备注 from base_zykj_xm t " );

Manipulate.ExportToExcel( this, DataSource, path, Caption );

}

// 3 文本文件

else

{

SaveFileDialog of = new SaveFileDialog();

of.Filter = "文本文件|*.txt";

if( of.ShowDialog() == DialogResult.OK )

{

string path = of.FileName;

//DataTable dt = Oracle.GetDataTable( "select t.id as ID, t.code 代码, t.py_code 拼音码, t.wb_code 五笔码, t.ITEM_NAME 项目名称, t.COMMENTS 备注 from base_zykj_xm t " );

Manipulate.ExportToText( DataSource, path, Caption );

}

// 具体的导出方法

using System;

using System.IO;

using System.Data;

using System.Windows.Forms;

using e = Microsoft.Office.Interop.Excel;

using w = Microsoft.Office.Interop.Word;

namespace JueJue2007

{

/// <summary>

/// 整合一组导出方法

/// </summary>

public class Manipulate

{

#region EXCEL

/// <summary>

/// 导出文件到EXCEL

/// </summary>

/// <param name="dt">要导出的数据</param>

/// <param name="path">存放路径</param>

public static void ExportToExcel( Button b, DataTable dt, string path, string caption )

{

object Nothing = System.Reflection.Missing.Value;

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

app.Workbooks.Add( Nothing );

try

{

int ColumnLen = dt.Columns.Count;

int RowLen = dt.Rows.Count;

app.Cells[1, 1 ] = caption;

//写列名

for( int m = 0; m < ColumnLen; m ++ )

{

app.Cells[ 2, 1 + m ] = dt.Columns[ m ].ColumnName;

}

//写行

int ii = dt.Rows.Count;

b.Text = "剩余" + ii.ToString() + "条数据";

for( int n = 0; n < RowLen; n ++ )

{

for( int j = 0; j < ColumnLen; j ++ )

{

app.Cells[ 3 + n, 1 + j ] = dt.Rows[ n ][ j ].ToString();

}

ii -= 1;

b.ForeColor = System.Drawing.Color.Blue;

b.Text = "剩余" + ii.ToString() + "条数据";

b.Refresh();

}

b.Text = "导出到……";

b.Refresh();

try

{

//保存

app.ActiveWorkbook._SaveAs( path, Nothing, Nothing, Nothing, Nothing, Nothing, e.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing );

}

catch

{

System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件： " + path );

return;

}

//app.Visible = true; //让其打开

}

catch(Exception e)

{

System.Windows.Forms.MessageBox.Show("向excel文件中写入数据出错： " + e.Message + e.StackTrace + e.Source );

}

app.Workbooks.Close();

MessageBox.Show( "已经成功导出到：/n" + path + ".", "操作成功", MessageBoxButtons.OK, MessageBoxIcon.Information );

}

#endregion

#region WORD

/// <summary>

/// 导出数据到WORD文件中

/// </summary>

/// <param name="dt">数据</param>

/// <param name="path">存放路径</param>

public static void ExportToWord( Button b, DataTable dt, string path, string caption )

{

object tableBehavior = w.WdDefaultTableBehavior.wdWord9TableBehavior;

object autoFitBehavior = w.WdAutoFitBehavior.wdAutoFitFixed;

object unit = w.WdUnits.wdStory;

object extend = System.Reflection.Missing.Value;

object breakType = (int)w.WdBreakType.wdSectionBreakNextPage;

object count = 1;

object character = w.WdUnits.wdCharacter;

object Nothing = System.Reflection.Missing.Value;

try

{

b.Text = dt.Rows.Count.ToString();

//创建一个word文件，文件名用系统时间生成精确到毫秒

w.Application myWord= new w.ApplicationClass();

w._Document myDoc = new w.DocumentClass();

myDoc = myWord.Documents.Add(ref Nothing,ref Nothing,ref Nothing,ref Nothing);

myDoc.Activate();

//向把dataset中的表插入到word的文件中

myWord.Application.Selection.TypeText( caption );

myWord.Application.Selection.TypeParagraph();

w.Range para = myWord.Application.Selection.Range;

myDoc.Tables.Add(para, dt.Rows.Count+1,dt.Columns.Count,ref tableBehavior,ref autoFitBehavior);

int ii = dt.Rows.Count;

b.Text = "剩余" + ii.ToString() + "条数据";

for(int column = 0; column< dt.Columns.Count;column++)

{

myDoc.Tables[ 1 ].Cell(1,column+1).Range.InsertBefore( dt.Columns[column].ColumnName.Trim() );

}

for( int row = 0; row < dt.Rows.Count; row ++ )

{

for( int column = 0; column<dt.Columns.Count; column++ )

{

myDoc.Tables[ 1 ].Cell( row+2, column+1 ).Range.InsertBefore(dt.Rows[row][column].ToString().Trim() );

}

ii -= 1;

b.ForeColor = System.Drawing.Color.Blue;

b.Text = "剩余" + ii.ToString() + "条数据";

b.Refresh();

}

b.Text = "导出到……";

b.Refresh();

myWord.Application.Selection.EndKey(ref unit,ref extend);

myWord.Application.Selection.TypeParagraph();

myWord.Application.Selection.InsertBreak(ref breakType);

myWord.Application.Selection.TypeBackspace();

myWord.Application.Selection.Delete(ref character,ref count);

myWord.Application.Selection.HomeKey(ref unit,ref extend);

//保存word文件到指定的目录下

try

{

object PATH = path;

myDoc.SaveAs( ref PATH,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing, ref Nothing , ref Nothing , ref Nothing , ref Nothing , ref Nothing );

//myWord.Visible = true; //打开WORD文件

}

catch

{

System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件： " + path );

return;

}

//关闭WordDoc文档对象

myDoc.Close(ref Nothing, ref Nothing, ref Nothing);

//关闭WordApp组件对象

myWord.Quit(ref Nothing, ref Nothing, ref Nothing);

}

catch(Exception ex)

{

System.Windows.Forms.MessageBox.Show("向word文件中写入数据出错： " + ex.Message);

}

MessageBox.Show( "已经成功导出到：/n" + path + ".", "操作成功", MessageBoxButtons.OK, MessageBoxIcon.Information );

}

#endregion

#region Text

public static void ExportToText( DataTable dt, string path, string caption )

{

int DATADISTANCE = 5;

int TABDISTANCE = 8;

//创建一个.txt文件，文件名用系统时间生成精确到毫秒

FileInfo file = new FileInfo( path );

StreamWriter textFile = null;

try

{

textFile = file.CreateText();

}

catch

{

System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件： " + path );

return;

}

//统计当前表的行数

int row = dt.Rows.Count;

//统计当前表的列数

int column = dt.Columns.Count;

//用于统计当前表中每列记录中字符数最长的字符串的长度之和

int totalLength = 0;

//用于统计标题的长度（dataset中的表名的length+"表的数据如下"的length）

int titleLength = 0;

//统计每列记录中字符数最长的字符串的长度

int[] columnLength = new int[column];

for( int i = 0; i < column; i ++ )

{

columnLength[i] = dt.Columns[i].ColumnName.ToString().Length;

}

for(int i = 0;i<row;i++)

{

for(int j = 0;j<column;j++)

{

if(dt.Rows[i][j].ToString().Length > columnLength[j] )

{

columnLength[j] = dt.Rows[i][j].ToString().Length;

}

//统计当前表中每列记录中字符数最长的字符串的长度之和

for(int i = 0;i<column;i++)

{

totalLength = totalLength+columnLength[i]+DATADISTANCE;

}

totalLength = totalLength+2 * TABDISTANCE - DATADISTANCE;

//统计标题的长度（dataset中的当前表名的length+"表的数据如下"的length）

titleLength = dt.TableName.ToString().Length+"表的数据如下".Length*2;

//把标题写入.txt文件中

for(int i = 0;i<(int)((totalLength-titleLength)/2);i++)

{

textFile.Write(' ');

}

textFile.Write( caption );

textFile.WriteLine();

for(int i = 0;i<totalLength;i++)

{

textFile.Write('*');

}

textFile.WriteLine();

textFile.Write("/t");

//把dataset中当前表的字段名写入.txt文件中

for(int i = 0;i<column;i++)

{

textFile.Write(dt.Columns[i].ColumnName.ToString());

for(int k = 0;k<columnLength[i]-dt.Columns[i].ColumnName.ToString().Length+DATADISTANCE;k++)

{

textFile.Write(' ');

}

textFile.WriteLine();

for(int i = 0;i<totalLength;i++)

{

textFile.Write('-');

}

textFile.WriteLine();

textFile.Write("/t");

//把dataset中当前表的数据写入.txt文件中

for(int i = 0;i<row;i++)

{

for(int j = 0;j<column;j++)

{

textFile.Write(dt.Rows[i][j].ToString());

for(int k = 0;k<columnLength[j]-dt.Rows[i][j].ToString().Length+DATADISTANCE;k++)

{

textFile.Write(' ');

}

textFile.WriteLine();

textFile.Write("/t");

}

textFile.WriteLine();

for(int i = 0;i<totalLength;i++)

{

textFile.Write('-');

}

textFile.WriteLine();

//关闭当前的StreamWriter流

textFile.Close();

MessageBox.Show( "已经成功导出到：/n" + path + ".", "操作成功", MessageBoxButtons.OK, MessageBoxIcon.Information );

}

#endregion

}