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