private static void SetCellProperties(PivotExportCellInfo cellInfo, CellSelection cellSelection)
{
var fill = GenerateFill(cellInfo.Background);
if (fill != null)
{
cellSelection.SetFill(fill);
}
SolidColorBrush solidBrush = cellInfo.Foreground as SolidColorBrush;
if (solidBrush != null)
{
cellSelection.SetForeColor(new ThemableColor(solidBrush.Color));
}
if (cellInfo.FontWeight.HasValue && cellInfo.FontWeight.Value != FontWeights.Normal)
{
cellSelection.SetIsBold(true);
}
SolidColorBrush solidBorderBrush = cellInfo.BorderBrush as SolidColorBrush;
if (solidBorderBrush != null && cellInfo.BorderThickness.HasValue)
{
var borderThickness = cellInfo.BorderThickness.Value;
var color = new ThemableColor(solidBorderBrush.Color);
//var leftBorder = new CellBorder(GetBorderStyle(borderThickness.Left), color);
//var topBorder = new CellBorder(GetBorderStyle(borderThickness.Top), color);
var rightBorder = new CellBorder(GetBorderStyle(borderThickness.Right), color);
var bottomBorder = new CellBorder(GetBorderStyle(borderThickness.Bottom), color);
var insideBorder = cellInfo.Background != null ? new CellBorder(CellBorderStyle.None, color) : null;
cellSelection.SetBorders(new CellBorders(null, null, rightBorder, bottomBorder, insideBorder, insideBorder, null, null));
}
}
public void CreateCellStyle()
{
#region radspreadprocessing-features-styling-cell-styles_1
Workbook workbook = new Workbook();
workbook.Worksheets.Add();
CellStyle cellStyle = workbook.Styles.Add("My style", CellStyleCategory.Custom);
cellStyle.BeginUpdate();
CellBorder border = new CellBorder(CellBorderStyle.DashDotDot, new ThemableColor(Colors.Red));
cellStyle.LeftBorder = border;
cellStyle.TopBorder = border;
cellStyle.RightBorder = border;
cellStyle.BottomBorder = border;
ThemableColor patternColor = new ThemableColor(ThemeColorType.Accent1);
ThemableColor backgroundColor = new ThemableColor(ThemeColorType.Accent5, ColorShadeType.Shade2);
IFill fill = new PatternFill(PatternType.Gray75Percent, patternColor, backgroundColor);
cellStyle.Fill = fill;
cellStyle.HorizontalAlignment = RadHorizontalAlignment.Left;
cellStyle.VerticalAlignment = RadVerticalAlignment.Center;
cellStyle.EndUpdate();
workbook.ActiveWorksheet.Cells[0, 0].SetStyleName("My style");
#endregion
}
public void Borders()
{
var border = new CellBorder();
border.Left = new BorderEdge { Style = BorderStyle.Double, Color = Color.Black }; //apply double black border to left edge
border.All = new BorderEdge { Style = BorderStyle.Medium, Color = Color.Black }; //apply medium black border to left, right, top and bottom edge
border.Diagonal = new BorderEdge { Style = BorderStyle.Dashed, Color = Color.Black }; // diagonal border is supported also
border.DiagonalUp = true; //in this case you should set which diagonal to use
border.DiagonalDown = false; //in this case you should set which diagonal to use
var wb = new Workbook();
var sheet = wb.Sheets.AddSheet("Demo Sheet");
sheet["A1"].Style.Border = border; //apply prepared border to cell A1
sheet["B1"].Style.Border = border; //apply prepared border to cell B1
sheet["C1"].Style.Border = border; //apply prepared border to cell C1
sheet["C1"].Style.Border.Bottom = null; //watch out, you have removed bottom border on A1, A2 and A3
var range = sheet["A3", "E10"];
range.SetBorder(new BorderEdge { Style = BorderStyle.Thin, Color = Color.Black }); //border is easier to set on ranges
range.GetRow(0).SetOutsideBorder(new BorderEdge { Style = BorderStyle.Medium, Color = Color.Black }); //header may need a heavier border
range.SetOutsideBorder(new BorderEdge { Style = BorderStyle.Double, Color = Color.Black }); //use double border outside the table
wb.Save("Borders.xlsx");
}
internal RenderObject MakeOwnerDrawCellRO(int row, int col, ref CellStyle cellStyle, ref string text, ref Image image)
{
bool handled = false;
// draw rectangle:
Rectangle rc = Rectangle.Empty;
rc.Width = Cols[col].WidthDisplay;
rc.Height = Rows[row].HeightDisplay;
Metafile meta;
using (Graphics gRef = Graphics.FromHwnd(IntPtr.Zero))
{
IntPtr hdcRef = gRef.GetHdc();
meta = new Metafile(hdcRef, rc, MetafileFrameUnit.Pixel, EmfType.EmfPlusDual);
using (Graphics g = Graphics.FromImage(meta))
{
// owner-drawing cell borders is not supported:
CellBorder cbSave = cellStyle.Border;
BorderStyleEnum bsSave = cbSave.Style;
IsPrinting = true;
OwnerDrawCellEventArgs e = new OwnerDrawCellEventArgs(
this, g, row, col, cellStyle, rc, text, image);
try
{
OnOwnerDrawCell(e);
}
finally
{
IsPrinting = false;
cbSave.Style = bsSave;
}
handled = e.Handled;
if (!handled)
{
cellStyle = e.Style;
text = e.Text;
image = e.Image;
}
}
gRef.ReleaseHdc(hdcRef);
}
if (handled)
{
return(new RenderImage(meta));
}
// release unwanted resources asap:
meta.Dispose();
return(null);
}
private CellBorder ConvertDxfBorder(CT_Border border)
{
CellBorder b = new CellBorder
{
Bottom = ConvertBorderEdge(border.bottom),
Right = ConvertBorderEdge(border.right),
Left = ConvertBorderEdge(border.left),
Top = ConvertBorderEdge(border.top),
Diagonal = ConvertBorderEdge(border.diagonal),
DiagonalDown = border.diagonalDown,
DiagonalUp = border.diagonalUp,
Vertical = ConvertBorderEdge(border.vertical),
Horizontal = ConvertBorderEdge(border.horizontal),
Outline = border.outline
};
return(b);
}
protected void prepareDocument(Worksheet worksheet)
{
PatternFill pfGreen = new PatternFill(PatternType.Solid, System.Windows.Media.Color.FromArgb(255, 153, 204, 0), System.Windows.Media.Colors.Transparent);
PatternFill pfOrange = new PatternFill(PatternType.Solid, System.Windows.Media.Color.FromArgb(255, 255, 204, 0), System.Windows.Media.Colors.Transparent);
PatternFill pfRed = new PatternFill(PatternType.Solid, System.Windows.Media.Color.FromArgb(255, 255, 0, 0), System.Windows.Media.Colors.Transparent);
PatternFill pfBlue = new PatternFill(PatternType.Solid, System.Windows.Media.Color.FromArgb(255, 0, 0, 255), System.Windows.Media.Colors.Transparent);
CellBorder border = new CellBorder(CellBorderStyle.Thin, tcBlack);
CellBorders borders = new CellBorders(border, border, border, border, null, null, null, null);
double fontSize = 12;
worksheet.Cells[0, 0].SetValue("A/A");
worksheet.Cells[0, 0].SetFontSize(fontSize);
worksheet.Cells[0, 0].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 0].SetIsBold(true);
worksheet.Cells[0, 0].SetFill(pfGreen);
worksheet.Cells[0, 0].SetBorders(borders);
worksheet.Cells[0, 1].SetValue("ΑΙΤΩΝ");
worksheet.Cells[0, 1].SetFontSize(fontSize);
worksheet.Cells[0, 1].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 1].SetIsBold(true);
//worksheet.Cells[0, 1].SetIsWrapped(true);
worksheet.Cells[0, 1].SetFill(pfGreen);
worksheet.Cells[0, 1].SetBorders(borders);
worksheet.Cells[0, 2].SetValue("ΑΠΟ");
worksheet.Cells[0, 2].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 2].SetFontSize(fontSize);
worksheet.Cells[0, 2].SetIsBold(true);
//worksheet.Cells[0, 2].SetIsWrapped(true);
worksheet.Cells[0, 2].SetFill(pfOrange);
worksheet.Cells[0, 2].SetBorders(borders);
worksheet.Cells[0, 3].SetValue("ΩΡΑ");
worksheet.Cells[0, 3].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 3].SetFontSize(fontSize);
worksheet.Cells[0, 3].SetIsBold(true);
worksheet.Cells[0, 3].SetFill(pfGreen);
worksheet.Cells[0, 3].SetBorders(borders);
worksheet.Cells[0, 4].SetValue("ΠΑΡΑΤΗΡΗΣΕΙΣ");
worksheet.Cells[0, 4].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 4].SetFontSize(fontSize);
worksheet.Cells[0, 4].SetIsBold(true);
worksheet.Cells[0, 4].SetFill(pfGreen);
worksheet.Cells[0, 4].SetBorders(borders);
}
CT_Border ConvertBorder(CellBorder border)
{
var res = new CT_Border
{
left = ConvertBorderPr(border.left),
right = ConvertBorderPr(border.right),
top = ConvertBorderPr(border.top),
bottom = ConvertBorderPr(border.bottom),
vertical = ConvertBorderPr(border.vertical),
horizontal = ConvertBorderPr(border.horizontal),
diagonal = ConvertBorderPr(border.diagonal),
diagonalDown = border.DiagonalDown.GetValueOrDefault(),
diagonalDownSpecified = border.DiagonalDown.HasValue,
diagonalUp = border.DiagonalUp.GetValueOrDefault(),
diagonalUpSpecified = border.DiagonalUp.HasValue,
outline = border.Outline
};
return(res);
}
private void PrepareInvoiceDocument(Worksheet worksheet, int itemsCount)
{
int lastItemIndexRow = IndexRowItemStart + itemsCount;
CellIndex firstUsedCellIndex = new CellIndex(0, 0);
CellIndex lastUsedCellIndex = new CellIndex(lastItemIndexRow + 1, IndexColumnSubTotal);
CellBorder border = new CellBorder(CellBorderStyle.DashDot, InvoiceBackground);
worksheet.Cells[firstUsedCellIndex, lastUsedCellIndex].SetBorders(new CellBorders(border, border, border, border, null, null, null, null));
worksheet.Cells[firstUsedCellIndex].SetValue("INVOICE");
worksheet.Cells[firstUsedCellIndex].SetFontSize(20);
worksheet.Cells[firstUsedCellIndex].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 0, 0, IndexColumnSubTotal].MergeAcross();
worksheet.Columns[IndexColumnUnitPrice].SetWidth(new ColumnWidth(125, true));
worksheet.Columns[IndexColumnSubTotal].SetWidth(new ColumnWidth(125, true));
worksheet.Cells[IndexRowItemStart, 0].SetValue("Item");
worksheet.Cells[IndexRowItemStart, IndexColumnQuantity].SetValue("QTY");
worksheet.Cells[IndexRowItemStart, IndexColumnUnitPrice].SetValue("Unit Price");
worksheet.Cells[IndexRowItemStart, IndexColumnSubTotal].SetValue("SubTotal");
worksheet.Cells[IndexRowItemStart, 0, lastItemIndexRow, IndexColumnQuantity - 1].MergeAcross();
worksheet.Cells[IndexRowItemStart, 0, IndexRowItemStart, IndexColumnSubTotal].SetFill
(new GradientFill(GradientType.Horizontal, InvoiceBackground, InvoiceBackground));
worksheet.Cells[IndexRowItemStart, 0, IndexRowItemStart, IndexColumnSubTotal].SetForeColor(InvoiceHeaderForeground);
worksheet.Cells[IndexRowItemStart, IndexColumnUnitPrice, lastItemIndexRow, IndexColumnSubTotal].SetFormat(
new CellValueFormat(AccountFormatString));
worksheet.Cells[lastItemIndexRow + 1, 6].SetValue("TOTAL: ");
worksheet.Cells[lastItemIndexRow + 1, 7].SetFormat(new CellValueFormat(AccountFormatString));
string subTotalColumnCellRange = NameConverter.ConvertCellRangeToName(
new CellIndex(IndexRowItemStart + 1, IndexColumnSubTotal),
new CellIndex(lastItemIndexRow, IndexColumnSubTotal));
worksheet.Cells[lastItemIndexRow + 1, IndexColumnSubTotal].SetValue(string.Format("=SUM({0})", subTotalColumnCellRange));
worksheet.Cells[lastItemIndexRow + 1, IndexColumnUnitPrice, lastItemIndexRow + 1, IndexColumnSubTotal].SetFontSize(20);
}
public void Borders()
{
var border = new CellBorder();
border.Left = new BorderEdge {
Style = BorderStyle.Double, Color = Color.Black
}; //apply double black border to left edge
border.All = new BorderEdge {
Style = BorderStyle.Medium, Color = Color.Black
}; //apply medium black border to left, right, top and bottom edge
border.Diagonal = new BorderEdge {
Style = BorderStyle.Dashed, Color = Color.Black
}; // diagonal border is supported also
border.DiagonalUp = true; //in this case you should set which diagonal to use
border.DiagonalDown = false; //in this case you should set which diagonal to use
var wb = new Workbook();
var sheet = wb.Sheets.AddSheet("Demo Sheet");
sheet["A1"].Style.Border = border; //apply prepared border to cell A1
sheet["B1"].Style.Border = border; //apply prepared border to cell B1
sheet["C1"].Style.Border = border; //apply prepared border to cell C1
sheet["C1"].Style.Border.Bottom = null; //watch out, you have removed bottom border on A1, A2 and A3
var range = sheet["A3", "E10"];
range.SetBorder(new BorderEdge {
Style = BorderStyle.Thin, Color = Color.Black
}); //border is easier to set on ranges
range.GetRow(0).SetOutsideBorder(new BorderEdge {
Style = BorderStyle.Medium, Color = Color.Black
}); //header may need a heavier border
range.SetOutsideBorder(new BorderEdge {
Style = BorderStyle.Double, Color = Color.Black
}); //use double border outside the table
wb.Save("Borders.xlsx");
}
// todo: handle dotted lines
private LineDef BorderToLineDef(CellStyle cellStyle, bool horizontal)
{
CellBorder cb = cellStyle.Border;
bool none;
if (horizontal)
{
none = cb.Style == BorderStyleEnum.None || cb.Direction == BorderDirEnum.Vertical;
}
else // vertical
{
none = cb.Style == BorderStyleEnum.None || cb.Direction == BorderDirEnum.Horizontal;
}
if (none)
{
return(LineDef.Empty);
}
else
{
return(new LineDef(PixelsToUnit(cb.Width), cb.Color));
}
}
private static void SetCellProperties(PivotExportCellInfo cellInfo, CellSelection cellSelection)
{
var fill = GenerateFill(cellInfo.Background);
if (fill != null)
{
cellSelection.SetFill(fill);
}
SolidColorBrush solidBrush = cellInfo.Foreground as SolidColorBrush;
if (solidBrush != null)
{
cellSelection.SetForeColor(new ThemableColor(solidBrush.Color));
}
if (cellInfo.FontWeight.HasValue && cellInfo.FontWeight.Value != FontWeights.Normal)
{
cellSelection.SetIsBold(true);
}
SolidColorBrush solidBorderBrush = cellInfo.BorderBrush as SolidColorBrush;
if (solidBorderBrush != null && cellInfo.BorderThickness.HasValue)
{
var borderThickness = cellInfo.BorderThickness.Value;
var color = new ThemableColor(solidBorderBrush.Color);
//var leftBorder = new CellBorder(GetBorderStyle(borderThickness.Left), color);
//var topBorder = new CellBorder(GetBorderStyle(borderThickness.Top), color);
var rightBorder = new CellBorder(GetBorderStyle(borderThickness.Right), color);
var bottomBorder = new CellBorder(GetBorderStyle(borderThickness.Bottom), color);
var insideBorder = cellInfo.Background != null ? new CellBorder(CellBorderStyle.None, color) : null;
cellSelection.SetBorders(new CellBorders(null, null, rightBorder, bottomBorder, insideBorder, insideBorder, null, null));
}
}
private static Thickness ConvertToThickness(CellBorder cellBorder)
{
switch (cellBorder.LineStyle) {
case LineStyle.Hair:
return new Thickness(0.1d);
case LineStyle.Medium:
case LineStyle.MediumDashDot:
case LineStyle.MediumDashDotDot:
case LineStyle.MediumDashed:
return new Thickness(1.25d);
case LineStyle.None:
return new Thickness(0);
case LineStyle.Thick:
return new Thickness(3);
case LineStyle.SlantDashDot:
case LineStyle.DashDot:
case LineStyle.DashDotDot:
case LineStyle.Dashed:
case LineStyle.Dotted:
case LineStyle.Double:
case LineStyle.Thin:
default:
return new Thickness(0.4d);
}
}
private void PrepareInvoiceDocument(Worksheet worksheet, int itemsCount)
{
int lastItemIndexRow = IndexRowItemStart + itemsCount;
CellIndex firstUsedCellIndex = new CellIndex(0, 0);
CellIndex lastUsedCellIndex = new CellIndex(lastItemIndexRow + 1, IndexColumnSubTotal);
CellBorder border = new CellBorder(CellBorderStyle.DashDot, InvoiceBackground);
worksheet.Cells[firstUsedCellIndex, lastUsedCellIndex].SetBorders(new CellBorders(border, border, border, border, null, null, null, null));
worksheet.Cells[firstUsedCellIndex].SetValue("INVOICE");
worksheet.Cells[firstUsedCellIndex].SetFontSize(20);
worksheet.Cells[firstUsedCellIndex].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 0, 0, IndexColumnSubTotal].MergeAcross();
worksheet.Columns[IndexColumnUnitPrice].SetWidth(new ColumnWidth(120, true));
worksheet.Columns[IndexColumnSubTotal].SetWidth(new ColumnWidth(120, true));
worksheet.Cells[IndexRowItemStart, 0, lastItemIndexRow, IndexColumnQuantity - 1].MergeAcross();
worksheet.Cells[IndexRowItemStart, 0].SetValue("Item");
worksheet.Cells[IndexRowItemStart, IndexColumnQuantity].SetValue("QTY");
worksheet.Cells[IndexRowItemStart, IndexColumnUnitPrice].SetValue("Unit Price");
worksheet.Cells[IndexRowItemStart, IndexColumnSubTotal].SetValue("SubTotal");
worksheet.Cells[IndexRowItemStart, 0, IndexRowItemStart, IndexColumnSubTotal].SetFill
(new GradientFill(GradientType.Horizontal, InvoiceBackground, InvoiceBackground));
worksheet.Cells[IndexRowItemStart, 0, IndexRowItemStart, IndexColumnSubTotal].SetForeColor(InvoiceHeaderForeground);
worksheet.Cells[IndexRowItemStart, IndexColumnUnitPrice, lastItemIndexRow, IndexColumnSubTotal].SetFormat(
new CellValueFormat(EnUSCultureAccountFormatString));
worksheet.Cells[lastItemIndexRow + 1, 6].SetValue("TOTAL: ");
worksheet.Cells[lastItemIndexRow + 1, 7].SetFormat(new CellValueFormat(EnUSCultureAccountFormatString));
string subTotalColumnCellRange = NameConverter.ConvertCellRangeToName(
new CellIndex(IndexRowItemStart + 1, IndexColumnSubTotal),
new CellIndex(lastItemIndexRow, IndexColumnSubTotal));
worksheet.Cells[lastItemIndexRow + 1, IndexColumnSubTotal].SetValue(string.Format("=SUM({0})", subTotalColumnCellRange));
worksheet.Cells[lastItemIndexRow + 1, IndexColumnUnitPrice, lastItemIndexRow + 1, IndexColumnSubTotal].SetFontSize(20);
}
protected Workbook createWorkbook()
{
Workbook workbook = new Workbook();
workbook.Sheets.Add(SheetType.Worksheet);
Worksheet worksheet = workbook.ActiveWorksheet;
worksheet.Name = "KET " + forDate.ToString("dd-MM-yyyy");
List <TaskForH> tasks = getTasksForHelpers(forDate);
prepareDocument(worksheet);
int currentRow = 1;
CellBorder border = new CellBorder(CellBorderStyle.Thin, tcBlack);
CellBorders borders = new CellBorders(border, border, border, border, null, null, null, null);
double fontSize = 12;
foreach (TaskForH curTask in tasks)
{
worksheet.Cells[currentRow, 0].SetValue(curTask.Count.ToString());
worksheet.Cells[currentRow, 0].SetFontSize(fontSize);
worksheet.Cells[currentRow, 0].SetBorders(borders);
worksheet.Cells[currentRow, 1].SetValue(curTask.Customer);
worksheet.Cells[currentRow, 1].SetFormat(new CellValueFormat("@"));
worksheet.Cells[currentRow, 1].SetFontSize(fontSize);
worksheet.Cells[currentRow, 1].SetBorders(borders);
worksheet.Cells[currentRow, 2].SetValue(curTask.FromPlace);
worksheet.Cells[currentRow, 2].SetFormat(new CellValueFormat("@"));
worksheet.Cells[currentRow, 2].SetFontSize(fontSize);
worksheet.Cells[currentRow, 2].SetBorders(borders);
worksheet.Cells[currentRow, 3].SetValue(curTask.FromTime + " - " + curTask.ToTime);
worksheet.Cells[currentRow, 3].SetFontSize(fontSize);
worksheet.Cells[currentRow, 3].SetBorders(borders);
worksheet.Cells[currentRow, 4].SetValue(curTask.Comments);
worksheet.Cells[currentRow, 4].SetIsWrapped(true);
worksheet.Cells[currentRow, 4].SetFontSize(fontSize);
worksheet.Cells[currentRow, 4].SetBorders(borders);
currentRow++;
}
for (int i = 0; i < worksheet.Columns.Count; i++)
{
worksheet.Columns[i].AutoFitWidth();
}
for (int i = 0; i < worksheet.Columns.Count; i++)
{
if (i == 4)
{
worksheet.Columns[i].SetWidth(new ColumnWidth(500, true));
}
ColumnSelection columnSelection = worksheet.Columns[i];
ColumnWidth columnWidth = columnSelection.GetWidth().Value;
double curColWidth = columnWidth.Value + 10;
if (curColWidth > 2000)
{
curColWidth = 2000;
}
columnSelection.SetWidth(new ColumnWidth(curColWidth, columnWidth.IsCustom));
}
ColumnSelection columnSelection4 = worksheet.Columns[4];
ColumnWidth columnWidth4 = columnSelection4.GetWidth().Value;
double curColWidth4 = columnWidth4.Value + 10;
if (curColWidth4 > 2000)
{
curColWidth4 = 2000;
}
columnSelection4.SetWidth(new ColumnWidth(curColWidth4, columnWidth4.IsCustom));
return(workbook);
}
private static void setCell(CellBorder cell, LineWeight lw, bool visible)
{
//cell.Overrides = GridProperties.Visibility | GridProperties.LineWeight;
cell.LineWeight = lw;
cell.IsVisible = visible;
}
private static string smethod_1(ebexcel.ExcelCell excelCell_0)
{
string str = "";
StringBuilder builder = new StringBuilder();
if (!excelCell_0.Style.IsDefault)
{
builder.Append(" style=\"");
string name = "";
name = excelCell_0.Style.Font.Name;
if ((name != "") && (name != "宋体"))
{
builder.Append("FONT-FAMILY: " + name + ";");
}
int size = excelCell_0.Style.Font.Size;
if ((size / 20) != 12)
{
builder.Append("FONT-SIZE: " + Convert.ToString(size / 20) + "px;");
}
Color patternForegroundColor = excelCell_0.Style.Font.Color;
if ((patternForegroundColor != Color.Black) && (ColorTranslator.ToHtml(patternForegroundColor) != "#000000"))
{
builder.Append("COLOR: " + ColorTranslator.ToHtml(patternForegroundColor) + ";");
}
patternForegroundColor = excelCell_0.Style.FillPattern.PatternForegroundColor;
if (((patternForegroundColor != Color.White) && (ColorTranslator.ToHtml(patternForegroundColor) != "#ffffff")) && (ColorTranslator.ToHtml(patternForegroundColor) != "#000000"))
{
builder.Append("BACKGROUND-COLOR: " + ColorTranslator.ToHtml(patternForegroundColor) + ";");
}
if (excelCell_0.Style.Font.Weight > 400)
{
builder.Append("FONT-WEIGHT: bold;");
}
if (excelCell_0.Style.Font.Italic)
{
builder.Append("FONT-STYLE: italic;");
}
if (excelCell_0.Style.Font.UnderlineStyle != UnderlineStyle.None)
{
builder.Append("TEXT-DECORATION: underline;");
}
if (excelCell_0.Style.HorizontalAlignment != HorizontalAlignmentStyle.Right)
{
if (excelCell_0.Style.HorizontalAlignment == HorizontalAlignmentStyle.Center)
{
builder.Append("TEXT-ALIGN: center;");
}
}
else
{
builder.Append("TEXT-ALIGN: right;");
}
CellBorder border = excelCell_0.Style.Borders[IndividualBorder.Right];
if (border.LineStyle != LineStyle.None)
{
builder.Append("BORDER-RIGHT: ");
builder.Append(ColorTranslator.ToHtml(border.LineColor));
builder.Append(smethod_2(border.LineStyle) + ";");
}
border = excelCell_0.Style.Borders[IndividualBorder.Bottom];
if (border.LineStyle != LineStyle.None)
{
builder.Append("BORDER-BOTTOM: ");
builder.Append(ColorTranslator.ToHtml(border.LineColor));
builder.Append(smethod_2(border.LineStyle) + ";");
}
border = excelCell_0.Style.Borders[IndividualBorder.Left];
if (border.LineStyle != LineStyle.None)
{
builder.Append("BORDER-LEFT: ");
builder.Append(ColorTranslator.ToHtml(border.LineColor));
builder.Append(smethod_2(border.LineStyle) + ";");
}
border = excelCell_0.Style.Borders[IndividualBorder.Top];
if (border.LineStyle != LineStyle.None)
{
builder.Append("BORDER-TOP: ");
builder.Append(ColorTranslator.ToHtml(border.LineColor));
builder.Append(smethod_2(border.LineStyle) + ";");
}
builder.Append("\"");
}
str = builder.ToString();
if (str == " style=\"\"")
{
str = "";
}
return(str);
}
private static void SetCell([NotNull] CellBorder cell, LineWeight lw, bool visible)
{
cell.LineWeight = lw;
cell.IsVisible = visible;
}
private static Brush ConvertToBrush(CellBorder cellBorder)
{
switch (cellBorder.LineStyle) {
case LineStyle.SlantDashDot:
case LineStyle.MediumDashDot:
case LineStyle.DashDot:
case LineStyle.MediumDashDotDot:
case LineStyle.DashDotDot:
case LineStyle.MediumDashed:
case LineStyle.Dashed:
case LineStyle.Dotted:
case LineStyle.Double:
case LineStyle.None:
return new SolidColorBrush(Colors.Transparent);
case LineStyle.Hair:
case LineStyle.Medium:
case LineStyle.Thick:
case LineStyle.Thin:
default:
return new SolidColorBrush(cellBorder.LineColor.ConvertToNewColor());
}
}
protected Workbook createWorkbook()
{
Workbook workbook = new Workbook();
workbook.Sheets.Add(SheetType.Worksheet);
Worksheet worksheet = workbook.ActiveWorksheet;
worksheet.Name = "OTE RT";
List <TaskB> tasks = new List <TaskB>();
try {
string recFilter = gridMain.MasterTableView.FilterExpression;
GridSortExpressionCollection gridSortExxpressions = gridMain.MasterTableView.SortExpressions;
TasksController cont = new TasksController();
int tasksCount = cont.CountAllTasks(recFilter);
tasks = cont.GetAllTasks(0, tasksCount, recFilter, gridSortExxpressions);
}
catch (Exception) { }
prepareDocument(worksheet);
int currentRow = 1;
CellBorder border = new CellBorder(CellBorderStyle.Thin, tcBlack);
CellBorders borders = new CellBorders(border, border, border, border, null, null, null, null);
double fontSize = 12;
foreach (TaskB curTask in tasks)
{
worksheet.Cells[currentRow, 0].SetValue(curTask.ID.ToString());
worksheet.Cells[currentRow, 0].SetFontSize(fontSize);
worksheet.Cells[currentRow, 0].SetBorders(borders);
worksheet.Cells[currentRow, 1].SetValue(curTask.RegNo);
worksheet.Cells[currentRow, 1].SetFormat(new CellValueFormat("@"));
worksheet.Cells[currentRow, 1].SetFontSize(fontSize);
worksheet.Cells[currentRow, 1].SetBorders(borders);
worksheet.Cells[currentRow, 2].SetValue(curTask.OrderDate);
worksheet.Cells[currentRow, 2].SetFormat(new CellValueFormat(dateFormat));
worksheet.Cells[currentRow, 2].SetFontSize(fontSize);
worksheet.Cells[currentRow, 2].SetBorders(borders);
worksheet.Cells[currentRow, 3].SetValue(curTask.Customer.NameGR);
worksheet.Cells[currentRow, 3].SetFontSize(fontSize);
worksheet.Cells[currentRow, 3].SetBorders(borders);
worksheet.Cells[currentRow, 4].SetValue(curTask.Job.Name);
worksheet.Cells[currentRow, 4].SetFontSize(fontSize);
worksheet.Cells[currentRow, 4].SetBorders(borders);
string distance = curTask.Distance.Position1 + " - " + curTask.Distance.Position2 + " (" + curTask.Distance.KM.ToString() + ")";
worksheet.Cells[currentRow, 5].SetValue(distance);
worksheet.Cells[currentRow, 5].SetFontSize(fontSize);
worksheet.Cells[currentRow, 5].SetBorders(borders);
worksheet.Cells[currentRow, 6].SetValue(curTask.DateTimeStartOrder.GetValueOrDefault());
worksheet.Cells[currentRow, 6].SetFormat(new CellValueFormat(dateFormat));
worksheet.Cells[currentRow, 6].SetFontSize(fontSize);
worksheet.Cells[currentRow, 6].SetBorders(borders);
worksheet.Cells[currentRow, 7].SetValue(curTask.DateTimeEndOrder.GetValueOrDefault());
worksheet.Cells[currentRow, 7].SetFormat(new CellValueFormat(dateFormat));
worksheet.Cells[currentRow, 7].SetFontSize(fontSize);
worksheet.Cells[currentRow, 7].SetBorders(borders);
worksheet.Cells[currentRow, 8].SetValue(curTask.DateTimeDurationOrder);
worksheet.Cells[currentRow, 8].SetFontSize(fontSize);
worksheet.Cells[currentRow, 8].SetBorders(borders);
worksheet.Cells[currentRow, 9].SetValue(double.Parse(curTask.AddedCharges.GetValueOrDefault().ToString()));
worksheet.Cells[currentRow, 9].SetFormat(new CellValueFormat(currencyFormat));
worksheet.Cells[currentRow, 9].SetFontSize(fontSize);
worksheet.Cells[currentRow, 9].SetBorders(borders);
worksheet.Cells[currentRow, 10].SetValue(double.Parse(curTask.CostCalculated.GetValueOrDefault().ToString()));
worksheet.Cells[currentRow, 10].SetFormat(new CellValueFormat(currencyFormat));
worksheet.Cells[currentRow, 10].SetFontSize(fontSize);
worksheet.Cells[currentRow, 10].SetBorders(borders);
if (curTask.DateTimeStartActual.GetValueOrDefault().Year > 2000)
{
worksheet.Cells[currentRow, 11].SetValue(curTask.DateTimeStartActual.GetValueOrDefault());
}
else
{
worksheet.Cells[currentRow, 11].SetValue("");
}
worksheet.Cells[currentRow, 11].SetFormat(new CellValueFormat(dateFormat));
worksheet.Cells[currentRow, 11].SetFontSize(fontSize);
worksheet.Cells[currentRow, 11].SetBorders(borders);
if (curTask.DateTimeEndActual.GetValueOrDefault().Year > 2000)
{
worksheet.Cells[currentRow, 12].SetValue(curTask.DateTimeEndActual.GetValueOrDefault());
}
else
{
worksheet.Cells[currentRow, 12].SetValue("");
}
worksheet.Cells[currentRow, 12].SetFormat(new CellValueFormat(dateFormat));
worksheet.Cells[currentRow, 12].SetFontSize(fontSize);
worksheet.Cells[currentRow, 12].SetBorders(borders);
if (curTask.IsCanceled == true)
{
worksheet.Cells[currentRow, 13].SetValue(0);
}
else
{
worksheet.Cells[currentRow, 13].SetValue(curTask.DateTimeDurationActual.GetValueOrDefault());
}
worksheet.Cells[currentRow, 13].SetFontSize(fontSize);
worksheet.Cells[currentRow, 13].SetBorders(borders);
if (curTask.PaymentDateOrder.GetValueOrDefault().Year > 2000)
{
worksheet.Cells[currentRow, 14].SetValue(curTask.PaymentDateOrder.GetValueOrDefault());
}
else
{
worksheet.Cells[currentRow, 14].SetValue("");
}
worksheet.Cells[currentRow, 14].SetFormat(new CellValueFormat(dateFormat));
worksheet.Cells[currentRow, 14].SetFontSize(fontSize);
worksheet.Cells[currentRow, 14].SetBorders(borders);
worksheet.Cells[currentRow, 15].SetValue(double.Parse(curTask.CostActual.GetValueOrDefault().ToString()));
worksheet.Cells[currentRow, 15].SetFormat(new CellValueFormat(currencyFormat));
worksheet.Cells[currentRow, 15].SetFontSize(fontSize);
worksheet.Cells[currentRow, 15].SetBorders(borders);
if (curTask.PaymentDateCalculated.GetValueOrDefault().Year > 2000)
{
worksheet.Cells[currentRow, 16].SetValue(curTask.PaymentDateCalculated.GetValueOrDefault());
}
else
{
worksheet.Cells[currentRow, 16].SetValue("");
}
worksheet.Cells[currentRow, 16].SetFormat(new CellValueFormat(dateFormat));
worksheet.Cells[currentRow, 16].SetFontSize(fontSize);
worksheet.Cells[currentRow, 16].SetBorders(borders);
//if (curTask.PaymentDateActual.GetValueOrDefault().Year > 2000) {
// worksheet.Cells[currentRow, 17].SetValue(curTask.PaymentDateActual.GetValueOrDefault());
//} else {
// worksheet.Cells[currentRow, 17].SetValue("");
//}
//worksheet.Cells[currentRow,17].SetFormat(new CellValueFormat(dateFormat));
//worksheet.Cells[currentRow,17].SetFontSize(fontSize);
//worksheet.Cells[currentRow,17].SetBorders(borders);
string isCanc = "ΟΧΙ";
if (curTask.IsCanceled == true)
{
isCanc = "ΝΑΙ";
}
worksheet.Cells[currentRow, 17].SetValue(isCanc);
worksheet.Cells[currentRow, 17].SetFontSize(fontSize);
worksheet.Cells[currentRow, 17].SetBorders(borders);
worksheet.Cells[currentRow, 18].SetValue(curTask.Comments);
worksheet.Cells[currentRow, 18].SetIsWrapped(true);
worksheet.Cells[currentRow, 18].SetFontSize(fontSize);
worksheet.Cells[currentRow, 18].SetBorders(borders);
worksheet.Cells[currentRow, 19].SetValue(curTask.InvoceComments);
worksheet.Cells[currentRow, 19].SetIsWrapped(true);
worksheet.Cells[currentRow, 19].SetFontSize(fontSize);
worksheet.Cells[currentRow, 19].SetBorders(borders);
worksheet.Cells[currentRow, 20].SetValue(curTask.EnteredByUser);
worksheet.Cells[currentRow, 20].SetFontSize(fontSize);
worksheet.Cells[currentRow, 20].SetBorders(borders);
worksheet.Cells[currentRow, 21].SetValue(curTask.DateStamp);
worksheet.Cells[currentRow, 21].SetFormat(new CellValueFormat(dateFormat));
worksheet.Cells[currentRow, 21].SetFontSize(fontSize);
worksheet.Cells[currentRow, 21].SetBorders(borders);
currentRow++;
}
for (int i = 0; i < worksheet.Columns.Count; i++)
{
worksheet.Columns[i].AutoFitWidth();
}
for (int i = 0; i < worksheet.Columns.Count; i++)
{
if (i == 18 || i == 19)
{
worksheet.Columns[i].SetWidth(new ColumnWidth(300, true));
}
ColumnSelection columnSelection = worksheet.Columns[i];
ColumnWidth columnWidth = columnSelection.GetWidth().Value;
double curColWidth = columnWidth.Value + 10;
if (curColWidth > 2000)
{
curColWidth = 2000;
}
columnSelection.SetWidth(new ColumnWidth(curColWidth, columnWidth.IsCustom));
}
ColumnSelection columnSelection4 = worksheet.Columns[4];
ColumnWidth columnWidth4 = columnSelection4.GetWidth().Value;
double curColWidth4 = columnWidth4.Value + 10;
if (curColWidth4 > 2000)
{
curColWidth4 = 2000;
}
columnSelection4.SetWidth(new ColumnWidth(curColWidth4, columnWidth4.IsCustom));
return(workbook);
}
private void LoadStyles(string path)
{
var styles = ReadFile <CT_Stylesheet>(path);
indexedColors = new List <Color>();
if (styles.colors != null && styles.colors.indexedColors != null)
{
foreach (var color in styles.colors.indexedColors)
{
indexedColors.Add(new Color(color.rgb));
}
}
borders = new List <CellBorder>();
if (styles.borders != null && styles.borders.border != null)
{
foreach (var border in styles.borders.border)
{
CellBorder b = new CellBorder
{
Bottom = ConvertBorderEdge(border.bottom),
Right = ConvertBorderEdge(border.right),
Left = ConvertBorderEdge(border.left),
Top = ConvertBorderEdge(border.top),
Diagonal = ConvertBorderEdge(border.diagonal),
DiagonalDown = border.diagonalDown,
DiagonalUp = border.diagonalUp,
Vertical = ConvertBorderEdge(border.vertical),
Horizontal = ConvertBorderEdge(border.horizontal),
Outline = border.outline
};
borders.Add(b);
}
}
fills = new List <CellFill>();
if (styles.fills != null && styles.fills.fill != null)
{
foreach (var fill in styles.fills.fill)
{
CT_PatternFill pf;
CellFill f = new CellFill();
if (Util.TryCast(fill.Item, out pf))
{
f.Background = ConvertColor(pf.bgColor);
f.Foreground = ConvertColor(pf.fgColor);
f.Pattern = (FillPattern)pf.patternType;
}
fills.Add(f);
}
}
fonts = new List <CellFont>();
if (styles.fonts != null && styles.fonts.font != null)
{
foreach (var font in styles.fonts.font)
{
CellFont f = null;
if (font.ItemsElementName != null && font.Items != null)
{
f = new CellFont();
for (int i = 0; i < font.ItemsElementName.Length; i++)
{
var item = font.Items[i];
CT_BooleanProperty bp;
switch (font.ItemsElementName[i])
{
case ItemsChoiceType3.name:
CT_FontName name;
if (Util.TryCast(item, out name))
{
f.Name = name.val;
}
break;
case ItemsChoiceType3.sz:
CT_FontSize size;
if (Util.TryCast(item, out size))
{
f.Size = size.val;
}
break;
case ItemsChoiceType3.b:
if (Util.TryCast(item, out bp))
{
f.Bold = bp.val;
}
break;
case ItemsChoiceType3.strike:
if (Util.TryCast(item, out bp))
{
f.Strike = bp.val;
}
break;
case ItemsChoiceType3.shadow:
if (Util.TryCast(item, out bp))
{
f.Shadow = bp.val;
}
break;
case ItemsChoiceType3.outline:
if (Util.TryCast(item, out bp))
{
f.Outline = bp.val;
}
break;
case ItemsChoiceType3.i:
if (Util.TryCast(item, out bp))
{
f.Italic = bp.val;
}
break;
case ItemsChoiceType3.u:
CT_UnderlineProperty up;
if (Util.TryCast(item, out up))
{
f.Underline = (FontUnderline)up.val;
}
break;
case ItemsChoiceType3.vertAlign:
CT_VerticalAlignFontProperty vafp;
if (Util.TryCast(item, out vafp))
{
f.Script = (FontScript)vafp.val;
}
break;
case ItemsChoiceType3.color:
CT_Color1 color;
if (Util.TryCast(item, out color))
{
f.Color = ConvertColor(color);
}
break;
}
}
}
fonts.Add(f);
}
}
if (fonts.Count > 0)
{
workbook.DefaultFont = fonts[0];
}
numFmts = new Dictionary <uint, string>();
if (styles.numFmts != null && styles.numFmts.numFmt != null)
{
foreach (var nfmt in styles.numFmts.numFmt)
{
numFmts[nfmt.numFmtId] = nfmt.formatCode;
}
}
xfs = new List <CellStyle>();
if (styles.cellXfs != null && styles.cellXfs.xf != null)
{
foreach (var xf in styles.cellXfs.xf)
{
var entry = new CellStyle();
if (xf.applyBorderSpecified && xf.applyBorder && xf.borderIdSpecified)
{
entry.Border = GetBorder((int)xf.borderId);
}
if (xf.applyFillSpecified && xf.applyFill && xf.fillIdSpecified)
{
entry.Fill = GetFill((int)xf.fillId);
}
if (xf.applyFontSpecified && xf.applyFont && xf.fontIdSpecified)
{
entry.Font = GetFont((int)xf.fontId);
}
if (xf.applyNumberFormatSpecified && xf.applyNumberFormat && xf.numFmtIdSpecified)
{
entry.Format = GetNumFmt(xf.numFmtId);
}
if (xf.applyAlignmentSpecified && xf.applyAlignment && xf.alignment != null)
{
entry.Alignment = ConvertAlignment(xf.alignment);
}
xfs.Add(entry);
}
}
dxfs = new List <CellStyle>();
if (styles.dxfs != null && styles.dxfs.dxf != null)
{
foreach (CT_Dxf dxf in styles.dxfs.dxf)
{
var entry = new CellStyle();
if (dxf.font != null)
{
entry.Font = ConvertDxfFont(dxf.font);
}
if (dxf.numFmt != null)
{
entry.format = GetNumFmt(dxf.numFmt.numFmtId);
}
if (dxf.fill != null)
{
entry.fill = ConvertDxfCellFill(dxf.fill);
}
if (dxf.alignment != null)
{
entry.alignment = ConvertAlignment(dxf.alignment);
}
if (dxf.border != null)
{
entry.border = ConvertDxfBorder(dxf.border);
}
dxfs.Add(entry);
}
}
}
private static void SetCell([NotNull] CellBorder cell, LineWeight lw, bool visible)
{
// cell.Overrides = GridProperties.Visibility | GridProperties.LineWeight;
cell.LineWeight = lw;
cell.IsVisible = visible;
}
static public Border GetBorder(this CellBorder cellFontAttribute)
{
Border retBorder = new Border();
if (cellFontAttribute.LeftBorder)
{
retBorder.LeftBorder = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
retBorder.LeftBorder.Color = new Color()
{
Indexed = 64U
};
}
if (cellFontAttribute.RightBorder)
{
retBorder.RightBorder = new RightBorder()
{
Style = BorderStyleValues.Thin
};
retBorder.RightBorder.Color = new Color()
{
Indexed = 64U
};
}
if (cellFontAttribute.TopBorder)
{
retBorder.TopBorder = new TopBorder()
{
Style = BorderStyleValues.Thin
};
retBorder.TopBorder.Color = new Color()
{
Indexed = 64U
};
}
if (cellFontAttribute.BottomBorder)
{
retBorder.BottomBorder = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
retBorder.BottomBorder.Color = new Color()
{
Indexed = 64U
};
}
if (cellFontAttribute.DiagonalBorder)
{
retBorder.DiagonalBorder = new DiagonalBorder()
{
Style = BorderStyleValues.Thin
};
retBorder.DiagonalBorder.Color = new Color()
{
Indexed = 64U
};
}
return(retBorder);
}
private void LoadStyles(string path)
{
var styles = ReadFile<CT_Stylesheet>(path);
indexedColors = new List<Color>();
if (styles.colors != null && styles.colors.indexedColors != null)
foreach (var color in styles.colors.indexedColors)
indexedColors.Add(new Color(color.rgb));
borders = new List<CellBorder>();
if (styles.borders!=null && styles.borders.border!=null)
foreach (var border in styles.borders.border)
{
CellBorder b = new CellBorder
{
Bottom = ConvertBorderEdge(border.bottom),
Right = ConvertBorderEdge(border.right),
Left = ConvertBorderEdge(border.left),
Top = ConvertBorderEdge(border.top),
Diagonal = ConvertBorderEdge(border.diagonal),
DiagonalDown = border.diagonalDown,
DiagonalUp = border.diagonalUp,
Vertical = ConvertBorderEdge(border.vertical),
Horizontal = ConvertBorderEdge(border.horizontal),
Outline = border.outline
};
borders.Add(b);
}
fills = new List<CellFill>();
if (styles.fills != null && styles.fills.fill != null)
foreach (var fill in styles.fills.fill)
{
CT_PatternFill pf;
CellFill f = new CellFill();
if (Util.TryCast(fill.Item, out pf))
{
f.Background = ConvertColor(pf.bgColor);
f.Foreground = ConvertColor(pf.fgColor);
f.Pattern = (FillPattern)pf.patternType;
}
fills.Add(f);
}
fonts = new List<CellFont>();
if (styles.fonts!=null && styles.fonts.font!=null)
foreach (var font in styles.fonts.font)
{
CellFont f = null;
if (font.ItemsElementName != null && font.Items != null)
{
f = new CellFont();
for (int i = 0; i < font.ItemsElementName.Length; i++)
{
var item = font.Items[i];
CT_BooleanProperty bp;
switch (font.ItemsElementName[i])
{
case ItemsChoiceType3.name:
CT_FontName name;
if (Util.TryCast(item, out name))
f.Name = name.val;
break;
case ItemsChoiceType3.sz:
CT_FontSize size;
if (Util.TryCast(item, out size))
f.Size = size.val;
break;
case ItemsChoiceType3.b:
if (Util.TryCast(item, out bp))
f.Bold = bp.val;
break;
case ItemsChoiceType3.strike:
if (Util.TryCast(item, out bp))
f.Strike = bp.val;
break;
case ItemsChoiceType3.shadow:
if (Util.TryCast(item, out bp))
f.Shadow = bp.val;
break;
case ItemsChoiceType3.outline:
if (Util.TryCast(item, out bp))
f.Outline = bp.val;
break;
case ItemsChoiceType3.i:
if (Util.TryCast(item, out bp))
f.Italic = bp.val;
break;
case ItemsChoiceType3.u:
CT_UnderlineProperty up;
if (Util.TryCast(item, out up))
f.Underline = (FontUnderline)up.val;
break;
case ItemsChoiceType3.vertAlign:
CT_VerticalAlignFontProperty vafp;
if (Util.TryCast(item, out vafp))
f.Script = (FontScript)vafp.val;
break;
case ItemsChoiceType3.color:
CT_Color1 color;
if (Util.TryCast(item, out color))
f.Color = ConvertColor(color);
break;
}
}
}
fonts.Add(f);
}
if (fonts.Count > 0)
workbook.DefaultFont = fonts[0];
numFmts = new Dictionary<uint, string>();
if (styles.numFmts!=null && styles.numFmts.numFmt!=null)
foreach (var nfmt in styles.numFmts.numFmt)
{
numFmts[nfmt.numFmtId] = nfmt.formatCode;
}
xfs = new List<CellStyle>();
if (styles.cellXfs != null && styles.cellXfs.xf!=null)
foreach (var xf in styles.cellXfs.xf)
{
var entry = new CellStyle();
if (xf.applyBorderSpecified && xf.applyBorder && xf.borderIdSpecified)
entry.Border = GetBorder((int)xf.borderId);
if (xf.applyFillSpecified && xf.applyFill && xf.fillIdSpecified)
entry.Fill = GetFill((int)xf.fillId);
if (xf.applyFontSpecified && xf.applyFont && xf.fontIdSpecified)
entry.Font = GetFont((int)xf.fontId);
if (xf.applyNumberFormatSpecified && xf.applyNumberFormat && xf.numFmtIdSpecified)
entry.Format = GetNumFmt(xf.numFmtId);
if (xf.applyAlignmentSpecified && xf.applyAlignment && xf.alignment != null)
entry.Alignment = ConvertAlignment(xf.alignment);
xfs.Add(entry);
}
dxfs = new List<CellStyle>();
if (styles.dxfs != null && styles.dxfs.dxf != null)
{
foreach (CT_Dxf dxf in styles.dxfs.dxf)
{
var entry = new CellStyle();
if (dxf.font != null)
entry.Font = ConvertDxfFont(dxf.font);
if (dxf.numFmt != null)
entry.format = GetNumFmt(dxf.numFmt.numFmtId);
if (dxf.fill != null)
entry.fill = ConvertDxfCellFill(dxf.fill);
if (dxf.alignment != null)
entry.alignment = ConvertAlignment(dxf.alignment);
if (dxf.border != null)
entry.border = ConvertDxfBorder(dxf.border);
dxfs.Add(entry);
}
}
}
protected void prepareDocument(Worksheet worksheet)
{
PatternFill pfGreen = new PatternFill(PatternType.Solid, System.Windows.Media.Color.FromArgb(255, 153, 204, 0), System.Windows.Media.Colors.Transparent);
PatternFill pfOrange = new PatternFill(PatternType.Solid, System.Windows.Media.Color.FromArgb(255, 255, 204, 0), System.Windows.Media.Colors.Transparent);
PatternFill pfRed = new PatternFill(PatternType.Solid, System.Windows.Media.Color.FromArgb(255, 255, 0, 0), System.Windows.Media.Colors.Transparent);
PatternFill pfBlue = new PatternFill(PatternType.Solid, System.Windows.Media.Color.FromArgb(255, 0, 0, 255), System.Windows.Media.Colors.Transparent);
CellBorder border = new CellBorder(CellBorderStyle.Thin, tcBlack);
CellBorders borders = new CellBorders(border, border, border, border, null, null, null, null);
double fontSize = 12;
worksheet.Cells[0, 0].SetValue("A/A");
worksheet.Cells[0, 0].SetFontSize(fontSize);
worksheet.Cells[0, 0].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 0].SetIsBold(true);
worksheet.Cells[0, 0].SetFill(pfGreen);
worksheet.Cells[0, 0].SetBorders(borders);
worksheet.Cells[0, 1].SetValue("Αριθμός Πρωτοκόλλου");
worksheet.Cells[0, 1].SetFontSize(fontSize);
worksheet.Cells[0, 1].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 1].SetIsBold(true);
//worksheet.Cells[0, 1].SetIsWrapped(true);
worksheet.Cells[0, 1].SetFill(pfGreen);
worksheet.Cells[0, 1].SetBorders(borders);
worksheet.Cells[0, 2].SetValue("Ημερομηνία Παραγγελίας");
worksheet.Cells[0, 2].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 2].SetFontSize(fontSize);
worksheet.Cells[0, 2].SetIsBold(true);
//worksheet.Cells[0, 2].SetIsWrapped(true);
worksheet.Cells[0, 2].SetFill(pfOrange);
worksheet.Cells[0, 2].SetBorders(borders);
worksheet.Cells[0, 3].SetValue("Πελάτης");
worksheet.Cells[0, 3].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 3].SetFontSize(fontSize);
worksheet.Cells[0, 3].SetIsBold(true);
worksheet.Cells[0, 3].SetFill(pfGreen);
worksheet.Cells[0, 3].SetBorders(borders);
worksheet.Cells[0, 4].SetValue("Κατηγορία Έργου");
worksheet.Cells[0, 4].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 4].SetFontSize(fontSize);
worksheet.Cells[0, 4].SetIsBold(true);
worksheet.Cells[0, 4].SetFill(pfGreen);
worksheet.Cells[0, 4].SetBorders(borders);
worksheet.Cells[0, 5].SetValue("Διαδρομή (km)");
worksheet.Cells[0, 5].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 5].SetFontSize(fontSize);
worksheet.Cells[0, 5].SetIsBold(true);
worksheet.Cells[0, 5].SetFill(pfGreen);
worksheet.Cells[0, 5].SetBorders(borders);
worksheet.Cells[0, 6].SetValue("Προγραμματισμένη Ημερομηνία Έναρξης");
worksheet.Cells[0, 6].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 6].SetFontSize(fontSize);
worksheet.Cells[0, 6].SetIsBold(true);
//worksheet.Cells[0, 6].SetIsWrapped(true);
worksheet.Cells[0, 6].SetFill(pfOrange);
worksheet.Cells[0, 6].SetBorders(borders);
worksheet.Cells[0, 7].SetValue("Προγραμματισμένη Ημερομηνία Λήξης");
worksheet.Cells[0, 7].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 7].SetFontSize(fontSize);
worksheet.Cells[0, 7].SetIsBold(true);
//worksheet.Cells[0, 7].SetIsWrapped(true);
worksheet.Cells[0, 7].SetFill(pfOrange);
worksheet.Cells[0, 7].SetBorders(borders);
worksheet.Cells[0, 8].SetValue("Προγραμματισμένη Διάρκεια");
worksheet.Cells[0, 8].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 8].SetFontSize(fontSize);
worksheet.Cells[0, 8].SetIsBold(true);
//worksheet.Cells[0, 8].SetIsWrapped(true);
worksheet.Cells[0, 8].SetFill(pfGreen);
worksheet.Cells[0, 8].SetBorders(borders);
worksheet.Cells[0, 9].SetValue("Πρόσθετα Τέλη");
worksheet.Cells[0, 9].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 9].SetFontSize(fontSize);
worksheet.Cells[0, 9].SetIsBold(true);
//worksheet.Cells[0, 9].SetIsWrapped(true);
worksheet.Cells[0, 9].SetForeColor(tcWhite);
worksheet.Cells[0, 9].SetFill(pfRed);
worksheet.Cells[0, 9].SetBorders(borders);
worksheet.Cells[0, 10].SetValue("Προϋπολογιζόμενο Κόστος");
worksheet.Cells[0, 10].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 10].SetFontSize(fontSize);
worksheet.Cells[0, 10].SetIsBold(true);
//worksheet.Cells[0, 10].SetIsWrapped(true);
worksheet.Cells[0, 10].SetForeColor(tcWhite);
worksheet.Cells[0, 10].SetFill(pfRed);
worksheet.Cells[0, 10].SetBorders(borders);
worksheet.Cells[0, 11].SetValue("Ημερομηνία Υλοποίησης (Έναρξη)");
worksheet.Cells[0, 11].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 11].SetFontSize(fontSize);
worksheet.Cells[0, 11].SetIsBold(true);
//worksheet.Cells[0, 11].SetIsWrapped(true);
worksheet.Cells[0, 11].SetFill(pfOrange);
worksheet.Cells[0, 11].SetBorders(borders);
worksheet.Cells[0, 12].SetValue("Ημερομηνία Υλοποίησης (Λήξη)");
worksheet.Cells[0, 12].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 12].SetFontSize(fontSize);
worksheet.Cells[0, 12].SetIsBold(true);
//worksheet.Cells[0, 12].SetIsWrapped(true);
worksheet.Cells[0, 12].SetFill(pfOrange);
worksheet.Cells[0, 12].SetBorders(borders);
worksheet.Cells[0, 13].SetValue("Τελική Διάρκεια");
worksheet.Cells[0, 13].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 13].SetFontSize(fontSize);
worksheet.Cells[0, 13].SetIsBold(true);
//worksheet.Cells[0, 13].SetIsWrapped(true);
worksheet.Cells[0, 13].SetFill(pfGreen);
worksheet.Cells[0, 13].SetBorders(borders);
worksheet.Cells[0, 14].SetValue("Ημερομηνία Εντολής Τιμολόγησης");
worksheet.Cells[0, 14].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 14].SetFontSize(fontSize);
worksheet.Cells[0, 14].SetIsBold(true);
//worksheet.Cells[0, 14].SetIsWrapped(true);
worksheet.Cells[0, 14].SetFill(pfOrange);
worksheet.Cells[0, 14].SetBorders(borders);
worksheet.Cells[0, 15].SetValue("Ποσό Είσπραξης");
worksheet.Cells[0, 15].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 15].SetFontSize(fontSize);
worksheet.Cells[0, 15].SetIsBold(true);
//worksheet.Cells[0, 15].SetIsWrapped(true);
worksheet.Cells[0, 15].SetForeColor(tcWhite);
worksheet.Cells[0, 15].SetFill(pfRed);
worksheet.Cells[0, 15].SetBorders(borders);
worksheet.Cells[0, 16].SetValue("Προγραμματισμένη Ημερομηνία Είσπραξης");
worksheet.Cells[0, 16].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 16].SetFontSize(fontSize);
worksheet.Cells[0, 16].SetIsBold(true);
//worksheet.Cells[0, 16].SetIsWrapped(true);
worksheet.Cells[0, 16].SetFill(pfOrange);
worksheet.Cells[0, 16].SetBorders(borders);
worksheet.Cells[0, 17].SetValue("Ακυρώθηκε;");
worksheet.Cells[0, 17].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 17].SetFontSize(fontSize);
worksheet.Cells[0, 17].SetIsBold(true);
worksheet.Cells[0, 17].SetFill(pfGreen);
worksheet.Cells[0, 17].SetBorders(borders);
worksheet.Cells[0, 18].SetValue("Παρατηρήσεις");
worksheet.Cells[0, 18].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 18].SetFontSize(fontSize);
worksheet.Cells[0, 18].SetIsBold(true);
worksheet.Cells[0, 18].SetFill(pfGreen);
worksheet.Cells[0, 18].SetBorders(borders);
worksheet.Cells[0, 19].SetValue("Παρατηρήσεις Τιμολογίου");
worksheet.Cells[0, 19].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 19].SetFontSize(fontSize);
worksheet.Cells[0, 19].SetIsBold(true);
worksheet.Cells[0, 19].SetFill(pfGreen);
worksheet.Cells[0, 19].SetBorders(borders);
worksheet.Cells[0, 20].SetValue("Χρήστης");
worksheet.Cells[0, 20].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 20].SetFontSize(fontSize);
worksheet.Cells[0, 20].SetIsBold(true);
worksheet.Cells[0, 20].SetForeColor(tcWhite);
worksheet.Cells[0, 20].SetFill(pfBlue);
worksheet.Cells[0, 20].SetBorders(borders);
worksheet.Cells[0, 21].SetValue("Ημερομηνία Καταχώρησης");
worksheet.Cells[0, 21].SetHorizontalAlignment(RadHorizontalAlignment.Center);
worksheet.Cells[0, 21].SetFontSize(fontSize);
worksheet.Cells[0, 21].SetIsBold(true);
worksheet.Cells[0, 21].SetForeColor(tcWhite);
worksheet.Cells[0, 21].SetFill(pfBlue);
worksheet.Cells[0, 21].SetBorders(borders);
}
public RowData ToRow(uint rowNo)
{
if (rowNo == 0)
{
throw new ArgumentException("rowNo must be greater than 0.");
}
var retRowData = new RowData();
retRowData.Row.RowIndex = rowNo;
var dataRowType = GetType();
var dataCellProperties = dataRowType.GetProperties().Where(prop => prop.IsDefined(typeof(CellDataAttribute), false));
foreach (var dataCell in dataCellProperties)
{
if (dataCell == null)
{
continue;
}
var target = dataCell.GetValue(this);
if (target == null)
{
continue;
}
var dataCellAttr = dataCell.GetCustomAttributes(false).Where(atr => atr is CellDataAttribute).FirstOrDefault() as CellDataAttribute;
if (dataCellAttr == null)
{
continue;
}
var cellColumnName = dataCellAttr.ColumnName;
if (_propertyMappings.ContainsKey(dataCell.Name))
{
cellColumnName = _propertyMappings[dataCell.Name];
}
var cellDataType = ExcelHelper.ResolveCellType(target.GetType());
var cellDataValue = target.ToString();
var cellReference = cellColumnName + rowNo;
var cell = new Cell()
{
CellReference = cellReference
};
cell.CellValue = new CellValue(cellDataValue);
cell.DataType = new EnumValue <CellValues>(cellDataType);
var excelCell = new ExcelCell();
excelCell.Cell = cell;
var cellFillAttr = dataCell.GetCustomAttributes(false).Where(atr => atr is CellFillAttribute).FirstOrDefault() as CellFillAttribute;
var cellBorderAttr = dataCell.GetCustomAttributes(false).Where(atr => atr is CellBorderAttribute).FirstOrDefault() as CellBorderAttribute;
var cellFontAttr = dataCell.GetCustomAttributes(false).Where(atr => atr is CellTextAttribute).FirstOrDefault() as CellTextAttribute;
var cellStyleMappings = _propertyStylMappings.ContainsKey(dataCell.Name) ? _propertyStylMappings[dataCell.Name] : null;
CellFill cellFillMap = null;
CellBorder cellBorderMap = null;
CellText cellFontMap = null;
if (cellStyleMappings != null)
{
cellFillMap = cellStyleMappings.Where(x => x is CellFill).FirstOrDefault() as CellFill;
cellBorderMap = cellStyleMappings.Where(x => x is CellBorder).FirstOrDefault() as CellBorder;
cellFontMap = cellStyleMappings.Where(x => x is CellText).FirstOrDefault() as CellText;
}
if (cellFillMap == null)
{
if (cellFillAttr != null)
{
excelCell.Styles.Add(cellFillAttr.GetFill());
}
}
else
{
excelCell.Styles.Add(cellFillMap.GetFill());
}
if (cellBorderMap == null)
{
if (cellBorderAttr != null)
{
excelCell.Styles.Add(cellBorderAttr.GetBorder());
}
}
else
{
excelCell.Styles.Add(cellBorderMap.GetBorder());
}
if (cellFontMap == null)
{
if (cellFontAttr != null)
{
excelCell.Styles.Add(cellFontAttr.GetFont());
}
}
else
{
excelCell.Styles.Add(cellFontMap.GetFont());
}
retRowData.Cells.Add(excelCell);
}
return(retRowData);
}
private CellBorder ConvertDxfBorder(CT_Border border)
{
CellBorder b = new CellBorder
{
Bottom = ConvertBorderEdge(border.bottom),
Right = ConvertBorderEdge(border.right),
Left = ConvertBorderEdge(border.left),
Top = ConvertBorderEdge(border.top),
Diagonal = ConvertBorderEdge(border.diagonal),
DiagonalDown = border.diagonalDown,
DiagonalUp = border.diagonalUp,
Vertical = ConvertBorderEdge(border.vertical),
Horizontal = ConvertBorderEdge(border.horizontal),
Outline = border.outline
};
return b;
}
