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 button7_Click(object sender, EventArgs e)
{
Demand demand = (Demand)propertyGrid.SelectedObject;
NumberToLetterUtil converter = new NumberToLetterUtil();
demand.PscEcsData.Clear();
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2008", Column = "J"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2009", Column = "K"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2010", Column = "L"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2011", Column = "M"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2012", Column = "N"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2013", Column = "O"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2014", Column = "P"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2015", Column = "Q"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2016", Column = "R"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2017", Column = "S"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2018", Column = "T"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2019", Column = "U"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2020", Column = "V"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2021", Column = "W"
});
demand.PscEcsData.Add(new PscEcsData()
{
Name = "2022", Column = "X"
});
NumberToLetterUtil numUtil = new NumberToLetterUtil();
if (demand.RowSequenceType == RowSequenceType.Range)
{
try
{
String firstLetter = demand.RowRangeFrom.ElementAt(0).ToString();
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 col = 1; col <= obj.GetUpperBound(1); col++) {
// if (obj[row, col] == null) {
// Console.WriteLine("NUl");
// continue;
// }
// Console.WriteLine(obj[row, col].ToString());
//}
int numberGap = Int32.Parse(numUtil.getNumberByLetter(firstLetter));
//Area area = new Area(obj[row, Int32.Parse(numUtil.getNumberByLetter(demand.AreaColumn)) - numberGap + 1].ToString());
Area area = new Area("Luzon");
area = (Area)areaSessionData.createIfNotExist(area);
Regions region = new Regions(obj[row, Int32.Parse(numUtil.getNumberByLetter(demand.RegionColumn)) - numberGap + 1].ToString(), area.areaId);
region = (Regions)regionSessionData.createIfNotExist(region);
Cooperative cooperative = new Cooperative(obj[row, Int32.Parse(numUtil.getNumberByLetter(demand.CooperativeColumn)) - numberGap + 1].ToString(), "", region.regionId);
cooperative = (Cooperative)cooperativeSessionData.createIfNotExist(cooperative);
if (obj[row, Int32.Parse(numUtil.getNumberByLetter(demand.PlantColumn)) - numberGap + 1] == null)
{
continue;
}
Plant plant = new Plant(obj[row, Int32.Parse(numUtil.getNumberByLetter(demand.PlantColumn)) - numberGap + 1].ToString(), cooperative.cooperativeId);
plant = (Plant)plantSessionData.createIfNotExist(plant);
//plant = (Plant)plantSessionData.add(plant);
//if (plant == null) continue;
Console.WriteLine("Plant: " + plant.plantId);
DataType dataType = new DataType(obj[row, Int32.Parse(numUtil.getNumberByLetter(demand.PscEcsKeywordColumn)) - numberGap + 1].ToString());
dataType = (DataType)dataTypeSessionData.createIfNotExist(dataType);
DataValues dataValue = new DataValues(plant.plantId, dataType.dataTypeId);
dataValue = (DataValues)dataValuesSessionData.createIfNotExist(dataValue);
foreach (PscEcsData ecsData in demand.PscEcsData)
{
object dataObect = obj[row, Int32.Parse(numUtil.getNumberByLetter(ecsData.Column)) - numberGap + 1];
String data = "0";
if (dataObect != null)
{
data = dataObect.ToString();
}
DataContent dataContent = new DataContent(ecsData.Name, data, dataValue.dataValuesId);
dataContentSessionData.add(dataContent);
}
}
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);
}
catch (IndexOutOfRangeException iore) {
MessageBox.Show(null, "Index not in a range.", "Error Window", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else if (demand.RowSequenceType == RowSequenceType.Collection)
{
try
{
excel = new ExcelUtil();
excel.AddWorkbook();
excel.Worksheet("Sheet1");
String cell = null;
String path = null;
foreach (String row in demand.RowCollection)
{
cell = demand.PlantColumnIndex + row;
path = @"='" + demand.Path + "\\[" + demand.FileName + "]" + demand.WorkSheet + "'!" + cell;
excel.WriteCell(1, 1, path);
String plantName = excel.ReadCell("A1").ToString();
Console.WriteLine(plantName);
int rowIndex = Int32.Parse(row) + 2;
for (int cntr = rowIndex; cntr < (rowIndex + 2); cntr++)
{
cell = demand.SupplyContractedKeywordColumn + cntr;
path = @"='" + demand.Path + "\\[" + demand.FileName + "]" + demand.WorkSheet + "'!" + cell;
excel.WriteCell(1, 1, path);
String data = excel.ReadCell("A1").ToString();
dataType = new DataType(data);
dataType = (DataType)dataTypeSessionData.createIfNotExist(dataType);
Console.WriteLine("data: " + dataType.dataTypeId);
Area area = new Area("Luzon");
area = (Area)areaSessionData.createIfNotExist(area);
Regions region = new Regions(demand.SupplyContractedRegion.ToString().Replace("_", " "), area.areaId);
region = (Regions)regionSessionData.createIfNotExist(region);
Cooperative cooperative = new Cooperative(demand.SupplyContractedCooperative, demand.SupplyContractedCooperativeAccronym, region.regionId);
cooperative = (Cooperative)cooperativeSessionData.createIfNotExist(cooperative);
Plant plant = new Plant(plantName, cooperative.cooperativeId);
plant = (Plant)plantSessionData.createIfNotExist(plant);
Console.WriteLine("Plant: " + plant.plantId);
//DataValues coopSearch = data
DataValues coopSearch = dataValuesSessionData.findDataValuesByPlantId(plant, dataType);
if (coopSearch != null)
{
dataContentSessionData.deleteByDataValueId(coopSearch.dataValuesId);
dataValue = coopSearch;
}
else
{
dataValue = new DataValues();
dataValue.plantId = plant.plantId;
dataValue.dataTypeId = dataType.dataTypeId;
dataValue = (DataValues)dataValuesSessionData.add(dataValue);
}
foreach (DDPSupplyContractedData contractedData in demand.SupplyContractedData)
{
cell = contractedData.Column + cntr;
path = @"='" + demand.Path + "\\[" + demand.FileName + "]" + demand.WorkSheet + "'!" + cell;
excel.WriteCell(1, 1, path);
Console.WriteLine(contractedData.Name + " - " + excel.ReadCell(1, "A"));
dataContent = new DataContent();
dataContent.header = contractedData.Name;
dataContent.value = excel.ReadCell("A1");
dataContent.dataValuesId = dataValue.dataValuesId;
dataContentSessionData.add(dataContent);
}
}
}
excel.Close();
}
catch (NullReferenceException nre)
{
MessageBox.Show(null, nre.Message.ToString(), "Error Window", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Console.WriteLine("Success!");
Console.WriteLine(demand.Region);
}
public Group(int index, IEnumerable <Movie> movies)
{
Name = NumberToLetterUtil.GetName(index);
Movies = movies.OrderByDescending(m => m.AverageRating).ThenBy(m => m.PrimaryTitle).ToList();
}
