public override void FormatRange(ExternalExcelRangeFormatInfo formatInfo)
{
ModuleProc PROC = new ModuleProc(this.DYN_MODULE_NAME, "Method");
Range range = null;
Microsoft.Office.Interop.Excel.Font font = null;
Borders borders = null;
Interior interior = null;
Range rows = null;
Range columns = null;
Range entireRow = null;
Range entireColumn = null;
Validation validation = null;
try
{
range = this.GetRange(formatInfo);
if (range == null) return;
// colors
font = range.Font;
borders = range.Borders;
interior = range.Interior;
if (formatInfo.ForeColor != Color.Empty) font.ColorIndex = ExternalExcelColorMappings.GetColor(formatInfo.ForeColor.ToArgb());
if (formatInfo.BackColor != Color.Empty) interior.ColorIndex = ExternalExcelColorMappings.GetColor(formatInfo.BackColor.ToArgb());
if (formatInfo.BorderColor != Color.Empty) borders.ColorIndex = ExternalExcelColorMappings.GetColor(formatInfo.BorderColor.ToArgb());
// font
if (formatInfo.Font != null)
{
font.Name = formatInfo.Font.Name;
font.Bold = formatInfo.Font.Bold;
font.Size = formatInfo.Font.Size;
}
// row height and column width
if (formatInfo.AutoFitRows)
{
rows = range.Rows;
rows.AutoFit();
}
if (formatInfo.AutoFitColumns)
{
columns = range.Columns;
columns.AutoFit();
}
if (formatInfo.RowHeight > 0)
{
entireRow = range.EntireRow;
entireRow.RowHeight = formatInfo.RowHeight;
}
if (formatInfo.ColumnWidth > 0)
{
entireColumn = range.EntireColumn;
entireColumn.ColumnWidth = formatInfo.ColumnWidth;
}
// text
range.WrapText = formatInfo.WrapText;
if (!formatInfo.Text.IsEmpty())
{
range.Value2 = formatInfo.Text;
}
// merge
if (formatInfo.Merge) range.Merge(0);
// horizontal and vertical alignment
range.HorizontalAlignment = (XlHAlign)formatInfo.HorizontalAlignment;
range.VerticalAlignment = (XlVAlign)formatInfo.VerticalAlignment;
// auto filter
if (formatInfo.AutoFilter)
{
range.AutoFilter(formatInfo.StartColumn);
}
// custom format
if (!formatInfo.CustomFormat.IsEmpty())
{
range.NumberFormat = formatInfo.CustomFormat;
}
if (formatInfo.ValidationType != ExternalExcelValidationType.ValidationNone)
{
validation = range.Validation;
validation.Delete();
object formula1 = Type.Missing;
object formula2 = Type.Missing;
if (formatInfo.ValidationFormat.Length > 0) formula1 = formatInfo.ValidationFormat[0];
if (formatInfo.ValidationFormat.Length > 1) formula2 = formatInfo.ValidationFormat[1];
switch (formatInfo.ValidationType)
{
case ExternalExcelValidationType.ValidateInputOnly:
break;
case ExternalExcelValidationType.ValidateWholeNumber:
break;
case ExternalExcelValidationType.ValidateDecimal:
{
validation.Add(XlDVType.xlValidateDecimal, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlBetween, formula1, formula2);
validation.ErrorMessage = string.Format("Please enter the value between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateList:
{
validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlBetween, formula1);
validation.ErrorMessage = string.Format("Please enter the valid range of values.");
}
break;
case ExternalExcelValidationType.ValidateDate:
{
validation.Add(XlDVType.xlValidateDate, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlBetween, formula1, formula2);
validation.ErrorMessage = string.Format("Please enter the date between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateTime:
break;
case ExternalExcelValidationType.ValidateTextLength:
{
validation.Add(XlDVType.xlValidateTextLength, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlLessEqual, formula1);
validation.ErrorMessage = string.Format("Please enter the value with max length of {0:D}", formula1.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateCustom:
break;
default:
break;
}
validation.ErrorTitle = formatInfo.ColumName;
}
}
catch (Exception ex)
{
Log.Exception(PROC, ex);
}
finally
{
Extensions.DisposeComObject(validation);
Extensions.DisposeComObject(rows);
Extensions.DisposeComObject(columns);
Extensions.DisposeComObject(entireRow);
Extensions.DisposeComObject(entireColumn);
Extensions.DisposeComObject(borders);
Extensions.DisposeComObject(interior);
Extensions.DisposeComObject(font);
Extensions.DisposeComObject(range);
}
}
public override void FormatRange(ExternalExcelRangeFormatInfo formatInfo)
{
ModuleProc PROC = new ModuleProc(this.DYN_MODULE_NAME, "Method");
try
{
XLRange range = this.GetRange(formatInfo);
if (range == null) return;
// colors
if (formatInfo.ForeColor != Color.Empty) range.Style.Font.FontColor = XLColor.FromColor(formatInfo.ForeColor);
if (formatInfo.BackColor != Color.Empty) range.Style.Fill.BackgroundColor = XLColor.FromColor(formatInfo.BackColor);
if (formatInfo.BorderColor != Color.Empty) range.Style.Border.OutsideBorderColor = XLColor.FromColor(formatInfo.BorderColor);
// font
if (formatInfo.Font != null)
{
range.Style.Font.FontName = formatInfo.Font.Name;
range.Style.Font.Bold = formatInfo.Font.Bold;
range.Style.Font.FontSize = formatInfo.Font.Size;
}
// row height and column width
//if (formatInfo.AutoFitRows) range.Rows().AutoFit();
//if (formatInfo.AutoFitColumns) range.Columns.AutoFit();
if (formatInfo.RowHeight > 0) range.Worksheet.RowHeight = formatInfo.RowHeight;
if (formatInfo.ColumnWidth > 0 && formatInfo.ColumnIndex>0)
{
range.Column(formatInfo.ColumnIndex).WorksheetColumn().Width = formatInfo.ColumnWidth;
}
// text
range.Style.Alignment.WrapText = formatInfo.WrapText;
if (!formatInfo.Text.IsEmpty())
{
range.Value = formatInfo.Text;
}
// merge
if (formatInfo.Merge) range.Merge();
// horizontal and vertical alignment
switch (formatInfo.HorizontalAlignment)
{
case ExternalExcelCellHAlign.AlignRight:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
case ExternalExcelCellHAlign.AlignLeft:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
break;
case ExternalExcelCellHAlign.AlignJustify:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Justify;
break;
case ExternalExcelCellHAlign.AlignDistributed:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Distributed;
break;
case ExternalExcelCellHAlign.AlignCenter:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
break;
case ExternalExcelCellHAlign.AlignGeneral:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.General;
break;
case ExternalExcelCellHAlign.AlignFill:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Fill;
break;
case ExternalExcelCellHAlign.AlignCenterAcrossSelection:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
break;
default:
break;
}
switch (formatInfo.VerticalAlignment)
{
case ExternalExcelCellVAlign.AlignTop:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
break;
case ExternalExcelCellVAlign.AlignJustify:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Justify;
break;
case ExternalExcelCellVAlign.AlignDistributed:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Distributed;
break;
case ExternalExcelCellVAlign.AlignCenter:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
break;
case ExternalExcelCellVAlign.AlignBottom:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Bottom;
break;
default:
break;
}
// auto filter
if (formatInfo.AutoFilter)
{
range.SetAutoFilter();
}
// custom format
if (!formatInfo.CustomFormat.IsEmpty())
{
range.Style.NumberFormat.Format = formatInfo.CustomFormat;
}
if (formatInfo.ValidationType != ExternalExcelValidationType.ValidationNone)
{
var validation = range.DataValidation;
validation.Clear();
object formula1 = Type.Missing;
object formula2 = Type.Missing;
if (formatInfo.ValidationFormat.Length > 0) formula1 = formatInfo.ValidationFormat[0];
if (formatInfo.ValidationFormat.Length > 1) formula2 = formatInfo.ValidationFormat[1];
switch (formatInfo.ValidationType)
{
case ExternalExcelValidationType.ValidateInputOnly:
break;
case ExternalExcelValidationType.ValidateWholeNumber:
break;
case ExternalExcelValidationType.ValidateDecimal:
{
validation.Decimal.Between(Convert.ToDouble(formula1), Convert.ToDouble(formula2));
validation.ErrorMessage = string.Format("Please enter the value between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateList:
//validations.List(formatInfo.ValidationFormat);
break;
case ExternalExcelValidationType.ValidateDate:
{
validation.Date.Between(Convert.ToDateTime(formula1), Convert.ToDateTime(formula2));
validation.ErrorMessage = string.Format("Please enter the date between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateTime:
break;
case ExternalExcelValidationType.ValidateTextLength:
{
validation.TextLength.EqualOrLessThan(Convert.ToInt32(formula1));
validation.ErrorMessage = string.Format("Please enter the value with max length of {0:D}", formula1.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateCustom:
break;
default:
break;
}
validation.ErrorTitle = formatInfo.ColumName;
}
}
catch (Exception ex)
{
Log.Exception(PROC, ex);
}
}
public override void FormatRange(ExternalExcelRangeFormatInfo formatInfo) { }
public override void FormatRange(ExternalExcelRangeFormatInfo formatInfo)
{
ModuleProc PROC = new ModuleProc(this.DYN_MODULE_NAME, "Method");
Range range = null;
Microsoft.Office.Interop.Excel.Font font = null;
Borders borders = null;
Interior interior = null;
Range rows = null;
Range columns = null;
Range entireRow = null;
Range entireColumn = null;
Validation validation = null;
try
{
range = this.GetRange(formatInfo);
if (range == null)
{
return;
}
// colors
font = range.Font;
borders = range.Borders;
interior = range.Interior;
if (formatInfo.ForeColor != Color.Empty)
{
font.ColorIndex = ExternalExcelColorMappings.GetColor(formatInfo.ForeColor.ToArgb());
}
if (formatInfo.BackColor != Color.Empty)
{
interior.ColorIndex = ExternalExcelColorMappings.GetColor(formatInfo.BackColor.ToArgb());
}
if (formatInfo.BorderColor != Color.Empty)
{
borders.ColorIndex = ExternalExcelColorMappings.GetColor(formatInfo.BorderColor.ToArgb());
}
// font
if (formatInfo.Font != null)
{
font.Name = formatInfo.Font.Name;
font.Bold = formatInfo.Font.Bold;
font.Size = formatInfo.Font.Size;
}
// row height and column width
if (formatInfo.AutoFitRows)
{
rows = range.Rows;
rows.AutoFit();
}
if (formatInfo.AutoFitColumns)
{
columns = range.Columns;
columns.AutoFit();
}
if (formatInfo.RowHeight > 0)
{
entireRow = range.EntireRow;
entireRow.RowHeight = formatInfo.RowHeight;
}
if (formatInfo.ColumnWidth > 0)
{
entireColumn = range.EntireColumn;
entireColumn.ColumnWidth = formatInfo.ColumnWidth;
}
// text
range.WrapText = formatInfo.WrapText;
if (!formatInfo.Text.IsEmpty())
{
range.Value2 = formatInfo.Text;
}
// merge
if (formatInfo.Merge)
{
range.Merge(0);
}
// horizontal and vertical alignment
range.HorizontalAlignment = (XlHAlign)formatInfo.HorizontalAlignment;
range.VerticalAlignment = (XlVAlign)formatInfo.VerticalAlignment;
// auto filter
if (formatInfo.AutoFilter)
{
range.AutoFilter(formatInfo.StartColumn);
}
// custom format
if (!formatInfo.CustomFormat.IsEmpty())
{
range.NumberFormat = formatInfo.CustomFormat;
}
if (formatInfo.ValidationType != ExternalExcelValidationType.ValidationNone)
{
validation = range.Validation;
validation.Delete();
object formula1 = Type.Missing;
object formula2 = Type.Missing;
if (formatInfo.ValidationFormat.Length > 0)
{
formula1 = formatInfo.ValidationFormat[0];
}
if (formatInfo.ValidationFormat.Length > 1)
{
formula2 = formatInfo.ValidationFormat[1];
}
switch (formatInfo.ValidationType)
{
case ExternalExcelValidationType.ValidateInputOnly:
break;
case ExternalExcelValidationType.ValidateWholeNumber:
break;
case ExternalExcelValidationType.ValidateDecimal:
{
validation.Add(XlDVType.xlValidateDecimal, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlBetween, formula1, formula2);
validation.ErrorMessage = string.Format("Please enter the value between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateList:
{
validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlBetween, formula1);
validation.ErrorMessage = string.Format("Please enter the valid range of values.");
}
break;
case ExternalExcelValidationType.ValidateDate:
{
validation.Add(XlDVType.xlValidateDate, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlBetween, formula1, formula2);
validation.ErrorMessage = string.Format("Please enter the date between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateTime:
break;
case ExternalExcelValidationType.ValidateTextLength:
{
validation.Add(XlDVType.xlValidateTextLength, XlDVAlertStyle.xlValidAlertStop,
XlFormatConditionOperator.xlLessEqual, formula1);
validation.ErrorMessage = string.Format("Please enter the value with max length of {0:D}", formula1.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateCustom:
break;
default:
break;
}
validation.ErrorTitle = formatInfo.ColumName;
}
}
catch (Exception ex)
{
Log.Exception(PROC, ex);
}
finally
{
Extensions.DisposeComObject(validation);
Extensions.DisposeComObject(rows);
Extensions.DisposeComObject(columns);
Extensions.DisposeComObject(entireRow);
Extensions.DisposeComObject(entireColumn);
Extensions.DisposeComObject(borders);
Extensions.DisposeComObject(interior);
Extensions.DisposeComObject(font);
Extensions.DisposeComObject(range);
}
}
public override void FormatRange(ExternalExcelRangeFormatInfo formatInfo)
{
ModuleProc PROC = new ModuleProc(this.DYN_MODULE_NAME, "Method");
try
{
XLRange range = this.GetRange(formatInfo);
if (range == null)
{
return;
}
// colors
if (formatInfo.ForeColor != Color.Empty)
{
range.Style.Font.FontColor = XLColor.FromColor(formatInfo.ForeColor);
}
if (formatInfo.BackColor != Color.Empty)
{
range.Style.Fill.BackgroundColor = XLColor.FromColor(formatInfo.BackColor);
}
if (formatInfo.BorderColor != Color.Empty)
{
range.Style.Border.OutsideBorderColor = XLColor.FromColor(formatInfo.BorderColor);
}
// font
if (formatInfo.Font != null)
{
range.Style.Font.FontName = formatInfo.Font.Name;
range.Style.Font.Bold = formatInfo.Font.Bold;
range.Style.Font.FontSize = formatInfo.Font.Size;
}
// row height and column width
//if (formatInfo.AutoFitRows) range.Rows().AutoFit();
//if (formatInfo.AutoFitColumns) range.Columns.AutoFit();
if (formatInfo.RowHeight > 0)
{
range.Worksheet.RowHeight = formatInfo.RowHeight;
}
if (formatInfo.ColumnWidth > 0 && formatInfo.ColumnIndex > 0)
{
range.Column(formatInfo.ColumnIndex).WorksheetColumn().Width = formatInfo.ColumnWidth;
}
// text
range.Style.Alignment.WrapText = formatInfo.WrapText;
if (!formatInfo.Text.IsEmpty())
{
range.Value = formatInfo.Text;
}
// merge
if (formatInfo.Merge)
{
range.Merge();
}
// horizontal and vertical alignment
switch (formatInfo.HorizontalAlignment)
{
case ExternalExcelCellHAlign.AlignRight:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
break;
case ExternalExcelCellHAlign.AlignLeft:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
break;
case ExternalExcelCellHAlign.AlignJustify:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Justify;
break;
case ExternalExcelCellHAlign.AlignDistributed:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Distributed;
break;
case ExternalExcelCellHAlign.AlignCenter:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
break;
case ExternalExcelCellHAlign.AlignGeneral:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.General;
break;
case ExternalExcelCellHAlign.AlignFill:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Fill;
break;
case ExternalExcelCellHAlign.AlignCenterAcrossSelection:
range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
break;
default:
break;
}
switch (formatInfo.VerticalAlignment)
{
case ExternalExcelCellVAlign.AlignTop:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
break;
case ExternalExcelCellVAlign.AlignJustify:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Justify;
break;
case ExternalExcelCellVAlign.AlignDistributed:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Distributed;
break;
case ExternalExcelCellVAlign.AlignCenter:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
break;
case ExternalExcelCellVAlign.AlignBottom:
range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Bottom;
break;
default:
break;
}
// auto filter
if (formatInfo.AutoFilter)
{
range.SetAutoFilter();
}
// custom format
if (!formatInfo.CustomFormat.IsEmpty())
{
range.Style.NumberFormat.Format = formatInfo.CustomFormat;
}
if (formatInfo.ValidationType != ExternalExcelValidationType.ValidationNone)
{
var validation = range.DataValidation;
validation.Clear();
object formula1 = Type.Missing;
object formula2 = Type.Missing;
if (formatInfo.ValidationFormat.Length > 0)
{
formula1 = formatInfo.ValidationFormat[0];
}
if (formatInfo.ValidationFormat.Length > 1)
{
formula2 = formatInfo.ValidationFormat[1];
}
switch (formatInfo.ValidationType)
{
case ExternalExcelValidationType.ValidateInputOnly:
break;
case ExternalExcelValidationType.ValidateWholeNumber:
break;
case ExternalExcelValidationType.ValidateDecimal:
{
validation.Decimal.Between(Convert.ToDouble(formula1), Convert.ToDouble(formula2));
validation.ErrorMessage = string.Format("Please enter the value between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateList:
//validations.List(formatInfo.ValidationFormat);
break;
case ExternalExcelValidationType.ValidateDate:
{
validation.Date.Between(Convert.ToDateTime(formula1), Convert.ToDateTime(formula2));
validation.ErrorMessage = string.Format("Please enter the date between {0} and {1}", formula1.ToStringSafe(), formula2.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateTime:
break;
case ExternalExcelValidationType.ValidateTextLength:
{
validation.TextLength.EqualOrLessThan(Convert.ToInt32(formula1));
validation.ErrorMessage = string.Format("Please enter the value with max length of {0:D}", formula1.ToStringSafe());
}
break;
case ExternalExcelValidationType.ValidateCustom:
break;
default:
break;
}
validation.ErrorTitle = formatInfo.ColumName;
}
}
catch (Exception ex)
{
Log.Exception(PROC, ex);
}
}
