public ISheet CloneSheet(int sheetNum)
{
this.ValidateSheetIndex(sheetNum);
XSSFSheet sheet1 = this.sheets[sheetNum];
XSSFSheet sheet2 = (XSSFSheet)this.CreateSheet(this.GetUniqueSheetName(sheet1.SheetName));
try
{
MemoryStream memoryStream = new MemoryStream();
sheet1.Write((Stream)memoryStream);
sheet2.Read((Stream) new MemoryStream(memoryStream.ToArray()));
}
catch (IOException ex)
{
throw new POIXMLException("Failed to clone sheet", (Exception)ex);
}
CT_Worksheet ctWorksheet = sheet2.GetCTWorksheet();
if (ctWorksheet.IsSetLegacyDrawing())
{
XSSFWorkbook.logger.Log(5, (object)"Cloning sheets with comments is not yet supported.");
ctWorksheet.UnsetLegacyDrawing();
}
if (ctWorksheet.IsSetPageSetup())
{
XSSFWorkbook.logger.Log(5, (object)"Cloning sheets with page setup is not yet supported.");
ctWorksheet.UnsetPageSetup();
}
sheet2.IsSelected = false;
List <POIXMLDocumentPart> relations = sheet1.GetRelations();
XSSFDrawing xssfDrawing = (XSSFDrawing)null;
foreach (POIXMLDocumentPart part in relations)
{
if (part is XSSFDrawing)
{
xssfDrawing = (XSSFDrawing)part;
}
else
{
PackageRelationship packageRelationship = part.GetPackageRelationship();
sheet2.GetPackagePart().AddRelationship(packageRelationship.TargetUri, packageRelationship.TargetMode.Value, packageRelationship.RelationshipType);
sheet2.AddRelation(packageRelationship.Id, part);
}
}
if (xssfDrawing != null)
{
if (ctWorksheet.IsSetDrawing())
{
ctWorksheet.UnsetDrawing();
}
(sheet2.CreateDrawingPatriarch() as XSSFDrawing).GetCTDrawing().Set(xssfDrawing.GetCTDrawing());
foreach (POIXMLDocumentPart relation in (sheet1.CreateDrawingPatriarch() as XSSFDrawing).GetRelations())
{
PackageRelationship packageRelationship = relation.GetPackageRelationship();
(sheet2.CreateDrawingPatriarch() as XSSFDrawing).GetPackagePart().AddRelationship(packageRelationship.TargetUri, packageRelationship.TargetMode.Value, packageRelationship.RelationshipType, packageRelationship.Id);
}
}
return((ISheet)sheet2);
}
public void TestColumn()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_Cols cols1 = worksheet.AddNewCols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Cols cols2 = worksheet.AddNewCols();
CT_Col col4 = cols2.AddNewCol();
col4.min = (3);
col4.max = (6);
// Remember - POI column 0 == OOXML column 1
ColumnHelper helper = new ColumnHelper(worksheet);
Assert.IsNotNull(helper.GetColumn(0, false));
Assert.IsNotNull(helper.GetColumn(1, false));
Assert.AreEqual(88.0, helper.GetColumn(0, false).width, 0.0);
Assert.AreEqual(0.0, helper.GetColumn(1, false).width, 0.0);
Assert.IsTrue(helper.GetColumn(0, false).hidden);
Assert.IsFalse(helper.GetColumn(1, false).hidden);
Assert.IsNull(helper.GetColumn(99, false));
Assert.IsNotNull(helper.GetColumn(5, false));
}
public StreamsheetParser(TextWriter output, bool startAtPrelude)
{
_output = output;
_worksheet = (CT_Worksheet)Root;
_sectionsInOrder = new WriteDelegate[14]
{
WritePrelude,
_worksheet.Write_sheetPr,
_worksheet.Write_sheetViews,
_worksheet.Write_sheetFormatPr,
_worksheet.Write_cols,
_worksheet.Write_sheetData,
_worksheet.Write_mergeCells,
_worksheet.Write_hyperlinks,
_worksheet.Write_pageMargins,
_worksheet.Write_pageSetup,
_worksheet.Write_headerFooter,
_worksheet.Write_drawing,
_worksheet.Write_picture,
WriteAfterword
};
_pendingCloseTag = null;
if (startAtPrelude)
{
_currentSection = StreamSheetSection.Prelude;
return;
}
TryMoveToSection(StreamSheetSection.SheetData, 1);
_pendingCloseTag = CT_Worksheet.SheetDataElementName;
new CT_SheetData().WriteOpenTag(_output, CT_Worksheet.SheetDataElementName, 1, Namespaces, root: false);
}
public StreamsheetParser(TextWriter output, bool startAtPrelude)
{
this._output = output;
this._worksheet = (CT_Worksheet)this.Root;
this._sectionsInOrder = new WriteDelegate[14]
{
this.WritePrelude,
this._worksheet.Write_sheetPr,
this._worksheet.Write_sheetViews,
this._worksheet.Write_sheetFormatPr,
this._worksheet.Write_cols,
this._worksheet.Write_sheetData,
this._worksheet.Write_mergeCells,
this._worksheet.Write_hyperlinks,
this._worksheet.Write_pageMargins,
this._worksheet.Write_pageSetup,
this._worksheet.Write_headerFooter,
this._worksheet.Write_drawing,
this._worksheet.Write_picture,
this.WriteAfterword
};
this._pendingCloseTag = null;
if (startAtPrelude)
{
this._currentSection = StreamSheetSection.Prelude;
}
else
{
this.TryMoveToSection(StreamSheetSection.SheetData, 1);
this._pendingCloseTag = CT_Worksheet.SheetDataElementName;
new CT_SheetData().WriteOpenTag(this._output, CT_Worksheet.SheetDataElementName, 1, this.Namespaces, false);
}
}
/**
* Adds a copy of HSSFConditionalFormatting object to the sheet
* <p>This method could be used to copy HSSFConditionalFormatting object
* from one sheet to another. For example:
* <pre>
* HSSFConditionalFormatting cf = sheet.GetConditionalFormattingAt(index);
* newSheet.AddConditionalFormatting(cf);
* </pre>
*
* @param cf HSSFConditionalFormatting object
* @return index of the new Conditional Formatting object
*/
public int AddConditionalFormatting(IConditionalFormatting cf)
{
XSSFConditionalFormatting xcf = (XSSFConditionalFormatting)cf;
CT_Worksheet sh = _sheet.GetCTWorksheet();
sh.AddNewConditionalFormatting().Set(xcf.GetCTConditionalFormatting());//this is already copied in Set -> .Copy()); ommitted
return(sh.SizeOfConditionalFormattingArray() - 1);
}
public int AddConditionalFormatting(IConditionalFormatting cf)
{
XSSFConditionalFormatting conditionalFormatting = (XSSFConditionalFormatting)cf;
CT_Worksheet ctWorksheet = this._sheet.GetCTWorksheet();
ctWorksheet.AddNewConditionalFormatting().Set(conditionalFormatting.GetCTConditionalFormatting());
return(ctWorksheet.SizeOfConditionalFormattingArray() - 1);
}
public void TestAddCleanColIntoCols()
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols1 = new CT_Cols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Col col3 = cols1.AddNewCol();
col3.min = (13);
col3.max = (16750);
Assert.AreEqual(3, cols1.sizeOfColArray());
CT_Col col4 = cols1.AddNewCol();
col4.min = (8);
col4.max = (9);
Assert.AreEqual(4, cols1.sizeOfColArray());
// No overlap
helper.addCleanColIntoCols(cols1, createCol(4, 5));
Assert.AreEqual(5, cols1.sizeOfColArray());
// Overlaps with 8 - 9 (overlap and after replacements required)
CT_Col col6 = createCol(8, 11);
col6.hidden = (true);
helper.AddCleanColIntoCols(cols1, col6);
Assert.AreEqual(6, cols1.sizeOfColArray());
// Overlaps with 8 - 9 (before and overlap replacements required)
CT_Col col7 = createCol(6, 8);
col7.width = (17.0);
helper.AddCleanColIntoCols(cols1, col7);
Assert.AreEqual(8, cols1.sizeOfColArray());
// Overlaps with 13 - 16750 (before, overlap and after replacements required)
helper.addCleanColIntoCols(cols1, createCol(20, 30));
Assert.AreEqual(10, cols1.sizeOfColArray());
// Overlaps with 20 - 30 (before, overlap and after replacements required)
helper.addCleanColIntoCols(cols1, createCol(25, 27));
// TODO - assert something interesting
Assert.AreEqual(12, cols1.col.Count);
Assert.AreEqual(1u, cols1.GetColArray(0).min);
Assert.AreEqual(16750u, cols1.GetColArray(11).max);
}
public XSSFPrintSetup(CT_Worksheet worksheet)
{
this.ctWorksheet = worksheet;
this.pageSetup = !this.ctWorksheet.IsSetPageSetup() ? this.ctWorksheet.AddNewPageSetup() : this.ctWorksheet.pageSetup;
if (this.ctWorksheet.IsSetPageMargins())
{
this.pageMargins = this.ctWorksheet.pageMargins;
}
else
{
this.pageMargins = this.ctWorksheet.AddNewPageMargins();
}
}
public void TestSetGetUsePage()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.useFirstPageNumber = (false);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(false, printSetup.UsePage);
printSetup.UsePage = (true);
Assert.AreEqual(true, pSetup.useFirstPageNumber);
}
public void TestSetGetNotes()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.cellComments = ST_CellComments.none;
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(false, printSetup.Notes);
printSetup.Notes = true;
Assert.AreEqual(PrintCellComments.AS_DISPLAYED.Value, (int)pSetup.cellComments);
}
public void TestSetGetDraft()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.draft = (false);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(false, printSetup.Draft);
printSetup.Draft = (true);
Assert.AreEqual(true, pSetup.draft);
}
public void TestSetGetNoColor()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.blackAndWhite = (false);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(false, printSetup.NoColor);
printSetup.NoColor = true;
Assert.AreEqual(true, pSetup.blackAndWhite);
}
public void TestSetGetValidSettings()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.usePrinterDefaults = (false);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(false, printSetup.ValidSettings);
printSetup.ValidSettings = (true);
Assert.AreEqual(true, pSetup.usePrinterDefaults);
}
public void TestSetGetScale()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.scale = (uint)9;
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(9, printSetup.Scale);
printSetup.Scale = ((short)100);
Assert.AreEqual((uint)100, pSetup.scale);
}
public void TestSetGetCopies()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.copies = (9);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(9, printSetup.Copies);
printSetup.Copies = (short)15;
Assert.AreEqual((uint)15, pSetup.copies);
}
public void TestSetGetLeftToRight()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.pageOrder = (ST_PageOrder.downThenOver);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(false, printSetup.LeftToRight);
printSetup.LeftToRight = (true);
Assert.AreEqual(PageOrder.OVER_THEN_DOWN.Value, (int)pSetup.pageOrder);
}
public void TestSetGetPageStart()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.firstPageNumber = 9;
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(9, printSetup.PageStart);
printSetup.PageStart = ((short)1);
Assert.AreEqual((uint)1, pSetup.firstPageNumber);
}
public void SerializeEmptyWorksheetDocumentTest()
{
CT_Worksheet worksheet = new CT_Worksheet();
StringWriter stream = new StringWriter();
WorksheetDocument_Accessor.serializer.Serialize(stream, worksheet, WorksheetDocument_Accessor.namespaces);
string expected = @"<?xml version=""1.0"" encoding=""utf-16""?>
<worksheet xmlns:r=""http://schemas.openxmlformats.org/officeDocument/2006/relationships"" xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"">
<sheetData />
</worksheet>";
Assert.AreEqual(expected, stream.ToString());
}
public void TestSetGetPaperSize()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.paperSize = (9);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(PaperSize.A4, printSetup.GetPaperSizeEnum());
Assert.AreEqual(9, printSetup.PaperSize);
printSetup.SetPaperSize(PaperSize.A3);
Assert.AreEqual((uint)8, pSetup.paperSize);
}
/**
* Creates and adds a hidden column and then a best fit column with the given min/max pairs.
* Suitable for testing handling of overlap.
*/
private CT_Cols createHiddenAndBestFitColsWithHelper(int hiddenMin, int hiddenMax, int bestFitMin, int bestFitMax)
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols = worksheet.GetColsArray(0);
CT_Col hidden = createCol(hiddenMin, hiddenMax);
hidden.hidden = (true);
helper.addCleanColIntoCols(cols, hidden);
CT_Col bestFit = createCol(bestFitMin, bestFitMax);
bestFit.bestFit = (true);
helper.addCleanColIntoCols(cols, bestFit);
return(cols);
}
public void TestSetGetOrientation()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.orientation = (ST_Orientation.portrait);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(PrintOrientation.PORTRAIT, printSetup.Orientation);
Assert.AreEqual(false, printSetup.Landscape);
Assert.AreEqual(false, printSetup.NoOrientation);
printSetup.Orientation = (PrintOrientation.LANDSCAPE);
Assert.AreEqual((int)pSetup.orientation, printSetup.Orientation.Value);
Assert.AreEqual(true, printSetup.Landscape);
Assert.AreEqual(false, printSetup.NoOrientation);
}
public void TestSetGetHVResolution()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.horizontalDpi = (120);
pSetup.verticalDpi = (100);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(120, printSetup.HResolution);
Assert.AreEqual(100, printSetup.VResolution);
printSetup.HResolution = ((short)150);
printSetup.VResolution = ((short)130);
Assert.AreEqual((uint)150, pSetup.horizontalDpi);
Assert.AreEqual((uint)130, pSetup.verticalDpi);
}
public void TestSetGetHeaderFooterMargin()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageMargins pMargins = worksheet.AddNewPageMargins();
pMargins.header = (1.5);
pMargins.footer = (2);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(1.5, printSetup.HeaderMargin, 0.0);
Assert.AreEqual(2.0, printSetup.FooterMargin, 0.0);
printSetup.HeaderMargin = (5);
printSetup.FooterMargin = (3.5);
Assert.AreEqual(5.0, pMargins.header, 0.0);
Assert.AreEqual(3.5, pMargins.footer, 0.0);
}
public void TestSetGetFitWidthHeight()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_PageSetup pSetup = worksheet.AddNewPageSetup();
pSetup.fitToWidth = (50);
pSetup.fitToHeight = (99);
XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
Assert.AreEqual(50, printSetup.FitWidth);
Assert.AreEqual(99, printSetup.FitHeight);
printSetup.FitWidth = ((short)66);
printSetup.FitHeight = ((short)80);
Assert.AreEqual((uint)66, pSetup.fitToWidth);
Assert.AreEqual((uint)80, pSetup.fitToHeight);
}
private static int countColumns(CT_Worksheet worksheet)
{
int count;
count = 0;
for (int i = 0; i < worksheet.sizeOfColsArray(); i++)
{
for (int y = 0; y < worksheet.GetColsArray(i).sizeOfColArray(); y++)
{
for (long k = worksheet.GetColsArray(i).GetColArray(y).min; k <= worksheet
.GetColsArray(i).GetColArray(y).max; k++)
{
count++;
}
}
}
return(count);
}
public XMLPageSetupModel(CT_Worksheet sheet, XMLWorksheetModel sheetModel)
{
if (sheet == null || sheetModel == null)
{
throw new FatalException();
}
_sheet = sheet;
_sheetModel = sheetModel;
_interface = new PageSetup(this);
if (_sheet.PageMargins == null)
{
SetDefaultMargins();
}
if (_sheet.PageSetup == null)
{
SetDefaultPageSetup();
}
SetupUseZoom();
}
public void TestCloneCol()
{
CT_Worksheet worksheet = new CT_Worksheet();
ColumnHelper helper = new ColumnHelper(worksheet);
CT_Cols cols = new CT_Cols();
CT_Col col = new CT_Col();
col.min = (2);
col.max = (8);
col.hidden = (true);
col.width = (13.4);
CT_Col newCol = helper.CloneCol(cols, col);
Assert.AreEqual(2u, newCol.min);
Assert.AreEqual(8u, newCol.max);
Assert.IsTrue(newCol.hidden);
Assert.AreEqual(13.4, newCol.width, 0.0);
}
public void TestBug57423()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CT_Worksheet wsh = sheet.GetCTWorksheet();
CT_SheetData sheetData = wsh.sheetData;
XSSFRow row1 = (XSSFRow)sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("a");
XSSFRow row2 = (XSSFRow)sheet.CreateRow(1);
row2.CreateCell(0).SetCellValue("b");
XSSFRow row3 = (XSSFRow)sheet.CreateRow(2);
row3.CreateCell(0).SetCellValue("c");
sheet.ShiftRows(0, 1, 3); //move "a" and "b" 3 rows down
// Before: After:
// A A
// 1 a <empty>
// 2 b <empty>
// 3 c c
// 4 a
// 5 b
List <CT_Row> xrow = sheetData.row;
Assert.AreEqual(3, xrow.Count);
// Rows are sorted: [3, 4, 5]
Assert.AreEqual(3u, xrow[0].r);
Assert.IsTrue(xrow[0].Equals(row3.GetCTRow()));
Assert.AreEqual(4u, xrow[1].r);
Assert.IsTrue(xrow[1].Equals(row1.GetCTRow()));
Assert.AreEqual(5u, xrow[2].r);
Assert.IsTrue(xrow[2].Equals(row2.GetCTRow()));
}
public XMLPageSetupModel(CT_Worksheet sheet, XMLWorksheetModel sheetModel)
{
if (sheet != null && sheetModel != null)
{
this._sheet = sheet;
this._sheetModel = sheetModel;
this._interface = new PageSetup(this);
if (this._sheet.PageMargins == null)
{
this.SetDefaultMargins();
}
if (this._sheet.PageSetup == null)
{
this.SetDefaultPageSetup();
}
this.SetupUseZoom();
return;
}
throw new FatalException();
}
public void TestCleanColumns()
{
CT_Worksheet worksheet = new CT_Worksheet();
CT_Cols cols1 = worksheet.AddNewCols();
CT_Col col1 = cols1.AddNewCol();
col1.min = (1);
col1.max = (1);
col1.width = (88);
col1.hidden = (true);
CT_Col col2 = cols1.AddNewCol();
col2.min = (2);
col2.max = (3);
CT_Cols cols2 = worksheet.AddNewCols();
CT_Col col4 = cols2.AddNewCol();
col4.min = (13);
col4.max = (16384);
// Test cleaning cols
Assert.AreEqual(2, worksheet.sizeOfColsArray());
int count = countColumns(worksheet);
Assert.AreEqual(16375, count);
// Clean columns and Test a clean worksheet
ColumnHelper helper = new ColumnHelper(worksheet);
Assert.AreEqual(1, worksheet.sizeOfColsArray());
count = countColumns(worksheet);
Assert.AreEqual(16375, count);
// Remember - POI column 0 == OOXML column 1
Assert.AreEqual(88.0, helper.GetColumn(0, false).width, 0.0);
Assert.IsTrue(helper.GetColumn(0, false).hidden);
Assert.AreEqual(0.0, helper.GetColumn(1, false).width, 0.0);
Assert.IsFalse(helper.GetColumn(1, false).hidden);
}
