static public void GenerateXLS(string fileName, List<RoadMap> roadmaps, double conso, double gasCost)
{
Total total = new Total();
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.ApplicationName = "PlanMyWay";
doc.Creator = "SuperBen";
doc.Company = "PlanMyWay & Co.";
doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows[0].Cells[1].SetValue("Consommation moyenne du véhicule");
doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows[1].Cells[1].SetValue("Prix du carburant");
consoDefinition = doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows[0].Cells[3];
gasCostDefinition = doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows[1].Cells[3];
consoDefinition.SetValue(new Decimal(conso));
gasCostDefinition.SetValue(new Decimal(gasCost));
for (int i = 0; i< roadmaps.Count; i++)
{
if (i > 0)
{
total.Distance += "+";
total.Conso += "+";
total.CoutTotal += "+";
}
total += SpreadSheetRoadmapGenerator.AddTableXLS(ref doc, roadmaps[i]);
}
SpreadSheetRoadmapGenerator.AddTotalXLS(ref doc, total);
using (IsolatedStorageFile file = IsolatedStorageFile.GetUserStoreForApplication())
using (Stream writeFile = new IsolatedStorageFileStream(fileName, FileMode.Create, file))
using (IStreamProvider storage = new ZipStreamProvider(writeFile))
{
doc.Save(storage);
}
}
private void Create_Workers(ref SpreadsheetDocument doc, List<ArchyvedEmployeeClass> EC)
{
int y = 1;
for (int n = 0; n < EC.Count; n++)
{
ArchyvedEmployeeClass emp = EC[n];
doc.Workbook.Sheets[0].Sheet.Rows[y].Cells[0].SetValue(emp.NameLine);
doc.Workbook.Sheets[0].Sheet.Rows[y].Cells[1].SetValue(emp.Date);
if (System.Threading.Thread.CurrentThread.CurrentUICulture.ToString().Equals("lt-LT"))
{
doc.Workbook.Sheets[0].Sheet.Rows[y + 1].Cells[0].SetValue("Bazinis atlyginimas");
doc.Workbook.Sheets[0].Sheet.Rows[y + 2].Cells[0].SetValue("Kintama dalis pagal rodiklius");
doc.Workbook.Sheets[0].Sheet.Rows[y + 3].Cells[0].SetValue("Kintama dalis pagal užduotis");
doc.Workbook.Sheets[0].Sheet.Rows[y + 4].Cells[0].SetValue("Viso");
}
else if (System.Threading.Thread.CurrentThread.CurrentUICulture.ToString().Equals("ru-RU"))
{
doc.Workbook.Sheets[0].Sheet.Rows[y + 1].Cells[0].SetValue("Баз. Зарплата");
doc.Workbook.Sheets[0].Sheet.Rows[y + 2].Cells[0].SetValue("Перем. Часть-показатели");
doc.Workbook.Sheets[0].Sheet.Rows[y + 3].Cells[0].SetValue("Перем. Часть-задании");
doc.Workbook.Sheets[0].Sheet.Rows[y + 4].Cells[0].SetValue("Итого");
}
else
{
doc.Workbook.Sheets[0].Sheet.Rows[y + 1].Cells[0].SetValue("Basic salary");
doc.Workbook.Sheets[0].Sheet.Rows[y + 2].Cells[0].SetValue("Indicators related part");
doc.Workbook.Sheets[0].Sheet.Rows[y + 3].Cells[0].SetValue("Tasks related part");
doc.Workbook.Sheets[0].Sheet.Rows[y + 4].Cells[0].SetValue("Total");
}
doc.Workbook.Sheets[0].Sheet.Rows[y + 1].Cells[1].SetValue(emp.BALine.ToString());
doc.Workbook.Sheets[0].Sheet.Rows[y + 2].Cells[1].SetValue(emp.RODLine.ToString());
doc.Workbook.Sheets[0].Sheet.Rows[y + 3].Cells[1].SetValue(emp.UZDLine.ToString());
doc.Workbook.Sheets[0].Sheet.Rows[y + 4].Cells[1].SetValue(emp.VisoLine.ToString());
if (emp.RodList == null)
{
emp.RodList = new List<IndicatorsClass>();
}
if (emp.UzdList == null)
{
emp.UzdList = new List<TasksClass>();
}
for (int i = 0; i < emp.RodList.Count; i++)
{
doc.Workbook.Sheets[0].Sheet.Rows[y + i].Cells[2].SetValue(emp.RodList[i].INDPAVLine);
doc.Workbook.Sheets[0].Sheet.Rows[y + i].Cells[3].SetValue(emp.RodList[i].MKDLine.ToString());
doc.Workbook.Sheets[0].Sheet.Rows[y + i].Cells[4].SetValue(emp.RodList[i].BRLine.ToString());
doc.Workbook.Sheets[0].Sheet.Rows[y + i].Cells[5].SetValue(emp.RodList[i].TRLine.ToString());
doc.Workbook.Sheets[0].Sheet.Rows[y + i].Cells[6].SetValue(emp.RodList[i].FRLine.ToString());
}
doc.Workbook.Sheets[0].Sheet.Rows[y].Cells[8].SetValue(emp.MaxKDP.ToString());
for (int i = 0; i < emp.UzdList.Count; i++)
{
doc.Workbook.Sheets[0].Sheet.Rows[y + i].Cells[7].SetValue(emp.UzdList[i].UZDPAVLine);
doc.Workbook.Sheets[0].Sheet.Rows[y + i].Cells[9].SetValue(emp.UzdList[i].MaxIvert.ToString());
doc.Workbook.Sheets[0].Sheet.Rows[y + i].Cells[10].SetValue(emp.UzdList[i].Ivert.ToString());
}
int max = Math.Max(emp.RodList.Count, emp.UzdList.Count); max = Math.Max(max,5);
//TablePart table = doc.Workbook.Sheets[0].Sheet.AddTable(emp.NameLine + " " + emp.Date.Replace('.', '_'), emp.NameLine + " " + emp.Date.Replace('.', '_'), doc.Workbook.Sheets[0].Sheet.Rows[y].Cells[0], doc.Workbook.Sheets[0].Sheet.Rows[y + max - 2].Cells[9]);
y += max;
}
}
private void Create_Header(ref SpreadsheetDocument doc)
{
List<SharedStringDefinition> str = new List<SharedStringDefinition>() { };
if (System.Threading.Thread.CurrentThread.CurrentUICulture.ToString().Equals("lt-LT"))
{
str = new List<SharedStringDefinition>()
{
doc.Workbook.SharedStrings.AddString("Darbuotojas"), doc.Workbook.SharedStrings.AddString(""),
doc.Workbook.SharedStrings.AddString("Rodikliai"),
doc.Workbook.SharedStrings.AddString("Maksimali kintama\ndalis pagal\nrodiklius"),
doc.Workbook.SharedStrings.AddString("Bazinė reikšmė"),
doc.Workbook.SharedStrings.AddString("Tikslinė reikšmė"),
doc.Workbook.SharedStrings.AddString("Faktinė reikšmė"),
doc.Workbook.SharedStrings.AddString("Užduotys"),
doc.Workbook.SharedStrings.AddString("Maksimali kintama\ndalis pagal\nužduotis"),
doc.Workbook.SharedStrings.AddString("Maksimalus\nįvertinimas"),
doc.Workbook.SharedStrings.AddString("Įvertinimas")
};
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(0, 0, 23);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(1, 2, 10);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(3, 3, 15);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(4, 6, 13);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(7, 7, 10);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(8, 10, 15);
doc.Workbook.Sheets[0].Sheet.Rows[0].Height = 40;
}
else if (System.Threading.Thread.CurrentThread.CurrentUICulture.ToString().Equals("ru-RU"))
{
str = new List<SharedStringDefinition>()
{
doc.Workbook.SharedStrings.AddString("Сотрудники"), doc.Workbook.SharedStrings.AddString(""),
doc.Workbook.SharedStrings.AddString("Показатели"),
doc.Workbook.SharedStrings.AddString("Max. Часть\nотносительно\nпоказателей"),
doc.Workbook.SharedStrings.AddString("Базовое\nзначение"),
doc.Workbook.SharedStrings.AddString("Целевое\nзначение"),
doc.Workbook.SharedStrings.AddString("Фактическое\nзначение"),
doc.Workbook.SharedStrings.AddString("Задании"),
doc.Workbook.SharedStrings.AddString("Max. Часть в\nсоответствии\nс заданиями"),
doc.Workbook.SharedStrings.AddString("Mаксимальная\nоценка"),
doc.Workbook.SharedStrings.AddString("Оценка")
};
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(0, 0, 23);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(1, 2, 10);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(3, 3, 15);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(4, 6, 13);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(7, 7, 10);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(8, 10, 15);
doc.Workbook.Sheets[0].Sheet.Rows[0].Height = 40;
}
else
{
str = new List<SharedStringDefinition>()
{
doc.Workbook.SharedStrings.AddString("Employee"), doc.Workbook.SharedStrings.AddString(""),
doc.Workbook.SharedStrings.AddString("Indicators"),
doc.Workbook.SharedStrings.AddString("Max. Indicators\nrelated variable\npart"),
doc.Workbook.SharedStrings.AddString("Baseline value"),
doc.Workbook.SharedStrings.AddString("Target value"),
doc.Workbook.SharedStrings.AddString("Actual outcome"),
doc.Workbook.SharedStrings.AddString("Tasks"),
doc.Workbook.SharedStrings.AddString("Max. tasks\nrelated variable\npart"),
doc.Workbook.SharedStrings.AddString("Maximum\nevaluation"),
doc.Workbook.SharedStrings.AddString("Evaluation")
};
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(0, 0, 23);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(1, 2, 10);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(3, 3, 15);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(4, 6, 13);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(7, 7, 10);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(8, 10, 15);
doc.Workbook.Sheets[0].Sheet.Rows[0].Height = 40;
}
for (int i = 0; i < str.Count; i++) doc.Workbook.Sheets[0].Sheet.Rows[0].Cells[i].SetValue(str[i]);
}
private async void Share_Click(object sender, RoutedEventArgs e) // Share
{
bool ReadOnly = true;
for (int i = 0; i < Employees.Items.Count; i++)
{
EmployeeClass emp = (EmployeeClass)Employees.Items[i];
List<ArchyvedEmployeeClass> EC = new List<ArchyvedEmployeeClass>(){};
if (emp.IsChecked == true)
{
ReadOnly = false;
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.ApplicationName = "Goals_Results_Salary";
Create_Header(ref doc);
Read_Workers(ref EC, emp);
Create_Workers(ref doc, EC);
using (var isoStore = System.IO.IsolatedStorage.IsolatedStorageFile.GetUserStoreForApplication())
{
if (isoStore.FileExists("Goals_Results_Salary.xlsx")) isoStore.DeleteFile("Goals_Results_Salary.xlsx");
using (var s = isoStore.CreateFile("Goals_Results_Salary.xlsx"))
using (IStreamProvider storage = new ZipStreamProvider(s))
doc.Save(storage);
await Windows.System.Launcher.LaunchFileAsync(await ApplicationData.Current.LocalFolder.GetFileAsync("Goals_Results_Salary.xlsx"));
}
}
}
if (ReadOnly == true)
{
List<ArchyvedEmployeeClass> EC = new List<ArchyvedEmployeeClass>() { };
SpreadsheetDocument doc = new SpreadsheetDocument();
doc.ApplicationName = "Goals_Results_Salary";
Create_Header(ref doc);
Read_Workers(ref EC);
Create_Workers(ref doc, EC);
using (var isoStore = System.IO.IsolatedStorage.IsolatedStorageFile.GetUserStoreForApplication())
{
if (isoStore.FileExists("Goals_Results_Salary.xlsx")) isoStore.DeleteFile("Goals_Results_Salary.xlsx");
using (var s = isoStore.CreateFile("Goals_Results_Salary.xlsx"))
using (IStreamProvider storage = new ZipStreamProvider(s))
doc.Save(storage);
await Windows.System.Launcher.LaunchFileAsync(await ApplicationData.Current.LocalFolder.GetFileAsync("Goals_Results_Salary.xlsx"));
}
}
KillAll();
SaveAll();
SaveKalba(System.Threading.Thread.CurrentThread.CurrentCulture.ToString());
}
static private Total AddTableXLS(ref SpreadsheetDocument doc, RoadMap roadmap)
{
Total total = new Total();
int countRows = doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows.Indexes.Count();
doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows[countRows].Cells[0].SetValue(String.Empty);
countRows = doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows.Indexes.Count();
doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows[countRows].Cells[1].SetValue("Feuille de route du "+ roadmap.Date);
Debug.WriteLine("tableau " + roadmap.Meetings.FirstOrDefault().DateTime.ToShortDateString());
List<SharedStringDefinition> strList = new List<SharedStringDefinition>();
strList.Add(doc.Workbook.SharedStrings.AddString("Départ"));
strList.Add(doc.Workbook.SharedStrings.AddString("Arrivée"));
strList.Add(doc.Workbook.SharedStrings.AddString("Distance(km)"));
strList.Add(doc.Workbook.SharedStrings.AddString("Consomation(Litre)"));
strList.Add(doc.Workbook.SharedStrings.AddString("Coût(€)"));
countRows = doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows.Indexes.Count();
Debug.WriteLine("countRows 1 : "+countRows);
// Entêtes de colonne
for (var i=0; i<strList.Count; i++)
{
doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows[countRows].Cells[i + 1].SetValue(strList[i]);
}
// 1 ligne par trip
foreach (var trip in roadmap.Trips)
{
countRows = doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows.Indexes.Count();
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[1].SetValue(trip.Start.City);
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[2].SetValue(trip.End.City);
//Consommmation d'essence en fonction de la conso moyenne du véhicule
int distance = (int)Math.Round(trip.Distance, 0);
Cell distanceCell = doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[3];
Cell ConsumptionCell = doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[4];
distanceCell.SetValue(distance);
//double consumption = distance * conso / 100;
ConsumptionCell.Formula = distanceCell.CellName + "*" + consoDefinition.CellName + "/100";
//Prix du trajet en fonction de la consommation pendant le trajet et le prix du carburant
//double cost = consumption * gasCost;
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[5].Formula = ConsumptionCell.CellName + "*" + gasCostDefinition.CellName;
}
//Taille des colonnes
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(0, 1, 30);
doc.Workbook.Sheets[0].Sheet.AddColumnSizeDefinition(1, 5, 22);
//Mise à jour du prochain numéro de ligne libre
countRows = doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows.Indexes.Count();
Debug.WriteLine("countRows 3 : " + countRows);
//Récupération du numéro de ligne de début et de fin du tableau courrant
int idRowTableStart, idRowTableEnd;
idRowTableStart = countRows - roadmap.Trips.Count;
idRowTableEnd = countRows - 1;
Debug.WriteLine("idRowTableStart : " + idRowTableStart);
Debug.WriteLine("idRowTableEnd : " + idRowTableEnd);
Row rowTop = doc.Workbook.Sheets[0].Sheet.Rows[idRowTableStart];
Row rowBottom = doc.Workbook.Sheets[0].Sheet.Rows[idRowTableEnd];
//// Total
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[1].SetValue("Total");
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[3].Formula = "SUM(" + rowTop.Cells[3].CellName + ":" + rowBottom.Cells[3].CellName + ")";
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[4].Formula = "SUM(" + rowTop.Cells[4].CellName + ":" + rowBottom.Cells[4].CellName + ")";
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[5].Formula = "SUM(" + rowTop.Cells[5].CellName + ":" + rowBottom.Cells[5].CellName + ")";
//Incrémentation du total
total.Distance += "SUM(" + rowTop.Cells[3].CellName + ":" + rowBottom.Cells[3].CellName + ")";
total.Conso += "SUM(" + rowTop.Cells[4].CellName + ":" + rowBottom.Cells[4].CellName + ")";
total.CoutTotal += "SUM(" + rowTop.Cells[5].CellName + ":" + rowBottom.Cells[5].CellName + ")";
Debug.WriteLine(roadmap.Meetings.FirstOrDefault().DateTime.ToShortDateString());
//Ajout d'un tableau
Row rowCol = doc.Workbook.Sheets[0].Sheet.Rows[idRowTableStart - 1];
Row rowTotal = doc.Workbook.Sheets[0].Sheet.Rows[idRowTableEnd + 1];
Debug.WriteLine("rowCol : " + (idRowTableStart - 1));
Debug.WriteLine("rowTotal : " + (idRowTableEnd + 1));
TablePart table = doc.Workbook.Sheets[0].Sheet.AddTable(
"tableau 1",
"rapport du " + roadmap.Meetings.FirstOrDefault().DateTime.ToShortDateString(),
rowCol.Cells[1],
rowTotal.Cells[5]
);
//Précision explicite des colonnes du tableau
for (var i = 0; i < strList.Count; i++)
{
table.TableColumns[i].Name = strList[i].String;
}
table.ShowTotalsRow = true;
table.TableStyle.ShowColumnStripes = true;
table.TableStyle.ShowRowStripes = true;
return total;
}
static private void AddTotalXLS(ref SpreadsheetDocument doc, Total total)
{
int countRows = doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows.Indexes.Count();
doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows[countRows].Cells[0].SetValue(String.Empty);
List<SharedStringDefinition> strList = new List<SharedStringDefinition>();
strList.Add(doc.Workbook.SharedStrings.AddString("Distance totale(km)"));
strList.Add(doc.Workbook.SharedStrings.AddString("Consomation totale(Litre)"));
strList.Add(doc.Workbook.SharedStrings.AddString("Coût total(€)"));
countRows = doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows.Indexes.Count();
// Entêtes de colonne
for (var i = 0; i < strList.Count; i++)
{
doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows[countRows].Cells[i + 3].SetValue(strList[i]);
}
countRows = doc.Workbook.Sheets.FirstOrDefault().Sheet.Rows.Indexes.Count();
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[2].SetValue("Récapitulatif");
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[3].Formula = total.Distance;
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[4].Formula = total.Conso;
doc.Workbook.Sheets[0].Sheet.Rows[countRows].Cells[5].Formula = total.CoutTotal;
}
