我正在开发 OPenXML SDK 来处理 Excel。目前,我面临一个问题,如何识别单元格的数据类型,日期时间或数字。因为,如果单元格的类型是日期,我们需要再次将双精度值转换为日期时间。
根据我从 OpenXML SDK 帮助文件中找到的信息,我编写了以下代码来回答您的问题
public class ExcelEngine : IExcelEngine
{
private readonly SpreadsheetDocument _wb;
private WorkbookPart _wbp;
private SharedStringTablePart _sstp;
// Used to cache the lookup of worksheet parts
private Dictionary<string, WorksheetPart> _wsParts = new Dictionary<string, WorksheetPart>();
#region Constructors
public ExcelEngine(Stream stream)
{
Contracts.IsNotNull(stream);
_wb = SpreadsheetDocument.Open(stream, false);
Initialise();
}
public ExcelEngine(string fileName)
{
Contracts.IsNullOrWhiteSpace(fileName);
_wb = SpreadsheetDocument.Open(fileName, false);
Initialise();
}
#endregion
#region IExcelEngine
/// <summary>
/// Get the list of sheet names from the spreadsheet
/// </summary>
/// <returns></returns>
public IList<string> GetSheetNames()
{
return _wbp.Workbook
.Descendants<Sheet>()
.Select(s => s.Name.Value)
.ToList<String>();
}
/// <summary>
/// Given a sheet name and a cell reference, return the contents of the cell
/// </summary>
/// <param name="sheetName"></param>
/// <param name="addressName"></param>
/// <returns></returns>
public string GetCellValue(string sheetName, string addressName)
{
return GetCellValueLocal(sheetName, addressName);
}
/// <summary>
/// Given a sheet name and a cell reference, return the contents of the cell as a boolean value
/// </summary>
/// <param name="sheetName"></param>
/// <param name="addressName"></param>
/// <returns></returns>
public bool GetCellBool(string sheetName, string addressName)
{
var value = GetCellValueLocal(sheetName, addressName);
bool result;
bool.TryParse(value, out result);
return result;
}
#endregion
#region Private Methods
private void Initialise()
{
_wbp = _wb.WorkbookPart;
_sstp = _wbp.GetPartsOfType<SharedStringTablePart>().First();
}
private string GetCellValueLocal(string sheetName, string addressName)
{
string value = null;
WorksheetPart wsPart = GetWorkSheetPart(sheetName);
// Use its Worksheet property to get a reference to the cell
// whose address matches the address you supplied.
Cell cell = wsPart.Worksheet.Descendants<Cell>().
Where(c => c.CellReference == addressName).FirstOrDefault();
if (cell != null)
{
value = cell.InnerText;
if (cell.DataType != null)
switch (cell.DataType.Value)
{
case CellValues.SharedString:
int ssid = int.Parse(cell.CellValue.Text);
value = _sstp.SharedStringTable.ElementAt(ssid).InnerText;
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
case CellValues.Date:
break;
case CellValues.String:
break;
}
Debug.WriteLine($"Cell {cell.CellReference}: '{value}'");
}
return value;
}
private WorksheetPart GetWorkSheetPart(string sheetName)
{
// Does it exist in the cache? If not, load it
if (!_wsParts.ContainsKey(sheetName))
{
// Find the sheet with the supplied name, and then use that
// Sheet object to retrieve a reference to the first worksheet.
Sheet theSheet = _wbp.Workbook.Descendants<Sheet>()
.Where(s => s.Name == sheetName)
.FirstOrDefault();
// If not sheet throw an exception
if (theSheet == null)
throw new ArgumentException($"Sheet {sheetName} not found in workbook");
// Retrieve a reference to the worksheet part.
_wsParts.Add(sheetName, (WorksheetPart) (_wbp.GetPartById(theSheet.Id)));
}
return _wsParts[sheetName];
}
#endregion
public void Dispose()
{
_wb.Close();
_wb.Dispose();
}
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)