/// <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; }
/// <summary> /// Writes Individual design review type summary pages /// </summary> /// <param name="sheet">the sheet to write to</param> /// <param name="design_review_type">the design review type (CDR,DDR,...)</param> private void WriteDesRevPage(Excel.Worksheet sheet, string design_review_type) { // get headers from config List <string> headers_to_write = ConfigLoader.drtheaders[design_review_type]; int NUM_INDIVIDUAL_PAGE_HEADERS = headers_to_write.Count; object[,] headers = new object[1, NUM_INDIVIDUAL_PAGE_HEADERS]; // fill headers for (int i = 0; i < NUM_INDIVIDUAL_PAGE_HEADERS; i++) { headers[0, i] = headers_to_write[i]; } // Write headers Excel.Range header_row = sheet.get_Range(Utility.GetRowRange(1, 1, NUM_INDIVIDUAL_PAGE_HEADERS)); header_row.Value2 = headers; header_row.WrapText = false; long write_row = 2; //Write data // foreach business segment foreach (BusinessSegment bs in dictionary_business_segments.Values) { //for each product line in that business segment foreach (ProductLine pl in bs.ProductLines) { // for each project in that product line foreach (Project proj in pl.Projects) { // create array to hold data object[,] data = new object[1, NUM_INDIVIDUAL_PAGE_HEADERS]; // get the range for the row of data Excel.Range data_write = sheet.get_Range(Utility.GetRowRange(write_row, 1, NUM_INDIVIDUAL_PAGE_HEADERS)); // get the project data for the specific design review type ProjectData pd = proj[design_review_type]; // if that design review type exists if (pd != null) { // for each header entry for (int i = 0; i < headers_to_write.Count; i++) { // get the data associated with that header string key = headers_to_write[i]; object val = pd[key]; data[0, i] = val; } // set range to have data data_write.Value = data; data_write.WrapText = false; // increment row count write_row++; } } } } sheet.Columns.AutoFit(); //Shrink Large Columns for (int i = 1; i < header_row.Count; i++) { if (header_row.Item[i].ColumnWidth > MAX_COLUMN_WIDTH) { header_row.Item[i].ColumnWidth = MAX_COLUMN_WIDTH; } } sheet.Rows.UseStandardHeight = true; }