예제 #1
0
        internal SLColumnProperties Clone()
        {
            SLColumnProperties cp = new SLColumnProperties(this.ThemeDefaultColumnWidth, this.ThemeDefaultColumnWidthInEMU, this.MaxDigitWidth, this.listColumnStepSize);

            cp.HasWidth     = this.HasWidth;
            cp.fWidth       = this.fWidth;
            cp.lWidthInEMU  = this.lWidthInEMU;
            cp.StyleIndex   = this.StyleIndex;
            cp.Hidden       = this.Hidden;
            cp.BestFit      = this.BestFit;
            cp.Phonetic     = this.Phonetic;
            cp.OutlineLevel = this.OutlineLevel;
            cp.Collapsed    = this.Collapsed;

            return(cp);
        }
예제 #2
0
        /// <summary>
        /// Copy the style of one column to a range of columns.
        /// </summary>
        /// <param name="FromColumnIndex">The column index of the column to be copied from.</param>
        /// <param name="ToStartColumnIndex">The column index of the start column of the column range. This is typically the left-most column.</param>
        /// <param name="ToEndColumnIndex">The column index of the end column of the column range. This is typically the right-most column.</param>
        /// <returns>True if successful. False otherwise.</returns>
        public bool CopyColumnStyle(int FromColumnIndex, int ToStartColumnIndex, int ToEndColumnIndex)
        {
            int iStartColumnIndex = 1, iEndColumnIndex = 1;
            bool result = false;

            if (ToStartColumnIndex < ToEndColumnIndex)
            {
                iStartColumnIndex = ToStartColumnIndex;
                iEndColumnIndex = ToEndColumnIndex;
            }
            else
            {
                iStartColumnIndex = ToEndColumnIndex;
                iEndColumnIndex = ToStartColumnIndex;
            }

            if (FromColumnIndex >= 1 && FromColumnIndex <= SLConstants.ColumnLimit
                && iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit
                && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
            {
                result = true;

                uint iStyleIndex = 0;
                if (slws.ColumnProperties.ContainsKey(FromColumnIndex))
                {
                    iStyleIndex = slws.ColumnProperties[FromColumnIndex].StyleIndex;
                }

                SLColumnProperties cp;
                int i, j;
                for (i = iStartColumnIndex; i <= iEndColumnIndex; ++i)
                {
                    if (i != FromColumnIndex)
                    {
                        if (iStyleIndex == 0)
                        {
                            if (slws.ColumnProperties.ContainsKey(i))
                            {
                                slws.ColumnProperties[i].StyleIndex = 0;
                            }
                        }
                        else
                        {
                            if (slws.ColumnProperties.ContainsKey(i))
                            {
                                slws.ColumnProperties[i].StyleIndex = iStyleIndex;
                            }
                            else
                            {
                                cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
                                cp.StyleIndex = iStyleIndex;
                                slws.ColumnProperties[i] = cp;
                            }

                            slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(false, i));
                        }
                    }
                }

                #region copying cell styles
                SLCell cell;
                SLCellPoint pt, destpt;
                for (i = 1; i <= SLConstants.RowLimit; ++i)
                {
                    pt = new SLCellPoint(i, FromColumnIndex);
                    if (slws.Cells.ContainsKey(pt))
                    {
                        if (slws.Cells[pt].StyleIndex > 0)
                        {
                            for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
                            {
                                if (j != FromColumnIndex)
                                {
                                    destpt = new SLCellPoint(i, j);
                                    if (slws.Cells.ContainsKey(destpt))
                                    {
                                        slws.Cells[destpt].StyleIndex = slws.Cells[pt].StyleIndex;
                                    }
                                    else
                                    {
                                        cell = new SLCell();
                                        cell.CellText = string.Empty;
                                        cell.StyleIndex = slws.Cells[pt].StyleIndex;
                                        slws.Cells[destpt] = cell;
                                    }
                                }
                            }
                        }
                    }
                    else
                    {
                        for (j = iStartColumnIndex; j <= iEndColumnIndex; ++j)
                        {
                            if (j != FromColumnIndex)
                            {
                                destpt = new SLCellPoint(i, j);
                                if (slws.Cells.ContainsKey(destpt))
                                {
                                    slws.Cells[destpt].StyleIndex = iStyleIndex;
                                }
                                // no need else because the default style will take over
                            }
                        }
                    }
                }
                #endregion
            }

            return result;
        }
예제 #3
0
        /// <summary>
        /// Set the column style for a range of rows.
        /// </summary>
        /// <param name="StartColumnIndex">The column index of the starting column.</param>
        /// <param name="EndColumnIndex">The column index of the ending column.</param>
        /// <param name="ColumnStyle">The style for the columns.</param>
        /// <returns>True if the column indices are valid. False otherwise.</returns>
        public bool SetColumnStyle(int StartColumnIndex, int EndColumnIndex, SLStyle ColumnStyle)
        {
            int iStartColumnIndex = 1, iEndColumnIndex = 1;
            if (StartColumnIndex < EndColumnIndex)
            {
                iStartColumnIndex = StartColumnIndex;
                iEndColumnIndex = EndColumnIndex;
            }
            else
            {
                iStartColumnIndex = EndColumnIndex;
                iEndColumnIndex = StartColumnIndex;
            }

            bool result = false;
            if (iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
            {
                result = true;
                int i = 0;
                int iStyleIndex = this.SaveToStylesheet(ColumnStyle.ToHash());
                SLColumnProperties cp;
                for (i = iStartColumnIndex; i <= iEndColumnIndex; ++i)
                {
                    if (slws.ColumnProperties.ContainsKey(i))
                    {
                        cp = slws.ColumnProperties[i];
                        cp.StyleIndex = (uint)iStyleIndex;
                        slws.ColumnProperties[i] = cp;
                    }
                    else
                    {
                        cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
                        cp.StyleIndex = (uint)iStyleIndex;
                        slws.ColumnProperties.Add(i, cp);
                    }
                    slws.RowColumnStyleHistory.Add(new SLRowColumnStyleHistory(false, i));
                }

                List<SLCellPoint> listCellKeys = slws.Cells.Keys.ToList<SLCellPoint>();
                SLStyle cellstyle;
                foreach (SLCellPoint pt in listCellKeys)
                {
                    if (iStartColumnIndex <= pt.ColumnIndex && pt.ColumnIndex <= iEndColumnIndex)
                    {
                        cellstyle = this.GetCellStyle(pt.RowIndex, pt.ColumnIndex);
                        cellstyle.MergeStyle(ColumnStyle);
                        this.SetCellStyle(pt.RowIndex, pt.ColumnIndex, cellstyle);
                    }
                }
            }

            return result;
        }
예제 #4
0
        internal SLColumnProperties Clone()
        {
            SLColumnProperties cp = new SLColumnProperties(this.ThemeDefaultColumnWidth, this.ThemeDefaultColumnWidthInEMU, this.MaxDigitWidth, this.listColumnStepSize);
            cp.HasWidth = this.HasWidth;
            cp.fWidth = this.fWidth;
            cp.lWidthInEMU = this.lWidthInEMU;
            cp.StyleIndex = this.StyleIndex;
            cp.Hidden = this.Hidden;
            cp.BestFit = this.BestFit;
            cp.Phonetic = this.Phonetic;
            cp.OutlineLevel = this.OutlineLevel;
            cp.Collapsed = this.Collapsed;

            return cp;
        }
예제 #5
0
        private void LoadSelectedWorksheet()
        {
            // Need to check?
            //if (string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID)) return;

            WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID);

            slws = new SLWorksheet(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors, SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize, SimpleTheme.ThemeRowHeight);

            int index = 0;
            SLColumnProperties cp;
            Column col;
            SLSheetView slsv;
            MergeCell mc;
            SLMergeCell slmc;
            SLConditionalFormatting condformat;
            SLHyperlink hl;

            OpenXmlReader oxrRow;
            int iRowIndex = -1;
            int iColumnIndex = -1;
            int iGuessRowIndex = 0;
            int iGuessColumnIndex = 0;
            SLRowProperties rp;
            Row r;
            Cell c;
            SLCell slc;

            OpenXmlReader oxr = OpenXmlReader.Create(wsp);
            while (oxr.Read())
            {
                if (oxr.ElementType == typeof(SheetProperties))
                {
                    SheetProperties sprop = (SheetProperties)oxr.LoadCurrentElement();
                    slws.PageSettings.SheetProperties.FromSheetProperties(sprop);
                }
                else if (oxr.ElementType == typeof(SheetView))
                {
                    slsv = new SLSheetView();
                    slsv.FromSheetView((SheetView)oxr.LoadCurrentElement());
                    slws.SheetViews.Add(slsv);

                    // we're concerned only with the first workbook view.
                    if (slsv.ShowFormulas && slsv.WorkbookViewId == 0) slws.IsDoubleColumnWidth = true;
                }
                else if (oxr.ElementType == typeof(SheetFormatProperties))
                {
                    SheetFormatProperties sfp = (SheetFormatProperties)oxr.LoadCurrentElement();
                    slws.SheetFormatProperties.FromSheetFormatProperties(sfp);
                }
                else if (oxr.ElementType == typeof(Column))
                {
                    #region Column
                    int i = 0;
                    col = (Column)oxr.LoadCurrentElement();
                    int min = (int)col.Min.Value;
                    int max = (int)col.Max.Value;
                    for (i = min; i <= max; ++i)
                    {
                        cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
                        if (col.Width != null)
                        {
                            cp.Width = col.Width.Value;
                            cp.HasWidth = true;
                        }
                        if (col.Style != null)
                        {
                            index = (int)col.Style.Value;
                            // default is 0
                            if (index > 0 && index < listStyle.Count)
                            {
                                cp.StyleIndex = (uint)index;
                            }
                        }
                        if (col.Hidden != null && col.Hidden.Value) cp.Hidden = col.Hidden.Value;
                        if (col.BestFit != null && col.BestFit.Value) cp.BestFit = col.BestFit.Value;
                        if (col.Phonetic != null && col.Phonetic.Value) cp.Phonetic = col.Phonetic.Value;
                        if (col.OutlineLevel != null && col.OutlineLevel.Value > 0) cp.OutlineLevel = col.OutlineLevel.Value;
                        if (col.Collapsed != null && col.Collapsed.Value) cp.Collapsed = col.Collapsed.Value;
                        slws.ColumnProperties[i] = cp;
                    }
                    #endregion
                }
                else if (oxr.ElementType == typeof(Row))
                {
                    #region Row
                    ++iGuessRowIndex;
                    iGuessColumnIndex = 0;
                    r = (Row)oxr.LoadCurrentElement();
                    rp = new SLRowProperties(SimpleTheme.ThemeRowHeight);
                    if (r.RowIndex != null)
                    {
                        iRowIndex = (int)r.RowIndex.Value;
                        iGuessRowIndex = iRowIndex;
                    }
                    if (r.StyleIndex != null)
                    {
                        index = (int)r.StyleIndex.Value;
                        // default is 0
                        if (index > 0 && index < listStyle.Count)
                        {
                            rp.StyleIndex = (uint)index;
                        }
                    }
                    if (r.Height != null)
                    {
                        rp.HasHeight = true;
                        rp.Height = r.Height.Value;
                    }
                    if (r.Hidden != null && r.Hidden.Value) rp.Hidden = r.Hidden.Value;
                    if (r.OutlineLevel != null && r.OutlineLevel.Value > 0) rp.OutlineLevel = r.OutlineLevel.Value;
                    if (r.Collapsed != null && r.Collapsed.Value) rp.Collapsed = r.Collapsed.Value;
                    if (r.ThickTop != null && r.ThickTop.Value) rp.ThickTop = r.ThickTop.Value;
                    if (r.ThickBot != null && r.ThickBot.Value) rp.ThickBottom = r.ThickBot.Value;
                    if (r.ShowPhonetic != null && r.ShowPhonetic.Value) rp.ShowPhonetic = r.ShowPhonetic.Value;

                    if (slws.RowProperties.ContainsKey(iGuessRowIndex))
                    {
                        slws.RowProperties[iGuessRowIndex] = rp;
                    }
                    else
                    {
                        slws.RowProperties.Add(iGuessRowIndex, rp);
                    }

                    oxrRow = OpenXmlReader.Create(r);
                    while (oxrRow.Read())
                    {
                        if (oxrRow.ElementType == typeof(Cell))
                        {
                            ++iGuessColumnIndex;
                            c = (Cell)oxrRow.LoadCurrentElement();
                            slc = new SLCell();
                            slc.FromCell(c);
                            if (c.CellReference != null)
                            {
                                if (SLTool.FormatCellReferenceToRowColumnIndex(c.CellReference.Value, out iRowIndex, out iColumnIndex))
                                {
                                    iGuessRowIndex = iRowIndex;
                                    iGuessColumnIndex = iColumnIndex;
                                    slws.Cells[new SLCellPoint(iGuessRowIndex, iGuessColumnIndex)] = slc;
                                }
                                else
                                {
                                    slws.Cells[new SLCellPoint(iGuessRowIndex, iGuessColumnIndex)] = slc;
                                }
                            }
                            else
                            {
                                slws.Cells[new SLCellPoint(iGuessRowIndex, iGuessColumnIndex)] = slc;
                            }
                        }
                    }
                    oxrRow.Close();
                    #endregion
                }
                else if (oxr.ElementType == typeof(SheetProtection))
                {
                    SLSheetProtection sp = new SLSheetProtection();
                    sp.FromSheetProtection((SheetProtection)oxr.LoadCurrentElement());
                    slws.HasSheetProtection = true;
                    slws.SheetProtection = sp.Clone();
                }
                else if (oxr.ElementType == typeof(AutoFilter))
                {
                    SLAutoFilter af = new SLAutoFilter();
                    af.FromAutoFilter((AutoFilter)oxr.LoadCurrentElement());
                    slws.HasAutoFilter = true;
                    slws.AutoFilter = af.Clone();
                }
                else if (oxr.ElementType == typeof(MergeCell))
                {
                    mc = (MergeCell)oxr.LoadCurrentElement();
                    slmc = new SLMergeCell();
                    slmc.FromMergeCell(mc);
                    if (slmc.IsValid) slws.MergeCells.Add(slmc);
                }
                else if (oxr.ElementType == typeof(ConditionalFormatting))
                {
                    condformat = new SLConditionalFormatting();
                    condformat.FromConditionalFormatting((ConditionalFormatting)oxr.LoadCurrentElement());
                    slws.ConditionalFormattings.Add(condformat);
                }
                else if (oxr.ElementType == typeof(DataValidations))
                {
                    DataValidations dvs = (DataValidations)oxr.LoadCurrentElement();
                    if (dvs.DisablePrompts != null) slws.DataValidationDisablePrompts = dvs.DisablePrompts.Value;
                    if (dvs.XWindow != null) slws.DataValidationXWindow = dvs.XWindow.Value;
                    if (dvs.YWindow != null) slws.DataValidationYWindow = dvs.YWindow.Value;

                    using (OpenXmlReader oxrDataValidation = OpenXmlReader.Create(dvs))
                    {
                        SLDataValidation dv;
                        while (oxrDataValidation.Read())
                        {
                            if (oxrDataValidation.ElementType == typeof(DataValidation))
                            {
                                dv = new SLDataValidation();
                                dv.FromDataValidation((DataValidation)oxrDataValidation.LoadCurrentElement());
                                slws.DataValidations.Add(dv);
                            }
                        }
                    }
                }
                else if (oxr.ElementType == typeof(Hyperlink))
                {
                    hl = new SLHyperlink();
                    hl.FromHyperlink((Hyperlink)oxr.LoadCurrentElement());
                    slws.Hyperlinks.Add(hl);
                }
                else if (oxr.ElementType == typeof(PrintOptions))
                {
                    PrintOptions po = (PrintOptions)oxr.LoadCurrentElement();
                    if (po.HorizontalCentered != null) slws.PageSettings.PrintHorizontalCentered = po.HorizontalCentered.Value;
                    if (po.VerticalCentered != null) slws.PageSettings.PrintVerticalCentered = po.VerticalCentered.Value;
                    if (po.Headings != null) slws.PageSettings.PrintHeadings = po.Headings.Value;
                    if (po.GridLines != null) slws.PageSettings.PrintGridLines = po.GridLines.Value;
                    if (po.GridLinesSet != null) slws.PageSettings.PrintGridLinesSet = po.GridLinesSet.Value;
                }
                else if (oxr.ElementType == typeof(PageMargins))
                {
                    PageMargins pm = (PageMargins)oxr.LoadCurrentElement();
                    if (pm.Left != null) slws.PageSettings.LeftMargin = pm.Left.Value;
                    if (pm.Right != null) slws.PageSettings.RightMargin = pm.Right.Value;
                    if (pm.Top != null) slws.PageSettings.TopMargin = pm.Top.Value;
                    if (pm.Bottom != null) slws.PageSettings.BottomMargin = pm.Bottom.Value;
                    if (pm.Header != null) slws.PageSettings.HeaderMargin = pm.Header.Value;
                    if (pm.Footer != null) slws.PageSettings.FooterMargin = pm.Footer.Value;
                }
                else if (oxr.ElementType == typeof(PageSetup))
                {
                    PageSetup ps = (PageSetup)oxr.LoadCurrentElement();
                    // consider setting to 1 if not one of the "valid" paper sizes?
                    if (ps.PaperSize != null) slws.PageSettings.PaperSize = (SLPaperSizeValues)ps.PaperSize.Value;
                    if (ps.Scale != null) slws.PageSettings.iScale = ps.Scale.Value;
                    if (ps.FirstPageNumber != null) slws.PageSettings.FirstPageNumber = ps.FirstPageNumber.Value;
                    if (ps.FitToWidth != null) slws.PageSettings.iFitToWidth = ps.FitToWidth.Value;
                    if (ps.FitToHeight != null) slws.PageSettings.iFitToHeight = ps.FitToHeight.Value;
                    if (ps.PageOrder != null) slws.PageSettings.PageOrder = ps.PageOrder.Value;
                    if (ps.Orientation != null) slws.PageSettings.Orientation = ps.Orientation.Value;
                    if (ps.UsePrinterDefaults != null) slws.PageSettings.UsePrinterDefaults = ps.UsePrinterDefaults.Value;
                    if (ps.BlackAndWhite != null) slws.PageSettings.BlackAndWhite = ps.BlackAndWhite.Value;
                    if (ps.Draft != null) slws.PageSettings.Draft = ps.Draft.Value;
                    if (ps.CellComments != null) slws.PageSettings.CellComments = ps.CellComments.Value;
                    if (ps.Errors != null) slws.PageSettings.Errors = ps.Errors.Value;
                    if (ps.HorizontalDpi != null) slws.PageSettings.HorizontalDpi = ps.HorizontalDpi.Value;
                    if (ps.VerticalDpi != null) slws.PageSettings.VerticalDpi = ps.VerticalDpi.Value;
                    if (ps.Copies != null) slws.PageSettings.Copies = ps.Copies.Value;
                }
                else if (oxr.ElementType == typeof(HeaderFooter))
                {
                    HeaderFooter hf = (HeaderFooter)oxr.LoadCurrentElement();
                    if (hf.OddHeader != null) slws.PageSettings.OddHeaderText = hf.OddHeader.Text;
                    if (hf.OddFooter != null) slws.PageSettings.OddFooterText = hf.OddFooter.Text;
                    if (hf.EvenHeader != null) slws.PageSettings.EvenHeaderText = hf.EvenHeader.Text;
                    if (hf.EvenFooter != null) slws.PageSettings.EvenFooterText = hf.EvenFooter.Text;
                    if (hf.FirstHeader != null) slws.PageSettings.FirstHeaderText = hf.FirstHeader.Text;
                    if (hf.FirstFooter != null) slws.PageSettings.FirstFooterText = hf.FirstFooter.Text;
                    if (hf.DifferentOddEven != null) slws.PageSettings.DifferentOddEvenPages = hf.DifferentOddEven.Value;
                    if (hf.DifferentFirst != null) slws.PageSettings.DifferentFirstPage = hf.DifferentFirst.Value;
                    if (hf.ScaleWithDoc != null) slws.PageSettings.ScaleWithDocument = hf.ScaleWithDoc.Value;
                    if (hf.AlignWithMargins != null) slws.PageSettings.AlignWithMargins = hf.AlignWithMargins.Value;
                }
                else if (oxr.ElementType == typeof(RowBreaks))
                {
                    SLBreak b;
                    uint rowbkindex;
                    using (OpenXmlReader oxrRowBreaks = OpenXmlReader.Create((RowBreaks)oxr.LoadCurrentElement()))
                    {
                        while (oxrRowBreaks.Read())
                        {
                            if (oxrRowBreaks.ElementType == typeof(Break))
                            {
                                b = new SLBreak();
                                b.FromBreak((Break)oxrRowBreaks.LoadCurrentElement());
                                rowbkindex = b.Id;
                                slws.RowBreaks[(int)rowbkindex] = b;
                            }
                        }
                    }
                }
                else if (oxr.ElementType == typeof(ColumnBreaks))
                {
                    SLBreak b;
                    uint colbkindex;
                    using (OpenXmlReader oxrColBreaks = OpenXmlReader.Create((ColumnBreaks)oxr.LoadCurrentElement()))
                    {
                        while (oxrColBreaks.Read())
                        {
                            if (oxrColBreaks.ElementType == typeof(Break))
                            {
                                b = new SLBreak();
                                b.FromBreak((Break)oxrColBreaks.LoadCurrentElement());
                                colbkindex = b.Id;
                                slws.ColumnBreaks[(int)colbkindex] = b;
                            }
                        }
                    }
                }
                else if (oxr.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Drawing))
                {
                    DocumentFormat.OpenXml.Spreadsheet.Drawing drawing = (DocumentFormat.OpenXml.Spreadsheet.Drawing)oxr.LoadCurrentElement();
                    slws.DrawingId = drawing.Id;
                    if (wsp.DrawingsPart != null)
                    {
                        Xdr.NonVisualDrawingProperties nvdp;
                        uint iUniqueId = 1;
                        using (OpenXmlReader oxrDrawing = OpenXmlReader.Create(wsp.DrawingsPart.WorksheetDrawing))
                        {
                            while (oxrDrawing.Read())
                            {
                                if (oxrDrawing.ElementType == typeof(Xdr.NonVisualDrawingProperties))
                                {
                                    nvdp = (Xdr.NonVisualDrawingProperties)oxrDrawing.LoadCurrentElement();
                                    if (nvdp.Id != null && nvdp.Id.Value > iUniqueId)
                                    {
                                        iUniqueId = nvdp.Id.Value;
                                    }
                                }
                            }
                        }
                        slws.NextWorksheetDrawingId = iUniqueId + 1;
                    }
                }
                else if (oxr.ElementType == typeof(Picture))
                {
                    Picture pic = (Picture)oxr.LoadCurrentElement();
                    slws.BackgroundPictureId = pic.Id;
                    slws.BackgroundPictureDataIsInFile = null;
                }
                else if (oxr.ElementType == typeof(WorksheetExtensionList))
                {
                    WorksheetExtensionList wsextlist = (WorksheetExtensionList)oxr.LoadCurrentElement();

                    SLConditionalFormatting2010 cf2010;
                    X14.SparklineGroup sparkgrp;
                    SLSparklineGroup spkgrp;

                    using (OpenXmlReader oxrext = OpenXmlReader.Create(wsextlist))
                    {
                        while (oxrext.Read())
                        {
                            if (oxrext.ElementType == typeof(X14.ConditionalFormatting))
                            {
                                cf2010 = new SLConditionalFormatting2010();
                                cf2010.FromConditionalFormatting((X14.ConditionalFormatting)oxrext.LoadCurrentElement());
                                slws.ConditionalFormattings2010.Add(cf2010.Clone());
                            }
                            else if (oxrext.ElementType == typeof(X14.SparklineGroup))
                            {
                                sparkgrp = (X14.SparklineGroup)oxrext.LoadCurrentElement();
                                spkgrp = new SLSparklineGroup(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
                                spkgrp.FromSparklineGroup(sparkgrp);
                                slws.SparklineGroups.Add(spkgrp.Clone());
                            }
                        }
                    }
                }
            }
            oxr.Dispose();

            if (wsp.TableDefinitionParts != null)
            {
                SLTable t;
                foreach (TableDefinitionPart tdp in wsp.TableDefinitionParts)
                {
                    t = new SLTable();
                    t.FromTable(tdp.Table);
                    t.RelationshipID = wsp.GetIdOfPart(tdp);
                    t.IsNewTable = false;
                    slws.Tables.Add(t);
                }
            }
        }
예제 #6
0
        /// <summary>
        /// Set the show phonetic property for a range of columns.
        /// </summary>
        /// <param name="StartColumnIndex">The column index of the start column.</param>
        /// <param name="EndColumnIndex">The column index of the end column.</param>
        /// <param name="ShowPhonetic">True if the columns should show phonetic information. False otherwise.</param>
        /// <returns>True if the column indices are valid. False otherwise.</returns>
        public bool SetColumnShowPhonetic(int StartColumnIndex, int EndColumnIndex, bool ShowPhonetic)
        {
            int iStartColumnIndex = 1, iEndColumnIndex = 1;
            if (StartColumnIndex < EndColumnIndex)
            {
                iStartColumnIndex = StartColumnIndex;
                iEndColumnIndex = EndColumnIndex;
            }
            else
            {
                iStartColumnIndex = EndColumnIndex;
                iEndColumnIndex = StartColumnIndex;
            }

            bool result = false;
            if (iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
            {
                result = true;
                int i = 0;
                SLColumnProperties cp;
                for (i = iStartColumnIndex; i <= iEndColumnIndex; ++i)
                {
                    if (slws.ColumnProperties.ContainsKey(i))
                    {
                        cp = slws.ColumnProperties[i];
                        cp.Phonetic = ShowPhonetic;
                        slws.ColumnProperties[i] = cp;
                    }
                    else
                    {
                        cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
                        cp.Phonetic = ShowPhonetic;
                        slws.ColumnProperties.Add(i, cp);
                    }
                }
            }

            return result;
        }
예제 #7
0
        /// <summary>
        /// Automatically fit column width according to cell contents.
        /// </summary>
        /// <param name="StartColumnIndex">The column index of the start column.</param>
        /// <param name="EndColumnIndex">The column index of the end column.</param>
        public void AutoFitColumn(int StartColumnIndex, int EndColumnIndex)
        {
            int iStartColumnIndex = 1, iEndColumnIndex = 1;
            if (StartColumnIndex < EndColumnIndex)
            {
                iStartColumnIndex = StartColumnIndex;
                iEndColumnIndex = EndColumnIndex;
            }
            else
            {
                iStartColumnIndex = EndColumnIndex;
                iEndColumnIndex = StartColumnIndex;
            }

            if (iStartColumnIndex < 1) iStartColumnIndex = 1;
            if (iStartColumnIndex > SLConstants.ColumnLimit) iStartColumnIndex = SLConstants.ColumnLimit;
            if (iEndColumnIndex < 1) iEndColumnIndex = 1;
            if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;

            Dictionary<int, int> pixellength = this.AutoFitRowColumn(false, iStartColumnIndex, iEndColumnIndex);

            SLColumnProperties cp;
            double fColumnWidth;
            int iPixelLength;
            double fWholeNumber;
            double fRemainder;
            foreach (int pixlenpt in pixellength.Keys)
            {
                iPixelLength = pixellength[pixlenpt];
                if (iPixelLength > 0)
                {
                    fWholeNumber = (double)(iPixelLength / (SimpleTheme.ThemeMaxDigitWidth - 1));
                    fRemainder = (double)(iPixelLength % (SimpleTheme.ThemeMaxDigitWidth - 1));
                    fRemainder = fRemainder / (double)(SimpleTheme.ThemeMaxDigitWidth - 1);
                    // we'll leave it to the algorithm within SLColumnProperties.Width to handle
                    // the actual column width refitting.
                    fColumnWidth = fWholeNumber + fRemainder;
                    if (slws.ColumnProperties.ContainsKey(pixlenpt))
                    {
                        cp = slws.ColumnProperties[pixlenpt];
                        cp.Width = fColumnWidth;
                        cp.BestFit = true;
                        slws.ColumnProperties[pixlenpt] = cp.Clone();
                    }
                    else
                    {
                        cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
                        cp.Width = fColumnWidth;
                        cp.BestFit = true;
                        slws.ColumnProperties[pixlenpt] = cp.Clone();
                    }
                }
                // else we don't have to do anything
            }
        }
        /// <summary>
        /// Collapse a group of columns.
        /// </summary>
        /// <param name="ColumnIndex">The column index of the column just after the group of columns you want to collapse. For example, this will be column 5 if columns 2 to 4 are grouped.</param>
        public void CollapseColumns(int ColumnIndex)
        {
            if (ColumnIndex < 1 || ColumnIndex > SLConstants.ColumnLimit) return;

            // the following algorithm is not guaranteed to work in all cases.
            // The data is sort of loosely linked together with no guarantee that they
            // all make sense together. If you use Excel, then the internal data is sort of
            // guaranteed to make sense together, but anyone can make an Open XML spreadsheet
            // with possibly invalid-looking data. Maybe Excel will accept it, maybe not.

            SLColumnProperties cp;
            byte byCurrentOutlineLevel = 0;
            if (slws.ColumnProperties.ContainsKey(ColumnIndex))
            {
                cp = slws.ColumnProperties[ColumnIndex];
                byCurrentOutlineLevel = cp.OutlineLevel;
            }

            bool bFound = false;
            int i;

            for (i = ColumnIndex - 1; i >= 1; --i)
            {
                if (slws.ColumnProperties.ContainsKey(i))
                {
                    cp = slws.ColumnProperties[i];
                    if (cp.OutlineLevel > byCurrentOutlineLevel)
                    {
                        bFound = true;
                        cp.Hidden = true;
                        slws.ColumnProperties[i] = cp.Clone();
                    }
                    else break;
                }
                else break;
            }

            if (bFound)
            {
                if (slws.ColumnProperties.ContainsKey(ColumnIndex))
                {
                    cp = slws.ColumnProperties[ColumnIndex];
                    cp.Collapsed = true;
                    slws.ColumnProperties[ColumnIndex] = cp.Clone();
                }
                else
                {
                    cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
                    cp.Collapsed = true;
                    slws.ColumnProperties[ColumnIndex] = cp.Clone();
                }
            }
        }
        /// <summary>
        /// Group columns.
        /// </summary>
        /// <param name="StartColumnIndex">The column index of the start column.</param>
        /// <param name="EndColumnIndex">The column index of the end column.</param>
        public void GroupColumns(int StartColumnIndex, int EndColumnIndex)
        {
            int iStartColumnIndex = 1, iEndColumnIndex = 1;
            if (StartColumnIndex < EndColumnIndex)
            {
                iStartColumnIndex = StartColumnIndex;
                iEndColumnIndex = EndColumnIndex;
            }
            else
            {
                iStartColumnIndex = EndColumnIndex;
                iEndColumnIndex = StartColumnIndex;
            }

            // I haven't personally checked this, but there's a collapsing -/+ box on the column
            // just right of the grouped columns. This implies the very very last column that can be
            // grouped is the (column limit - 1)th column, because (column limit)th column will have that
            // collapsing box.
            if (iEndColumnIndex >= SLConstants.RowLimit) iEndColumnIndex = SLConstants.ColumnLimit - 1;
            // there's nothing to group...
            if (iStartColumnIndex > iEndColumnIndex) return;

            if (iStartColumnIndex >= 1 && iStartColumnIndex <= SLConstants.ColumnLimit && iEndColumnIndex >= 1 && iEndColumnIndex <= SLConstants.ColumnLimit)
            {
                int i = 0;
                SLColumnProperties cp;
                for (i = iStartColumnIndex; i <= iEndColumnIndex; ++i)
                {
                    if (slws.ColumnProperties.ContainsKey(i))
                    {
                        cp = slws.ColumnProperties[i];
                        // Excel supports only 8 levels
                        if (cp.OutlineLevel < 8) ++cp.OutlineLevel;
                        slws.ColumnProperties[i] = cp.Clone();
                    }
                    else
                    {
                        cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
                        cp.OutlineLevel = 1;
                        slws.ColumnProperties[i] = cp.Clone();
                    }
                }
            }
        }
        /// <summary>
        /// Automatically fit column width according to cell contents.
        /// </summary>
        /// <param name="StartColumnIndex">The column index of the start column.</param>
        /// <param name="EndColumnIndex">The column index of the end column.</param>
        /// <param name="MaximumColumnWidth">The maximum column width in number of characters.</param>
        public void AutoFitColumn(int StartColumnIndex, int EndColumnIndex, double MaximumColumnWidth)
        {
            int iStartColumnIndex = 1, iEndColumnIndex = 1;
            if (StartColumnIndex < EndColumnIndex)
            {
                iStartColumnIndex = StartColumnIndex;
                iEndColumnIndex = EndColumnIndex;
            }
            else
            {
                iStartColumnIndex = EndColumnIndex;
                iEndColumnIndex = StartColumnIndex;
            }

            if (iStartColumnIndex < 1) iStartColumnIndex = 1;
            if (iStartColumnIndex > SLConstants.ColumnLimit) iStartColumnIndex = SLConstants.ColumnLimit;
            if (iEndColumnIndex < 1) iEndColumnIndex = 1;
            if (iEndColumnIndex > SLConstants.ColumnLimit) iEndColumnIndex = SLConstants.ColumnLimit;

            if (MaximumColumnWidth > SLConstants.MaximumColumnWidth) MaximumColumnWidth = SLConstants.MaximumColumnWidth;
            // this is taken from SLColumnProperties...
            int iWholeNumber = Convert.ToInt32(Math.Truncate(MaximumColumnWidth));
            double fStepRemainder = MaximumColumnWidth - (double)iWholeNumber;
            int iStep = 0;
            for (iStep = SimpleTheme.listColumnStepSize.Count - 1; iStep >= 0; --iStep)
            {
                if (fStepRemainder > SimpleTheme.listColumnStepSize[iStep]) break;
            }
            if (iStep < 0) iStep = 0;
            int iMaximumPixelLength = iWholeNumber * (SimpleTheme.ThemeMaxDigitWidth - 1) + iStep;

            Dictionary<int, int> pixellength = this.AutoFitRowColumn(false, iStartColumnIndex, iEndColumnIndex, iMaximumPixelLength);

            SLColumnProperties cp;
            double fColumnWidth;
            int iPixelLength;
            double fWholeNumber;
            double fRemainder;
            foreach (int pixlenpt in pixellength.Keys)
            {
                iPixelLength = pixellength[pixlenpt];
                if (iPixelLength > 0)
                {
                    fWholeNumber = (double)(iPixelLength / (SimpleTheme.ThemeMaxDigitWidth - 1));
                    fRemainder = (double)(iPixelLength % (SimpleTheme.ThemeMaxDigitWidth - 1));
                    fRemainder = fRemainder / (double)(SimpleTheme.ThemeMaxDigitWidth - 1);
                    // we'll leave it to the algorithm within SLColumnProperties.Width to handle
                    // the actual column width refitting.
                    fColumnWidth = fWholeNumber + fRemainder;
                    if (slws.ColumnProperties.ContainsKey(pixlenpt))
                    {
                        cp = slws.ColumnProperties[pixlenpt];
                        cp.Width = fColumnWidth;
                        cp.BestFit = true;
                        slws.ColumnProperties[pixlenpt] = cp.Clone();
                    }
                    else
                    {
                        cp = new SLColumnProperties(SimpleTheme.ThemeColumnWidth, SimpleTheme.ThemeColumnWidthInEMU, SimpleTheme.ThemeMaxDigitWidth, SimpleTheme.listColumnStepSize);
                        cp.Width = fColumnWidth;
                        cp.BestFit = true;
                        slws.ColumnProperties[pixlenpt] = cp.Clone();
                    }
                }
                // else we don't have to do anything
            }
        }