示例#1
0
        public static string ReadCell(Excel.Cell cell, StringKeyList buffer = null)
        {
            string value = cell.CellValue?.InnerText ?? string.Empty;

            if (cell.DataType != null)
            {
                if (cell.DataType.Value == Excel.CellValues.SharedString)
                {
                    // shared strings table.
                    if (int.TryParse(value, out int val))
                    {
                        //if (strings.SharedStringTable.ChildElements.Count > val)
                        if (buffer != null)
                        {
                            value = buffer[val]?.Key;
                        }
                    }
                }
                else if (cell.DataType.Value == Excel.CellValues.Boolean)
                {
                    switch (value)
                    {
                    case "0":
                        value = "FALSE";
                        break;

                    default:
                        value = "TRUE";
                        break;
                    }
                }
            }
            return(value);
        }
示例#2
0
 public Excel.Cell GetCell(object value, int c, int r, uint styleIndex, StringKeyList sharedStrings)
 {
     Excel.Cell cell = new Excel.Cell()
     {
         CellReference = Helper.IntToChar(c) + (r).ToString(),
         StyleIndex    = styleIndex,
     };
     WriteCell(cell, value, sharedStrings);
     return(cell);
 }
示例#3
0
        public static StringKeyList ReadStringTable(SharedStringTablePart sharedStringTablePart)
        {
            var dict = new StringKeyList();

            using (var reader = OpenXmlReader.Create(sharedStringTablePart))
            {
                while (reader.Read())
                {
                    if (reader.ElementType == typeof(Excel.SharedStringItem))
                    {
                        dict.Add(new StringKey((Excel.SharedStringItem)reader.LoadCurrentElement()));
                    }
                }
            }
            return(dict);
        }
示例#4
0
        public static void WriteCell(Excel.Cell cell, object value, StringKeyList sharedStrings)
        {
            if (value != null)
            {
                Type type = value.GetType();
                if (type == typeof(int) || type == typeof(uint) || type == typeof(long) || type == typeof(ulong) || type == typeof(short) || type == typeof(ushort))
                {
                    cell.DataType  = Excel.CellValues.Number;
                    cell.CellValue = new Excel.CellValue(value.ToString());
                }
                else if (value.GetType() == typeof(decimal) || value.GetType() == typeof(float) || value.GetType() == typeof(double))
                {
                    cell.DataType  = Excel.CellValues.Number;
                    cell.CellValue = new Excel.CellValue(((decimal)value).ToString(".00", CultureInfo.InvariantCulture.NumberFormat));
                }
                else
                {
                    if (cell.CellFormula != null)
                    {
                        cell.DataType  = Excel.CellValues.String;
                        cell.CellValue = new Excel.CellValue(value.ToString());
                    }
                    else
                    {
                        var stringValue = value.ToString();

                        if (!sharedStrings.TryGetIndex(stringValue, out var index))
                        {
                            index = sharedStrings.Add(new StringKey(stringValue));
                        }
                        cell.DataType  = Excel.CellValues.SharedString;
                        cell.CellValue = new Excel.CellValue(index.ToString());
                    }
                    //cell.DataType = Excel.CellValues.String;
                    //cell.CellValue = new Excel.CellValue(value.ToString().Replace("", string.Empty));
                }
            }
        }
示例#5
0
        public List <Excel.Cell> FindParsedCells(StringKeyList stringTable, Excel.SheetData sd)
        {
            List <Excel.Cell> results = new List <Excel.Cell>();

            foreach (OpenXmlElement element in sd)
            {
                if (element is Excel.Row row)
                {
                    foreach (OpenXmlElement celement in element)
                    {
                        if (celement is Excel.Cell cell)
                        {
                            string val = ReadCell(cell, stringTable);
                            if (val.IndexOf('#') >= 0)
                            {
                                results.Add(cell);
                            }
                        }
                    }
                }
            }
            return(results);
        }
示例#6
0
        public Excel.Cell GetCell(OpenXmlCompositeElement row, object value, int c, int r, uint styleIndex, StringKeyList sharedStrings)
        {
            string reference = Helper.IntToChar(c) + r.ToString();

            Excel.Cell cell = null;

            if (row != null)
            {
                foreach (var rowCell in row.Elements <Excel.Cell>())
                {
                    var cref = CellReference.Parse(rowCell.CellReference.Value);
                    if (cref.Col == c)
                    {
                        cell = rowCell;
                        break;
                    }
                    else if (cref.Col > c)
                    {
                        cell = new Excel.Cell()
                        {
                            CellReference = reference, StyleIndex = styleIndex
                        };
                        row.InsertBefore <Excel.Cell>(cell, rowCell);
                        break;
                    }
                }
            }
            if (cell == null || cell.CellReference.Value != reference)
            {
                cell = new Excel.Cell()
                {
                    CellReference = reference, StyleIndex = styleIndex
                };
                if (row != null)
                {
                    row.AppendChild <Excel.Cell>(cell);
                }
            }

            WriteCell(cell, value, sharedStrings);

            return(cell);
        }
示例#7
0
 public static void WriteStringTable(SharedStringTablePart sharedStringTablePart, StringKeyList items)
 {
     using (var writer = XmlWriter.Create(sharedStringTablePart.GetStream(FileMode.Create)
                                          , new XmlWriterSettings {
         Encoding = Encoding.UTF8, CloseOutput = true
     }))
     {
         writer.WriteStartDocument();
         WriteStartElement(writer, new Excel.SharedStringTable {
             Count = (uint)items.Count
         });
         foreach (var item in items)
         {
             WriteElement(writer, item.Value);
         }
         writer.WriteEndElement();
         writer.WriteEndDocument();
         writer.Flush();
     }
 }
示例#8
0
        private void ParseWorksheetPart(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, StringKeyList sharedStrings, Stream worksheetPart, WorksheetPart newWorksheetPart)
        {
            var inserts = new List <CellRange>();

            using (var reader = OpenXmlReader.Create(worksheetPart))
                using (var writer = XmlWriter.Create(newWorksheetPart.GetStream(FileMode.Create)
                                                     , new XmlWriterSettings {
                    Encoding = Encoding.UTF8, CloseOutput = true
                }))
                {
                    int ind, dif = 0;
                    writer.WriteStartDocument(true);

                    while (reader.Read())
                    {
                        //remove protection
                        //if (reader.ElementType == typeof(Excel.SheetProtection))
                        //{
                        //    reader.LoadCurrentElement();
                        //    continue;
                        //}
                        if (reader.ElementType == typeof(Excel.Row))
                        {
                            var row      = (Excel.Row)reader.LoadCurrentElement();
                            var rowIndex = (int)row.RowIndex.Value;
                            ind = rowIndex + dif;
                            UpdateRowIndex(row, ind);
                            QResult query = null;
                            foreach (Excel.Cell ocell in row.Descendants <Excel.Cell>())
                            {
                                object rz = null;
                                if (cacheNames.TryGetValue(ocell.CellReference.Value, out var defName))
                                {
                                    rz = defName.CacheValue ?? param.GetValue(defName.Code);
                                }
                                else
                                {
                                    string value = ReadCell(ocell, sharedStrings);
                                    rz = ReplaceExcelString(param, value);
                                }

                                if (rz != null)
                                {
                                    query = rz as QResult;
                                    if (query != null)
                                    {
                                        var       sref     = CellReference.Parse(ocell.CellReference);
                                        var       insert   = new CellRange(sref, sref);
                                        Excel.Row tableRow = null;
                                        foreach (object[] dataRow in query.Values)
                                        {
                                            if (tableRow == null)
                                            {
                                                tableRow = row;
                                            }
                                            else if (defName != null && defName.Range.End.Row > sref.Row)
                                            {
                                                if (reader.Read() && reader.ElementType == typeof(Excel.Row))
                                                {
                                                    tableRow = (Excel.Row)reader.LoadCurrentElement();
                                                    UpdateRowIndex(tableRow, (int)tableRow.RowIndex.Value + dif);
                                                    insert.Start.Row++;
                                                    insert.End.Row++;
                                                }
                                                else
                                                {
                                                }
                                            }
                                            else
                                            {
                                                tableRow = CloneRow(tableRow, sref.Row);// GetRow(sd, srow, excelRow == null, cell.Parent as Excel.Row);
                                                insert.End.Row++;
                                            }

                                            int col = sref.Col;
                                            foreach (object itemValue in dataRow)
                                            {
                                                GetCell(tableRow, itemValue, col, sref.Row, 0, sharedStrings);
                                                col++;
                                            }
                                            sref.Row++;
                                            WriteElement(writer, tableRow);
                                        }

                                        if (insert.Rows > 0)
                                        {
                                            inserts.Add(insert);
                                            dif += insert.Rows;
                                        }
                                        break;
                                    }
                                    else
                                    {
                                        WriteCell(ocell, rz, sharedStrings);
                                    }
                                }
                            }
                            if (query == null)
                            {
                                WriteElement(writer, row);
                            }
                        }
                        else if (reader.ElementType == typeof(Excel.MergeCell))
                        {
                            var merge = reader.LoadCurrentElement() as Excel.MergeCell;
                            var range = CellRange.Parse(merge.Reference);

                            foreach (var insert in inserts)
                            {
                                if (insert.Start.Row < range.Start.Row)
                                {
                                    range.Start.Row += insert.Rows;
                                    range.End.Row   += insert.Rows;
                                }
                            }
                            merge.Reference = range.ToString();
                            WriteElement(writer, merge);
                        }
                        else if (reader.ElementType == typeof(Excel.DataValidation))
                        {
                            var validation = (Excel.DataValidation)reader.LoadCurrentElement();
                            if (validation.SequenceOfReferences.HasValue)
                            {
                                var newlist = new List <StringValue>();
                                foreach (var item in validation.SequenceOfReferences.Items)
                                {
                                    var range = CellRange.Parse(item);
                                    foreach (var insert in inserts)
                                    {
                                        if (insert.Start.Row <= range.End.Row && insert.End.Row > range.End.Row)
                                        {
                                            range.End.Row = insert.End.Row;
                                            newlist.Add(range.ToString());
                                            continue;
                                        }
                                    }
                                    newlist.Add(item);
                                }
                                validation.SequenceOfReferences = new ListValue <StringValue>(newlist);
                            }
                            WriteElement(writer, validation);
                        }
                        else if (reader.ElementType == typeof(Excel.OddFooter) ||
                                 reader.ElementType == typeof(Excel.EvenFooter) ||
                                 reader.ElementType == typeof(Excel.FirstFooter))
                        {
                            var footer = reader.LoadCurrentElement() as OpenXmlLeafTextElement;
                            var str    = ReplaceExcelString(param, footer.Text) as string;
                            if (str != null)
                            {
                                footer.Text = str;
                            }
                            WriteElement(writer, footer);
                        }
                        else if (reader.IsStartElement)
                        {
                            WriteStartElement(writer, reader);
                        }
                        else if (reader.IsEndElement)
                        {
                            writer.WriteEndElement();
                        }
                    }
                    writer.WriteEndDocument();
                    writer.Flush();
                    writer.Close();
                    //newWorksheetPart.FeedData(temp);
                }
        }
示例#9
0
 private void ParseWorksheetPart(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, StringKeyList sharedStrings, WorksheetPart worksheetPart, WorksheetPart newWorksheetPart)
 {
     using (var stream = worksheetPart.GetStream())
     {
         ParseWorksheetPart(param, cacheNames, sharedStrings, stream, newWorksheetPart);
     }
 }
示例#10
0
        private void ParseWorksheetPart(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, StringKeyList sharedStrings, WorksheetPart worksheetPart)
        {
            var tempName = Path.GetTempFileName();

            using (var temp = new FileStream(tempName, FileMode.Create, FileAccess.ReadWrite))
            {
                using (var stream = worksheetPart.GetStream())
                    stream.CopyTo(temp);
                temp.Position = 0;
                ParseWorksheetPart(param, cacheNames, sharedStrings, temp, worksheetPart);
            }
            File.Delete(tempName);
        }