public static void CreateHeaderTable(ExcelWorksheet sheet, int row, int col, object value, ExcelHorizontalAlignment align, bool merge, bool autoFitColumn = true)
{
CreateCellTable(sheet.Cells[row, col], value, align, merge, null, null, null, true, autoFitColumn: autoFitColumn);
}
public static void CreateCell(ExcelWorksheet sheet, int fromRow, int fromCol, int toRow, int toCol, object value, ExcelHorizontalAlignment align, bool merge,
Color?background, Color?color, float?size, bool bold = false, bool autoFitColumn = true)
{
CreateCell(sheet.Cells[fromRow, fromCol, toRow, toCol], value, align, merge, background, color, size, bold, autoFitColumn: autoFitColumn);
}
public static void CreateHeaderTable(ExcelWorksheet sheet, int fromRow, int fromCol, int toRow, int toCol, object value,
ExcelHorizontalAlignment align, bool merge, Color background, Color color, bool autoFitColumn = true)
{
CreateCellTable(sheet.Cells[fromRow, fromCol, toRow, toCol], value, align, merge, background, color, null, true, autoFitColumn: autoFitColumn);
}
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;
}
}
public static void CreateCellTable(ExcelWorksheet sheet, string range, object value, ExcelHorizontalAlignment align, bool merge, bool autoFitColumn = true)
{
CreateCellTable(sheet.Cells[range], value, align, merge, null, null, null, autoFitColumn: autoFitColumn);
}
public static ExcelRange SetHorizontalAligment(this ExcelRange excelRange, ExcelHorizontalAlignment horizontalAlignment = ExcelHorizontalAlignment.Center)
{
excelRange.Style.HorizontalAlignment = horizontalAlignment;
return(excelRange);
}
public StyleHorizontalAlignment(ExcelHorizontalAlignment horizontalAlignment)
{
this.HorizontalAlignment = horizontalAlignment;
}
/// <summary> /// Sets the horizontal alignment of ExcelWorksheet cells /// </summary> /// <param name="worksheet"></param> /// <param name="horizontalAlignment"></param> /// <returns></returns> public static ExcelWorksheet SetHorizontalAlignment(this ExcelWorksheet worksheet, ExcelHorizontalAlignment horizontalAlignment) => worksheet.SetHorizontalAlignment(worksheet.Cells, horizontalAlignment);
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);
}
public static void AgregarTexto(this ExcelWorksheet ws, int fila, int columna, object valor, ExcelHorizontalAlignment horizontalAlignment, bool fontBold)
{
ws.Cells[fila, columna].Value = valor;
ws.Cells[fila, columna].Style.HorizontalAlignment = horizontalAlignment;
ws.Cells[fila, columna].Style.Font.Bold = fontBold;
}
/// <summary>
/// Sets the horizontal alignment of given cell range
/// </summary>
/// <param name="worksheet"></param>
/// <param name="cellRange"></param>
/// <param name="horizontalAlignment"></param>
/// <returns></returns>
public static ExcelWorksheet SetHorizontalAlignment(this ExcelWorksheet worksheet, ExcelRange cellRange, ExcelHorizontalAlignment horizontalAlignment)
{
worksheet.Cells[cellRange.Address].SetHorizontalAlignment(horizontalAlignment);
return(worksheet);
}
public static void AgregarTexto(this ExcelWorksheet ws, int fila, int columna, object valor, string formato, bool fontBold, ExcelBorderStyle excelBorderStyle, ExcelHorizontalAlignment horizontalAlignment)
{
var cell = ws.Cells[fila, columna];
cell.Value = valor;
cell.Style.Numberformat.Format = formato;
cell.Style.Font.Bold = fontBold;
cell.Style.Border.Top.Style = excelBorderStyle;
cell.Style.HorizontalAlignment = horizontalAlignment;
}
public static ExcelRange HorizontalAlign(this ExcelRange cell, ExcelHorizontalAlignment align = ExcelHorizontalAlignment.Right)
{
cell.Style.HorizontalAlignment = align;
return(cell);
}
public static ExcelRange Alinea(this ExcelRange cell, ExcelHorizontalAlignment Alinea = ExcelHorizontalAlignment.Center)
{
cell.Style.HorizontalAlignment = Alinea;
return(cell);
}
public static void AgregarTexto(this ExcelWorksheet ws, int fila, int columna, int aLafila, int aLaColumna, object valor, float size, bool fontBold, ExcelHorizontalAlignment horizontalAlignment, Color bgColor, bool ajustarTexto)
{
using (var rango = ws.Cells[fila, columna, aLafila, aLaColumna])
{
rango.Value = valor;
rango.Style.Font.Bold = fontBold;
rango.Style.Font.Size = size;
rango.Merge = true;
rango.Style.HorizontalAlignment = horizontalAlignment;
rango.Style.Fill.PatternType = ExcelFillStyle.Solid;
rango.Style.Fill.BackgroundColor.SetColor(bgColor);
rango.Style.WrapText = ajustarTexto;
}
}
/// <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);
}
public static ExcelRange SetVerticalHorizontalAligment(this ExcelRange excelRange, ExcelVerticalAlignment verticalAlignment = ExcelVerticalAlignment.Center, ExcelHorizontalAlignment horizontalAlignment = ExcelHorizontalAlignment.Center)
{
return(excelRange.SetHorizontalAligment(horizontalAlignment).SetVerticalAligment(verticalAlignment));
}
/// <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 CreateCell(ExcelWorksheet sheet, int row, int column, double value, Color?color, float size = 11, bool isFontBold = false,
ExcelHorizontalAlignment alignment = ExcelHorizontalAlignment.Center,
ExcelBorderStyle borderStyle = ExcelBorderStyle.Medium)
{
CreateCell(sheet, row, column, row, column, value, color, size, isFontBold, alignment, borderStyle);
}
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;
}
}
private void FormatAsHeader(ref ExcelWorksheet ws, string range, bool usePattern, ExcelHorizontalAlignment alignment)
{
//Formatting - Bold
using (var r = ws.Cells[range])
{
r.Style.Font.SetFromFont(new Font("Calibri", 10, FontStyle.Bold));
r.Style.HorizontalAlignment = alignment;
if (usePattern)
{
r.Style.Fill.PatternType = ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
r.Style.Font.Color.SetColor(Color.White);
}
else
{
r.Style.Font.Color.SetColor(Color.Black);
}
}
}
public virtual TMemberOptionsBuilder HAlign(ExcelHorizontalAlignment alignment)
{
Options.HAlign = alignment;
return((TMemberOptionsBuilder)this);
}
public static void CreateCellTable(ExcelWorksheet sheet, int fromRow, int fromCol, int toRow, int toCol, object value, ExcelHorizontalAlignment align, bool autoFitColumn = true)
{
CreateCellTable(sheet.Cells[fromRow, fromCol, toRow, toCol], value, align, false, null, null, null, autoFitColumn: autoFitColumn);
}
/// <summary>
/// Sets the horizontal alignment of given range
/// </summary>
/// <param name="range"></param>
/// <param name="horizontalAlignment"></param>
/// <returns></returns>
public static ExcelRangeBase SetHorizontalAlignment(this ExcelRangeBase range, ExcelHorizontalAlignment horizontalAlignment)
{
range.Style.SetHorizontalAlignment(horizontalAlignment);
return(range);
}
public static void CreateCellTable(ExcelWorksheet sheet, string range, object value, ExcelHorizontalAlignment align, bool merge,
Color background, Color color, float size, bool autoFitColumn = true)
{
CreateCellTable(sheet.Cells[range], value, align, merge, background, color, size, autoFitColumn: autoFitColumn);
}
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;
}
public static void CreateCell(ExcelWorksheet sheet, string range, object value, ExcelHorizontalAlignment align, bool merge,
Color?background, Color?color, float?size, bool bold, bool wrapText, bool autoFitColumn = true)
{
CreateCell(sheet.Cells[range], value, align, merge, background, color, size, bold, wrapText, autoFitColumn: autoFitColumn);
}
private void setCellHeaderStyle(ExcelRangeBase cell, int fontSize, ExcelHorizontalAlignment align)
{
setCellHeaderStyle(cell, fontSize, align, ExcelVerticalAlignment.Center);
}
private static void CreateCellTable(ExcelRange cell, object value, ExcelHorizontalAlignment align, bool merge, Color?background,
Color?color, float?size, bool bold = false, bool wrapText = false, bool autoFitColumn = true)
{
CreateCell(cell, value, align, merge, background, color, size, bold, wrapText, autoFitColumn: autoFitColumn);
SetBorder(cell);
}
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);
}
public static void CreateHeaderTable(ExcelWorksheet sheet, int row, int col, object value, ExcelHorizontalAlignment align, bool merge,
Color background, Color color, float size, bool bold, bool autoFitColumn = true)
{
CreateCellTable(sheet.Cells[row, col], value, align, merge, background, color, size, bold, autoFitColumn: autoFitColumn);
}
public static void AgregarTexto(this ExcelWorksheet ws, int fila, int columna, int aLafila, int aLaColumna, object valor, float size, bool fontBold, ExcelHorizontalAlignment horizontalAlignment)
{
using (var rango = ws.Cells[fila, columna, aLafila, aLaColumna])
{
rango.Value = valor;
rango.Style.Font.Bold = fontBold;
rango.Style.Font.Size = size;
rango.Merge = true;
rango.Style.HorizontalAlignment = horizontalAlignment;
}
}
