//importiert die Mitarbeiter aus der mitarbeiter.xml
private void btnXmlGet_Click(object sender, EventArgs e)
{
try
{
XmlDocument reader = new XmlDocument();
reader.Load("mitarbeiter.xml");
int i = 1;
Form2 newMaDatenForm;
foreach (XmlNode node in reader.DocumentElement.ChildNodes[0].ChildNodes)
{
Mitarbeiter ma = new Mitarbeiter(listMA.Last().ma_id + i++, node.Attributes["name"].Value, node.Attributes["vorname"].Value);
newMaDatenForm = new Form2(ma);
newMaDatenForm.ShowDialog();
db.saveNewMA(ma.ma_id.ToString(), ma.nachname, ma.vorname, ma.geburtsdatum, ma.tagesArbeitszeit.ToString(), ma.urlaubsAnspruch.ToString());
}
updateMA();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void ExcelEintragen(Mitarbeiter ma)
{
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
try
{
var fn = @"C:\Users\tn.DESKTOP-91L17BK\Documents\Visual Studio 2015\Projects\PersonalVerwaltung\PersonalVerwaltung\bin\Debug\auswertung_fehlzeiten2.xlsx";
excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Visible = true;
// Arbeitsmappe öffnen
workbook = excel.Workbooks.Open(fn);
// Arbeitsblatt referenzieren
// Tabelle öffnen
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Worksheets[1];
worksheet.Name = $"{ma.vorname} {ma.nachname}";
/*
* // Hintergrundfarbe, Linienart, -staerke, -farbe, Format
* aRange.Cells.Borders.LineStyle = DataGridLineStyle.Solid;
* bRange.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(col);
* bRange.Borders.Weight = 2;
* bRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
* bRange.Borders.Color = Color.Black.ToArgb();
* String[] cellFormat = { "#####", "dd/mm/yyyy", "@", "#######0.00", "@", "@", "#######0.00 €" };
* bRange.NumberFormat = cellFormat[2];
*/
// Ueberschrift: Mitarbeitername
worksheet.Cells.ClearContents();
Microsoft.Office.Interop.Excel.Range bRange = worksheet.get_Range("B2:B2");
bRange.Value = $"{ma.vorname} {ma.nachname}";
//bRange.Font.Size = 15;
// Zelleneinstellungen
//bRange.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.Red);
//bRange.Borders.Weight = 3;
//bRange.Cells.Borders.LineStyle = DataGridLineStyle.Solid;
//bRange.Borders.Color = Color.Black.ToArgb();
// Tabellenkopf
bRange = worksheet.get_Range("B4:B4");
bRange.Value = "Datum von";
bRange = worksheet.get_Range("C4:C4");
bRange.Value = "Datum bis";
bRange = worksheet.get_Range("D4:D4");
bRange.Value = "Fehltage";
bRange = worksheet.get_Range("E4:E4");
bRange.Value = "Fehlgrund";
// Eintraege
int i = 5;
char c = 'B';
Dictionary <string, int> sum = new Dictionary <string, int>();
//Hashtable sum = new Hashtable();
foreach (var x in listFG)
{
sum.Add(x.fehlgrund, 0);
}
sum.Add("gesamt", 0);
//int[] sum = new int[listFG.Count()];
foreach (var fz in listFZ)
{
if (ma.ma_id == fz.ma_id)
{
bRange = worksheet.get_Range(incrementRange(c++, i));
bRange.Value = fz.fz_von.ToShortDateString();
bRange = worksheet.get_Range(incrementRange(c++, i));
bRange.Value = fz.fz_bis.ToShortDateString();
bRange = worksheet.get_Range(incrementRange(c++, i));
bRange.Value = fz.fehltage.ToString();
bRange = worksheet.get_Range(incrementRange(c++, i));
bRange.Value = listFG[listFG.FindIndex(fg => fg.fg_id == fz.fehlgrund)].fehlgrund;
// todo
sum[bRange.Value] += fz.fehltage;
sum["gesamt"] += fz.fehltage;
c = 'B';
i++;
}
}
i += 2;
bRange = worksheet.get_Range(incrementRange('C', i));
bRange.Value = "Summe:";
bRange = worksheet.get_Range(incrementRange('D', i));
bRange.Value = sum["gesamt"];
sum.Remove("gesamt");
i += 2;
foreach (var x in sum)
{
bRange = worksheet.get_Range(incrementRange('C', i));
bRange.Value = x.Key;
bRange = worksheet.get_Range(incrementRange('D', i));
bRange.Value = x.Value;
i++;
}
bRange = worksheet.get_Range(incrementRange('C', ++i));
bRange.Value = "Resturlaub";
bRange = worksheet.get_Range(incrementRange('D', i));
bRange.Value = ma.urlaubsAnspruch - sum["Urlaub"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// Arbeitsmappe speichern
workbook.Save();
// Excel beenden
excel.Quit();
}
}
public Form2(Mitarbeiter ma)
{
InitializeComponent();
this.ma = ma;
labelMA_name.Text = ma.vorname + " " + ma.nachname;
}