hi guys, it took me some time to find out how to export listview to excel, so i wanted to share it with you. i would like to post this thread as code snippet but in that section there is no attachment button that i can attach project to. i show two ways of exporting, one with assuming that excel is installed on the target machine, the other is with assuming excel is not installed so i chose to export as comma delimited csv file.
Form1.cs :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
namespace ListViewToExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
for (int i = 0; i < 10; i++)
{
myList.Columns.Add(i.ToString());
ListViewItem lv = new ListViewItem(i.ToString());
for (int ai = 0; ai < 10; ai++)
{
lv.SubItems.Add(ai.ToString());
}
myList.Items.Add(lv);
}
}
private void btnExcel_Click(object sender, EventArgs e)
{
Excel.Application app = new Excel.Application();
app.Visible = true;
Excel.Workbook wb = app.Workbooks.Add(1);
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
int i = 1;
int i2 = 1;
foreach (ListViewItem lvi in myList.Items)
{
i = 1;
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
ws.Cells[i2, i] = lvs.Text;
i++;
}
i2++;
}
}
private void btnCsv_Click(object sender, EventArgs e)
{
saveFileDialog1.Filter = "csv files (*.csv)|*.csv";
saveFileDialog1.FileName = "logs";
saveFileDialog1.Title = "Export to Excel";
StringBuilder sb = new StringBuilder();
foreach (ColumnHeader ch in myList.Columns)
{
sb.Append(ch.Text + ",");
}
sb.AppendLine();
foreach (ListViewItem lvi in myList.Items)
{
foreach (ListViewItem.ListViewSubItem lvs in lvi.SubItems)
{
if (lvs.Text.Trim() == string.Empty)
sb.Append(" ,");
else
sb.Append(lvs.Text + ",");
}
sb.AppendLine();
}
DialogResult dr = saveFileDialog1.ShowDialog();
if (dr == DialogResult.OK)
{
StreamWriter sw = new StreamWriter(saveFileDialog1.FileName);
sw.Write(sb.ToString());
sw.Close();
}
}
}
}namespace ListViewToExcel
{
partial class Form1
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.IContainer components = null;
/// <summary>
/// Clean up any resources being used.
/// </summary>
/// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.myList = new System.Windows.Forms.ListView();
this.btnExcel = new System.Windows.Forms.Button();
this.btnCsv = new System.Windows.Forms.Button();
this.saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
this.SuspendLayout();
//
// myList
//
this.myList.Location = new System.Drawing.Point(29, 37);
this.myList.Name = "myList";
this.myList.Size = new System.Drawing.Size(637, 232);
this.myList.TabIndex = 0;
this.myList.UseCompatibleStateImageBehavior = false;
this.myList.View = System.Windows.Forms.View.Details;
//
// btnExcel
//
this.btnExcel.Location = new System.Drawing.Point(29, 8);
this.btnExcel.Name = "btnExcel";
this.btnExcel.Size = new System.Drawing.Size(75, 23);
this.btnExcel.TabIndex = 1;
this.btnExcel.Text = "Excel";
this.btnExcel.UseVisualStyleBackColor = true;
this.btnExcel.Click += new System.EventHandler(this.btnExcel_Click);
//
// btnCsv
//
this.btnCsv.Location = new System.Drawing.Point(122, 8);
this.btnCsv.Name = "btnCsv";
this.btnCsv.Size = new System.Drawing.Size(75, 23);
this.btnCsv.TabIndex = 2;
this.btnCsv.Text = "CSV";
this.btnCsv.UseVisualStyleBackColor = true;
this.btnCsv.Click += new System.EventHandler(this.btnCsv_Click);
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(691, 290);
this.Controls.Add(this.btnCsv);
this.Controls.Add(this.btnExcel);
this.Controls.Add(this.myList);
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
this.ResumeLayout(false);
}
#endregion
private System.Windows.Forms.ListView myList;
private System.Windows.Forms.Button btnExcel;
private System.Windows.Forms.Button btnCsv;
private System.Windows.Forms.SaveFileDialog saveFileDialog1;
}
}the project is attached to this post. notice that excel object libraries 11 is referenced.