private void assertCellsWithMissingR(XSSFRow row)
{
XSSFCell a1 = (XSSFCell)row.GetCell(0);
Assert.IsNotNull(a1);
XSSFCell a2 = (XSSFCell)row.GetCell(1);
Assert.IsNotNull(a2);
XSSFCell a5 = (XSSFCell)row.GetCell(4);
Assert.IsNotNull(a5);
XSSFCell a6 = (XSSFCell)row.GetCell(5);
Assert.IsNotNull(a6);
Assert.AreEqual(6, row.LastCellNum);
Assert.AreEqual(4, row.PhysicalNumberOfCells);
Assert.AreEqual(a1.StringCellValue, "A1");
Assert.AreEqual(a2.StringCellValue, "B1");
Assert.AreEqual(a5.StringCellValue, "E1");
Assert.AreEqual(a6.StringCellValue, "F1");
// even if R attribute is not set,
// POI is able to re-construct it from column and row indexes
Assert.AreEqual(a1.GetReference(), "A1");
Assert.AreEqual(a2.GetReference(), "B1");
Assert.AreEqual(a5.GetReference(), "E1");
Assert.AreEqual(a6.GetReference(), "F1");
}
/**
* Synchronize table headers with cell values in the parent sheet.
* Headers <em>must</em> be in sync, otherwise Excel will display a
* "Found unreadable content" message on startup.
*/
public void UpdateHeaders()
{
XSSFSheet sheet = (XSSFSheet)GetParent();
CellReference ref1 = GetStartCellReference() as CellReference;
if (ref1 == null)
{
return;
}
int headerRow = ref1.Row;
int firstHeaderColumn = ref1.Col;
XSSFRow row = sheet.GetRow(headerRow) as XSSFRow;
if (row != null && row.GetCTRow() != null)
{
int cellnum = firstHeaderColumn;
foreach (CT_TableColumn col in GetCTTable().tableColumns.tableColumn)
{
XSSFCell cell = row.GetCell(cellnum) as XSSFCell;
if (cell != null)
{
col.name = (cell.StringCellValue);
}
cellnum++;
}
}
}
public void TestMissingRAttribute()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFRow row = (XSSFRow)sheet.CreateRow(0);
XSSFCell a1 = (XSSFCell)row.CreateCell(0);
a1.SetCellValue("A1");
XSSFCell a2 = (XSSFCell)row.CreateCell(1);
a2.SetCellValue("B1");
XSSFCell a4 = (XSSFCell)row.CreateCell(4);
a4.SetCellValue("E1");
XSSFCell a6 = (XSSFCell)row.CreateCell(5);
a6.SetCellValue("F1");
assertCellsWithMissingR(row);
a2.GetCTCell().unsetR();
a6.GetCTCell().unsetR();
assertCellsWithMissingR(row);
wb = (XSSFWorkbook)_testDataProvider.WriteOutAndReadBack(wb);
row = (XSSFRow)wb.GetSheetAt(0).GetRow(0);
assertCellsWithMissingR(row);
}
public void TestBug55843b()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFRow row2 = sheet.CreateRow(1) as XSSFRow;
XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
cellB1.SetCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))");
CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(Evaluate);
Assert.AreEqual("2", Evaluate.FormatAsString());
cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")");
CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(EvaluateN);
Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
Assert.AreEqual("2", EvaluateN.FormatAsString());
}
finally
{
wb.Close();
}
}
private void CheckWorkbookGrouping(IWorkbook wb, bool?[] collapsed, bool[] hidden, int[] outlineLevel)
{
printWorkbook(wb);
ISheet sheet = wb.GetSheetAt(0);
Assert.AreEqual(collapsed.Length, hidden.Length);
Assert.AreEqual(collapsed.Length, outlineLevel.Length);
Assert.AreEqual(collapsed.Length, sheet.LastRowNum - sheet.FirstRowNum + 1,
"Expected " + collapsed.Length + " rows with collapsed state, but had " + (sheet.LastRowNum - sheet.FirstRowNum + 1) + " rows ("
+ sheet.FirstRowNum + "-" + sheet.LastRowNum + ")");
for (int i = sheet.FirstRowNum; i < sheet.LastRowNum; i++)
{
if (collapsed[i - sheet.FirstRowNum] == null)
{
continue;
}
XSSFRow row = (XSSFRow)sheet.GetRow(i);
Assert.IsNotNull(row, "Could not read row " + i);
Assert.IsNotNull(row.GetCTRow(), "Could not read row " + i);
Assert.AreEqual(collapsed[i - sheet.FirstRowNum], row.GetCTRow().collapsed, "Row: " + i + ": collapsed");
Assert.AreEqual(hidden[i - sheet.FirstRowNum], row.GetCTRow().hidden, "Row: " + i + ": hidden");
Assert.AreEqual(outlineLevel[i - sheet.FirstRowNum], row.GetCTRow().outlineLevel, "Row: " + i + ": level");
}
WriteToFile(wb);
}
public void Bug56274()
{
// read sample file
XSSFWorkbook inputWorkbook = XSSFTestDataSamples.OpenSampleWorkbook("56274.xlsx");
// read the original sheet header order
XSSFRow row = inputWorkbook.GetSheetAt(0).GetRow(0) as XSSFRow;
List <String> headers = new List <String>();
foreach (ICell cell in row)
{
headers.Add(cell.StringCellValue);
}
// no SXSSF class
// save the worksheet as-is using SXSSF
//File outputFile = File.CreateTempFile("poi-56274", ".xlsx");
//SXSSFWorkbook outputWorkbook = new NPOI.XSSF.streaming.SXSSFWorkbook(inputWorkbook);
//outputWorkbook.Write(new FileOutputStream(outputFile));
// re-read the saved file and make sure headers in the xml are in the original order
//inputWorkbook = new NPOI.XSSF.UserModel.XSSFWorkbook(new FileStream(outputFile));
inputWorkbook = XSSFTestDataSamples.WriteOutAndReadBack(inputWorkbook) as XSSFWorkbook;
CT_Table ctTable = (inputWorkbook.GetSheetAt(0) as XSSFSheet).GetTables()[0].GetCTTable();
List <CT_TableColumn> ctTableColumnList = ctTable.tableColumns.tableColumn;
Assert.AreEqual(headers.Count, ctTableColumnList.Count,
"number of headers in xml table should match number of header cells in worksheet");
for (int i = 0; i < headers.Count; i++)
{
Assert.AreEqual(headers[i], ctTableColumnList[i].name,
"header name in xml table should match number of header cells in worksheet");
}
//Assert.IsTrue(outputFile.Delete());
}
public void UpdateHeaders()
{
XSSFSheet parent = (XSSFSheet)this.GetParent();
CellReference startCellReference = this.GetStartCellReference();
if (startCellReference == null)
{
return;
}
int row1 = startCellReference.Row;
int col = (int)startCellReference.Col;
XSSFRow row2 = parent.GetRow(row1) as XSSFRow;
if (row2 == null)
{
return;
}
foreach (CT_TableColumn ctTableColumn in this.GetCTTable().tableColumns.tableColumn)
{
int cellnum = (int)ctTableColumn.id - 1 + col;
XSSFCell cell = row2.GetCell(cellnum) as XSSFCell;
if (cell != null)
{
ctTableColumn.name = cell.StringCellValue;
}
}
}
/**
* Construct a XSSFCell.
*
* @param row the parent row.
* @param cell the xml bean Containing information about the cell.
*/
public XSSFCell(XSSFRow row, CT_Cell cell)
{
_cell = cell;
_row = row;
if (cell.r != null)
{
_cellNum = new CellReference(cell.r).Col;
}
_sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
_stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
}
public IEvaluationCell GetCell(int rowIndex, int columnIndex)
{
// cache for performance: ~30% speedup due to caching
if (_cellCache == null)
{
_cellCache = new Dictionary <CellKey, IEvaluationCell>(_xs.LastRowNum * 3);
foreach (IRow row in _xs)
{
int rowNum = row.RowNum;
foreach (ICell cell in row)
{
// cast is safe, the iterator is just defined using the interface
CellKey key1 = new CellKey(rowNum, cell.ColumnIndex);
IEvaluationCell evalcell1 = new XSSFEvaluationCell((XSSFCell)cell, this);
_cellCache.Add(key1, evalcell1);
}
}
}
CellKey key = new CellKey(rowIndex, columnIndex);
IEvaluationCell evalcell = null;
if (_cellCache.ContainsKey(key))
{
evalcell = _cellCache[key];
}
// If cache is stale, update cache with this one cell
// This is a compromise between rebuilding the entire cache
// (which would quickly defeat the benefit of the cache)
// and not caching at all.
// See bug 59958: Add cells on the fly to the evaluation sheet cache on cache miss
if (evalcell == null)
{
XSSFRow row = _xs.GetRow(rowIndex) as XSSFRow;
if (row == null)
{
return(null);
}
XSSFCell cell = row.GetCell(columnIndex) as XSSFCell;
if (cell == null)
{
return(null);
}
evalcell = new XSSFEvaluationCell(cell, this);
_cellCache[key] = evalcell;
}
return(evalcell);
}
public void Bug51158()
{
// create a workbook
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet("Test Sheet") as XSSFSheet;
XSSFRow row = sheet.CreateRow(2) as XSSFRow;
XSSFCell cell = row.CreateCell(3) as XSSFCell;
cell.SetCellValue("test1");
//XSSFCreationHelper helper = workbook.GetCreationHelper();
//cell.Hyperlink=(/*setter*/helper.CreateHyperlink(0));
XSSFComment comment = (sheet.CreateDrawingPatriarch() as XSSFDrawing).CreateCellComment(new XSSFClientAnchor()) as XSSFComment;
Assert.IsNotNull(comment);
comment.SetString("some comment");
// ICellStyle cs = workbook.CreateCellStyle();
// cs.ShrinkToFit=(/*setter*/false);
// row.CreateCell(0).CellStyle=(/*setter*/cs);
// write the first excel file
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
Assert.IsNotNull(wb2);
sheet = wb2.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.IsNull(row.GetCell(4));
// add a new cell to the sheet
cell = row.CreateCell(4) as XSSFCell;
cell.SetCellValue("test2");
// write the second excel file
XSSFWorkbook wb3 = XSSFTestDataSamples.WriteOutAndReadBack(wb2) as XSSFWorkbook;
Assert.IsNotNull(wb3);
sheet = wb3.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.AreEqual("test2", row.GetCell(4).StringCellValue);
wb3.Close();
wb2.Close();
wb1.Close();
}
public void TestCopyRowFrom()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet("test") as XSSFSheet;
XSSFRow srcRow = sheet.CreateRow(0) as XSSFRow;
srcRow.CreateCell(0).SetCellValue("Hello");
XSSFRow destRow = sheet.CreateRow(1) as XSSFRow;
destRow.CopyRowFrom(srcRow, new CellCopyPolicy());
Assert.IsNotNull(destRow.GetCell(0));
Assert.AreEqual("Hello", destRow.GetCell(0).StringCellValue);
workbook.Close();
}
private void setUp_testCopyCellFrom_CellCopyPolicy()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFRow row = wb.CreateSheet().CreateRow(0) as XSSFRow;
srcCell = row.CreateCell(0) as XSSFCell;
destCell = row.CreateCell(1) as XSSFCell;
srcCell.CellFormula = ("2+3");
ICellStyle style = wb.CreateCellStyle();
style.BorderTop = BorderStyle.Thick;
style.FillBackgroundColor = ((short)5);
srcCell.CellStyle = (style);
destCell.SetCellValue(true);
}
public void TestMissingRAttributeBug54288()
{
// workbook with cells missing the R attribute
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288.xlsx");
// same workbook re-saved in Excel 2010, the R attribute is updated for every cell with the right value.
XSSFWorkbook wbRef = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288-ref.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
XSSFSheet sheetRef = (XSSFSheet)wbRef.GetSheetAt(0);
Assert.AreEqual(sheetRef.PhysicalNumberOfRows, sheet.PhysicalNumberOfRows);
// Test idea: iterate over cells in the reference worksheet, they all have the R attribute set.
// For each cell from the reference sheet find the corresponding cell in the problematic file (with missing R)
// and assert that POI reads them equally:
DataFormatter formater = new DataFormatter();
foreach (IRow r in sheetRef)
{
XSSFRow rowRef = (XSSFRow)r;
XSSFRow row = (XSSFRow)sheet.GetRow(rowRef.RowNum);
Assert.AreEqual(rowRef.PhysicalNumberOfCells, row.PhysicalNumberOfCells, "number of cells in row[" + row.RowNum + "]");
foreach (ICell c in rowRef.Cells)
{
XSSFCell cellRef = (XSSFCell)c;
XSSFCell cell = (XSSFCell)row.GetCell(cellRef.ColumnIndex);
Assert.AreEqual(cellRef.ColumnIndex, cell.ColumnIndex);
Assert.AreEqual(cellRef.GetReference(), cell.GetReference());
if (!cell.GetCTCell().IsSetR())
{
Assert.IsTrue(cellRef.GetCTCell().IsSetR(), "R must e set in cellRef");
String valRef = formater.FormatCellValue(cellRef);
String val = formater.FormatCellValue(cell);
Assert.AreEqual(valRef, val);
}
}
}
}
/**
* Construct a XSSFCell.
*
* @param row the parent row.
* @param cell the xml bean Containing information about the cell.
*/
public XSSFCell(XSSFRow row, CT_Cell cell)
{
_cell = cell;
_row = row;
if (cell.r != null)
{
_cellNum = new CellReference(cell.r).Col;
}
else
{
int prevNum = row.LastCellNum;
if (prevNum != -1)
{
_cellNum = (row as XSSFRow).GetCell(prevNum - 1, MissingCellPolicy.RETURN_NULL_AND_BLANK).ColumnIndex + 1;
}
}
_sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
_stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
}
public void TestCopyRowOverwritesExistingRow()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet1 = workbook.CreateSheet("Sheet1") as XSSFSheet;
ISheet sheet2 = workbook.CreateSheet("Sheet2");
IRow srcRow = sheet1.CreateRow(0);
XSSFRow destRow = sheet1.CreateRow(1) as XSSFRow;
IRow observerRow = sheet1.CreateRow(2);
IRow externObserverRow = sheet2.CreateRow(0);
srcRow.CreateCell(0).SetCellValue("hello");
srcRow.CreateCell(1).SetCellValue("world");
destRow.CreateCell(0).SetCellValue(5.0); //A2 -> 5.0
destRow.CreateCell(1).CellFormula = ("A1"); // B2 -> A1 -> "hello"
observerRow.CreateCell(0).CellFormula = ("A2"); // A3 -> A2 -> 5.0
observerRow.CreateCell(1).CellFormula = ("B2"); // B3 -> B2 -> A1 -> "hello"
externObserverRow.CreateCell(0).CellFormula = ("Sheet1!A2"); //Sheet2!A1 -> Sheet1!A2 -> 5.0
// overwrite existing destRow with row-copy of srcRow
destRow.CopyRowFrom(srcRow, new CellCopyPolicy());
// copyRowFrom should update existing destRow, rather than creating a new row and reassigning the destRow pointer
// to the new row (and allow the old row to be garbage collected)
// this is mostly so existing references to rows that are overwritten are updated
// rather than allowing users to continue updating rows that are no longer part of the sheet
Assert.AreSame(srcRow, sheet1.GetRow(0), "existing references to srcRow are still valid");
Assert.AreSame(destRow, sheet1.GetRow(1), "existing references to destRow are still valid");
Assert.AreSame(observerRow, sheet1.GetRow(2), "existing references to observerRow are still valid");
Assert.AreSame(externObserverRow, sheet2.GetRow(0), "existing references to externObserverRow are still valid");
// Make sure copyRowFrom actually copied row (this is tested elsewhere)
Assert.AreEqual(CellType.String, destRow.GetCell(0).CellType);
Assert.AreEqual("hello", destRow.GetCell(0).StringCellValue);
// We don't want #REF! errors if we copy a row that contains cells that are referred to by other cells outside of copied region
Assert.AreEqual("A2", observerRow.GetCell(0).CellFormula, "references to overwritten cells are unmodified");
Assert.AreEqual("B2", observerRow.GetCell(1).CellFormula, "references to overwritten cells are unmodified");
Assert.AreEqual("Sheet1!A2", externObserverRow.GetCell(0).CellFormula, "references to overwritten cells are unmodified");
workbook.Close();
}
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()));
}
private static DataTable GetDataTableByXlsx(string excelFilePath, DataTable dt)
{
XSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
int r = 0;
while (rows.MoveNext())
{
r++;
if (r == 1)
{
continue;
}
NPOI.XSSF.UserModel.XSSFRow row = (NPOI.XSSF.UserModel.XSSFRow)rows.Current;
DataRow rw = dt.NewRow();
for (int i = 1; i <= row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell == null)
{
continue;
}
else
{
rw[i - 1] = cell.ToString();
}
}
dt.Rows.Add(rw);
}
return(dt);
}
}
public void TestInlineString()
{
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("xlsx-jdbc.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
XSSFRow row = (XSSFRow)sheet.GetRow(1);
XSSFCell cell_0 = (XSSFCell)row.GetCell(0);
Assert.AreEqual(ST_CellType.inlineStr, cell_0.GetCTCell().t);
Assert.IsTrue(cell_0.GetCTCell().IsSetIs());
Assert.AreEqual("A Very large string in column 1 AAAAAAAAAAAAAAAAAAAAA", cell_0.StringCellValue);
XSSFCell cell_1 = (XSSFCell)row.GetCell(1);
Assert.AreEqual(ST_CellType.inlineStr, cell_1.GetCTCell().t);
Assert.IsTrue(cell_1.GetCTCell().IsSetIs());
Assert.AreEqual("foo", cell_1.StringCellValue);
XSSFCell cell_2 = (XSSFCell)row.GetCell(2);
Assert.AreEqual(ST_CellType.inlineStr, cell_2.GetCTCell().t);
Assert.IsTrue(cell_2.GetCTCell().IsSetIs());
Assert.AreEqual("bar", row.GetCell(2).StringCellValue);
}
public void TestBug56511()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56511.xlsx");
foreach (XSSFSheet sheet in wb)
{
int lastRow = sheet.LastRowNum;
for (int rowIdx = sheet.FirstRowNum; rowIdx <= lastRow; rowIdx++)
{
XSSFRow row = sheet.GetRow(rowIdx) as XSSFRow;
if (row != null)
{
int lastCell = row.LastCellNum;
for (int cellIdx = row.FirstCellNum; cellIdx <= lastCell; cellIdx++)
{
XSSFCell cell = row.GetCell(cellIdx) as XSSFCell;
if (cell != null)
{
//System.out.Println("row " + rowIdx + " column " + cellIdx + ": " + cell.CellType + ": " + cell.ToString());
XSSFRichTextString richText = cell.RichStringCellValue as XSSFRichTextString;
int anzFormattingRuns = richText.NumFormattingRuns;
for (int run = 0; run < anzFormattingRuns; run++)
{
/*XSSFFont font =*/
richText.GetFontOfFormattingRun(run);
//System.out.Println(" run " + run
// + " font " + (font == null ? "<null>" : font.FontName));
}
}
}
}
}
}
}
public void TestCreate()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;
String[] urls =
{
"http://apache.org/",
"www.apache.org",
"/temp",
"file:///c:/temp",
"http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"
};
for (int i = 0; i < urls.Length; i++)
{
String s = urls[i];
XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
link.Address = (s);
XSSFCell cell = row.CreateCell(i) as XSSFCell;
cell.Hyperlink = (link);
}
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
// Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
sheet = workbook.GetSheetAt(0) as XSSFSheet;
rels = sheet.GetPackagePart().Relationships;
Assert.AreEqual(urls.Length, rels.Size);
for (int i = 0; i < rels.Size; i++)
{
PackageRelationship rel = rels.GetRelationship(i);
if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
{
Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
}
else
{
// there should be a relationship for each URL
Assert.AreEqual(urls[i], rel.TargetUri.ToString());
}
}
}
public void TestCopyRowFromExternalSheet()
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet srcSheet = workbook.CreateSheet("src");
XSSFSheet destSheet = workbook.CreateSheet("dest") as XSSFSheet;
workbook.CreateSheet("other");
IRow srcRow = srcSheet.CreateRow(0);
int col = 0;
//Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks)
srcRow.CreateCell(col++).CellFormula = ("B5");
srcRow.CreateCell(col++).CellFormula = ("src!B5");
srcRow.CreateCell(col++).CellFormula = ("dest!B5");
srcRow.CreateCell(col++).CellFormula = ("other!B5");
//Test 2D and 3D Ref Ptgs with absolute row
srcRow.CreateCell(col++).CellFormula = ("B$5");
srcRow.CreateCell(col++).CellFormula = ("src!B$5");
srcRow.CreateCell(col++).CellFormula = ("dest!B$5");
srcRow.CreateCell(col++).CellFormula = ("other!B$5");
//Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks)
srcRow.CreateCell(col++).CellFormula = ("SUM(B5:D$5)");
srcRow.CreateCell(col++).CellFormula = ("SUM(src!B5:D$5)");
srcRow.CreateCell(col++).CellFormula = ("SUM(dest!B5:D$5)");
srcRow.CreateCell(col++).CellFormula = ("SUM(other!B5:D$5)");
//////////////////
XSSFRow destRow = destSheet.CreateRow(1) as XSSFRow;
destRow.CopyRowFrom(srcRow, new CellCopyPolicy());
//////////////////
//Test 2D and 3D Ref Ptgs (Pxg for OOXML Workbooks)
col = 0;
ICell cell = destRow.GetCell(col++);
Assert.IsNotNull(cell);
Assert.AreEqual("B6", cell.CellFormula, "RefPtg");
cell = destRow.GetCell(col++);
Assert.IsNotNull(cell);
Assert.AreEqual("src!B6", cell.CellFormula, "Ref3DPtg");
cell = destRow.GetCell(col++);
Assert.IsNotNull(cell);
Assert.AreEqual("dest!B6", cell.CellFormula, "Ref3DPtg");
cell = destRow.GetCell(col++);
Assert.IsNotNull(cell);
Assert.AreEqual("other!B6", cell.CellFormula, "Ref3DPtg");
/////////////////////////////////////////////
//Test 2D and 3D Ref Ptgs with absolute row (Ptg row number shouldn't change)
cell = destRow.GetCell(col++);
Assert.IsNotNull(cell);
Assert.AreEqual("B$5", cell.CellFormula, "RefPtg");
cell = destRow.GetCell(col++);
Assert.IsNotNull(cell);
Assert.AreEqual("src!B$5", cell.CellFormula, "Ref3DPtg");
cell = destRow.GetCell(col++);
Assert.IsNotNull(cell);
Assert.AreEqual("dest!B$5", cell.CellFormula, "Ref3DPtg");
cell = destRow.GetCell(col++);
Assert.IsNotNull(cell);
Assert.AreEqual("other!B$5", cell.CellFormula, "Ref3DPtg");
//////////////////////////////////////////
//Test 2D and 3D Area Ptgs (Pxg for OOXML Workbooks)
// Note: absolute row changes from last cell to first cell in order
// to maintain topLeft:bottomRight order
cell = destRow.GetCell(col++);
Assert.IsNotNull(cell);
Assert.AreEqual("SUM(B$5:D6)", cell.CellFormula, "Area2DPtg");
cell = destRow.GetCell(col++);
Assert.IsNotNull(cell);
Assert.AreEqual("SUM(src!B$5:D6)", cell.CellFormula, "Area3DPtg");
cell = destRow.GetCell(col++);
Assert.IsNotNull(destRow.GetCell(6));
Assert.AreEqual("SUM(dest!B$5:D6)", cell.CellFormula, "Area3DPtg");
cell = destRow.GetCell(col++);
Assert.IsNotNull(destRow.GetCell(7));
Assert.AreEqual("SUM(other!B$5:D6)", cell.CellFormula, "Area3DPtg");
workbook.Close();
}