Feedback

C# - Listview in Excel Sheet exportieren

Veröffentlicht von am 3/20/2007
(1 Bewertungen)
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!\n\n" + 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
    }
}

Abgelegt unter Office, Excel, Listview, Export.

3 Kommentare zum Snippet

TBBsolutions schrieb am 6/15/2009:
funktioniert leider nicht, wenn es mehr als 26 Spalten sind!
gruß
TBBsolutions schrieb am 6/16/2009:
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;
}
}
Alexander Wölkart schrieb am 7/9/2009:
Die definition von columnLetters ist nicht korrekt, "F" kommt zweimal vor dafür fehlt ein "H"
 

Logge dich ein, um hier zu kommentieren!