示例#1
0
文件: ExcelStyles.cs 项目: zhgl7688/-
        private void SetStyleAddress(StyleBase sender, Style.StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe)
        {
            if (address.Start.Column == 0 || address.Start.Row == 0)
            {
                throw (new Exception("error address"));
            }
            //Columns
            else if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows)
            {
                ExcelColumn column;
                //Get the startcolumn
                ulong colID = ExcelColumn.GetColumnID(ws.SheetID, address.Start.Column);
                if (!ws._columns.ContainsKey(colID))
                {
                    column = ws.Column(address.Start.Column);
                }
                else
                {
                    column = ws._columns[colID] as ExcelColumn;
                }

                var index = ws._columns.IndexOf(colID);
                while (column.ColumnMin <= address.End.Column)
                {
                    if (column.ColumnMax > address.End.Column)
                    {
                        var newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax);
                        column.ColumnMax = address.End.Column;
                    }

                    if (styleCashe.ContainsKey(column.StyleID))
                    {
                        column.StyleID = styleCashe[column.StyleID];
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[column.StyleID];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(column.StyleID, newId);
                        column.StyleID = newId;
                    }

                    index++;
                    if (index >= ws._columns.Count)
                    {
                        break;
                    }
                    else
                    {
                        column = (ws._columns[index] as ExcelColumn);
                    }
                }

                if (column._columnMax < address.End.Column)
                {
                    var newCol = ws.Column(column._columnMax + 1) as ExcelColumn;
                    newCol._columnMax = address.End.Column;

                    if (styleCashe.ContainsKey(newCol.StyleID))
                    {
                        newCol.StyleID = styleCashe[newCol.StyleID];
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[column.StyleID];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(newCol.StyleID, newId);
                        newCol.StyleID = newId;
                    }

                    //column._columnMax = address.End.Column;
                }

                //Set for individual cells in the spann. We loop all cells here since the cells are sorted with columns first.
                foreach (ExcelCell cell in ws._cells)
                {
                    if (cell.Column >= address.Start.Column &&
                        cell.Column <= address.End.Column)
                    {
                        if (styleCashe.ContainsKey(cell.StyleID))
                        {
                            cell.StyleID = styleCashe[cell.StyleID];
                        }
                        else
                        {
                            ExcelXfs st    = CellXfs[cell.StyleID];
                            int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(cell.StyleID, newId);
                            cell.StyleID = newId;
                        }
                    }
                }
            }
            //Rows
            else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns)
            {
                for (int rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++)
                {
                    ExcelRow row = ws.Row(rowNum);
                    if (row.StyleID == 0 && ws._columns.Count > 0)
                    {
                        //TODO: We should loop all columns here and change each cell. But for now we take style of column A.
                        foreach (ExcelColumn column in ws._columns)
                        {
                            row.StyleID = column.StyleID;
                            break;  //Get the first one and break.
                        }
                    }
                    if (styleCashe.ContainsKey(row.StyleID))
                    {
                        row.StyleID = styleCashe[row.StyleID];
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[row.StyleID];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(row.StyleID, newId);
                        row.StyleID = newId;
                    }
                }

                //Get Start Cell
                ulong rowID = ExcelRow.GetRowID(ws.SheetID, address.Start.Row);
                int   index = ws._cells.IndexOf(rowID);

                index = ~index;
                while (index < ws._cells.Count)
                {
                    var cell = ws._cells[index] as ExcelCell;
                    if (cell.Row > address.End.Row)
                    {
                        break;
                    }
                    if (styleCashe.ContainsKey(cell.StyleID))
                    {
                        cell.StyleID = styleCashe[cell.StyleID];
                    }
                    else
                    {
                        ExcelXfs st    = CellXfs[cell.StyleID];
                        int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                        styleCashe.Add(cell.StyleID, newId);
                        cell.StyleID = newId;
                    }
                    index++;
                }
            }
            else             //Cellrange
            {
                for (int col = address.Start.Column; col <= address.End.Column; col++)
                {
                    for (int row = address.Start.Row; row <= address.End.Row; row++)
                    {
                        ExcelCell cell = ws.Cell(row, col);
                        if (styleCashe.ContainsKey(cell.StyleID))
                        {
                            cell.StyleID = styleCashe[cell.StyleID];
                        }
                        else
                        {
                            ExcelXfs st    = CellXfs[cell.StyleID];
                            int      newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value);
                            styleCashe.Add(cell.StyleID, newId);
                            cell.StyleID = newId;
                        }
                    }
                }
            }
        }
示例#2
0
        internal static AddressType IsValid(string Address)
        {
            string ws = "";

            if (Address.StartsWith("'"))
            {
                int ix = Address.IndexOf('\'', 1);
                if (ix > -1)
                {
                    ws      = Address.Substring(1, ix - 1);
                    Address = Address.Substring(ix + 2);
                }
            }
            if (Address.IndexOfAny(new char[] { '(', ')', '+', '-', '*', '/', '.', '=', '^', '&', '%', '\"' }) > -1)
            {
                return(AddressType.Invalid);
            }
            if (Address.IndexOf('!') > 0)
            {
                string[] split = Address.Split('!');
                if (split.Length == 2)
                {
                    ws      = split[0];
                    Address = split[1];
                }
                else if (split.Length == 3 && split[1] == "#REF" && split[2] == "")
                {
                    ws      = split[0];
                    Address = "#REF!";
                    if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
                    {
                        return(AddressType.ExternalAddress);
                    }
                    else
                    {
                        return(AddressType.InternalAddress);
                    }
                }
                else
                {
                    return(AddressType.Invalid);
                }
            }
            int _fromRow, _fromCol, _toRow, _toCol;

            if (ExcelAddressBase.GetRowColFromAddress(Address, out _fromRow, out _fromCol, out _toRow, out _toCol))
            {
                if (_fromRow > 0 && _fromCol > 0 && _toRow <= ExcelPackage.MaxRows && _toCol <= ExcelPackage.MaxColumns)
                {
                    if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
                    {
                        return(AddressType.ExternalAddress);
                    }
                    else
                    {
                        return(AddressType.InternalAddress);
                    }
                }
                else
                {
                    return(AddressType.Invalid);
                }
            }
            else
            {
                if (IsValidName(Address))
                {
                    if (ws.StartsWith("[") && ws.IndexOf("]") > 1)
                    {
                        return(AddressType.ExternalName);
                    }
                    else
                    {
                        return(AddressType.InternalName);
                    }
                }
                else
                {
                    return(AddressType.Invalid);
                }
            }
        }
示例#3
0
        internal void GetDefinedNames()
        {
            XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);

            if (nl != null)
            {
                foreach (XmlElement elem in nl)
                {
                    string fullAddress = elem.InnerText;

                    int            localSheetID;
                    ExcelWorksheet nameWorksheet;
                    if (!int.TryParse(elem.GetAttribute("localSheetId"), out localSheetID))
                    {
                        localSheetID  = -1;
                        nameWorksheet = null;
                    }
                    else
                    {
                        nameWorksheet = Worksheets[localSheetID + 1];
                    }
                    var             addressType = ExcelAddressBase.IsValid(fullAddress);
                    ExcelRangeBase  range;
                    ExcelNamedRange namedRange;

                    if (fullAddress.IndexOf("[") > -1)
                    {
                        int start = fullAddress.IndexOf("[");
                        int end   = fullAddress.IndexOf("]", start);
                        if (start >= 0 && end >= 0)
                        {
                            string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
                            int    index;
                            if (int.TryParse(externalIndex, out index))
                            {
                                if (index > 0 && index <= _externalReferences.Count)
                                {
                                    fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
                                }
                            }
                        }
                    }

                    if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName)                        //A value or a formula
                    {
                        double value;
                        range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
                        if (nameWorksheet == null)
                        {
                            namedRange = _names.Add(elem.GetAttribute("name"), range);
                        }
                        else
                        {
                            namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
                        }

                        if (fullAddress.StartsWith("\""))                         //String value
                        {
                            namedRange.NameValue = fullAddress.Substring(1, fullAddress.Length - 2);
                        }
                        else if (double.TryParse(fullAddress, NumberStyles.Any, CultureInfo.InvariantCulture, out value))
                        {
                            namedRange.NameValue = value;
                        }
                        else
                        {
                            namedRange.NameFormula = fullAddress;
                        }
                    }
                    else
                    {
                        ExcelAddress addr = new ExcelAddress(fullAddress);
                        if (localSheetID > -1)
                        {
                            if (string.IsNullOrEmpty(addr._ws))
                            {
                                namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + 1], fullAddress, false));
                            }
                            else
                            {
                                namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
                            }
                        }
                        else
                        {
                            var ws = Worksheets[addr._ws];
                            namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
                        }
                    }
                    if (elem.GetAttribute("hidden") == "1" && namedRange != null)
                    {
                        namedRange.IsNameHidden = true;
                    }
                    if (!string.IsNullOrEmpty(elem.GetAttribute("comment")))
                    {
                        namedRange.NameComment = elem.GetAttribute("comment");
                    }
                }
            }
        }