using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text.RegularExpressions; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using Drawing = DocumentFormat.OpenXml.Drawing; namespace Kreta.BusinessLogic.Classes.ExcelHelpers { public class ExcelExportManager { bool needProtecttion = false; public MemoryStream CreateExcelExport(List data, string versionNumber = null, bool needProtecttion = false, bool fejlec = false) { // excel fajl letrehozasa var memoryStream = new MemoryStream(); using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook)) { this.needProtecttion = needProtecttion; CreateExcelWorkbook(spreadSheet, data, fejlec: fejlec); } memoryStream.Position = 0; return memoryStream; } void CreateExcelWorkbook(SpreadsheetDocument spreadSheet, List data, bool fejlec = false) { // Add workbook WorkbookPart workbookPart = spreadSheet.AddWorkbookPart(); workbookPart.Workbook = new Workbook(new Sheets()); workbookPart.Workbook.Save(); // temak ThemePart themePart = workbookPart.AddNewPart(); GenerateThemePart().Save(themePart); // stilusok WorkbookStylesPart WorkbookstylesPart = workbookPart.AddNewPart(); GenerateWorkbookStylesPart().Save(WorkbookstylesPart); if (!fejlec) { foreach (var item in data) { WorksheetPart newWorksheetPart = InsertWorksheet(workbookPart, item.SheetCaption); // Get the SharedStringTablePart. If it does not exist, create a new one. SharedStringTablePart shareStringPart; if (spreadSheet.WorkbookPart.GetPartsOfType().Any()) { shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType().First(); } else { shareStringPart = spreadSheet.WorkbookPart.AddNewPart(); } if (needProtecttion && !newWorksheetPart.Worksheet.Descendants().Any()) { // lapvedelem SheetProtection sheetProtection = new SheetProtection() { Password = "CA75", Sheet = true, Objects = true, Scenarios = true, FormatCells = false, FormatRows = false, Sort = false, AutoFilter = false }; newWorksheetPart.Worksheet.Append(sheetProtection); } // sheethez cellák felvétele InsertDataIntoCells(newWorksheetPart.Worksheet, shareStringPart, item); } } else { WorksheetPart newWorksheetPart; List dataReszlet = new List(); foreach (var item in data) { if (item.SheetCaption[0].ToString() == "_") { dataReszlet.Add(item); item.SheetCaption = item.SheetCaption.Substring(1); newWorksheetPart = InsertWorksheet(workbookPart, item.SheetCaption); // Get the SharedStringTablePart. If it does not exist, create a new one. SharedStringTablePart shareStringPart; if (spreadSheet.WorkbookPart.GetPartsOfType().Any()) { shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType().First(); } else { shareStringPart = spreadSheet.WorkbookPart.AddNewPart(); } if (needProtecttion && !newWorksheetPart.Worksheet.Descendants().Any()) { // lapvedelem SheetProtection sheetProtection = new SheetProtection() { Password = "CA75", Sheet = true, Objects = true, Scenarios = true, FormatCells = false, FormatRows = false, Sort = false, AutoFilter = false }; newWorksheetPart.Worksheet.Append(sheetProtection); } // sheethez cellák felvétele InsertDataIntoCellsList(newWorksheetPart.Worksheet, shareStringPart, dataReszlet); dataReszlet.Clear(); } else { dataReszlet.Add(item); } } } } #region Insert worksheet // Given a WorkbookPart, inserts a new worksheet. private WorksheetPart InsertWorksheet(WorkbookPart workbookPart, string SheetName) { // Add a new worksheet part to the workbook. WorksheetPart newWorksheetPart = workbookPart.AddNewPart(); newWorksheetPart.Worksheet = new Worksheet(new Columns(), new SheetData()); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); // Get a unique ID for the new sheet. uint sheetId = 1; if (sheets.Elements().Any()) { sheetId = sheets.Elements().Select(s => s.SheetId.Value).Max() + 1; } // Append the new worksheet and associate it with the workbook. //eltávolítjuk a nem meg felelő charaktereket a sheetNameből //{ '.', '?', '!', '*', '/', '[', ']', '\'', ':' }; if (string.IsNullOrWhiteSpace(SheetName)) { // ha nincs benne szoveg, akkor bele kell tenni, mert kulonben elszall SheetName = "Kréta.NET export"; } SheetName = new Regex(@"[\?,\!,\*,\/,\[,\],\\,\:]").Replace(SheetName, string.Empty); if (SheetName.Length > 30) SheetName = SheetName.Substring(0, 30); Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = SheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); return newWorksheetPart; } #endregion #region InsertStyles private Stylesheet GenerateWorkbookStylesPart() { var element = new Stylesheet( new Fonts( new Font( new FontSize() { Val = 11D }, new Color() { Theme = (UInt32Value)1U }, new FontName() { Val = "Calibri" }, new FontFamilyNumbering() { Val = 2 }, new FontCharSet() { Val = 238 }, new FontScheme() { Val = FontSchemeValues.Minor }), new Font( new Bold(), new FontSize() { Val = 11D }, new Color() { Theme = (UInt32Value)1U }, new FontName() { Val = "Calibri" }, new FontFamilyNumbering() { Val = 2 }, new FontCharSet() { Val = 238 }, new FontScheme() { Val = FontSchemeValues.Minor }), new Font( new Bold(), new FontSize() { Val = 11D }, new Color() { Rgb = "FFFFFF" }, new FontName() { Val = "Calibri" }, new FontFamilyNumbering() { Val = 2 }, new FontCharSet() { Val = 238 }, new FontScheme() { Val = FontSchemeValues.Minor }) ) { Count = (UInt32Value)3U }, new Fills( new Fill( new PatternFill() { PatternType = PatternValues.None }), new Fill( new PatternFill() { PatternType = PatternValues.Gray125 }), new Fill( new PatternFill( new ForegroundColor() { Theme = (UInt32Value)0U, Tint = new DoubleValue() { InnerText = "-0.249977111117893" } }, new BackgroundColor() { Indexed = (UInt32Value)64U } ) { PatternType = PatternValues.Solid }), new Fill( new PatternFill( new ForegroundColor() { Theme = (UInt32Value)2U }, new BackgroundColor() { Indexed = (UInt32Value)64U } ) { PatternType = PatternValues.Solid }), new Fill( new PatternFill( new ForegroundColor() { Theme = (UInt32Value)0U }, new BackgroundColor() { Indexed = (UInt32Value)64U } ) { PatternType = PatternValues.Solid }), new Fill( new PatternFill( new ForegroundColor() { Rgb = "FFFFCC" }, new BackgroundColor() { Rgb = "FFFFCC" } ) { PatternType = PatternValues.Solid }), new Fill( new PatternFill( new ForegroundColor() { Rgb = "388047" }, new BackgroundColor() { Rgb = "388047" } ) { PatternType = PatternValues.Solid }), new Fill( new PatternFill( new ForegroundColor() { Rgb = "943e41" }, new BackgroundColor() { Rgb = "943e41" } ) { PatternType = PatternValues.Solid }) ) { Count = (UInt32Value)8U }, new Borders( new Border( new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder() ) ) { Count = (UInt32Value)1U }, new CellStyleFormats( new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U } ) { Count = (UInt32Value)1U }, new CellFormats( new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U }, new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, Protection = new Protection() { Locked = false }, ApplyFont = true, ApplyFill = true }, new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, Protection = new Protection() { Locked = true, Hidden = true }, ApplyFont = true, ApplyFill = true, ApplyProtection = true }, new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, WrapText = true }) { NumberFormatId = (UInt32Value)2U, FontId = (UInt32Value)1U, FillId = (UInt32Value)5U, BorderId = (UInt32Value)0U }, //összegző formulához: sárga háttér, félkövér, két tizedes jegy new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, WrapText = true }) { NumberFormatId = (UInt32Value)2U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }, //általános szám formátum: nincs háttér, sima számok, két tizedes jegy new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center, WrapText = true }) { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, Protection = new Protection() { Locked = false }, ApplyFont = true, ApplyFill = true }, new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U }, new CellFormat(new Alignment() { WrapText = true }) { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U }, new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)6U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, Protection = new Protection() { Locked = false }, ApplyFont = true, ApplyFill = true }, new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)7U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, Protection = new Protection() { Locked = false }, ApplyFont = true, ApplyFill = true } ) { Count = (UInt32Value)10U }, new CellStyles( new CellStyle() { Name = "Normál", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U } ) { Count = (UInt32Value)2U }, new DifferentialFormats() { Count = (UInt32Value)0U }, new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" }, new Colors( new MruColors( new Color() { Rgb = new HexBinaryValue() { Value = "FF969696" } }) ) ); return element; } #endregion #region Themes private Drawing.Theme GenerateThemePart() { var element = new Drawing.Theme( new Drawing.ThemeElements( new Drawing.ColorScheme( new Drawing.Dark1Color( new Drawing.SystemColor() { Val = Drawing.SystemColorValues.WindowText, LastColor = new HexBinaryValue() { Value = "000000" } }), new Drawing.Light1Color( new Drawing.SystemColor() { Val = Drawing.SystemColorValues.Window, LastColor = new HexBinaryValue() { Value = "FFFFFF" } }), new Drawing.Dark2Color( new Drawing.RgbColorModelHex() { Val = new HexBinaryValue() { Value = "1F497D" } }), new Drawing.Light2Color( new Drawing.RgbColorModelHex() { Val = new HexBinaryValue() { Value = "EEECE1" } }), new Drawing.Accent1Color( new Drawing.RgbColorModelHex() { Val = new HexBinaryValue() { Value = "4F81BD" } }), new Drawing.Accent2Color( new Drawing.RgbColorModelHex() { Val = new HexBinaryValue() { Value = "C0504D" } }), new Drawing.Accent3Color( new Drawing.RgbColorModelHex() { Val = new HexBinaryValue() { Value = "9BBB59" } }), new Drawing.Accent4Color( new Drawing.RgbColorModelHex() { Val = new HexBinaryValue() { Value = "8064A2" } }), new Drawing.Accent5Color( new Drawing.RgbColorModelHex() { Val = new HexBinaryValue() { Value = "4BACC6" } }), new Drawing.Accent6Color( new Drawing.RgbColorModelHex() { Val = new HexBinaryValue() { Value = "F79646" } }), new Drawing.Hyperlink( new Drawing.RgbColorModelHex() { Val = new HexBinaryValue() { Value = "0000FF" } }), new Drawing.FollowedHyperlinkColor( new Drawing.RgbColorModelHex() { Val = new HexBinaryValue() { Value = "800080" } }) ) { Name = "Office" }, new Drawing.FontScheme( new Drawing.MajorFont( new Drawing.LatinFont() { Typeface = "Cambria" }, new Drawing.EastAsianFont() { Typeface = "" }, new Drawing.ComplexScriptFont() { Typeface = "" }, new Drawing.SupplementalFont() { Script = "Arab", Typeface = "Times New Roman" }, new Drawing.SupplementalFont() { Script = "Hebr", Typeface = "Times New Roman" }, new Drawing.SupplementalFont() { Script = "Thai", Typeface = "Tahoma" }, new Drawing.SupplementalFont() { Script = "Ethi", Typeface = "Nyala" }, new Drawing.SupplementalFont() { Script = "Beng", Typeface = "Vrinda" }, new Drawing.SupplementalFont() { Script = "Gujr", Typeface = "Shruti" }, new Drawing.SupplementalFont() { Script = "Khmr", Typeface = "MoolBoran" }, new Drawing.SupplementalFont() { Script = "Knda", Typeface = "Tunga" }, new Drawing.SupplementalFont() { Script = "Guru", Typeface = "Raavi" }, new Drawing.SupplementalFont() { Script = "Cans", Typeface = "Euphemia" }, new Drawing.SupplementalFont() { Script = "Cher", Typeface = "Plantagenet Cherokee" }, new Drawing.SupplementalFont() { Script = "Yiii", Typeface = "Microsoft Yi Baiti" }, new Drawing.SupplementalFont() { Script = "Tibt", Typeface = "Microsoft Himalaya" }, new Drawing.SupplementalFont() { Script = "Thaa", Typeface = "MV Boli" }, new Drawing.SupplementalFont() { Script = "Deva", Typeface = "Mangal" }, new Drawing.SupplementalFont() { Script = "Telu", Typeface = "Gautami" }, new Drawing.SupplementalFont() { Script = "Taml", Typeface = "Latha" }, new Drawing.SupplementalFont() { Script = "Syrc", Typeface = "Estrangelo Edessa" }, new Drawing.SupplementalFont() { Script = "Orya", Typeface = "Kalinga" }, new Drawing.SupplementalFont() { Script = "Mlym", Typeface = "Kartika" }, new Drawing.SupplementalFont() { Script = "Laoo", Typeface = "DokChampa" }, new Drawing.SupplementalFont() { Script = "Sinh", Typeface = "Iskoola Pota" }, new Drawing.SupplementalFont() { Script = "Mong", Typeface = "Mongolian Baiti" }, new Drawing.SupplementalFont() { Script = "Viet", Typeface = "Times New Roman" }, new Drawing.SupplementalFont() { Script = "Uigh", Typeface = "Microsoft Uighur" }), new Drawing.MinorFont( new Drawing.LatinFont() { Typeface = "Calibri" }, new Drawing.EastAsianFont() { Typeface = "" }, new Drawing.ComplexScriptFont() { Typeface = "" }, new Drawing.SupplementalFont() { Script = "Arab", Typeface = "Arial" }, new Drawing.SupplementalFont() { Script = "Hebr", Typeface = "Arial" }, new Drawing.SupplementalFont() { Script = "Thai", Typeface = "Tahoma" }, new Drawing.SupplementalFont() { Script = "Ethi", Typeface = "Nyala" }, new Drawing.SupplementalFont() { Script = "Beng", Typeface = "Vrinda" }, new Drawing.SupplementalFont() { Script = "Gujr", Typeface = "Shruti" }, new Drawing.SupplementalFont() { Script = "Khmr", Typeface = "DaunPenh" }, new Drawing.SupplementalFont() { Script = "Knda", Typeface = "Tunga" }, new Drawing.SupplementalFont() { Script = "Guru", Typeface = "Raavi" }, new Drawing.SupplementalFont() { Script = "Cans", Typeface = "Euphemia" }, new Drawing.SupplementalFont() { Script = "Cher", Typeface = "Plantagenet Cherokee" }, new Drawing.SupplementalFont() { Script = "Yiii", Typeface = "Microsoft Yi Baiti" }, new Drawing.SupplementalFont() { Script = "Tibt", Typeface = "Microsoft Himalaya" }, new Drawing.SupplementalFont() { Script = "Thaa", Typeface = "MV Boli" }, new Drawing.SupplementalFont() { Script = "Deva", Typeface = "Mangal" }, new Drawing.SupplementalFont() { Script = "Telu", Typeface = "Gautami" }, new Drawing.SupplementalFont() { Script = "Taml", Typeface = "Latha" }, new Drawing.SupplementalFont() { Script = "Syrc", Typeface = "Estrangelo Edessa" }, new Drawing.SupplementalFont() { Script = "Orya", Typeface = "Kalinga" }, new Drawing.SupplementalFont() { Script = "Mlym", Typeface = "Kartika" }, new Drawing.SupplementalFont() { Script = "Laoo", Typeface = "DokChampa" }, new Drawing.SupplementalFont() { Script = "Sinh", Typeface = "Iskoola Pota" }, new Drawing.SupplementalFont() { Script = "Mong", Typeface = "Mongolian Baiti" }, new Drawing.SupplementalFont() { Script = "Viet", Typeface = "Arial" }, new Drawing.SupplementalFont() { Script = "Uigh", Typeface = "Microsoft Uighur" }) ) { Name = "Office" }, new Drawing.FormatScheme( new Drawing.FillStyleList( new Drawing.SolidFill( new Drawing.SchemeColor() { Val = Drawing.SchemeColorValues.PhColor }), new Drawing.GradientFill( new Drawing.GradientStopList( new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Tint() { Val = 50000 }, new Drawing.SaturationModulation() { Val = 300000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 0 }, new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Tint() { Val = 37000 }, new Drawing.SaturationModulation() { Val = 300000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 35000 }, new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Tint() { Val = 15000 }, new Drawing.SaturationModulation() { Val = 350000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 100000 }), new Drawing.LinearGradientFill() { Angle = 16200000, Scaled = true } ) { RotateWithShape = true }, new Drawing.GradientFill( new Drawing.GradientStopList( new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Shade() { Val = 51000 }, new Drawing.SaturationModulation() { Val = 130000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 0 }, new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Shade() { Val = 93000 }, new Drawing.SaturationModulation() { Val = 130000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 80000 }, new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Shade() { Val = 94000 }, new Drawing.SaturationModulation() { Val = 135000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 100000 }), new Drawing.LinearGradientFill() { Angle = 16200000, Scaled = false } ) { RotateWithShape = true }), new Drawing.LineStyleList( new Drawing.Outline( new Drawing.SolidFill( new Drawing.SchemeColor( new Drawing.Shade() { Val = 95000 }, new Drawing.SaturationModulation() { Val = 105000 } ) { Val = Drawing.SchemeColorValues.PhColor }), new Drawing.PresetDash() { Val = Drawing.PresetLineDashValues.Solid } ) { Width = 9525, CapType = Drawing.LineCapValues.Flat, CompoundLineType = Drawing.CompoundLineValues.Single, Alignment = Drawing.PenAlignmentValues.Center }, new Drawing.Outline( new Drawing.SolidFill( new Drawing.SchemeColor() { Val = Drawing.SchemeColorValues.PhColor }), new Drawing.PresetDash() { Val = Drawing.PresetLineDashValues.Solid } ) { Width = 25400, CapType = Drawing.LineCapValues.Flat, CompoundLineType = Drawing.CompoundLineValues.Single, Alignment = Drawing.PenAlignmentValues.Center }, new Drawing.Outline( new Drawing.SolidFill( new Drawing.SchemeColor() { Val = Drawing.SchemeColorValues.PhColor }), new Drawing.PresetDash() { Val = Drawing.PresetLineDashValues.Solid } ) { Width = 38100, CapType = Drawing.LineCapValues.Flat, CompoundLineType = Drawing.CompoundLineValues.Single, Alignment = Drawing.PenAlignmentValues.Center }), new Drawing.EffectStyleList( new Drawing.EffectStyle( new Drawing.EffectList( new Drawing.OuterShadow( new Drawing.RgbColorModelHex( new Drawing.Alpha() { Val = 38000 } ) { Val = new HexBinaryValue() { Value = "000000" } } ) { BlurRadius = 40000L, Distance = 20000L, Direction = 5400000, RotateWithShape = false })), new Drawing.EffectStyle( new Drawing.EffectList( new Drawing.OuterShadow( new Drawing.RgbColorModelHex( new Drawing.Alpha() { Val = 35000 } ) { Val = new HexBinaryValue() { Value = "000000" } } ) { BlurRadius = 40000L, Distance = 23000L, Direction = 5400000, RotateWithShape = false })), new Drawing.EffectStyle( new Drawing.EffectList( new Drawing.OuterShadow( new Drawing.RgbColorModelHex( new Drawing.Alpha() { Val = 35000 } ) { Val = new HexBinaryValue() { Value = "000000" } } ) { BlurRadius = 40000L, Distance = 23000L, Direction = 5400000, RotateWithShape = false }), new Drawing.Scene3DType( new Drawing.Camera( new Drawing.Rotation() { Latitude = 0, Longitude = 0, Revolution = 0 } ) { Preset = Drawing.PresetCameraValues.OrthographicFront }, new Drawing.LightRig( new Drawing.Rotation() { Latitude = 0, Longitude = 0, Revolution = 1200000 } ) { Rig = Drawing.LightRigValues.ThreePoints, Direction = Drawing.LightRigDirectionValues.Top }), new Drawing.Shape3DType( new Drawing.BevelTop() { Width = 63500L, Height = 25400L }))), new Drawing.BackgroundFillStyleList( new Drawing.SolidFill( new Drawing.SchemeColor() { Val = Drawing.SchemeColorValues.PhColor }), new Drawing.GradientFill( new Drawing.GradientStopList( new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Tint() { Val = 40000 }, new Drawing.SaturationModulation() { Val = 350000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 0 }, new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Tint() { Val = 45000 }, new Drawing.Shade() { Val = 99000 }, new Drawing.SaturationModulation() { Val = 350000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 40000 }, new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Shade() { Val = 20000 }, new Drawing.SaturationModulation() { Val = 255000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 100000 }), new Drawing.PathGradientFill( new Drawing.FillToRectangle() { Left = 50000, Top = -80000, Right = 50000, Bottom = 180000 } ) { Path = Drawing.PathShadeValues.Circle } ) { RotateWithShape = true }, new Drawing.GradientFill( new Drawing.GradientStopList( new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Tint() { Val = 80000 }, new Drawing.SaturationModulation() { Val = 300000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 0 }, new Drawing.GradientStop( new Drawing.SchemeColor( new Drawing.Shade() { Val = 30000 }, new Drawing.SaturationModulation() { Val = 200000 } ) { Val = Drawing.SchemeColorValues.PhColor } ) { Position = 100000 }), new Drawing.PathGradientFill( new Drawing.FillToRectangle() { Left = 50000, Top = 50000, Right = 50000, Bottom = 50000 } ) { Path = Drawing.PathShadeValues.Circle } ) { RotateWithShape = true }) ) { Name = "Office" }), new Drawing.ObjectDefaults(), new Drawing.ExtraColorSchemeList() ) { Name = "Office-téma" }; return element; } #endregion #region InsertDataIntoCells Dictionary _ColumnMaxCharacterCount = new Dictionary(); // ebben szamolom ki, hogy hany karakter lehet oszloponkent maximum private void InsertDataIntoCells(Worksheet sheet, SharedStringTablePart SharedstringPart, ExcelExportItem exportItems) { foreach (var colIndex in exportItems.ExcelColumns.Keys) { // header string headercellreference = OpenXmlExcelUtils.GetExcelColumnName(colIndex) + (1); // uj cella (TD) Cell headernewCell = OpenXmlExcelUtils.CreateSpreadsheetCellIfNotExist(sheet, headercellreference); string headercellText = exportItems.ExcelColumns[colIndex].ColumnTitle; // szoveg objektum letrehozasa // eloszor letrehozom a ShareStringTable-ben vagy megkeresem van-e mar int headersharedindex = OpenXmlExcelUtils.InsertSharedStringItem(headercellText, SharedstringPart); // majd index alapjan referenciat adok ra headernewCell.CellValue = new CellValue(headersharedindex.ToString()); // ez bizony egy sharedstring tipus headernewCell.DataType = new EnumValue(CellValues.SharedString); if (exportItems.ExcelColumns[colIndex].FormatId.HasValue) headernewCell.StyleIndex = exportItems.ExcelColumns[colIndex].FormatId.Value; // beallitom a cella stilusat else SetStyleIndex(headernewCell, true); // maximalis karakter szamolas if (!_ColumnMaxCharacterCount.Keys.Contains(colIndex + 1) || (_ColumnMaxCharacterCount.Keys.Contains(colIndex + 1) && _ColumnMaxCharacterCount[colIndex + 1] < headercellText.Length)) { _ColumnMaxCharacterCount[colIndex + 1] = headercellText.Length; } } // vegigmegyek az osszes soron for (var rowIndex = 2; rowIndex < exportItems.RowsCount + 2; rowIndex++) { // ujra hasznositom az elobbi foreach colindex-et foreach (var colIndex in exportItems.ExcelColumns.Keys) { var rowData = exportItems.ExcelColumns[colIndex].ExcelRows.FirstOrDefault(x => x.Key == rowIndex); ExcelExportItem.ExcelExportCell cell; if (rowData.Equals(default(KeyValuePair))) continue; cell = rowData.Value; if (cell.IsEmpty) continue; string cellReference = OpenXmlExcelUtils.GetExcelColumnName(colIndex) + (rowIndex); // uj cella Cell newCell = OpenXmlExcelUtils.CreateSpreadsheetCellIfNotExist(sheet, cellReference); if (cell.FormatID.HasValue) { newCell.StyleIndex = cell.FormatID.Value; } else { // beallitom a cella stilusat SetStyleIndex(newCell, false); } if (!string.IsNullOrWhiteSpace(cell.Formula)) { var cellFormula = new CellFormula(cell.Formula); newCell.Append(cellFormula); } else { string cellText = cell.Value != null ? cell.Value.ToString() : string.Empty; // szoveg objektum letrehozasa // eloszor letrehozom a ShareStringTable-ben vagy megkeresem van-e mar int sharedindex = 0; if (cell.ValueType != CellValues.Number) sharedindex = OpenXmlExcelUtils.InsertSharedStringItem(cellText, SharedstringPart); // majd index alapjan referenciat adok ra newCell.CellValue = new CellValue(cell.ValueType == CellValues.Number ? cellText.Replace(',', '.') : sharedindex.ToString()); newCell.DataType = new EnumValue(cell.ValueType); // maximalis karakter szamolas if (!_ColumnMaxCharacterCount.Keys.Contains(colIndex + 1) || (_ColumnMaxCharacterCount.Keys.Contains(colIndex + 1) && _ColumnMaxCharacterCount[colIndex + 1] < cellText.Length)) { _ColumnMaxCharacterCount[colIndex + 1] = cellText.Length; } } } } // validációs listák felvétele List hiddenColumns = new List(); foreach (var colIndex in exportItems.ExcelColumns.Keys) { var column = exportItems.ExcelColumns[colIndex]; if (column.ValidationList != null) { // ha van hozzárendelve, felvesszük hiddenColumns.AddRange(CreateDataValidationList(sheet, SharedstringPart, column.ValidationList, OpenXmlExcelUtils.GetExcelColumnName(colIndex))); } if (column.isHidden) hiddenColumns.Add(colIndex); } // oszlopok beallitasa (szelesseg) foreach (int colIndex in _ColumnMaxCharacterCount.Keys) { var column = exportItems.ExcelColumns[colIndex - 1]; var maxCharacterCount = column.MaxCharacterCount.HasValue ? column.MaxCharacterCount.Value : _ColumnMaxCharacterCount[colIndex]; OpenXmlExcelUtils.SetColumns(sheet, colIndex, maxCharacterCount, false); } // hidden columns foreach (int colindex in hiddenColumns) { OpenXmlExcelUtils.SetColumns(sheet, colindex, 0, true); } sheet.Save(); _ColumnMaxCharacterCount = new Dictionary(); } private void InsertDataIntoCellsList(Worksheet sheet, SharedStringTablePart SharedstringPart, List Items) { int headerRowIndex = 1; //több tábla esetén adott tábla fejléc sorának indexe int dataRowIndex = 0; //több tábla esetén adott tábla adatsorának indexe foreach (var exportItems in Items) { foreach (var colIndex in exportItems.ExcelColumns.Keys) { // header string headercellreference = OpenXmlExcelUtils.GetExcelColumnName(colIndex) + headerRowIndex; // uj cella (TD) Cell headernewCell = OpenXmlExcelUtils.CreateSpreadsheetCellIfNotExist(sheet, headercellreference); string headercellText = exportItems.ExcelColumns[colIndex].ColumnTitle; // szoveg objektum letrehozasa // eloszor letrehozom a ShareStringTable-ben vagy megkeresem van-e mar int headersharedindex = OpenXmlExcelUtils.InsertSharedStringItem(headercellText, SharedstringPart); // majd index alapjan referenciat adok ra headernewCell.CellValue = new CellValue(headersharedindex.ToString()); // ez bizony egy sharedstring tipus headernewCell.DataType = new EnumValue(CellValues.SharedString); if (exportItems.ExcelColumns[colIndex].FormatId.HasValue) headernewCell.StyleIndex = exportItems.ExcelColumns[colIndex].FormatId.Value; // beallitom a cella stilusat else SetStyleIndex(headernewCell, true); // maximalis karakter szamolas if (!_ColumnMaxCharacterCount.Keys.Contains(colIndex + 1) || (_ColumnMaxCharacterCount.Keys.Contains(colIndex + 1) && _ColumnMaxCharacterCount[colIndex + 1] < headercellText.Length)) { _ColumnMaxCharacterCount[colIndex + 1] = headercellText.Length; } } // vegigmegyek az osszes soron for (var rowIndex = 2; rowIndex < exportItems.RowsCount + 2; rowIndex++) { // ujra hasznositom az elobbi foreach colindex-et foreach (var colIndex in exportItems.ExcelColumns.Keys) { var rowData = exportItems.ExcelColumns[colIndex].ExcelRows.FirstOrDefault(x => x.Key == rowIndex); ExcelExportItem.ExcelExportCell cell; if (rowData.Equals(default(KeyValuePair))) continue; cell = rowData.Value; if (cell.IsEmpty) continue; string cellReference = OpenXmlExcelUtils.GetExcelColumnName(colIndex) + (rowIndex + dataRowIndex); // uj cella Cell newCell = OpenXmlExcelUtils.CreateSpreadsheetCellIfNotExist(sheet, cellReference); if (cell.FormatID.HasValue) { newCell.StyleIndex = cell.FormatID.Value; } else { // beallitom a cella stilusat SetStyleIndex(newCell, false); } if (!string.IsNullOrWhiteSpace(cell.Formula)) { var cellFormula = new CellFormula(cell.Formula); newCell.Append(cellFormula); } else { string cellText = cell.Value != null ? cell.Value.ToString() : string.Empty; // szoveg objektum letrehozasa // eloszor letrehozom a ShareStringTable-ben vagy megkeresem van-e mar int sharedindex = 0; if (cell.ValueType != CellValues.Number) sharedindex = OpenXmlExcelUtils.InsertSharedStringItem(cellText, SharedstringPart); // majd index alapjan referenciat adok ra newCell.CellValue = new CellValue(cell.ValueType == CellValues.Number ? cellText.Replace(',', '.') : sharedindex.ToString()); newCell.DataType = new EnumValue(cell.ValueType); // maximalis karakter szamolas if (!_ColumnMaxCharacterCount.Keys.Contains(colIndex + 1) || (_ColumnMaxCharacterCount.Keys.Contains(colIndex + 1) && _ColumnMaxCharacterCount[colIndex + 1] < cellText.Length)) { _ColumnMaxCharacterCount[colIndex + 1] = cellText.Length; } } } } // validációs listák felvétele List hiddenColumns = new List(); foreach (var colIndex in exportItems.ExcelColumns.Keys) { var column = exportItems.ExcelColumns[colIndex]; if (column.ValidationList != null || column.isHidden) { // ha van hozzárendelve, felvesszük hiddenColumns.AddRange(CreateDataValidationList(sheet, SharedstringPart, column.ValidationList, OpenXmlExcelUtils.GetExcelColumnName(colIndex))); } } // oszlopok beallitasa (szelesseg) foreach (int colIndex in _ColumnMaxCharacterCount.Keys) { var column = exportItems.ExcelColumns[colIndex - 1]; var maxCharacterCount = column.MaxCharacterCount.HasValue ? column.MaxCharacterCount.Value : _ColumnMaxCharacterCount[colIndex]; OpenXmlExcelUtils.SetColumns(sheet, colIndex, maxCharacterCount, false); } // hidden columns foreach (int colindex in hiddenColumns) { OpenXmlExcelUtils.SetColumns(sheet, colindex, 0, true); } headerRowIndex += exportItems.RowsCount + 2; dataRowIndex += exportItems.RowsCount + 2; sheet.Save(); _ColumnMaxCharacterCount = new Dictionary(); } } #endregion #region Design private void SetStyleIndex(Cell cell, bool IsHeader) { cell.StyleIndex = IsHeader ? (UInt32Value)1U //header : (UInt32Value)0U; } #endregion #region Validációs lista létrehozása /// /// Letrehoz egy validacios listat az atadott lista alapjan. /// /// Aktiv sheet. /// Aktiv sharedStringPart /// Azon oszlop neve, amelyekre ra lesz huzva a lista. pl: A , B, C /// List CreateDataValidationList(Worksheet sheet, SharedStringTablePart sharedStringPart, ExcelExportItem.ExcelColumnValidationList validationList, string referenceColumn) { int rowIndex = 1; // lista inicializalasa if (string.IsNullOrWhiteSpace(validationList.ValueColumnReference)) validationList.ValueColumnReference = string.Format("{0}A", referenceColumn); // lista értékeinek mentése (pl. AA, BA, CA, stb.) if (string.IsNullOrWhiteSpace(validationList.KeyColumnReference)) validationList.KeyColumnReference = string.Format("{0}B", referenceColumn); // lista kulcsainak mentése (pl. AB, BB, CB, stb.) string validationformula = string.Format("${0}${1}:${0}${2}", validationList.ValueColumnReference, rowIndex, (rowIndex + validationList.ValidationList.Count - 1)); foreach (var item in validationList.ValidationList.Keys) { // értékek (szöveg) mentése string cellreference = validationList.ValueColumnReference + rowIndex; Cell cellKey = OpenXmlExcelUtils.CreateSpreadsheetCellIfNotExist(sheet, cellreference); var cellText = validationList.ValidationList[item].ToString(); var sharedstringindex = OpenXmlExcelUtils.InsertSharedStringItem(validationList.ValidationList[item].ToString(), sharedStringPart); cellKey.DataType = new EnumValue(CellValues.SharedString); cellKey.CellValue = new CellValue(sharedstringindex.ToString()); cellKey.StyleIndex = (UInt32Value)2U; // kulcsok (ID) mentése cellreference = validationList.KeyColumnReference + rowIndex; cellKey = OpenXmlExcelUtils.CreateSpreadsheetCellIfNotExist(sheet, cellreference); sharedstringindex = OpenXmlExcelUtils.InsertSharedStringItem(item, sharedStringPart); cellKey.DataType = new EnumValue(CellValues.SharedString); cellKey.CellValue = new CellValue(sharedstringindex.ToString()); cellKey.StyleIndex = (UInt32Value)2U; // maximalis karakter szamolas var valueColumnIndex = OpenXmlExcelUtils.GetColumnIndex(referenceColumn); if (!_ColumnMaxCharacterCount.Keys.Contains(valueColumnIndex) || (_ColumnMaxCharacterCount.Keys.Contains(valueColumnIndex) && _ColumnMaxCharacterCount[valueColumnIndex] < cellText.Length)) { _ColumnMaxCharacterCount[valueColumnIndex] = cellText.Length; } rowIndex++; } sheet.Save(); // validacios lista hozzarendelese // ha nem letezik hozza validacios lista tulajdonsag DataValidations validlists; if (!sheet.Descendants().Any()) { validlists = new DataValidations(); sheet.Append(validlists); } else { validlists = sheet.Descendants().First(); } // leellenorzom, hogy van-e mar felveve egyaltalan erre a listaelemekre validacios lista var datavalidationlista = validlists.Descendants().Where(a => a.Formula1.Text == validationformula); bool vanmarilyenlista = datavalidationlista.Any(); referenceColumn = string.Format("{0}1:{0}1048576", referenceColumn); if (vanmarilyenlista) { // ha van mar ilyen lista, akkor csak a formulahoz adom hozza a cellakat DataValidation validlist = datavalidationlista.First(); validlist.SequenceOfReferences.InnerText += " " + referenceColumn.Trim(); } else { // ha meg nincs ilyen lista, felveszek egy ujat // lista felvetele DataValidation validlist = new DataValidation( new Formula1(validationformula)) { AllowBlank = true, Type = DataValidationValues.List, SequenceOfReferences = new ListValue() { InnerText = referenceColumn.Trim() }, ShowErrorMessage = true, ShowInputMessage = true }; validlists.Append(validlist); } return new List { OpenXmlExcelUtils.GetColumnIndex(validationList.ValueColumnReference), OpenXmlExcelUtils.GetColumnIndex(validationList.KeyColumnReference) }; } #endregion } #region Excel export item public class ExcelExportItem { public ExcelExportItem() { ExcelColumns = new SortedDictionary(); } public string SheetCaption { get; set; } public SortedDictionary ExcelColumns { get; set; } public int RowsCount { get { return ExcelColumns.Max(col => col.Value.ExcelRows.Count); } } public ExcelExportColumn AddColumn(int columnIndex, string columnTitle, uint? formatId = new uint?(), ExcelColumnValidationList columnValidationList = null, bool isHidden = false) { if (!ExcelColumns.ContainsKey(columnIndex)) { ExcelColumns.Add(columnIndex, new ExcelExportColumn(columnTitle, columnValidationList) { FormatId = formatId, isHidden = isHidden }); } return GetColumn(columnIndex); } public ExcelExportColumn AddColumn(int columnIndex, string columnTitle, string formula, ExcelColumnValidationList columnValidationList = null) { if (!ExcelColumns.ContainsKey(columnIndex)) { ExcelColumns.Add(columnIndex, new ExcelExportColumn(columnTitle, columnValidationList) { Formula = formula }); } return GetColumn(columnIndex); } public void AddCell(int rowIndex, int columnIndex, object value, string formula = null, CellValues valuetype = CellValues.SharedString, uint? formatID = new uint?()) { var col = GetColumn(columnIndex); if (col != null) { if (!col.ExcelRows.ContainsKey(rowIndex)) { col.ExcelRows.Add(rowIndex, new ExcelExportCell() { Value = value, Formula = formula, ValueType = valuetype, FormatID = formatID }); } } else { throw new Exception("A " + columnIndex + " oszlop nem létezik!"); } } public ExcelExportColumn GetColumn(int colIndex) { return ExcelColumns[colIndex]; } public ExcelExportCell GetCell(int colIndex, int rowIndex) { var result = GetColumn(colIndex); return result != null && result.ExcelRows.TryGetValue(rowIndex, out ExcelExportCell value) ? value : null; } public class ExcelExportColumn { public ExcelExportColumn(string columnTitle, ExcelColumnValidationList validationList = null) { ExcelRows = new SortedDictionary(); ValidationList = validationList; ColumnTitle = columnTitle; } public uint? FormatId { get; set; } public string Formula { get; set; } public string ColumnTitle { get; set; } public int? MaxCharacterCount { get; set; } public bool isHidden { get; set; } public ExcelColumnValidationList ValidationList { get; set; } public SortedDictionary ExcelRows { get; set; } } public class ExcelExportCell { public ExcelExportCell() { } public uint? FormatID { get; set; } public CellValues ValueType { get; set; } public object Value { get; set; } public string Formula { get; set; } public bool IsEmpty { get { return Value == null && string.IsNullOrWhiteSpace(Formula); } } } public class ExcelColumnValidationList { public string KeyColumnReference { get; set; } public string ValueColumnReference { get; set; } SortedDictionary validationList = new SortedDictionary(); public SortedDictionary ValidationList { get { return validationList; } } public void AddValidationItem(string itemKey, object value) { ValidationList.Add(itemKey, value); } } } #endregion }