private void button8_Click(object sender, EventArgs e) { excel = new ExcelUtil(); excel.AddWorkbook(); excel.Worksheet("Sheet1"); List <String> lstData = new List <String>(); StreamWriter writer = new StreamWriter("testCSV.csv", false); List <String> luzonPSASummary = new List <String>(); List <String> luzonDemandSummary = new List <String>(); List <String> mindanaoPSASummary = new List <String>(); List <String> mindanaoDemandSummary = new List <String>(); List <String> visayasPSASummary = new List <String>(); List <String> visayasDemandSummary = new List <String>(); //testing for changes row = 0; int areaCntr = 0; IList <Area> areas = areaSessionData.getAllAreas(); foreach (Area area in areas) { areaCntr++; foreach (Regions region in regionSessionData.getAllRegionsByArea(area)) { List <Cooperative> coops = cooperativeSessionData.getCooperativeByRegion(region.regionId); int coopCntr = 0; foreach (Cooperative coop in coops) { coopCntr++; row++; writer.WriteLine(); row++; writer.WriteLine(area.name + "," + region.name + "," + coop.name); IList <Plant> plants = plantSessionData.getAllPlantByCoop(coop.cooperativeId); //row++; //writer.WriteLine(area.name + "," + region.name + ",PSA,=SUM(D" + (row + 1) + ":D" + (row + plants.Count) + ")"); int PSARow = row; //row++; int col; //Console.WriteLine(plants.Count.ToString()); Console.WriteLine("Coop: " + coop.name + " " + ((double)coopCntr / coops.Count)); txtOutput.AppendText(areaCntr + " out of" + areas.Count + ": " + (((double)coopCntr / coops.Count) * 100) + "%\n"); loadingBar.Value = (int)(((double)coopCntr / coops.Count) * 100); int colCntr; int psaRowMarker = 0; List <String> data1; List <String> data2; bool psaRegistered = false; foreach (Plant plant in plants) { if (plant != null) { col = 3; colCntr = 1; // data1 = new List <String>(); data2 = new List <String>(); String psaOutput = ""; foreach (DataValues dataValue in plant.dataValues) { col++; List <string> tempData = null; if (colCntr == 1) { data1 = dataContentSessionData.findDataContentValueByDataValuesId(dataValue); tempData = data1; } else if (colCntr == 2) { data2 = dataContentSessionData.findDataContentValueByDataValuesId(dataValue); tempData = data2; } if (!psaRegistered) { int column = 4; foreach (String str in tempData) { psaOutput += "=SUM(" + numUtil.getLetterByNumber(column) + ("row") + ":" + numUtil.getLetterByNumber(column) + ("rows") + "),"; column++; } } colCntr++; col = 4; } if (!psaRegistered) { row++; psaRowMarker = row; if (area.name.Equals("Luzon")) { luzonPSASummary.Add(row.ToString()); } else if (area.name.Equals("Mindanao")) { mindanaoPSASummary.Add(row.ToString()); } else if (area.name.Equals("Visayas")) { visayasPSASummary.Add(row.ToString()); } psaOutput.Remove(psaOutput.LastIndexOf(',')); writer.WriteLine(area.name + "," + region.name + ",PSA," + psaOutput.Replace("rows", (row + plants.Count).ToString()).Replace("row", (row + 1).ToString())); } psaRegistered = true; List <String> output = merge(data1, data2); output.Insert(0, area.name.Replace(",", "")); output.Insert(1, region.name); output.Insert(2, plant.name.Replace(",", "")); Console.WriteLine(String.Join(",", output)); row++; writer.WriteLine(String.Join(",", output)); } } Console.WriteLine("-----------"); col = 3; colCntr = 1; data1 = new List <String>(); data2 = new List <String>(); foreach (CooperativeDataValues dataValue in coop.cooperativeDataValues) { List <string> tempData = null; col++; if (colCntr == 1) { data1 = cooperativeDataContentSessionData.findDataContentValueByDataValuesId(dataValue); tempData = data1; } else if (colCntr == 2) { data2 = cooperativeDataContentSessionData.findDataContentValueByDataValuesId(dataValue); tempData = data2; } colCntr++; } List <String> output2 = merge(data1, data2); int resColumn = 4; row++; String reserveDef = ""; foreach (String str in output2) { reserveDef += "=" + (psaRowMarker == 0 ? "0" : numUtil.getLetterByNumber(resColumn) + (psaRowMarker)) + "-" + numUtil.getLetterByNumber(resColumn) + (row) + ","; resColumn++; } output2.Insert(0, area.name.Replace(",", "")); output2.Insert(1, region.name); output2.Insert(2, "DEMAND"); if (area.name.Equals("Luzon")) { luzonDemandSummary.Add(row.ToString()); } else if (area.name.Equals("Mindanao")) { mindanaoDemandSummary.Add(row.ToString()); } else if (area.name.Equals("Visayas")) { visayasDemandSummary.Add(row.ToString()); } row++; writer.WriteLine(String.Join(",", output2)); if (!reserveDef.Equals("")) { reserveDef.Remove(reserveDef.LastIndexOf(',')); } writer.WriteLine(area.name + "," + region.name + ",RESERVE/DEFICIT," + reserveDef); } } } writer.Close(); //ExcelUtil excel = new ExcelUtil(); row++; row++; excel.Open(Application.StartupPath.ToString() + "\\testCSV.csv"); excel.Worksheet("testCSV"); excel.WriteCell(2, 1, "Area"); excel.WriteCell(2, 2, "Region"); for (int index = 0; index < luzonPSASummary.Count; index++) { excel.setBackgroundColor("A" + luzonPSASummary[index].ToString() + ":AG" + luzonPSASummary[index].ToString(), Color.Aqua); } for (int index = 0; index < luzonDemandSummary.Count; index++) { excel.setBackgroundColor("A" + luzonDemandSummary[index].ToString() + ":AG" + luzonDemandSummary[index].ToString(), Color.Wheat); excel.setBackgroundColor("A" + (Int32.Parse(luzonDemandSummary[index].ToString()) + 1) + ":AG" + (Int32.Parse(luzonDemandSummary[index].ToString()) + 1), Color.Gainsboro); } for (int index = 0; index < mindanaoPSASummary.Count; index++) { excel.setBackgroundColor("A" + mindanaoPSASummary[index].ToString() + ":AG" + mindanaoPSASummary[index].ToString(), Color.Aqua); } for (int index = 0; index < mindanaoDemandSummary.Count; index++) { excel.setBackgroundColor("A" + mindanaoDemandSummary[index].ToString() + ":AG" + mindanaoDemandSummary[index].ToString(), Color.Wheat); excel.setBackgroundColor("A" + (Int32.Parse(mindanaoDemandSummary[index].ToString()) + 1) + ":AG" + (Int32.Parse(mindanaoDemandSummary[index].ToString()) + 1), Color.Gainsboro); } for (int index = 0; index < visayasPSASummary.Count; index++) { excel.setBackgroundColor("A" + visayasPSASummary[index].ToString() + ":AG" + visayasPSASummary[index].ToString(), Color.Aqua); } for (int index = 0; index < visayasPSASummary.Count; index++) { excel.setBackgroundColor("A" + visayasPSASummary[index].ToString() + ":AG" + visayasPSASummary[index].ToString(), Color.Wheat); excel.setBackgroundColor("A" + (Int32.Parse(visayasPSASummary[index].ToString()) + 1) + ":AG" + (Int32.Parse(visayasPSASummary[index].ToString()) + 1), Color.Gainsboro); } int colHeader = 4; int colDataHeader = 2008; for (int yr = 2008; yr <= 2022 + (2022 - 2008); yr++) { if (yr % 2 == 0) { excel.WriteCell(1, colHeader, colDataHeader.ToString()); excel.Merge(numUtil.getLetterByNumber(colHeader) + "1", numUtil.getLetterByNumber(colHeader + 1) + "1"); excel.WriteCell(2, colHeader, "PSA DEMAND"); excel.WriteCell(2, colHeader + 1, "E. SALES"); colDataHeader++; } colHeader++; } excel.filter("A1", "B" + row); excel.WriteCell(row, 2, "Luzon"); excel.WriteCell(row, 3, "PSA"); excel.WriteCell(row, 4, Summary(luzonPSASummary).Replace(",", ",D")); excel.setBackgroundColor("D" + row, Color.Gainsboro); excel.copy("D" + row, "E" + row + ":AG" + row); row++; excel.WriteCell(row, 2, "Luzon"); excel.WriteCell(row, 3, "DEMAND"); excel.WriteCell(row, 4, Summary(luzonDemandSummary).Replace(",", ",D")); excel.setBackgroundColor("D" + row, Color.LightCyan); excel.copy("D" + row, "E" + row + ":AG" + row); row++; excel.WriteCell(row, 2, "Luzon"); excel.WriteCell(row, 3, "RESERVE/DEFICIT"); excel.WriteCell(row, 4, "=D" + (row - 2) + "-D" + (row - 1)); excel.setBackgroundColor("D" + row, Color.LightCyan); excel.copy("D" + row, "E" + row + ":AG" + row); row++; row++; excel.WriteCell(row, 2, "Visayas"); excel.WriteCell(row, 3, "PSA"); excel.WriteCell(row, 4, Summary(visayasPSASummary).Replace(",", ",D")); excel.setBackgroundColor("D" + row, Color.Gainsboro); excel.copy("D" + row, "E" + row + ":AG" + row); row++; excel.WriteCell(row, 2, "Visayas"); excel.WriteCell(row, 3, "DEMAND"); excel.WriteCell(row, 4, Summary(visayasDemandSummary).Replace(",", ",D")); excel.setBackgroundColor("D" + row, Color.LightCyan); excel.copy("D" + row, "E" + row + ":AG" + row); row++; excel.WriteCell(row, 2, "Visayas"); excel.WriteCell(row, 3, "RESERVE/DEFICIT"); excel.WriteCell(row, 4, "=D" + (row - 2) + "-D" + (row - 1)); excel.setBackgroundColor("D" + row, Color.LightCyan); excel.copy("D" + row, "E" + row + ":AG" + row); row++; row++; excel.WriteCell(row, 2, "Mindanao"); excel.WriteCell(row, 3, "PSA"); excel.WriteCell(row, 4, Summary(mindanaoPSASummary).Replace(",", ",D")); excel.setBackgroundColor("D" + row, Color.Gainsboro); excel.copy("D" + row, "E" + row + ":AG" + row); row++; excel.WriteCell(row, 2, "Mindanao"); excel.WriteCell(row, 3, "DEMAND"); excel.WriteCell(row, 4, Summary(mindanaoDemandSummary).Replace(",", ",D")); excel.setBackgroundColor("D" + row, Color.LightCyan); excel.copy("D" + row, "E" + row + ":AG" + row); row++; excel.WriteCell(row, 2, "Mindanao"); excel.WriteCell(row, 3, "RESERVE/DEFICIT"); excel.WriteCell(row, 4, "=D" + (row - 2) + "-D" + (row - 1)); excel.setBackgroundColor("D" + row, Color.LightCyan); excel.Format("A3:AG" + row, "#,##0.00"); excel.copy("D" + row, "E" + row + ":AG" + row); excel.Save("newExcel.xls"); GC.Collect(); Console.WriteLine("Save!"); }