Sprache: C#
Dieses Snippet generiert ein Excel-Sheet aus einer Listview.
Es basiert auf dem Beispiel in der MSDN Referenz.
"using Excel" verweist auf die COM-Referenz "Microsoft Excel 10.0 Object Library".
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using Excel;
namespace InventoryManager
{
public class ExcelExport
{
public ExcelExport(ListView Listview, string Title)
{
this.listview = Listview;
this.title = Title;
}
#region Private Members
private ListView listview;
private string title;
private double onePoint = 0.13;
private string[] columnLetters = { "A", "B", "C", "D", "F", "E", "F", "G", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
// Excel object references.
private Excel.Application objExcel = null;
private Excel.Workbooks objBooks = null;
private Excel._Workbook objBook = null;
private Excel.Sheets objSheets = null;
private Excel._Worksheet objSheet = null;
private Excel.Range objRange = null;
// Frequenty-used variable for optional arguments.
private object objOpt = System.Reflection.Missing.Value;
#endregion
#region Public Methods
public void GenerateDynamicExcelSheet()
{
try
{
// Show SaveFileDialog
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "In Excel exportieren";
sfd.DefaultExt = "*.xls";
sfd.RestoreDirectory = true;
sfd.AddExtension = true;
sfd.Filter = "Excel Sheets|*.xls";
DialogResult dr = sfd.ShowDialog();
if (dr == DialogResult.Cancel)
{
MessageBox.Show("return");
return;
}
string path = sfd.FileName;
// Start a new workbook in Excel.
objExcel = new Excel.Application();
objBooks = (Excel.Workbooks)objExcel.Workbooks;
objBook = (Excel._Workbook)(objBooks.Add(objOpt));
objSheets = (Excel.Sheets)objBook.Worksheets;
objSheet = (Excel._Worksheet)(objSheets.get_Item(1));
// Determine column count
int columncount = 0;
object[] objHeaders = new object[26];
foreach (ColumnHeader ch in this.listview.Columns)
{
objHeaders[columncount] = ch.Text;
columncount++;
}
columncount--;
// Merge title cells
objRange = objSheet.get_Range("A1", this.columnLetters[columncount] + "1");
objRange.MergeCells = true;
objRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
// Set table title and format it
objRange = objSheet.get_Range("A1", "A1");
objRange.set_Value(objOpt, this.title);
objRange.Font.Size = "14";
objRange.Font.Name = "Arial";
objRange.Font.Bold = true;
// Format header cells
objRange = objSheet.get_Range("A3", this.columnLetters[columncount] + "3");
objRange.set_Value(objOpt, objHeaders);
objRange.Font.Bold = true;
objRange.Font.Italic = true;
objRange.Font.Name = "Arial";
// Fill Cells with the listview data
int i = 4;
foreach (ListViewItem item in this.listview.Items)
{
for (int t = 0; t <= columncount; t++)
{
objRange = objSheet.get_Range(this.columnLetters[t] + i.ToString(), this.columnLetters[t] + i.ToString());
objRange.set_Value(objOpt, item.SubItems[t].Text);
objRange.HorizontalAlignment = Excel.Constants.xlLeft;
objRange.Font.Size = "8";
objRange.Font.Name = "Arial";
}
i++;
}
// Set Column widths
int x = 0;
foreach (ColumnHeader ch in this.listview.Columns)
{
objRange = objSheet.get_Range(this.columnLetters[x] + "1", this.columnLetters[x] + "1");
objRange.Columns.ColumnWidth = convertPixelToPoint(ch.Width);
x++;
}
// Set table borders
int j = i - 1;
objRange = objSheet.get_Range("A3", this.columnLetters[columncount] + j.ToString());
objRange.Borders.Value = XlBordersIndex.xlInsideHorizontal;
objRange.Borders.LineStyle = XlLineStyle.xlContinuous;
objRange.Borders.Weight = XlBorderWeight.xlThin;
// Set title border
objRange = objSheet.get_Range("A3", this.columnLetters[columncount] + "3");
objRange.BorderAround(Excel.XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, 001001001);
// Save the workbook and quit Excel.
objBook.SaveAs(path, objOpt, objOpt,
objOpt, objOpt, objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
objOpt, objOpt, objOpt, objOpt, objOpt);
objBook.Close(false, objOpt, objOpt);
objExcel.Quit();
MessageBox.Show("I bi fertig im Fall du");
}
catch (Exception ex)
{
MessageBox.Show("Es trat ein Fehler beim Speichern auf!nn" + ex.ToString(), "In Excel exportieren", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion
#region Private Methods
private double convertPixelToPoint(int Pixel)
{
return this.onePoint * Convert.ToDouble(Pixel + 35);
}
#endregion
}
}
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using Excel;
namespace InventoryManager
{
public class ExcelExport
{
public ExcelExport(ListView Listview, string Title)
{
this.listview = Listview;
this.title = Title;
}
#region Private Members
private ListView listview;
private string title;
private double onePoint = 0.13;
private string[] columnLetters = { "A", "B", "C", "D", "F", "E", "F", "G", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
// Excel object references.
private Excel.Application objExcel = null;
private Excel.Workbooks objBooks = null;
private Excel._Workbook objBook = null;
private Excel.Sheets objSheets = null;
private Excel._Worksheet objSheet = null;
private Excel.Range objRange = null;
// Frequenty-used variable for optional arguments.
private object objOpt = System.Reflection.Missing.Value;
#endregion
#region Public Methods
public void GenerateDynamicExcelSheet()
{
try
{
// Show SaveFileDialog
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "In Excel exportieren";
sfd.DefaultExt = "*.xls";
sfd.RestoreDirectory = true;
sfd.AddExtension = true;
sfd.Filter = "Excel Sheets|*.xls";
DialogResult dr = sfd.ShowDialog();
if (dr == DialogResult.Cancel)
{
MessageBox.Show("return");
return;
}
string path = sfd.FileName;
// Start a new workbook in Excel.
objExcel = new Excel.Application();
objBooks = (Excel.Workbooks)objExcel.Workbooks;
objBook = (Excel._Workbook)(objBooks.Add(objOpt));
objSheets = (Excel.Sheets)objBook.Worksheets;
objSheet = (Excel._Worksheet)(objSheets.get_Item(1));
// Determine column count
int columncount = 0;
object[] objHeaders = new object[26];
foreach (ColumnHeader ch in this.listview.Columns)
{
objHeaders[columncount] = ch.Text;
columncount++;
}
columncount--;
// Merge title cells
objRange = objSheet.get_Range("A1", this.columnLetters[columncount] + "1");
objRange.MergeCells = true;
objRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
// Set table title and format it
objRange = objSheet.get_Range("A1", "A1");
objRange.set_Value(objOpt, this.title);
objRange.Font.Size = "14";
objRange.Font.Name = "Arial";
objRange.Font.Bold = true;
// Format header cells
objRange = objSheet.get_Range("A3", this.columnLetters[columncount] + "3");
objRange.set_Value(objOpt, objHeaders);
objRange.Font.Bold = true;
objRange.Font.Italic = true;
objRange.Font.Name = "Arial";
// Fill Cells with the listview data
int i = 4;
foreach (ListViewItem item in this.listview.Items)
{
for (int t = 0; t <= columncount; t++)
{
objRange = objSheet.get_Range(this.columnLetters[t] + i.ToString(), this.columnLetters[t] + i.ToString());
objRange.set_Value(objOpt, item.SubItems[t].Text);
objRange.HorizontalAlignment = Excel.Constants.xlLeft;
objRange.Font.Size = "8";
objRange.Font.Name = "Arial";
}
i++;
}
// Set Column widths
int x = 0;
foreach (ColumnHeader ch in this.listview.Columns)
{
objRange = objSheet.get_Range(this.columnLetters[x] + "1", this.columnLetters[x] + "1");
objRange.Columns.ColumnWidth = convertPixelToPoint(ch.Width);
x++;
}
// Set table borders
int j = i - 1;
objRange = objSheet.get_Range("A3", this.columnLetters[columncount] + j.ToString());
objRange.Borders.Value = XlBordersIndex.xlInsideHorizontal;
objRange.Borders.LineStyle = XlLineStyle.xlContinuous;
objRange.Borders.Weight = XlBorderWeight.xlThin;
// Set title border
objRange = objSheet.get_Range("A3", this.columnLetters[columncount] + "3");
objRange.BorderAround(Excel.XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, 001001001);
// Save the workbook and quit Excel.
objBook.SaveAs(path, objOpt, objOpt,
objOpt, objOpt, objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
objOpt, objOpt, objOpt, objOpt, objOpt);
objBook.Close(false, objOpt, objOpt);
objExcel.Quit();
MessageBox.Show("I bi fertig im Fall du");
}
catch (Exception ex)
{
MessageBox.Show("Es trat ein Fehler beim Speichern auf!nn" + ex.ToString(), "In Excel exportieren", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion
#region Private Methods
private double convertPixelToPoint(int Pixel)
{
return this.onePoint * Convert.ToDouble(Pixel + 35);
}
#endregion
}
}
Alte URL:
/snippet/listview-in-excel-sheet-exportieren/493
funktioniert leider nicht, wenn es mehr als 26 Spalten sind!
gruß
kleine Modifikation dann gehen auch mehr als 26 Spalten
object[] objHeaders = new object[26];
ändern in
object[] objHeaders = new object[this.listview.Columns.Count];
in der Methode GenerateDynamicExcelSheet() die „columnLetters“ erst bilden
columnLetters = new string[this.listview.Columns.Count];
int zaehler = -1;
int SpaltenCounter = 0;
for (int c = 0; c < this.listview.Columns.Count; c++) { string Spalte = ""; Spalte = Convert.ToChar(65 + SpaltenCounter).ToString(); SpaltenCounter++; if (zaehler >= 0)
{
Spalte = Convert.ToChar(65 + zaehler).ToString() + Spalte;
}
columnLetters[c] = Spalte;
if (SpaltenCounter == 26)
{
//wenn bei Z angekommen Zähler erhöhen
zaehler++;
SpaltenCounter = 0;
}
}
Die definition von columnLetters ist nicht korrekt, „F“ kommt zweimal vor dafür fehlt ein „H“