/// <summary>
/// Add a single string to shared strings table.
/// Shared string table is created if it doesn't exist.
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="stringItem">string to add</param>
/// <param name="save">Save the shared string table</param>
/// <returns></returns>
public static bool AddSharedString(SpreadsheetDocument spreadsheet, string stringItem, bool save = true)
{
SharedStringTable sharedStringTable = spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable;
if (!sharedStringTable.Any(item => item.InnerText == stringItem))
{
sharedStringTable.AppendChild(
new SharedStringItem(
new Text(stringItem)));
// Save the changes
if (save)
{
sharedStringTable.Save();
}
}
return(true);
}
/// <summary>
/// 获取单位格的值
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="workbookPart"></param>
/// <param name="type">1 不去空格 2 前后空格 3 所有空格 </param>
/// <returns></returns>
public static string GetCellValue(Cell cell, WorkbookPart workbookPart, int type = 2)
{
//合并单元格不做处理
if (cell.CellValue == null)
{
return(string.Empty);
}
string cellInnerText = cell.CellValue.InnerXml;
//纯字符串
if (cell.DataType != null && (cell.DataType.Value == CellValues.SharedString || cell.DataType.Value == CellValues.String || cell.DataType.Value == CellValues.Number))
{
//获取spreadsheetDocument中共享的数据
SharedStringTable stringTable = workbookPart.SharedStringTablePart.SharedStringTable;
//如果共享字符串表丢失，则说明出了问题。
if (!stringTable.Any())
{
return(string.Empty);
}
string text = stringTable.ElementAt(int.Parse(cellInnerText)).InnerText;
if (type == 2)
{
return(text.Trim());
}
else if (type == 3)
{
return(text.Replace(" ", ""));
}
else
{
return(text);
}
}
//bool类型
else if (cell.DataType != null && cell.DataType.Value == CellValues.Boolean)
{
return((cellInnerText != "0").ToString().ToUpper());
}
//数字格式代码（numFmtId）小于164是内置的：https://www.it1352.com/736329.html
else
{
//为空为数值
if (cell.StyleIndex == null)
{
return(cellInnerText);
}
Stylesheet styleSheet = workbookPart.WorkbookStylesPart.Stylesheet;
CellFormat cellFormat = (CellFormat)styleSheet.CellFormats.ChildElements[(int)cell.StyleIndex.Value];
uint formatId = cellFormat.NumberFormatId.Value;
double doubleTime; //OLE 自动化日期值
DateTime dateTime; //yyyy/MM/dd HH:mm:ss
switch (formatId)
{
case 0: //常规
return(cellInnerText);
case 9: //百分比【0%】
case 10: //百分比【0.00%】
case 11: //科学计数【1.00E+02】
case 12: //分数【1/2】
return(cellInnerText);
case 14:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd"));
//case 15:
//case 16:
case 17:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM"));
//case 18:
//case 19:
case 20:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("H:mm"));
case 21:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("HH:mm:ss"));
case 22:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd HH:mm"));
//case 45:
//case 46:
case 47:
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd"));
case 58: //【中国】11月11日
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("MM/dd"));
case 176: //【中国】2020年11月11日
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("yyyy/MM/dd"));
case 177: //【中国】11:22:00
doubleTime = double.Parse(cellInnerText);
dateTime = DateTime.FromOADate(doubleTime);
return(dateTime.ToString("HH:mm:ss"));
default:
return(cellInnerText);
}
}
}
