EnableControls()
        {
            AssertValid();

            ExcelColumnFormat eSelectedVertexColumnFormat =
                this.SelectedVertexColumnFormat;

            Boolean bEnableMinimumValueToAddTextBox    = false;
            SimpleDateTimeFormat eSimpleDateTimeFormat = SimpleDateTimeFormat.Date;

            switch (eSelectedVertexColumnFormat)
            {
            case ExcelColumnFormat.Other:
            case ExcelColumnFormat.Number:

                bEnableMinimumValueToAddTextBox = true;
                break;

            case ExcelColumnFormat.Date:

                break;

            case ExcelColumnFormat.Time:

                eSimpleDateTimeFormat = SimpleDateTimeFormat.Time;
                break;

            case ExcelColumnFormat.DateAndTime:

                eSimpleDateTimeFormat = SimpleDateTimeFormat.DateAndTime;
                break;

            default:

                Debug.Assert(false);
                break;
            }

            // Note that the txbMinimumValueToAdd TextBox and the
            // dtpMinimumValueToAdd DateTimePicker have the same size and location.
            // Only one is visible at any given time.

            txbMinimumValueToAdd.Enabled = txbMinimumValueToAdd.Visible =
                bEnableMinimumValueToAddTextBox;

            dtpMinimumValueToAdd.Enabled = dtpMinimumValueToAdd.Visible =
                !bEnableMinimumValueToAddTextBox;

            dtpMinimumValueToAdd.SimpleFormat = eSimpleDateTimeFormat;

            btnRemoveSelected.Enabled =
                (lbxMinimumValues.SelectedIndices.Count > 0);

            btnRemoveAll.Enabled = (lbxMinimumValues.Items.Count > 0);

            grpMinimumValues.Enabled =
                !String.IsNullOrEmpty(cbxVertexColumnName.Text) &&
                (eSelectedVertexColumnFormat != ExcelColumnFormat.Other);
        }
Exemple #2
0
        DateTimeToString
        (
            Int64 ticks,
            ExcelColumnFormat format
        )
        {
            AssertValid();

            return(DateTimeToString(new DateTime(ticks), format));
        }
Exemple #3
0
        DateTimeToString
        (
            DateTime dateTime,
            ExcelColumnFormat format
        )
        {
            AssertValid();

            return(DateTimeToString(dateTime, format,
                                    CultureInfo.CurrentCulture));
        }
Exemple #4
0
        DateTimeToStringLocale1033
        (
            DateTime dateTime,
            ExcelColumnFormat format
        )
        {
            AssertValid();

            return(DateTimeToString(dateTime, format,
                                    CultureInfo.GetCultureInfo(1033)));
        }
        //*************************************************************************
        //  Constructor: DateTimeFilterParameters()
        //
        /// <summary>
        /// Initializes a new instance of the <see
        /// cref="DateTimeFilterParameters" /> class.
        /// </summary>
        ///
        /// <param name="columnName">
        /// Name of the column that can be filtered on.
        /// </param>
        ///
        /// <param name="minimumCellValue">
        /// Minimum cell value in the column, in Excel's date/time format.  Sample:
        /// 39448.583333 (1/1/2008, 2 PM).
        /// </param>
        ///
        /// <param name="maximumCellValue">
        /// Maximum cell value in the column, in Excel's date/time format.
        /// </param>
        ///
        /// <param name="format">
        /// The column format.
        /// </param>
        //*************************************************************************
        public DateTimeFilterParameters(
            String columnName,
            Double minimumCellValue,
            Double maximumCellValue,
            ExcelColumnFormat format
            )
            : base(columnName, minimumCellValue, maximumCellValue, 0)
        {
            m_eFormat = format;

            AssertValid();
        }
        //*************************************************************************
        //  Constructor: DateTimeFilterParameters()
        //
        /// <summary>
        /// Initializes a new instance of the <see
        /// cref="DateTimeFilterParameters" /> class.
        /// </summary>
        ///
        /// <param name="columnName">
        /// Name of the column that can be filtered on.
        /// </param>
        ///
        /// <param name="minimumCellValue">
        /// Minimum cell value in the column, in Excel's date/time format.  Sample:
        /// 39448.583333 (1/1/2008, 2 PM).
        /// </param>
        ///
        /// <param name="maximumCellValue">
        /// Maximum cell value in the column, in Excel's date/time format.
        /// </param>
        ///
        /// <param name="format">
        /// The column format.
        /// </param>
        //*************************************************************************

        public DateTimeFilterParameters
        (
            String columnName,
            Double minimumCellValue,
            Double maximumCellValue,
            ExcelColumnFormat format
        )
            : base(columnName, minimumCellValue, maximumCellValue, 0)
        {
            m_eFormat = format;

            AssertValid();
        }
        /// <summary>
        /// Applies the format to range.
        /// </summary>
        /// <param name="range">The range.</param>
        /// <param name="format">The format.</param>
        private static void ApplyFormatToRange(Excel.Range range, ExcelColumnFormat format)
        {
            switch (format)
            {
            case ExcelColumnFormat.Accounting:
                range.NumberFormat = @"_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)";
                break;

            case ExcelColumnFormat.Currency:
                range.NumberFormat = "$#,##0.00";
                break;

            case ExcelColumnFormat.Fraction:
                range.NumberFormat = "# ?/?";
                break;

            case ExcelColumnFormat.LongDate:
                range.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy";
                break;

            case ExcelColumnFormat.Number:
                range.NumberFormat = "0.00";
                break;

            case ExcelColumnFormat.Percentage:
                range.NumberFormat = "0.00%";
                break;

            case ExcelColumnFormat.Scientific:
                range.NumberFormat = "0.00E+00";
                break;

            case ExcelColumnFormat.ShortDate:
                range.NumberFormat = "m/d/yyyy";
                break;

            case ExcelColumnFormat.Text:
                range.NumberFormat = "@";
                break;

            case ExcelColumnFormat.Time:
                range.NumberFormat = "[$-F400]h:mm:ss AM/PM";
                break;

            case ExcelColumnFormat.General:
            default:
                range.NumberFormat = "General";
                break;
            }
        }
Exemple #8
0
        GetEdgeTimestampResults
        (
            out String edgeTimestampColumnName,
            out ExcelColumnFormat edgeTimestampColumnFormat,
            out Double sourceCalculationNumber1,
            out Double sourceCalculationNumber2
        )
        {
            Debug.Assert(m_eSchemeType == AutoFillSchemeType.EdgeTimestamp);
            AssertValid();

            edgeTimestampColumnName   = m_sEdgeTimestampColumnName;
            edgeTimestampColumnFormat = m_eEdgeTimestampColumnFormat;
            sourceCalculationNumber1  = m_dEdgeTimestampSourceCalculationNumber1;
            sourceCalculationNumber2  = m_dEdgeTimestampSourceCalculationNumber2;
        }
Exemple #9
0
        public object ConvertStringToValue(string value, Type type, ExcelColumnFormat format = ExcelColumnFormat.None)
        {
            var    stringFormat  = GetFormatByFormatType(format);
            object propertyValue = null;

            if (!string.IsNullOrEmpty(value))
            {
                if (type == typeof(DateTime) || type == typeof(DateTime?))
                {
                    propertyValue = value.ToDateFormat(stringFormat);
                }
                else if (type == typeof(DateRange))
                {
                    propertyValue = value.ToDateRangeFormat(stringFormat);
                }
                else if (type == typeof(TimeSpan) || type == typeof(TimeSpan?))
                {
                    propertyValue = value.ToTimeFormat(stringFormat);
                }
                else if (type == typeof(bool) || type == typeof(bool?))
                {
                    propertyValue = value.ToBoolFormat();
                }
                else if (type == typeof(decimal) || type == typeof(decimal?))
                {
                    decimal decimalValue;
                    if (decimal.TryParse(value, out decimalValue))
                    {
                        propertyValue = decimalValue;
                    }
                }
                else if (type == typeof(double) || type == typeof(double?))
                {
                    double doubleValue;
                    if (double.TryParse(value, out doubleValue))
                    {
                        propertyValue = doubleValue;
                    }
                }
                else
                {
                    propertyValue = value;
                }
            }

            return(propertyValue);
        }
Exemple #10
0
        SetEdgeTimestampResults
        (
            String edgeTimestampColumnName,
            ExcelColumnFormat edgeTimestampColumnFormat,
            Double sourceCalculationNumber1,
            Double sourceCalculationNumber2
        )
        {
            Debug.Assert(!String.IsNullOrEmpty(edgeTimestampColumnName));
            AssertValid();

            m_eSchemeType = AutoFillSchemeType.EdgeTimestamp;
            m_sEdgeTimestampColumnName               = edgeTimestampColumnName;
            m_eEdgeTimestampColumnFormat             = edgeTimestampColumnFormat;
            m_dEdgeTimestampSourceCalculationNumber1 = sourceCalculationNumber1;
            m_dEdgeTimestampSourceCalculationNumber2 = sourceCalculationNumber2;
        }
Exemple #11
0
        public string GetFormatByFormatType(ExcelColumnFormat type)
        {
            switch (type)
            {
            case ExcelColumnFormat.Date:
                return(DateTimeExtention.DefaultDateFormat);

            case ExcelColumnFormat.DateTime:
                return(DateTimeExtention.DefaultDateTimeFormat);

            case ExcelColumnFormat.Hours12Time:
                return(TimeSpanExtention.Time12HourFormat);

            case ExcelColumnFormat.Hours24Time:
                return(TimeSpanExtention.DefaultTime24HourFormat);
            }

            return(null);
        }
Exemple #12
0
        DateTimeToString
        (
            DateTime dateTime,
            ExcelColumnFormat format,
            IFormatProvider formatProvider
        )
        {
            Debug.Assert(formatProvider != null);
            AssertValid();

            String sToStringFormat = null;

            switch (format)
            {
            case ExcelColumnFormat.Date:

                sToStringFormat = "d";
                break;

            case ExcelColumnFormat.Time:

                sToStringFormat = "T";
                break;

            case ExcelColumnFormat.DateAndTime:

                sToStringFormat = "g";
                break;

            default:

                Debug.Assert(false);
                return(null);
            }

            return(dateTime.ToString(sToStringFormat, formatProvider));
        }
        //*************************************************************************
        //  Method: SourceCalculationNumberToTimestampString()
        //
        /// <summary>
        /// Converts a source calculation number to a string for the edge timestamp
        /// scheme.
        /// </summary>
        ///
        /// <param name="dSourceCalculationNumber">
        /// The number to convert.
        /// </param>
        ///
        /// <param name="eEdgeTimestampColumnFormat">
        /// The source column format.
        /// </param>
        ///
        /// <returns>
        /// <paramref name="dSourceCalculationNumber" /> converted to a string.
        /// </returns>
        //*************************************************************************
        protected String SourceCalculationNumberToTimestampString(
            Double dSourceCalculationNumber,
            ExcelColumnFormat eEdgeTimestampColumnFormat
            )
        {
            AssertValid();

            switch (eEdgeTimestampColumnFormat)
            {
            case ExcelColumnFormat.Date:
            case ExcelColumnFormat.DateAndTime:

                // The calculation numbers are in ticks.

                return ( ExcelDateTimeUtil.DateTimeToString(
                    (Int64)dSourceCalculationNumber,
                    eEdgeTimestampColumnFormat) );

            case ExcelColumnFormat.Time:

                // The calculation numbers are in fractions of a day.

                return ( ExcelDateTimeUtil.DateTimeToString(
                    (Int64)(dSourceCalculationNumber * TimeSpan.TicksPerDay),
                    ExcelColumnFormat.Time) );

            default:

                // The calculation numbers are actual numbers.  In this oddball
                // case, use an arbitrary number of decimal places.

                return ( DoubleToString(dSourceCalculationNumber, 2) );
            }
        }
Exemple #14
0
 /// <summary>
 /// Initializes a new instance of the <see cref="ExcelColumnAttribute"/> class.
 /// </summary>
 /// <param name="columnTitle">The column heading.</param>
 /// <param name="columnFormat">The column format.</param>
 public ExcelColumnAttribute(String columnTitle, ExcelColumnFormat columnFormat = ExcelColumnFormat.General)
 {
     this.Title  = columnTitle ?? String.Empty;
     this.Format = columnFormat;
 }
        btnAddMinimumValue_Click
        (
            object sender,
            EventArgs e
        )
        {
            AssertValid();

            ExcelColumnFormat eSelectedVertexColumnFormat =
                this.SelectedVertexColumnFormat;

            switch (eSelectedVertexColumnFormat)
            {
            case ExcelColumnFormat.Number:

                Double dMinimumValue;

                if (!ValidateDoubleTextBox(txbMinimumValueToAdd,
                                           Double.MinValue, Double.MaxValue, "Enter a numeric value.",
                                           out dMinimumValue))
                {
                    return;
                }

                lbxMinimumValues.AddItem <FormattableNumber, Double>(
                    new FormattableNumber(dMinimumValue));

                txbMinimumValueToAdd.Focus();
                txbMinimumValueToAdd.SelectAll();
                return;

            case ExcelColumnFormat.Date:

                lbxMinimumValues.AddItem <FormattableDate, DateTime>(
                    new FormattableDate(dtpMinimumValueToAdd.Value));

                break;

            case ExcelColumnFormat.Time:

                lbxMinimumValues.AddItem <FormattableTime, DateTime>(
                    new FormattableTime(dtpMinimumValueToAdd.Value));

                break;

            case ExcelColumnFormat.DateAndTime:

                lbxMinimumValues.AddItem <FormattableDateAndTime, DateTime>(
                    new FormattableDateAndTime(dtpMinimumValueToAdd.Value));

                break;

            case ExcelColumnFormat.Other:

                // This should never occur, because the Add button is disabled
                // when the column format is Other.

                Debug.Assert(false);
                break;

            default:

                Debug.Assert(false);
                break;
            }

            dtpMinimumValueToAdd.Focus();
        }
        //*************************************************************************
        //  Constructor: AutoFillWorkbookWithSchemeResults()
        //
        /// <summary>
        /// Initializes a new instance of the <see
        /// cref="AutoFillWorkbookWithSchemeResults" /> class.
        /// </summary>
        //*************************************************************************
        public AutoFillWorkbookWithSchemeResults()
        {
            m_eSchemeType = AutoFillSchemeType.None;

            m_sVertexCategoryColumnName = null;
            m_asVertexCategoryNames = null;

            m_sEdgeWeightColumnName = null;
            m_dEdgeWeightSourceCalculationNumber1 = 0;
            m_dEdgeWeightSourceCalculationNumber2 = 0;
            m_iEdgeWeightDecimalPlaces = 0;

            m_sEdgeTimestampColumnName = null;
            m_eEdgeTimestampColumnFormat = ExcelColumnFormat.Other;
            m_dEdgeTimestampSourceCalculationNumber1 = 0;
            m_dEdgeTimestampSourceCalculationNumber2 = 0;

            AssertValid();
        }
        DoDataExchange
        (
            Boolean bFromControls
        )
        {
            if (bFromControls)
            {
                // Perform error checking.

                if (cbxVertexColumnName.Items.Count == 0)
                {
                    OnInvalidComboBox(cbxVertexColumnName,
                                      "There are no columns that can be used.");

                    return(false);
                }

                String sVertexColumnName;

                if (!ValidateRequiredComboBox(cbxVertexColumnName,
                                              "Select a column.", out sVertexColumnName)
                    )
                {
                    return(false);
                }

                // Error checking passed.  Transfer the data.

                m_oGroupByVertexAttributeDialogUserSettings.VertexColumnName =
                    sVertexColumnName;

                ExcelColumnFormat eSelectedVertexColumnFormat =
                    this.SelectedVertexColumnFormat;

                m_oGroupByVertexAttributeDialogUserSettings.VertexColumnFormat =
                    eSelectedVertexColumnFormat;

                String sMinimumValues = String.Empty;

                switch (eSelectedVertexColumnFormat)
                {
                case ExcelColumnFormat.Number:

                    sMinimumValues =
                        lbxMinimumValues.ItemsToCultureInvariantString <
                            FormattableNumber, Double>();
                    break;

                case ExcelColumnFormat.Date:

                    sMinimumValues =
                        lbxMinimumValues.ItemsToCultureInvariantString <
                            FormattableDate, DateTime>();

                    break;

                case ExcelColumnFormat.Time:

                    sMinimumValues =
                        lbxMinimumValues.ItemsToCultureInvariantString <
                            FormattableTime, DateTime>();

                    break;

                case ExcelColumnFormat.DateAndTime:

                    sMinimumValues =
                        lbxMinimumValues.ItemsToCultureInvariantString <
                            FormattableDateAndTime, DateTime>();

                    break;

                case ExcelColumnFormat.Other:

                    break;

                default:

                    Debug.Assert(false);
                    break;
                }

                m_oGroupByVertexAttributeDialogUserSettings.MinimumValues =
                    sMinimumValues;
            }
            else
            {
                cbxVertexColumnName.Text =
                    m_oGroupByVertexAttributeDialogUserSettings.VertexColumnName;

                ExcelColumnFormat eSelectedVertexColumnFormat =
                    m_oGroupByVertexAttributeDialogUserSettings.VertexColumnFormat;

                this.SelectedVertexColumnFormat = eSelectedVertexColumnFormat;

                String sMinimumValues =
                    m_oGroupByVertexAttributeDialogUserSettings.MinimumValues;

                switch (eSelectedVertexColumnFormat)
                {
                case ExcelColumnFormat.Number:

                    lbxMinimumValues.CultureInvariantStringToItems <
                        FormattableNumber, Double>(sMinimumValues);

                    break;

                case ExcelColumnFormat.Date:

                    lbxMinimumValues.CultureInvariantStringToItems <
                        FormattableDate, DateTime>(sMinimumValues);

                    break;

                case ExcelColumnFormat.Time:

                    lbxMinimumValues.CultureInvariantStringToItems <
                        FormattableTime, DateTime>(sMinimumValues);

                    break;

                case ExcelColumnFormat.DateAndTime:

                    lbxMinimumValues.CultureInvariantStringToItems <
                        FormattableDateAndTime, DateTime>(sMinimumValues);

                    break;

                case ExcelColumnFormat.Other:

                    break;

                default:

                    Debug.Assert(false);
                    break;
                }


                EnableControls();
            }

            return(true);
        }
    DoDataExchange
    (
        Boolean bFromControls
    )
    {
        if (bFromControls)
        {
            // Perform error checking.

            if (cbxVertexColumnName.Items.Count == 0)
            {
                OnInvalidComboBox(cbxVertexColumnName,
                    "There are no columns that can be used.");

                return (false);
            }

            String sVertexColumnName;

            if ( !ValidateRequiredComboBox(cbxVertexColumnName,
                "Select a column.", out sVertexColumnName)
                )
            {
                return (false);
            }

            // Error checking passed.  Transfer the data.

            m_oGroupByVertexAttributeDialogUserSettings.VertexColumnName =
                sVertexColumnName;

            ExcelColumnFormat eSelectedVertexColumnFormat =
                this.SelectedVertexColumnFormat;

            m_oGroupByVertexAttributeDialogUserSettings.VertexColumnFormat =
                eSelectedVertexColumnFormat;

            String sMinimumValues = String.Empty;

            switch (eSelectedVertexColumnFormat)
            {
                case ExcelColumnFormat.Number:

                    sMinimumValues =
                        lbxMinimumValues.ItemsToCultureInvariantString<
                            FormattableNumber, Double>();
                    break;

                case ExcelColumnFormat.Date:

                    sMinimumValues =
                        lbxMinimumValues.ItemsToCultureInvariantString<
                            FormattableDate, DateTime>();

                    break;

                case ExcelColumnFormat.Time:

                    sMinimumValues =
                        lbxMinimumValues.ItemsToCultureInvariantString<
                            FormattableTime, DateTime>();

                    break;

                case ExcelColumnFormat.DateAndTime:

                    sMinimumValues =
                        lbxMinimumValues.ItemsToCultureInvariantString<
                            FormattableDateAndTime, DateTime>();

                    break;

                case ExcelColumnFormat.Other:

                    break;

                default:

                    Debug.Assert(false);
                    break;
            }

            m_oGroupByVertexAttributeDialogUserSettings.MinimumValues =
                sMinimumValues;
        }
        else
        {
            cbxVertexColumnName.Text =
                m_oGroupByVertexAttributeDialogUserSettings.VertexColumnName;

            ExcelColumnFormat eSelectedVertexColumnFormat =
                m_oGroupByVertexAttributeDialogUserSettings.VertexColumnFormat;

            this.SelectedVertexColumnFormat = eSelectedVertexColumnFormat;

            String sMinimumValues =
                m_oGroupByVertexAttributeDialogUserSettings.MinimumValues;

            switch (eSelectedVertexColumnFormat)
            {
                case ExcelColumnFormat.Number:

                    lbxMinimumValues.CultureInvariantStringToItems<
                        FormattableNumber, Double>(sMinimumValues);

                    break;

                case ExcelColumnFormat.Date:

                    lbxMinimumValues.CultureInvariantStringToItems<
                        FormattableDate, DateTime>(sMinimumValues);

                    break;

                case ExcelColumnFormat.Time:

                    lbxMinimumValues.CultureInvariantStringToItems<
                        FormattableTime, DateTime>(sMinimumValues);

                    break;

                case ExcelColumnFormat.DateAndTime:

                    lbxMinimumValues.CultureInvariantStringToItems<
                        FormattableDateAndTime, DateTime>(sMinimumValues);

                    break;

                case ExcelColumnFormat.Other:

                    break;

                default:

                    Debug.Assert(false);
                    break;
            }


            EnableControls();
        }

        return (true);
    }
        //*************************************************************************
        //  Method: GetEdgeTimestampResults()
        //
        /// <summary>
        /// Gets the results of a call to <see
        /// cref="WorkbookSchemeAutoFiller.AutoFillByEdgeTimestamp" />.
        /// </summary>
        ///
        /// <param name="edgeTimestampColumnName">
        /// Where the name of the edge table column containing edge timestamps gets
        /// stored.
        /// </param>
        ///
        /// <param name="edgeTimestampColumnFormat">
        /// Where the format of the edge table column containing edge timestamps
        /// gets stored.
        /// </param>
        ///
        /// <param name="sourceCalculationNumber1">
        /// Where the actual first source number used in the calculations gets
        /// stored if true is returned.
        /// </param>
        ///
        /// <param name="sourceCalculationNumber2">
        /// Where the actual second source number used in the calculations gets
        /// stored if true is returned.
        /// </param>
        ///
        /// <remarks>
        /// Call this only if <see cref="Type" /> returns <see
        /// cref="AutoFillSchemeType.EdgeTimestamp" />.
        /// </remarks>
        //*************************************************************************
        public void GetEdgeTimestampResults(
            out String edgeTimestampColumnName,
            out ExcelColumnFormat edgeTimestampColumnFormat,
            out Double sourceCalculationNumber1,
            out Double sourceCalculationNumber2
            )
        {
            Debug.Assert(m_eSchemeType == AutoFillSchemeType.EdgeTimestamp);
            AssertValid();

            edgeTimestampColumnName = m_sEdgeTimestampColumnName;
            edgeTimestampColumnFormat = m_eEdgeTimestampColumnFormat;
            sourceCalculationNumber1 = m_dEdgeTimestampSourceCalculationNumber1;
            sourceCalculationNumber2 = m_dEdgeTimestampSourceCalculationNumber2;
        }
        //*************************************************************************
        //  Method: DateTimeToString()
        //
        /// <overloads>
        /// Converts a DateTime to a string.
        /// </overloads>
        ///
        /// <summary>
        /// Converts a DateTime to a string using the current culture.
        /// </summary>
        ///
        /// <param name="dateTime">
        /// The DateTime to convert.
        /// </param>
        ///
        /// <param name="format">
        /// The format to use.  Must be <see cref="ExcelColumnFormat.Date" />,
        /// <see cref="ExcelColumnFormat.Time" />, or <see
        /// cref="ExcelColumnFormat.DateAndTime" />.
        /// </param>
        ///
        /// <returns>
        /// <paramref name="dateTime" /> converted to a string in the <paramref
        /// name="format" /> format.
        /// </returns>
        ///
        /// <remarks>
        /// This method can be used to convert DateTimes in Excel applications to a
        /// String using consistent string formats.
        /// </remarks>
        //*************************************************************************
        public static String DateTimeToString(
            DateTime dateTime,
            ExcelColumnFormat format
            )
        {
            AssertValid();

            return ( DateTimeToString( dateTime, format,
            CultureInfo.CurrentCulture) );
        }
        //*************************************************************************
        //  Method: SetEdgeTimestampResults()
        //
        /// <summary>
        /// Stores the results of a call to <see
        /// cref="WorkbookSchemeAutoFiller.AutoFillByEdgeTimestamp" />.
        /// </summary>
        ///
        /// <param name="edgeTimestampColumnName">
        /// The name of the edge table column containing edge timestamps.
        /// </param>
        ///
        /// <param name="edgeTimestampColumnFormat">
        /// The format of the edge table column containing edge timestamps.
        /// </param>
        ///
        /// <param name="sourceCalculationNumber1">
        /// The actual first source number used in the calculations.
        /// </param>
        ///
        /// <param name="sourceCalculationNumber2">
        /// The actual second source number used in the calculations.
        /// </param>
        //*************************************************************************
        public void SetEdgeTimestampResults(
            String edgeTimestampColumnName,
            ExcelColumnFormat edgeTimestampColumnFormat,
            Double sourceCalculationNumber1,
            Double sourceCalculationNumber2
            )
        {
            Debug.Assert( !String.IsNullOrEmpty(edgeTimestampColumnName) );
            AssertValid();

            m_eSchemeType = AutoFillSchemeType.EdgeTimestamp;
            m_sEdgeTimestampColumnName = edgeTimestampColumnName;
            m_eEdgeTimestampColumnFormat = edgeTimestampColumnFormat;
            m_dEdgeTimestampSourceCalculationNumber1 = sourceCalculationNumber1;
            m_dEdgeTimestampSourceCalculationNumber2 = sourceCalculationNumber2;
        }
Exemple #22
0
        public static Stream DataTableToExcel(string fileName, DataTable data,
                                              List <ExcelColumnFormat> colFmt,
                                              string cols,
                                              Func <object, string, object> tranfunc = null,
                                              bool isColumnWritten = true, string sheetName = "Sheet1", string tableTitle = null)
        {
            if (data.Rows.Count == 0)
            {
                return(null);
            }                                         //data为空,直接退出
            int              i        = 0;
            int              j        = 0;
            int              count    = 0;
            IWorkbook        workbook = null;
            ISheet           sheet    = null;
            NPOIMemoryStream ms       = null;

            try
            {
                //转换对齐
                Func <System.Drawing.ContentAlignment?, HorizontalAlignment> _transHAlignment = alignment =>
                {
                    switch (alignment)
                    {
                    case System.Drawing.ContentAlignment.BottomRight:
                    case System.Drawing.ContentAlignment.MiddleRight:
                    case System.Drawing.ContentAlignment.TopRight:
                        return(HorizontalAlignment.Right);

                    case System.Drawing.ContentAlignment.BottomCenter:
                    case System.Drawing.ContentAlignment.MiddleCenter:
                    case System.Drawing.ContentAlignment.TopCenter:
                        return(HorizontalAlignment.Center);
                    }
                    return(HorizontalAlignment.Left);
                };
                //设置值的格式
                Action <IRow, int, object, string> _val = (row, columnIndex, v, colCode) =>
                {
                    string rlt  = "";
                    string vstr = Ass.P.PStr(v);
                    string tv   = null;
                    if (tranfunc != null)
                    {
                        tv = Ass.P.PStr(tranfunc(v, colCode));
                    }

                    ExcelColumnFormat fmt = null;
                    if (tv != null && tv != vstr)
                    {
                        rlt = tv;
                    }
                    else if (colFmt.Any(m => m.ColumnNameCode == colCode))
                    {
                        rlt = vstr;
                        string v0 = "";
                        fmt = colFmt.Find(m => m.ColumnNameCode == colCode);
                        if (v != null && fmt.ToStringFormat.IsNotEmpty())
                        {
                            v0 = string.Format("{0:" + fmt.ToStringFormat + "}", v);
                            if (v0 != vstr)
                            {
                                rlt = v0;
                            }
                        }
                    }
                    else
                    {
                        rlt = vstr;
                    }

                    //设置值
                    var    cell = row.CreateCell(j);
                    var    t = rlt.GetStringType();
                    double dbval = 0; DateTime dtval;
                    if (double.TryParse(rlt, out dbval))
                    {
                        cell.SetCellValue(dbval);
                    }
                    else if (DateTime.TryParse(rlt, out dtval))
                    {
                        cell.SetCellValue(rlt);
                    }
                    else
                    {
                        cell.SetCellValue(rlt);
                    }
                };


                using (ms = new NPOIMemoryStream())
                {
                    if (!GetWorkbook(fileName, out workbook))
                    {
                        return(null);
                    }
                    List <string> colList = cols.IsEmpty()?new List <string>(): (cols + "").Split(',').ToList();//获取列名集合
                    sheet = workbook.CreateSheet(sheetName);

                    //表标题
                    if (tableTitle.IsNotEmpty())
                    {
                        IRow          row     = sheet.CreateRow(count++);
                        ICell         cell    = row.CreateCell(0);
                        XSSFCellStyle ztStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                        IFont         ztFont  = workbook.CreateFont();
                        ztFont.FontHeightInPoints = 14;
                        ztFont.Underline          = FontUnderlineType.DoubleAccounting;
                        ztStyle.SetFont(ztFont);
                        ztStyle.Alignment = HorizontalAlignment.Center;
                        cell.CellStyle    = ztStyle;
                        cell.SetCellValue(tableTitle);
                        var colnum = colList.Count > 0 ? colList.Count : data.Columns.Count; //列数
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, colnum - 1));    //合并单元格
                    }

                    Dictionary <int, ICellStyle> _columeCellStyles = new Dictionary <int, ICellStyle>();
                    //写入DataTable的列名
                    if (isColumnWritten == true)
                    {
                        IRow row = sheet.CreateRow(count++);
                        if (colList.Count > 0) //设定了输出的列
                        {
                            for (j = 0; j < colList.Count(); j++)
                            {
                                var key     = colList[j];
                                var keyname = colFmt.Any(m => m.ColumnNameCode == key) ? colFmt.Find(m => m.ColumnNameCode == key).ColumnName : key;
                                var cell    = row.CreateCell(j);
                                var cellfmt = colFmt.Find(m => m.ColumnNameCode == key);
                                cell.SetCellValue(keyname);

                                //设置对齐与数据格式
                                var cStyle = workbook.CreateCellStyle(); bool bfmt = false;
                                if (cellfmt.Alignment != null)
                                {
                                    bfmt             = true;
                                    cStyle.Alignment = _transHAlignment(cellfmt.Alignment);
                                }
                                if (cellfmt.DataFormat.IsNotEmpty())
                                {
                                    bfmt = true;
                                    short?dfmt = null; IDataFormat format = workbook.CreateDataFormat();
                                    switch (cellfmt.DataFormat)
                                    {
                                    case "Price": dfmt = format.GetFormat("#,##0.00"); break;

                                    case "Date": dfmt = HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd"); break;

                                    case "DateTime": dfmt = 0x16; break;   // HSSFDataFormat.GetBuiltinFormat("yyyy-mm-dd hh:mm:ss"); break;

                                    case "Id": dfmt = HSSFDataFormat.GetBuiltinFormat("@"); break;
                                    }
                                    cStyle.DataFormat = dfmt.Value;
                                }
                                if (bfmt)
                                {
                                    _columeCellStyles.Add(j, cStyle);
                                }

                                //设置列宽
                                if (cellfmt.ColumnWidth.HasValue)
                                {
                                    sheet.SetColumnWidth(j, (int)((cellfmt.ColumnWidth.Value + 0.72) * 256));
                                }
                            }
                        }
                        else
                        {
                            for (j = 0; j < data.Columns.Count; j++)
                            {
                                string key = data.Columns[j].ColumnName;
                                if (colFmt.Any(m => m.ColumnNameCode == key))//改列名为中文
                                {
                                    var cell    = row.CreateCell(j);
                                    var cellfmt = colFmt.Find(m => m.ColumnNameCode == key);
                                    cell.SetCellValue(cellfmt.ColumnName);
                                    //设置对齐
                                    if (cellfmt.Alignment != null)
                                    {
                                        sheet.GetColumnStyle(j).Alignment = _transHAlignment(cellfmt.Alignment);
                                    }
                                    //设置列宽
                                    if (cellfmt.ColumnWidth.HasValue)
                                    {
                                        sheet.SetColumnWidth(j, cellfmt.ColumnWidth.Value);
                                    }
                                }
                                else
                                {
                                    row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                                }
                            }
                        }
                    }

                    //写入数据
                    if (colList.Count > 0)
                    {
                        for (i = 0; i < data.Rows.Count; i++, count++)
                        {
                            IRow row = sheet.CreateRow(count);
                            for (j = 0; j < colList.Count; j++)
                            {
                                _val(row, j, data.Rows[i][colList[j]], colList[j]);
                            }
                        }
                    }
                    else
                    {
                        for (i = 0; i < data.Rows.Count; i++, count++)
                        {
                            IRow row = sheet.CreateRow(count);
                            for (j = 0; j < data.Columns.Count; j++)
                            {
                                _val(row, j, data.Rows[i][j], data.Columns[j].ColumnName);
                            }
                        }
                    }

                    //设置列格式
                    foreach (int colIndex in _columeCellStyles.Keys)
                    {
                        sheet.SetDefaultColumnStyle(colIndex, _columeCellStyles[colIndex]);
                    }


                    workbook.Write(ms); //写入到excel
                    ms.Flush();
                    ms.Position = 0;
                    return(ms);
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("Exception: " + ex.Message);
                return(null);
            }
        }
Exemple #23
0
        GetDynamicFilterParameters
        (
            Microsoft.Office.Interop.Excel.Workbook workbook,
            String worksheetName,
            String tableName
        )
        {
            Debug.Assert(workbook != null);
            Debug.Assert(!String.IsNullOrEmpty(worksheetName));
            Debug.Assert(!String.IsNullOrEmpty(tableName));

        #if false  // For testing.
            return(GetRandomDynamicFilterParameters(tableName));
        #endif

            LinkedList <DynamicFilterParameters> oDynamicFilterParameters =
                new LinkedList <DynamicFilterParameters>();

            // Get the specified table and loop through its columns.

            ListObject oTable;

            if (ExcelUtil.TryGetTable(workbook, worksheetName, tableName,
                                      out oTable))
            {
                Application oApplication = workbook.Application;

                foreach (ListColumn oColumn in oTable.ListColumns)
                {
                    if (ColumnShouldBeExcluded(oColumn))
                    {
                        continue;
                    }

                    ExcelColumnFormat eColumnFormat =
                        ExcelUtil.GetColumnFormat(oColumn);

                    switch (eColumnFormat)
                    {
                    case ExcelColumnFormat.Number:
                    case ExcelColumnFormat.Date:
                    case ExcelColumnFormat.Time:
                    case ExcelColumnFormat.DateAndTime:

                        // Get the range of values in the column.

                        Double dMinimumCellValue, dMaximumCellValue;

                        if (TryGetNumericRange(worksheetName, oColumn,
                                               out dMinimumCellValue, out dMaximumCellValue))
                        {
                            if (eColumnFormat == ExcelColumnFormat.Number)
                            {
                                oDynamicFilterParameters.AddLast(
                                    new NumericFilterParameters(oColumn.Name,
                                                                dMinimumCellValue, dMaximumCellValue,

                                                                ExcelUtil.GetTableColumnDecimalPlaces(
                                                                    oColumn))
                                    );
                            }
                            else
                            {
                                oDynamicFilterParameters.AddLast(
                                    new DateTimeFilterParameters(oColumn.Name,
                                                                 dMinimumCellValue, dMaximumCellValue,
                                                                 eColumnFormat));
                            }
                        }

                        break;

                    case ExcelColumnFormat.Other:

                        // Skip the column.

                        break;

                    default:

                        Debug.Assert(false);
                        break;
                    }
                }
            }

            return(oDynamicFilterParameters);
        }
        //*************************************************************************
        //  Method: DateTimeToString()
        //
        /// <summary>
        /// Converts a DateTime in ticks to a string using the current culture.
        /// </summary>
        ///
        /// <param name="ticks">
        /// The date/time to convert, in 100-nanosecond ticks.
        /// </param>
        ///
        /// <param name="format">
        /// The format to use.  Must be <see cref="ExcelColumnFormat.Date" />,
        /// <see cref="ExcelColumnFormat.Time" />, or <see
        /// cref="ExcelColumnFormat.DateAndTime" />.
        /// </param>
        ///
        /// <returns>
        /// <paramref name="ticks" /> converted to a string in the 
        /// <paramref name="format" /> format.
        /// </returns>
        ///
        /// <remarks>
        /// This method can be used to convert DateTimes in Excel applications to a
        /// String using consistent string formats.
        /// </remarks>
        //*************************************************************************
        public static String DateTimeToString(
            Int64 ticks,
            ExcelColumnFormat format
            )
        {
            AssertValid();

            return ( DateTimeToString(new DateTime(ticks), format) );
        }
        //*************************************************************************
        //  Method: DateTimeToString()
        //
        /// <summary>
        /// Converts a DateTime to a string using a specified format provider.
        /// </summary>
        ///
        /// <param name="dateTime">
        /// The DateTime to convert.
        /// </param>
        ///
        /// <param name="format">
        /// The format to use.  Must be <see cref="ExcelColumnFormat.Date" />,
        /// <see cref="ExcelColumnFormat.Time" />, or <see
        /// cref="ExcelColumnFormat.DateAndTime" />.
        /// </param>
        ///
        /// <param name="formatProvider">
        /// The IFormatProvider to use.
        /// </param>
        ///
        /// <returns>
        /// <paramref name="dateTime" /> converted to a string in the <paramref
        /// name="format" /> format.
        /// </returns>
        ///
        /// <remarks>
        /// This method can be used to convert DateTimes in Excel applications to a
        /// String using consistent string formats.
        /// </remarks>
        //*************************************************************************
        public static String DateTimeToString(
            DateTime dateTime,
            ExcelColumnFormat format,
            IFormatProvider formatProvider
            )
        {
            Debug.Assert(formatProvider != null);
            AssertValid();

            String sToStringFormat = null;

            switch (format)
            {
            case ExcelColumnFormat.Date:

                sToStringFormat = "d";
                break;

            case ExcelColumnFormat.Time:

                sToStringFormat = "T";
                break;

            case ExcelColumnFormat.DateAndTime:

                sToStringFormat = "g";
                break;

            default:

                Debug.Assert(false);
                return (null);
            }

            return ( dateTime.ToString( sToStringFormat, formatProvider) );
        }
        //*************************************************************************
        //  Method: DateTimeToStringLocale1033()
        //
        /// <summary>
        /// Converts a DateTime to a string using the locale with the ID 1033.
        /// </summary>
        ///
        /// <param name="dateTime">
        /// The DateTime to convert.
        /// </param>
        ///
        /// <param name="format">
        /// The format to use.  Must be <see cref="ExcelColumnFormat.Date" />,
        /// <see cref="ExcelColumnFormat.Time" />, or <see
        /// cref="ExcelColumnFormat.DateAndTime" />.
        /// </param>
        ///
        /// <returns>
        /// <paramref name="dateTime" /> converted to a string in the <paramref
        /// name="format" /> format.
        /// </returns>
        ///
        /// <remarks>
        /// This method can be used to convert DateTimes in Excel applications to a
        /// String using consistent string formats.
        ///
        /// <para>
        /// Locale ID 1033 must be used when writing to the Excel object model.
        /// See "Formatting Data in Excel with Various Regional Settings" at
        /// http://msdn.microsoft.com/en-us/library/ms268748.aspx.
        /// </para>
        ///
        /// </remarks>
        //*************************************************************************
        public static String DateTimeToStringLocale1033(
            DateTime dateTime,
            ExcelColumnFormat format
            )
        {
            AssertValid();

            return ( DateTimeToString(dateTime, format,
            CultureInfo.GetCultureInfo(1033) ) );
        }