public static List <Adresa> GenerateFromXLS(string fileName)
{
var retval = new List <Adresa>();
var fi = new FileInfo(fileName);
using (var pck = new ExcelPackage(fi))
{
var workbook = pck.Workbook;
ExcelWorksheet worksheet = workbook.Worksheets.First();
var newcollection = worksheet.ConvertSheetToObjects <TestObject>();
//newcollection.ToList().ForEach(to => Console.WriteLine("{{ Col1:{0}, Col2: {1}, Col3: \"{2}\", Col4: {3} }}", to.ADDRESS_ID, to.ADDRESS_STRING, to.LAT, to.LON));
foreach (var item in newcollection.ToList())
{
if (item.ADDRESS_ID.Equals("ADDRESS_ID"))
{
continue;
}
var oneAdress = new Adresa()
{
geonameid = item.ADDRESS_ID,
name = item.ADDRESS_STRING,
asciiname = item.ADDRESS_STRING,
alternatenames = item.ADDRESS_STRING,
longitude = item.LON,
latitude = item.LAT
};
retval.Add(oneAdress);
}
}
return(retval);
}
public static List <Meal> GetMeals()
{
var mealList = new List <Meal>();
try
{
var baseDirectory = System.AppDomain.CurrentDomain.BaseDirectory;
var fileName = "Feb.xlsx";
var filePath = Path.GetFullPath(Path.Combine(baseDirectory, fileName));
FileInfo file = new FileInfo(filePath);
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
mealList = worksheet.ConvertSheetToObjects <Meal>().ToList();
}
}
catch (Exception ex)
{
throw ex;
}
return(mealList);
}
/// <summary>
/// Beolvassa az adathalmazt a paraméterben átadott Excel fájlból,
/// majd egy T Típusban megadott Entitású Adathalmazzá konvertálja át.
/// </summary>
/// <returns>Az Excel tábla tartalma T Típusú Generikus Objektum halmazzá konvertálva.</returns>
public List <DataModel> ReadDataFromExcel()
{
try
{
FileInfo existingFileInfo = new FileInfo(FilePath);
if (existingFileInfo.Exists)
{
using (ExcelPackage excelPackage = new ExcelPackage(existingFileInfo))
{
ExcelWorksheet excelWorksheet = excelPackage.Workbook.Worksheets[1];
return(excelWorksheet.ConvertSheetToObjects <DataModel>().ToList());
}
}
Console.WriteLine("\n\nAz Excel fájl nem található!");
return(null);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("+++++ ERROR - Hiba!" +
$" Hiba az Excel beolvasása közben!\n\nException Message {ex.Message}");
return(null);
}
}
public void ReadFile()
{
var fileName = "currentUser_25.09.2018 0828.xlsx";
string fileFullPath = $"{_hostingEnvironment.WebRootPath}\\Upload\\{fileName}";
var fi = new FileInfo(fileFullPath);
using (ExcelPackage package = new ExcelPackage(fi))
{
//get the first worksheet in the workbook
try
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.First();
int colCount = worksheet.Dimension.End.Column; //get Column Count
int rowCount = worksheet.Dimension.End.Row; //get row count
var newcollection = worksheet.ConvertSheetToObjects <TestClass>();
var testList = new List <TestClass>();
}
catch (Exception ex)
{
throw;
}
}
}
/// <summary>
/// Returns list of objects based on *.xlsx worksheet.
/// </summary>
public static List <T> ImportFromExcel(string fileName)
{
using (FileStream fileStream = new FileStream(fileName, FileMode.Open))
{
ExcelPackage excel = new ExcelPackage(fileStream);
ExcelWorksheet worksheet = excel.Workbook.Worksheets[1];
return(worksheet.ConvertSheetToObjects <T>().ToList());
}
}
public IEnumerable <T> ConvertToObjects <T>() where T : new()
{
try
{
ExcelWorksheet worksheet = _excel.Workbook.Worksheets[0];
return(worksheet.ConvertSheetToObjects <T>());
}
catch (Exception ex)
{
throw new ExcelConvertionException(ex.Message, ex);
}
}
private static void Main()
{
using (var fileStream = new FileStream("sample-data/financial-sample.xlsx", FileMode.Open))
{
var excelPackage = new ExcelPackage(fileStream);
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[0];
IEnumerable <ExcelResourceDto> objectsList = worksheet.ConvertSheetToObjects <ExcelResourceDto>();
foreach (ExcelResourceDto dto in objectsList)
{
Console.WriteLine(dto.Title);
}
}
}
/// <summary>
/// 导入Excel文件
/// </summary>
/// <param name="excelfile"></param>
/// <param name="sheetIndex"></param>
/// <param name="_hostingEnvironment"></param>
/// <returns></returns>
public static List <T> UpLoad(IFormFile file, int sheetIndex)
{
//FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
//using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
//{
// excelfile.CopyTo(fs);
// fs.Flush();
//}
using (ExcelPackage package = new ExcelPackage(file.OpenReadStream()))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.First();
List <T> list = worksheet.ConvertSheetToObjects <T>().ToList();
return(list);
}
}
/// <summary>
/// 导入
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="filepath">文件路径</param>
/// <param name="msg">消息</param>
/// <returns></returns>
public static List <T> ImportExcel <T>(string filepath, ref string msg) where T : new()
{
try
{
//定义返回集合
List <T> result = new List <T>();
FileInfo file = new FileInfo(filepath);
if (file != null)
{
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.First();
result = worksheet.ConvertSheetToObjects <T>().ToList();
}
}
return(result);
}
catch (Exception ex)
{
msg = "导入Excel失败：" + ex.Message;
//写入日志
return(null);
}
}