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