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