public static void SetCellStyle(CellStyle cellStyle, CellBorderType borderType) { cellStyle.BorderTop = borderType; cellStyle.BorderBottom = borderType; cellStyle.BorderLeft = borderType; cellStyle.BorderRight = borderType; }
public Cell AddCell(object value, int columnIndex, int rowIndex, TextStyle textStyle, short backgroundColor = short.MinValue, CellBorderType borderStyle = CellBorderType.NONE, short borderColor = short.MinValue) { Row row = GetRow(rowIndex); return(AddCell(value, columnIndex, row, textStyle, backgroundColor, borderStyle, borderColor)); }
public static void SetCellStyle(CellStyle cellStyle, CellBorderType borderType, HorizontalAlignment hAlign) { cellStyle.BorderTop = borderType; cellStyle.BorderBottom = borderType; cellStyle.BorderLeft = borderType; cellStyle.BorderRight = borderType; cellStyle.Alignment = hAlign; }
private static void ApplyBorder(Style style, Color color, CellBorderType cellBorderType) { style.Borders[BorderType.TopBorder].Color = color; style.Borders[BorderType.TopBorder].LineStyle = cellBorderType; style.Borders[BorderType.BottomBorder].Color = color; style.Borders[BorderType.BottomBorder].LineStyle = cellBorderType; style.Borders[BorderType.LeftBorder].Color = color; style.Borders[BorderType.LeftBorder].LineStyle = cellBorderType; style.Borders[BorderType.RightBorder].Color = color; style.Borders[BorderType.RightBorder].LineStyle = cellBorderType; }
private void WriteCell(ExcelWorkbook ew, Worksheet ws, int level, int totalLevels, int row, int column, object val, ExcelDataTableFormat format, bool useHeaderFormats, Dictionary <string, Style> styleCollection) { Cell cell = ws.Cells[row, column]; cell.Value = val; bool cellHasLineBreaks = false; if (val != null && val is string) { if (string.IsNullOrWhiteSpace((string)val)) { cell.Value = null; } else { string s = (string)val; s = StringUtil.StripHTML(s); s = s.Replace(" ", " "); s = s.Replace("&NBSP;", " "); s = s.Replace("<br />", "\n"); s = s.Replace("<br>", "\n"); s = s.Replace("", "-"); cell.Value = s; //if (s.IndexOf("<") != -1 && s.IndexOf(">") != -1) //{ //cell.HtmlString = s; // the aspose throws errors on html it doesn't recognize; commenting out for now //} if (s.IndexOf("\n") != -1) { cellHasLineBreaks = true; } } } Style style = null; if (useHeaderFormats) { if (styleCollection.Keys.Contains("L" + level + "_HEADER" + (cellHasLineBreaks ? "_WITHLINEBREAK" : ""))) { style = styleCollection["L" + level + "_HEADER" + (cellHasLineBreaks ? "_WITHLINEBREAK" : "")]; } else { style = new Style(); style.Pattern = BackgroundType.Solid; style.ForegroundColor = format.HeaderBackgroundColor; // the aspost style.foreground color actually sets the background style.Font.Name = format.HeaderFontName; style.Font.Size = format.HeaderFontSize; style.Font.IsBold = format.HeaderFontBold; style.Font.IsItalic = format.HeaderFontItatlic; style.Font.Color = format.HeaderFontColor; style.Font.Underline = format.HeaderFontUnderline ? FontUnderlineType.Single : FontUnderlineType.None; CellBorderType cbt = CellBorderType.None; if (format.HeaderBorderWidth > 0 && format.HeaderBorderWidth < 1.0f) { cbt = format.HeaderBorderDotted ? CellBorderType.Dotted : format.HeaderBorderDashed ? CellBorderType.Dashed : CellBorderType.Hair; } else if (format.HeaderBorderWidth >= 1.0f && format.HeaderBorderWidth < 2.0f) { cbt = format.HeaderBorderDotted ? CellBorderType.Dotted : format.HeaderBorderDashed ? CellBorderType.Dashed : CellBorderType.Thin; } else if (format.HeaderBorderWidth >= 2.0f) { cbt = format.HeaderBorderDotted ? CellBorderType.Dotted : format.HeaderBorderDashed ? CellBorderType.Dashed : CellBorderType.Medium; } if (cbt != CellBorderType.None) { style.SetBorder(BorderType.TopBorder, cbt, format.HeaderBorderColor); style.SetBorder(BorderType.RightBorder, cbt, format.HeaderBorderColor); style.SetBorder(BorderType.BottomBorder, cbt, format.HeaderBorderColor); style.SetBorder(BorderType.LeftBorder, cbt, format.HeaderBorderColor); } styleCollection["L" + level + "_HEADER" + (cellHasLineBreaks ? "_WITHLINEBREAK" : "")] = style; } } else { if (styleCollection.Keys.Contains("L" + level + "_DATA" + (cellHasLineBreaks ? "_WITHLINEBREAK" : ""))) { style = styleCollection["L" + level + "_DATA" + (cellHasLineBreaks ? "_WITHLINEBREAK" : "")]; } else { style = new Style(); style.Pattern = BackgroundType.Solid; style.ForegroundColor = format.BackgroundColor; // the aspost style.foreground color actually sets the background style.Font.Name = format.FontName; style.Font.Size = format.FontSize; style.Font.IsBold = format.FontBold; style.Font.IsItalic = format.FontItatlic; style.Font.Color = format.FontColor; style.Font.Underline = format.FontUnderline ? FontUnderlineType.Single : FontUnderlineType.None; if (format.AllowWrap) { style.IsTextWrapped = cellHasLineBreaks; } CellBorderType cbt = CellBorderType.None; if (format.BorderWidth > 0 && format.HeaderBorderWidth < 1.0f) { cbt = format.BorderDotted ? CellBorderType.Dotted : format.BorderDashed ? CellBorderType.Dashed : CellBorderType.Hair; } else if (format.BorderWidth >= 1.0f && format.HeaderBorderWidth < 2.0f) { cbt = format.BorderDotted ? CellBorderType.Dotted : format.BorderDashed ? CellBorderType.Dashed : CellBorderType.Thin; } else if (format.BorderWidth >= 2.0f) { cbt = format.BorderDotted ? CellBorderType.Dotted : format.BorderDashed ? CellBorderType.Dashed : CellBorderType.Medium; } if (cbt != CellBorderType.None) { style.SetBorder(BorderType.TopBorder, cbt, format.BorderColor); style.SetBorder(BorderType.RightBorder, cbt, format.BorderColor); style.SetBorder(BorderType.BottomBorder, cbt, format.BorderColor); style.SetBorder(BorderType.LeftBorder, cbt, format.BorderColor); } styleCollection["L" + level + "_DATA" + (cellHasLineBreaks ? "_WITHLINEBREAK" : "")] = style; } } cell.SetStyle(style); }
public static void SetCellStyle(CellStyle cellStyle, CellBorderType borderType, HorizontalAlignment hAlign, VerticalAlignment vAlign) { cellStyle.BorderTop = borderType; cellStyle.BorderBottom = borderType; cellStyle.BorderLeft = borderType; cellStyle.BorderRight = borderType; cellStyle.Alignment = hAlign; cellStyle.VerticalAlignment = vAlign; }
public void setBorders(string name, CellBorderType type) { HSSFExcelStyles[name].BorderBottom = type; HSSFExcelStyles[name].BorderLeft = type; HSSFExcelStyles[name].BorderRight = type; HSSFExcelStyles[name].BorderTop = type; }
public void setBorders(string name, bool top, bool bottom, bool left, bool right, CellBorderType type) { if (bottom) HSSFExcelStyles[name].BorderBottom = type; if (left) HSSFExcelStyles[name].BorderLeft = type; if (right) HSSFExcelStyles[name].BorderRight = type; if (top) HSSFExcelStyles[name].BorderTop = type; }
public static CellBorder Get(CellBorderType name) { return(_BORDERS[name]); }
public void AddList <T>(IEnumerable <T> dataList, IEnumerable <DataColumn> columns, CellBorderType borderStyle, short borderColor) { //PropertyInfo[] propertys = typeof (T).GetProperties(); // шапка таблицы Row tableHederRow = NextRow; int index = 0; foreach (DataColumn column in columns) { if (column.ExcelColumWidth >= 0) { Sheet.SetColumnWidth(index, (int)(256 * column.ExcelColumWidth)); } AddCell(column.FieldAlias, index++, tableHederRow, TextStyle.BoldTextAlignCenter, HSSFColor.GREY_25_PERCENT.index, borderStyle, borderColor); } // заполнение данными int listRowIndex = 1; foreach (T cls in dataList) { int indexData = 0; Row dataRow = NextRow; foreach (DataColumn column in columns) { if (column.IsRowNumberColumn) { AddCell(listRowIndex++, indexData++, dataRow, TextStyle.NormalText, HSSFColor.WHITE.index, borderStyle, borderColor); } else { PropertyInfo property = typeof(T).GetProperty(column.FieldName); object value = property.GetValue(cls, null); AddCell(value, indexData++, dataRow, TextStyle.NormalText, HSSFColor.WHITE.index, borderStyle, borderColor); } } } }
public Cell AddCell(Object value, int columnIndex, Row row, TextStyle textStyle, short backgroundColor, CellBorderType borderStyle, short borderColor) { Cell cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex); CellStyle cellStyle = Styles[textStyle]; if (value == null) { cell.SetCellValue(""); } else if (value.GetType() == typeof(DateTime)) { cell.SetCellValue((DateTime)value); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy"); } else if (value.GetType() == typeof(double) || value.GetType() == typeof(float)) { cell.SetCellValue((double)value); DataFormat format = Workbook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("# ### ### ##0.00"); } else if (value.GetType() == typeof(decimal)) { var d = (decimal)value; cell.SetCellValue((double)d); DataFormat format = Workbook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("# ### ### ##0.00"); } else if (value.GetType() == typeof(Int16) || value.GetType() == typeof(Int32) || value.GetType() == typeof(Int64) || value.GetType() == typeof(UInt16) || value.GetType() == typeof(UInt32) || value.GetType() == typeof(UInt64) || value.GetType() == typeof(Byte) || value.GetType() == typeof(SByte)) { cell.SetCellValue((int)value); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); } else if (value.GetType() == typeof(bool)) { cell.SetCellValue((bool)value); } else { cell.SetCellValue(value.ToString()); } if (backgroundColor != short.MinValue) { cellStyle.FillForegroundColor = backgroundColor; cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; } if (borderColor != short.MinValue) { cellStyle.BorderBottom = borderStyle; cellStyle.BorderLeft = borderStyle; cellStyle.BorderRight = borderStyle; cellStyle.BorderTop = borderStyle; cellStyle.BottomBorderColor = borderColor; cellStyle.LeftBorderColor = borderColor; cellStyle.RightBorderColor = borderColor; cellStyle.TopBorderColor = borderColor; } cell.CellStyle = cellStyle; return(cell); }
/// <summary> /// Convert Excel BorderType to Word LineStyle /// </summary> /// <param name="lineType">Excel LineType</param> /// <returns>Word LineStyle</returns> private LineStyle ConvertLineStyle(CellBorderType lineType) { LineStyle wordsLineStyle = LineStyle.None; switch (lineType) { case CellBorderType.DashDot: { wordsLineStyle = LineStyle.DotDash; break; } case CellBorderType.DashDotDot: { wordsLineStyle = LineStyle.DotDotDash; break; } case CellBorderType.Dashed: { wordsLineStyle = LineStyle.DashSmallGap; break; } case CellBorderType.Dotted: { wordsLineStyle = LineStyle.Dot; break; } case CellBorderType.Double: { wordsLineStyle = LineStyle.Double; break; } case CellBorderType.Hair: { wordsLineStyle = LineStyle.Hairline; break; } case CellBorderType.Medium: { wordsLineStyle = LineStyle.Single; break; } case CellBorderType.MediumDashDot: { wordsLineStyle = LineStyle.DotDash; break; } case CellBorderType.MediumDashDotDot: { wordsLineStyle = LineStyle.DotDotDash; break; } case CellBorderType.MediumDashed: { wordsLineStyle = LineStyle.DashSmallGap; break; } case CellBorderType.None: { wordsLineStyle = LineStyle.None; break; } case CellBorderType.SlantedDashDot: { wordsLineStyle = LineStyle.DotDash; break; } case CellBorderType.Thick: { wordsLineStyle = LineStyle.Thick; break; } case CellBorderType.Thin: { wordsLineStyle = LineStyle.Single; break; } default: { wordsLineStyle = LineStyle.None; break; } } return wordsLineStyle; }