I am currently making an excel validation program.
I want to validate the selected KPI values under a specific month. Say, the selected KPI column box is "SALES VOLUME" and the checkbox that is checked is "JANUARY", only SALES VOLUME KPI under JANUARY shall be only validated.
Result example should be like this :
A textfile would pop out showing this values on the selected KPI Combo Box and checkbox of the month.
KPI: Sales Volume
Category: Macau & Shipstore
Month: January
Value: 1283091823
Only the KPI SALES VOLUME from the month January shall be validated.
For reference, here's an image of the UI.
My codes are as follows:
From ExcelMethods Class:
This method validates each month depending on the checkbox checked.
public void Validate_Month(DataGridView dataGridView, int month, int select)
{
int kpi = 2;
int category = 3;
decimal num;
FileStream fs = new FileStream(@"C:\brandon\InvalidColumnsByMonth.txt", FileMode.OpenOrCreate, FileAccess.Write);
StreamWriter sw = new StreamWriter(fs);
sw.BaseStream.Seek(0, SeekOrigin.End);
StringBuilder sb = new StringBuilder();
if (dataGridView.ColumnCount > 3)
{
for (int h = select; h <= month; h++)
{
for (int i = 0; i < dataGridView.RowCount; i++)
{
if (!Decimal.TryParse(dataGridView[h, i].Value.ToString(), out num))
{
if (dataGridView[h, i].Value.ToString() == null || dataGridView[h, i].Value.ToString() == "")
{
}
else
{
sb.AppendLine("[KPI]: " + dataGridView.Rows[i].Cells[kpi].Value.ToString());
sb.AppendLine("[Category]: " + dataGridView.Rows[i].Cells[category].Value.ToString());
sb.AppendLine("[Month]:" + dataGridView.Columns[h].Name.ToUpper());
sb.AppendLine("[VALUE]: " + dataGridView[h, i].Value.ToString() + "");
sb.AppendLine("");
sw.WriteLine("[KPI]: " + dataGridView.Rows[i].Cells[kpi].Value.ToString());
sw.WriteLine("[Category]: " + dataGridView.Rows[i].Cells[category].Value.ToString());
sw.WriteLine("[Month]:" + dataGridView.Columns[h].Name.ToUpper());
sw.WriteLine("[VALUE]: {" + dataGridView[h, i].Value.ToString() + "}");
sw.WriteLine("");
}
}
}
}
if (sb.Length != 0 )
{
MessageBox.Show(sb.ToString());
Process.Start(@"C:\brandon\InvalidColumnsByMonth.txt");
}
else
{
int h = select;
MessageBox.Show("No errors in month of " + dataGridView.Columns[h].Name + ".");
}
sw.Flush();
sw.Close();
}
}
From my Form 1 Class
This is for reference ExcelMethods method, Validate_Month
public void Validate(CheckBox cb, DataGridView dataGridView1, String month, int i)
{
if (cb.Checked == true && dataGridView1.Columns.Contains(month) )
{
ExcelMethods.Validate_Month(dataGridView1, 4 + i, 4 + i);
}
}
and lastly, from Form 1 Class is the btnValidateAll_MouseClick method
private void btnValidate_MouseClick(object sender, MouseEventArgs e)
{
Validate(checkBox1, dataGridView1, "January", 0);
Validate(checkBox2, dataGridView1, "February", 1);
Validate(checkBox3, dataGridView1, "March", 2);
Validate(checkBox4, dataGridView1, "April", 3);
Validate(checkBox5, dataGridView1, "May", 4);
Validate(checkBox6, dataGridView1, "June", 5);
Validate(checkBox7, dataGridView1, "July", 6);
Validate(checkBox8, dataGridView1, "August", 7);
Validate(checkBox9, dataGridView1, "September", 8);
Validate(checkBox10, dataGridView1, "October", 9);
Validate(checkBox11, dataGridView1, "November", 10);
Validate(checkBox12, dataGridView1, "December", 11);
}
Aucun commentaire:
Enregistrer un commentaire