/// <summary>
/// Writes design review totals
/// </summary>
/// <param name="sheet">The sheet to write to</param>
/// <param name="design_review_type">the design review type</param>
/// <param name="col_start">the column to start on</param>
/// <param name="total_foreach_headers">the list of headers</param>
/// <param name="feheaders">the data array for headers</param>
/// <param name="vert_business_seg">the array of business segments</param>
/// <returns></returns>
private int WriteDesignTotals(Excel.Worksheet sheet, string design_review_type, int col_start, List <string> total_foreach_headers, object[,] feheaders, object[,] vert_business_seg)
{
int NUM_TOTAL_PAGE_HEADERS = 0;
// fill in headers
feheaders[0, col_start - 2] = Properties.Resources.string_design_review_type;
int feheader_index = col_start - 1;
for (int i = 0; i < total_foreach_headers.Count; i++)
{
SortedSet <string> set = dict_total[total_foreach_headers[i]];
foreach (string h in set)
{
feheaders[0, feheader_index++] = total_foreach_headers[i] + " " + h;
NUM_TOTAL_PAGE_HEADERS++;
}
}
//write headers to sheet
string range_text = Utility.GetRowRange(1, col_start, col_start + NUM_TOTAL_PAGE_HEADERS);
//Utility.Log("Total Range:", range_text);
Excel.Range rheader = sheet.get_Range(range_text);
rheader.Value2 = feheaders;
rheader.WrapText = false;
int row_start = 2;
//Go through all business segments
for (int i = 0; i < vert_business_seg.GetLength(0); i++)
{
//get business segment string
string business_seg = vert_business_seg[i, 0] as string;
//create array to store data
object[,] oadata = new object[1, NUM_TOTAL_PAGE_HEADERS + 1];
//set column design review type
oadata[0, 0] = design_review_type;
int col_index = 1;
// calculate totals for each header key
for (int j = 0; j < total_foreach_headers.Count; j++)
{
string key = total_foreach_headers[j];
SortedSet <string> set = dict_total[total_foreach_headers[j]];
// for each header value calculate totals
foreach (string value in set)
{
BusinessSegment bs = dictionary_business_segments[business_seg];
oadata[0, col_index++] = bs.CalculateTotal(design_review_type, key, value);
}
}
// set data
Excel.Range r_total = sheet.get_Range(Utility.GetRowRange(row_start + i, col_start, col_start + NUM_TOTAL_PAGE_HEADERS));
r_total.Value2 = oadata;
}
//return columns written
return(NUM_TOTAL_PAGE_HEADERS + 1);
}
/// <summary>
/// Fill in data from sheet into mempry
/// </summary>
private void ReadRows()
{
int offset = 1;
// read headers
object[,] headers_2d = o_first_sheet.get_Range(Utility.GetRowRange(header_row, column_start, column_end)).Value2;
string[] headers = new string[headers_2d.GetLength(1)];
for (int i = 0; i < headers_2d.GetLength(1); i++)
{
headers[i] = headers_2d[1, i + 1] as string;
}
object[,] data = o_first_sheet.get_Range(Utility.GetRowRange(header_row + (offset++), column_start, num_columns + 1)).Value;
//get data necessary for keys in dictionaries
string business_segment_name = Utility.AvoidNull(data[1, ConfigLoader.headerinfo[HeaderConstants.BusinessSegment] - column_start + 1] as string);
string product_line_name = Utility.AvoidNull(data[1, ConfigLoader.headerinfo[HeaderConstants.ProductLine] - column_start + 1] as string);
string project_number = Utility.AvoidNull(data[1, ConfigLoader.headerinfo[HeaderConstants.ProjectNumber] - column_start + 1] as string);
string project_name = Utility.AvoidNull(data[1, ConfigLoader.headerinfo[HeaderConstants.ProjectName] - column_start + 1] as string);
string design_review_type = Utility.AvoidNull(data[1, ConfigLoader.headerinfo[HeaderConstants.DesignReviewType] - column_start + 1] as string);
// while projects still left to read
while (project_name != "-")
{
//get business segment
BusinessSegment bs = dictionary_business_segments[business_segment_name];
// add product line
bs.AddProductLine(product_line_name);
//get product line
ProductLine pl = bs[product_line_name];
//add project
pl.AddProject(project_name, project_number);
// get project
Project proj = pl[project_number];
//add project data
proj.AddProjectData(design_review_type);
//get project data
ProjectData pd = proj[design_review_type];
//set project data key value pairs
for (int i = 0; i < headers.Length; i++)
{
pd[headers[i] as string] = data[1, i + 1];
}
//get data again
data = o_first_sheet.get_Range(Utility.GetRowRange(header_row + (offset++), column_start, num_columns + 1)).Value;
business_segment_name = Utility.AvoidNull(data[1, ConfigLoader.headerinfo[HeaderConstants.BusinessSegment] - column_start + 1] as string);
product_line_name = Utility.AvoidNull(data[1, ConfigLoader.headerinfo[HeaderConstants.ProductLine] - column_start + 1] as string);
project_number = Utility.AvoidNull(data[1, ConfigLoader.headerinfo[HeaderConstants.ProjectNumber] - column_start + 1] as string);
project_name = Utility.AvoidNull(data[1, ConfigLoader.headerinfo[HeaderConstants.ProjectName] - column_start + 1] as string);
design_review_type = Utility.AvoidNull(data[1, ConfigLoader.headerinfo[HeaderConstants.DesignReviewType] - column_start + 1] as string);
}
}
/// <summary>
/// Loads business segment dictionary
/// </summary>
private void ReadBusinessSegments()
{
// Get Business Segments in the file
int business_seg_column = ConfigLoader.headerinfo[HeaderConstants.BusinessSegment];
int row_index = header_row + 1;
string value = null;
//get column of business segments
Excel.Range business_seg = o_first_sheet.get_Range(Utility.GetColumnRange(business_seg_column, header_row + 1, last_row));
//get data from column
object[,] data = business_seg.Value2;
//go through column and pull out any strings
for (int i = 0; i < data.Length; i++)
{
value = data[i + 1, 1] as string;
//if no value use '-'
if (value == null)
{
value = "-";
}
//add business segment to dictionary if it doesn't already exist
BusinessSegment bs;
if (!dictionary_business_segments.TryGetValue(value, out bs))
{
dictionary_business_segments[value] = new BusinessSegment(value);
}
row_index++;
}
num_rows_of_data = row_index - header_row - 1;
Utility.Log("num rows of data: ", num_rows_of_data);
foreach (string s in dictionary_business_segments.Keys)
{
Utility.Log("dictionary business segment key:", s);
}
}
/// <summary>
/// Writes all generic totals information
/// </summary>
/// <param name="sheet">the sheet to write to</param>
/// <param name="col_start">the column to start on</param>
/// <param name="totals_headers">the list of headers</param>
/// <param name="feheaders">the array that stores the headers</param>
/// <param name="vert_business_seg">the business segment array</param>
/// <returns></returns>
private int WriteAllTotal(Excel.Worksheet sheet, int col_start, List <string> totals_headers, object[,] feheaders, object[,] vert_business_seg)
{
int NUM_TOTAL_HEADERS = 0;
List <string> list_drt = ConfigLoader.list_drt;
//fill in header info
int feheader_index = col_start - 2;
for (int i = 0; i < totals_headers.Count; i++)
{
SortedSet <string> set = dict_total[totals_headers[i]];
foreach (string h in set)
{
feheaders[0, feheader_index++] = totals_headers[i] + " " + h;
NUM_TOTAL_HEADERS++;
}
}
//correct offset by copying header into new array
object[,] feheaders_actual = new object[1, NUM_TOTAL_HEADERS];
for (int i = 0; i < NUM_TOTAL_HEADERS; i++)
{
feheaders_actual[0, i] = feheaders[0, i + feheader_index - NUM_TOTAL_HEADERS];
}
//set headers in sheet
string range_text = Utility.GetRowRange(1, col_start, col_start + NUM_TOTAL_HEADERS - 1);
Excel.Range rheader = sheet.get_Range(range_text);
rheader.Value2 = feheaders_actual;
rheader.WrapText = false;
int row_start = 2;
//for each business segment
for (int i = 0; i < vert_business_seg.GetLength(0); i++)
{
string business_seg = vert_business_seg[i, 0] as string;
object[,] oadata = new object[1, NUM_TOTAL_HEADERS];
//for each header
int col_index = 0;
for (int j = 0; j < totals_headers.Count; j++)
{
//for each value associated with that header
string key = totals_headers[j];
SortedSet <string> set = dict_total[totals_headers[j]];
foreach (string value in set)
{
BusinessSegment bs = dictionary_business_segments[business_seg];
int sum = 0;
// include all design review types in this total
foreach (string drt in list_drt)
{
sum += bs.CalculateTotal(drt, key, value);
}
oadata[0, col_index++] = sum;
}
}
//set data in sheet
Excel.Range r_total = sheet.get_Range(Utility.GetRowRange(row_start + i, col_start, col_start + NUM_TOTAL_HEADERS - 1));
r_total.Value2 = oadata;
}
// return columns written
return(NUM_TOTAL_HEADERS);
}
/// <summary>
/// Writes The Summary Page by copying the original data
/// </summary>
/// <param name="from">The sheet to copy from</param>
/// <param name="to">The sheet to write to</param>
private void CopyOriginalSheet(Excel.Worksheet from, Excel.Worksheet to)
{
//Copy header row
Excel.Range header_f = from.get_Range(Utility.GetRowRange(header_row, column_start, column_end));
Excel.Range header_t = to.get_Range(Utility.GetRowRange(1, 1, num_columns));
header_t.Value2 = header_f.Value2;
// List of design review types
List <string> design_review_types = ConfigLoader.list_drt;
// List of extra headers for the summary page
List <string> extra_summary_headers = ConfigLoader.summary_headers;
// List of extra headers for each design review type
List <string> summary_foreach_headers = ConfigLoader.summary_foreach_headers;
// Calculate size of array of data
int NUM_EXTRA_SUMMARY_HEADERS = design_review_types.Count * summary_foreach_headers.Count + extra_summary_headers.Count;
//Add extra headers
object[,] oaeheader = new object[1, NUM_EXTRA_SUMMARY_HEADERS];
// Fill header with extra summary headers
for (int i = 0; i < extra_summary_headers.Count; i++)
{
oaeheader[0, i] = extra_summary_headers[i];
}
// Fill header with extra headers for each design review type
for (int i = 0; i < design_review_types.Count * summary_foreach_headers.Count; i++)
{
oaeheader[0, i + extra_summary_headers.Count] = design_review_types[i / summary_foreach_headers.Count] + " " + summary_foreach_headers[i % summary_foreach_headers.Count];
}
// set header data in sheet
Excel.Range eheader = to.get_Range(Utility.GetRowRange(1, num_columns + 1, num_columns + NUM_EXTRA_SUMMARY_HEADERS));
eheader.Value2 = oaeheader;
eheader.WrapText = false;
//Copy Data
Excel.Range data_f;
Excel.Range data_t;
string old_project_num = "";
// go through every row in the original sheet that has data
for (int i = 0, current_row_read = header_row + 1, current_row_write = 2; i < num_rows_of_data; i++, current_row_read++)
{
//get the current row on 1st page
data_f = from.get_Range(Utility.GetRowRange(current_row_read, column_start, column_end));
object[,] oa_data_f = data_f.Value2;
//get the project number
string current_project_num = oa_data_f[1, ConfigLoader.headerinfo[HeaderConstants.ProjectNumber] - column_start + 1] as string;
//if the project number is different than the previous, create a new entry.
// this only lets one project number show up
if (!current_project_num.Equals(old_project_num))
{
// copy original data over
data_t = to.get_Range(Utility.GetRowRange(current_row_write, 1, num_columns));
data_t.Value = data_f.Value;
data_t.WrapText = false;
//get business segment
string business_segment = Utility.AvoidNull(oa_data_f[1, ConfigLoader.headerinfo[HeaderConstants.BusinessSegment] - column_start + 1] as string);
//get product line
string product_line = Utility.AvoidNull(oa_data_f[1, ConfigLoader.headerinfo[HeaderConstants.ProductLine] - column_start + 1] as string);
// Add Extra data for decisions and dates
object[,] oaedata = new object[1, NUM_EXTRA_SUMMARY_HEADERS];
//get business segment from map
BusinessSegment bs = dictionary_business_segments[business_segment];
//get product line from business line
ProductLine pl = bs[product_line];
//get project from product line by project number
Project proj = pl[current_project_num];
// fill in extra data for general project
for (int k = 0; k < extra_summary_headers.Count; k++)
{
ProjectData pd = proj[design_review_types[0]];
// data will get first design review type
if (pd != null)
{
oaedata[0, k] = pd[extra_summary_headers[k]];
}
else
{
oaedata[0, k] = "-";
}
}
//for each design review type, add the extra specified headers
for (int k = 0; k < design_review_types.Count * summary_foreach_headers.Count; k++)
{
ProjectData pd = proj[design_review_types[k / summary_foreach_headers.Count]];
if (pd != null)
{
oaedata[0, k + extra_summary_headers.Count] = pd[summary_foreach_headers[k % summary_foreach_headers.Count]];
}
else
{
oaedata[0, k + extra_summary_headers.Count] = "-";
}
}
//write data to sheet
string write_range = Utility.GetRowRange(current_row_write, num_columns + 1, num_columns + NUM_EXTRA_SUMMARY_HEADERS);
Excel.Range edata = to.get_Range(write_range);
edata.Value = oaedata;
edata.WrapText = false;
old_project_num = current_project_num;
current_row_write++;
}
}
//Auto fit columns to appropiate width
to.Columns.AutoFit();
//Shrink Large Columns
for (int i = 1; i < header_t.Count; i++)
{
if (header_t.Item[i].ColumnWidth > MAX_COLUMN_WIDTH)
{
header_t.Item[i].ColumnWidth = MAX_COLUMN_WIDTH;
}
}
//Shrink Large Columns
for (int i = 1; i < eheader.Count; i++)
{
if (eheader.Item[i].ColumnWidth > MAX_COLUMN_WIDTH)
{
eheader.Item[i].ColumnWidth = MAX_COLUMN_WIDTH;
}
}
to.Rows.UseStandardHeight = true;
}
