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);
            }
        }
Пример #5
0
        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);
            }
        }