반응형
EP Plus - Excel 표 읽기
EPLus를 사용하여 엑셀 테이블을 읽은 다음 각 열의 모든 내용을 해당하는 것으로 저장하고 싶습니다.List나는 그것이 표의 표제를 인식하고 그것을 바탕으로 내용을 분류하기를 원합니다.
예를 들어, Excel 테이블이 아래와 같은 경우:
Id Name Gender
1 John Male
2 Maria Female
3 Daniel Unknown
저장할 데이터를 원합니다.List<ExcelData>어디에
public class ExcelData
{
public string Id { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
}
제목을 사용하여 내용을 부를 수 있도록 합니다.예를 들어, 다음 작업을 수행합니다.
foreach (var data in ThatList)
{
Console.WriteLine(data.Id + data.Name + data.Gender);
}
그러면 다음과 같은 출력이 표시됩니다.
1JohnMale
2MariaFemale
3DanielUnknown
이게 제가 가진 전부입니다.
var package = new ExcelPackage(new FileInfo(@"C:\ExcelFile.xlsx"));
ExcelWorksheet sheet = package.Workbook.Worksheets[1];
var table = sheet.Tables.First();
table.Columns.Something //I guess I can use this to do what I want
도와주세요 :( 저는 이것에 대한 샘플 코드를 찾기 위해 오랜 시간을 들여서 그것으로부터 배울 수 있었지만 소용이 없었습니다.ExcelToLinQ도 그렇게 하는 것으로 알고 있지만 테이블을 인식할 수 없습니다.
왜 그런지는 모르겠지만 위의 해결책 중 어떤 것도 저에게 효과가 없습니다.효과적인 기능 공유:
public void readXLS(string FilePath)
{
FileInfo existingFile = new FileInfo(FilePath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
//get the first worksheet in the workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int colCount = worksheet.Dimension.End.Column; //get Column Count
int rowCount = worksheet.Dimension.End.Row; //get row count
for (int row = 1; row <= rowCount; row++)
{
for (int col = 1; col <= colCount; col++)
{
Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value?.ToString().Trim());
}
}
}
}
네이티브는 없지만 제가 이 게시물에 올린 것을 사용하면 어떨까요?
EPLUS를 사용하여 Excel 행을 다시 유형으로 구문 분석하는 방법
테이블만 가리키려면 수정해야 합니다.다음과 같은 작업이 수행되어야 합니다.
public static IEnumerable<T> ConvertTableToObjects<T>(this ExcelTable table) where T : new()
{
//DateTime Conversion
var convertDateTime = new Func<double, DateTime>(excelDate =>
{
if (excelDate < 1)
throw new ArgumentException("Excel dates cannot be smaller than 0.");
var dateOfReference = new DateTime(1900, 1, 1);
if (excelDate > 60d)
excelDate = excelDate - 2;
else
excelDate = excelDate - 1;
return dateOfReference.AddDays(excelDate);
});
//Get the properties of T
var tprops = (new T())
.GetType()
.GetProperties()
.ToList();
//Get the cells based on the table address
var start = table.Address.Start;
var end = table.Address.End;
var cells = new List<ExcelRangeBase>();
//Have to use for loops insteadof worksheet.Cells to protect against empties
for (var r = start.Row; r <= end.Row; r++)
for (var c = start.Column; c <= end.Column; c++)
cells.Add(table.WorkSheet.Cells[r, c]);
var groups = cells
.GroupBy(cell => cell.Start.Row)
.ToList();
//Assume the second row represents column data types (big assumption!)
var types = groups
.Skip(1)
.First()
.Select(rcell => rcell.Value.GetType())
.ToList();
//Assume first row has the column names
var colnames = groups
.First()
.Select((hcell, idx) => new { Name = hcell.Value.ToString(), index = idx })
.Where(o => tprops.Select(p => p.Name).Contains(o.Name))
.ToList();
//Everything after the header is data
var rowvalues = groups
.Skip(1) //Exclude header
.Select(cg => cg.Select(c => c.Value).ToList());
//Create the collection container
var collection = rowvalues
.Select(row =>
{
var tnew = new T();
colnames.ForEach(colname =>
{
//This is the real wrinkle to using reflection - Excel stores all numbers as double including int
var val = row[colname.index];
var type = types[colname.index];
var prop = tprops.First(p => p.Name == colname.Name);
//If it is numeric it is a double since that is how excel stores all numbers
if (type == typeof(double))
{
if (!string.IsNullOrWhiteSpace(val?.ToString()))
{
//Unbox it
var unboxedVal = (double)val;
//FAR FROM A COMPLETE LIST!!!
if (prop.PropertyType == typeof(Int32))
prop.SetValue(tnew, (int)unboxedVal);
else if (prop.PropertyType == typeof(double))
prop.SetValue(tnew, unboxedVal);
else if (prop.PropertyType == typeof(DateTime))
prop.SetValue(tnew, convertDateTime(unboxedVal));
else
throw new NotImplementedException(String.Format("Type '{0}' not implemented yet!", prop.PropertyType.Name));
}
}
else
{
//Its a string
prop.SetValue(tnew, val);
}
});
return tnew;
});
//Send it back
return collection;
}
다음은 테스트 방법입니다.
[TestMethod]
public void Table_To_Object_Test()
{
//Create a test file
var fi = new FileInfo(@"c:\temp\Table_To_Object.xlsx");
using (var package = new ExcelPackage(fi))
{
var workbook = package.Workbook;
var worksheet = workbook.Worksheets.First();
var ThatList = worksheet.Tables.First().ConvertTableToObjects<ExcelData>();
foreach (var data in ThatList)
{
Console.WriteLine(data.Id + data.Name + data.Gender);
}
package.Save();
}
}
콘솔에 제공된 정보:
1JohnMale
2MariaFemale
3DanielUnknown
클래스에 문자열이 필요하므로 ID 필드가 Excel의 숫자 또는 문자열인 경우에만 주의하십시오.
이것은 제 작업 버전입니다.확인자 코드는 표시되지 않지만 각 워크시트에서 열 이름이 약간 다르더라도 열을 확인할 수 있는 구현의 스핀입니다.
public static IEnumerable<T> ToArray<T>(this ExcelWorksheet worksheet, List<PropertyNameResolver> resolvers) where T : new()
{
// List of all the column names
var header = worksheet.Cells.GroupBy(cell => cell.Start.Row).First();
// Get the properties from the type your are populating
var properties = typeof(T).GetProperties().ToList();
var start = worksheet.Dimension.Start;
var end = worksheet.Dimension.End;
// Resulting list
var list = new List<T>();
// Iterate the rows starting at row 2 (ie start.Row + 1)
for (int row = start.Row + 1; row <= end.Row; row++)
{
var instance = new T();
for (int col = start.Column; col <= end.Column; col++)
{
object value = worksheet.Cells[row, col].Text;
// Get the column name zero based (ie col -1)
var column = (string)header.Skip(col - 1).First().Value;
// Gets the corresponding property to set
var property = properties.Property(resolvers, column);
try
{
var propertyName = property.PropertyType.IsGenericType
? property.PropertyType.GetGenericArguments().First().FullName
: property.PropertyType.FullName;
// Implement setter code as needed.
switch (propertyName)
{
case "System.String":
property.SetValue(instance, Convert.ToString(value));
break;
case "System.Int32":
property.SetValue(instance, Convert.ToInt32(value));
break;
case "System.DateTime":
if (DateTime.TryParse((string) value, out var date))
{
property.SetValue(instance, date);
}
property.SetValue(instance, FromExcelSerialDate(Convert.ToInt32(value)));
break;
case "System.Boolean":
property.SetValue(instance, (int)value == 1);
break;
}
}
catch (Exception e)
{
// instance property is empty because there was a problem.
}
}
list.Add(instance);
}
return list;
}
// Utility function taken from the above post's inline function.
public static DateTime FromExcelSerialDate(int excelDate)
{
if (excelDate < 1)
throw new ArgumentException("Excel dates cannot be smaller than 0.");
var dateOfReference = new DateTime(1900, 1, 1);
if (excelDate > 60d)
excelDate = excelDate - 2;
else
excelDate = excelDate - 1;
return dateOfReference.AddDays(excelDate);
}
아래 코드는 엑셀 데이터를 데이터 테이블로 읽어 들이고, 데이터 테이블은 데이터 행 목록으로 변환됩니다.
if (FileUpload1.HasFile)
{
if (Path.GetExtension(FileUpload1.FileName) == ".xlsx")
{
Stream fs = FileUpload1.FileContent;
ExcelPackage package = new ExcelPackage(fs);
DataTable dt = new DataTable();
dt= package.ToDataTable();
List<DataRow> listOfRows = new List<DataRow>();
listOfRows = dt.AsEnumerable().ToList();
}
}
using OfficeOpenXml;
using System.Data;
using System.Linq;
public static class ExcelPackageExtensions
{
public static DataTable ToDataTable(this ExcelPackage package)
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.First();
DataTable table = new DataTable();
foreach (var firstRowCell in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
{
table.Columns.Add(firstRowCell.Text);
}
for (var rowNumber = 2; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
{
var row = workSheet.Cells[rowNumber, 1, rowNumber, workSheet.Dimension.End.Column];
var newRow = table.NewRow();
foreach (var cell in row)
{
newRow[cell.Start.Column - 1] = cell.Text;
}
table.Rows.Add(newRow);
}
return table;
}
}
하지만 또 다른 방법입니다.
Ernie S 솔루션을 사용했지만 첫 번째 데이터 행에 빈 셀이 있으면 작동하지 않았습니다(데이터 유형을 추측할 수 없음).
따라서 Excel 테이블에서 데이터 유형을 가져오는 대신,T반사를 사용하는 매개 변수 클래스 속성입니다.
/// <summary>
/// Converts table to list of T objects
/// </summary>
/// <typeparam name="T">The type to return</typeparam>
/// <param name="table">Data source</param>
/// <returns>List of T objects</returns>
public static IEnumerable<T> ConvertToObjects<T>(this ExcelTable table) where T : new()
{
ExcelCellAddress start = table.Address.Start;
ExcelCellAddress end = table.Address.End;
List<ExcelRange> cells = new();
for (int r = start.Row; r <= end.Row; r++)
for (int c = start.Column; c <= end.Column; c++)
cells.Add(table.WorkSheet.Cells[r, c]);
List<IGrouping<int, ExcelRange>> allRows = cells
.GroupBy(cell => cell.Start.Row)
.OrderBy(cell => cell.Key)
.ToList();
IEnumerable<PropertyInfo> typeProperties = typeof(T).GetProperties();
IGrouping<int, ExcelRangeBase> header = allRows.First();
Dictionary<PropertyInfo, int> columns = new();
foreach (ExcelRangeBase col in header)
{
string propName = col.GetValue<string>();
PropertyInfo propInfo = typeProperties.FirstOrDefault(x => x.Name.Equals(propName));
if (propInfo != null)
{
columns.Add(propInfo, col.Start.Column);
}
}
IEnumerable<IGrouping<int, ExcelRangeBase>> rows = allRows.Skip(1);
List<T> objects = new();
foreach (IGrouping<int, ExcelRangeBase> row in rows)
{
T obj = new();
foreach (KeyValuePair<PropertyInfo, int> colInfo in columns)
{
ExcelRangeBase col = row.First(x => x.Start.Column == colInfo.Value);
if (col.Value == null)
continue;
object value = Convert.ChangeType(col.Value, Nullable.GetUnderlyingType(colInfo.Key.PropertyType) ?? colInfo.Key.PropertyType);
colInfo.Key.SetValue(obj, value);
}
objects.Add(obj);
}
return objects;
}
첫 번째 답변에서 오류가 발생하여 코드 라인을 변경했습니다.
제 새 코드를 사용해 보세요, 저한테 효과가 있어요.
using OfficeOpenXml;
using OfficeOpenXml.Table;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
public static class ImportExcelReader
{
public static List<T> ImportExcelToList<T>(this ExcelWorksheet worksheet) where T : new()
{
//DateTime Conversion
Func<double, DateTime> convertDateTime = new Func<double, DateTime>(excelDate =>
{
if (excelDate < 1)
{
throw new ArgumentException("Excel dates cannot be smaller than 0.");
}
DateTime dateOfReference = new DateTime(1900, 1, 1);
if (excelDate > 60d)
{
excelDate = excelDate - 2;
}
else
{
excelDate = excelDate - 1;
}
return dateOfReference.AddDays(excelDate);
});
ExcelTable table = null;
if (worksheet.Tables.Any())
{
table = worksheet.Tables.FirstOrDefault();
}
else
{
table = worksheet.Tables.Add(worksheet.Dimension, "tbl" + ShortGuid.NewGuid().ToString());
ExcelAddressBase newaddy = new ExcelAddressBase(table.Address.Start.Row, table.Address.Start.Column, table.Address.End.Row + 1, table.Address.End.Column);
//Edit the raw XML by searching for all references to the old address
table.TableXml.InnerXml = table.TableXml.InnerXml.Replace(table.Address.ToString(), newaddy.ToString());
}
//Get the cells based on the table address
List<IGrouping<int, ExcelRangeBase>> groups = table.WorkSheet.Cells[table.Address.Start.Row, table.Address.Start.Column, table.Address.End.Row, table.Address.End.Column]
.GroupBy(cell => cell.Start.Row)
.ToList();
//Assume the second row represents column data types (big assumption!)
List<Type> types = groups.Skip(1).FirstOrDefault().Select(rcell => rcell.Value.GetType()).ToList();
//Get the properties of T
List<PropertyInfo> modelProperties = new T().GetType().GetProperties().ToList();
//Assume first row has the column names
var colnames = groups.FirstOrDefault()
.Select((hcell, idx) => new
{
Name = hcell.Value.ToString(),
index = idx
})
.Where(o => modelProperties.Select(p => p.Name).Contains(o.Name))
.ToList();
//Everything after the header is data
List<List<object>> rowvalues = groups
.Skip(1) //Exclude header
.Select(cg => cg.Select(c => c.Value).ToList()).ToList();
//Create the collection container
List<T> collection = new List<T>();
foreach (List<object> row in rowvalues)
{
T tnew = new T();
foreach (var colname in colnames)
{
//This is the real wrinkle to using reflection - Excel stores all numbers as double including int
object val = row[colname.index];
Type type = types[colname.index];
PropertyInfo prop = modelProperties.FirstOrDefault(p => p.Name == colname.Name);
//If it is numeric it is a double since that is how excel stores all numbers
if (type == typeof(double))
{
//Unbox it
double unboxedVal = (double)val;
//FAR FROM A COMPLETE LIST!!!
if (prop.PropertyType == typeof(int))
{
prop.SetValue(tnew, (int)unboxedVal);
}
else if (prop.PropertyType == typeof(double))
{
prop.SetValue(tnew, unboxedVal);
}
else if (prop.PropertyType == typeof(DateTime))
{
prop.SetValue(tnew, convertDateTime(unboxedVal));
}
else if (prop.PropertyType == typeof(string))
{
prop.SetValue(tnew, val.ToString());
}
else
{
throw new NotImplementedException(string.Format("Type '{0}' not implemented yet!", prop.PropertyType.Name));
}
}
else
{
//Its a string
prop.SetValue(tnew, val);
}
}
collection.Add(tnew);
}
return collection;
}
}
이 함수를 어떻게 부릅니까?아래 코드를 참조하십시오.
private List<FundraiserStudentListModel> GetStudentsFromExcel(HttpPostedFileBase file)
{
List<FundraiserStudentListModel> list = new List<FundraiserStudentListModel>();
if (file != null)
{
try
{
using (ExcelPackage package = new ExcelPackage(file.InputStream))
{
ExcelWorkbook workbook = package.Workbook;
if (workbook != null)
{
ExcelWorksheet worksheet = workbook.Worksheets.FirstOrDefault();
if (worksheet != null)
{
list = worksheet.ImportExcelToList<FundraiserStudentListModel>();
}
}
}
}
catch (Exception err)
{
//save error log
}
}
return list;
}
기금 마련자 학생 목록 모델:
public class FundraiserStudentListModel
{
public string Name { get; set; }
public string Email { get; set; }
public string Phone { get; set; }
}
유효한 이메일, 휴대폰 번호를 사용한 작업 솔루션
public class ExcelProcessing
{
public List<ExcelUserData> ReadExcel()
{
string path = Config.folderPath + @"\MemberUploadFormat.xlsx";
using (var excelPack = new ExcelPackage())
{
//Load excel stream
using (var stream = File.OpenRead(path))
{
excelPack.Load(stream);
}
//Lets Deal with first worksheet.(You may iterate here if dealing with multiple sheets)
var ws = excelPack.Workbook.Worksheets[0];
List<ExcelUserData> userList = new List<ExcelUserData>();
int colCount = ws.Dimension.End.Column; //get Column Count
int rowCount = ws.Dimension.End.Row;
for (int row = 2; row <= rowCount; row++) // start from to 2 omit header
{
bool IsValid = true;
ExcelUserData _user = new ExcelUserData();
for (int col = 1; col <= colCount; col++)
{
if (col == 1)
{
_user.FirstName = ws.Cells[row, col].Value?.ToString().Trim();
if (string.IsNullOrEmpty(_user.FirstName))
{
_user.ErrorMessage += "Enter FirstName <br/>";
IsValid = false;
}
}
else if (col == 2)
{
_user.Email = ws.Cells[row, col].Value?.ToString().Trim();
if (string.IsNullOrEmpty(_user.Email))
{
_user.ErrorMessage += "Enter Email <br/>";
IsValid = false;
}
else if (!IsValidEmail(_user.Email))
{
_user.ErrorMessage += "Invalid Email Address <br/>";
IsValid = false;
}
}
else if (col ==3)
{
_user.MobileNo = ws.Cells[row, col].Value?.ToString().Trim();
if (string.IsNullOrEmpty(_user.MobileNo))
{
_user.ErrorMessage += "Enter Mobile No <br/>";
IsValid = false;
}
else if (_user.MobileNo.Length != 10)
{
_user.ErrorMessage += "Invalid Mobile No <br/>";
IsValid = false;
}
}
else if (col == 4)
{
_user.IsAdmin = ws.Cells[row, col].Value?.ToString().Trim();
if (string.IsNullOrEmpty(_user.IsAdmin))
{
_user.IsAdmin = "0";
}
}
_user.IsValid = IsValid;
}
userList.Add(_user);
}
return userList;
}
}
public static bool IsValidEmail(string email)
{
Regex regex = new Regex(@"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$",
RegexOptions.CultureInvariant | RegexOptions.Singleline);
return regex.IsMatch(email);
}
}
이 코드를 사용하면 셀이 null이므로 오류가 발생하지 않습니다.또한 클래스의 속성에 따라 데이터 유형을 캐스트합니다!
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using OfficeOpenXml;
public static class ReadExcel
{
public static List<T> ReadExcelToList<T>(this ExcelWorksheet worksheet) where T : new()
{
List<T> collection = new List<T>();
try
{
DataTable dt = new DataTable();
foreach (var firstRowCell in new T().GetType().GetProperties().ToList())
{
//Add table colums with properties of T
dt.Columns.Add(firstRowCell.Name);
}
for (int rowNum = 2; rowNum <= worksheet.Dimension.End.Row; rowNum++)
{
var wsRow = worksheet.Cells[rowNum, 1, rowNum, worksheet.Dimension.End.Column];
DataRow row = dt.Rows.Add();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
}
//Get the colums of table
var columnNames = dt.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToList();
//Get the properties of T
List<PropertyInfo> properties = new T().GetType().GetProperties().ToList();
collection = dt.AsEnumerable().Select(row =>
{
T item = Activator.CreateInstance<T>();
foreach (var pro in properties)
{
if (columnNames.Contains(pro.Name) || columnNames.Contains(pro.Name.ToUpper()))
{
PropertyInfo pI = item.GetType().GetProperty(pro.Name);
pro.SetValue(item, (row[pro.Name] == DBNull.Value) ? null : Convert.ChangeType(row[pro.Name], (Nullable.GetUnderlyingType(pI.PropertyType) == null) ? pI.PropertyType : Type.GetType(pI.PropertyType.GenericTypeArguments[0].FullName)));
}
}
return item;
}).ToList();
}
catch (Exception ex)
{
//Save error log
}
return collection;
}
}
이 함수를 어떻게 부릅니까?아래 코드를 참조하십시오.
public List<Users> GetStudentsFromExcel(HttpPostedFileBase file)
{
List<Users> list = new List<Users>();
if (file != null)
{
try
{
using (ExcelPackage package = new ExcelPackage(file.InputStream))
{
ExcelWorkbook workbook = package.Workbook;
if (workbook != null)
{
ExcelWorksheet worksheet = workbook.Worksheets.FirstOrDefault();
if (worksheet != null)
{
list = worksheet.ReadExcelToList<Users>();
//Your code
}
}
}
}
catch (Exception ex)
{
//Save error log
}
}
return list;
}
public class Users
{
public string Code { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public string Phone { get; set; }
public string Address { get; set; }
public DateTime CreatedAt { get; set; }
}
누군가를 돕기를 바랍니다!
언급URL : https://stackoverflow.com/questions/36637882/epplus-read-excel-table
반응형
'programing' 카테고리의 다른 글
| POI 생성 Excel 파일의 셀에 테두리 추가 (0) | 2023.05.12 |
|---|---|
| UITableViewController 외부의 정적 테이블 보기 (0) | 2023.05.12 |
| Swift Dictionary: 배열로 값 가져오기 (0) | 2023.05.07 |
| Bash에서 지정된 시간 초과 후 하위 프로세스를 종료하는 방법은 무엇입니까? (0) | 2023.05.07 |
| 탐색 모음에서 뒤로 단추 색 변경 (0) | 2023.05.07 |