internal static ExtendedFormat GetExcelStyle(XFRecrod xf, ExcelFont font) { ExcelBorder border2 = new ExcelBorder(); ExcelBorderSide side = new ExcelBorderSide { Color = new ExcelColor(ExcelColorType.Indexed, xf.LeftBorderColorIndex, 0.0), LineStyle = (ExcelBorderStyle)xf.LeftBorderLine }; border2.Left = side; ExcelBorderSide side2 = new ExcelBorderSide { Color = new ExcelColor(ExcelColorType.Indexed, xf.RightBorderColorIndex, 0.0), LineStyle = (ExcelBorderStyle)xf.RightBorderLine }; border2.Right = side2; ExcelBorderSide side3 = new ExcelBorderSide { Color = new ExcelColor(ExcelColorType.Indexed, xf.TopBorderColorIndex, 0.0), LineStyle = (ExcelBorderStyle)xf.TopBorderLine }; border2.Top = side3; ExcelBorderSide side4 = new ExcelBorderSide { Color = new ExcelColor(ExcelColorType.Indexed, xf.BottomBorderColorIndex, 0.0), LineStyle = (ExcelBorderStyle)xf.BottomBorderLine }; border2.Bottom = side4; ExcelBorder border = border2; ExtendedFormat format = new ExtendedFormat { Font = font, Border = border, PatternBackgroundColor = new ExcelColor(ExcelColorType.Indexed, xf.FillPatternBackgroundColor, 0.0), PatternColor = new ExcelColor(ExcelColorType.Indexed, xf.FillPatternColor, 0.0), FillPattern = (FillPatternType)xf.FillPatternIndex, HorizontalAlign = (ExcelHorizontalAlignment)xf.HAlignment, VerticalAlign = (ExcelVerticalAlignment)xf.VAlignment, Indent = xf.IndentLevel, IsHidden = xf.IsHidden, ApplyAlignment = xf.ApplyAlignment, ApplyBorder = xf.ApplyBorder, ApplyFont = xf.ApplyFont, ApplyFill = xf.ApplyFill, ApplyNumberFormat = xf.ApplyNumberFormat, ApplyProtection = xf.ApplyProtection, IsStyleFormat = xf.IsStyleXF, IsJustfyLastLine = xf.JustifyLastCharacter, IsLocked = xf.IsLocked, IsShrinkToFit = xf.IsShrinkContent, IsWordWrap = xf.IsWordWrap, ReadingOrder = (TextDirection)xf.Direction, Rotation = xf.TextRotation, IsFirstSymbolApostrophe = xf.IsF123Prefix }; if (!format.IsStyleFormat) { format.ParentFormatID = new int?(xf.ParentXFIndex); } return(format); }
private void AssertBorder(ExcelBorderStyle expectedBorderStyle, ExcelBorder border) { Assert.AreEqual(expectedBorderStyle, border.Bottom.Style); Assert.AreEqual(expectedBorderStyle, border.Left.Style); Assert.AreEqual(expectedBorderStyle, border.Top.Style); Assert.AreEqual(expectedBorderStyle, border.Right.Style); }
private void footerPrint(ExcelXmlParser parser) { cols = parser.getColumnsInfo("foot"); ExcelBorder border = getBorder(); if (parser.getWithoutHeader() == false) { ExcelFont font = wb.CreateFont(FontFamily, FooterFontSize); font.Bold = true; if (HeaderTextColor != "FF000000") { font.Color = HeaderTextColor; } for (uint row = 1; row <= cols.Length; row++) { uint rowInd = (uint)(row + headerOffset); sheet.Rows[rowInd].Height = RowHeight; for (uint col = 1; col <= cols[row - 1].Length; col++) { if (BGColor != "FFFFFFFF") { sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = BGColor; } sheet.Cells[rowInd, col].Style.Font = font; //TODO add text color, vertical alignment, horizontal alignment sheet.Cells[rowInd, col].Style.Border = border; sheet.Cells[rowInd, col].Value = cols[row - 1][col - 1].GetName(); } } } headerOffset += cols.Length; }
protected ExcelBorder getBorder() { ExcelBorder border = new ExcelBorder(null, wb.Styles, 0); border.BottomStyle = OpenExcel.OfficeOpenXml.Style.ExcelBorderStyleValues.Thin; border.BottomColor = lineColor; border.LeftStyle = OpenExcel.OfficeOpenXml.Style.ExcelBorderStyleValues.Thin; border.LeftColor = lineColor; border.RightStyle = OpenExcel.OfficeOpenXml.Style.ExcelBorderStyleValues.Thin; border.RightColor = lineColor; border.TopStyle = OpenExcel.OfficeOpenXml.Style.ExcelBorderStyleValues.Thin; border.TopColor = lineColor; return(border); }
private void footerPrint(ExcelXmlParser parser) { cols = parser.getColumnsInfo("foot"); ExcelBorder border = getBorder(); if (parser.getWithoutHeader() == false) { ExcelFont font = wb.CreateFont("Arial", 10); font.Bold = true; if (headerTextColor != "FF000000") { font.Color = headerTextColor; } for (uint row = 1; row <= cols.Length; row++) { uint rowInd = (uint)(row + headerOffset); // sheet.Rows[rowInd].Height = 22.5; for (uint col = 1; col <= cols[row - 1].Length; col++) { if (bgColor != "FFFFFFFF") { sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = bgColor; } sheet.Cells[rowInd, col].Style.Font = font; //TODO add text color, vertical alignment, horizontal alignment sheet.Cells[rowInd, col].Style.Border = border; sheet.Cells[rowInd, col].Value = cols[row - 1][col - 1].GetName(); } } /* for (int col = 0; col < cols.Length; col++) { * for (int row = 0; row < cols[col].Length; row++) { * int cspan = cols[col][row].GetColspan(); * if (cspan > 0) { * sheet.mergeCells(row, headerOffset + col, row + cspan - 1, headerOffset + col); * } * int rspan = cols[col][row].GetRowspan(); * if (rspan > 0) { * sheet.mergeCells(row, headerOffset + col, row, headerOffset + col + rspan - 1); * } * } * }*/ } headerOffset += cols.Length; }
private void watermarkPrint(ExcelXmlParser parser) { if (watermark == null) { return; } ExcelFont font = wb.CreateFont(FontFamily, WatermarkFontSize); font.Bold = true; font.Color = WatermarkTextColor; ExcelBorder border = getBorder(); // f.setAlignment(Alignment.CENTRE); sheet.Cells[(uint)(headerOffset + 1), 0].Value = watermark; }
private void watermarkPrint(ExcelXmlParser parser) { if (watermark == null) { return; } ExcelFont font = wb.CreateFont("Arial", 10); font.Bold = true; font.Color = watermarkTextColor; ExcelBorder border = getBorder(); //f.setAlignment(Alignment.CENTRE); sheet.Cells[(uint)(headerOffset + 1), 0].Value = watermark; // Label label = new Label(0, headerOffset, watermark , f); // sheet.addCell(label); // sheet.mergeCells(0, headerOffset, colsNumber, headerOffset);*/ }
private static void ApplyBorderStyle(ExcelBorder borderFormat, ExcelApp.Borders border, bool isConditionnalFormatting = false) { var sides = new[] { ExcelApp.XlBordersIndex.xlEdgeLeft, ExcelApp.XlBordersIndex.xlEdgeRight, ExcelApp.XlBordersIndex.xlEdgeTop, ExcelApp.XlBordersIndex.xlEdgeBottom, ExcelApp.XlBordersIndex.xlInsideVertical, ExcelApp.XlBordersIndex.xlInsideHorizontal }; var condFormatSides = new[] { ExcelApp.Constants.xlLeft, ExcelApp.Constants.xlRight, ExcelApp.Constants.xlTop, ExcelApp.Constants.xlBottom }.Cast <ExcelApp.XlBordersIndex>(); if (borderFormat != null) { foreach (var side in isConditionnalFormatting ? condFormatSides : sides) { border[side].LineStyle = borderFormat.BorderStyle; if (borderFormat.BorderColor.HasValue) { border[side].Color = borderFormat.BorderColor.Value; } if (borderFormat.BorderThickness.HasValue) { border[side].Weight = borderFormat.BorderThickness.Value; } } } }
private void rowsPrint(ExcelXmlParser parser, Stream resp) { ExcelRow[] rows = parser.getGridContent(); this.rows_stat = rows.Length; ExcelBorder border = getBorder(); ExcelFont font = wb.CreateFont("Arial", 10); // if (gridTextColor != "FF000000") // font.Color = gridTextColor; for (uint row = 1; row <= rows.Length; row++) { ExcelCell[] cells = rows[row - 1].getCells(); uint rowInd = (uint)(row + headerOffset); // sheet.Rows[rowInd].Height = 20; for (uint col = 1; col <= cells.Length; col++) { if (cells[col - 1].GetBold() || cells[col - 1].GetItalic()) { ExcelFont curFont = wb.CreateFont("Arial", 10);; // if (gridTextColor != "FF000000") // font.Color = gridTextColor; if (cells[col - 1].GetBold()) { font.Bold = true; } if (cells[col - 1].GetItalic()) { font.Italic = true; } sheet.Cells[rowInd, col].Style.Font = curFont; } else { sheet.Cells[rowInd, col].Style.Font = font; } sheet.Cells[rowInd, col].Style.Border = border; if ((!cells[col - 1].GetBgColor().Equals("")) && (parser.getProfile().Equals("full_color"))) { sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = "FF" + cells[col - 1].GetBgColor(); } else { //Colour bg; if (row % 2 == 0 && scaleTwoColor != "FFFFFFFF") { sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = scaleTwoColor; } else { if (scaleOneColor != "FFFFFFFF") { sheet.Cells[rowInd, col].Style.Fill.ForegroundColor = scaleOneColor; } } } int intVal; double dbVal; if (int.TryParse(cells[col - 1].GetValue(), out intVal)) { sheet.Cells[rowInd, col].Value = intVal; } else if (double.TryParse(cells[col - 1].GetValue(), out dbVal)) { sheet.Cells[rowInd, col].Value = dbVal; } else { sheet.Cells[rowInd, col].Value = cells[col - 1].GetValue(); } //COLOR! //String al = cells[row].getAlign(); //if (al == "") // al = cols[0][row].getAlign(); //if (al.equalsIgnoreCase("left")) //{ // f.setAlignment(Alignment.LEFT); //} //else //{ // if (al.equalsIgnoreCase("right")) // { // f.setAlignment(Alignment.RIGHT); // } // else // { // f.setAlignment(Alignment.CENTRE); // } //} } } headerOffset += rows.Length; }
//public void Generate(string xml, HttpResponseBase resp) //{ // var data = new MemoryStream(); // resp.ContentType = ContentType; // resp.HeaderEncoding = Encoding.UTF8; // resp.AppendHeader("Content-Disposition", "attachment;filename=my_report.xlsx"); // resp.AppendHeader("Cache-Control", "max-age=0"); // Generate(xml, data); // data.WriteTo(resp.OutputStream); //} private void headerPrint(ExcelXmlParser parser) { cols = parser.getColumnsInfo("head"); int[] widths = parser.getWidths(); this.cols_stat = widths.Length; int sumWidth = 0; for (int i = 0; i < widths.Length; i++) { sumWidth += widths[i]; } if (parser.getWithoutHeader() == false) { ExcelFont font = wb.CreateFont("Arial", 10); font.Bold = true; if (headerTextColor != "FF000000") { font.Color = headerTextColor; } ExcelBorder border = getBorder(); for (uint row = 1; row <= cols.Length; row++) { // sheet.Rows[row].Height = 22.5; for (uint col = 1; col <= cols[row - 1].Length; col++) { // trick before font //sheet.Cells[row, col].Style.SetAlignmentCenter = true; sheet.Cells[row, col, true].IsHead = true; sheet.Cells[row, col, true].Style.Font = font;//if bold font assigned after border - all table will be bold, weird, find out later sheet.Cells[row, col].Style.Border = border; sheet.Columns[col].Width = widths[col - 1] / scale; String name = cols[row - 1][col - 1].GetName(); if (bgColor != "FFFFFFFF") { sheet.Cells[row, col, true].Style.Fill.ForegroundColor = bgColor; } ///TODO: ///font color, merge cells, alignment sheet.Cells[row, col, true].Value = name; colsNumber = (int)col; } } headerOffset = cols.Length; /* for (int col = 0; col < cols.Length; col++) { * for (int row = 0; row < cols[col].Length; row++) { * int cspan = cols[col][row].GetColspan(); * if (cspan > 0) { * sheet.mergeCells(row, col, row + cspan - 1, col); * } * int rspan = cols[col][row].GetRowspan(); * if (rspan > 0) { * sheet.mergeCells(row, col, row, col + rspan - 1); * } * } * }*/ } }
private void headerPrint(ExcelXmlParser parser) { cols = parser.getColumnsInfo("head"); //Widths int[] widths = parser.getWidths(); if (this.Widths != null) { foreach (var index in this.Widths.Keys) { if (index >= 0 && index < widths.Length) { widths[index] = Widths[index]; } } } this.cols_stat = widths.Length; int sumWidth = 0; for (int i = 0; i < widths.Length; i++) { sumWidth += widths[i]; } if (parser.getWithoutHeader() == false) { ExcelFont font = wb.CreateFont(FontFamily, HeaderFontSize); font.Bold = true; if (HeaderTextColor != "FF000000") { font.Color = HeaderTextColor; } ExcelBorder border = getBorder(); for (uint row = 1; row <= cols.Length; row++) { sheet.Rows[row].Height = RowHeight; for (uint col = 1; col <= cols[row - 1].Length; col++) { sheet.Cells[row, col].Style.Font = font;//if bold font assigned after border - all table will be bold, weird, find out later sheet.Cells[row, col].Style.Border = border; sheet.Columns[col].Width = widths[col - 1] / scale; String name = cols[row - 1][col - 1].GetName(); if (BGColor != "FFFFFFFF") { sheet.Cells[row, col].Style.Fill.ForegroundColor = BGColor; } ///TODO: ///font color, merge cells, alignment sheet.Cells[row, col].Value = name; colsNumber = (int)col; } } headerOffset = cols.Length; } }