private void button4_Click(object sender, EventArgs e) { openFileDialog1.ShowDialog(); String fileName = openFileDialog1.FileName; ExcelUtil exc = new ExcelUtil(); exc.Open(fileName); //exc.AddWorkbook(); exc.Worksheet("Sheet1"); Object[,] obj = exc.ReadCellByRange("A1:Y806"); Console.WriteLine(obj.GetUpperBound(0)); Console.WriteLine(obj.GetUpperBound(1)); NumberToLetterUtil numUtil = new NumberToLetterUtil(); for (int x = 1; x < obj.GetUpperBound(0); x++) { for (int y = 1; y < obj.GetUpperBound(1); y++) { Console.WriteLine(numUtil.getLetterByNumber(y) + x + " = " + obj[x, y]); } } //for (int cntr = 5; cntr < 500; cntr++) { // Console.WriteLine(exc.ReadCell("A" + cntr.ToString())); //} }
private void button5_Click(object sender, EventArgs e) { Demand demand = (Demand)propertyGrid.SelectedObject; NumberToLetterUtil converter = new NumberToLetterUtil(); NumberToLetterUtil numUtil = new NumberToLetterUtil(); if (demand.RowSequenceType == RowSequenceType.Range) { try { ExcelUtil excel = new ExcelUtil(); excel.Open(demand.Path + "/" + demand.FileName); excel.Worksheet(demand.WorkSheet); Object[,] obj = excel.ReadCellByRange(demand.RowRangeFrom + ":" + demand.RowRangeTo); for (int row = 1; row <= obj.GetUpperBound(0); row++) { for (int column = 1; column <= obj.GetUpperBound(1); column++) { Console.WriteLine(numUtil.getLetterByNumber(column) + "[" + numUtil.getNumberByLetter(numUtil.getLetterByNumber(column)) + "]" + row + " = " + obj[row, column]); } } excel.Close(); } catch (WorksheetNotFoundException wnfe) { MessageBox.Show(null, wnfe.Message, "Error Window", MessageBoxButtons.OK, MessageBoxIcon.Error); } catch (RangeInvalidException rie) { MessageBox.Show(null, rie.Message, "Error Window", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else if (demand.RowSequenceType == RowSequenceType.Collection) { try { readDDPDemandByCollection(demand); } catch (NullReferenceException nre) { MessageBox.Show(null, nre.Message.ToString(), "Error Window", MessageBoxButtons.OK, MessageBoxIcon.Error); } } Console.WriteLine("Success!"); Console.WriteLine(demand.Region); }
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!"); }