public void Format(Excel.Worksheet PivotSheet, string TableName, ListingStatus Status, string City) { Excel.PivotTable pvt = PivotSheet.PivotTables(TableName); int FirstRow = 0; int LastRow = 0; int LastCol = 0; int TitleRow = 0; FirstRow = pvt.TableRange1.Row + 1; LastRow = FirstRow + pvt.TableRange1.Rows.Count - 2; LastCol = pvt.ColumnRange.Columns.Count + pvt.ColumnRange.Column; TitleRow = pvt.TableRange2.Row - 1; //Todo: Format Title //Hide Values Row Excel.Range rng0 = PivotSheet.Range["A" + (FirstRow - 1)]; rng0.EntireRow.Hidden = true; Excel.Range c1 = PivotSheet.Cells[FirstRow, 1]; Excel.Range c2 = PivotSheet.Cells[FirstRow, LastCol]; Excel.Range rng = PivotSheet.Range[c1, c2]; rng.Select(); //rng.Style.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //rng.Style.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //rng.Style.Font.Name = "Roboto"; //rng.Style.Font.Size = 11; //rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; rng.RowHeight = 38; rng.WrapText = true; rng.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; rng.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; rng = PivotSheet.Range["A" + LastRow]; rng.Select(); rng.RowHeight = 36; // Create the table style //ListingBook.TableStyles.Add("Attached Report Style"); Excel.TableStyle ptStyle = ListingBook.TableStyles["PivotStyleLight16"]; //ptStyle.ShowAsAvailablePivotTableStyle = true; // Set Table Style pvt.TableStyle2 = ptStyle; FormatColumnWidth(); HideComplexSubTotal(PivotSheet, TableName); AddSubGroupBottomBorder(TableName); FormatMaxCells(); FormatMinCells(); AddSectionTitle(PivotSheet, TableName, City + " " + Status + " Records:"); }
private void Pregunta1() { if (wsheetAlumno.ListObjects.Count == 0) { p1 = "INCORRECTO"; p2 = "INCORRECTO"; } else { Excel.TableStyle tableStyleALumno = wsheetAlumno.ListObjects.get_Item("Tabla1").TableStyle; Excel.TableStyle tableStyleResuelto = wsheetResuelto.ListObjects.get_Item("Tabla2").TableStyle; Excel.Range rangeAlumno = wsheetAlumno.ListObjects.get_Item("Tabla1").Range; Excel.Range rangeResuelto = wsheetResuelto.ListObjects.get_Item("Tabla2").Range; if (tableStyleALumno.Name == tableStyleResuelto.Name && rangeAlumno.Column == rangeResuelto.Column && rangeAlumno.Row == rangeResuelto.Row && rangeAlumno.Count == rangeResuelto.Count) { p1 = "CORRECTO"; } else { p1 = "INCORRECTO"; } if (wsheetAlumno.ListObjects.get_Item("Tabla1").ShowTableStyleRowStripes == false) { p2 = "CORRECTO"; } else { p2 = "INCORRECTO"; } } GuardarPuntaje(); CerrarExcels(); BorrarPreguntasTemporales(); }
public void Format(Excel.Worksheet PivotSheet, string TableName, ListingStatus Status, string City) { Excel.PivotTable pvt = PivotSheet.PivotTables(TableName); int FirstRow = 0; int LastRow = 0; int LastCol = 0; int TitleRow = 0; FirstRow = pvt.TableRange1.Row + 1; LastRow = FirstRow + pvt.TableRange1.Rows.Count - 2; LastCol = pvt.ColumnRange.Columns.Count + pvt.ColumnRange.Column; TitleRow = pvt.TableRange2.Row - 1; //Todo: Format Title //Hide Values Row Excel.Range rng0 = PivotSheet.Range["A" + (FirstRow - 1)]; rng0.EntireRow.Hidden = true; rng0 = PivotSheet.Range["A" + (FirstRow - 2)]; rng0.RowHeight = 32; rng0.Font.Size = 24; Excel.Range c1 = PivotSheet.Cells[FirstRow, 1]; Excel.Range c2 = PivotSheet.Cells[FirstRow, LastCol]; Excel.Range rng = PivotSheet.Range[c1, c2]; rng.Select(); rng.RowHeight = 60; // 38; rng.WrapText = true; rng.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; rng.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; rng.Font.Size = 16; rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);; rng = PivotSheet.Range["A" + LastRow, "O" + LastRow]; rng.Select(); rng.RowHeight = 36; rng.Font.Size = 16; rng = PivotSheet.Range["A" + (FirstRow + 1), "O" + (LastRow - 2)]; rng.RowHeight = 32; rng.Font.Size = 16; // Create the table style //ListingBook.TableStyles.Add("Attached Report Style"); Excel.TableStyle ptStyle = ListingBook.TableStyles["PivotStyleLight16"]; //ptStyle.ShowAsAvailablePivotTableStyle = true; // Table style Header Row //Excel.TableStyleElement HeaderRow = ptStyle.TableStyleElements[Excel.XlTableStyleElementType.xlHeaderRow]; //HeaderRow.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1; //HeaderRow.Interior.TintAndShade = -0.249946592608417; //HeaderRow.Font.Bold = true; //HeaderRow.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.Color.Black.ToArgb(); //HeaderRow.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; //HeaderRow.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; //HeaderRow.Borders[Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.Color.Black.ToArgb(); //HeaderRow.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; //HeaderRow.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.Color.Black.ToArgb(); //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin; //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.Color.Black.ToArgb(); //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin; //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.Color.Black.ToArgb(); //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin; //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous; //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Black.ToArgb(); //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin; //HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; //// Table style Row Stripe 1 //Excel.TableStyleElement RowStripe1 = ptStyle.TableStyleElements[Excel.XlTableStyleElementType.xlRowStripe1]; //RowStripe1.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Red.ToArgb(); //RowStripe1.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin; //RowStripe1.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; //// Table style Row Stripe 2 //Excel.TableStyleElement RowStripe2 = ptStyle.TableStyleElements[Excel.XlTableStyleElementType.xlRowStripe2]; //RowStripe2.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.Blue.ToArgb(); //RowStripe2.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin; //RowStripe2.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous; // Set Table Style pvt.TableStyle2 = ptStyle; FormatColumnWidth(); HideComplexSubTotal(PivotSheet, TableName); AddSubGroupBottomBorder(TableName); FormatMaxCells(); FormatMinCells(); AddSectionTitle(PivotSheet, TableName, City + " " + Status + " Records:"); }
public void FormatPivotTable(Excel.Worksheet PivotSheet, string TableName) { Excel.PivotTable pvt = PivotSheet.PivotTables(TableName); int FirstRow = 0; int LastRow = 0; int LastCol = 0; int TitleRow = 0; PivotSheet.Select(); PivotSheet.Cells[1, 1].Select(); FirstRow = pvt.TableRange1.Row + 1; LastRow = FirstRow + pvt.TableRange1.Rows.Count - 2; LastCol = pvt.ColumnRange.Columns.Count + pvt.ColumnRange.Column - 1; TitleRow = pvt.TableRange2.Row - 1; //Todo: Format Title //Hide Values Row Excel.Range rng0 = PivotSheet.Range["A" + (FirstRow - 1)]; rng0.EntireRow.Hidden = true; //Title Row Excel.Range c1 = PivotSheet.Cells[FirstRow, 1]; Excel.Range c2 = PivotSheet.Cells[FirstRow, LastCol]; Excel.Range rng = PivotSheet.Range[c1, c2]; rng.Select(); rng.Style.Font.Name = "Roboto"; rng.Style.Font.Size = 14; rng.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlDouble; rng.RowHeight = 38; rng.WrapText = true; rng.EntireRow.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; rng.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignTop; //Grand Total Row rng = PivotSheet.Range["A" + LastRow]; rng.Select(); rng.RowHeight = 36; rng.Style.Font.Name = "Roboto"; rng.EntireRow.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //All Rows pvt.TableRange1.RowHeight = 36; //All DataRange pvt.DataBodyRange.Font.Size = 12; pvt.TableRange1.Select(); pvt.DataBodyRange.Select(); pvt.ColumnRange.Select(); pvt.RowRange.Select(); Console.WriteLine(pvt.ColumnRange.Columns.Count); Console.WriteLine(pvt.DataBodyRange.Columns.Count); pvt.DataLabelRange.Select(); pvt.ColumnRange.Columns[2].Select(); pvt.ColumnRange.Columns[2].Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); pvt.ColumnRange.Columns[2].Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin; pvt.ColumnRange.Columns[2].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; pvt.DataBodyRange.Columns[2].Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); pvt.DataBodyRange.Columns[2].Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin; pvt.DataBodyRange.Columns[2].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; pvt.ColumnRange.Columns[3].Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); pvt.ColumnRange.Columns[3].Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin; pvt.ColumnRange.Columns[3].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; pvt.DataBodyRange.Columns[3].Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); pvt.DataBodyRange.Columns[3].Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin; pvt.DataBodyRange.Columns[3].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; pvt.ColumnRange.Columns[5].Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); pvt.ColumnRange.Columns[5].Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin; pvt.ColumnRange.Columns[5].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; pvt.DataBodyRange.Columns[5].Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); pvt.DataBodyRange.Columns[5].Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin; pvt.DataBodyRange.Columns[5].Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous; pvt.ColumnRange.Columns[6].Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); pvt.ColumnRange.Columns[6].Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin; pvt.ColumnRange.Columns[6].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; pvt.DataBodyRange.Columns[6].Borders[Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue); pvt.DataBodyRange.Columns[6].Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin; pvt.DataBodyRange.Columns[6].Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous; //ListingBook.TableStyles.Add("Attached Report Style"); Excel.TableStyle ptStyle = ListingBook.TableStyles["PivotStyleLight16"]; // Set Table Style pvt.TableStyle2 = ptStyle; pvt.TableRange1.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexNone, System.Drawing.Color.DarkBlue); FormatTop3(pvt, FirstRow, LastRow, 4); FormatBottom3(pvt, FirstRow, LastRow, 4); FormatTop3(pvt, FirstRow, LastRow, 5); FormatBottom3(pvt, FirstRow, LastRow, 5); FormatTop3(pvt, FirstRow, LastRow, 7); FormatBottom3(pvt, FirstRow, LastRow, 7); FormatTop3(pvt, FirstRow, LastRow, 9); FormatBottom3(pvt, FirstRow, LastRow, 9); FormatColumnWidth(); }