diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 611cd20..c11e341 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -48,6 +48,7 @@ + @@ -57,6 +58,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLPrintOptions.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLPrintOptions.cs new file mode 100644 index 0000000..370b455 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLPrintOptions.cs @@ -0,0 +1,16 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public enum XLPageOrientation { Default, Portrait, Landscape } + public interface IXLPrintOptions + { + IXLRange PrintArea { get; set; } + XLPageOrientation PageOrientation { get; set; } + Int32 PagesWide { get; set; } + Int32 PagesTall { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs index ee30786..6c4b78d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs @@ -19,6 +19,7 @@ Int32 RowNumber { get; } Int32 ColumnNumber { get; } String ColumnLetter { get; } + IXLRange PrintArea { get; set; } } public static class IXLRangeMethods @@ -83,26 +84,26 @@ } public static IXLRange Range(this IXLRange range, IXLAddress firstCellAddress, IXLAddress lastCellAddress) { - + var newFirstCellAddress = (XLAddress)firstCellAddress + (XLAddress)range.FirstCellAddress - 1; + var newLastCellAddress = (XLAddress)lastCellAddress + (XLAddress)range.FirstCellAddress - 1; var xlRangeParameters = new XLRangeParameters() { - FirstCellAddress = (XLAddress)firstCellAddress + (XLAddress)range.FirstCellAddress - 1, - LastCellAddress = (XLAddress)lastCellAddress + (XLAddress)range.FirstCellAddress - 1, CellsCollection = range.CellsCollection, MergedCells = range.MergedCells, - DefaultStyle = range.Style + DefaultStyle = range.Style, + PrintArea = range.PrintArea }; if ( - xlRangeParameters.FirstCellAddress.Row < range.FirstCellAddress.Row - || xlRangeParameters.FirstCellAddress.Row > range.LastCellAddress.Row - || xlRangeParameters.LastCellAddress.Row > range.LastCellAddress.Row - || xlRangeParameters.FirstCellAddress.Column < range.FirstCellAddress.Column - || xlRangeParameters.FirstCellAddress.Column > range.LastCellAddress.Column - || xlRangeParameters.LastCellAddress.Column > range.LastCellAddress.Column + newFirstCellAddress.Row < range.FirstCellAddress.Row + || newFirstCellAddress.Row > range.LastCellAddress.Row + || newLastCellAddress.Row > range.LastCellAddress.Row + || newFirstCellAddress.Column < range.FirstCellAddress.Column + || newFirstCellAddress.Column > range.LastCellAddress.Column + || newLastCellAddress.Column > range.LastCellAddress.Column ) throw new ArgumentOutOfRangeException(); - return new XLRange(xlRangeParameters); + return new XLRange(newFirstCellAddress, newLastCellAddress, xlRangeParameters); } public static IXLRange Range(this IXLRange range, IXLCell firstCell, IXLCell lastCell) { @@ -256,7 +257,10 @@ return retVal; } - + public static void SetAsPrintArea(this IXLRange range) + { + range.PrintArea = range; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 5bccab0..a54b5cc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -15,13 +15,6 @@ String Name { get; set; } List Columns(); - void SetPrintArea(IXLRange range); - void SetPrintArea(String rangeAddress); - void SetPrintArea(IXLCell firstCell, IXLCell lastCell); - void SetPrintArea(String firstCellAddress, String lastCellAddress); - void SetPrintArea(IXLAddress firstCellAddress, IXLAddress lastCellAddress); - void SetPrintArea(Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn); - - + IXLPrintOptions PrintOptions { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs index 32c2c8e..5a7eac6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLCell.cs @@ -6,7 +6,7 @@ namespace ClosedXML.Excel { - public class XLCell: IXLCell + public class XLCell : IXLCell { public XLCell(IXLAddress address, IXLStyle defaultStyle) { @@ -14,7 +14,7 @@ Style = defaultStyle; if (Style == null) Style = XLWorkbook.DefaultStyle; } - + public IXLAddress Address { get; private set; } private Boolean initialized = false; @@ -80,7 +80,7 @@ public IEnumerable Styles { - get + get { UpdatingStyle = true; yield return style; @@ -93,7 +93,7 @@ #endregion private XLCellValues dataType; - public XLCellValues DataType + public XLCellValues DataType { get { @@ -147,7 +147,7 @@ cellValue = DateTime.FromOADate(Double.Parse(cellValue)).ToString(Style.NumberFormat.Format); } } - + dataType = value; } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs index 2076edb..7ceadcd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs @@ -8,15 +8,16 @@ { public class XLColumn: IXLColumn { - public XLColumn(Int32 column, Dictionary cellsCollection, IXLStyle defaultStyle) + public XLColumn(Int32 column, XLRangeParameters xlRangeParameters) { FirstCellAddress = new XLAddress(1, column); LastCellAddress = new XLAddress(XLWorksheet.MaxNumberOfRows, column); RowNumber = 1; ColumnNumber = column; ColumnLetter = XLAddress.GetColumnLetterFromNumber(column); - CellsCollection = cellsCollection; - this.style = new XLStyle(this, defaultStyle); + CellsCollection = xlRangeParameters.CellsCollection; + PrintArea = xlRangeParameters.PrintArea; + this.style = new XLStyle(this, xlRangeParameters.DefaultStyle); this.Width = XLWorkbook.DefaultColumnWidth; } @@ -86,5 +87,7 @@ } #endregion + + public IXLRange PrintArea { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLPrintOptions.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLPrintOptions.cs new file mode 100644 index 0000000..d16dd17 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLPrintOptions.cs @@ -0,0 +1,19 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public class XLPrintOptions : IXLPrintOptions + { + public XLPrintOptions() + { + PageOrientation = XLPageOrientation.Default; + } + public IXLRange PrintArea { get; set; } + public XLPageOrientation PageOrientation { get; set; } + public Int32 PagesWide { get; set; } + public Int32 PagesTall { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRange.cs index a26c65a..c0696d3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRange.cs @@ -10,15 +10,16 @@ { private IXLStyle defaultStyle; - public XLRange(XLRangeParameters xlRangeParameters) + public XLRange(IXLAddress firstCellAddress, IXLAddress lastCellAddress, XLRangeParameters xlRangeParameters) { - FirstCellAddress = xlRangeParameters.FirstCellAddress; - LastCellAddress = xlRangeParameters.LastCellAddress; + FirstCellAddress = firstCellAddress; + LastCellAddress = lastCellAddress; CellsCollection = xlRangeParameters.CellsCollection; MergedCells = xlRangeParameters.MergedCells; RowNumber = FirstCellAddress.Row; ColumnNumber = FirstCellAddress.Column; ColumnLetter = FirstCellAddress.ColumnLetter; + PrintArea = xlRangeParameters.PrintArea; this.defaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); } @@ -53,7 +54,8 @@ public Int32 RowNumber { get; private set; } public Int32 ColumnNumber { get; private set; } public String ColumnLetter { get; private set; } - + + public IXLRange PrintArea { get; set; } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRangeParameters.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRangeParameters.cs index 0bc8e4a..ee29ae9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRangeParameters.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRangeParameters.cs @@ -20,11 +20,10 @@ #region Properties // Public - public IXLAddress FirstCellAddress { get; set; } - public IXLAddress LastCellAddress { get; set; } public Dictionary CellsCollection { get; set; } public List MergedCells { get; set; } public IXLStyle DefaultStyle { get; set; } + public IXLRange PrintArea { get; set; } // Private diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs index 8bf6982..1dd52ea 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs @@ -8,15 +8,16 @@ { public class XLRow: IXLRow { - public XLRow(Int32 row, Dictionary cellsCollection, IXLStyle defaultStyle) + public XLRow(Int32 row, XLRangeParameters xlRangeParameters) { FirstCellAddress = new XLAddress(row, 1); LastCellAddress = new XLAddress(row, XLWorksheet.MaxNumberOfColumns); RowNumber = row; ColumnNumber = 1; ColumnLetter = "A"; - CellsCollection = cellsCollection; - this.style = new XLStyle(this, defaultStyle); + CellsCollection = xlRangeParameters.CellsCollection; + PrintArea = xlRangeParameters.PrintArea; + this.style = new XLStyle(this, xlRangeParameters.DefaultStyle); this.Height = XLWorkbook.DefaultRowHeight; } @@ -31,6 +32,7 @@ public List MergedCells { get; private set; } public IXLAddress FirstCellAddress { get; private set; } public IXLAddress LastCellAddress { get; private set; } + public IXLRange PrintArea { get; set; } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 0e69a5c..0c94552 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -19,6 +19,19 @@ { public partial class XLWorkbook { + private enum RelType { General, Workbook, Worksheet } + private class RelId + { + private static Dictionary relIds = new Dictionary(); + public static Int32 GetNext(RelType relType) + { + if (!relIds.ContainsKey(relType)) + relIds.Add(relType, -1); + var relId = relIds[relType]; + relIds[relType] = ++relId; + return relId; + } + } private Dictionary sharedStrings = new Dictionary(); private struct FontInfo { public UInt32 FontId; public IXLFont Font; }; @@ -64,6 +77,17 @@ } } + private OrientationValues GetOrientationValue(XLPageOrientation xlPageOrientation) + { + switch (xlPageOrientation) + { + case XLPageOrientation.Default: return OrientationValues.Default; + case XLPageOrientation.Landscape: return OrientationValues.Landscape; + case XLPageOrientation.Portrait: return OrientationValues.Portrait; + default: throw new NotImplementedException(); + } + } + private VerticalAlignmentRunValues GetVerticalAlignmentRunValue(XLFontVerticalTextAlignmentValues xlFontVerticalTextAlignmentValue) { switch (xlFontVerticalTextAlignmentValue) @@ -158,6 +182,7 @@ { using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) { + RelId.GetNext(RelType.Worksheet); CreateParts(package); } } @@ -165,16 +190,17 @@ // Adds child parts and generates content of the specified part. private void CreateParts(SpreadsheetDocument document) { - ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart("rId3"); + Int32 startId = Worksheets.Count(); + ExtendedFilePropertiesPart extendedFilePropertiesPart1 = document.AddNewPart("rId" + (startId)); GenerateExtendedFilePropertiesPartContent(extendedFilePropertiesPart1); WorkbookPart workbookPart = document.AddWorkbookPart(); GenerateWorkbookPartContent(workbookPart); - SharedStringTablePart sharedStringTablePart = workbookPart.AddNewPart("rId6"); + SharedStringTablePart sharedStringTablePart = workbookPart.AddNewPart("rId" + (startId + 3)); GenerateSharedStringTablePartContent(sharedStringTablePart); - WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart("rId5"); + WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart("rId" + (startId + 2)); GenerateWorkbookStylesPartContent(workbookStylesPart); UInt32 sheetId = 0; @@ -185,7 +211,7 @@ GenerateWorksheetPartContent(worksheetPart, worksheet); } - ThemePart themePart1 = workbookPart.AddNewPart("rId4"); + ThemePart themePart1 = workbookPart.AddNewPart("rId" + (startId + 1)); GenerateThemePartContent(themePart1); //SetPackageProperties(document); @@ -204,7 +230,7 @@ Ap.HeadingPairs headingPairs1 = new Ap.HeadingPairs(); - Vt.VTVector vTVector1 = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Variant, Size = (UInt32Value)2U }; + Vt.VTVector vTVector1 = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Variant, Size = (UInt32Value)4U }; Vt.Variant variant1 = new Vt.Variant(); Vt.VTLPSTR vTLPSTR1 = new Vt.VTLPSTR(); @@ -214,24 +240,42 @@ Vt.Variant variant2 = new Vt.Variant(); Vt.VTInt32 vTInt321 = new Vt.VTInt32(); - vTInt321.Text = "3"; + vTInt321.Text = Worksheets.Count().ToString(); variant2.Append(vTInt321); + Vt.Variant variant3 = new Vt.Variant(); + Vt.VTLPSTR vTLPSTR2 = new Vt.VTLPSTR(); + vTLPSTR2.Text = "Named Ranges"; + + variant3.Append(vTLPSTR2); + + Vt.Variant variant4 = new Vt.Variant(); + Vt.VTInt32 vTInt322 = new Vt.VTInt32(); + vTInt322.Text = Worksheets.Count().ToString(); + + variant4.Append(vTInt322); + vTVector1.Append(variant1); vTVector1.Append(variant2); + vTVector1.Append(variant3); + vTVector1.Append(variant4); headingPairs1.Append(vTVector1); Ap.TitlesOfParts titlesOfParts1 = new Ap.TitlesOfParts(); UInt32 sheetCount = (UInt32)Worksheets.Count(); - Vt.VTVector vTVector2 = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Lpstr, Size = (UInt32Value)sheetCount }; + Vt.VTVector vTVector2 = new Vt.VTVector() { BaseType = Vt.VectorBaseValues.Lpstr, Size = (UInt32Value)sheetCount * 2 }; foreach (var worksheet in Worksheets) { - Vt.VTLPSTR vTLPSTR2 = new Vt.VTLPSTR(); - vTLPSTR2.Text = worksheet.Name; - vTVector2.Append(vTLPSTR2); + Vt.VTLPSTR vTLPSTR3 = new Vt.VTLPSTR(); + vTLPSTR3.Text = worksheet.Name; + vTVector2.Append(vTLPSTR3); + + Vt.VTLPSTR vTLPSTR4 = new Vt.VTLPSTR(); + vTLPSTR4.Text = worksheet.Name + "!Print_Area"; + vTVector2.Append(vTLPSTR4); } titlesOfParts1.Append(vTVector2); @@ -274,11 +318,27 @@ UInt32 sheetId = 0; Sheets sheets = new Sheets(); + DefinedNames definedNames = new DefinedNames(); foreach (var worksheet in Worksheets) { sheetId++; Sheet sheet = new Sheet() { Name = worksheet.Name, SheetId = (UInt32Value)sheetId, Id = "rId" + sheetId.ToString() }; sheets.Append(sheet); + + if (worksheet.PrintOptions.PrintArea == null) + { + var minCell = worksheet.CellsCollection.Min(c => c.Key); + var maxCell = worksheet.CellsCollection.Max(c => c.Key); + if (minCell != null && maxCell != null) + { + worksheet.PrintOptions.PrintArea = worksheet.Range(minCell, maxCell); + DefinedName definedName = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)sheetId - 1 }; + definedName.Text = "'" + worksheet.Name + "'!" + + worksheet.PrintOptions.PrintArea.FirstCellAddress.ToString() + + ":" + worksheet.PrintOptions.PrintArea.LastCellAddress.ToString(); + definedNames.Append(definedName); + } + } } CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U, CalculationMode = CalculateModeValues.Manual }; @@ -287,6 +347,7 @@ workbook1.Append(workbookProperties1); workbook1.Append(bookViews1); workbook1.Append(sheets); + if (definedNames.Count() > 0) workbook1.Append(definedNames); workbook1.Append(calculationProperties1); workbookPart.Workbook = workbook1; @@ -581,6 +642,11 @@ Worksheet worksheet = new Worksheet(); worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); SheetProperties sheetProperties = new SheetProperties() { CodeName = xlWorksheet.Name.RemoveSpecialCharacters() }; + if (xlWorksheet.PrintOptions.PagesTall >= 0 || xlWorksheet.PrintOptions.PagesWide >= 0) + { + PageSetupProperties pageSetupProperties = new PageSetupProperties() { FitToPage = true }; + sheetProperties.Append(pageSetupProperties); + } UInt32 maxColumn = 0; UInt32 maxRow = 0; @@ -626,10 +692,10 @@ Min = (UInt32Value)(UInt32)xlColumn.FirstCellAddress.Column, Max = (UInt32Value)(UInt32)xlColumn.FirstCellAddress.Column, Style = sharedStyles[xlColumn.Style.ToString()].StyleId, - Width = xlColumn.Width, - CustomWidth = true + Width = xlColumn.Width, + CustomWidth = true }; - + columns.Append(column); } } @@ -714,7 +780,14 @@ } PageMargins pageMargins = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D }; - Drawing drawing1 = new Drawing() { Id = "rId1" }; + //Drawing drawing1 = new Drawing() { Id = "rId1" }; + + PageSetup pageSetup1 = new PageSetup() { Orientation = GetOrientationValue(xlWorksheet.PrintOptions.PageOrientation), HorizontalDpi = (UInt32Value)300U, VerticalDpi = (UInt32Value)300U, Id = "rId" + RelId.GetNext(RelType.Worksheet) }; + if (xlWorksheet.PrintOptions.PagesWide >= 0) + pageSetup1.FitToWidth = (UInt32Value)(UInt32)xlWorksheet.PrintOptions.PagesWide; + if (xlWorksheet.PrintOptions.PagesTall >= 0) + pageSetup1.FitToHeight = (UInt32Value)(UInt32)xlWorksheet.PrintOptions.PagesTall; + worksheet.Append(sheetProperties); worksheet.Append(sheetDimension); worksheet.Append(sheetViews); @@ -723,6 +796,7 @@ worksheet.Append(sheetData); if (mergeCells != null) worksheet.Append(mergeCells); worksheet.Append(pageMargins); + worksheet.Append(pageSetup1); //worksheet.Append(drawing1); worksheetPart.Worksheet = worksheet; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 6f53b74..c160f1b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -20,15 +20,18 @@ Dictionary columnsCollection = new Dictionary(); public XLWorksheet(String sheetName) - { + { Style = XLWorkbook.DefaultStyle; MergedCells = new List(); RowNumber = 1; ColumnNumber = 1; ColumnLetter = "A"; + PrintOptions = new XLPrintOptions(); this.Name = sheetName; } + public IXLPrintOptions PrintOptions { get; private set; } + #region IXLRange Members public List MergedCells { get; private set; } public Dictionary CellsCollection @@ -76,6 +79,8 @@ return retVal; } + + public IXLRange PrintArea { get; set; } #endregion @@ -120,7 +125,14 @@ } else { - xlRow = new XLRow(row, cellsCollection, Style); + var xlRangeParameters = new XLRangeParameters() + { + CellsCollection = cellsCollection, + MergedCells = this.MergedCells, + DefaultStyle = Style, + PrintArea = this.PrintArea + }; + xlRow = new XLRow(row, xlRangeParameters); rowsCollection.Add(row, xlRow); } @@ -135,7 +147,14 @@ } else { - xlColumn = new XLColumn(column, cellsCollection, Style); + var xlRangeParameters = new XLRangeParameters() + { + CellsCollection = cellsCollection, + MergedCells = this.MergedCells, + DefaultStyle = Style, + PrintArea = this.PrintArea + }; + xlColumn = new XLColumn(column, xlRangeParameters); columnsCollection.Add(column, xlColumn); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 1e6e05b..a50c2a7 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -13,8 +13,25 @@ static void Main(string[] args) { var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("New Sheet"); - + var ws1 = wb.Worksheets.Add("Sheet1"); + ws1.PrintOptions.PrintArea = ws1.Range("A1:B2"); + ws1.PrintOptions.PageOrientation = XLPageOrientation.Portrait; + var ws2 = wb.Worksheets.Add("Sheet2"); + ws2.PrintOptions.PrintArea = ws2.Range("B2:E5"); + ws2.PrintOptions.PageOrientation = XLPageOrientation.Landscape; + ws2.PrintOptions.PagesWide = 1; + ws2.PrintOptions.PagesTall = 2; + foreach (var wsNum in Enumerable.Range(1, 5)) + { + var ws = wb.Worksheets.Add("New Sheet " + wsNum.ToString()); + foreach (var ro in Enumerable.Range(1, 5)) + { + foreach (var co in Enumerable.Range(1, 5)) + { + ws.Cell(ro, co).Value = "(" + ro + "," + co + ")"; + } + } + } //foreach (var c in ws.Range("B2:C3").Columns())