各位有發現EXCEL的格式有分為.CSV、.XLS、 .XLXS 等，但作法不儘相同，所以幫各位整理起來，希望各位網友可以善加利用。
一、CLOSEXML 套件 （目前不支援.CSV、.XLS）
匯入ｅｘｃｅｌ檔案，並轉換是物件Ｌｉｓｔ
|
public List<T>
ReadExcelToList<T>(string fileName, string sheetName) {
List<T> dataList = new List<T>(); Type
typeObject = typeof(T); using (XLWorkbook work
= new
XLWorkbook(fileName)) { var workSheet =
work.Worksheets.Where(q => q.Name == sheetName).First(); var properties =
typeObject.GetProperties(); var colums =
workSheet.FirstRow().Cells().Select((v, i) => new { Value =
v.Value, Index = i + 1 }); foreach (IXLRow row in
workSheet.RowsUsed().Skip(1)) { T obj = (T)Activator.CreateInstance(typeObject);
foreach (var prop in properties)
{
int colIndex =
colums.SingleOrDefault(c => c.Value.ToString() ==
prop.Name.ToString()).Index;
var val = row.Cell(colIndex).Value;
var type = prop.PropertyType;
prop.SetValue(obj, Convert.ChangeType(val, type)); }
dataList.Add(obj); } } return dataList;
} |
二、ＮＰＯＩ （支援.ＸＬＳ、．ＸＬＳＸ）
|
public List<T>
ReadExcelToListForXLS<T>(string fileName) { try {
List<T> dataList = new List<T>(); Type typeObject = typeof(T);
IWorkbook workbook = null; //新建IWorkbook物件 FileStream fileStream = new
FileStream(fileName, FileMode.Open, FileAccess.Read);
workbook = new HSSFWorkbook(fileStream); //xlsx資料讀入workbook ISheet sheet = workbook.GetSheetAt(0); //獲取第一個工作表 // 建立一個空的 List<string> 用來儲存轉換後的資料 var properties =
typeObject.GetProperties(); var colums = sheet.GetRow(0).Cells.Select((v, i) => new { Value = v.StringCellValue, Index = i });
IRow row; //新建當前工作表行資料 for (int i = 1; i <=
sheet.LastRowNum + 1; i++) //對工作表每一行 { row = sheet.GetRow(i); //row讀入第i行資料
if (row != null)
{ T obj = (T)Activator.CreateInstance(typeObject);
foreach (var prop in properties)
{ int colIndex =
colums.SingleOrDefault(c => c.Value.ToString() == prop.Name.ToString()).Index; var val =
row.GetCell(colIndex); var type =
prop.PropertyType;
prop.SetValue(obj, Convert.ChangeType(val?.ToString(), type)); }
dataList.Add(obj);
} } return dataList; } catch (Exception ex) { throw ex; }
} |
三、ＣＳＶ的匯入
|
public List<T>
ReadExcelToListForCSV<T>(string fileName) { try {
List<T> dataList = new List<T>();
Type typeObject = typeof(T); var contents = File.ReadAllText(fileName,
Encoding.GetEncoding("Big5")).Split('\n'); var csv = from line in contents select line.Split(',').ToArray(); var properties =
typeObject.GetProperties(); var colums =
csv.ToList().First().Select((v, i) => new { Value =
v.ToString().Replace("\r", string.Empty), Index = i }); { T obj = (T)Activator.CreateInstance(typeObject);
foreach (var prop in properties)
{
int colIndex = colums.SingleOrDefault(c =>
c.Value.ToString() == prop.Name.ToString()).Index;
var val = row[colIndex];
var type = prop.PropertyType;
prop.SetValue(obj, Convert.ChangeType(val?.ToString(), type));
}
dataList.Add(obj); } return dataList; } catch (Exception ex) { throw ex; }
} |
