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 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 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 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 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 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();
}
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 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());
}
}
}