/**
* @return whether the objects have the same HyperlinkRecord
*/
public override bool Equals(Object other)
{
if (this == other)
{
return(true);
}
if (!(other is HSSFHyperlink))
{
return(false);
}
HSSFHyperlink otherLink = (HSSFHyperlink)other;
return(record == otherLink.record);
}
public HSSFHyperlink(IHyperlink other)
{
if (other is HSSFHyperlink)
{
HSSFHyperlink hlink = (HSSFHyperlink)other;
record = hlink.record.Clone() as HyperlinkRecord;
link_type = getType(record);
}
else
{
link_type = other.Type;
record = new HyperlinkRecord();
FirstRow = (other.FirstRow);
FirstColumn = (other.FirstColumn);
LastRow = (other.LastRow);
LastColumn = (other.LastColumn);
}
}
public void TestCreate()
{
HSSFWorkbook wb = new HSSFWorkbook();
ICell cell;
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
IHyperlink link = new HSSFHyperlink(HyperlinkType.URL);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = link;
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new HSSFHyperlink(HyperlinkType.FILE);
link.Address = ("link1.xls");
cell.Hyperlink = link;
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new HSSFHyperlink(HyperlinkType.EMAIL);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = link;
//link to a place in this workbook
//Create a target sheet and cell
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.TextMark = ("'Target Sheet'!A1");
cell.Hyperlink = link;
//serialize and read again
MemoryStream out1 = new MemoryStream();
wb.Write(out1);
wb = new HSSFWorkbook(new MemoryStream(out1.ToArray()));
sheet = wb.GetSheet("Hyperlinks");
cell = sheet.GetRow(0).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("http://poi.apache.org/", link.Address);
cell = sheet.GetRow(1).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("link1.xls", link.Address);
cell = sheet.GetRow(2).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("mailto:[email protected]?subject=Hyperlinks", link.Address);
cell = sheet.GetRow(3).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
}
public void TestShiftRows()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("46445.xls");
NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(0);
//verify existing hyperlink in A3
ICell cell1 = sheet.GetRow(2).GetCell(0);
IHyperlink link1 = cell1.Hyperlink;
Assert.IsNotNull(link1);
Assert.AreEqual(2, link1.FirstRow);
Assert.AreEqual(2, link1.LastRow);
//assign a hyperlink to A4
HSSFHyperlink link2 = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link2.Address=("Sheet2!A2");
ICell cell2 = sheet.GetRow(3).GetCell(0);
cell2.Hyperlink=(link2);
Assert.AreEqual(3, link2.FirstRow);
Assert.AreEqual(3, link2.LastRow);
//move the 3rd row two rows down
sheet.ShiftRows(sheet.FirstRowNum, sheet.LastRowNum, 2);
//cells A3 and A4 don't contain hyperlinks anymore
Assert.IsNull(sheet.GetRow(2).GetCell(0).Hyperlink);
Assert.IsNull(sheet.GetRow(3).GetCell(0).Hyperlink);
//the first hypelink now belongs to A5
IHyperlink link1_shifted = sheet.GetRow(2 + 2).GetCell(0).Hyperlink;
Assert.IsNotNull(link1_shifted);
Assert.AreEqual(4, link1_shifted.FirstRow);
Assert.AreEqual(4, link1_shifted.LastRow);
//the second hypelink now belongs to A6
IHyperlink link2_shifted = sheet.GetRow(3 + 2).GetCell(0).Hyperlink;
Assert.IsNotNull(link2_shifted);
Assert.AreEqual(5, link2_shifted.FirstRow);
Assert.AreEqual(5, link2_shifted.LastRow);
}
public void TestCreateDocumentLink()
{
HSSFWorkbook wb = new HSSFWorkbook();
//link to a place in this workbook
IHyperlink link;
ICell cell;
NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Hyperlinks");
//create a target sheet and cell
NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target Cell");
//cell A1 has a link to 'Target Sheet-1'!A1
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.TextMark=("'Target Sheet'!A1");
cell.Hyperlink=(link);
//cell B1 has a link to cell A1 on the same sheet
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.Address=("'Hyperlinks'!A1");
cell.Hyperlink=(link);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sheet = wb.GetSheet("Hyperlinks");
cell = sheet.GetRow(0).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Target Sheet'!A1", link.TextMark);
Assert.AreEqual("'Target Sheet'!A1", link.Address);
cell = sheet.GetRow(1).GetCell(0);
link = cell.Hyperlink;
Assert.IsNotNull(link);
Assert.AreEqual("'Hyperlinks'!A1", link.TextMark);
Assert.AreEqual("'Hyperlinks'!A1", link.Address);
}
public void TestRemoveHyperlink()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell1 = row.CreateCell(1) as HSSFCell;
HSSFHyperlink link1 = new HSSFHyperlink(HyperlinkType.Url);
Assert.IsNotNull(link1);
cell1.RemoveHyperlink();
Assert.IsNull(cell1.Hyperlink);
HSSFCell cell2 = row.CreateCell(0) as HSSFCell;
HSSFHyperlink link2 = new HSSFHyperlink(HyperlinkType.Url);
Assert.IsNotNull(link2);
cell2.Hyperlink = (/*setter*/null);
Assert.IsNull(cell2.Hyperlink);
HSSFTestDataSamples.WriteOutAndReadBack(wb);
}
static void Main(string[] args)
{
InitializeWorkbook();
////cell style for hyperlinks
////by default hyperlinks are blue and underlined
ICellStyle hlink_style = hssfworkbook.CreateCellStyle();
IFont hlink_font = hssfworkbook.CreateFont();
hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
hlink_font.Color = HSSFColor.BLUE.index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = hssfworkbook.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
HSSFHyperlink link = new HSSFHyperlink(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 HSSFHyperlink(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 HSSFHyperlink(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 = hssfworkbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new HSSFHyperlink(HyperlinkType.DOCUMENT);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
WriteToFile();
}
