コード例 #1
0
ファイル: LogLib.cs プロジェクト: Rossano/Dome_Control
        private WorksheetPart wsSheet = null; //  WorkSheetPart

        #endregion Fields

        #region Constructors

        public LogGenerator(string fn)
        {
            ////  get spreadsheet path from constructor
            //path = folder;
            ////  File name is based on date and time
            //DateTime now = DateTime.Now;
            ////  Construct the spreadsheet filename
            //string fn = string.Format("{0}\\report_{1}-{2}-{3}_{4}{5}{6}.xlsx",
            //    path, now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second);
            if (File.Exists(fn))
            {
                doc = SpreadsheetDocument.Open(fn, true);
            }
            else
            {
                //
                //  Create the Spreadsheet document
                //
                doc = SpreadsheetDocument.Create(fn, SpreadsheetDocumentType.Workbook);
                //
                //  Add WoorkBookPart to the document
                //
                wbPart = doc.AddWorkbookPart();
                wbPart.Workbook = new Workbook();
                wbPart.Workbook.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                //
                //  Add WorkSheetPart to the WorkBookPart
                //
                wsSheet = wbPart.AddNewPart<WorksheetPart>();
                wsSheet.Worksheet = new Worksheet(new SheetData());
                wsSheet.Worksheet.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                //
                //  Add sheets to the WorkBook
                //
                sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                //
                //  Append new sheet and associate it with the workbook
                //
                sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(wsSheet), SheetId = 1, Name = wsName };
                sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                sheets.Append(sheet);

                wbPart.Workbook.Save();
                //CreatePackage(fn);

                //if (File.Exists(fn))
                //{
                //    TestXls();
                //}
            }
        }
コード例 #2
0
        public override void Save(ResearchResult result)
        {
            if (!Directory.Exists(storageStr))
            {
                Directory.CreateDirectory(storageStr);
            }

            string fileName = storageStr + result.ResearchName;
            if (File.Exists(fileName + ".xls") || File.Exists(fileName + ".xlsx"))
                fileName += result.ResearchID;

            document = SpreadsheetDocument.Create(fileName + ".xlsx", SpreadsheetDocumentType.Workbook);
            workbook = document.AddWorkbookPart();
            workbook.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbook.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = document.WorkbookPart.Workbook.
                AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = document.WorkbookPart.
                    GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "mySheet"
            };
            sheets.Append(sheet);

            /*SaveResearchInfo(result.ResearchID, result.ResearchName,
                    result.ResearchType, result.ModelType, result.RealizationCount,
                    result.Size, result.Edges);
            SaveResearchParameters(result.ResearchParameterValues);
            SaveGenerationParameters(result.GenerationParameterValues);

            for (int i = 0; i < result.EnsembleResults.Count; ++i)
            {
                SaveEnsembleResult(result.EnsembleResults[i], i);
            }*/

            workbook.Workbook.Save();
            document.Close();
        }
コード例 #3
0
ファイル: GroupOrder.cs プロジェクト: Bulgrak/praktikpbasw
        /// <summary>
        /// Creates the sheet
        /// </summary>
        /// <param name="sheets">The sheet for this item</param>
        /// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
        /// <param name="workbookPart">The workbookpart associated with the spreadsheet</param>
        public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart)
        {
            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            Sheet sheet = new Sheet
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 3U,       // <-- Change for each sheet that is created
                Name = _groupOrder.SheetName
            };
            sheets.Append(sheet);

            //Add cells to the sheet
            InsertTextIntoCells(spreadsheetDocument, worksheetPart);
        }
コード例 #4
0
ファイル: Resources.cs プロジェクト: Bulgrak/praktikpbasw
        public void CreateSheet(Sheets sheets, SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookPart)
        {
            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            Sheet sheet = new Sheet
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 4U,
                Name = _resources.SheetName
            };
            sheets.Append(sheet);

            //Add cells to the sheet
            InsertTextIntoCells(spreadsheetDocument, worksheetPart);
        }
コード例 #5
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);
        }
コード例 #6
0
ファイル: OpenXmlWrapper.cs プロジェクト: haimtz/OpenXml-demo
        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;
        }
コード例 #7
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();
        }
コード例 #8
0
        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);
            }
        }
コード例 #9
0
ファイル: ExcelUtil.cs プロジェクト: paopaofeng/dp2
        // Given the main workbook part, and a text value, insert the text into 
        // the shared string table. Create the table if necessary. If the value 
        // already exists, return its index. If it doesn't exist, insert it and 
        // return its new index.
        internal static int InsertSharedStringItem(WorkbookPart wbPart, string value)
        {
            int index = 0;
            bool found = false;
            var stringTablePart = wbPart
                .GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

            // If the shared string table is missing, something's wrong.
            // Just return the index that you found in the cell.
            // Otherwise, look up the correct text in the table.
            if (stringTablePart == null)
            {
                // Create it.
                stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
            }

            var stringTable = stringTablePart.SharedStringTable;
            if (stringTable == null)
            {
                stringTable = new SharedStringTable();
                stringTablePart.SharedStringTable = stringTable;
            }

            // Iterate through all the items in the SharedStringTable. 
            // If the text already exists, return its index.
            foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == value)
                {
                    found = true;
                    break;
                }
                index += 1;
            }

            if (!found)
            {
                stringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(value)));
                stringTable.Save();
            }

            return index;
        }
コード例 #10
0
        //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);
        }
コード例 #11
0
ファイル: LogLib.cs プロジェクト: Rossano/Dome_Control
        //
        //  Insert the string into the shared string table of the WorkBookPart
        //
        private int insertSharedStringItem(WorkbookPart wbPart, string Value)
        {
            //
            //  Look if the string already exists into the SharedStringTable, if it does return it
            //  else return a new one
            //
            int index = 0;
            bool found = false;
            //
            // If the shared string table is missing, something's wrong.
            // Just return the index that you found in the cell.
            // Otherwise, look up the correct text in the table.
            //
            var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
            if (stringTablePart == null)
            {
                //  Create a new SharedString
                stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
            }

            var stringTable = stringTablePart.SharedStringTable;
            if (stringTable == null)
            {
                stringTable = new SharedStringTable();
            }
            //
            // Iterate through all the items in the SharedStringTable.
            // If the text already exists, return its index.
            //
            foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == Value)
                {
                    found = true;
                    break;
                }
                index++;
            }
            //
            //  if not found append a new SharedString to the table
            //
            if (!found)
            {
                stringTable.AppendChild(new SharedStringItem(new Text(Value)));
                try
                {
                    stringTable.Save();
                }
                catch (Exception) { }
            }

            return index;
        }
コード例 #12
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);
    }
コード例 #13
0
ファイル: ExcelBusiness.cs プロジェクト: ramirobr/VestalisV3
        /// <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++;
            }
        }
コード例 #14
0
        private void GenerateCalculationChainPartContent(WorkbookPart workbookPart, SaveContext context)
        {
            var thisRelId = context.RelIdGenerator.GetNext(RelType.Workbook);
            if (workbookPart.CalculationChainPart == null)
                workbookPart.AddNewPart<CalculationChainPart>(thisRelId);

            if (workbookPart.CalculationChainPart.CalculationChain == null)
                workbookPart.CalculationChainPart.CalculationChain = new CalculationChain();

            var calculationChain = workbookPart.CalculationChainPart.CalculationChain;
            calculationChain.RemoveAllChildren<CalculationCell>();

            foreach (var worksheet in WorksheetsInternal)
            {
                var cellsWithoutFormulas = new HashSet<String>();
                foreach (var c in worksheet.Internals.CellsCollection.GetCells())
                {
                    if (XLHelper.IsNullOrWhiteSpace(c.FormulaA1))
                        cellsWithoutFormulas.Add(c.Address.ToStringRelative());
                    else
                    {
                        if (c.FormulaA1.StartsWith("{"))
                        {
                            var cc = new CalculationCell
                            {
                                CellReference = c.Address.ToString(),
                                SheetId = worksheet.SheetId
                            };

                            if (c.FormulaReference.FirstAddress.Equals(c.Address))
                            {
                                cc.Array = true;
                                calculationChain.AppendChild(cc);
                                calculationChain.AppendChild(new CalculationCell {CellReference = c.Address.ToString(), InChildChain = true});
                            }
                            else
                            {
                                calculationChain.AppendChild(cc);
                            }
                        }
                        else
                        {
                            calculationChain.AppendChild(new CalculationCell
                            {
                                CellReference = c.Address.ToString(),
                                SheetId = worksheet.SheetId
                            });
                        }
                    }
                }

                //var cCellsToRemove = new List<CalculationCell>();
                var m = from cc in calculationChain.Elements<CalculationCell>()
                    where !(cc.SheetId != null || cc.InChildChain != null)
                          && calculationChain.Elements<CalculationCell>()
                              .Where(c1 => c1.SheetId != null)
                              .Select(c1 => c1.CellReference.Value)
                              .Contains(cc.CellReference.Value)
                          || cellsWithoutFormulas.Contains(cc.CellReference.Value)
                    select cc;
                //m.ToList().ForEach(cc => cCellsToRemove.Add(cc));
                m.ToList().ForEach(cc => calculationChain.RemoveChild(cc));
            }

            if (!calculationChain.Any())
                workbookPart.DeletePart(workbookPart.CalculationChainPart);
        }
コード例 #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;
        }
コード例 #16
0
ファイル: Form1.cs プロジェクト: jhpeng/ExcelSample
        /// <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;
        }
コード例 #17
0
        static Excel.Stylesheet SaveStyleSheet(Package.WorkbookPart exportedWorkbookPart, ref Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document)
        {
            var exportedStyleSheetPart = exportedWorkbookPart.AddNewPart <Package.WorkbookStylesPart>();

            cellFormatList = new Dictionary <CellFormat, uint>();
            Excel.Stylesheet exportedStyleSheet = new Excel.Stylesheet();
            exportedStyleSheetPart.Stylesheet = exportedStyleSheet;
            exportedStyleSheet.CellFormats    = new Excel.CellFormats();
            exportedStyleSheet.CellFormats.Append(new Excel.CellFormat()
            {
                NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
            });
            exportedStyleSheet.Fills = new Excel.Fills();
            exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
            {
                PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.None)
            }));

            exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
            {
                PatternType = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Gray125)
            }));
            exportedStyleSheet.Fonts = new Excel.Fonts();
            exportedStyleSheet.Fonts.Append(new Excel.Font()
            {
                FontSize = new Excel.FontSize()
                {
                    Val = 11
                },
                Color = new Excel.Color()
                {
                    Theme = 1
                },
                FontName = new Excel.FontName()
                {
                    Val = "Calibri"
                },
                FontFamilyNumbering = new Excel.FontFamilyNumbering()
                {
                    Val = 2
                },
                FontScheme = new Excel.FontScheme()
                {
                    Val = new OpenXml.EnumValue <Excel.FontSchemeValues>(Excel.FontSchemeValues.Minor)
                }
            });
            exportedStyleSheet.Borders = new Excel.Borders();
            exportedStyleSheet.Borders.Append(new Excel.Border()
            {
                LeftBorder     = new Excel.LeftBorder(),
                RightBorder    = new Excel.RightBorder(),
                TopBorder      = new Excel.TopBorder(),
                BottomBorder   = new Excel.BottomBorder(),
                DiagonalBorder = new Excel.DiagonalBorder()
            });
            exportedStyleSheet.CellStyleFormats = new Excel.CellStyleFormats();
            exportedStyleSheet.CellStyleFormats.Append(new Excel.CellFormat()
            {
                NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
            });
            exportedStyleSheet.CellStyles = new Excel.CellStyles();
            exportedStyleSheet.CellStyles.Append(new Excel.CellStyle()
            {
                Name = "Normal", FormatId = 0, BuiltinId = 0
            });
            exportedStyleSheet.DifferentialFormats = new Excel.DifferentialFormats();
            exportedStyleSheet.TableStyles         = new Excel.TableStyles()
            {
                DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16"
            };

            foreach (var cellFormat in document.CellFormats())
            {
                if (cellFormat.Count() > 0)
                {
                    cellFormatList.Add(cellFormat, (uint)exportedStyleSheet.CellFormats.ChildElements.Count());
                    exportedStyleSheet.CellFormats.Append(new Excel.CellFormat()
                    {
                        NumberFormatId = 0,
                        FontId         = 0,
                        BorderId       = 0,
                        FormatId       = 0,
                        FillId         = (uint)exportedStyleSheet.Fills.ChildElements.Count(),
                        ApplyFill      = true
                    });
                    exportedStyleSheet.Fills.Append(new Excel.Fill(new Excel.PatternFill()
                    {
                        PatternType     = new OpenXml.EnumValue <Excel.PatternValues>(Excel.PatternValues.Solid),
                        ForegroundColor = new Excel.ForegroundColor()
                        {
                            Rgb = new OpenXml.HexBinaryValue(cellFormat.FillColor)
                        }
                    }));
                }
            }

            exportedStyleSheet.Save();
            return(exportedStyleSheet);
        }
コード例 #18
0
        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();
        }
コード例 #19
0
        public virtual void SaveCustomStylesheet(WorkbookPart workbookPart)
        {
            //get a copy of the default excel style sheet then add additional styles to it
            var stylesheet = CreateDefaultStylesheet();

            // ***************************** Fills *********************************
            var fills = stylesheet.Fills;

            //header fills background color
            var fill = new Fill();
            var patternFill = new PatternFill();
            patternFill.PatternType = PatternValues.Solid;
            //patternFill.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("C8EEFF") };
            patternFill.BackgroundColor = new BackgroundColor() { Indexed = 64 };
            fill.PatternFill = patternFill;
            fills.AppendChild(fill);
            fills.Count = (uint)fills.ChildElements.Count;

            // *************************** numbering formats ***********************
            var nfs = stylesheet.NumberingFormats;
            //number less than 164 is reserved by excel for default formats
            uint iExcelIndex = 165;
            NumberingFormat nf;
            nf = new NumberingFormat();
            nf.NumberFormatId = iExcelIndex++;
            nf.FormatCode = @"[$-409]m/d/yy\ h:mm\ AM/PM;@";
            nfs.Append(nf);

            nfs.Count = (uint)nfs.ChildElements.Count;

            //************************** cell formats ***********************************
            var cfs = stylesheet.CellFormats;//this should already contain a default StyleIndex of 0

            var cf = new CellFormat();// Date time format is defined as StyleIndex = 1
            cf.NumberFormatId = nf.NumberFormatId;
            cf.FontId = 0;
            cf.FillId = 0;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cf.ApplyNumberFormat = true;
            cfs.Append(cf);

            cf = new CellFormat();// Header format is defined as StyleINdex = 2
            cf.NumberFormatId = 0;
            cf.FontId = 0;
            cf.FillId = 2;
            cf.ApplyFill = true;
            cf.BorderId = 0;
            cf.FormatId = 0;
            cfs.Append(cf);

            cfs.Count = (uint)cfs.ChildElements.Count;

            var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
            var style = workbookStylesPart.Stylesheet = stylesheet;
            style.Save();
        }
コード例 #20
0
 /// <summary>
 /// write out the share string xml.  Call this after writing out all shared string values in sheet
 /// </summary>
 /// <param name="workbookPart"></param>
 public void CreateShareStringPart(WorkbookPart workbookPart)
 {
     if (_shareStringMaxIndex > 0)
     {
         var sharedStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
         using (var writer = OpenXmlWriter.Create(sharedStringPart))
         {
             writer.WriteStartElement(new SharedStringTable());
             foreach (var item in _shareStringDictionary)
             {
                 writer.WriteStartElement(new SharedStringItem());
                 writer.WriteElement(new Text(item.Key));
                 writer.WriteEndElement();
             }
             writer.WriteEndElement();
         }
     }
 }
コード例 #21
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;
        }
コード例 #22
0
        //private void GenerateDrawingsPartContent(DrawingsPart drawingsPart, XLWorksheet worksheet)
        //{
        //    if (drawingsPart.WorksheetDrawing == null)
        //        drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();

        //    var worksheetDrawing = drawingsPart.WorksheetDrawing;

        //    if (!worksheetDrawing.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing")))
        //        worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
        //    if (!worksheetDrawing.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("a", "http://schemas.openxmlformats.org/drawingml/2006/main")))
        //        worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");

        //    foreach (var chart in worksheet.Charts.OrderBy(c => c.ZOrder).Select(c => c))
        //    {
        //        Xdr.TwoCellAnchor twoCellAnchor = new Xdr.TwoCellAnchor();
        //        worksheetDrawing.AppendChild(twoCellAnchor);
        //        if (chart.Anchor == XLDrawingAnchor.MoveAndSizeWithCells)
        //            twoCellAnchor.EditAs = Xdr.EditAsValues.TwoCell;
        //        else if (chart.Anchor == XLDrawingAnchor.MoveWithCells)
        //            twoCellAnchor.EditAs = Xdr.EditAsValues.OneCell;
        //        else
        //            twoCellAnchor.EditAs = Xdr.EditAsValues.Absolute;

        //        if (twoCellAnchor.FromMarker == null)
        //            twoCellAnchor.FromMarker = new Xdr.FromMarker();
        //        twoCellAnchor.FromMarker.RowId = new Xdr.RowId((chart.FirstRow - 1).ToString());
        //        twoCellAnchor.FromMarker.RowOffset = new Xdr.RowOffset(chart.FirstRowOffset.ToString());
        //        twoCellAnchor.FromMarker.ColumnId = new Xdr.ColumnId((chart.FirstColumn - 1).ToString());
        //        twoCellAnchor.FromMarker.ColumnOffset = new Xdr.ColumnOffset(chart.FirstColumnOffset.ToString());

        //        if (twoCellAnchor.ToMarker == null)
        //            twoCellAnchor.ToMarker = new Xdr.ToMarker();
        //        twoCellAnchor.ToMarker.RowId = new Xdr.RowId((chart.LastRow - 1).ToString());
        //        twoCellAnchor.ToMarker.RowOffset = new Xdr.RowOffset(chart.LastRowOffset.ToString());
        //        twoCellAnchor.ToMarker.ColumnId = new Xdr.ColumnId((chart.LastColumn - 1).ToString());
        //        twoCellAnchor.ToMarker.ColumnOffset = new Xdr.ColumnOffset(chart.LastColumnOffset.ToString());

        //        Xdr.GraphicFrame graphicFrame = new Xdr.GraphicFrame();
        //        twoCellAnchor.AppendChild(graphicFrame);

        //        if (graphicFrame.NonVisualGraphicFrameProperties == null)
        //            graphicFrame.NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties();

        //        if (graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties == null)
        //            graphicFrame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties() { Id = (UInt32)chart.Id, Name = chart.Name, Description = chart.Description, Hidden = chart.Hidden };
        //        if (graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties == null)
        //            graphicFrame.NonVisualGraphicFrameProperties.NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties();

        //        if (graphicFrame.Transform == null)
        //            graphicFrame.Transform = new Xdr.Transform();

        //        if (chart.HorizontalFlip)
        //            graphicFrame.Transform.HorizontalFlip = true;
        //        else
        //            graphicFrame.Transform.HorizontalFlip = null;

        //        if (chart.VerticalFlip)
        //            graphicFrame.Transform.VerticalFlip = true;
        //        else
        //            graphicFrame.Transform.VerticalFlip = null;

        //        if (chart.Rotation != 0)
        //            graphicFrame.Transform.Rotation = chart.Rotation;
        //        else
        //            graphicFrame.Transform.Rotation = null;

        //        if (graphicFrame.Transform.Offset == null)
        //            graphicFrame.Transform.Offset = new A.Offset();

        //        graphicFrame.Transform.Offset.X = chart.OffsetX;
        //        graphicFrame.Transform.Offset.Y = chart.OffsetY;

        //        if (graphicFrame.Transform.Extents == null)
        //            graphicFrame.Transform.Extents = new A.Extents();

        //        graphicFrame.Transform.Extents.Cx = chart.ExtentLength;
        //        graphicFrame.Transform.Extents.Cy = chart.ExtentWidth;

        //        if (graphicFrame.Graphic == null)
        //            graphicFrame.Graphic = new A.Graphic();

        //        if (graphicFrame.Graphic.GraphicData == null)
        //            graphicFrame.Graphic.GraphicData = new A.GraphicData() { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" };

        //        if (!graphicFrame.Graphic.GraphicData.Elements<C.ChartReference>().Any())
        //        {
        //            C.ChartReference chartReference = new C.ChartReference() { Id = "rId" + chart.Id.ToStringLookup() };
        //            chartReference.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
        //            chartReference.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

        //            graphicFrame.Graphic.GraphicData.AppendChild(chartReference);
        //        }

        //        if (!twoCellAnchor.Elements<Xdr.ClientData>().Any())
        //            twoCellAnchor.AppendChild(new Xdr.ClientData());
        //    }
        //}

        //private void GenerateChartPartContent(ChartPart chartPart, XLChart xlChart)
        //{
        //    if (chartPart.ChartSpace == null)
        //        chartPart.ChartSpace = new C.ChartSpace();

        //    C.ChartSpace chartSpace = chartPart.ChartSpace;

        //    if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("c", "http://schemas.openxmlformats.org/drawingml/2006/chart")))
        //        chartSpace.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
        //    if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("a", "http://schemas.openxmlformats.org/drawingml/2006/main")))
        //        chartSpace.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
        //    if (!chartSpace.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
        //        chartSpace.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

        //    if (chartSpace.EditingLanguage == null)
        //        chartSpace.EditingLanguage = new C.EditingLanguage() { Val = CultureInfo.CurrentCulture.Name };
        //    else
        //        chartSpace.EditingLanguage.Val = CultureInfo.CurrentCulture.Name;

        //    C.Chart chart = new C.Chart();
        //    chartSpace.AppendChild(chart);

        //    if (chart.Title == null)
        //        chart.Title = new C.Title();

        //    if (chart.Title.Layout == null)
        //        chart.Title.Layout = new C.Layout();

        //    if (chart.View3D == null)
        //        chart.View3D = new C.View3D();

        //    if (chart.View3D.RightAngleAxes == null)
        //        chart.View3D.RightAngleAxes = new C.RightAngleAxes();

        //    chart.View3D.RightAngleAxes.Val = xlChart.RightAngleAxes;

        //    if (chart.PlotArea == null)
        //        chart.PlotArea = new C.PlotArea();

        //    if (chart.PlotArea.Layout == null)
        //        chart.PlotArea.Layout = new C.Layout();

        //    OpenXmlElement chartElement = GetChartElement(xlChart);

        //    chart.PlotArea.AppendChild(chartElement);

        //    C.CategoryAxis categoryAxis1 = new C.CategoryAxis();
        //    C.AxisId axisId4 = new C.AxisId() { Val = (UInt32Value)71429120U };

        //    C.Scaling scaling1 = new C.Scaling();
        //    C.Orientation orientation1 = new C.Orientation() { Val = C.OrientationValues.MinMax };

        //    scaling1.AppendChild(orientation1);
        //    C.AxisPosition axisPosition1 = new C.AxisPosition() { Val = C.AxisPositionValues.Bottom };
        //    C.TickLabelPosition tickLabelPosition1 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo };
        //    C.CrossingAxis crossingAxis1 = new C.CrossingAxis() { Val = (UInt32Value)71432064U };
        //    C.Crosses crosses1 = new C.Crosses() { Val = C.CrossesValues.AutoZero };
        //    C.AutoLabeled autoLabeled1 = new C.AutoLabeled() { Val = true };
        //    C.LabelAlignment labelAlignment1 = new C.LabelAlignment() { Val = C.LabelAlignmentValues.Center };
        //    C.LabelOffset labelOffset1 = new C.LabelOffset() { Val = (UInt16Value)100U };

        //    categoryAxis1.AppendChild(axisId4);
        //    categoryAxis1.AppendChild(scaling1);
        //    categoryAxis1.AppendChild(axisPosition1);
        //    categoryAxis1.AppendChild(tickLabelPosition1);
        //    categoryAxis1.AppendChild(crossingAxis1);
        //    categoryAxis1.AppendChild(crosses1);
        //    categoryAxis1.AppendChild(autoLabeled1);
        //    categoryAxis1.AppendChild(labelAlignment1);
        //    categoryAxis1.AppendChild(labelOffset1);

        //    C.ValueAxis valueAxis1 = new C.ValueAxis();
        //    C.AxisId axisId5 = new C.AxisId() { Val = (UInt32Value)71432064U };

        //    C.Scaling scaling2 = new C.Scaling();
        //    C.Orientation orientation2 = new C.Orientation() { Val = C.OrientationValues.MinMax };

        //    scaling2.AppendChild(orientation2);
        //    C.AxisPosition axisPosition2 = new C.AxisPosition() { Val = C.AxisPositionValues.Left };
        //    C.MajorGridlines majorGridlines1 = new C.MajorGridlines();
        //    C.NumberingFormat numberingFormat1 = new C.NumberingFormat() { FormatCode = "General", SourceLinked = true };
        //    C.TickLabelPosition tickLabelPosition2 = new C.TickLabelPosition() { Val = C.TickLabelPositionValues.NextTo };
        //    C.CrossingAxis crossingAxis2 = new C.CrossingAxis() { Val = (UInt32Value)71429120U };
        //    C.Crosses crosses2 = new C.Crosses() { Val = C.CrossesValues.AutoZero };
        //    C.CrossBetween crossBetween1 = new C.CrossBetween() { Val = C.CrossBetweenValues.Between };

        //    valueAxis1.AppendChild(axisId5);
        //    valueAxis1.AppendChild(scaling2);
        //    valueAxis1.AppendChild(axisPosition2);
        //    valueAxis1.AppendChild(majorGridlines1);
        //    valueAxis1.AppendChild(numberingFormat1);
        //    valueAxis1.AppendChild(tickLabelPosition2);
        //    valueAxis1.AppendChild(crossingAxis2);
        //    valueAxis1.AppendChild(crosses2);
        //    valueAxis1.AppendChild(crossBetween1);

        //    plotArea.AppendChild(bar3DChart1);
        //    plotArea.AppendChild(categoryAxis1);
        //    plotArea.AppendChild(valueAxis1);

        //    C.Legend legend1 = new C.Legend();
        //    C.LegendPosition legendPosition1 = new C.LegendPosition() { Val = C.LegendPositionValues.Right };
        //    C.Layout layout3 = new C.Layout();

        //    legend1.AppendChild(legendPosition1);
        //    legend1.AppendChild(layout3);
        //    C.PlotVisibleOnly plotVisibleOnly1 = new C.PlotVisibleOnly() { Val = true };

        //    chart.AppendChild(legend1);
        //    chart.AppendChild(plotVisibleOnly1);

        //    C.PrintSettings printSettings1 = new C.PrintSettings();
        //    C.HeaderFooter headerFooter1 = new C.HeaderFooter();
        //    C.PageMargins pageMargins4 = new C.PageMargins() { Left = 0.70000000000000018D, Right = 0.70000000000000018D, Top = 0.75000000000000022D, Bottom = 0.75000000000000022D, Header = 0.3000000000000001D, Footer = 0.3000000000000001D };
        //    C.PageSetup pageSetup1 = new C.PageSetup();

        //    printSettings1.AppendChild(headerFooter1);
        //    printSettings1.AppendChild(pageMargins4);
        //    printSettings1.AppendChild(pageSetup1);

        //    chartSpace.AppendChild(printSettings1);

        //}

        //private OpenXmlElement GetChartElement(XLChart xlChart)
        //{
        //    if (xlChart.ChartTypeCategory == XLChartTypeCategory.Bar3D)
        //        return GetBar3DChart(xlChart);
        //    else
        //        return null;
        //}

        //private OpenXmlElement GetBar3DChart(XLChart xlChart)
        //{

        //    C.Bar3DChart bar3DChart = new C.Bar3DChart();
        //    bar3DChart.BarDirection = new C.BarDirection() { Val = GetBarDirection(xlChart) };
        //    bar3DChart.BarGrouping = new C.BarGrouping() { Val = GetBarGrouping(xlChart) };

        //    C.BarChartSeries barChartSeries = new C.BarChartSeries();
        //    barChartSeries.Index = new C.Index() { Val = (UInt32Value)0U };
        //    barChartSeries.Order = new C.Order() { Val = (UInt32Value)0U };

        //    C.SeriesText seriesText1 = new C.SeriesText();

        //    C.StringReference stringReference1 = new C.StringReference();
        //    C.Formula formula1 = new C.Formula();
        //    formula1.Text = "Sheet1!$B$1";

        //    stringReference1.AppendChild(formula1);

        //    seriesText1.AppendChild(stringReference1);

        //    C.CategoryAxisData categoryAxisData1 = new C.CategoryAxisData();

        //    C.StringReference stringReference2 = new C.StringReference();
        //    C.Formula formula2 = new C.Formula();
        //    formula2.Text = "Sheet1!$A$2:$A$3";

        //    C.StringCache stringCache2 = new C.StringCache();
        //    C.PointCount pointCount2 = new C.PointCount() { Val = (UInt32Value)2U };

        //    C.StringPoint stringPoint2 = new C.StringPoint() { Index = (UInt32Value)0U };
        //    C.NumericValue numericValue2 = new C.NumericValue();
        //    numericValue2.Text = "A";

        //    stringPoint2.AppendChild(numericValue2);

        //    C.StringPoint stringPoint3 = new C.StringPoint() { Index = (UInt32Value)1U };
        //    C.NumericValue numericValue3 = new C.NumericValue();
        //    numericValue3.Text = "B";

        //    stringPoint3.AppendChild(numericValue3);

        //    stringCache2.AppendChild(pointCount2);
        //    stringCache2.AppendChild(stringPoint2);
        //    stringCache2.AppendChild(stringPoint3);

        //    stringReference2.AppendChild(formula2);
        //    stringReference2.AppendChild(stringCache2);

        //    categoryAxisData1.AppendChild(stringReference2);

        //    C.Values values1 = new C.Values();

        //    C.NumberReference numberReference1 = new C.NumberReference();
        //    C.Formula formula3 = new C.Formula();
        //    formula3.Text = "Sheet1!$B$2:$B$3";

        //    C.NumberingCache numberingCache1 = new C.NumberingCache();
        //    C.FormatCode formatCode1 = new C.FormatCode();
        //    formatCode1.Text = "General";
        //    C.PointCount pointCount3 = new C.PointCount() { Val = (UInt32Value)2U };

        //    C.NumericPoint numericPoint1 = new C.NumericPoint() { Index = (UInt32Value)0U };
        //    C.NumericValue numericValue4 = new C.NumericValue();
        //    numericValue4.Text = "5";

        //    numericPoint1.AppendChild(numericValue4);

        //    C.NumericPoint numericPoint2 = new C.NumericPoint() { Index = (UInt32Value)1U };
        //    C.NumericValue numericValue5 = new C.NumericValue();
        //    numericValue5.Text = "10";

        //    numericPoint2.AppendChild(numericValue5);

        //    numberingCache1.AppendChild(formatCode1);
        //    numberingCache1.AppendChild(pointCount3);
        //    numberingCache1.AppendChild(numericPoint1);
        //    numberingCache1.AppendChild(numericPoint2);

        //    numberReference1.AppendChild(formula3);
        //    numberReference1.AppendChild(numberingCache1);

        //    values1.AppendChild(numberReference1);

        //    barChartSeries.AppendChild(index1);
        //    barChartSeries.AppendChild(order1);
        //    barChartSeries.AppendChild(seriesText1);
        //    barChartSeries.AppendChild(categoryAxisData1);
        //    barChartSeries.AppendChild(values1);
        //    C.Shape shape1 = new C.Shape() { Val = C.ShapeValues.Box };
        //    C.AxisId axisId1 = new C.AxisId() { Val = (UInt32Value)71429120U };
        //    C.AxisId axisId2 = new C.AxisId() { Val = (UInt32Value)71432064U };
        //    C.AxisId axisId3 = new C.AxisId() { Val = (UInt32Value)0U };

        //    bar3DChart.AppendChild(barChartSeries);
        //    bar3DChart.AppendChild(shape1);
        //    bar3DChart.AppendChild(axisId1);
        //    bar3DChart.AppendChild(axisId2);
        //    bar3DChart.AppendChild(axisId3);

        //    return bar3DChart;
        //}

        //private C.BarGroupingValues GetBarGrouping(XLChart xlChart)
        //{
        //    if (xlChart.BarGrouping == XLBarGrouping.Clustered)
        //        return C.BarGroupingValues.Clustered;
        //    else if (xlChart.BarGrouping == XLBarGrouping.Percent)
        //        return C.BarGroupingValues.PercentStacked;
        //    else if (xlChart.BarGrouping == XLBarGrouping.Stacked)
        //        return C.BarGroupingValues.Stacked;
        //    else
        //        return C.BarGroupingValues.Standard;
        //}

        //private C.BarDirectionValues GetBarDirection(XLChart xlChart)
        //{
        //    if (xlChart.BarOrientation == XLBarOrientation.Vertical)
        //        return C.BarDirectionValues.Column;
        //    else
        //        return C.BarDirectionValues.Bar;
        //}
        //--

        private static void GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart,
            XLWorksheet xlWorksheet,
            SaveContext context)
        {
            foreach (var pt in xlWorksheet.PivotTables)
            {
                var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook);

                var pivotTableCacheDefinitionPart = workbookPart.AddNewPart<PivotTableCacheDefinitionPart>(ptCdp);
                GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt);

                var pivotCaches = new PivotCaches();
                var pivotCache = new PivotCache {CacheId = 0U, Id = ptCdp};

                pivotCaches.AppendChild(pivotCache);

                workbookPart.Workbook.AppendChild(pivotCaches);

                var pivotTablePart =
                    worksheetPart.AddNewPart<PivotTablePart>(context.RelIdGenerator.GetNext(RelType.Workbook));
                GeneratePivotTablePartContent(pivotTablePart, pt);

                pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook));
            }
        }
コード例 #23
0
 private static SharedStringTablePart GetSharedStringPart(WorkbookPart workbookPart)
 {
     return workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0 ? workbookPart.GetPartsOfType<SharedStringTablePart>().First() : workbookPart.AddNewPart<SharedStringTablePart>();
 }
コード例 #24
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);
        }
コード例 #25
0
ファイル: ExcelBusiness.cs プロジェクト: ramirobr/VestalisV3
        /// <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++;
            }
        }
コード例 #26
0
ファイル: Form1.cs プロジェクト: jhpeng/ExcelSample
        /// <summary>
        /// 創建一個SharedStringTablePart(相當於各Sheet共用的存放字符串的容器)
        /// </summary>
        /// <param name="workbookPart"></param>
        /// <returns></returns>
        private static SharedStringTablePart CreateSharedStringTablePart(WorkbookPart workbookPart)
        {
            SharedStringTablePart shareStringPart = null;
            if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
            {
                shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
            }
            else
            {
                shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
            }
            return shareStringPart;
        }
コード例 #27
0
ファイル: ExcellWorker.cs プロジェクト: gerasyana/Academy
        /// <summary>
        /// Заполнение ячейки данными
        /// </summary>
        /// <param name="wbPart"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private int InsertSharedStringItem(WorkbookPart wbPart, string value)
        {
            var index = 0;
            var found = false;
            var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault() ??
                                  wbPart.AddNewPart<SharedStringTablePart>();

            var stringTable = stringTablePart.SharedStringTable ?? new SharedStringTable();

            foreach (var item in stringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == value)
                {
                    found = true;
                    break;
                }
                index += 1;
            }

            if (found) return index;
            stringTable.AppendChild(new SharedStringItem(new Text(value)));
            stringTable.Save();

            return index;
        }