Пример #1
0
        /// <summary>
        /// Adds a given worksheet to the document
        /// </summary>
        /// <param name="worksheet">Worksheet document to add</param>
        /// <returns>Worksheet part just added</returns>
        public OpenXmlSDK.WorksheetPart Add(XDocument worksheet)
        {
            // Associates base content to a new worksheet part
            OpenXmlSDK.WorkbookPart  workbook      = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
            OpenXmlSDK.WorksheetPart worksheetPart = workbook.AddNewPart <OpenXmlSDK.WorksheetPart>();
            XDocument worksheetDocument            = parentDocument.GetXDocument(worksheetPart);

            if (worksheetDocument.Root == null)
            {
                worksheetDocument.Add(
                    new XElement("root")
                    );
            }
            worksheetDocument.Root.ReplaceWith(worksheet.Root);

            // Associates the worksheet part to the workbook part
            XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);
            int       sheetId  =
                document.Root
                .Element(ns + "sheets")
                .Elements(ns + "sheet")
                .Count() + 1;

            int worksheetCount =
                document.Root
                .Element(ns + "sheets")
                .Elements(ns + "sheet")
                .Where(
                    t =>
                    t.Attribute("name").Value.StartsWith("sheet", StringComparison.OrdinalIgnoreCase)
                    )
                .Count() + 1;

            // Adds content to workbook document to reference worksheet document
            document.Root
            .Element(ns + "sheets")
            .Add(
                new XElement(ns + "sheet",
                             new XAttribute("name", string.Format("sheet{0}", worksheetCount)),
                             new XAttribute("sheetId", sheetId),
                             new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(worksheetPart))
                             )
                );
            return(worksheetPart);
        }
Пример #2
0
        public Worksheet AddSheet(WorkbookPart workbookPart, string name)
        {
            var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();

            // add single Sheet
            var workSheetpart = workbookPart.AddNewPart<WorksheetPart>();
            workSheetpart.Worksheet = new Worksheet(new SheetData());
            workSheetpart.Worksheet.Save();

            var sheet = new Sheet
            {
                Id = workbookPart.GetIdOfPart(workSheetpart),
                SheetId = (uint)(workbookPart.Workbook.Sheets.Count() + 1),
                Name = name
            };

            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return workSheetpart.Worksheet;
        }
Пример #3
0
/*
 *      static Excel.SharedStringTable SaveSharedStringTable()
 *      {
 *          var exportedSharedStringTable = new Excel.SharedStringTable();
 *
 *          return exportedSharedStringTable;
 *      }
 */

        static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId)
        {
            var    exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>();
            string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart);

            var exportedWorksheet = new Excel.Worksheet();

            exportedWorksheetPart.Worksheet = exportedWorksheet;

            var exportedColumns = new Excel.Columns();

            exportedWorksheet.Append(exportedColumns);

            var exportedSheetData = new Excel.SheetData();

            exportedWorksheet.Append(exportedSheetData);

            var exportedSheet = new Excel.Sheet()
            {
                Name = sheet.Name, Id = relId, SheetId = sheetId
            };

            if (sheet.Hidden)
            {
                exportedSheet.State = Excel.SheetStateValues.Hidden;
            }
            exportedSheets.Append(exportedSheet);

            foreach (var column in sheet.Columns.OrderBy(r => r.Index))
            {
                SaveColumn(exportedColumns, column);
            }

            foreach (var row in sheet.Rows.OrderBy(r => r.Index))
            {
                SaveRow(exportedSheetData, styleSheet, cellFormatList, row);
            }

            exportedWorksheetPart.Worksheet.Save();
        }
Пример #4
0
        /// <summary>
        /// 插入worksheet
        /// </summary>
        /// <param name="workbookPart"></param>
        /// <returns></returns>
        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName = null)
        {
            //創建一個新的WorkssheetPart(後面將用它來容納具體的Sheet)
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();
 
            //取得Sheet集合
            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            if (sheets == null)
            {
                sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());
            }
 
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
 
            //得到Sheet的唯一序號
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }
 
            string sheetTempName = "Sheet" + sheetId;
 
            if (sheetName != null)
            {
                bool hasSameName = false;
                //檢測是否有重名
                foreach (var item in sheets.Elements<Sheet>())
                {
                    if (item.Name == sheetName)
                    {
                        hasSameName = true;
                        break;
                    }
                }
                if (!hasSameName)
                {
                    sheetTempName = sheetName;
                }
            }
 
            //創建Sheet實例並將它與sheets關聯
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetTempName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();
 
            return newWorksheetPart;
        }
Пример #5
0
        // Given a WorkbookPart, inserts a new worksheet.
        private WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            // We need single sheet only, if there is a sheet, return
            if (workbookPart.WorksheetParts.Count() > 0)
            {
                return workbookPart.WorksheetParts.FirstOrDefault<WorksheetPart>();
            }

            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new sheet.
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            string sheetName = "Sheet" + sheetId;

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return newWorksheetPart;
        }
        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName)
        {
            Sheet foundSheet = null;
            foreach (Sheet sheet in workbookPart.Workbook.Sheets)
            {
                if (sheet.Name == sheetName)
                {
                    foundSheet = sheet;
                    break;
                }
            }

            if (foundSheet == null)
            {
                // Add a new worksheet part to the workbook.
                WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();

                Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

                // Get a unique ID for the new sheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                // Append the new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append(sheet);
                workbookPart.Workbook.Save();
                return newWorksheetPart;
            }
            else
            {
                return (WorksheetPart)workbookPart.GetPartById(foundSheet.Id);
            }
        }
Пример #7
0
        private void OpenExistingSpreadsheet(string SheetNameOnOpen)
        {
            xl = SpreadsheetDocument.Open(memstream, true);
            wbp = xl.WorkbookPart;
            IsNewSpreadsheet = false;
            slwb = new SLWorkbook();

            this.DocumentProperties = new SLDocumentProperties();
            this.LoadDocumentProperties();

            InitialiseAutoFitCache();

            LoadBuiltInNumberingFormats();
            InitialiseStylesheetWhatNots(SLThemeTypeValues.Office);
            LoadSharedStringTable();

            giWorksheetIdCounter = 0;
            using (OpenXmlReader oxr = OpenXmlReader.Create(wbp))
            {
                SLWorkbookView wv;
                Sheet s;
                SLSheet sheet;
                DefinedName dn;
                SLDefinedName sldn;
                while (oxr.Read())
                {
                    if (oxr.ElementType == typeof(WorkbookView))
                    {
                        wv = new SLWorkbookView();
                        wv.FromWorkbookView((WorkbookView)oxr.LoadCurrentElement());
                        slwb.WorkbookViews.Add(wv);
                    }
                    else if (oxr.ElementType == typeof(Sheet))
                    {
                        s = (Sheet)oxr.LoadCurrentElement();
                        sheet = new SLSheet(s.Name.Value, s.SheetId.Value, s.Id.Value, SLSheetType.Unknown);
                        if (s.State != null) sheet.State = s.State.Value;
                        slwb.Sheets.Add(sheet);
                        if (sheet.SheetId > giWorksheetIdCounter)
                        {
                            giWorksheetIdCounter = (int)sheet.SheetId;
                        }
                    }
                    else if (oxr.ElementType == typeof(DefinedName))
                    {
                        dn = (DefinedName)oxr.LoadCurrentElement();
                        sldn = new SLDefinedName(dn.Name.Value);
                        sldn.FromDefinedName(dn);
                        slwb.DefinedNames.Add(sldn);
                    }
                    else if (oxr.ElementType == typeof(PivotCache))
                    {
                        // cache IDs supposed to be unique, so I'm not gonna check for the hash set
                        slwb.PivotTableCacheIds.Add(((PivotCache)oxr.LoadCurrentElement()).CacheId.Value);
                    }
                }
            }

            if (wbp.Workbook.WorkbookProperties != null)
            {
                slwb.WorkbookProperties.FromWorkbookProperties(wbp.Workbook.WorkbookProperties);
            }

            if (wbp.CalculationChainPart != null)
            {
                int iCurrentSheetId = 0;
                SLCalculationCell slcc = new SLCalculationCell(string.Empty);
                CalculationCell cc;
                using (OpenXmlReader oxr = OpenXmlReader.Create(wbp.CalculationChainPart))
                {
                    while (oxr.Read())
                    {
                        if (oxr.ElementType == typeof(CalculationCell))
                        {
                            cc = (CalculationCell)oxr.LoadCurrentElement();
                            if (cc.SheetId == null)
                            {
                                cc.SheetId = iCurrentSheetId;
                            }
                            else
                            {
                                if (cc.SheetId.Value != iCurrentSheetId)
                                    iCurrentSheetId = cc.SheetId.Value;
                            }
                            slcc.FromCalculationCell(cc);
                            slwb.CalculationCells.Add(slcc.Clone());
                        }
                    }
                }
            }

            // To determine the type of sheet. Do this before the part
            // where the table and pivot table parts are set.

            bool bFound = false;
            string sRelID = string.Empty;
            foreach (SLSheet sheet in slwb.Sheets)
            {
                bFound = false;
                foreach (WorksheetPart wspFound in wbp.WorksheetParts)
                {
                    sRelID = wbp.GetIdOfPart(wspFound);
                    if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                    {
                        sheet.SheetType = SLSheetType.Worksheet;
                        bFound = true;
                        break;
                    }
                }

                if (!bFound)
                {
                    foreach (ChartsheetPart csp in wbp.ChartsheetParts)
                    {
                        sRelID = wbp.GetIdOfPart(csp);
                        if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                        {
                            sheet.SheetType = SLSheetType.Chartsheet;
                            bFound = true;
                            break;
                        }
                    }
                }

                if (!bFound)
                {
                    foreach (DialogsheetPart dsp in wbp.DialogsheetParts)
                    {
                        sRelID = wbp.GetIdOfPart(dsp);
                        if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                        {
                            sheet.SheetType = SLSheetType.DialogSheet;
                            bFound = true;
                            break;
                        }
                    }
                }

                if (!bFound)
                {
                    foreach (MacroSheetPart msp in wbp.MacroSheetParts)
                    {
                        sRelID = wbp.GetIdOfPart(msp);
                        if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                        {
                            sheet.SheetType = SLSheetType.Macrosheet;
                            bFound = true;
                            break;
                        }
                    }
                }
            }

            WorksheetPart wsp;
            foreach (SLSheet sheet in slwb.Sheets)
            {
                if (sheet.SheetType == SLSheetType.Worksheet)
                {
                    wsp = (WorksheetPart)wbp.GetPartById(sheet.Id);
                    foreach (TableDefinitionPart tdp in wsp.TableDefinitionParts)
                    {
                        if (tdp.Table.Id != null && !slwb.TableIds.Contains(tdp.Table.Id.Value))
                            slwb.TableIds.Add(tdp.Table.Id.Value);

                        if (tdp.Table.Name != null && !slwb.TableNames.Contains(tdp.Table.Name.Value))
                            slwb.TableNames.Add(tdp.Table.Name.Value);
                    }

                    foreach (PivotTablePart ptp in wsp.PivotTableParts)
                    {
                        if (ptp.PivotTableDefinition.Name != null
                            && !slwb.PivotTableNames.Contains(ptp.PivotTableDefinition.Name.Value))
                            slwb.PivotTableNames.Add(ptp.PivotTableDefinition.Name.Value);

                        // the cache ID should already be added, from the workbook part above.
                        // But we check again just to be sure. Cache IDs have to be unique throughout
                        // the workbook.
                        if (ptp.PivotTableDefinition.CacheId != null
                            && !slwb.PivotTableCacheIds.Contains(ptp.PivotTableDefinition.CacheId.Value))
                            slwb.PivotTableCacheIds.Add(ptp.PivotTableDefinition.CacheId.Value);
                    }
                }
            }

            string sWorksheetName = SLConstants.DefaultFirstSheetName;
            int i = 1;
            bool bCannotFind = true;
            bool bIsLegit = true;
            if (wbp.WorksheetParts.Count() == 0)
            {
                // no worksheets! Apparently an Excel file with only 1 dialog sheet is perfectly legit...
                // come up with a legit worksheet name that's not already taken...
                i = 1;
                bCannotFind = true;
                while (bCannotFind)
                {
                    sWorksheetName = string.Format("Sheet{0}", i);
                    bIsLegit = true;
                    foreach (SLSheet sheet in slwb.Sheets)
                    {
                        if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase))
                        {
                            bIsLegit = false;
                            break;
                        }
                    }
                    ++i;
                    if (bIsLegit) bCannotFind = false;
                }

                AddWorksheet(sWorksheetName);
            }
            else
            {
                bFound = false;
                // there's a given worksheet name
                if (SheetNameOnOpen.Length > 0)
                {
                    foreach (SLSheet sheet in slwb.Sheets)
                    {
                        if (sheet.Name.Equals(SheetNameOnOpen, StringComparison.OrdinalIgnoreCase)
                            && sheet.SheetType == SLSheetType.Worksheet)
                        {
                            giSelectedWorksheetID = sheet.SheetId;
                            gsSelectedWorksheetName = sheet.Name;
                            gsSelectedWorksheetRelationshipID = sheet.Id;
                            bFound = true;
                            break;
                        }
                    }
                }
                else
                {
                    // we try to get the "actively selected" worksheet already selected.
                    uint iActiveTab = 0;
                    if (slwb.WorkbookViews.Count > 0)
                    {
                        iActiveTab = slwb.WorkbookViews[0].ActiveTab;
                    }

                    // there should be at least *this* number of sheets (whether it's a worksheet
                    // chartsheet or whatnot).
                    if (slwb.Sheets.Count > iActiveTab
                        && slwb.Sheets[(int)iActiveTab].SheetType == SLSheetType.Worksheet)
                    {
                        giSelectedWorksheetID = slwb.Sheets[(int)iActiveTab].SheetId;
                        gsSelectedWorksheetName = slwb.Sheets[(int)iActiveTab].Name;
                        gsSelectedWorksheetRelationshipID = slwb.Sheets[(int)iActiveTab].Id;
                        bFound = true;
                    }
                }

                if (!bFound)
                {
                    // we get here either if there's no given worksheet name (bFound is still false),
                    // or there's a given worksheet name but corresponding values weren't found.
                    // The given worksheet name must be that of a worksheet. A chartsheet name is
                    // considered "invalid".
                    // Either way, we use the first available worksheet as the selected worksheet.
                    wsp = wbp.WorksheetParts.First();
                    sRelID = wbp.GetIdOfPart(wsp);

                    foreach (SLSheet sheet in slwb.Sheets)
                    {
                        if (sheet.Id.Equals(sRelID, StringComparison.OrdinalIgnoreCase))
                        {
                            giSelectedWorksheetID = sheet.SheetId;
                            gsSelectedWorksheetName = sheet.Name;
                            gsSelectedWorksheetRelationshipID = sheet.Id;
                            bFound = true;
                            break;
                        }
                    }
                }

                if (bFound)
                {
                    // A viable worksheet should be found by now. Otherwise, it's probably
                    // a corrupted spreadsheet...
                    LoadSelectedWorksheet();
                    IsNewWorksheet = false;
                }
                else
                {
                    // why is it not found!?! The file is corrupted somehow... we'll try to recover
                    // by adding a new worksheet and selecting it. Same algorithm as above.
                    i = 1;
                    bCannotFind = true;
                    while (bCannotFind)
                    {
                        sWorksheetName = string.Format("Sheet{0}", i);
                        bIsLegit = true;
                        foreach (SLSheet sheet in slwb.Sheets)
                        {
                            if (sheet.Name.Equals(sWorksheetName, StringComparison.OrdinalIgnoreCase))
                            {
                                bIsLegit = false;
                                break;
                            }
                        }
                        ++i;
                        if (bIsLegit) bCannotFind = false;
                    }

                    AddWorksheet(sWorksheetName);
                }
            }
        }
Пример #8
0
    public void LoadData(string sheetName, uint sheetId, DataTable dt)
    {
        DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart    = null;
        DocumentFormat.OpenXml.Spreadsheet.SheetData   sheetData = null;
        DocumentFormat.OpenXml.Spreadsheet.Sheets      sheets    = null;
        DocumentFormat.OpenXml.Spreadsheet.Sheet       sheet     = null;
        uint   rowIndex = 1;
        uint   colIndex = 1;
        string collName = string.Empty;
        Row    row      = null;
        Cell   cell     = null;

        sheetData = new SheetData();

        // Header Row
        row = new Row()
        {
            RowIndex = rowIndex
        };
        sheetData.Append(row);

        // Header Row cellls
        foreach (DataColumn dc in dt.Columns)
        {
            collName = ExcelHelper.ConvertColumnNumberToName((int)colIndex);

            cell = new Cell()
            {
                CellReference = collName + rowIndex
            };
            row.AppendChild(cell);

            Text txt = new Text();
            txt.Text = dc.ColumnName;
            InlineString inStr = new InlineString();
            inStr.AppendChild(txt);

            cell.DataType = CellValues.InlineString;
            cell.AppendChild(inStr);

            colIndex++;
        }
        rowIndex++;

        foreach (DataRow dr in dt.Rows)
        {
            row = new Row()
            {
                RowIndex = rowIndex
            };
            sheetData.Append(row);

            colIndex = 1;
            foreach (DataColumn dc in dt.Columns)
            {
                collName = ExcelHelper.ConvertColumnNumberToName((int)colIndex);
                cell     = new Cell()
                {
                    CellReference = collName + rowIndex
                };
                row.AppendChild(cell);

                Text txt = new Text();
                txt.Text = dr[dc.ColumnName].ToString();
                InlineString inStr = new InlineString();
                inStr.AppendChild(txt);

                cell.DataType = CellValues.InlineString;
                cell.AppendChild(inStr);

                colIndex++;
            }

            rowIndex++;
        }

        wsPart           = wbPart.AddNewPart <WorksheetPart>();
        wsPart.Worksheet = new Worksheet(sheetData);

        sheet         = new Sheet();
        sheet.Id      = wbPart.GetIdOfPart(wsPart);
        sheet.SheetId = sheetId;
        sheet.Name    = sheetName;

        sheets = wbPart.Workbook.AppendChild(new Sheets());
        sheets.Append(sheet);
    }
Пример #9
0
        /// <summary>
        /// Creates a chartsheet part from given data
        /// </summary>
        /// <param name="chartType">Type of chart to generate</param>
        /// <param name="values">Values to represent in the chart</param>
        /// <param name="headerReference">Columns to be used as series</param>
        /// <param name="categoryReference">Column to be used as category</param>
        /// <returns>Chartsheet part with contents related</returns>
        public OpenXmlSDK.ChartsheetPart Create(ChartType chartType, List <string> values, List <string> headerReference, string categoryReference)
        {
            //Creates base content and associates it to a new chartsheet part
            XDocument chartsheet = CreateEmptyChartsheet();

            OpenXmlSDK.WorkbookPart   workbook       = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
            OpenXmlSDK.ChartsheetPart chartsheetPart = workbook.AddNewPart <OpenXmlSDK.ChartsheetPart>();
            XDocument chartsheetDocument             = parentDocument.GetXDocument(chartsheetPart);
            XDocument newChartsheetDocument          = CreateEmptyChartsheet();

            if (chartsheetDocument.Root == null)
            {
                chartsheetDocument.Add(
                    newChartsheetDocument.Root
                    );
            }
            else
            {
                chartsheetDocument.Root.ReplaceWith(newChartsheetDocument.Root);
            }

            //Creates a base drawings part and associates it to the chartsheet part
            OpenXmlSDK.DrawingsPart drawingsPart = chartsheetPart.AddNewPart <OpenXmlSDK.DrawingsPart>();
            XDocument drawingsDocument           = parentDocument.GetXDocument(drawingsPart);
            XDocument newDrawingDocument         = CreateEmptyDrawing();

            if (drawingsDocument.Root == null)
            {
                drawingsDocument.Add(
                    newDrawingDocument.Root
                    );
            }
            else
            {
                drawingsDocument.Root.ReplaceWith(newDrawingDocument.Root);
            }

            //Adds content to chartsheet document to reference drawing document
            chartsheetDocument
            .Element(ns + "chartsheet")
            .Add(
                new XElement(ns + "drawing",
                             new XAttribute(relationshipsns + "id", chartsheetPart.GetIdOfPart(drawingsPart))
                             )
                );

            //creates the chart part and associates it to the drawings part
            OpenXmlSDK.ChartPart chartPart        = drawingsPart.AddNewPart <OpenXmlSDK.ChartPart>();
            XDocument            chartDocument    = parentDocument.GetXDocument(chartPart);
            XDocument            newChartDocument = CreateChart(chartType, values, headerReference, categoryReference);// CreateEmptyChart();

            if (chartDocument.Root == null)
            {
                chartDocument.Add(
                    newChartDocument.Root
                    );
            }
            else
            {
                chartDocument.Root.ReplaceWith(newChartDocument.Root);
            }

            //Adds content to drawing document to reference chart document
            drawingsDocument
            .Descendants(drawingns + "graphicData")
            .First()
            .Add(
                new XAttribute("uri", chartns),
                new XElement(chartns + "chart",
                             new XAttribute(XNamespace.Xmlns + "c", chartns),
                             new XAttribute(XNamespace.Xmlns + "r", relationshipsns),
                             new XAttribute(relationshipsns + "id", drawingsPart.GetIdOfPart(chartPart))
                             )
                );

            //Associates the chartsheet part to the workbook part
            XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);

            int sheetId = document.Root.Element(ns + "sheets").Elements(ns + "sheet").Count() + 1;

            int chartsheetCount =
                document.Root
                .Element(ns + "sheets")
                .Elements(ns + "sheet")
                .Where(
                    t =>
                    t.Attribute("name").Value.StartsWith("chart")
                    )
                .Count() + 1;

            //Adds content to workbook document to reference chartsheet document
            document.Root
            .Element(ns + "sheets")
            .Add(
                new XElement(ns + "sheet",
                             new XAttribute("name", string.Format("chart{0}", chartsheetCount)),
                             new XAttribute("sheetId", sheetId),
                             new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(chartsheetPart))
                             )
                );

            return(chartsheetPart);
        }
Пример #10
0
        /// <summary>
        ///     Copies a sheet with a given sheet name and saves it with the clonedSheetName in the workbook
        /// </summary>
        /// <param name="docType">DocType to created for temp file in the method</param>
        /// <param name="workbookPart">Workbook which the worksheet to be copied and the copied worksheet to be saved</param>
        /// <param name="sheetName">Name of the worksheet to copy</param>
        /// <param name="clonedSheetName">New name of the copied sheet</param>
        private void copySheet(SpreadsheetDocumentType docType,
                               WorkbookPart workbookPart, string sheetName,
                               string clonedSheetName)
        {
            //Get the source sheet to be copied
            WorksheetPart sourceSheetPart = getWorksheetPart(workbookPart,
                                                             sheetName);
            //Take advantage of AddPart for deep cloning
            SpreadsheetDocument tempSheet =
                SpreadsheetDocument.Create(new MemoryStream(), docType);
            WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart();
            WorksheetPart tempWorksheetPart =
                tempWorkbookPart.AddPart(sourceSheetPart);
            //Add cloned sheet and all associated parts to workbook
            WorksheetPart clonedSheet = workbookPart.AddPart(tempWorksheetPart);

            //Table definition parts are somewhat special and need unique ids...so let's make an id based on count
            int numTableDefParts =
                sourceSheetPart.GetPartsCountOfType<TableDefinitionPart>();
            _tableId = numTableDefParts;
            //Clean up table definition parts (tables need unique ids)
            if (numTableDefParts != 0) {
                fixupTableParts(clonedSheet);
            }
            //There should only be one sheet that has focus
            cleanView(clonedSheet);

            //Add new sheet to main workbook part
            var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            var copiedSheet = new Sheet();
            copiedSheet.Name = clonedSheetName;
            copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet);
            copiedSheet.SheetId = (uint) sheets.ChildElements.Count + 1;
            sheets.Append(copiedSheet);
            //Save Changes
            workbookPart.Workbook.Save();
        }
Пример #11
0
        /// <summary>
        /// Generate service order report
        /// </summary>
        /// <param name="itemsource">Item source</param>
        /// <param name="workbookPart">Worbook part</param>
        private static void GenerateServiceOrder(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets,int sheetId,string logoPath)
        {
            if (itemsource.IsSelectedServiceOrder)
            {

                // Remove the sheet reference from the workbook.
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                // The SheetData object will contain all the data.
                SheetData sheetData = new SheetData();
                Worksheet worksheet = new Worksheet();

                Form serviceOrder = itemsource.ServiceOrderData;

                Row rowTitle;
                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[9];
                for (int n = 0; n < 9; n++)
                    excelColumnNamesTitle[n] = GetExcelColumnName(n);

                //build the title
                for (int i = 1; i <= 6; i++)
                {
                    rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
                    for (int cellval = 0; cellval < 9; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    sheetData.Append(rowTitle);
                }

                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the business application name
                UpdateStringCellValue("B2", itemsource.BusinessApplicationName, currentRowTitle, 5);

                //merge all cells in the title
                MergeCell mergeCell = new MergeCell();
                mergeCell.Reference = "B2:E2";
                mergeCells.Append(mergeCell);

                currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
                //add the form name
                UpdateStringCellValue("B4", itemsource.ServiceOrderSheetName, currentRowTitle, 5);

                //merge all cell in the form name
                mergeCell = new MergeCell();
                mergeCell.Reference = "B4:E4";
                mergeCells.Append(mergeCell);
                Drawing drawing = AddLogo(logoPath, worksheetPart);
                Columns columns = new Columns();
                columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 26));
                columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 73));

                worksheet.Append(columns);

                int rowIndex = 8;
                Row sectionRow;

                foreach (var section in serviceOrder.Sections)
                {
                    sectionRow = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                    mergeCell = new MergeCell();
                    mergeCell.Reference = "A" + rowIndex + ":B" + rowIndex;
                    mergeCells.Append(mergeCell);
                    AppendTextCell("A" + rowIndex, section.Caption, sectionRow, 6);
                    AppendTextCell("B" + rowIndex, string.Empty, sectionRow, 6);
                    sheetData.Append(sectionRow);
                    foreach (var element in section.FormElements)
                    {
                        rowIndex++;
                        //The current row is obtained for updating the value of the cell
                        Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                        switch (element.Field.FieldType)
                        {
                            case FieldType.Catalogue:
                                AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
                                if (!string.IsNullOrEmpty(element.Field.FieldValue))
                                {
                                    string catalogueValue = CatalogueBusiness.GetCatalogueValue(new Guid(element.Field.FieldValue)).CatalogueValueData;
                                    AppendTextCell("B" + rowIndex.ToString(), catalogueValue, rowData, 1);
                                }
                                else
                                {
                                    AppendTextCell("B" + rowIndex.ToString(), string.Empty, rowData, 1);
                                }
                                break;
                            case FieldType.RegularExpressionText:
                            case FieldType.Time:
                            case FieldType.SingleTextLine:
                            case FieldType.MultipleTextLine:
                            case FieldType.Datepicker:
                                AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
                                AppendTextCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
                                break;
                            case FieldType.Boolean:
                                string boolValue = element.Field.FieldValue == "True" ? LanguageResource.Yes : LanguageResource.No;
                                AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
                                AppendTextCell("B" + rowIndex.ToString(), boolValue, rowData, 1);
                                break;
                            case FieldType.Integer:
                            case FieldType.Decimal:
                                AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
                                AppendNumberCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
                                break;
                            default:
                                break;
                        }

                        sheetData.Append(rowData);
                    }
                    rowIndex+=2;
                }

                worksheet.Append(sheetData);
                //add merged cells
                worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                worksheet.Append(drawing);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                //create the new sheet for this report
                Sheet sheet = new Sheet() { Name = itemsource.ServiceOrderSheetName, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                sheetId++;
            }
        }
Пример #12
0
        /// <summary>
        /// Generate inspection reports
        /// </summary>
        /// <param name="itemsource">Item source</param>
        /// <param name="workbookPart">Worbook part</param>
        private static void GenerateAllInspectionReports(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets, int sheetId, string logoPath)
        {
            //  Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
            foreach (var item in itemsource.InspectionReports)
            {
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();

                Worksheet worksheet = new Worksheet();
                SheetData sheetData1 = new SheetData();

                //get the number of columns in the report
                Row rowTitle;
                int numberOfColumnsCaption = item.Value.Captions.Count;

                //get the string name of the columns
                string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
                for (int n = 0; n < numberOfColumnsCaption; n++)
                    excelColumnNamesTitle[n] = GetExcelColumnName(n);

                //build the title
                for (int i = 1; i <= 6; i++)
                {
                    rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
                    for (int cellval = 0; cellval < numberOfColumnsCaption; cellval++)
                    {
                        AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
                    }
                    sheetData1.Append(rowTitle);
                }

                MergeCells mergeCells = new MergeCells();

                Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
                //add the business application name
                UpdateStringCellValue("B2", item.Value.BusinessApplicationName, currentRowTitle, 5);

                string lastColumnName = excelColumnNamesTitle.Last() + "2";
                //merge all cells in the title
                MergeCell mergeCell = new MergeCell();
                mergeCell.Reference = "B2:" + lastColumnName;
                mergeCells.Append(mergeCell);

                currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
                //add the form name
                UpdateStringCellValue("B4", item.Key, currentRowTitle, 5);

                lastColumnName = lastColumnName.Replace("2", "4");

                //merge all cell in the form name
                mergeCell = new MergeCell();
                mergeCell.Reference = "B4:" + lastColumnName;
                mergeCells.Append(mergeCell);

                Drawing drawing = AddLogo(logoPath, worksheetPart);

                int rowIndex = 7;

                //get the names of the columns
                string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
                for (int n = 0; n < numberOfColumnsCaption; n++)
                    excelColumnNamesCaptions[n] = GetExcelColumnName(n);

                Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                //build column names of the report
                Columns columns = new Columns();
                for (int i = 0; i < item.Value.Captions.Count; i++)
                {
                    var caption = item.Value.Captions[i];
                    AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
                    columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
                }
                sheetData1.Append(rowCaption);
                //add the new row with the name of the columns
                worksheet.Append(columns);
                rowIndex = 8;
                //write the data of the report
                foreach (var row in item.Value.DataRows)
                {
                    int numberOfColumnsData = row.FieldValues.Count;
                    //get column names
                    string[] excelColumnNamesData = new string[numberOfColumnsData];
                    for (int n = 0; n < numberOfColumnsData; n++)
                        excelColumnNamesData[n] = GetExcelColumnName(n);

                    //build the row
                    Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
                    for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
                    {
                        DynamicDataRowValue col = row.FieldValues[colInx];
                        switch (col.FieldType)
                        {
                            case (int)FieldType.Catalogue:
                            case (int)FieldType.RegularExpressionText:
                            case (int)FieldType.Time:
                            case (int)FieldType.SingleTextLine:
                            case (int)FieldType.MultipleTextLine:
                            case (int)FieldType.Datepicker:
                            case (int)FieldType.Boolean:
                            case (int)FieldType.AutoComplete:
                            case (int)FieldType.StatusField:
                                AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
                                break;
                            case (int)FieldType.Integer:
                            case (int)FieldType.Decimal:
                                AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
                                break;
                            default:
                                break;
                        }
                    }

                    //add the new row to the report
                    sheetData1.Append(rowData);
                    rowIndex++;
                }

                //add the information of the current sheet
                worksheet.Append(sheetData1);
                //add merged cells
                worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
                worksheet.Append(drawing);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();

                //create the new sheet for this report
                Sheet sheet = new Sheet() { Name = item.Key, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
                sheets.Append(sheet);
                sheetId++;
            }
        }
        protected internal override void CreateDocument()
        {
            Report r = base.Report();
            _workbookPart = _spreadSheet.AddWorkbookPart();

            var openXmlExportHelper = new OpenXmlWriterHelper();
            _styleSheet = openXmlExportHelper.CreateDefaultStylesheet();

            _workbookPart.Workbook = _workbook;
            var sheets = _workbook.AppendChild<Sheets>(new Sheets());

            // create worksheet 1
            var worksheetPart =_workbookPart.AddNewPart<WorksheetPart>();
            var sheet = new Sheet() { Id = _workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = r.Name };
            sheets.Append(sheet);

            _workSheetSettings.Add(new WorkSheetSetting(r.Name));

            _writer = OpenXmlWriter.Create(worksheetPart);
            _writer.WriteStartElement(new Worksheet());
            _writer.WriteStartElement(new SheetData());

               _currentWorkSheet = _workSheetSettings.Last();
        }
        //private static void AddTextToWorkSheet(string path, string sheetName, string text, string columnName, uint rowIndex)
        //{
        //    // Open the document for editing.
        //    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true))
        //    {
        //        // Get the SharedStringTablePart. If it does not exist, create a new one.
        //        SharedStringTablePart shareStringPart;
        //        if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
        //        {
        //            shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
        //        }
        //        else
        //        {
        //            shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();
        //        }
        //        // Insert the text into the SharedStringTablePart.
        //        int index = InsertSharedStringItem(spreadSheet.WorkbookPart, text);
        //        // Insert a new worksheet.
        //        WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart, sheetName);
        //        // Insert cell A1 into the new worksheet.
        //        Cell cell = InsertCellInWorksheet(worksheetPart, columnName, rowIndex);
        //        // Set the value of cell A1.
        //        cell.CellValue = new CellValue(index.ToString());
        //        cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
        //        // Save the new worksheet.
        //        worksheetPart.Worksheet.Save();
        //    }
        //}
        public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string sheetName)
        {
            Sheet foundSheet = workbookPart.Workbook.Sheets.Cast<Sheet>().FirstOrDefault(sheet => sheet.Name == sheetName);

            if (foundSheet == null)
            {
                // Add a new worksheet part to the workbook.
                var newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                newWorksheetPart.Worksheet = new Worksheet(new SheetData());
                newWorksheetPart.Worksheet.Save();

                var sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
                string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

                // Get a unique ID for the new sheet.
                uint sheetId = 1;
                if (sheets.Elements<Sheet>().Count() > 0)
                {
                    sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                // Append the new worksheet and associate it with the workbook.
                var sheet = new Sheet { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                sheets.Append((IEnumerable<OpenXmlElement>)sheet);
                return newWorksheetPart;
            }

            return (WorksheetPart)workbookPart.GetPartById(foundSheet.Id);
        }
Пример #15
0
        // Given a WorkbookPart, inserts a new worksheet.
        public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            // Add a new worksheet part to the workbook.
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            newWorksheetPart.Worksheet = new Worksheet(new SheetData());
            newWorksheetPart.Worksheet.Save();

            // GetFirstChild <T>: Find the first child element in type T
            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            // Get a unique ID for the new worksheet.
            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Count() > 0)
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            // Give the new worksheet a name.
            string sheetName = "Sheet" + sheetId;

            // Append the new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return newWorksheetPart;
        }