Listview in Excel Sheet exportieren

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150

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
    }
}