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