private static string GetValue(SpreadsheetDocument spreadsheetdocument, Cell cell, NumberingFormats formats = null)
{
// Get value in Cell
var sharedString = spreadsheetdocument.WorkbookPart.SharedStringTablePart;
if (cell.CellValue == null)
{
return(string.Empty);
}
var cellValue = cell.CellValue.InnerText;
// The condition that the Cell DataType is SharedString
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return(sharedString.SharedStringTable.ChildElements[int.Parse(cellValue)].InnerText);
}
if (cell.StyleIndex != null)
{
CellFormat cf = spreadsheetdocument.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[int.Parse(cell.StyleIndex.InnerText)] as CellFormat;
if ((cf.NumberFormatId >= 14 && cf.NumberFormatId <= 22) ||
(cf.NumberFormatId >= 165 && cf.NumberFormatId <= 180) ||
cf.NumberFormatId == 278 || cf.NumberFormatId == 185 || cf.NumberFormatId == 196 ||
cf.NumberFormatId == 217 || cf.NumberFormatId == 326 || cf.NumberFormatId == 323)
{
return(DateTime.FromOADate(Convert.ToDouble(cell.CellValue.Text)).ToShortDateString());
}
if (formats != null)
{
var numberingFormat = formats.Cast <NumberingFormat>()
.SingleOrDefault(f => f.NumberFormatId.Value == cf.NumberFormatId);
if (numberingFormat != null && numberingFormat.FormatCode.HasValue && IsDateTimeType(numberingFormat.FormatCode.Value))
{
return(DateTime.FromOADate(Convert.ToDouble(cell.CellValue.Text)).ToShortDateString());
}
}
}
return(cellValue);
}
private string EstraiValoreCella(Cell cell, SharedStringTable sharedStringTable, CellFormats cellFormats, NumberingFormats numberingFormats)
{
CellValue cellValue = cell.CellValue;
if (cellValue != null)
{
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
return(sharedStringTable.ElementAt(Int32.Parse(cell.InnerText)).InnerText);
case CellValues.Date:
double oaDateAsDouble;
if (double.TryParse(cell.InnerText, out oaDateAsDouble)) //this line is Culture dependent!
{
DateTime dateTime = DateTime.FromOADate(oaDateAsDouble);
return(dateTime.ToShortDateString());
}
return(string.Empty);
default:
return(cell.InnerText);
}
}
else
{
if (cell.StyleIndex != null)
{
var cellFormat = (CellFormat)cellFormats.ElementAt((int)cell.StyleIndex.Value);
if (cellFormat.NumberFormatId != null)
{
var numberFormatId = cellFormat.NumberFormatId.Value;
var numberingFormat = numberingFormats.Cast <NumberingFormat>()
.SingleOrDefault(f => f.NumberFormatId.Value == numberFormatId);
// Here's yer string! Example: $#,##0.00_);[Red]($#,##0.00)
if (numberingFormat != null && (numberingFormat.FormatCode.Value.Contains("yyyy-mm-dd") || numberingFormat.FormatCode.Value.Contains("yyyy\\-mm\\-dd")))
{
string formatString = numberingFormat.FormatCode.Value;
double oaDateAsDouble;
if (double.TryParse(cell.InnerText, out oaDateAsDouble)) //this line is Culture dependent!
{
DateTime dateTime = DateTime.FromOADate(oaDateAsDouble);
return(dateTime.ToShortDateString());
}
else
{
return(string.Empty);
}
}
else
{
return(cell.InnerText);
}
}
}
else
{
return(cell.InnerText);
}
}
}
return(string.Empty);
}