private Dictionary <int, XlsxRow> GetXlsxRows(Worksheet sheet)
{
var rows = new Dictionary <int, XlsxRow>();
// The order matters!
foreach (var cell in sheet.Cells.OrderBy(c => c.Key.Row).ThenBy(c => c.Key.Column))
{
if (!rows.ContainsKey(cell.Key.Row))
{
rows[cell.Key.Row] = new XlsxRow {
RowIndex = cell.Key.Row + 1
};
}
var styleIndex = _styles.IndexOf(cell.Value.XlsxCellStyle) + 1;
var xc = new XlsxCell
{
StyleIndex = styleIndex,
Reference = cell.Key.ToString()
};
switch (cell.Value.CellType)
{
case CellType.Text:
xc.CellType = XlsxCellTypes.SharedString;
xc.Value = _sharedStrings.GetStringIndex((string)cell.Value.Value);
break;
case CellType.Number:
xc.CellType = XlsxCellTypes.Number;
xc.Value = ((Decimal)cell.Value.Value).ToString(System.Globalization.CultureInfo.InvariantCulture);
break;
case CellType.Date:
xc.CellType = XlsxCellTypes.Number;
if (cell.Value.Value != null)
#if NETSTANDARD1_3
{ xc.Value = TicksToOADate(((DateTime)cell.Value.Value).Ticks); }
#else
{ xc.Value = ((DateTime)cell.Value.Value).ToOADate(); }
#endif
break;
default:
throw new ArgumentException("Unknown Cell Type: " + cell.Value.CellType + " in cell " + cell.Key.ToString() + " of " + sheet.Name);
}
rows[cell.Key.Row].Cells.Add(xc);
}
return(rows);
}
private static Dictionary <int, XlsxRow> GetXlsxRows(Worksheet sheet, IList <XlsxCellStyle> styles,
SharedStrings sharedStrings)
{
var rows = new Dictionary <int, XlsxRow>();
if (!Enum.IsDefined(typeof(LargeNumberHandlingMode), sheet.LargeNumberHandlingMode))
{
throw new InvalidOperationException(
$"Invalid value for {nameof(Worksheet.LargeNumberHandlingMode)} in sheet {sheet.Name}: {sheet.LargeNumberHandlingMode}");
}
// The order matters!
foreach (var cell in sheet.Cells.OrderBy(c => c.Key.Row).ThenBy(c => c.Key.Column))
{
if (!rows.ContainsKey(cell.Key.Row))
{
rows[cell.Key.Row] = new XlsxRow {
RowIndex = cell.Key.Row + 1
};
}
var styleIndex = styles.IndexOf(cell.Value.XlsxCellStyle) + 1;
var xc = new XlsxCell
{
StyleIndex = styleIndex,
Reference = cell.Key.ToString()
};
switch (cell.Value.CellType)
{
case CellType.Text:
xc.CellType = XlsxCellTypes.SharedString;
xc.Value = sharedStrings.GetStringIndex((string)cell.Value.Value);
break;
case CellType.Formula:
xc.CellType = XlsxCellTypes.FormulaString;
xc.Value = (string)cell.Value.Value;
break;
case CellType.Number:
// Fun: Excel can't handle large numbers as numbers
// https://support.microsoft.com/en-us/help/2643223/long-numbers-are-displayed-incorrectly-in-excel
var numVal = Convert.ToDecimal(cell.Value.Value);
if (sheet.LargeNumberHandlingMode != LargeNumberHandlingMode.None &&
Cell.IsLargeNumber(numVal))
{
switch (sheet.LargeNumberHandlingMode)
{
case LargeNumberHandlingMode.StoreAsText:
xc.CellType = XlsxCellTypes.SharedString;
xc.Value = sharedStrings.GetStringIndex(
numVal.ToString(System.Globalization.CultureInfo.InvariantCulture));
break;
default:
throw new InvalidOperationException(
"Unhandled LargeNumberHandlingMode: " + sheet.LargeNumberHandlingMode);
}
}
else
{
xc.CellType = XlsxCellTypes.Number;
xc.Value = Convert.ToDecimal(cell.Value.Value)
.ToString(System.Globalization.CultureInfo.InvariantCulture);
}
break;
case CellType.Date:
xc.CellType = XlsxCellTypes.Number;
if (cell.Value.Value != null)
{
xc.Value = ((DateTime)cell.Value.Value).ToOleAutDate();
}
break;
default:
throw new ArgumentException("Unknown Cell Type: " + cell.Value.CellType + " in cell " +
cell.Key.ToString() + " of " + sheet.Name);
}
rows[cell.Key.Row].Cells.Add(xc);
}
return(rows);
}
private Dictionary<int, XlsxRow> GetXlsxRows(Worksheet sheet)
{
var rows = new Dictionary<int, XlsxRow>();
// The order matters!
foreach (var cell in sheet.Cells.OrderBy(c => c.Key.Row).ThenBy(c => c.Key.Column))
{
if (!rows.ContainsKey(cell.Key.Row))
{
rows[cell.Key.Row] = new XlsxRow {RowIndex = cell.Key.Row + 1};
}
var styleIndex = _styles.IndexOf(cell.Value.XlsxCellStyle) + 1;
var xc = new XlsxCell
{
StyleIndex = styleIndex,
Reference = cell.Key.ToString()
};
switch (cell.Value.CellType)
{
case CellType.Text:
xc.CellType = XlsxCellTypes.SharedString;
xc.Value = _sharedStrings.GetStringIndex((string)cell.Value.Value);
break;
case CellType.Number:
xc.CellType = XlsxCellTypes.Number;
xc.Value = ((Decimal)cell.Value.Value).ToString(System.Globalization.CultureInfo.InvariantCulture);
break;
default:
throw new ArgumentException("Unknown Cell Type: " + cell.Value.CellType + " in cell " + cell.Key.ToString() + " of " + sheet.Name);
}
rows[cell.Key.Row].Cells.Add(xc);
}
return rows;
}