用C#快速往Excel写数据 (FROM 阿宝的专栏)

于 2006-10-07 15:29:00 发布
阅读量933 收藏
点赞数
分类专栏： C#(其它) 文章标签： excel c# listview button textbox string

添加 引用－COM－Micosoft  EXCEL 11.0 Object Library

using Excel;
using System.Reflection; 

   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");/到E1即可，因为只有五列
   object [] objHeader = {"标题1","标题2","标题3","标题4","标题5"};
   r.Value2 = objHeader;
   if (listView1.Items.Count >0)
   {
    r = ws.get_Range("A2",Missing.Value); 
    object [,] objData = new Object[this.listView1.Items.Count,5];
    foreach(ListViewItem lvi in listView1.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;
    }
    r = r.get_Resize(listView1.Items.Count,5);
    r.Value2 = objData;
    r.EntireColumn.AutoFit();
   }
   app = null;

本人的例子

设置saveFileDialog1
AddExtension 属性为 false
DefaultExt   属性为 xls
filter       属性为  *.xls|*.xls|所有文件|*.*
//导出窗体
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.IO;
using System.Data;

namespace PowerWeldManageForm.GL
{
 /// <summary>
 /// WelderExportFrm 焊工名单导出窗体。
 /// </summary>
 public class WelderExportFrm : System.Windows.Forms.Form
 {
  private System.Windows.Forms.GroupBox groupBox1;
  private System.Windows.Forms.GroupBox groupBox2;
  private System.Windows.Forms.ProgressBar progressBar1;
  private System.Windows.Forms.TextBox textBox1;
  private System.Windows.Forms.Button button1;
  private System.Windows.Forms.ListView listView1;
  private System.Windows.Forms.Button button2;
  private System.Windows.Forms.Button button3;
  public Manage.ExcelOut EO = new PowerWeldManageForm.Manage.ExcelOut();
  /// <summary>
  /// 如:AddExtension 属性为 false FName值为FName 工作薄的名称为FName
, AddExtension 属性为 true  FName值为FName.xls  工作薄的名称为XLS
  /// </summary>
  private string FName="";
  private FileInfo FInfo;
  public RecallDataset RD =new RecallDataset();
  private System.Windows.Forms.ColumnHeader columnHeader1;
  private System.Windows.Forms.ColumnHeader columnHeader2;
  private System.Windows.Forms.ColumnHeader columnHeader3;
  private System.Windows.Forms.ColumnHeader columnHeader4;
  private System.Windows.Forms.ColumnHeader columnHeader5;
  private System.Windows.Forms.ColumnHeader columnHeader6;
  private System.Windows.Forms.SaveFileDialog saveFileDialog1;
  private System.Windows.Forms.ColumnHeader columnHeader7;
  private System.ComponentModel.IContainer components = null;

  public WelderExportFrm()
  {
   //
   // Windows 窗体设计器支持所必需的
   //
   InitializeComponent();

   //
   // TODO: 在 InitializeComponent 调用后添加任何构造函数代码
   //
  }

  /// <summary>
  /// 清理所有正在使用的资源。
  /// </summary>
  protected override void Dispose( bool disposing )
  {
   if( disposing )
   {
    if(components != null)
    {
     components.Dispose();
    }
   }
   base.Dispose( disposing );
  }

  #region Windows 窗体设计器生成的代码
  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {
   this.groupBox1 = new System.Windows.Forms.GroupBox();
   this.button2 = new System.Windows.Forms.Button();
   this.button1 = new System.Windows.Forms.Button();
   this.textBox1 = new System.Windows.Forms.TextBox();
   this.groupBox2 = new System.Windows.Forms.GroupBox();
   this.progressBar1 = new System.Windows.Forms.ProgressBar();
   this.listView1 = new System.Windows.Forms.ListView();
   this.columnHeader1 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader2 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader3 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader4 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader5 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader6 = new System.Windows.Forms.ColumnHeader();
   this.columnHeader7 = new System.Windows.Forms.ColumnHeader();
   this.button3 = new System.Windows.Forms.Button();
   this.saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
   this.groupBox1.SuspendLayout();
   this.groupBox2.SuspendLayout();
   this.SuspendLayout();
   //
   // groupBox1
   //
   this.groupBox1.Controls.Add(this.button2);
   this.groupBox1.Controls.Add(this.button1);
   this.groupBox1.Controls.Add(this.textBox1);
   this.groupBox1.Location = new System.Drawing.Point(8, 8);
   this.groupBox1.Name = "groupBox1";
   this.groupBox1.Size = new System.Drawing.Size(496, 64);
   this.groupBox1.TabIndex = 0;
   this.groupBox1.TabStop = false;
   this.groupBox1.Text = "请选择文件保存路径";
   //
   // button2
   //
   this.button2.Enabled = false;
   this.button2.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
   this.button2.Location = new System.Drawing.Point(407, 23);
   this.button2.Name = "button2";
   this.button2.TabIndex = 2;
   this.button2.Text = "导出";
   this.button2.Click += new System.EventHandler(this.button2_Click);
   //
   // button1
   //
   this.button1.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
   this.button1.Location = new System.Drawing.Point(352, 23);
   this.button1.Name = "button1";
   this.button1.Size = new System.Drawing.Size(40, 23);
   this.button1.TabIndex = 1;
   this.button1.Text = "浏览";
   this.button1.Click += new System.EventHandler(this.button1_Click);
   //
   // textBox1
   //
   this.textBox1.Location = new System.Drawing.Point(24, 24);
   this.textBox1.Name = "textBox1";
   this.textBox1.ReadOnly = true;
   this.textBox1.Size = new System.Drawing.Size(304, 21);
   this.textBox1.TabIndex = 0;
   this.textBox1.Text = "";
   //
   // groupBox2
   //
   this.groupBox2.Controls.Add(this.progressBar1);
   this.groupBox2.Location = new System.Drawing.Point(8, 72);
   this.groupBox2.Name = "groupBox2";
   this.groupBox2.Size = new System.Drawing.Size(496, 72);
   this.groupBox2.TabIndex = 1;
   this.groupBox2.TabStop = false;
   this.groupBox2.Text = "进度条";
   //
   // progressBar1
   //
   this.progressBar1.Location = new System.Drawing.Point(24, 31);
   this.progressBar1.Name = "progressBar1";
   this.progressBar1.Size = new System.Drawing.Size(448, 23);
   this.progressBar1.TabIndex = 0;
   //
   // listView1
   //
   this.listView1.Columns.AddRange(new System.Windows.Forms.ColumnHeader[] {
             

         this.columnHeader1,
             

         this.columnHeader2,
             

         this.columnHeader3,
             

         this.columnHeader4,
             

         this.columnHeader5,
             

         this.columnHeader6,
             

         this.columnHeader7});
   this.listView1.FullRowSelect = true;
   this.listView1.GridLines = true;
   this.listView1.HideSelection = false;
   this.listView1.Location = new System.Drawing.Point(8, 152);
   this.listView1.MultiSelect = false;
   this.listView1.Name = "listView1";
   this.listView1.Size = new System.Drawing.Size(496, 176);
   this.listView1.TabIndex = 2;
   this.listView1.View = System.Windows.Forms.View.Details;
   //
   // columnHeader1
   //
   this.columnHeader1.Text = "姓名";
   this.columnHeader1.Width = 70;
   //
   // columnHeader2
   //
   this.columnHeader2.Text = "性别";
   this.columnHeader2.Width = 50;
   //
   // columnHeader3
   //
   this.columnHeader3.Text = "种类";
   //
   // columnHeader4
   //
   this.columnHeader4.Text = "钢印代号";
   this.columnHeader4.Width = 90;
   //
   // columnHeader5
   //
   this.columnHeader5.Text = "等级";
   //
   // columnHeader6
   //
   this.columnHeader6.Text = "单位名称";
   this.columnHeader6.Width = 100;
   //
   // columnHeader7
   //
   this.columnHeader7.Text = "状态";
   //
   // button3
   //
   this.button3.DialogResult = System.Windows.Forms.DialogResult.Cancel;
   this.button3.FlatStyle = System.Windows.Forms.FlatStyle.Popup;
   this.button3.Location = new System.Drawing.Point(430, 333);
   this.button3.Name = "button3";
   this.button3.TabIndex = 3;
   this.button3.Text = "关闭";
   this.button3.Click += new System.EventHandler(this.button3_Click);
   //
   // saveFileDialog1
   //
   this.saveFileDialog1.AddExtension = false;
   this.saveFileDialog1.DefaultExt = "xls";
   this.saveFileDialog1.Filter = "*.xls|*.xls|所有文件|*.*";
   this.saveFileDialog1.Title = "保存文件";
   this.saveFileDialog1.FileOk += new System.ComponentModel.CancelEventHandler

(this.saveFileDialog1_FileOk);
   //
   // WelderExportFrm
   //
   this.AcceptButton = this.button2;
   this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
   this.CancelButton = this.button3;
   this.ClientSize = new System.Drawing.Size(512, 358);
   this.Controls.Add(this.button3);
   this.Controls.Add(this.listView1);
   this.Controls.Add(this.groupBox2);
   this.Controls.Add(this.groupBox1);
   this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;
   this.MaximizeBox = false;
   this.MinimizeBox = false;
   this.Name = "WelderExportFrm";
   this.ShowInTaskbar = false;
   this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
   this.Text = "焊工名单导出";
   this.Load += new System.EventHandler(this.WelderExportFrm_Load);
   this.groupBox1.ResumeLayout(false);
   this.groupBox2.ResumeLayout(false);
   this.ResumeLayout(false);

  }
  #endregion

  private void WelderExportFrm_Load(object sender, System.EventArgs e)
  {
   if (RD.ValidateHelpDataSet())
   {
    listView1.Items.Clear();
    DataRow[] DR=RD.HelpDs.welder.Select("","WelderName");//WelderName为

焊工姓名
    int a=0;
    foreach(DataRow dr in DR)
    {        
     listView1.Items.Add(dr["WelderName"].ToString());  

//焊工姓名      
     listView1.Items[a].SubItems.Add(dr["WelderSex"].ToString());

                     //焊工性别       
     listView1.Items[a].SubItems.Add(dr["WelderType"].ToString

());                      //焊工种类
     listView1.Items[a].SubItems.Add(dr["SealSn"].ToString());   

                      //焊工钢印代号
     listView1.Items[a].SubItems.Add(dr["PrjSn"].ToString());    

                      //焊工等级
     listView1.Items[a].SubItems.Add(dr["Department"].ToString

());                         //单位名称
     //listView1.Items[a].SubItems.Add("");
     if(dr["WelderStt"].ToString()=="0")
     {
      listView1.Items[a].SubItems.Add("在现场");  

    //焊工状态（0在现场、1不在现场）  
     }
     if(dr["WelderStt"].ToString()=="1")
     {
      listView1.Items[a].SubItems.Add("不在现场");  

    //焊工状态（0在现场、1不在现场 
     }
     if(dr["WelderStt"].ToString()=="")
     {
      listView1.Items[a].SubItems.Add("");   

   //焊工状态（0在现场、1不在现场 
     }
     a++;                        
    }
   }
  }
  private void button3_Click(object sender, System.EventArgs e)
  {
   this.Close();
  }
  /// <summary>
  /// 浏览
  /// </summary>
  /// <param name="sender"></param>
  /// <param name="e"></param>
  private void button1_Click(object sender, System.EventArgs e)
  {
   saveFileDialog1.ShowDialog();
   textBox1.Text = saveFileDialog1.FileName.ToString();
   if( textBox1.Text.Trim() != "" )
   {
    button2.Enabled = true;
    button2.Focus();
   }
  }
  /// <summary>
  /// 导出
  /// </summary>
  /// <param name="sender"></param>
  /// <param name="e"></param>
  private void button2_Click(object sender, System.EventArgs e)
  {
   EO.ExportToExcel(textBox1.Text,listView1,progressBar1,FName); 
   textBox1.Text="";
   button2.Enabled=false;
   progressBar1.Value=0;
  }

  private void saveFileDialog1_FileOk(object sender, System.ComponentModel.CancelEventArgs e)
  {
   FInfo=new FileInfo(saveFileDialog1.FileName);
   FName=FInfo.Name.ToString()+System.DateTime.Now.Year.ToString()

+System.DateTime.Now.Month.ToString()+System.DateTime.Now.Day.ToString()

+System.DateTime.Now.Hour.ToString()+System.DateTime.Now.Minute.ToString()

+System.DateTime.Now.Second.ToString();
  }
 }
}
///
//导出中所用到的类ExcelOut
using System;
using System.Data.OleDb;
using System.IO;
using System.Windows.Forms;

namespace PowerWeldManageForm.Manage
{
 /// <summary>
 /// ExcelOut 的摘要说明。
 /// </summary>
 public class ExcelOut
 {
  private string ExcelName ="";

  public ExcelOut()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }
  public ExcelOut(string tableName)
  {
   this.ExcelName =tableName;
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }

  public void ExportToExcel(string fileName,ListView LV,ProgressBar PB,string FName)
  {
   try
   {
    int ColumnNum = LV.Columns.Count;
    string[] ColumnNames = new string[ColumnNum];
    for(int i=0;i<=LV.Columns.Count-1;i++)
    {
     ColumnNames[i] = LV.Columns[i].Text.Trim();
    }

    string OLEDBConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data

Source="+fileName+";";  
    OLEDBConnStr +=   " Extended Properties=Excel 8.0;";

    string createStr = "create table "+FName+"(";
    for(int i=0;i<=LV.Columns.Count-1;i++)
    {
     createStr = createStr +ColumnNames[i]+" varchar,";
    }
    createStr = createStr.Remove(createStr.Length-1,1);
    createStr = createStr+")";

    OleDbConnection oConn = new OleDbConnection();
  
    oConn.ConnectionString = OLEDBConnStr;
    OleDbCommand oCreateComm = new OleDbCommand();
    oCreateComm.Connection = oConn;
    //MessageBox.Show(createStr.ToString());
    oCreateComm.CommandText = createStr;
              
    try
    {
     oConn.Open();
     oCreateComm.ExecuteNonQuery();
    }
    catch(OleDbException ee)
    {
     MessageBox.Show(ee.ToString());                   
    }

    //中间填充数据;
    string insertStr = "insert into "+FName+"  values(";
    PB.Maximum=LV.Items.Count;
    //MessageBox.Show(LV.Items.Count.ToString());
    PB.Minimum=0;
    
    for(int i=0;i<=LV.Items.Count-1;i++)
    {
     try
     {
      for (int m=0;m<=LV.Columns.Count-1;m++)
      {
       insertStr = insertStr +"'"+LV.Items

[i].SubItems[m].Text.ToString()+"',";       
      }
      insertStr = insertStr.Remove(insertStr.Length-1,1)

+")";
      oCreateComm.CommandText = insertStr;   

   
      PB.Value=i+1;
      oCreateComm.ExecuteNonQuery();
      
     }
     catch
     {
      MessageBox.Show("导出数据失败，请重新导

出！",SysInfo.SysMess,MessageBoxButtons.OK,MessageBoxIcon.Information);
      return;
     }
     insertStr = "insert into "+FName+"  values(";
     
    }
    //中间填充数据;

    oConn.Close();
    MessageBox.Show("导出数据成功，文件保存

在"+fileName+"",SysInfo.SysMess);
   }
   catch
   {
    MessageBox.Show("导出数据失败，请检查是否安装有Excel！或没有获得数据

！",SysInfo.SysMess,MessageBoxButtons.OK,MessageBoxIcon.Information);
    return;
   }
  }
 }
}

guolei0451
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值