예제 #1
0
        /// <summary>
        /// Removes the sheet from the document
        /// using  the given sheet unique name
        /// </summary>
        /// <param name="document">SpreadSheetDocument reference</param>
        /// <param name="name">Sheet name</param>
        public static void RemoveSheet(this SpreadsheetDocument document, string name)
        {
            //Check whether the document or the sheet name is a non-valid to work with object value
            if (document.IsNotValid() || name.IsNotValid())
            {
                return;
            }
            //Search for sheet collection reference
            Sheets sheets = document
                            .WorkbookPart
                            .Workbook
                            .GetFirstChild <Sheets>();

            //If there is not any existing sheet with the given name, there is nothing to remove.
            if (!sheets.Descendants <Sheet>().Any(s => s.Name.Equals(name)))
            {
                return;
            }
            Sheet sheet = sheets.Descendants <Sheet>()
                          .First(s => s.Name.Equals(name));
            int index = sheets
                        .ToList()
                        .IndexOf(sheet);

            sheet.RemoveAllChildren();
            sheet.Remove();
        }
예제 #2
0
        /// <summary>
        /// Gets the sheet data from the document
        /// using the given sheet unique name
        /// </summary>
        /// <param name="document">SpreadSheetDocument reference</param>
        /// <param name="name">Sheet name</param>
        /// <returns>SheetData reference</returns>
        public static SheetData GetSheetData(this SpreadsheetDocument document, string name)
        {
            //Check whether the document or the sheet name is a non-valid to work with object value
            if (document.IsNotValid() || name.IsNotValid())
            {
                return(null);
            }
            //Search the sheet by the unique name given
            Sheet sheet = document.WorkbookPart.Workbook
                          .GetFirstChild <Sheets>()
                          .Descendants <Sheet>()
                          .Where(s => s.Name.Equals(name))
                          .FirstOrDefault();

            if (sheet == null)
            {
                return(null);
            }
            //Get the workspace corresponding to the sheet founded
            WorksheetPart workSpace = document.WorkbookPart.GetPartById(sheet.Id) as WorksheetPart;

            //return founded sheet data
            return(workSpace.Worksheet
                   .Descendants <SheetData>()
                   .First());
        }
예제 #3
0
        public void AutoAdjustWidth()
        {
            SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();

            document.AutoAdjustWidth();
            Assert.True(!document.IsNotValid());
        }
예제 #4
0
        public void SwapColumns()
        {
            SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
            SheetData           sheet    = document.GetSheetData(@"Report");

            sheet.SwapColumns(0, 1);
            Assert.True(!document.IsNotValid());
        }
예제 #5
0
        public async Task SetTemplateValues()
        {
            SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();
            SheetData           sheet    = document.GetSheetData(@"Entity");
            List <KeyValuePair <string, object> > data = new List <KeyValuePair <string, object> >(3)
            {
                new KeyValuePair <string, object>(@"{{Name}}", @"Israel"),
                new KeyValuePair <string, object>(@"{{Age}}", DateTime.Now.AddYears(-27)),
                new KeyValuePair <string, object>(@"{{Email}}", @"*****@*****.**")
            };
            await sheet.SetTemplateValues(data);

            Assert.True(!document.IsNotValid());
        }
예제 #6
0
        public async Task ToExcelDocument()
        {
            Pagination page = new Pagination();

            page.Calculate(1);
            List <TestingEntity> entities = new List <TestingEntity>()
            {
                new TestingEntity()
                {
                }
            };
            PaginatedCollection <TestingEntity> collection = new PaginatedCollection <TestingEntity>(page, entities);
            SpreadsheetDocument excel = await collection.ToExcelDocument();

            Assert.True(!excel.IsNotValid());
        }
예제 #7
0
        /// <summary>
        /// Adds a new Sheet to the document
        /// </summary>
        /// <param name="document">SpreadSheetDocument reference</param>
        /// <param name="name">Sheet name</param>
        public static void AddSheet(this SpreadsheetDocument document, string name)
        {
            //Check whether the document or the sheet name is a non-valid to work with object value
            if (document.IsNotValid() || name.IsNotValid())
            {
                return;
            }
            //Search for sheet collection reference
            Sheets sheets = document
                            .WorkbookPart
                            .Workbook
                            .GetFirstChild <Sheets>();

            //If there already exists a sheet by the given name, there is no need to create a new one.
            if (sheets.Descendants <Sheet>().Any(s => s.Name.Equals(name)))
            {
                return;
            }
            //Get the last sheet pushed into the sheets collection
            Sheet last = sheets
                         .Descendants <Sheet>()
                         .OrderByDescending(s => s.SheetId)
                         .FirstOrDefault();
            //Set current workspace reference
            WorksheetPart workSpace = document.WorkbookPart.AddNewPart <WorksheetPart>();

            //Add new worksheet with data
            workSpace.Worksheet = new Worksheet(new SheetData());
            //Stablish index value
            uint index = last == null ? 1 : last.SheetId.Value;

            index = last == null ? 1 : index + 1;
            //Create the new sheet
            Sheet sheet = new Sheet()
            {
                Id      = document.WorkbookPart.GetIdOfPart(workSpace),
                SheetId = index,
                Name    = name
            };

            //Add new sheet to the collection
            sheets.Append(sheet);
        }
예제 #8
0
        /// <summary>
        /// Adjust the column width to its best fit
        /// </summary>
        /// <param name="document">SpreadSheetDocument reference</param>
        public static void AutoAdjustWidth(this SpreadsheetDocument document)
        {
            if (document.IsNotValid())
            {
                return;
            }
            //Maximum digit with per cell ~96dpi (most common display)
            double max = 8;

            //Add the columns for each worksheet that has any valid sheet data
            document.WorkbookPart.WorksheetParts
            .Select(wsp => wsp.Worksheet)
            .Where(wsp => wsp.Descendants <SheetData>().Any(sd => sd.Descendants <Row>().Any()))
            .ToList()
            .ForEach(ws =>
            {
                //Current SheetData reference
                SheetData sd = ws.GetFirstChild <SheetData>();
                //Find the widest row
                Row row = sd.Descendants <Row>().First();
                //Array of lengths (max length per cell per row)
                int[] lengths = new int[row.Descendants <Cell>().Count()];
                for (int i = 0; i < lengths.Length; i++)
                {
                    lengths[i] = 0;
                }
                //Find the widest cell in all rows
                foreach (Row r in sd.Descendants <Row>())
                {
                    for (int i = 0; i < lengths.Length; i++)
                    {
                        Cell cell = r.Descendants <Cell>().ElementAt(i);
                        int length;
                        //Take the cell length value from the shared string table if the cell has its value in the shared string table
                        if (cell.DataType.Equals(CellValues.SharedString) && cell.CellValue.Text.IsNumber())
                        {
                            int index = Convert.ToInt32(cell.CellValue.Text);
                            length    = StringPart.SharedStringTable.ElementAt(index).InnerText.Length;
                        }
                        else
                        {
                            //Take the length from the cell value directly
                            length = cell.CellValue.Text.Length;
                        }
                        //Set the current length for the cell at row if the length at the current position is less than the current
                        if (lengths[i] < length)
                        {
                            lengths[i] = length;
                        }
                    }
                }
                //Get or create the custom columns reference
                Columns columns = ws.GetFirstChild <Columns>() ?? new Columns();
                //Adjust the columns width
                for (uint i = 1; i <= lengths.Length; i++)
                {
                    double width = ((lengths[i - 1] * max + 5) / max * 256) / 256;
                    columns.Append(new Column()
                    {
                        Min         = i,
                        Max         = i,
                        Width       = width + 1,
                        CustomWidth = true
                    });
                }
                //Add the columns if they do not exists
                if (ws.GetFirstChild <Columns>() == null)
                {
                    ws.InsertBefore(columns, sd);
                }
                //Save all changes made
                ws.Save();
            });
            document.Save();
        }
예제 #9
0
        /// <summary>
        /// Loads the default style sheet part
        /// </summary>
        /// <param name="document">SpreadSheetDocument reference</param>
        public static void LoadDefaultStyleSheet(this SpreadsheetDocument document)
        {
            //Verify document to work with
            if (document.IsNotValid())
            {
                return;
            }
            //Initialize worksheet stylesheet once
            WorkbookStylesPart style = document.WorkbookPart.WorkbookStylesPart ?? document.WorkbookPart.AddNewPart <WorkbookStylesPart>();

            if (style.Stylesheet == null)
            {
                style.Stylesheet = new Stylesheet();
            }
            //Reserved fills Excel requires by default this two fills
            style.Stylesheet.Fills = new Fills();
            //Empty cell fill
            style.Stylesheet.Fills.AppendChild(new Fill
            {
                PatternFill = new PatternFill
                {
                    PatternType = PatternValues.None
                }
            });
            //Weird grey cell fill
            style.Stylesheet.Fills.AppendChild(new Fill
            {
                PatternFill = new PatternFill
                {
                    PatternType = PatternValues.Gray125
                }
            });
            style.Stylesheet.Fills.Count = 2;
            //Reserved Cell formats Excel requires by default this two formats
            style.Stylesheet.CellFormats = new CellFormats();
            style.Stylesheet.CellFormats.AppendChild(new CellFormat());
            style.Stylesheet.CellFormats.Count = 1;
            //Fonts
            style.Stylesheet.Fonts = new Fonts();
            Font font = new Font();

            font.Append(new FontSize()
            {
                Val = 11D
            });
            font.Append(new Color()
            {
                Rgb = SystemColor.Black.ToHexArgb()
            });
            font.Append(new FontName()
            {
                Val = "Calibri"
            });
            font.Append(new FontFamilyNumbering()
            {
                Val = 2
            });
            font.Append(new FontScheme()
            {
                Val = FontSchemeValues.Minor
            });
            font.Append(new Bold());
            style.Stylesheet.Fonts.AppendChild(font);
            style.Stylesheet.Fonts.Count = 1;
            //Save all reserved formats
            style.Stylesheet.Save();
            StylePart = style;
            document.Save();
        }
예제 #10
0
        public void ExcelDocument()
        {
            SpreadsheetDocument document = SpreadSheetExtensions.ExcelDocument();

            Assert.True(!document.IsNotValid());
        }
예제 #11
0
        public void IsNotValid()
        {
            SpreadsheetDocument document = null;

            Assert.True(document.IsNotValid());
        }