Source

main / Source / WebClient / Web.BizLogic / Helper / FixExcel.cs

using System;
using System.Linq;
using DataUp.Web.Common;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace DataUp.Web.BizLogic
{
    /// <summary>
    /// Contains methods that remove compatibility errors from Excel documents.
    /// </summary>
    public static class FixExcel
    {
        /// <summary>
        /// Removes all objects.
        /// </summary>
        /// <param name="document">The document.</param>
        public static void RemoveAllObjects(SpreadsheetDocument document)
        {
            WorkbookPart wbPart = document.WorkbookPart;
            var sheets = wbPart.GetPartsOfType<WorksheetPart>();
            CellFormats cellFormats = (CellFormats)wbPart.WorkbookStylesPart.Stylesheet.CellFormats;

            foreach (var sheet in sheets)
            {
                if (document.GetSheetName(sheet) != Constants.MetadataSheetName)
                {
                    var commentPart = sheet.WorksheetCommentsPart;
                    if (commentPart != null)
                    {
                        // Delete the comments part.
                        sheet.DeletePart(commentPart);
                    }
                    if (sheet.Worksheet.Descendants<MergeCells>().Count() > 0)
                    {
                        foreach (var mergedCell in sheet.Worksheet.Descendants<MergeCells>())
                        {
                            mergedCell.RemoveAllChildren();
                            mergedCell.Remove();
                        }
                    }
                    if (sheet.DrawingsPart != null)
                    {
                        ChartPart[] charts = new ChartPart[sheet.DrawingsPart.ChartParts.Count()];
                        sheet.DrawingsPart.ChartParts.ToList().CopyTo(charts);
                        foreach (var chartPart in charts)
                        {
                            sheet.DrawingsPart.DeletePart(chartPart);
                            foreach (var val in sheet.DrawingsPart.WorksheetDrawing.Descendants<DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame>())
                            {
                                val.Parent.Remove();
                            }

                        }
                        ImagePart[] images = new ImagePart[sheet.DrawingsPart.ImageParts.Count()];
                        sheet.DrawingsPart.ImageParts.ToList().CopyTo(images);
                        foreach (var imagePart in images)
                        {
                            sheet.DrawingsPart.DeletePart(imagePart);
                            foreach (var val in sheet.DrawingsPart.WorksheetDrawing.Descendants<DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture>())
                            {
                                val.Parent.Remove();
                            }
                        }
                        var shapes = sheet.DrawingsPart.WorksheetDrawing.Descendants<DocumentFormat.OpenXml.Drawing.Spreadsheet.Shape>();
                        foreach (DocumentFormat.OpenXml.Drawing.Spreadsheet.Shape val in shapes)
                        {
                            val.Parent.Remove();
                        }
                    }

                    SheetData sheetdata = sheet.Worksheet.Elements<SheetData>().FirstOrDefault();
                    if (sheetdata.Descendants<Row>().Count() > 0)
                    {
                        foreach (var row in sheetdata.Descendants<Row>())
                        {
                            foreach (Cell cell in row.Descendants<Cell>())
                            {
                                if (cell.StyleIndex != null)
                                {
                                    if (cell.CellValue == null && cell.StyleIndex == 0)
                                    {
                                        CellFormat cellformate = cellFormats.Descendants<CellFormat>().ElementAt<CellFormat>(Convert.ToInt32(cell.StyleIndex.Value));
                                        cellformate.ClearAllAttributes();
                                        cell.StyleIndex = null;
                                    }
                                    else
                                    {
                                        if (cell.CellValue != null || cell.StyleIndex >= 0)
                                        {
                                            CellFormat cellformate = cellFormats.Descendants<CellFormat>().ElementAt<CellFormat>(Convert.ToInt32(cell.StyleIndex.Value));
                                            cellformate.ClearAllAttributes();
                                            if (cellformate.ApplyNumberFormat != null)
                                            {
                                                cellformate.ClearAllAttributes();
                                                cell.StyleIndex = null;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        //Code to remove selected objects

        /// <summary>
        /// XLs the remove comment.
        /// </summary>
        /// <param name="document">The document.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <returns></returns>
        public static bool XLRemoveComment(SpreadsheetDocument document, string sheetName)
        {
            bool isDeleted = false;

            WorkbookPart wbPart = document.WorkbookPart;
            var sheets = wbPart.GetPartsOfType<WorksheetPart>();
            string getsheetName = String.Empty;

            foreach (var sheet in sheets)
            {
                getsheetName = document.GetSheetName(sheet);
                if (getsheetName != null && getsheetName != Constants.MetadataSheetName && getsheetName == sheetName)
                {
                    var commentpart = sheet.WorksheetCommentsPart;
                    if (commentpart != null)
                    {
                        sheet.DeletePart(commentpart);
                        isDeleted = true;
                    }
                }
            }

            return isDeleted;
        }

        /// <summary>
        /// XLs the remove chart.
        /// </summary>
        /// <param name="document">The document.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <returns></returns>
        public static bool XLRemoveChart(SpreadsheetDocument document, string sheetName)
        {
            bool isDeleted = false;
            WorkbookPart wbPart = document.WorkbookPart;
            var sheets = wbPart.GetPartsOfType<WorksheetPart>();
            string getsheetName = String.Empty;

            foreach (var sheet in sheets)
            {
                getsheetName = document.GetSheetName(sheet);
                if (getsheetName != null && getsheetName != Constants.MetadataSheetName && getsheetName == sheetName)
                {
                    if (sheet.DrawingsPart != null)
                    {
                        ChartPart[] charts = new ChartPart[sheet.DrawingsPart.ChartParts.Count()];
                        sheet.DrawingsPart.ChartParts.ToList().CopyTo(charts);
                        foreach (var chartPart in charts)
                        {
                            sheet.DrawingsPart.DeletePart(chartPart);
                            foreach (var val in sheet.DrawingsPart.WorksheetDrawing.Descendants<DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame>())
                            {
                                val.Parent.Remove();
                                isDeleted = true;
                            }
                        }
                    }
                }
            }
            return isDeleted;
        }

        /// <summary>
        /// XLs the remove pictures.
        /// </summary>
        /// <param name="document">The document.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <returns></returns>
        public static bool XLRemovePictures(SpreadsheetDocument document, string sheetName)
        {
            bool isDeleted = false;
            WorkbookPart wbPart = document.WorkbookPart;
            var sheets = wbPart.GetPartsOfType<WorksheetPart>();
            string getsheetName = String.Empty;

            foreach (var sheet in sheets)
            {
                getsheetName = document.GetSheetName(sheet);
                if (getsheetName != null && getsheetName != Constants.MetadataSheetName && getsheetName == sheetName && sheet.DrawingsPart != null)
                {
                    ImagePart[] images = new ImagePart[sheet.DrawingsPart.ImageParts.Count()];
                    sheet.DrawingsPart.ImageParts.ToList().CopyTo(images);
                    foreach (var imagePart in images)
                    {
                        ImagePart part = imagePart;
                        sheet.DrawingsPart.DeletePart((ImagePart)part);
                        foreach (var val in sheet.DrawingsPart.WorksheetDrawing.Descendants<DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture>())
                        {
                            val.Parent.Remove();
                            isDeleted = true;
                        }
                    }
                }
            }
            return isDeleted;
        }

        /// <summary>
        /// XLs the remove shape.
        /// </summary>
        /// <param name="document">The document.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <returns></returns>
        public static bool XLRemoveShape(SpreadsheetDocument document, string sheetName)
        {
            bool isDeleted = false;
            WorkbookPart wbPart = document.WorkbookPart;
            var sheets = wbPart.GetPartsOfType<WorksheetPart>();
            string getsheetName = String.Empty;

            foreach (var sheet in sheets)
            {
                getsheetName = document.GetSheetName(sheet);
                if (getsheetName != null && getsheetName != Constants.MetadataSheetName && getsheetName == sheetName && sheet.DrawingsPart != null)
                {
                    var shapes = sheet.DrawingsPart.WorksheetDrawing.Descendants<DocumentFormat.OpenXml.Drawing.Spreadsheet.Shape>();
                    foreach (DocumentFormat.OpenXml.Drawing.Spreadsheet.Shape val in shapes)
                    {
                        val.Parent.Remove();
                        isDeleted = true;
                    }
                }
            }
            return isDeleted;
        }

        /// <summary>
        /// XLs the remove merge cell.
        /// </summary>
        /// <param name="document">The document.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <returns></returns>
        public static bool XLRemoveMergeCell(SpreadsheetDocument document, string sheetName)
        {
            bool isDeleted = false;
            WorkbookPart wbPart = document.WorkbookPart;
            var sheets = wbPart.GetPartsOfType<WorksheetPart>();
            string getsheetName = String.Empty;

            foreach (var sheet in sheets)
            {
                getsheetName = document.GetSheetName(sheet);
                if (getsheetName != null && getsheetName != Constants.MetadataSheetName && getsheetName == sheetName)
                {
                    if (sheet.Worksheet.Descendants<MergeCells>().Count() > 0)
                    {
                        var mergcells = sheet.Worksheet.Descendants<MergeCells>();
                        foreach (var mergecell in mergcells)
                        {
                            mergecell.RemoveAllChildren();
                            mergecell.Remove();
                            isDeleted = true;
                        }
                    }
                }
            }

            return isDeleted;
        }

        /// <summary>
        /// XLs the remove color cell.
        /// </summary>
        /// <param name="document">The document.</param>
        /// <param name="sheetName">Name of the sheet.</param>
        /// <returns></returns>
        public static bool XLRemoveColorCell(SpreadsheetDocument document, string sheetName)
        {
            bool isDeleted = false;
            WorkbookPart wbPart = document.WorkbookPart;
            CellFormats cellFormats = (CellFormats)wbPart.WorkbookStylesPart.Stylesheet.CellFormats;

            var sheets = wbPart.GetPartsOfType<WorksheetPart>();
            string getsheetName = String.Empty;

            foreach (var sheet in sheets)
            {
                getsheetName = document.GetSheetName(sheet);
                if (getsheetName != null && getsheetName != Constants.MetadataSheetName && getsheetName == sheetName)
                {
                    SheetData sheetdata = sheet.Worksheet.Elements<SheetData>().FirstOrDefault();
                    foreach (var row in sheetdata.Descendants<Row>())
                    {
                        foreach (Cell cell in row.Descendants<Cell>())
                        {
                            if (cell.StyleIndex != null)
                            {
                                if (cell.CellValue == null && cell.StyleIndex == 0)
                                {
                                    CellFormat cellformate = cellFormats.Descendants<CellFormat>().ElementAt<CellFormat>(Convert.ToInt32(cell.StyleIndex.Value));
                                    cellformate.FontId = null;
                                    cellformate.FillId = null;
                                }
                                else
                                {
                                    if (cell.CellValue != null || cell.StyleIndex >= 0)
                                    {
                                        CellFormat cellformate = cellFormats.Descendants<CellFormat>().ElementAt<CellFormat>(Convert.ToInt32(cell.StyleIndex.Value));
                                        cellformate.FontId = null;
                                        cellformate.FillId = null;
                                    }
                                }
                            }
                        }
                    }
                }
            }

            return isDeleted;
        }

    }
}