public class ExcelHelper
{
/// <summary>
/// 将数据导出为Excel文件
/// </summary>
/// <param name="name">文件名</param>
/// <param name="strList">数据</param>
/// <param name="path">路径,默认D盘根目录</param>
/// <returns></returns>
public string ExportExcel(string name, List<string[]> strList, string path = "D:\\")
{
string documentPath = string.Format($"{path}{name}.xlsx");
var spreadsheetDocument = SpreadsheetDocument.Create(documentPath, SpreadsheetDocumentType.Workbook);
var workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
sheets.Append(sheet);
var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
foreach (var item in strList)
{
Row row = new Row();
foreach (var str in item)
{
Cell dataCell = new Cell
{
CellValue = new CellValue(str),
DataType = new EnumValue<CellValues>(CellValues.String)
};
row.AppendChild(dataCell);
}
sheetData.Append(row);
}
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
return documentPath;
}
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="path">文件路径</param>
public List<string[]> ImportExcel(string path)
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
{
WorkbookPart wbPart = doc.WorkbookPart;
SharedStringTablePart tablePart = wbPart.SharedStringTablePart;
Sheet mysheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.FirstOrDefault();
WorksheetPart worksheet = ((WorksheetPart)wbPart.GetPartById(mysheet.Id));
SheetData sheetData = worksheet.Worksheet.GetFirstChild<SheetData>();
List<string[]> strList = new List<string[]>();
ArrayList arrayList = new ArrayList();
foreach (var row in sheetData.ChildElements)
{
foreach (var cell in (row as Row).ChildElements)
{
var cellValue = (cell as Cell).CellValue;
if (cellValue != null)
{
arrayList.Add(GetValue((cell as Cell), tablePart));
}
}
string[] arrString = (string[])arrayList.ToArray(typeof(string));
strList.Add(arrString);
arrayList.Clear();
arrString = null;
}
return strList;
}
}
/// <summary>
/// 读取s类型的列(SharedStringTable类型),它的值是存储在子元素内的。
/// </summary>
/// <param name="cell">单元格</param>
/// <param name="stringTablePart">WorkbookPart.SharedStringTablePart</param>
/// <returns></returns>
public static String GetValue(Cell cell, SharedStringTablePart stringTablePart)
{
if (cell.ChildElements.Count == 0)
return null;
String value = cell.CellValue.InnerText;
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
value = stringTablePart.SharedStringTable
.ChildElements[Int32.Parse(value)]
.InnerText;
return value;
}
/// <summary>
/// 读取Excel数据(OfficeOpenXML)
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public List<string[]> ImportExcel(string path)
{
var file = new FileInfo(path);
var strList = new List<string[]>();
var arrayList = new ArrayList();
if (file != null)
{
using (ExcelPackage package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets[0];
//获取表格的列数和行数
var rowCount = worksheet.Dimension.Rows;
var ColCount = worksheet.Dimension.Columns;
for (int row = 1; row <= rowCount; row++)
{
for(int cel = 1; cel <= ColCount; cel++)
{
arrayList.Add(worksheet.Cells[row, cel].Value.ToString());
}
var arrString = (string[])arrayList.ToArray(typeof(string));
strList.Add(arrString);
arrayList.Clear();
arrString = null;
}
return strList;
}
}
return null;
}
/// <summary>
/// 读取Excel文件(NPOI)
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public List<string[]> ImportExcel_NPOI(string path)
{
var arrayList = new ArrayList();
var strList = new List<string[]>();
var dt = new DataTable();
IWorkbook workbook;
var fileExt = Path.GetExtension(path).ToLower();
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null) { return null; }
var sheet = workbook.GetSheetAt(0);
//表头
var header = sheet.GetRow(sheet.FirstRowNum);
var columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
var obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
arrayList.Add(obj.ToString());
}
var arrString = (string[])arrayList.ToArray(typeof(string));
strList.Add(arrString);
arrayList.Clear();
arrString = null;
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
var dr = dt.NewRow();
var hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
arrayList.Add(dr[j]);
}
}
if (hasValue)
{
arrString = (string[])arrayList.ToArray(typeof(string));
strList.Add(arrString);
arrayList.Clear();
arrString = null;
}
}
}
return strList;
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
}
【EPPLUS】
public class OfficeHelper<T> where T : new()
{
/// <summary>
/// 获取 Excel Sheet 数据集合
/// </summary>
/// <param name="path">文件路径</param>
/// <returns></returns>
public T[] GetSheetValues(string path)
{
var file = new FileInfo(path);
if (file == null) return null;
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets.First();
var result = worksheet.ConvertSheetToObjects<T>().ToArray();
return result;
}
}
}
public class Excels
{
/// <summary>
/// 分别对应表中的列名
/// </summary>
[ExcelColumn("Name")]
public string Name { get; set; }
[ExcelColumn("Level")]
public string Level { get; set; }
[ExcelColumn("Sort")]
public string Sort { get; set; }
[ExcelColumn("Code")]
public string Code { get; set; }
[ExcelColumn("Type")]
public string Type { get; set; }
//存储表格数据归属关系
public int Index { get; set; }
}
/// <summary>
/// 自定义excel头部标签
/// </summary>
[AttributeUsage(AttributeTargets.All)]
public class ExcelColumn : Attribute
{
/// <summary>
/// 标签名称
/// </summary>
public string ColumnName { get; set; }
/// <summary>
/// 构造函数
/// </summary>
/// <param name="name"></param>
public ExcelColumn(string name)
{
ColumnName = name;
}
}
public static class EpplusExtensions
{
/// <summary>
/// 获取标签对应excel的Index
/// </summary>
/// <param name="ws"></param>
/// <param name="columnName"></param>
/// <returns></returns>
/// <exception cref="ArgumentNullException"></exception>
public static int GetColumnByName(this ExcelWorksheet ws, string columnName)
{
if (ws == null) throw new ArgumentNullException(nameof(ws));
return ws.Cells["1:1"].FirstOrDefault(c => c.Value.ToString() == columnName).Start.Column;
}
/// <summary>
/// 扩展方法,转换为实体列表
/// </summary>
/// <param name="worksheet"></param>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static IEnumerable<T> ConvertSheetToObjects<T>(this ExcelWorksheet worksheet) where T : new()
{
bool columnOnly(CustomAttributeData y) => y.AttributeType == typeof(ExcelColumn);
var rows = worksheet.Cells
.Select(cell => cell.Start.Row)
.Distinct()
.OrderBy(x => x);
var columns = typeof(T)
.GetProperties()
.Where(x => x.CustomAttributes.Any(columnOnly))
.Select(p => new
{
Property = p,
Column = p.GetCustomAttributes<ExcelColumn>().First().ColumnName
}).ToList();
var str = "";
var index = worksheet.Cells["1:1"].Last().Start.Column;
for (int i = 1; i <= index; i++)
{
var result = worksheet.Cells[1, i].Value.ToString();
str += result;
}
var collection = rows.Skip(1)
.Select(row =>
{
var tnew = new T();
columns.ForEach(col =>
{
if (-1 != str.IndexOf(col.Column))
{
var val = worksheet.Cells[row, GetColumnByName(worksheet, col.Column)];
if (val.Value == null)
{
col.Property.SetValue(tnew, null);
return;
}
col.Property.SetValue(tnew, val.GetValue<string>());
}
});
return tnew;
});
return collection;
}
}
手机扫一扫
移动阅读更方便
你可能感兴趣的文章