コード例 #1
0
ファイル: ExcelHelper.cs プロジェクト: CaoQu1/Export
 /// <summary>
 /// Execl文件转内存表
 /// </summary>
 /// <param name="path"></param>
 /// <returns></returns>
 public static DataTable ExcelToTable <T>(string path)
 {
     if (string.IsNullOrEmpty(path))
     {
         throw new ArgumentNullException($"{path}路径为空!");
     }
     if (!File.Exists(path))
     {
         throw new ArgumentNullException($"{path}路径的文件不存在!");
     }
     try
     {
         DataTable      dataTable      = new DataTable();
         Type           tType          = typeof(T);
         string         tableName      = tType.Name;
         TableAttribute tableAttribute = tType.GetCustomAttribute <TableAttribute>();
         SheetAttribute sheetAttribute = tType.GetCustomAttribute <SheetAttribute>();
         if (tableAttribute != null)
         {
             tableName = tableAttribute.Name;
         }
         dataTable.TableName = tableName;
         int          startIndex   = 0;
         RowAttribute rowAttribute = tType.GetCustomAttribute <RowAttribute>();
         if (rowAttribute != null)
         {
             startIndex = rowAttribute.StartRow;
         }
         PropertyInfo[] properties = tType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
         dataTable.Columns.AddRange(properties.Select(x =>
         {
             string columnName = x.Name;
             TableColumnAttribute tableColumnAttribute = x.GetCustomAttribute <TableColumnAttribute>();
             if (tableColumnAttribute != null)
             {
                 columnName = tableColumnAttribute.Name;
             }
             return(new DataColumn(columnName, x.PropertyType));
         }).ToArray());
         var          fileStream       = new FileStream(path, FileMode.Open, FileAccess.Read);
         IWorkbook    workbook         = null;
         HSSFWorkbook hSSFWorkbook2007 = null;
         XSSFWorkbook xSSFWorkbook2003 = null;
         try
         {
             hSSFWorkbook2007 = new HSSFWorkbook(fileStream);
             workbook         = (IWorkbook)hSSFWorkbook2007;
         }
         catch (Exception ex)
         {
             fileStream       = new FileStream(path, FileMode.Open, FileAccess.Read);
             xSSFWorkbook2003 = new XSSFWorkbook(fileStream);
             workbook         = (IWorkbook)xSSFWorkbook2003;
             System.Diagnostics.Trace.TraceError(ex.Message);
         }
         if (sheetAttribute != null)
         {
             tableName = sheetAttribute.Name;
         }
         ISheet sheet = workbook.GetSheet(tableName);
         for (int j = startIndex; j < sheet.LastRowNum; j++)
         {
             IRow row = sheet.GetRow(j);
             if (row == null || IsRowEmpty(row))
             {
                 continue;
             }
             else
             {
                 DataRow newRow = dataTable.NewRow();
                 foreach (PropertyInfo property in properties)
                 {
                     ColumnAttribute      columnAttribute      = property.GetCustomAttribute <ColumnAttribute>();
                     TableColumnAttribute tableColumnAttribute = property.GetCustomAttribute <TableColumnAttribute>();
                     if (tableColumnAttribute != null)
                     {
                         newRow[tableColumnAttribute.Name] = GetCellValue(row, property, tableColumnAttribute, columnAttribute);
                     }
                 }
                 dataTable.Rows.Add(newRow);
             }
         }
         workbook.Close();
         fileStream.Close();
         return(dataTable);
     }
     catch (Exception ex)
     {
         System.Diagnostics.Trace.TraceError(ex.Message);
         return(null);
     }
 }
コード例 #2
0
ファイル: ExcelHelper.cs プロジェクト: CaoQu1/Export
        /// <summary>
        /// 获取列值
        /// </summary>
        /// <param name="row"></param>
        /// <param name="property"></param>
        /// <param name="tableColumnAttribute"></param>
        /// <param name="columnAttribute"></param>
        /// <returns></returns>
        private static object GetCellValue(IRow row, PropertyInfo property, TableColumnAttribute tableColumnAttribute, ColumnAttribute columnAttribute)
        {
            object value = null;

            if (!string.IsNullOrEmpty(tableColumnAttribute.TableColumnProcess))
            {
                ColumnProcess(tableColumnAttribute.TableColumnProcess, "", ref value);
            }
            else if (columnAttribute != null)
            {
                ICell cell = row.GetCell(columnAttribute.Index);
                if (cell != null && cell.CellType != CellType.Blank)
                {
                    if (!string.IsNullOrEmpty(columnAttribute.ColumnProcess))
                    {
                        ColumnProcess(columnAttribute.ColumnProcess, cell.StringCellValue, ref value);
                    }
                    else
                    {
                        value = GetCellValue(cell, property);
                    }
                }
            }
            return(value);
        }