public static void SetCellAlignAndWrap(ExcelInterop.Range range, ExcelInterop.XlHAlign hAlign = ExcelInterop.XlHAlign.xlHAlignLeft)
{
Utility.AddNativieResource(range);
range.HorizontalAlignment = hAlign;
range.VerticalAlignment = ExcelInterop.XlVAlign.xlVAlignCenter;
range.WrapText = true;
}
public void SetCellAlign(int row, int column, Align type)
{
Excel.XlHAlign align = (type == Align.LEFT) ? Excel.XlHAlign.xlHAlignLeft :
(type == Align.MIDDLE) ? Excel.XlHAlign.xlHAlignCenter :
(type == Align.RIGHT) ? Excel.XlHAlign.xlHAlignRight : 0;
ExcelSheet.Cells[row, column].HorizontalAlignment = align;
}
private void writeToSingleCell(Location cellLocation, string value, int mode, int cellType, double cellWidth, double cellHeight, bool boldFlag, bool italicsFlag, bool wrapTextFlag, System.Drawing.Color cellColour,
System.Drawing.Color textColour, Excel.XlHAlign horizAlignment, Excel.XlVAlign vertAlignment, String fontName, int fontSize)
{
int row = cellLocation.getRow();
int col = cellLocation.getColumn();
if (mode == 1 || mode == 2)
{
Console.WriteLine("Writing to " + col + " (" + cellLocation.getExcelAddress() + "): " + value);
}
if (mode == 0 || mode == 2)
{
currentSheet.Cells[row, col] = value;
Excel.Range r = currentSheet.Cells[row, col] as Excel.Range;
if (cellWidth > 0)
{
currentSheet.Columns[startCol].ColumnWidth = cellWidth;
}
if (cellHeight > 0)
{
currentSheet.Rows[startRow].RowHeight = cellHeight;
}
r.Interior.Color = System.Drawing.ColorTranslator.ToOle(cellColour);
r.Font.Color = System.Drawing.ColorTranslator.ToOle(textColour);
r.Font.Bold = boldFlag;
r.Font.Italic = italicsFlag;
r.WrapText = wrapTextFlag;
r.HorizontalAlignment = horizAlignment;
r.VerticalAlignment = vertAlignment;
r.Font.Name = fontName;
r.Font.Size = fontSize;
}
}
//
private void SetRange(string l, string r, Excel.XlHAlign ha, string txt)
{
if (Show)
{
Rng = Sh.get_Range(l, r);
Rng.Merge(Type.Missing);
Rng.NumberFormat = "@";
Rng.HorizontalAlignment = ha;
Rng.Cells[1, "A"] = txt;
}
}
static MText PrepareText(Rectangle3d cell, string content, XL.XlHAlign alignment)
{
var mt = new MText();
mt.SetDatabaseDefaults();
mt.Contents = content;
mt.Layer = "ПК_С_Тексты_3.0";
mt.TextStyleId = style.ObjectId;
mt.Width = cell.LowerRight.X - cell.LowerLeft.X;
mt.Height = cell.UpperLeft.Y - cell.LowerLeft.Y;
switch (alignment)
{
case XL.XlHAlign.xlHAlignCenter:
{
mt.Location = cell.LowerLeft.Add(new Vector3d(mt.Width / 2, mt.Height / 2, 0));
mt.Attachment = AttachmentPoint.MiddleCenter;
break;
}
case XL.XlHAlign.xlHAlignLeft:
{
mt.Location = cell.LowerLeft.Add(new Vector3d(0, mt.Height / 2, 0));
mt.Attachment = AttachmentPoint.MiddleLeft;
break;
}
case XL.XlHAlign.xlHAlignRight:
{
mt.Location = cell.LowerLeft.Add(new Vector3d(mt.Width, mt.Height / 2, 0));
mt.Attachment = AttachmentPoint.MiddleRight;
break;
}
default:
{
mt.Location = cell.LowerLeft.Add(new Vector3d(mt.Width / 2, mt.Height / 2, 0));
mt.Attachment = AttachmentPoint.MiddleCenter;
break;
}
}
if (mt.ActualWidth > mt.Width)
{
mt.Contents = @"{\W0.6;" + content + @"}";
}
return(mt);
}
private void GetAlignment(FarPoint.Win.Spread.Cell cell, out XL.XlHAlign hAlign, out XL.XlVAlign vAlign)
{
switch (cell.HorizontalAlignment)
{
case CellHorizontalAlignment.Center:
hAlign = XL.XlHAlign.xlHAlignCenter;
break;
case CellHorizontalAlignment.General:
hAlign = XL.XlHAlign.xlHAlignGeneral;
break;
case CellHorizontalAlignment.Left:
hAlign = XL.XlHAlign.xlHAlignLeft;
break;
case CellHorizontalAlignment.Right:
hAlign = XL.XlHAlign.xlHAlignRight;
break;
default:
hAlign = XL.XlHAlign.xlHAlignGeneral;
break;
}
switch (cell.VerticalAlignment)
{
case CellVerticalAlignment.Bottom:
vAlign = XL.XlVAlign.xlVAlignBottom;
break;
case CellVerticalAlignment.Center:
vAlign = XL.XlVAlign.xlVAlignCenter;
break;
case CellVerticalAlignment.General:
vAlign = XL.XlVAlign.xlVAlignTop;
break;
case CellVerticalAlignment.Top:
vAlign = XL.XlVAlign.xlVAlignTop;
break;
default:
vAlign = XL.XlVAlign.xlVAlignTop;
break;
}
}
/// <summary>
/// Конструктор
/// </summary>
/// <param name="StartCell">Левый верхний угол объединения</param>
/// <param name="EndCell">Правый нижний угло объединения(по умолчанию равен левому верхнему)</param>
/// <param name="value">значение</param>
/// <param name="Align">Выравнивание в ячейке</param>
/// <param name="font_cell">Шрифт в ячейке</param>
/// <param name="borders">Рамки</param>
public ExcelParameter(string StartCell, string EndCell, string value, MExcel.XlHAlign Align, D.Font font_cell, params MExcel.XlBordersIndex[] borders)
{
NameOfExcel = StartCell;
NameOfEndMergeExcel = EndCell;
Value = value;
TextAlign = Align;
if (font_cell != null)
{
Font = font_cell;
}
else
{
Font = new System.Drawing.Font("Arial", 14);
}
Borders = borders;
}
/// <summary>
/// CellValue
/// </summary>
/// <param name="cell"></param>
/// <param name="value"></param>
/// <param name="fontsize"></param>
/// <param name="halign"></param>
/// <param name="valign"></param>
/// <param name="font_color"></param>
/// <param name="font"></param>
/// <param name="bold"></param>
/// <param name="underline"></param>
public void CellValue(string cell, string value, int fontsize = 10, Excel.XlHAlign halign = Excel.XlHAlign.xlHAlignLeft, Excel.XlVAlign valign = Excel.XlVAlign.xlVAlignBottom, Color?font_color = null, string font = "Arial", bool bold = false, bool underline = false)
{
int row;
Int32.TryParse(new String(cell.Where(Char.IsDigit).ToArray()), out row);
string col = new String(cell.Where(Char.IsLetter).ToArray());
Color font_rgb = font_color ?? Color.FromArgb(0, 0, 0);
sheet1.Cells[row, col].WrapText = true;
sheet1.Cells[row, col].Font.Name = font;
sheet1.Cells[row, col].Font.Size = fontsize;
sheet1.Cells[row, col].Font.Bold = bold;
sheet1.Cells[row, col].Font.Underline = underline;
sheet1.Cells[row, col].Font.Color = ColorTranslator.ToOle(font_rgb);
sheet1.Cells[row, col] = value;
sheet1.Cells[row, col].HorizontalAlignment = halign;
sheet1.Cells[row, col].VerticalAlignment = valign;
}
private void WorkSheet_SelectionChange(Excel.Range Target)
{
//myRange = Target.Copy();
foreach (Excel.Range cell in Target.Cells)
{
Console.WriteLine("Cell " + cell.Address);
string fontname = ((Excel.Range)cell).Font.Name.ToString();
Console.WriteLine("\t Font Name = " + fontname);
Excel.XlHAlign horizontalAlignment = (Excel.XlHAlign)cell.HorizontalAlignment;
Console.WriteLine("\t Horizontal Alignment = " + horizontalAlignment);
Excel.XlVAlign verticalalignment = (Excel.XlVAlign)cell.VerticalAlignment;
Console.WriteLine("\t Vertical Alignment = " + verticalalignment);
Console.WriteLine("\t Borders Line Style >");
Console.WriteLine("\t\t Left = " + (Excel.XlLineStyle)cell.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle);
Console.WriteLine("\t\t Right = " + (Excel.XlLineStyle)cell.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle);
Console.WriteLine("\t\t Top = " + (Excel.XlLineStyle)cell.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle);
Console.WriteLine("\t\t Bottom = " + (Excel.XlLineStyle)cell.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle);
//xlRgbColor에 기록된 컬러만 이용해야 한다.
Excel.XlRgbColor cellColor = (Excel.XlRgbColor)cell.Interior.Color;
Console.WriteLine("\t Cell Color = " + cellColor);
Excel.XlRgbColor fontColor = (Excel.XlRgbColor)cell.Font.Color;
Console.WriteLine("\t Font Color = " + fontColor);
bool merged = (bool)cell.MergeCells;
Console.WriteLine("\tMerged?\t" + merged);
if (merged)
{
Excel.Range mergeArea = (Excel.Range)cell.MergeArea;
Console.WriteLine("\tMerged Area = " + mergeArea.Address);
}
Excel.XlOrientation orientation = (Excel.XlOrientation)cell.Orientation;
Console.WriteLine("\tOrientation(Text Angle) = " + orientation);
}
SetText(this.textBox1, Target.Address);
}
private void writeToMultipleCells(Location startRangeLocation, Location endRangeLocation, string value, int mode, int cellType, double cellWidth, double cellHeight, bool boldFlag, bool italicsFlag, bool wrapTextFlag, System.Drawing.Color cellColour,
System.Drawing.Color textColour, Excel.XlHAlign horizAlignment, Excel.XlVAlign vertAlignment, String fontName, int fontSize)
{
int startRow = startRangeLocation.getRow();
int startCol = startRangeLocation.getColumn();
int endRow = endRangeLocation.getRow();
int endCol = endRangeLocation.getColumn();
string startRange = getExcelColumnName(startCol) + startRow.ToString();
string endRange = getExcelColumnName(endCol) + endRow.ToString();
if (mode == 1 || mode == 2)
{
Console.WriteLine("Writing to " + startCol + " (" + startRange + ":" + endRange + ") " + value);
}
if (mode == 0 || mode == 2)
{
if (cellWidth > 0)
{
currentSheet.Columns[startCol].ColumnWidth = cellWidth;
}
if (cellHeight > 0)
{
currentSheet.Rows[startRow].RowHeight = cellHeight;
}
Excel.Range r = currentSheet.get_Range(startRange, endRange); //Excel.Range r = newTableSheet.Cells[startRow, startCol] as Excel.Range;
r.Interior.Color = System.Drawing.ColorTranslator.ToOle(cellColour);
r.Font.Color = System.Drawing.ColorTranslator.ToOle(textColour);
r.Font.Bold = boldFlag;
r.Font.Italic = italicsFlag;
r.WrapText = wrapTextFlag;
r.HorizontalAlignment = horizAlignment;
r.VerticalAlignment = vertAlignment;
r.Font.Name = fontName;
r.Font.Size = fontSize;
if (value != "" && value != null)
{
r.Cells.Value = value;
}
}
}
/// <summary>
/// Sets the horizontal alignment for the specified cells on the specified sheet of the specified workbook.
/// </summary>
/// <param name="workbookName">The stored index of the workbook.</param>
/// <param name="sheetIndex">The sheet number you wish to alter (note: excel sheets begin with number 1, not 0).</param>
/// <param name="cellSelection">Indicates the cells to be changed. Works just like Excel. Example: "A:A,1:1,A2:B3,F4,F5"</param>
/// <param name="alignment">The horizontal justication enumeration.</param>
public void ChangeHorizontalAlignment(string workbookName, int sheetIndex, string cellSelection, Microsoft.Office.Interop.Excel.XlHAlign alignment)
{
List <Range> rangesToAlter = GetRanges(workbookName, sheetIndex, cellSelection);
foreach (Range rangeToAlter in rangesToAlter)
{
rangeToAlter.HorizontalAlignment = alignment;
}
}
/// <summary>
/// Sets the horizontal alignment for the specified cells on the default sheet of the default workbook.
/// </summary>
/// <param name="cellSelection">Indicates the cells to be changed. Works just like Excel. Example: "A:A,1:1,A2:B3,F4,F5"</param>
/// <param name="alignment">The horizontal justication enumeration.</param>
public void ChangeHorizontalAlignment(string cellSelection, Microsoft.Office.Interop.Excel.XlHAlign alignment)
{
ChangeHorizontalAlignment(ReportGlobals.defaultWorkbookName, ReportGlobals.defaultSheetIndex, cellSelection, alignment);
}
} // xlFontName, xlFontSize
private void SetValueCell(XLExcel.Range xlRange, Object xlValue, Boolean xlMergeCells = false, XLExcel.XlVAlign xlVAlign = XLExcel.XlVAlign.xlVAlignTop, XLExcel.XlHAlign xlHAlign = XLExcel.XlHAlign.xlHAlignLeft, Boolean xlBold = false, Boolean xlUnderline = false) //String xlFontName, Int32 xlFontSize;
{
SetValue(xlRange, xlValue, xlMergeCells, xlVAlign, xlHAlign, xlBold, xlUnderline);
} //, xlFontName, xlFontSize
public void AddDataWithBackgroundColor(int row, int col, string data, string cell1, string cell2, string format, Excel.XlHAlign alignment)
{
xlWorkSheet.Cells[row, col].HorizontalAlignment = alignment;
xlWorkSheet.Cells[row, col] = data;
workSheet_range = xlWorkSheet.get_Range(cell1, cell2);
workSheet_range.Cells.Interior.Color = System.Drawing.Color.Orange;
workSheet_range.NumberFormat = format;
}
/// <summary>
/// 单元格文字对齐方式
/// </summary>
/// <param name="startRow">起始行</param>
/// <param name="startColumn">起始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endColumn">结束列</param>
/// <param name="hAlign">水平对齐</param>
/// <param name="vAlign">垂直对齐</param>
public void CellsAlignment(int startRow, int startColumn, int endRow, int endColumn, Microsoft.Office.Interop.Excel.XlHAlign hAlign, Microsoft.Office.Interop.Excel.XlVAlign vAlign)
{
Range range = m_pExcelApp.get_Range(m_pExcelApp.Cells[startRow, startColumn], m_pExcelApp.Cells[endRow, endColumn]);
range.HorizontalAlignment = hAlign;
range.VerticalAlignment = vAlign;
//作者:董兰芳 日期:2009年2月4日17:17:06 描述:
Marshal.ReleaseComObject(range);
}
public void SetHorizontalA1ignmentInRange(string range, ExcelFile.XlHAlign align)
{
_excel.get_Range(range).HorizontalAlignment = align;
}
//R1 and R2 are ranges of cells and rows, FontSize, FontName, VAling and HAling changes the font size, font name, vertical and horizontal align of the selected range
public void FormatHelper(Excel.Worksheet xlWorkSheet, string R1, string R2, int FontSize, string FontName, Excel.XlVAlign VAlign, Excel.XlHAlign HAlign)
{
xlWorkSheet.Range[R1, R2].Font.Size = FontSize;
xlWorkSheet.Range[R1, R2].Font.Name = FontName;
xlWorkSheet.Range[R1, R2].Cells.VerticalAlignment = VAlign;
xlWorkSheet.Range[R1, R2].Cells.HorizontalAlignment = HAlign;
}
public void AddHeaderData(int row, int col, string data, string cell1, string cell2, bool isMergeCells, bool isFontBold, string fontName, int fontSize, Excel.XlHAlign alignment, string format)
{
xlWorkSheet.Cells[row, col] = data;
xlWorkSheet.Cells[row, col].HorizontalAlignment = alignment;
Excel.Range range = xlWorkSheet.get_Range(cell1, cell2);
range.NumberFormat = format;
range.MergeCells = isMergeCells;
range.Font.Name = fontName;
range.Font.Bold = isFontBold;
range.Font.Size = fontSize;
range.WrapText = true;
//range.AutoFilter("1", "<>", Microsoft.Office.Interop.Excel.XlAutoFilterOperator.xlOr, "", true);
}
public void AddHeaderDataWithBackgroundColor(int row, int col, string data, string cell1, string cell2, bool isMergeCells, bool isFontBold, string fontName, int fontSize, Excel.XlHAlign alignment)
{
xlWorkSheet.Cells[row, col] = data;
xlWorkSheet.Cells[row, col].HorizontalAlignment = alignment;
Excel.Range range = xlWorkSheet.get_Range(cell1, cell2);
range.NumberFormat = "Text";
range.MergeCells = isMergeCells;
range.Font.Name = fontName;
range.Font.Bold = isFontBold;
range.Font.Size = fontSize;
range.WrapText = true;
range.Cells.Interior.Color = System.Drawing.Color.Orange;
//range.NumberFormat = "Text";
}
public void SetAliment(int startRow, int startCol, int endRow, int endCol, Excel.XlHAlign horizontalAlignment, Excel.XlVAlign verticalAlignment)
{
this.SetRange(startRow, startCol, endRow, endCol);
this.SetAliment(horizontalAlignment, verticalAlignment);
}
public void SetAliment(int row, int col, Excel.XlHAlign horizontalAlignment, Excel.XlVAlign verticalAlignment)
{
this.SetRange(row, col);
this.SetAliment(horizontalAlignment, verticalAlignment);
}
public void SetAliment(Excel.XlHAlign horizontalAlignment, Excel.XlVAlign verticalAlignment)
{
this._range.HorizontalAlignment = horizontalAlignment;
this._range.VerticalAlignment = verticalAlignment;
}
public void criaCelulas(int pLinha, int pColuna, string pTexto, string pCelula1, string pCelula2, int pColunasMerge,
System.Drawing.Color pCorFundo, bool pNegrito, int pTamanhoCelula, System.Drawing.Color pCorLetra, Excel.XlHAlign pAlinhamentoH,
Excel.XlVAlign pAlinhamentoV, string pNomeFonte)
{
excelWorkSheetRange = excelWorksheet.get_Range(pCelula1, pCelula2);
excelWorkSheetRange.Merge(pColunasMerge);
excelWorkSheetRange.NumberFormat = "@";
excelWorksheet.Cells[pLinha, pColuna] = pTexto;
excelWorkSheetRange.VerticalAlignment = pAlinhamentoV;
excelWorkSheetRange.HorizontalAlignment = pAlinhamentoH;
excelWorkSheetRange.Interior.Color = pCorFundo.ToArgb();
excelWorkSheetRange.Borders.Color = System.Drawing.Color.Black.ToArgb();
excelWorkSheetRange.Font.Bold = pNegrito;
excelWorkSheetRange.Font.Name = pNomeFonte;
excelWorkSheetRange.ColumnWidth = pTamanhoCelula;
excelWorkSheetRange.Font.Color = pCorLetra.ToArgb();
}
public void SetHorizontalAlignment(Excel.XlHAlign horizontalAlignment)
{
_HorizontalAlignment = horizontalAlignment;
}
public TextAlignment(Excel.XlHAlign alignment, bool entireColumn)
{
_entireColumn = entireColumn;
_alignment = alignment;
}
public void SetAlignment(int iStartRow, int iStartCol, int iEndRow, int iEndCol, Microsoft.Office.Interop.Excel.XlHAlign hAlign, Microsoft.Office.Interop.Excel.XlVAlign vAlign)
{
Range objRange = m_objExcel.Range[m_objExcel.Cells[iStartRow, iStartCol], m_objExcel.Cells[iEndRow, iEndCol]];
objRange.HorizontalAlignment = hAlign;
objRange.VerticalAlignment = vAlign;
}
public xlsf SetHorztlAlgmet(Excel.XlHAlign AlignType)
{
CurrCell.HorizontalAlignment = AlignType;
return(this);
}
private void SetValue(XLExcel.Range xlRange, Object xlValue, Boolean xlMergeCells = false, XLExcel.XlVAlign xlVAlign = XLExcel.XlVAlign.xlVAlignCenter, XLExcel.XlHAlign xlHAlign = XLExcel.XlHAlign.xlHAlignCenter, Boolean xlBold = false, Boolean xlUnderline = false)//String xlFontName, Int32 xlFontSize,
{
xlRange.MergeCells = xlMergeCells;
xlRange.Value = xlValue;
//xlRange.Font.Name = xlFontName;
//xlRange.Font.Size = xlFontSize;
xlRange.Font.Bold = xlBold;
xlRange.Font.Underline = xlUnderline;
xlRange.VerticalAlignment = xlVAlign;
xlRange.HorizontalAlignment = xlHAlign;
}
private void SetValueHeader(XLExcel.Range xlRange, Object xlValue, Boolean xlMergeCells = true, XLExcel.XlVAlign xlVAlign = XLExcel.XlVAlign.xlVAlignCenter, XLExcel.XlHAlign xlHAlign = XLExcel.XlHAlign.xlHAlignCenter, Boolean xlBold = true, Boolean xlUnderline = false)//String xlFontName, Int32 xlFontSize,
{
SetValue(xlRange, xlValue, xlMergeCells, xlVAlign, xlHAlign, xlBold, xlUnderline);
} // xlFontName, xlFontSize
public void AddData(int row, int col, string data, string cell1, string cell2, string format, Excel.XlHAlign alignment)
{
xlWorkSheet.Cells[row, col].HorizontalAlignment = alignment;
xlWorkSheet.Cells[row, col] = data;
workSheet_range = xlWorkSheet.get_Range(cell1, cell2);
workSheet_range.NumberFormat = format;
}
