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();
}
