/***********************************
* PRIVATE METHODS
************************************/
private static string GetTargetAddress(Cell targetCell)
{
return string.Format("{0}!{1}", targetCell.Worksheet.Name, targetCell.Address);
}
internal static string GetRangeAddress(Cell cell1, Cell cell2, bool fixedReference = false)
{
return BaseRange.GetLocalAddress(cell1.Address, cell2.Address, fixedReference);
}
private static void WriteHyperlinkToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, Cell cell)
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Hyperlink());
writer.WriteAttribute("ref", cell.Address);
writer.WriteAttribute("display", cell.Text);
writer.WriteAttribute("location", GetTargetAddress(cell.HyperlinkToCell));
writer.WriteEndElement(); // Hyperlink
}
private void UpdateHyperlink(Cell targetCell)
{
_hyperlinkToCell = targetCell;
if (targetCell != null)
{
Font.Color.Rgb = Color.GetRgb(0, 0, 255);
Font.Underline = true;
}
}
// Write
internal static void WriteCellToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, Cell cell)
{
if (!cell.IsUsed)
return;
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Cell());
string dataType = GetAttributeValueFromCellDataType(cell.CellDataType);
if (dataType != "") writer.WriteAttribute("t", dataType);
if (cell.StyleIndex > CellFormat.DefaultStyleIndex) writer.WriteAttribute("s", cell.StyleIndex);
writer.WriteAttribute("r", cell.Address);
if (cell.RawValue != null && cell.RawValue.ToString() != "")
{
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.CellValue());
switch (cell.CellDataType)
{
case CellDataType.Boolean:
writer.WriteText((bool)cell.RawValue);
break;
case CellDataType.SharedString:
int sharedStringIndex;
if (cell.SharedString != null)
sharedStringIndex = cell.Worksheet.Workbook.SharedStrings[cell.SharedString];
else
sharedStringIndex = cell.Worksheet.Workbook.SharedStrings[cell.RawValue.ToString()];
writer.WriteText(sharedStringIndex.ToString());
break;
default:
writer.WriteString(cell.RawValue.ToString());
break;
}
writer.WriteEndElement(); // CellValue
}
writer.WriteEndElement(); // Cell
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static Cell ReadCellFromReader(CustomOpenXmlReader reader, Row row)
{
CellDataType cellDataType = CellDataType.Number;
int styleIndex = CellFormat.DefaultStyleIndex;
string address = "";
object value = null;
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "t":
cellDataType = GetCellDataTypeFromAttributeValue(attribute.Value);
break;
case "s":
styleIndex = attribute.GetIntValue();
break;
case "r":
address = attribute.Value;
break;
}
}
value = GetCellValueFromReader(reader, cellDataType, row);
if (value == null)
cellDataType = CellDataType.Blank;
// Address doesn't technically need to be included. Needs handling, but keep simple for now.
int columnIndex = ExcelUtilities.GetColumnIndexConverter().GetColumnIndexFromCellAddress(address);
Cell cell = new Cell(row, row.Worksheet.Columns[columnIndex], value, cellDataType, styleIndex);
return cell;
}
/// <summary>
/// Creates a copy of the Cell and assigns to the given Row and Column.
/// </summary>
public Cell Clone(Row row, Column column)
{
Cell newCell = new Cell(row, column, Value, StyleIndex);
return newCell;
}