/// <summary>
/// Set orientation of the worksheet, i.e. Portrait, Landscape or Default
/// </summary>
/// <param name="worksheetPart"></param>
/// <param name="oval"></param>
public void SetOrientation(WorksheetPart worksheetPart, OrientationValues oval)
{
PageSetup pageSetup = new PageSetup()
{
Orientation = oval
};
worksheetPart.Worksheet.AppendChild(pageSetup);
worksheetPart.Worksheet.Save();
workbookPart.Workbook.Save();
}
/// <summary>
/// Sets the page setup orientation of a worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="orientation"></param>
public static void SetOrientation(this Worksheet worksheet, OrientationValues orientation)
{
PageSetup pageSetup = worksheet.Descendants <PageSetup>().FirstOrDefault();
if (pageSetup == null)
{
worksheet.AppendChild(
new PageSetup
{
Orientation = orientation
});
}
}
public static void PageSetupUpdate(WorksheetPart worksheetPart, OrientationValues landscapeOrPortrait,
DoubleValue marginLeft, DoubleValue marginRight, DoubleValue marginTop, DoubleValue marginBottom, DoubleValue marginHeader, DoubleValue marginFooter,
Boolean isFitToPage, UInt32Value FitToHeight, UInt32Value FitToWidth, UInt32Value pageSize, string headerLeft, string headerCenter, string headerRight, string footerLeft, string footerRight)
{
Worksheet ws = worksheetPart.Worksheet;
//page setup them moi pagesetup properties
SheetProperties sp = new SheetProperties(new PageSetupProperties());
ws.SheetProperties = sp;
PrintOptions printOp = new PrintOptions();
printOp.HorizontalCentered = true;
ws.AppendChild(printOp);
PageMargins pageMargins = new PageMargins();
pageMargins.Left = marginLeft;
pageMargins.Right = marginRight;
pageMargins.Top = marginTop;
pageMargins.Bottom = marginBottom;
pageMargins.Header = marginHeader;
pageMargins.Footer = marginFooter;
ws.AppendChild(pageMargins);
// Set the FitToPage property to true
ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(isFitToPage);
DocumentFormat.OpenXml.Spreadsheet.PageSetup pgOr = new DocumentFormat.OpenXml.Spreadsheet.PageSetup();
pgOr.Orientation = landscapeOrPortrait;
pgOr.PaperSize = pageSize;
pgOr.FitToHeight = FitToHeight;
pgOr.FitToWidth = FitToWidth;
ws.AppendChild(pgOr);
HeaderFooter headerFooter1 = new HeaderFooter();
OddHeader oddHeader1 = new OddHeader();
oddHeader1.Text = "&L&\"Times New Roman,Regular\"" + headerLeft + "&C&\"Times New Roman,Regular\"" + headerCenter + "&R&\"Times New Roman,Regular\"" + headerRight;
OddFooter oddFooter1 = new OddFooter();
oddFooter1.Text = "&L&\"Times New Roman,Regular\"" + footerLeft + "&C&P&R&\"Times New Roman,Regular\"" + footerRight;
headerFooter1.Append(oddHeader1);
headerFooter1.Append(oddFooter1);
ws.AppendChild(headerFooter1);
//save worksheet properties
//worksheetPart.Worksheet.Save();
}
public static void PageSetupUpdate(WorksheetPart worksheetPart, OrientationValues landscapeOrPortrait,
DoubleValue marginLeft, DoubleValue marginRight, DoubleValue marginTop, DoubleValue marginBottom, DoubleValue marginHeader, DoubleValue marginFooter,
Boolean isFitToPage, UInt32Value pageSize)
{
Worksheet ws = worksheetPart.Worksheet;
//page setup them moi pagesetup properties
SheetProperties sp = new SheetProperties(new PageSetupProperties());
ws.SheetProperties = sp;
PrintOptions printOp = new PrintOptions();
printOp.HorizontalCentered = true;
ws.AppendChild(printOp);
PageMargins pageMargins = new PageMargins();
pageMargins.Left = marginLeft;
pageMargins.Right = marginRight;
pageMargins.Top = marginTop;
pageMargins.Bottom = marginBottom;
pageMargins.Header = marginHeader;
pageMargins.Footer = marginFooter;
ws.AppendChild(pageMargins);
// Set the FitToPage property to true
ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(isFitToPage);
DocumentFormat.OpenXml.Spreadsheet.PageSetup pgOr = new DocumentFormat.OpenXml.Spreadsheet.PageSetup();
pgOr.Orientation = landscapeOrPortrait;
pgOr.PaperSize = pageSize;
pgOr.FitToHeight = 0;
pgOr.FitToWidth = 1;
ws.AppendChild(pgOr);
//save worksheet properties
//worksheetPart.Worksheet.Save();
}
private static void GenerateWorksheetPartContent(object obj)
{
WorksheetPart worksheetPart = obj as WorksheetPart;
Worksheet worksheet = new Worksheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetData sheetData = new SheetData();
PageSetup pageSetup = new PageSetup();
OrientationValues orientation = OrientationValues.Default;
bool showGreed = true;
if (nPageSetup.Count > 0)
{
SheetProperties sheetProperties = new SheetProperties();
PageSetupProperties pageSetupProperties = new PageSetupProperties()
{
FitToPage = true
};
sheetProperties.Append(pageSetupProperties);
NPageSetup ps = nPageSetup[0];
if (ps.LandscapeOrientation)
{
orientation = OrientationValues.Landscape;
}
pageSetup = new PageSetup()
{
PaperSize = (UInt32Value)9U, Orientation = orientation, FitToWidth = (UInt32Value)(UInt32)ps.FitToPagesWide, FitToHeight = (UInt32Value)(UInt32)ps.FitToPagesTail, HorizontalDpi = (UInt32Value)300U, VerticalDpi = (UInt32Value)300U, Id = "rId1"
};
if (!ps.Grid)
{
showGreed = false;
}
worksheet.Append(sheetProperties);
}
SheetViews sheetViews = new SheetViews();
sheetViews.Append(new SheetView()
{
ShowGridLines = showGreed, TabSelected = true, WorkbookViewId = (UInt32Value)0U
});
worksheet.Append(new SheetDimension()
{
Reference = "A1"
});
worksheet.Append(sheetViews);
Row previousRow = null;
foreach (CellData d in cellsData)
{
int i = (int)d.Row;
int j = (int)d.Column;
int k = (int)d.Styleindex;
if (previousRow == null || previousRow.RowIndex != i)
{
previousRow = GetRow(sheetData, i);
}
string excelColName = GetExcelColumnName(j);
Cell cell = new Cell()
{
CellReference = excelColName + i, StyleIndex = (UInt32Value)(UInt32)k
};
if (d.Data == null)
{
d.Data = "";
}
SetFormatedCellData(cell, d.Data.ToString(), i, excelColName);
InsertCellIntoRow(cell, previousRow);
}
Columns columns = new Columns();
InsertColumnWidth(columns);
MergeCells mergeCells = new MergeCells();
SetMergeCell(mergeCells);
worksheet.Append(new SheetFormatProperties()
{
DefaultRowHeight = 15D, DyDescent = 0.25D
});
if (columnWidthArr.Count > 0)
{
worksheet.Append(columns);
}
worksheet.Append(sheetData);
if (mergeArr.Count > 0)
{
worksheet.Append(mergeCells);
}
worksheetPart.Worksheet = worksheet;
worksheet.Append(pageSetup);
worksheet.Save();
}
/// <summary>
/// Takes a List<List<XlsxValue>> as a matrix and adds an Excel Worksheet for that data
/// Style information will have been included in each XlsValue object
/// You may also specify an orientation at this point
/// </summary>
/// <param name="rowLists"></param>
/// <param name="sheetLabel"></param>
/// <param name="oval"></param>
public void InsertDataWorksheet(List <List <XlsxValue> > rowLists, string sheetLabel, OrientationValues oval = OrientationValues.Default, bool Autosize = true)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = (uint)workbookPart.WorksheetParts.Count(), Name = sheetLabel
};
Worksheet workSheet = new Worksheet();
SheetData sheetData = new SheetData();
UInt32Value rowcounter = 1;
foreach (var list in rowLists)
{
// Add a row to the cell table.
Row row;
row = new Row()
{
RowIndex = rowcounter
};
sheetData.Append(row);
Cell refCell = null;
foreach (XlsxValue str in list)
{
Cell addCell = new Cell();
row.InsertAfter(addCell, refCell);
addCell.CellValue = new CellValue(str.Value);
//If the value was flagged as a potential number, then check if it parses as a number.If so, it will be a number in Xlsx
if (str.DataType == CellValues.Number)
{
if (!Double.TryParse(str.Value, out double result))
{
addCell.DataType = new EnumValue <CellValues>(CellValues.String);
}
}
else //otherwise we just allow it to be whatever type it was created as (default is CellValues.String)
{
addCell.DataType = str.DataType;
}
addCell.StyleIndex = (uint)str.StyleId;
refCell = addCell;
}
rowcounter++;
}
//Set column sizes, either automatically or with reference to each XlsxValue property
if (Autosize)
{
workSheet.Append(AutoSize(sheetData));
}
else
{
workSheet.Append(SetColumnSize(rowLists));
}
workSheet.AppendChild(sheetData);
worksheetPart.Worksheet = workSheet;
sheets.Append(sheet);
if (oval != OrientationValues.Default)
{
SetOrientation(worksheetPart, oval);
}
}
