/// <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; }