Exemple #1
0
        public static List <T> ReadFromExcel <T>(string excelFilePath) where T : class, new()
        {
            List <T> entityList = new List <T>();

            var type = typeof(T);
            ExcelTableAttribute tableAttribute = (ExcelTableAttribute)type.GetCustomAttributes(typeof(ExcelTableAttribute), false)[0];
            var tableName  = tableAttribute.Name;
            var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            if (string.IsNullOrEmpty(tableName) || properties.Length == 0)
            {
                return(entityList);
            }

            Excel.Application app  = GetApplication();
            Excel.Workbook    book = app.Workbooks.Open(excelFilePath);
            for (int sheetIndex = 1; sheetIndex <= book.Sheets.Count; sheetIndex++)
            {
                Excel.Worksheet sheet = book.Sheets[sheetIndex];
                if (sheet.Name.Equals(tableName))
                {
                    var maxRow    = GetMaxRow(sheet);
                    var maxColumn = GetMaxColumn(sheet);

                    Excel.Range range = sheet.Range[ToColumnLabel(1) + 1, ToColumnLabel(maxColumn) + maxRow];

                    for (int row = 2; row <= maxRow; row++)
                    {
                        T entity = new T();
                        foreach (var property in type.GetProperties())
                        {
                            ExcelColumnAttribute columnAttribute = (ExcelColumnAttribute)property.GetCustomAttributes(typeof(ExcelColumnAttribute), false)[0];

                            Excel.Range cellRange = range[row, columnAttribute.ColumnIndex];

                            string cellText = cellRange.Text;

                            switch (property.PropertyType.ToString())
                            {
                            case "System.Int32":
                            {
                                int intValue = 0;
                                if (int.TryParse(cellRange.Text, out intValue))
                                {
                                    property.SetValue(entity, intValue, null);
                                }
                            }
                            break;

                            case "System.Int64":
                            {
                                long longValue = 0;
                                if (long.TryParse(cellRange.Text, out longValue))
                                {
                                    property.SetValue(entity, longValue, null);
                                }
                            }
                            break;

                            case "System.String":
                            {
                                property.SetValue(entity, cellRange.Text, null);
                            }
                            break;

                            default:
                                break;
                            }
                        }

                        entityList.Add(entity);
                    }
                    break;
                }
            }

            book.Close(false);
            app.Quit();

            return(entityList);
        }
Exemple #2
0
        /// <summary>
        /// 将数据写入excel文件并关闭excel文件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excelFilePath"></param>
        /// <param name="entityList"></param>
        public static void WriteToExcel <T>(string excelFilePath, List <T> entityList)
        {
            var type = typeof(T);
            ExcelTableAttribute tableAttribute = (ExcelTableAttribute)type.GetCustomAttributes(typeof(ExcelTableAttribute), false)[0];
            var tableName  = tableAttribute.Name;
            var properties = type.GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);

            if (string.IsNullOrEmpty(tableName) || properties.Length == 0)
            {
                return;
            }

            Excel.Application app = GetApplication();

            Excel.Workbook book;
            if (System.IO.File.Exists(excelFilePath))
            {
                book = app.Workbooks.Open(excelFilePath);
            }
            else
            {
                book = app.Workbooks.Add();
                book.SaveAs(excelFilePath);
            }

            for (int sheetIndex = 1; sheetIndex <= book.Sheets.Count; sheetIndex++)
            {
                Excel.Worksheet sheet = book.Sheets[sheetIndex];
                if (sheet.Name.Equals(tableName))
                {
                    book.Close(false);

                    return;
                }
            }

            Excel.Worksheet newWorkSheet = book.Worksheets.Add();
            newWorkSheet.Name = tableName;

            var startCell = ToColumnLabel(1) + 1;
            var endCell   = ToColumnLabel(properties.Length) + (entityList.Count + 1);

            Excel.Range range = newWorkSheet.Range[startCell, endCell];
            foreach (var property in properties)
            {
                ExcelColumnAttribute columnAttribute = (ExcelColumnAttribute)property.GetCustomAttributes(typeof(ExcelColumnAttribute), false)[0];

                Excel.Range titleCellRange = range[1, columnAttribute.ColumnIndex];
                titleCellRange.Value = columnAttribute.Name;
            }

            foreach (var property in properties)
            {
                ExcelColumnAttribute columnAttribute = (ExcelColumnAttribute)property.GetCustomAttributes(typeof(ExcelColumnAttribute), false)[0];

                for (int row = 0; row < entityList.Count; row++)
                {
                    Excel.Range valueCellRange = range[row + 2, columnAttribute.ColumnIndex];
                    switch (columnAttribute.ExcelCellFormat)
                    {
                    case ExcelCellFormat.Default:
                    {
                        switch (property.PropertyType.ToString())
                        {
                        case "System.String":
                        {
                            valueCellRange.NumberFormatLocal = "@";
                        }
                        break;

                        default:
                            break;
                        }
                    }
                    break;

                    case ExcelCellFormat.Text:
                    {
                        valueCellRange.NumberFormatLocal = "@";
                    }
                    break;

                    case ExcelCellFormat.Number2:
                    {
                        valueCellRange.NumberFormatLocal = "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * \"-\"??_ ;_ @_ ";
                    }
                    break;

                    default:
                        break;
                    }

                    valueCellRange.Value = property.GetValue(entityList[row], null);
                }
            }
            book.Save();
            book.Close();
            app.Quit();
        }