public static List <T> ReadFromExcel <T>(string excelFilePath) where T : class, new()
{
List <T> entityList = new List <T>();
var type = typeof(T);
ExcelTableAttribute tableAttribute = (ExcelTableAttribute)type.GetCustomAttributes(typeof(ExcelTableAttribute), false)[0];
var tableName = tableAttribute.Name;
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
if (string.IsNullOrEmpty(tableName) || properties.Length == 0)
{
return(entityList);
}
Excel.Application app = GetApplication();
Excel.Workbook book = app.Workbooks.Open(excelFilePath);
for (int sheetIndex = 1; sheetIndex <= book.Sheets.Count; sheetIndex++)
{
Excel.Worksheet sheet = book.Sheets[sheetIndex];
if (sheet.Name.Equals(tableName))
{
var maxRow = GetMaxRow(sheet);
var maxColumn = GetMaxColumn(sheet);
Excel.Range range = sheet.Range[ToColumnLabel(1) + 1, ToColumnLabel(maxColumn) + maxRow];
for (int row = 2; row <= maxRow; row++)
{
T entity = new T();
foreach (var property in type.GetProperties())
{
ExcelColumnAttribute columnAttribute = (ExcelColumnAttribute)property.GetCustomAttributes(typeof(ExcelColumnAttribute), false)[0];
Excel.Range cellRange = range[row, columnAttribute.ColumnIndex];
string cellText = cellRange.Text;
switch (property.PropertyType.ToString())
{
case "System.Int32":
{
int intValue = 0;
if (int.TryParse(cellRange.Text, out intValue))
{
property.SetValue(entity, intValue, null);
}
}
break;
case "System.Int64":
{
long longValue = 0;
if (long.TryParse(cellRange.Text, out longValue))
{
property.SetValue(entity, longValue, null);
}
}
break;
case "System.String":
{
property.SetValue(entity, cellRange.Text, null);
}
break;
default:
break;
}
}
entityList.Add(entity);
}
break;
}
}
book.Close(false);
app.Quit();
return(entityList);
}
/// <summary>
/// 将数据写入excel文件并关闭excel文件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="excelFilePath"></param>
/// <param name="entityList"></param>
public static void WriteToExcel <T>(string excelFilePath, List <T> entityList)
{
var type = typeof(T);
ExcelTableAttribute tableAttribute = (ExcelTableAttribute)type.GetCustomAttributes(typeof(ExcelTableAttribute), false)[0];
var tableName = tableAttribute.Name;
var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
if (string.IsNullOrEmpty(tableName) || properties.Length == 0)
{
return;
}
Excel.Application app = GetApplication();
Excel.Workbook book;
if (System.IO.File.Exists(excelFilePath))
{
book = app.Workbooks.Open(excelFilePath);
}
else
{
book = app.Workbooks.Add();
book.SaveAs(excelFilePath);
}
for (int sheetIndex = 1; sheetIndex <= book.Sheets.Count; sheetIndex++)
{
Excel.Worksheet sheet = book.Sheets[sheetIndex];
if (sheet.Name.Equals(tableName))
{
book.Close(false);
return;
}
}
Excel.Worksheet newWorkSheet = book.Worksheets.Add();
newWorkSheet.Name = tableName;
var startCell = ToColumnLabel(1) + 1;
var endCell = ToColumnLabel(properties.Length) + (entityList.Count + 1);
Excel.Range range = newWorkSheet.Range[startCell, endCell];
foreach (var property in properties)
{
ExcelColumnAttribute columnAttribute = (ExcelColumnAttribute)property.GetCustomAttributes(typeof(ExcelColumnAttribute), false)[0];
Excel.Range titleCellRange = range[1, columnAttribute.ColumnIndex];
titleCellRange.Value = columnAttribute.Name;
}
foreach (var property in properties)
{
ExcelColumnAttribute columnAttribute = (ExcelColumnAttribute)property.GetCustomAttributes(typeof(ExcelColumnAttribute), false)[0];
for (int row = 0; row < entityList.Count; row++)
{
Excel.Range valueCellRange = range[row + 2, columnAttribute.ColumnIndex];
switch (columnAttribute.ExcelCellFormat)
{
case ExcelCellFormat.Default:
{
switch (property.PropertyType.ToString())
{
case "System.String":
{
valueCellRange.NumberFormatLocal = "@";
}
break;
default:
break;
}
}
break;
case ExcelCellFormat.Text:
{
valueCellRange.NumberFormatLocal = "@";
}
break;
case ExcelCellFormat.Number2:
{
valueCellRange.NumberFormatLocal = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * \"-\"??_ ;_ @_ ";
}
break;
default:
break;
}
valueCellRange.Value = property.GetValue(entityList[row], null);
}
}
book.Save();
book.Close();
app.Quit();
}