添加 引用-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;
}
}
}
}