WSHCommon/XmdsCommonServer/XLS/XLSLoader.cs

using CommonLang;
using CommonLang.Log;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Reflection;


namespace XmdsCommonServer.XLS
{
    /// <summary>
    /// FileName: XLSLoader.cs
    /// Author: Alex.Yu
    /// Corporation:... 
    /// Description: 
    /// DateTime: 2015/7/8 11:45:39
    /// </summary>

    public class XLSLoader
    {
		public class WSHFieldInfo
		{
			public FieldInfo mFieldInfo;
			public string mFieldName;

			public WSHFieldInfo(FieldInfo fieldInfo, string fieldName)
			{
				this.mFieldInfo = fieldInfo;
				this.mFieldName = fieldName;
			}
		}


		public readonly static Logger log = LoggerFactory.GetLogger("XmdsXLSLoader");
        public XLSLoader()
        {

        }

        public static List<T> LoadSheet<T>(ISheet sheet, int row_head_index = 1, int row_data_index = 3) where T : new()
        {
            //取首行，拿到类型.
            HashMap<int, WSHFieldInfo> head_map = new HashMap<int, WSHFieldInfo>();
            IRow head_row = null;
            ICell head_cell = null;
            int ci = 0;
            try
            {
                Type data_type = typeof(T);
                head_row = sheet.GetRow(row_head_index);
                for (ci = head_row.FirstCellNum ; ci <= head_row.LastCellNum ; ci++)
                {
                    head_cell = head_row.GetCell(ci);
                    if (head_cell != null)
                    {
                        head_cell.SetCellType(CellType.String);
                        if (head_cell.StringCellValue != null)
                        {
                            FieldInfo field = data_type.GetField(head_cell.StringCellValue);
                            if (field != null)
                            {
                                head_map.Add(ci, new WSHFieldInfo(field, head_cell.ToString()));
                            }
                        }
                    }
                }
            }
            catch (Exception error)
            {
                throw new Exception(string.Format("LoadSheetError,sheetname = {0} row_headIndex = {1} cellNum = {2},error = {3}", sheet.SheetName, row_head_index, ci, error.ToString()));
            }

            int ri = 0;
            T data = new T();
			string rowContent = "", rowName = "";

            try
            {
                //取数据，赋值.
                List<T> ret = new List<T>(sheet.LastRowNum);
                for (ri = row_data_index ; ri <= sheet.LastRowNum ; ri++)
                {
                    IRow data_row = sheet.GetRow(ri);

                    if (data_row == null)
                    {
                        break;
                    }
					if(data_row.Cells.Count <= 0)
					{
						log.Warn("表识别到空行：" + sheet.SheetName + ", " + ri);
						continue;
					}

                    ICell headCell = data_row.GetCell(data_row.FirstCellNum);
                    headCell.SetCellType(CellType.String);
                    if (!string.IsNullOrEmpty(headCell.StringCellValue))
                    {
                        data = new T();
                        for (ci = data_row.FirstCellNum ; ci <= data_row.LastCellNum ; ci++)
                        {
							WSHFieldInfo fi = head_map.Get(ci);
                            ICell icell = data_row.GetCell(ci);

                            if (icell != null && fi != null)
                            {
								rowContent = icell.ToString();
								rowName = fi.mFieldName;
								switch (icell.CellType)
                                {
                                    case CellType.Numeric:
                                    case CellType.Formula:
                                        {
                                            object value = icell.NumericCellValue;
											if(typeof(System.Enum).IsAssignableFrom(fi.mFieldInfo.FieldType))
											{
												value = Enum.Parse(fi.mFieldInfo.FieldType, value.ToString());
											}
											else
											{
												value = Convert.ChangeType(value, fi.mFieldInfo.FieldType);
											}
                                           
                                            fi.mFieldInfo.SetValue(data, value);
                                        }
                                        break;
                                    default:
                                        {
                                            object value = Parser.StringToObject(icell.StringCellValue, fi.mFieldInfo.FieldType);
                                            fi.mFieldInfo.SetValue(data, value);
                                        }
                                        break;
                                }
                            }
                        }
                        ret.Add(data);
                    }
                    else
                    {
                        break;
                    }
                }
                return ret;
            }
            catch (Exception err)
            {
                throw new Exception(string.Format("LoadSheetError: 表名 = {0} 行 = {1} ，列= {2}, 列名={3}, 列内容={4} ,error = {5}",
					sheet.SheetName, ri + 1, ci + 1, rowName, rowContent, err.ToString()));
            }
        }
    }
}