/// <summary>
/// Save the styl for worksheet headers.
/// </summary>
/// <param name="cellLocation">Cell location.</param>
/// <param name="spreadSheet">Spreadsheet to change.</param>
/// <param name="workSheetPart">Worksheet to change.</param>
private static void SeatHeaderStyle(string cellLocation, SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart)
{
Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
Cell cell = workSheetPart.Worksheet.Descendants <Cell>().Where(c => c.CellReference == cellLocation).FirstOrDefault();
if (cell == null)
{
throw new ArgumentNullException("Cell not found");
}
cell.SetAttribute(new OpenXmlAttribute("", "s", "", "1"));
OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s", "");
CellFormats cellFormats = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
// pick tthe first cell format.
CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(0);
CellFormat cf = new CellFormat(cellFormat.OuterXml);
cf.FontId = styleSheet.Fonts.Count;
cf.FillId = styleSheet.Fills.Count;
cellFormats.AppendChild(cf);
int a = (int)styleSheet.CellFormats.Count.Value;
cell.SetAttribute(cellStyleAttribute);
cell.StyleIndex = styleSheet.CellFormats.Count;
workSheetPart.Worksheet.Save();
}
string GetCellValue(WorkbookPart workbookPart, CellFormats cellFormats, Cell c)
{
string cellValue = c.CellValue.InnerText;
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));
cellValue = ssi.Text.Text;
}
else if (c.StyleIndex != null)
{
var styleIndex = (int)c.StyleIndex.Value;
var cellFormat = (CellFormat)cellFormats.ElementAt(styleIndex);
cellValue = GetFormattedValue(cellFormat.NumberFormatId, c);
}
return(cellValue);
}
private static void SetHeaderStyle(SpreadsheetDocument spreadSheet, Cell cell)
{
Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
cell.SetAttribute(new OpenXmlAttribute("", "s", "", "1"));
OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s", "");
CellFormats cellFormats = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
// pick the first cell format.
CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(0);
CellFormat cf = new CellFormat(cellFormat.OuterXml);
cf.FontId = styleSheet.Fonts.Count;
cf.FillId = styleSheet.Fills.Count;
cellFormats.AppendChild(cf);
cell.SetAttribute(cellStyleAttribute);
cell.StyleIndex = styleSheet.CellFormats.Count;
}
/// <summary>
/// Gets the value of a cell, automatically referencing shared strings and formatting numbers
/// </summary>
/// <param name="c">The cell to get the value of</param>
/// <returns>The value of the cell formatted</returns>
private string GetCellValue(Cell c)
{
SharedStringTable sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
string cellText = c.CellValue?.Text;
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
return(sharedStrings.ElementAt(int.Parse(cellText)).InnerText);
}
if (!string.IsNullOrEmpty(cellText) && c.StyleIndex != null)
{
try
{
// Try to find cell formatting information if it's a number.
CellFormats cellFormats = document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
CellFormat cellFormat = (CellFormat)cellFormats.ElementAt((int)c.StyleIndex.Value);
NumberingFormats numberingFormats = document.WorkbookPart.WorkbookStylesPart.Stylesheet.NumberingFormats;
NumberingFormat numberFormat = numberingFormats.Elements <NumberingFormat>()
.Where(f => f.NumberFormatId.Value == cellFormat.NumberFormatId.Value)
.FirstOrDefault();
// Try to parse to double with format.
if (numberFormat != default(NumberingFormat) && double.TryParse(cellText, out double conv))
{
return(conv.ToString(numberFormat.FormatCode));
}
}
catch (Exception ex)
{
Logger.Warn(ex, "Error retrieving cell format information");
}
}
return(cellText);
}
/// <summary>
/// Обработка документа Excel.
/// </summary>
/// <param name="document">Документ Excel.</param>
public void Process(ref SpreadsheetDocument document, IExportParams parameters = null, NameValueCollection queryParams = null)
{
var workbookPart = document.WorkbookPart;
if (workbookPart != null)
{
WorkbookStylesPart styles = workbookPart.WorkbookStylesPart;
if (styles != null)
{
Stylesheet stylesheet = styles.Stylesheet;
if (stylesheet != null)
{
CellFormats cellformats = stylesheet.CellFormats;
Fonts fonts = stylesheet.Fonts;
UInt32 fontIndex = fonts.Count;
UInt32 formatIndex = cellformats.Count;
var worksheetPart = workbookPart.GetPartsOfType <WorksheetPart>().First();
if (worksheetPart != null)
{
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
if (sheetData != null)
{
var headerRow = sheetData.GetFirstChild <Row>();
if (headerRow != null)
{
Cell cell = headerRow.GetFirstChild <Cell>();
CellFormat f = (CellFormat)cellformats.ElementAt((int)cell.StyleIndex.Value);
var font = (Font)fonts.ElementAt((int)f.FontId.Value);
var newfont = (Font)font.Clone();
newfont.Bold = new Bold();
fonts.Append(newfont);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
CellFormat newformat = (CellFormat)f.Clone();
newformat.FontId = fontIndex;
Fill fill = new Fill();
PatternFill patternFill = new PatternFill()
{
PatternType = PatternValues.Solid
};
patternFill.ForegroundColor = new ForegroundColor()
{
Rgb = "FFD0D0D0"
};
patternFill.BackgroundColor = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
fill.PatternFill = patternFill;
Fills fills = new Fills();
fills.Append(new Fill()
{
PatternFill = new PatternFill()
});
fills.Append(new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.Gray125
}
});
fills.Append(fill);
stylesheet.Fills = fills;
stylesheet.Fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
newformat.FillId = stylesheet.Fills.Count - 1;
cellformats.Append(newformat);
cellformats.Count = UInt32Value.FromUInt32((uint)cellformats.ChildElements.Count);
stylesheet.Save();
foreach (OpenXmlElement el in headerRow.ChildElements)
{
(el as Cell).StyleIndex = formatIndex;
}
}
}
}
}
}
}
}
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);
}
/// <summary>
/// Gets the content of the spread sheet cell based on the data type of the cell.
/// </summary>
/// <param name="excelCell">The spread sheet cell object.</param>
/// <param name="sharedStringTable">The table containing the shared strings.</param>
/// <param name="cellFormats">The cell formats in use.</param>
/// <param name="numberingFormats">The numbering formats in use.</param>
/// <returns>The content of the spread sheet cell based on the data type of the cell.</returns>
private string GetSpreadsheetCellValue(Cell excelCell, IEnumerable <OpenXmlElement> sharedStringTable, CellFormats cellFormats, IEnumerable <NumberingFormat> numberingFormats)
{
string retVal = string.Empty;
retVal = excelCell.InnerText;
if (excelCell.DataType != null)
{
switch (excelCell.DataType.Value)
{
case CellValues.SharedString:
if (sharedStringTable != null)
{
retVal = sharedStringTable.ElementAt(int.Parse(retVal)).InnerText;
}
break;
case CellValues.Boolean:
switch (retVal)
{
case "0":
retVal = "FALSE";
break;
default:
retVal = "TRUE";
break;
}
break;
}
}
else
{
if (excelCell.StyleIndex != null)
{
UInt32Value styleIndex = excelCell.StyleIndex;
CellFormat cellFormat = (CellFormat)cellFormats.ElementAt((int)styleIndex.Value);
double tempDouble;
// Ecma Office Open XML Part 1 - Fundamentals And Markup Language Reference - Section: 18.8.30 numFmt (Number Format)
if (cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.Value < 163 && retVal != "0")
{
if (double.TryParse(retVal.ToString(), out tempDouble))
{
if (tempDouble >= MinExcelDate && tempDouble <= MaxExcelDate)
{
try
{
retVal = DateTime.FromOADate(double.Parse(retVal.ToString())).ToString();
}
catch (ArgumentException ae)
{
// Throw only if it is not an OleAut date error.
if (!ae.Message.Contains("Not a legal OleAut date"))
{
throw;
}
}
}
}
}
else if (numberingFormats != null && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.Value > 163)
{
NumberingFormat cellFormatUsed = numberingFormats.FirstOrDefault(nf => nf.NumberFormatId == cellFormat.NumberFormatId.Value);
if (cellFormatUsed.FormatCode.Value.ToUpper().Contains("D") || cellFormatUsed.FormatCode.Value.ToUpper().Contains("M") || cellFormatUsed.FormatCode.Value.ToUpper().Contains("Y"))
{
if (double.TryParse(retVal.ToString(), out tempDouble))
{
retVal = DateTime.FromOADate(double.Parse(retVal.ToString())).ToString();
}
}
}
}
}
return(retVal);
}
public static string GetCellValue(Cell c, SharedStringTable stringTable, CellFormats formats, Field colmn)
{
string sval = null;
int styleIndex;
CellFormat cellFormat = null;
if (c == null) return null;
if (c.StyleIndex != null)
{
styleIndex = (int)c.StyleIndex.Value;
cellFormat = (CellFormat)formats.ElementAt(styleIndex);
}
if (c == null || c.CellValue == null)
return sval;
if (c.DataType != null && c.CellFormula == null)
{
switch (c.DataType.Value)
{
case CellValues.SharedString:
sval = stringTable.ElementAt(int.Parse(c.InnerText)).InnerText;
break;
default:
sval = "not a string";
break;
}
}
else
sval = string.IsNullOrWhiteSpace(c.CellValue.InnerText) ? null : c.CellValue.InnerText;
if (colmn != null)
{
switch (colmn.DataFormat)
{
case DataFormatType.Date:
var pdt = c.CellValue.InnerText;
pdt = FixDate(pdt);
if (cellFormat != null && cellFormat.NumberFormatId != null && c.DataType == null)
{
var d = DateTime.FromOADate(double.Parse(pdt));
sval = d.ToString("MM/dd/yyyy");
}
else
{
DateTime tv;
if(sval != null && DateTime.TryParse(sval, out tv))
{
if (colmn.DataFormat == DataFormatType.Date)
sval = tv.ToShortDateString();
else
sval = tv.ToString();
}
else
sval = null;
}
break;
case DataFormatType.DateTime:
var dt = DateTime.FromOADate(double.Parse(c.CellValue.InnerText));
sval = dt.ToString();
break;
case DataFormatType.DateMixed:
double aoDate;
if (double.TryParse(c.CellValue.InnerText, out aoDate))
{
var aodt = DateTime.FromOADate(aoDate);
if ((DateTime.Now - aodt).Days < 36500)
sval = aodt.ToString("MM/dd/yyyy");
}
break;
}
}
if (sval != null && colmn != null && colmn.postProcRegex != null)
foreach(var d in colmn.postProcRegex)
sval = Regex.Replace(sval, d.Item1, d.Item2);
if (sval != null)
{
//if (sval.Length > 4000)
//{
// Log.New.Msg($"truncating to length 4000 cell: {c.CellReference.InnerText} contents: {sval}");
// sval = sval.Substring(0, 4000);
//}
sval = sval.Replace("\t", "");
sval.Trim();
}
return sval;
}