public void TestURLsWithHashMark()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("59775.xlsx");
XSSFSheet sh = wb.GetSheetAt(0) as XSSFSheet;
CellAddress A2 = new CellAddress("A2");
CellAddress A3 = new CellAddress("A3");
CellAddress A4 = new CellAddress("A4");
CellAddress A7 = new CellAddress("A7");
XSSFHyperlink link = sh.GetHyperlink(A2) as XSSFHyperlink;
Assert.AreEqual("address", "A2", link.CellRef);
Assert.AreEqual(HyperlinkType.Url, link.Type, "link type");
Assert.AreEqual("http://twitter.com/#!/apacheorg", link.Address, "link target");
link = sh.GetHyperlink(A3) as XSSFHyperlink;
Assert.AreEqual("address", "A3", link.CellRef);
Assert.AreEqual(HyperlinkType.Url, link.Type, "link type");
Assert.AreEqual("http://www.bailii.org/databases.html#ie", link.Address, "link target");
link = sh.GetHyperlink(A4) as XSSFHyperlink;
Assert.AreEqual("address", "A4", link.CellRef);
Assert.AreEqual(HyperlinkType.Url, link.Type, "link type");
Assert.AreEqual("https://en.wikipedia.org/wiki/Apache_POI#See_also", link.Address, "link target");
link = sh.GetHyperlink(A7) as XSSFHyperlink;
Assert.AreEqual("address", "A7", link.CellRef);
Assert.AreEqual(HyperlinkType.Document, link.Type, "link type");
Assert.AreEqual("Sheet1", link.Address, "link target");
wb.Close();
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
////cell style for hyperlinks
////by default hyperlinks are blue and underlined
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
hlink_font.Color = HSSFColor.BLUE.index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = workbook.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.URL);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new XSSFHyperlink(HyperlinkType.FILE);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new XSSFHyperlink(HyperlinkType.EMAIL);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a place in this workbook
//Create a target sheet and cell
ISheet sheet2 = workbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new XSSFHyperlink(HyperlinkType.DOCUMENT);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void Test53734()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53734.xlsx");
XSSFHyperlink link = wb.GetSheetAt(0).GetRow(0).GetCell(0).Hyperlink as XSSFHyperlink;
Assert.AreEqual("javascript:///", link.Address);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
link = wb.GetSheetAt(0).GetRow(0).GetCell(0).Hyperlink as XSSFHyperlink;
Assert.AreEqual("javascript:///", link.Address);
}
public void Test53282()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53282.xlsx");
XSSFHyperlink link = wb.GetSheetAt(0).GetRow(0).GetCell(14).Hyperlink as XSSFHyperlink;
Assert.AreEqual("mailto:[email protected]%C2%A0", link.Address);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
link = wb.GetSheetAt(0).GetRow(0).GetCell(14).Hyperlink as XSSFHyperlink;
Assert.AreEqual("mailto:[email protected]%C2%A0", link.Address);
}
public void Test53282()
{
//since limitation in .NET Uri class, it's impossible to accept uri like mailto:[email protected]%C2%A0
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53282.xlsx");
XSSFHyperlink link = wb.GetSheetAt(0).GetRow(0).GetCell(14).Hyperlink as XSSFHyperlink;
Assert.AreEqual("mailto:[email protected]%C2%A0", link.Address);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
link = wb.GetSheetAt(0).GetRow(0).GetCell(14).Hyperlink as XSSFHyperlink;
Assert.AreEqual("mailto:[email protected]%C2%A0", link.Address);
}
public void TestCopyHSSFHyperlink()
{
HSSFHyperlink hlink = new HSSFHyperlink(HyperlinkType.Url);
hlink.Address = ("http://poi.apache.org/");
hlink.FirstColumn = (3);
hlink.FirstRow = (2);
hlink.LastColumn = (5);
hlink.LastRow = (6);
hlink.Label = ("label");
XSSFHyperlink xlink = new XSSFHyperlink(hlink);
Assert.AreEqual("http://poi.apache.org/", xlink.Address);
Assert.AreEqual(new CellReference(2, 3), new CellReference(xlink.CellRef));
// Are HSSFHyperlink.label and XSSFHyperlink.tooltip the same? If so, perhaps one of these needs renamed for a consistent Hyperlink interface
// Assert.AreEqual("label", xlink.Tooltip);
}
/**
* Create a new XSSFHyperlink. This method is for Internal use only.
* XSSFHyperlinks can be created by XSSFCreationHelper.
*
* @param type - the type of hyperlink to create, see {@link Hyperlink}
*/
//FIXME: change to protected if/when SXSSFHyperlink class is created
public XSSFHyperlink(IHyperlink other)
{
if (other is XSSFHyperlink)
{
XSSFHyperlink xlink = (XSSFHyperlink)other;
_type = xlink.Type;
_location = xlink._location;
_externalRel = xlink._externalRel;
_ctHyperlink = xlink._ctHyperlink.Copy();
}
else
{
_type = other.Type;
_location = other.Address;
_externalRel = null;
_ctHyperlink = new CT_Hyperlink();
SetCellReference(new CellReference(other.FirstRow, other.FirstColumn));
}
}
public void Test52716()
{
XSSFWorkbook wb1 = XSSFTestDataSamples.OpenSampleWorkbook("52716.xlsx");
XSSFSheet sh1 = wb1.GetSheetAt(0) as XSSFSheet;
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
XSSFSheet sh2 = wb2.GetSheetAt(0) as XSSFSheet;
Assert.AreEqual(sh1.NumberOfComments, sh2.NumberOfComments);
XSSFHyperlink l1 = sh1.GetHyperlink(0, 1);
Assert.AreEqual(HyperlinkType.Document, l1.Type);
Assert.AreEqual("B1", l1.GetCellRef());
Assert.AreEqual("Sort on Titel", l1.Tooltip);
XSSFHyperlink l2 = sh2.GetHyperlink(0, 1);
Assert.AreEqual(l1.Tooltip, l2.Tooltip);
Assert.AreEqual(HyperlinkType.Document, l2.Type);
Assert.AreEqual("B1", l2.GetCellRef());
}
public void TestRemoveHyperlink()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell1 = row.CreateCell(1);
XSSFHyperlink link1 = new XSSFHyperlink(HyperlinkType.Url);
cell1.Hyperlink = (/*setter*/ link1);
Assert.IsNotNull(cell1.Hyperlink);
cell1.RemoveHyperlink();
Assert.IsNull(cell1.Hyperlink);
ICell cell2 = row.CreateCell(0);
XSSFHyperlink link2 = new XSSFHyperlink(HyperlinkType.Url);
cell2.Hyperlink = (/*setter*/ link2);
Assert.IsNotNull(cell2.Hyperlink);
cell2.Hyperlink = (/*setter*/ null);
Assert.IsNull(cell2.Hyperlink);
XSSFTestDataSamples.WriteOutAndReadBack(wb);
}
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());
}
}
}
/// <summary>
/// Copy cell value, formula and style, from srcCell per cell copy policy
/// If srcCell is null, clears the cell value and cell style per cell copy policy
///
/// This does not shift references in formulas. Use {@link XSSFRowShifter} to shift references in formulas.
/// </summary>
/// <param name="srcCell">The cell to take value, formula and style from</param>
/// <param name="policy">The policy for copying the information, see {@link CellCopyPolicy}</param>
/// <exception cref="ArgumentException">if copy cell style and srcCell is from a different workbook</exception>
public void CopyCellFrom(ICell srcCell, CellCopyPolicy policy)
{
// Copy cell value (cell type is updated implicitly)
if (policy.IsCopyCellValue)
{
if (srcCell != null)
{
CellType copyCellType = srcCell.CellType;
if (copyCellType == CellType.Formula && !policy.IsCopyCellFormula)
{
// Copy formula result as value
// FIXME: Cached value may be stale
copyCellType = srcCell.CachedFormulaResultType;
}
switch (copyCellType)
{
case CellType.Boolean:
SetCellValue(srcCell.BooleanCellValue);
break;
case CellType.Error:
SetCellErrorValue(srcCell.ErrorCellValue);
break;
case CellType.Formula:
SetCellFormula(srcCell.CellFormula);
break;
case CellType.Numeric:
// DataFormat is not copied unless policy.isCopyCellStyle is true
if (DateUtil.IsCellDateFormatted(srcCell))
{
SetCellValue(srcCell.DateCellValue);
}
else
{
SetCellValue(srcCell.NumericCellValue);
}
break;
case CellType.String:
SetCellValue(srcCell.StringCellValue);
break;
case CellType.Blank:
SetBlank();
break;
default:
throw new ArgumentException("Invalid cell type " + srcCell.CellType);
}
}
else
{ //srcCell is null
SetBlank();
}
}
// Copy CellStyle
if (policy.IsCopyCellStyle)
{
if (srcCell != null)
{
CellStyle = (srcCell.CellStyle);
}
else
{
// clear cell style
CellStyle = (null);
}
}
if (policy.IsMergeHyperlink)
{
// if srcCell doesn't have a hyperlink and destCell has a hyperlink, don't clear destCell's hyperlink
IHyperlink srcHyperlink = srcCell.Hyperlink;
if (srcHyperlink != null)
{
Hyperlink = new XSSFHyperlink(srcHyperlink);
}
}
else if (policy.IsCopyHyperlink)
{
// overwrite the hyperlink at dest cell with srcCell's hyperlink
// if srcCell doesn't have a hyperlink, clear the hyperlink (if one exists) at destCell
IHyperlink srcHyperlink = srcCell.Hyperlink;
if (srcHyperlink == null)
{
Hyperlink = (null);
}
else
{
Hyperlink = new XSSFHyperlink(srcHyperlink);
}
}
}
/**
* Register a hyperlink in the collection of hyperlinks on this sheet
*
* @param hyperlink the link to add
*/
public void AddHyperlink(XSSFHyperlink hyperlink)
{
hyperlinks.Add(hyperlink);
}
public void TestRemoveHyperlink()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell1 = row.CreateCell(1);
XSSFHyperlink link1 = new XSSFHyperlink(HyperlinkType.Url);
cell1.Hyperlink = (/*setter*/link1);
Assert.IsNotNull(cell1.Hyperlink);
cell1.RemoveHyperlink();
Assert.IsNull(cell1.Hyperlink);
ICell cell2 = row.CreateCell(0);
XSSFHyperlink link2 = new XSSFHyperlink(HyperlinkType.Url);
cell2.Hyperlink = (/*setter*/link2);
Assert.IsNotNull(cell2.Hyperlink);
cell2.Hyperlink = (/*setter*/null);
Assert.IsNull(cell2.Hyperlink);
XSSFTestDataSamples.WriteOutAndReadBack(wb);
}
