public class ExcelUntity
{ #region property
/// <summary>
/// excel文档（相当于excel程序）
/// </summary>
public SpreadsheetDocument spreadsheetDocument { get; set; } = null; /// <summary>
/// 工作本
/// </summary>
public WorkbookPart workbookpart { get; set; } = null; /// <summary>
/// sheet集合
/// </summary>
public Sheets sheets { get; set; } = null;
#endregion public SpreadsheetDocument Open(string filepath,bool isEdit)
{
spreadsheetDocument = SpreadsheetDocument.Open(filepath, isEdit);
return spreadsheetDocument;
} public SpreadsheetDocument Open(Stream stream, bool isEdit)
{
spreadsheetDocument = SpreadsheetDocument.Open(stream, isEdit);
return spreadsheetDocument;
} public void Close()
{
if (spreadsheetDocument != null)
spreadsheetDocument.Close();
}
} public static class ExtenOpenXML
{
#region Base /// <summary>
/// 获取Worksheet
/// </summary>
/// <param name="document">document对象</param>
/// <param name="sheetName">sheetName可空</param>
/// <returns>Worksheet对象</returns>
public static Worksheet GetWorksheet(this SpreadsheetDocument document, string sheetName = null)
{
var sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
var sheet = (sheetName == null
? sheets.FirstOrDefault()
: sheets.FirstOrDefault(s => s.Name == sheetName)) ?? sheets.FirstOrDefault(); var worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
return worksheetPart.Worksheet;
} /// <summary>
/// 获了共享字符的表格对象
/// </summary>
/// <param name="document">SpreadsheetDocument</param>
/// <returns>SharedStringTablePart对角</returns>
public static IEnumerable<SharedStringTablePart> GetSharedStringTable(this SpreadsheetDocument document)
{
var sharedStringTable = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>();
return sharedStringTable;
} /// <summary>
/// 获取第一个SheetData
/// </summary>
/// <param name="document">SpreadsheetDocument对象</param>
/// <param name="sheetName">sheetName可为空</param>
/// <returns>SheetData对象</returns>
public static SheetData GetFirstSheetData(this SpreadsheetDocument document, string sheetName = null)
{
return document.GetWorksheet(sheetName).GetFirstChild<SheetData>();
} /// <summary>
/// 获取第一个SheetData
/// </summary>
/// <param name="worksheet">Worksheet对象</param>
/// <returns>SheetData对象</returns>
public static SheetData GetFirstSheetData(this Worksheet worksheet)
{
return worksheet.GetFirstChild<SheetData>();
} /// <summary>
/// 获取WorkBook
/// </summary>
/// <param name="workBookPart"></param>
/// <returns></returns>
public static Workbook GetWorkbook(this WorkbookPart workBookPart)
{
return workBookPart.Workbook;
} /// <summary>
/// 修改单元格的内容.
/// </summary>
/// <param name="sheetData">
/// The sheet data.
/// </param>
/// <param name="cellName">
/// The cell name.
/// </param>
/// <param name="cellText">
/// The cell text.
/// </param>
public static void UpdateCellText(this SheetData sheetData, string cellName, string cellText)
{
var cell = sheetData.GetCell(cellName);
if (cell == null)
{
return;
}
cell.UpdateCellText(cellText); } /// <summary>
/// 修改单元格的文本
/// </summary>
/// <param name="cell">Cell对象</param>
/// <param name="cellText">文本字符串</param>
private static void UpdateCellText(this DocumentFormat.OpenXml.Spreadsheet.Cell cell, object cellText)
{
cell.DataType = GetCellDataType(cellText);
cell.CellValue = cell.CellValue ?? new CellValue();
cell.CellValue.Text = cellText.ToString();
} /// <summary>
/// The get cell data type.
/// </summary>
/// <param name="cellText">
/// The cell text.
/// </param>
/// <returns>
/// The <see cref="CellValues"/>.
/// </returns>
private static CellValues GetCellDataType(object cellText)
{
var type = cellText.GetType();
switch (type.Name)
{
case "Int32":
case "Decimal":
case "Double":
case "Int64":
return CellValues.Number;
case "String":
return CellValues.String;
//// case "DateTime":
//// return CellValues.Date;
default:
return CellValues.String;
}
} /// <summary>
/// 修改单元格内容（文本、样式）
/// </summary>
/// <param name="cell">
/// The cell.
/// </param>
/// <param name="cellText">
/// The cell text.
/// </param>
/// <param name="cellStyleIndex">
/// The cell style index.
/// </param>
private static void UpdateCell(this DocumentFormat.OpenXml.Spreadsheet.Cell cell, object cellText, uint cellStyleIndex)
{
cell.UpdateCellText(cellText);
cell.StyleIndex = cellStyleIndex;
} /// <summary>
/// 根据单元格名称获取行索引.
/// </summary>
/// <param name="cellName">
/// The cell name.
/// </param>
/// <returns>
/// The <see cref="uint"/>.
/// </returns>
private static uint GetRowIndex(string cellName)
{
var regex = new Regex(@"\d+");
var match = regex.Match(cellName);
return uint.Parse(match.Value);
} /// <summary>
/// 获取行
/// </summary>
/// <param name="sheetData">
/// The sheet data.
/// </param>
/// <param name="rowIndex">
/// The row index.
/// </param>
/// <returns>
/// The <see cref="Row"/>.
/// </returns>
private static DocumentFormat.OpenXml.Spreadsheet.Row GetRow(this SheetData sheetData, long rowIndex)
{
return sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
} /// <summary>
/// 获取单元格
/// </summary>
/// <param name="row">
/// The row.
/// </param>
/// <param name="cellName">
/// The cell name.
/// </param>
/// <returns>
/// The <see cref="Cell"/>.
/// </returns>
private static DocumentFormat.OpenXml.Spreadsheet.Cell GetCell(this DocumentFormat.OpenXml.Spreadsheet.Row row, string cellName)
{
return row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().FirstOrDefault(c => c.CellReference.Value == cellName);
} /// <summary>
/// 获取单元格
/// </summary>
/// <param name="sheetData">
/// The sheet data.
/// </param>
/// <param name="cellName">
/// The cell name.
/// </param>
/// <returns>
/// The <see cref="Cell"/>.
/// </returns>
private static DocumentFormat.OpenXml.Spreadsheet.Cell GetCell(this SheetData sheetData, string cellName)
{
return sheetData.Descendants<DocumentFormat.OpenXml.Spreadsheet.Cell>().FirstOrDefault(c => c.CellReference.Value == cellName);
} /// <summary>
/// 获取区域信息
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
public static DefinedNames GetDefinedNames(this Workbook workbook)
{
return workbook.DefinedNames;//区域名称集合
} /// <summary>
/// 取单元格的值
/// </summary>
/// <param name="RowData"></param>
/// <param name="columnCode"></param>
/// <param name="sharedStringTablePart"></param>
/// <returns></returns>
public static string GetCellValue(this Row RowData,string columnCode,SharedStringTablePart sharedStringTablePart)
{
string value = string.Empty;
try
{
Cell cell = RowData.GetCell(columnCode);//取单元格
if (cell != null)
value = GetCellValue(cell, sharedStringTablePart); value = string.IsNullOrEmpty(value) ? "" : value;
}
catch(Exception ex)
{
throw ex;
}
return value;
} public static string GetCellValue(this Cell cell, SharedStringTablePart sharedStringTablePart)
{
if (cell == null)
return string.Empty; if(cell.ChildElements.Count ==)
return string.Empty; var value = cell.CellValue.InnerText;
if (cell.DataType == null)
return value; switch (cell.DataType.Value)
{
case CellValues.SharedString:
if (sharedStringTablePart != null)
value = sharedStringTablePart.SharedStringTable.ElementAt(int.Parse(value)).InnerText; break;
case CellValues.Boolean:
value = value == "" ? "FALSE" : "TRUE";
break;
}
return value;
}
#endregion }