internal Cell(Row row, Column column, object value, int styleIndex)
: base(row.Worksheet, styleIndex)
{
Row = row;
Column = column;
UpdateCellValue(value);
}
/***********************************
* CONSTRUCTORS
************************************/
// internal for time being - until full styling is required.
internal Cell(Row row, Column column, object value, CellDataType cellDataType, int styleIndex)
: base(row.Worksheet, styleIndex)
{
// this constructor should be merged with the one below
Row = row;
Column = column;
CellDataType = cellDataType;
_value = value;
}
/***********************************
* PUBLIC METHODS
************************************/
/// <summary>
/// Creates a copy of the Row and assigns to the given Worksheet.
/// </summary>
public Row Clone(Worksheet worksheet)
{
Row newRow = new Row(worksheet, Index, StyleIndex, Height);
foreach (Cell cell in Cells)
{
Cell newCell = cell.Clone(newRow);
newRow.Cells[newCell.Column.Index] = newCell;
}
return newRow;
}
public Cell(Row row, Column column, object value)
: this(row, column, value, CellFormat.DefaultStyleIndex)
{
// take row or column format if they exist
// I can't find anything in the XML files to say which takes precedence if they both exist, so go with row
//if (row.RowFormat.CellFormatId > CellFormat.DefaultCellFormatId)
// CellFormat = row.RowFormat;
//else if (column.ColumnFormat.CellFormatId > CellFormat.DefaultCellFormatId)
// CellFormat = column.ColumnFormat;
//else
// CellFormat = row.Worksheet.Workbook.Styles.CellFormats[CellFormat.DefaultCellFormatId];
}
internal static string GetRangeAddress(Row row1, Row row2, bool fixedReference = false)
{
return BaseRange.GetLocalAddress(row1.Index, row2.Index, fixedReference);
}
// Write
internal static void WriteRowToWriter(CustomOpenXmlWriter<OpenXmlPackaging.WorksheetPart> writer, Row row)
{
if (row.Cells.FirstOrDefault(c => c.IsUsed) == null)
return;
writer.WriteOpenXmlElement(new OpenXmlSpreadsheet.Row());
writer.WriteAttribute("r", row.Index);
if (row.StyleIndex > CellFormat.DefaultStyleIndex) writer.WriteAttribute("s", row.StyleIndex);
if (row.Height > 0 && row.Height != row.Worksheet.Format.DefaultRowHeight) writer.WriteAttribute("ht", row.Height);
row.Cells.Action(c => Cell.WriteCellToWriter(writer, c));
writer.WriteEndElement(); // Row
}
/***********************************
* DAL METHODS
************************************/
// Read
internal static Row ReadRowFromReader(CustomOpenXmlReader reader, Worksheet worksheet)
{
Row row = new Row(worksheet);
foreach (CustomOpenXmlAttribute attribute in reader.Attributes)
{
switch (attribute.LocalName)
{
case "r":
row.Index = attribute.GetIntValue();
break;
case "s":
row.StyleIndex = attribute.GetIntValue();
break;
case "ht":
row.Height = attribute.GetDoubleValue();
break;
}
}
row.Cells = CellCollection.ReadCellsFromReader(reader, row);
return row;
}
private static object GetCellValueFromReader(CustomOpenXmlReader reader, CellDataType cellDataType, Row row)
{
object cellValue = null;
while (reader.ReadToEndElement<OpenXmlSpreadsheet.Cell>())
{
if (reader.IsStartElementOfType<OpenXmlSpreadsheet.CellValue>())
{
string rawValue = reader.GetText();
if (rawValue != "")
{
switch (cellDataType)
{
case CellDataType.Boolean:
cellValue = (rawValue == "1" ? true : false);
break;
case CellDataType.SharedString:
int sharedStringIndex = int.Parse(rawValue);
cellValue = row.Worksheet.Workbook.SharedStrings[sharedStringIndex];
break;
case CellDataType.Number:
double doubleValue = 0;
if (double.TryParse(rawValue, out doubleValue))
cellValue = doubleValue;
break;
case CellDataType.String:
cellValue = rawValue.ToString();
break;
default:
throw new ArgumentOutOfRangeException();
}
}
}
}
return cellValue;
}
public Cell(Row row, Column column)
: this(row, column, string.Empty)
{
}
/***********************************
* 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>
/// Copies and pastes the Cell to the given address. Overwrites existing target Cell.
/// </summary>
public Cell CopyTo(Row row, Column column)
{
if (row.Worksheet != column.Worksheet)
throw new Exception("Error in Cell.CopyTo(row, column). Row and column worksheets do not match");
Cell newCell = Clone(row, column);
row.Worksheet.Cells[row.Index, column.Index] = newCell;
return newCell;
}
/// <summary>
/// Creates a copy of the Cell and assigns to the given Row.
/// </summary>
public Cell Clone(Row row)
{
return Clone(row, row.Worksheet.Columns[Column.Index]);
}
/// <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;
}
