public static void AddColumnHeaderToSheet(
ref Worksheet ws,
int columnNumber,
int columnWidth,
int headerRow,
string headerTitle = "",
int headerFontSize = cHeaderFontSize,
MakeBold headerBold = MakeBold.Yes,
XlUnderlineStyle headerUnderline = XlUnderlineStyle.xlUnderlineStyleNone,
WrapText headerWrapText = WrapText.Yes,
XlHAlign headerHorizontalAlignment = XlHAlign.xlHAlignGeneral,
int headerOrientation = 0)
{
Range column = (Range)ws.Columns[columnNumber];
column.ColumnWidth = columnWidth;
if (!string.IsNullOrEmpty(headerTitle))
{
Range header = (Range)ws.Cells[headerRow, columnNumber];
header.Value = headerTitle;
header.Font.Size = headerFontSize;
header.Font.Bold = headerBold;
header.Font.Underline = headerUnderline;
header.WrapText = headerWrapText;
header.HorizontalAlignment = headerHorizontalAlignment;
header.Orientation = headerOrientation;
}
}
public void SetAlignment(string cell1, string cell2, XlHAlign align)
{
Range range = get_Range(cell1, cell2);
if (range != null)
{
range.HorizontalAlignment = align;
}
}
public async Task SetHorizontalAlign(int sheet, string range, XlHAlign xlHAlign)
{
await Task.Run(new System.Action(() =>
{
sheets = xlWorkbook.Sheets;
setValueSheet = sheets.get_Item(sheet);
setValueRange = setValueSheet.Range[range];
setValueRange.HorizontalAlignment = xlHAlign;
}
));
}
public static void SetCellValue(Range rngR, object vntValue, XlHAlign horizontalAlignment = XlHAlign.xlHAlignLeft, string strComment = "")
{
//var _with12 = rngR;
rngR.Value = vntValue;
rngR.HorizontalAlignment = horizontalAlignment;
if (!string.IsNullOrEmpty(strComment))
{
rngR.AddComment();
rngR.Comment.Visible = false;
rngR.Comment.Text(Text: strComment);
}
}
public void SetText(
int row,
int col,
string text,
bool wrapText = false,
XlHAlign xlHAligh = XlHAlign.xlHAlignLeft,
XlVAlign xlVAlign = XlVAlign.xlVAlignCenter,
float orientation = 0,
float indetLevel = 0,
float fontSize = 12,
bool bold = false,
bool?mergeCells = null,
int?interiorColor = null,
int?fontColor = null) => range.SetText(row, col, text, wrapText, xlHAligh, xlVAlign, orientation, indetLevel, fontSize, bold, mergeCells, interiorColor, fontColor);
public static void AddContentToCell(Range rng, string text,
int fontSize = 10,
MakeBold bold = MakeBold.No,
XlUnderlineStyle underline = XlUnderlineStyle.xlUnderlineStyleNone,
WrapText wrapText = WrapText.No,
XlHAlign horizontalAlignment = XlHAlign.xlHAlignGeneral)
{
rng.Value = text;
rng.Font.Size = fontSize;
rng.Font.Bold = (int)bold;
rng.Font.Underline = underline;
rng.WrapText = (int)wrapText;
rng.HorizontalAlignment = horizontalAlignment;
//rng.NumberFormat = "MM/DD/YYYY";
}
/// <summary>
/// Consolidar las celdas seleccionadas.
/// </summary>
/// <param name="alineacion">Alineación de las celdas consolidadas</param>
public override void ajustarCeldas(AlineacionHorizontal alineacion)
{
XlHAlign alineacion_horizontal = XlHAlign.xlHAlignJustify;
switch (alineacion)
{
case AlineacionHorizontal.Izquierda:
alineacion_horizontal = XlHAlign.xlHAlignLeft;
break;
case AlineacionHorizontal.Centro:
alineacion_horizontal = XlHAlign.xlHAlignCenter;
break;
}
_seleccionado.Merge(_missing);
_seleccionado.HorizontalAlignment = alineacion_horizontal;
}
public static void AddValue(this Worksheet ws, string begin, string end, dynamic value, int fontSize, bool merge = false
, XlHAlign align = XlHAlign.xlHAlignCenter, bool bold = true, int ColumnWidth = -1, string fontName = "Times New Roman")
{
Range range = ws.get_Range(begin, end);
if (merge)
{
range.Merge();
}
range.Font.Size = fontSize;
range.Font.Name = fontName;
range.Font.Bold = bold;
range.Cells.HorizontalAlignment = align;
range.Value2 = value;
if (ColumnWidth > -1)
{
range.ColumnWidth = ColumnWidth;
}
}
public static void SetText(
this Range rngHome,
int row,
int col,
string text,
bool wrapText = false,
XlHAlign xlHAligh = XlHAlign.xlHAlignLeft,
XlVAlign xlVAlign = XlVAlign.xlVAlignCenter,
float orientation = 0,
float indetLevel = 0,
float fontSize = 12,
bool bold = false,
bool?mergeCells = false,
int?interiorColor = null,
int?fontColor = null)
{
var rng = (Range)rngHome.Cells[row, col];
rng.SetText(text, wrapText, xlHAligh, xlVAlign, orientation, indetLevel, fontSize, bold, mergeCells, interiorColor, fontColor);
}
// NAMDV
public void SetHAlign(VTHAlign align)
{
XlHAlign _align = XlHAlign.xlHAlignLeft;
switch (align)
{
case VTHAlign.xlHAlignCenter:
_align = XlHAlign.xlHAlignLeft;
break;
case VTHAlign.xlHAlignCenterAcrossSelection:
_align = XlHAlign.xlHAlignCenterAcrossSelection;
break;
case VTHAlign.xlHAlignDistributed:
_align = XlHAlign.xlHAlignDistributed;
break;
case VTHAlign.xlHAlignFill:
_align = XlHAlign.xlHAlignFill;
break;
case VTHAlign.xlHAlignGeneral:
_align = XlHAlign.xlHAlignGeneral;
break;
case VTHAlign.xlHAlignJustify:
_align = XlHAlign.xlHAlignJustify;
break;
case VTHAlign.xlHAlignLeft:
_align = XlHAlign.xlHAlignLeft;
break;
case VTHAlign.xlHAlignRight:
_align = XlHAlign.xlHAlignRight;
break;
}
Range.HorizontalAlignment = _align;
}
public static void SetText(
this Range rngHome,
string text,
bool?wrapText = null,
XlHAlign xlHAligh = XlHAlign.xlHAlignLeft,
XlVAlign xlVAlign = XlVAlign.xlVAlignCenter,
float orientation = 0,
float indetLevel = 0,
float fontSize = 12,
bool bold = false,
bool?mergeCells = null,
int?interiorColor = null,
int?fontColor = null)
{
rngHome.Value2 = text;
rngHome.HorizontalAlignment = xlHAligh;
rngHome.VerticalAlignment = xlVAlign;
rngHome.Font.Size = fontSize;
rngHome.Font.Bold = bold;
rngHome.IndentLevel = indetLevel;
rngHome.Orientation = orientation;
rngHome.WrapText = wrapText;
if (wrapText.HasValue)
{
rngHome.WrapText = wrapText;
}
if (interiorColor.HasValue)
{
rngHome.Interior.Color = interiorColor.Value;
}
if (fontColor.HasValue)
{
rngHome.Font.Color = fontColor.Value;
}
if (mergeCells.HasValue && mergeCells.Value)
{
rngHome.Merge();
}
}
public void setAlign(int iRow, string sColumn, string sAlign)
{
XlHAlign xAlign = xAlign = XlHAlign.xlHAlignLeft;
switch (sAlign.ToUpper())
{
case "L":
xAlign = XlHAlign.xlHAlignLeft;
break;
case "R":
xAlign = XlHAlign.xlHAlignRight;
break;
case "C":
xAlign = XlHAlign.xlHAlignCenter;
break;
}
ws.Cells[iRow, sColumn].HorizontalAlignment = xAlign;
//ws.get_Range("", "").HorizontalAlignment = xAlign;
}
public static void AddColumnToSheet(
ref Worksheet ws,
int columnNumber,
int columnWidth,
bool columnWrapText,
string columnNumberFormat,
XlDirection shiftDirection,
XlInsertFormatOrigin insertFormatOrigin,
int headerRow,
string headerTitle = "",
int headerFontSize = cHeaderFontSize,
MakeBold headerBold = MakeBold.Yes,
XlUnderlineStyle headerUnderline = XlUnderlineStyle.xlUnderlineStyleSingle,
WrapText headerWrapText = WrapText.Yes,
XlHAlign headerHorizontalAlignment = XlHAlign.xlHAlignCenter,
int headerOrientation = 0)
{
// Insert the new column and apply things that pertain to all cells in the column
//((Range)ws.Columns[columnNumber]).Insert(Shift: shiftDirection, CopyOrigin: insertFormatOrigin);
Range newColumn = (Range)ws.Columns[columnNumber];
newColumn.Insert(Shift: shiftDirection, CopyOrigin: insertFormatOrigin);
newColumn.WrapText = columnWrapText;
newColumn.NumberFormat = columnNumberFormat;
// Pass all the rest on
AddColumnHeaderToSheet(ref ws,
columnNumber,
columnWidth,
headerRow,
headerTitle,
headerFontSize,
headerBold,
headerUnderline,
headerWrapText,
headerHorizontalAlignment,
headerOrientation);
}
/// <summary>
/// Cambiar la alineación del texto de las celdas seleccionadas.
/// <param name="vertical">Alineación vertical</param>
/// <param name="horizontal">Alineación horizontal</param>
/// </summary>
public override void cambiarAlineacionTexto(AlineacionVertical vertical, AlineacionHorizontal horizontal)
{
XlVAlign alineacion_vertical = XlVAlign.xlVAlignBottom;
XlHAlign alineacion_horizontal = XlHAlign.xlHAlignLeft;
switch (vertical)
{
case AlineacionVertical.Centro: alineacion_vertical = XlVAlign.xlVAlignCenter; break;
case AlineacionVertical.Arriba: alineacion_vertical = XlVAlign.xlVAlignTop; break;
case AlineacionVertical.Abajo: alineacion_vertical = XlVAlign.xlVAlignBottom; break;
case AlineacionVertical.Justificado: alineacion_vertical = XlVAlign.xlVAlignJustify; break;
case AlineacionVertical.Distribuido: alineacion_vertical = XlVAlign.xlVAlignDistributed; break;
}
switch (horizontal)
{
case AlineacionHorizontal.Centro: alineacion_horizontal = XlHAlign.xlHAlignCenter; break;
case AlineacionHorizontal.Izquierda: alineacion_horizontal = XlHAlign.xlHAlignLeft; break;
case AlineacionHorizontal.Derecha: alineacion_horizontal = XlHAlign.xlHAlignRight; break;
case AlineacionHorizontal.Lleno: alineacion_horizontal = XlHAlign.xlHAlignFill; break;
case AlineacionHorizontal.Justificado: alineacion_horizontal = XlHAlign.xlHAlignJustify; break;
case AlineacionHorizontal.Distribuido: alineacion_horizontal = XlHAlign.xlHAlignDistributed; break;
case AlineacionHorizontal.General: alineacion_horizontal = XlHAlign.xlHAlignGeneral; break;
}
_seleccionado.VerticalAlignment = alineacion_vertical;
_seleccionado.HorizontalAlignment = alineacion_horizontal;
}
/// <summary>
/// 设置单元格居中
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="align"></param>
public void SetCellsAlign(int startRow, int startColumn, int endRow, int endColumn, XlHAlign align)
{
dynamic range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.HorizontalAlignment = align;
}
private void RangeSetting(Range rng, string fontName, int fontSize, bool isBold, XlHAlign hAlignment, XlVAlign vAlignment, string text, int colorIndex)
{
rng.Font.Name = fontName;
rng.Font.Size = fontSize;
rng.Font.Bold = isBold ? 1 : 0;
rng.Interior.ColorIndex = colorIndex;
rng.HorizontalAlignment = hAlignment;
rng.VerticalAlignment = vAlignment;
rng.Value2 = text;
}
public CellFormatSpecification CreateCellFormat(string name, int fontSize, XlHAlign horizontalAlignment)
{
CellFormatSpecification cellFormatSpecification = new CellFormatSpecification(name);
InitializeFont(this.startRange.Font, cellFormatSpecification);
cellFormatSpecification.Font.Size = fontSize;
cellFormatSpecification.HorizontalAlignment = horizontalAlignment;
return(cellFormatSpecification);
}
void insertFormatData(ExcelFormatData formatData)
{
foreach (ExcelRangeInfo range in formatData.Ranges)
{
Range rng = GetRange(range);
try
{
if (formatData.Foreground.HasValue)
{
rng.Font.Color = ColorTranslator.ToOle(formatData.Foreground.Value);
}
if (formatData.Background.HasValue)
{
rng.Interior.Color = ColorTranslator.ToOle(formatData.Background.Value);
}
if (formatData.FontSize.HasValue)
{
rng.Font.Size = formatData.FontSize.Value;
}
if (formatData.IsFontBold.HasValue)
{
rng.Font.Bold = formatData.IsFontBold.Value;
}
if (formatData.IsFontItalic.HasValue)
{
rng.Font.Italic = formatData.IsFontItalic.Value;
}
if (formatData.IsWrapped.HasValue)
{
rng.WrapText = formatData.IsWrapped.Value;
}
if (formatData.IsCoumnAutofit.HasValue)
{
foreach (Range col in rng.Columns)
{
col.EntireColumn.AutoFit();
}
}
if (formatData.HorizontalAlignment.HasValue)
{
XlHAlign horizAlignment = (XlHAlign)formatData.HorizontalAlignment.Value;
rng.HorizontalAlignment = horizAlignment;
}
if (formatData.Vertical.HasValue)
{
XlVAlign verticalAlignment = (XlVAlign)formatData.Vertical.Value;
rng.VerticalAlignment = verticalAlignment;
}
if (formatData.IsPrintArea.HasValue && formatData.IsPrintArea.Value)
{
_sheet.PageSetup.PrintArea = rng.Address;
_sheet.DisplayPageBreaks = true;
}
if (formatData.ColumnWidths != null)
{
for (int c = 0; c < formatData.ColumnWidths.Length; ++c)
{
int col = range.Start.Column + c;
string colSymbol = ExcelSupport.GetColumnSymbol(col);
string colRange = string.Format("{0}:{1}", colSymbol, colSymbol);
int width = formatData.ColumnWidths[c];
_sheet.get_Range(colRange).EntireColumn.ColumnWidth = width;
}
}
}
catch (Exception exc)
{
}
}
}
public static void AddCommentToCell(ref Worksheet ws, int column, int row, string text, int headerFontSize = cHeaderFontSize, bool headerBold = true, bool headerUnderline = true, bool headerWrapText = true, XlHAlign headerHorizontalAlignment = XlHAlign.xlHAlignGeneral)
{
((Range)ws.Cells[row, column]).AddComment(text);
// TODO: Determine how to format the text differently.
//With ws
// With .Cells(row, column)
// .Value = headerTitle
// .Font.Size = headerFontSize
// .Font.Bold = headerBold
// .Font.Underline = headerUnderline
// .WrapText = headerWrapText
// .HorizontalAlignment = headerHorizontalAlignment
// End With
//End With
}
/// <summary>
/// 设置单元格居中、粗体、背景颜色
/// </summary>
/// <param name="columnIndex"></param>
/// <param name="align"></param>
public void SetCellsStyle(int startRow, int startColumn, int endRow, int endColumn, XlHAlign align, bool IsBold, ColorIndex color)
{
dynamic range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]);
range.HorizontalAlignment = align;
range.Font.Bold = IsBold;
range.Interior.ColorIndex = color;
}
