/// <summary> /// 设置范围单元格的垂直对齐方式 /// </summary> /// <param name="startCell">开始的单元格名称</param> /// <param name="endCell">结束的单元格名称</param> /// <param name="cellAlignment">对齐方式</param> public void SetCellVerticalAlignment(string startCell, string endCell, ExcelVerticalAlignment cellAlignment) { Range range = GetRange(startCell, endCell); range.Select(); range.VerticalAlignment = cellAlignment; }
/// <summary> /// Get vertical alignment style of a cell /// </summary> /// <returns>Vertical alignment css style</returns> public string GetVerticalAlignmentStyle() { ExcelVerticalAlignment vAlign = Cell.Style.VerticalAlignment; string result = ""; switch (vAlign) { case ExcelVerticalAlignment.Top: result = "top"; break; case ExcelVerticalAlignment.Center: result = "middle"; break; case ExcelVerticalAlignment.Bottom: result = "bottom"; break; default: result = "initial"; break; } return($"vertical-align: {result};"); }
/// <summary> /// 设置单元格的垂直对齐方式 /// </summary> /// <param name="row">单元格的行坐标</param> /// <param name="column">单元格的列坐标</param> /// <param name="cellAlignment">对齐方式</param> public void SetCellVerticalAlignment(int row, int column, ExcelVerticalAlignment cellAlignment) { Range range = GetRange(row, column); range.Select(); range.VerticalAlignment = cellAlignment; }
public ColumnInfo(string name, int width, ExcelHorizontalAlignment horiz_alignment, ExcelVerticalAlignment vert_alignment, bool wraps) { this.Name = name; this.Width = width; this.HorizAlignment = horiz_alignment; this.VerticalAlignment = vert_alignment; this.Wraps = wraps; }
/// <summary> /// Apply the given cell style options to the worksheet for this collection property. /// </summary> /// <param name="horizontalAlignment">The horizontal alignment to be applied to the cells for this property.</param> /// <param name="verticalAlignment">The vertical alignment to be applied to the cells for this property.</param> /// <param name="wrapText">Controls whether text will be wrapped in the cells for this property.</param> /// <param name="bold">Controls whether text will be bold in the cells for this property.</param> /// <param name="backgroundHtmlColor">Controls the background color of the cells for this property. Can be either a hex code ("#00cc00") or an HTML color name ("lime").</param> /// <param name="fontHtmlColor">Controls the font color of the cells for this property. Can be either a hex code ("#00cc00") or an HTML color name ("lime").</param> public SpreadsheetCellStyleAttribute(ExcelHorizontalAlignment horizontalAlignment = ExcelHorizontalAlignment.General, ExcelVerticalAlignment verticalAlignment = ExcelVerticalAlignment.Bottom, bool wrapText = false, bool bold = false, string backgroundHtmlColor = null, string fontHtmlColor = null) { HorizontalAlignment = horizontalAlignment; VerticalAlignment = verticalAlignment; WrapText = wrapText; Bold = bold; BackgroundHtmlColor = backgroundHtmlColor; FontHtmlColor = fontHtmlColor; }
private void AlineacionTexto(string celda, ExcelVerticalAlignment vertical, ExcelHorizontalAlignment horizontal) { try { worksheet.Cells[celda].Style.VerticalAlignment = vertical; worksheet.Cells[celda].Style.HorizontalAlignment = horizontal; } catch (Exception ex) { _log.Error($"Error a aliniar el texto {ex.StackTrace}"); } }
public static void VerticalAlignment(XlsxAlignment.Vertical alignment, ExcelVerticalAlignment expected) { using (var stream = new MemoryStream()) { using (var xlsxWriter = new XlsxWriter(stream)) xlsxWriter.BeginWorksheet("Sheet 1").BeginRow() .Write("Test", XlsxStyle.Default.With(new XlsxAlignment(vertical: alignment))); using (var package = new ExcelPackage(stream)) package.Workbook.Worksheets[0].Cells["A1"].Style.VerticalAlignment.Should().Be(expected); } }
/// <summary> /// Sets the vertical aligment. /// </summary> /// <param name = "grid" > /// The grid. /// </param> /// <param name = "align" > /// The align. /// </param> public void SetVerticalAligment(Grid grid, ExcelVerticalAlignment align) { if (grid?.GetWorksheet() != null && Enum.IsDefined(typeof(ExcelVerticalAlignment), align)) { try { using var range = grid.GetRange(); range.Style.VerticalAlignment = align; } catch (Exception ex) { Fail(ex); } } }
public static VerticalAlignment?ToVerticalAlignment(this ExcelVerticalAlignment verticalAlignment) { switch (verticalAlignment) { case ExcelVerticalAlignment.Top: return(VerticalAlignment.Top); case ExcelVerticalAlignment.Center: return(VerticalAlignment.Middle); case ExcelVerticalAlignment.Bottom: return(VerticalAlignment.Bottom); default: return(null); } }
public static void FormatCell(ExcelWorksheet ws, ref int row, ref int col, object value = null, int plusRow = 0, int plusCol = 0, int?colWidth = null, bool wrapText = false, int size = 12, bool bold = false, bool italic = false, bool VeBorder = true, ExcelBorderStyle viendamnhat = ExcelBorderStyle.Thin, ExcelHorizontalAlignment hAlign = ExcelHorizontalAlignment.Center, ExcelVerticalAlignment vAlign = ExcelVerticalAlignment.Center, string numFormat = null, string congthuc = null) { if (value != null) { ws.Cells[row, col].Value = value; } if (colWidth != null) { ws.Column(col).Width = (int)colWidth; } ws.Cells[row, col].Style.WrapText = wrapText; ws.Cells[row, col].Style.Font.Size = size; ws.Cells[row, col].Style.Font.Bold = bold; ws.Cells[row, col].Style.Font.Italic = italic; ws.Cells[row, col].Style.HorizontalAlignment = hAlign; ws.Cells[row, col].Style.VerticalAlignment = vAlign; if (VeBorder) { ws.Cells[row, col].Style.Border.BorderAround(viendamnhat, Color.Black); } if (numFormat != null) { ws.Cells[row, col].Style.Numberformat.Format = numFormat; } if (congthuc != null) { ws.Cells[row, col].Formula = congthuc; } if (plusCol > 0) { col += plusCol; } if (plusRow > 0) { row += plusRow; } }
protected void PrepareCells(ExcelWorksheet worksheet, ExcelRange cells, Color fontColor, Color backgroundColor, object value, ExcelHorizontalAlignment horizontalAlignment = ExcelHorizontalAlignment.Center, ExcelVerticalAlignment verticalAlignment = ExcelVerticalAlignment.Center, ExcelFillStyle patternType = ExcelFillStyle.Solid) { cells.Merge = true; cells.Style.VerticalAlignment = verticalAlignment; cells.Style.HorizontalAlignment = horizontalAlignment; cells.Style.Font.Bold = true; cells.Style.Font.Color.SetColor(fontColor); // Set background color only if specified - it will remove bounding box around cell if ((backgroundColor != null) && (backgroundColor != Color.Empty)) { cells.Style.Fill.PatternType = patternType; cells.Style.Fill.BackgroundColor.SetColor(backgroundColor); } // Fill right cell if (value is string) // For string add text { cells.Value = value; } else if (value is System.Windows.Controls.Image) // For Image try to insert image - by default it will understand Image as System.Drawing.Image (it is not) { System.Windows.Controls.Image parsed = value as System.Windows.Controls.Image; Image image = parsed.ToDrawingImage(); int rowIndex = cells.Start.Row; int columnIndex = cells.Start.Column; ExcelPicture picture = worksheet.Drawings.AddPicture($"Picture for cell [{ rowIndex };{ columnIndex }]", image); picture.SetPosition( rowIndex - 1, (int)(worksheet.Row(rowIndex).Height / 4), // row height center columnIndex - 1, (int)(worksheet.Column(columnIndex).Width * 3)); // column width center } }
internal ExcelXfs(XmlNamespaceManager nsm, XmlNode topNode, ExcelStyles styles) : base(nsm, topNode) { _styles = styles; _xfID = GetXmlNodeInt("@xfId"); if (_xfID == 0) { isBuildIn = true; //Normal taggen } _numFmtId = GetXmlNodeInt("@numFmtId"); _fontId = GetXmlNodeInt("@fontId"); _fillId = GetXmlNodeInt("@fillId"); _borderId = GetXmlNodeInt("@borderId"); _readingOrder = GetReadingOrder(GetXmlNodeString(readingOrderPath)); _indent = GetXmlNodeInt(indentPath); _shrinkToFit = GetXmlNodeString(shrinkToFitPath) == "1" ? true : false; _verticalAlignment = GetVerticalAlign(GetXmlNodeString(verticalAlignPath)); _horizontalAlignment = GetHorizontalAlign(GetXmlNodeString(horizontalAlignPath)); _wrapText = GetXmlNodeBool(wrapTextPath); _textRotation = GetXmlNodeInt(textRotationPath); _hidden = GetXmlNodeBool(hiddenPath); _locked = GetXmlNodeBool(lockedPath, true); _quotePrefix = GetXmlNodeBool(quotePrefixPath); }
public static void FormatCell_T_Merge(ExcelWorksheet ws, int currRow, int currCol, object value = null, /*int plusRow = 0, int plusCol = 0, */ int?colWidth = null, bool wrapText = true, int size = 12, bool bold = true, bool italic = false, bool merge = true, int?fromRow = null, int?fromCol = null, int?toRow = null, int?toCol = null, int?entireColIndexFormatNum = null, string numFormatEntireCol = null, bool VeBorder = true, ExcelBorderStyle viendamnhat = ExcelBorderStyle.Thin, ExcelHorizontalAlignment hAlign = ExcelHorizontalAlignment.Center, ExcelVerticalAlignment vAlign = ExcelVerticalAlignment.Center ) { FormatCell_T_Merge(ws, ref currRow, ref currCol, value: value, colWidth: colWidth, wrapText: wrapText, size: size, bold: bold, italic: italic, merge: merge, fromRow: fromRow, fromCol: fromCol, toRow: toRow, toCol: toCol, VeBorder: VeBorder, viendamnhat: viendamnhat, hAlign: hAlign, vAlign: vAlign); }
/// <summary> /// format mac dinh word noi dung , align left /// </summary> public static void FormatCell_W(ExcelWorksheet ws, ref int row, ref int col, object value = null, int plusRow = 0, int plusCol = 0, int?colWidth = null, bool wrapText = false, int size = 12, bool Bold = false, bool Italic = false, string numFormat = null, string congthuc = null, bool VeBorder = true, ExcelBorderStyle viendamnhat = ExcelBorderStyle.Thin, ExcelHorizontalAlignment hAlign = ExcelHorizontalAlignment.Left, ExcelVerticalAlignment vAlign = ExcelVerticalAlignment.Center) { FormatCell(ws, ref row, ref col, value: value, plusRow: plusRow, plusCol: plusCol, colWidth: colWidth, wrapText: wrapText, size: size, bold: Bold, italic: Italic, VeBorder: VeBorder, viendamnhat: viendamnhat, hAlign: hAlign, vAlign: vAlign); }
/// <summary> /// Sets the vertical alignment of ExcelColumn /// </summary> /// <param name="column"></param> /// <param name="verticalAlignment"></param> /// <returns></returns> public static ExcelColumn SetVerticalAlignment(this ExcelColumn column, ExcelVerticalAlignment verticalAlignment) { column.Style.SetVerticalAlignment(verticalAlignment); return(column); }
public void changeformat(ExcelWorksheet ws, Font font, string data, int rownum1, int colnum1, int?rownum2 = null, int?colnum2 = null, Color?fontcolor = null, Color?bgcolor = null, bool bold = false, bool merge = true, bool italic = false, bool underline = false, ExcelVerticalAlignment vertical = ExcelVerticalAlignment.Center, ExcelHorizontalAlignment horizontal = ExcelHorizontalAlignment.Center, ExcelBorderStyle borderstyle = ExcelBorderStyle.Thin, int?txtrotate = null) { int cellAmount; int row2 = rownum2.HasValue ? rownum2.Value : rownum1; int col2 = colnum2.HasValue ? colnum2.Value : colnum1; int rotate = txtrotate.HasValue ? txtrotate.Value : 0; try { if (Int32.TryParse(data, out cellAmount)) { ws.Cells[rownum1, colnum1, row2, col2].Value = cellAmount; } else { ws.Cells[rownum1, colnum1, row2, col2].Value = data; } ws.Cells[rownum1, colnum1, row2, col2].Style.TextRotation = rotate; ws.Cells[rownum1, colnum1, row2, col2].Merge = merge; ws.Cells[rownum1, colnum1, row2, col2].Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells[rownum1, colnum1, row2, col2].Style.Fill.BackgroundColor.SetColor(bgcolor.HasValue?bgcolor.Value:Color.White); ws.Cells[rownum1, colnum1, row2, col2].Style.Font.SetFromFont(font); ws.Cells[rownum1, colnum1, row2, col2].Style.Font.Color.SetColor(fontcolor.HasValue?fontcolor.Value: Color.Black); ws.Cells[rownum1, colnum1, row2, col2].Style.Font.Bold = bold; ws.Cells[rownum1, colnum1, row2, col2].Style.Font.Italic = italic; ws.Cells[rownum1, colnum1, row2, col2].Style.Font.UnderLine = underline; ws.Cells[rownum1, colnum1, row2, col2].Style.WrapText = true; ws.Cells[rownum1, colnum1, row2, col2].Style.VerticalAlignment = vertical; ws.Cells[rownum1, colnum1, row2, col2].Style.HorizontalAlignment = horizontal; ws.Cells[rownum1, colnum1, row2, col2].Style.Border.Left.Style = ws.Cells[rownum1, colnum1, row2, col2].Style.Border.Bottom.Style = ws.Cells[rownum1, colnum1, row2, col2].Style.Border.Right.Style = ws.Cells[rownum1, colnum1, row2, col2].Style.Border.Top.Style = borderstyle; } catch (Exception ee) { ws.Cells[rownum1, colnum1, row2, col2].Value = ee.Message; } }
/// <summary> /// Sets the vertical alignment of given range /// </summary> /// <param name="range"></param> /// <param name="verticalAlignment"></param> /// <returns></returns> public static ExcelRangeBase SetVerticalAlignment(this ExcelRangeBase range, ExcelVerticalAlignment verticalAlignment) { range.Style.SetVerticalAlignment(verticalAlignment); return(range); }
/// <summary> /// ko tăng row, ko tăng col, chỉ thực hiện với cell hiện tại /// </summary> public static void FormatCell(ExcelWorksheet ws, int row, int col, object value = null, /*int plusRow = 0, int plusCol = 0, */ int?colWidth = null, bool wrapText = false, int size = 12, bool bold = false, bool italic = false, string numFormat = null, string congthuc = null, bool VeBorder = true, ExcelBorderStyle viendamnhat = ExcelBorderStyle.Thin, ExcelHorizontalAlignment hAlign = ExcelHorizontalAlignment.Center, ExcelVerticalAlignment vAlign = ExcelVerticalAlignment.Center) { FormatCell(ws, ref row, ref col, value: value, //merge default false, wrap false /*int plusRow = 0, int plusCol = 0, */ colWidth: colWidth, wrapText: wrapText, size: size, bold: bold, italic: italic, VeBorder: VeBorder, viendamnhat: viendamnhat, hAlign: hAlign, vAlign: vAlign, numFormat: numFormat, congthuc: congthuc); }
public static void FormatCell_T_Merge(ExcelWorksheet ws, ref int currRow, ref int currCol, object value = null, int plusRow = 0, int plusCol = 0, int?colWidth = null, bool wrapText = true, int size = 12, bool bold = true, bool italic = false, bool merge = true, int?fromRow = null, int?fromCol = null, int?toRow = null, int?toCol = null, bool VeBorder = true, ExcelBorderStyle viendamnhat = ExcelBorderStyle.Thin, ExcelHorizontalAlignment hAlign = ExcelHorizontalAlignment.Center, ExcelVerticalAlignment vAlign = ExcelVerticalAlignment.Center ) { if (colWidth != null) { ws.Column(currCol).Width = (int)colWidth; } ExcelRange Cells = (merge && fromRow != null && fromCol != null && toCol != null && toRow != null) ? ws.Cells[(int)fromRow, (int)fromCol, (int)toRow, (int)toCol] : ws.Cells[currRow, currCol]; if (merge) { Cells.Merge = true; } Cells.Style.WrapText = wrapText; if (value != null) { Cells.Value = value; } Cells.Style.Font.Size = size; if (bold) { Cells.Style.Font.Bold = true; } if (italic) { Cells.Style.Font.Italic = true; } Cells.Style.HorizontalAlignment = hAlign; Cells.Style.VerticalAlignment = vAlign; if (VeBorder) { Cells.Style.Border.BorderAround(viendamnhat, Color.Black); } if (plusCol > 0) { currCol += plusCol; } if (plusRow > 0) { currRow += plusRow; } }
internal ExcelXfs(XmlNamespaceManager nsm, XmlNode topNode, ExcelStyles styles) : base(nsm, topNode) { _styles = styles; _xfID = GetXmlNodeInt("@xfid"); if (_xfID == 0) isBuildIn = true; //Normal taggen _numFmtId = GetXmlNodeInt("@numFmtId"); _fontId = GetXmlNodeInt("@fontId"); _fillId = GetXmlNodeInt("@fillId"); _borderId = GetXmlNodeInt("@borderId"); _readingOrder = GetXmlNode(readingOrderPath) == "1" ? true : false; _verticalAlignment = GetVerticalAlign(GetXmlNode(verticalAlignPath)); _horizontalAlignment = GetHorizontalAlign(GetXmlNode(horizontalAlignPath)); _wrapText = GetXmlNode(wrapTextPath) == "1" ? true : false; //ApplyFont = GetXmlNode("@applyFont") == "1" ? true : false; //ApplyFill = GetXmlNode("@applyFill") == "1" ? true : false; //ApplyBorder = GetXmlNode("@applyBorder") == "1" ? true : false; //ApplyNumberFormat = GetXmlNode("@applyNumberFormat") == "1" ? true : false; //ApplyFill = GetXmlNode("@applyFont") == "1" ? true : false; //ApplyProtection = GetXmlNode("@applyProtection") == "1" ? true : false; //ApplyAlignment = GetXmlNode("@applyAlitgnment") == "1" ? true : false; }
protected ExcelRange InsertString(string value, int colOffset = 0, TextAlign AlignMode = TextAlign.LEFT, float fontSize = 9, bool fontBold = false, ExcelVerticalAlignment VerticalAlign = ExcelVerticalAlignment.Center, int?col = null, int?row = null) { return(InsertValue(value, "string", colOffset, AlignMode, fontSize: fontSize, fontBold: fontBold, VerticalAlign: VerticalAlign, col: col, row: row)); }
private ExcelRange InsertValue(object value, string sType, int offset, TextAlign AlignMode = TextAlign.LEFT, Precision Decimals = Precision.ZERO, NumberTypes Type = NumberTypes.NUMBER, int mergeCols = 1, int?rowsOccuppied = null, float fontSize = 9, bool fontBold = false, ExcelVerticalAlignment VerticalAlign = ExcelVerticalAlignment.Center, int?col = null, int?row = null) { int targetColumn; int targetRow; if (col != null) { targetColumn = (int)col; } else { colIndex += offset; targetColumn = colIndex; } if (row != null) { targetRow = (int)row; } else { targetRow = rowIndex; } if (targetColumn > maxColIndex) { maxColIndex = targetColumn; } if (mergeCols > 1) { if (targetColumn + mergeCols > maxColIndex) { maxColIndex = targetColumn + mergeCols; } } if (rowsOccuppied != null) { ws.Row(rowIndex).Height = (int)rowsOccuppied * baseRowHeight; } ExcelRange cell = ws.Cells[targetRow, targetColumn]; if (inTable) { TD(cell); } switch (sType) { case "formula": FormatNumber(cell, Type: Type, Decimals: Decimals); if (value == null) { cell.Value = ""; } else { cell.Formula = (string)value; } break; case "string": switch (AlignMode) { case TextAlign.LEFT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; break; case TextAlign.CENTER: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; break; case TextAlign.RIGHT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; break; default: break; } cell.Style.VerticalAlignment = VerticalAlign; cell.Style.Font.Size = fontSize; cell.Style.Font.Bold = fontBold; if (string.IsNullOrWhiteSpace((string)value)) { cell.Value = null; } else { cell.Value = (string)value; } break; case "paragraph": if (mergeCols > 1) { cell = ws.Cells[cell.Address + ":" + cell.Offset(0, mergeCols).Address]; cell.Merge = true; } cell.Style.WrapText = true; switch (AlignMode) { case TextAlign.LEFT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; break; case TextAlign.CENTER: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; break; case TextAlign.RIGHT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; break; default: break; } if (value == null) { cell.Value = ""; } else { cell.Value = (string)value; } break; case "number": case "double": FormatNumber(cell, Type: NumberTypes.NUMBER, Decimals: Decimals); if (value == null) { cell.Value = 0; } else { cell.Value = value; try { if ((decimal)(double)value < 0) { cell.Style.Font.Color.SetColor(Color.Red); } } catch { } } break; case "int": FormatNumber(cell, Type: NumberTypes.NUMBER, Decimals: 0); if (value == null) { cell.Value = 0; } else { cell.Value = value; try { if ((int)value < 0) { cell.Style.Font.Color.SetColor(Color.Red); } } catch { } } break; case "currency": FormatNumber(cell, Type: NumberTypes.CURRENCY, Decimals: Decimals); if (value == null) { cell.Value = 0; } else { cell.Value = value; try { if ((decimal)(double)value < 0) { cell.Style.Font.Color.SetColor(Color.Red); } } catch { } } break; case "percentage": FormatNumber(cell, Type: NumberTypes.PERCENTAGE, Decimals: Decimals); switch (AlignMode) { case TextAlign.LEFT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; break; case TextAlign.CENTER: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; break; case TextAlign.RIGHT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; break; default: break; } if (value == null) { cell.Value = 0; } else { cell.Value = (double)value; try { if ((double)value < 0) { cell.Style.Font.Color.SetColor(Color.Red); } } catch { } } break; case "title": cell.Style.Font.Size = 18; ws.Row(rowIndex).Height = 23; switch (AlignMode) { case TextAlign.LEFT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; break; case TextAlign.CENTER: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; break; case TextAlign.RIGHT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; break; default: break; } cell.Style.VerticalAlignment = ExcelVerticalAlignment.Bottom; cell.Value = (string)value; break; case "subtitle": cell.Style.Font.Bold = true; cell.Style.Font.Size = 14; switch (AlignMode) { case TextAlign.LEFT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; break; case TextAlign.CENTER: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; break; case TextAlign.RIGHT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; break; default: break; } cell.Value = (string)value; break; case "label": cell.Style.Font.Bold = true; cell.Style.WrapText = true; switch (AlignMode) { case TextAlign.LEFT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; break; case TextAlign.CENTER: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; break; case TextAlign.RIGHT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; break; default: break; } cell.Value = (string)value; break; case "date": cell.Style.Numberformat.Format = "dd-mmm-yyyy"; switch (AlignMode) { case TextAlign.LEFT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; break; case TextAlign.CENTER: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; break; case TextAlign.RIGHT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; break; default: break; } if (value == null) { cell.Value = ""; } else { DateTime theDate = (DateTime)value; cell.Formula = "=DATE(" + theDate.Year + "," + theDate.Month + "," + theDate.Day + ")"; } break; case "datetime": cell.Style.Numberformat.Format = "dd-mmm-yyyy h:mm"; switch (AlignMode) { case TextAlign.LEFT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; break; case TextAlign.CENTER: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; break; case TextAlign.RIGHT: cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; break; default: break; } if (value == null) { cell.Value = ""; } else { DateTime theDate = (DateTime)value; cell.Formula = "=DATE(" + theDate.Year + "," + theDate.Month + "," + theDate.Day + ") + TIME(" + theDate.Hour + "," + theDate.Minute + "," + theDate.Second + ")"; } break; case "bool": cell.Value = value; break; } if (col == null && row == null) { tab(); } return(cell); }
/// <summary> /// Sets the vertical alignment of ExcelWorksheet cells /// </summary> /// <param name="worksheet"></param> /// <param name="verticalAlignment"></param> /// <returns></returns> public static ExcelWorksheet SetVerticalAlignment(this ExcelWorksheet worksheet, ExcelVerticalAlignment verticalAlignment) { return(worksheet.SetVerticalAlignment(worksheet.Cells, verticalAlignment)); }
internal ExcelXfs(XmlNamespaceManager nsm, XmlNode topNode, ExcelStyles styles) : base(nsm, topNode) { _styles = styles; _xfID = GetXmlNodeInt("@xfid"); if (_xfID == 0) isBuildIn = true; //Normal taggen _numFmtId = GetXmlNodeInt("@numFmtId"); _fontId = GetXmlNodeInt("@fontId"); _fillId = GetXmlNodeInt("@fillId"); _borderId = GetXmlNodeInt("@borderId"); _readingOrder = GetXmlNodeString(readingOrderPath) == "1" ? true : false; _indent = GetXmlNodeInt(indentPath); _shrinkToFit = GetXmlNodeString(shrinkToFitPath) == "1" ? true : false; _verticalAlignment = GetVerticalAlign(GetXmlNodeString(verticalAlignPath)); _horizontalAlignment = GetHorizontalAlign(GetXmlNodeString(horizontalAlignPath)); _wrapText = GetXmlNodeString(wrapTextPath) == "1" ? true : false; _textRotation = GetXmlNodeInt(textRotationPath); _hidden = GetXmlNodeBool(hiddenPath); _locked = GetXmlNodeBool(lockedPath,true); }
/// <summary> /// Sets the vertical alignment of given cell range /// </summary> /// <param name="worksheet"></param> /// <param name="cellRange"></param> /// <param name="verticalAlignment"></param> /// <returns></returns> public static ExcelWorksheet SetVerticalAlignment(this ExcelWorksheet worksheet, ExcelRange cellRange, ExcelVerticalAlignment verticalAlignment) { worksheet.Cells[cellRange.Address].SetVerticalAlignment(verticalAlignment); return(worksheet); }
public static ExcelRange SetVerticalHorizontalAligment(this ExcelRange excelRange, ExcelVerticalAlignment verticalAlignment = ExcelVerticalAlignment.Center, ExcelHorizontalAlignment horizontalAlignment = ExcelHorizontalAlignment.Center) { return(excelRange.SetHorizontalAligment(horizontalAlignment).SetVerticalAligment(verticalAlignment)); }
/// <summary> /// Sets vertical alignment of Excel style /// </summary> /// <param name="thisStyle">The Excel style</param> /// <param name="verticalAlignment"></param> /// <returns></returns> public static ExcelStyle SetVerticalAlignment(this ExcelStyle thisStyle, ExcelVerticalAlignment verticalAlignment) { thisStyle.VerticalAlignment = verticalAlignment; return(thisStyle); }
public static ExcelRange SetVerticalAligment(this ExcelRange excelRange, ExcelVerticalAlignment verticalAlignment = ExcelVerticalAlignment.Center) { excelRange.Style.VerticalAlignment = verticalAlignment; return(excelRange); }
/// <summary> /// 设置样式 /// </summary> /// <param name="x"></param> /// <param name="y"></param> /// <param name="isWrapText">是否换行</param> /// <param name="horizontal">水平格式</param> /// <param name="vertical">垂直格式</param> /// <param name="isBold">是否粗体</param> /// <param name="size">文字大小</param> /// <param name="height">行高</param> /// <param name="isShowGridLines">是否显示网格线</param> public void SetStyle(int x, int y, bool isWrapText = true, ExcelHorizontalAlignment horizontal = ExcelHorizontalAlignment.Center, ExcelVerticalAlignment vertical = ExcelVerticalAlignment.Center, bool isBold = false, int size = 12, int height = 15, bool isShowGridLines = false) { //worksheet.Cells[x, y].Style.Numberformat.Format = "#,##0.00";//这是保留两位小数 worksheet.Cells[x, y].Style.HorizontalAlignment = horizontal; //水平居中 worksheet.Cells[x, y].Style.VerticalAlignment = vertical; //垂直居中 //worksheet.Cells[1, 4, 1, 5].Merge = true;//合并单元格 worksheet.Cells.Style.WrapText = isWrapText; //自动换行 worksheet.Cells[x, y].Style.Font.Bold = isBold; //字体为粗体 //worksheet.Cells[1, 1].Style.Font.Color.SetColor(Color.White);//字体颜色 //worksheet.Cells[1, 1].Style.Font.Name = "微软雅黑";//字体 worksheet.Cells[x, y].Style.Font.Size = size;//字体大小 //worksheet.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid; //worksheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//设置单元格背景色 worksheet.Cells[x, y].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); //设置单元格所有边框 worksheet.Cells[x, y].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; //单独设置单元格底部边框样式和颜色(上下左右均可分开设置) worksheet.Cells[x, y].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191)); //worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小 worksheet.Row(x).Height = height; //设置行高 //worksheet.Row(1).CustomHeight = true;//自动调整行高 //worksheet.Column(1).Width = 15;//设置列宽 worksheet.View.ShowGridLines = isShowGridLines; //去掉sheet的网格线 //worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid; //worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray);//设置背景色 //worksheet.BackgroundImage.Image = Image.FromFile(@"firstbg.jpg");//设置背景图片 }
public virtual TMemberOptionsBuilder VAlign(ExcelVerticalAlignment alignment) { Options.VAlign = alignment; return((TMemberOptionsBuilder)this); }
/// <summary> /// 设置范围单元格的垂直对齐方式 /// </summary> /// <param name="startRow">开始的单元格行坐标</param> /// <param name="startColumn">开始的单元格列坐标</param> /// <param name="endRow">结束的单元格行坐标</param> /// <param name="endColumn">结束的单元格列坐标</param> /// <param name="cellAlignment">对齐方式</param> public void SetCellVerticalAlignment(int startRow, int startColumn, int endRow, int endColumn, ExcelVerticalAlignment cellAlignment) { Range range = GetRange(startRow, startColumn, endRow, endColumn); range.Select(); range.VerticalAlignment = cellAlignment; }
private void setCellBodyStyle(ExcelRangeBase cell, int fontSize, ExcelHorizontalAlignment align, ExcelVerticalAlignment vertalign) { //cell.Style.Font.Bold = true; cell.Style.Font.Size = fontSize; cell.Style.VerticalAlignment = vertalign; cell.Style.HorizontalAlignment = align; cell.Style.Fill.PatternType = ExcelFillStyle.Solid; cell.Style.Border.Bottom.Style = cell.Style.Border.Top.Style = cell.Style.Border.Left.Style = cell.Style.Border.Right.Style = ExcelBorderStyle.Thin; cell.Style.Border.Bottom.Color.SetColor(Color.Black); cell.Style.Border.Top.Color.SetColor(Color.Black); cell.Style.Border.Right.Color.SetColor(Color.Black); cell.Style.Border.Left.Color.SetColor(Color.Black); cell.Style.Fill.BackgroundColor.SetColor(Color.White); }