示例#1
0
        public void CreateExcelFile(string filePath)
        {
            DatabaseHelper db = new DatabaseHelper();

            db.TestConnection();
            DataTable tbTable  = db.GetAllTable();
            DataTable tbColumn = db.GetAllColumn();
            //
            ExcelPackage _excelPkg = new ExcelPackage();

            for (int i = 0; i < tbTable.Rows.Count; i++)
            {
                var _columnList = db.GetColumnInfor(tbTable.Rows[i][TableName.NAME].ToString().ToUpper());
                ExcelContentModel _excelModel = CreateExcelModel(tbTable.Rows[i][TableName.NAME].ToString().ToUpper(), _columnList);
                CreateSheet(_excelPkg, _excelModel);
            }
            //
            _excelPkg.SaveAs(new FileInfo(filePath));
            //
            Console.WriteLine("Finish");
            OpenExcelFile();
        }
示例#2
0
        private ExcelContentModel CreateExcelModel(string sheetName, DataTable data)
        {
            ExcelContentModel _return = new ExcelContentModel();

            _return.SheetName              = sheetName;
            _return.ShowGridLines          = false;
            _return.IsProtected            = false;
            _return.AllowSelectLockedCells = false;

            int _row = 7;

            #region " [ Data ] "

            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = 3,
                Col   = 2,
                Value = "Nhóm",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = 4,
                Col   = 2,
                Value = "Tên vật lý",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = 5,
                Col   = 2,
                Value = "Tên logic",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 2,
                Value = "STT",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 3,
                Value = "Column name",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 4,
                Value = "Physical name",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 5,
                Value = "Primary key",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 6,
                Value = "Data type",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 7,
                Value = "Data length",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 8,
                Value = "Allow null",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 9,
                Value = "Index",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 10,
                Value = "Indentity",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 11,
                Value = "Init value",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 12,
                Value = "Unique",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 13,
                Value = "Foreign key",
                Bold  = false
            });
            _return.Data.Add(new ExcelDataDetailModel()
            {
                Row   = _row,
                Col   = 14,
                Value = "Memo",
                Bold  = false
            });

            for (int i = 0; i < data.Rows.Count; i++)
            {
                _return.Data.Add(new ExcelDataDetailModel()
                {
                    Row   = _row + i + 1,
                    Col   = 2,
                    Value = (i + 1 + 1000)
                });
                _return.Data.Add(new ExcelDataDetailModel()
                {
                    Row   = _row + i + 1,
                    Col   = 4,
                    Value = data.Rows[i][ColumnName.ColName].ToString()
                });
                _return.Data.Add(new ExcelDataDetailModel()
                {
                    Row   = _row + i + 1,
                    Col   = 5,
                    Value = data.Rows[i][ColumnName.PrimaryKey].ToString()
                });
                _return.Data.Add(new ExcelDataDetailModel()
                {
                    Row   = _row + i + 1,
                    Col   = 6,
                    Value = data.Rows[i][ColumnName.DataType].ToString()
                });
                _return.Data.Add(new ExcelDataDetailModel()
                {
                    Row   = _row + i + 1,
                    Col   = 7,
                    Value = data.Rows[i][ColumnName.MaxLength]
                });
                _return.Data.Add(new ExcelDataDetailModel()
                {
                    Row   = _row + i + 1,
                    Col   = 8,
                    Value = data.Rows[i][ColumnName.IsNull].ToString()
                });
                _return.Data.Add(new ExcelDataDetailModel()
                {
                    Row   = _row + i + 1,
                    Col   = 10,
                    Value = data.Rows[i][ColumnName.Identity].ToString()
                });
                _return.Data.Add(new ExcelDataDetailModel()
                {
                    Row   = _row + i + 1,
                    Col   = 11,
                    Value = data.Rows[i][ColumnName.Default].ToString()
                });
                _return.Data.Add(new ExcelDataDetailModel()
                {
                    Row   = _row + i + 1,
                    Col   = 12,
                    Value = data.Rows[i][ColumnName.Unique].ToString()
                });
                _return.Data.Add(new ExcelDataDetailModel()
                {
                    Row   = _row + i + 1,
                    Col   = 13,
                    Value = data.Rows[i][ColumnName.ForeignKey].ToString()
                });
            }

            #endregion

            #region " [ Merge ] "

            _return.Merges.Add(new ExcelMergeDetailModel()
            {
                FromRow    = 3,
                FromColumn = 3,
                ToRow      = 3,
                ToColumn   = 5
            });
            _return.Merges.Add(new ExcelMergeDetailModel()
            {
                FromRow    = 4,
                FromColumn = 3,
                ToRow      = 4,
                ToColumn   = 5
            });
            _return.Merges.Add(new ExcelMergeDetailModel()
            {
                FromRow    = 5,
                FromColumn = 3,
                ToRow      = 5,
                ToColumn   = 5
            });

            #endregion

            #region " [ Horizontal ] "

            _return.Horizontals.Add(new ExcelHorizontalDetailModel()
            {
                FromRow    = 3,
                FromColumn = 2,
                ToRow      = 5,
                ToColumn   = 5,
                Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left
            });
            _return.Horizontals.Add(new ExcelHorizontalDetailModel()
            {
                FromRow    = _row,
                FromColumn = 2,
                ToRow      = _row,
                ToColumn   = 14,
                Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
            });
            _return.Horizontals.Add(new ExcelHorizontalDetailModel()
            {
                FromRow    = _row + 1,
                FromColumn = 2,
                ToRow      = _row + data.Rows.Count + 1,
                ToColumn   = 2,
                Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
            });
            _return.Horizontals.Add(new ExcelHorizontalDetailModel()
            {
                FromRow    = _row + 1,
                FromColumn = 5,
                ToRow      = _row + data.Rows.Count + 1,
                ToColumn   = 5,
                Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
            });
            _return.Horizontals.Add(new ExcelHorizontalDetailModel()
            {
                FromRow    = _row + 1,
                FromColumn = 7,
                ToRow      = _row + data.Rows.Count + 1,
                ToColumn   = 7,
                Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
            });
            _return.Horizontals.Add(new ExcelHorizontalDetailModel()
            {
                FromRow    = _row + 1,
                FromColumn = 8,
                ToRow      = _row + data.Rows.Count + 1,
                ToColumn   = 8,
                Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
            });
            _return.Horizontals.Add(new ExcelHorizontalDetailModel()
            {
                FromRow    = _row + 1,
                FromColumn = 9,
                ToRow      = _row + data.Rows.Count + 1,
                ToColumn   = 9,
                Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
            });
            _return.Horizontals.Add(new ExcelHorizontalDetailModel()
            {
                FromRow    = _row + 1,
                FromColumn = 10,
                ToRow      = _row + data.Rows.Count + 1,
                ToColumn   = 10,
                Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
            });
            _return.Horizontals.Add(new ExcelHorizontalDetailModel()
            {
                FromRow    = _row + 1,
                FromColumn = 11,
                ToRow      = _row + data.Rows.Count + 1,
                ToColumn   = 11,
                Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
            });
            _return.Horizontals.Add(new ExcelHorizontalDetailModel()
            {
                FromRow    = _row + 1,
                FromColumn = 12,
                ToRow      = _row + data.Rows.Count + 1,
                ToColumn   = 12,
                Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
            });

            #endregion

            #region " [ Vertical ] "

            #endregion

            #region " [ Border ] "

            _return.Borders.Add(new ExcelBorderDetailModel()
            {
                FromRow    = 3,
                FromColumn = 2,
                ToRow      = 5,
                ToColumn   = 5,
                Border     = OfficeOpenXml.Style.ExcelBorderStyle.Thin
            });

            _return.Borders.Add(new ExcelBorderDetailModel()
            {
                FromRow    = _row,
                FromColumn = 2,
                ToRow      = _row + data.Rows.Count + 1,
                ToColumn   = 14,
                Border     = OfficeOpenXml.Style.ExcelBorderStyle.Thin
            });

            #endregion

            #region " [ Formatting ] "

            _return.Formatings.Add(new ExcelFromatingDetailModel()
            {
                FromRow    = _row + 1,
                FromColumn = 2,
                ToRow      = _row + data.Rows.Count + 1,
                ToColumn   = 2,
                Format     = "#,##0"
            });

            #endregion

            return(_return);
        }
示例#3
0
        private ExcelWorksheet CreateSheet(ExcelPackage excelPackage, ExcelContentModel excelModel)
        {
            ExcelWorksheet _sheet = excelPackage.Workbook.Worksheets.Add(excelModel.SheetName);

            //Data content
            foreach (var item in excelModel.Data)
            {
                _sheet.Cells[item.Row, item.Col].Value = item.Value;
                if (item.Bold)
                {
                    _sheet.Cells[item.Row, item.Col].Style.Font.Bold = item.Bold;
                }
            }
            //Horizontal formating
            foreach (var item in excelModel.Horizontals)
            {
                using (ExcelRange range = _sheet.Cells[item.FromRow, item.FromColumn, item.ToRow, item.ToColumn])
                {
                    range.Style.HorizontalAlignment = item.Horizontal;
                }
            }
            //Vertical formating
            foreach (var item in excelModel.Verticals)
            {
                using (ExcelRange range = _sheet.Cells[item.FromRow, item.FromColumn, item.ToRow, item.ToColumn])
                {
                    range.Style.VerticalAlignment = item.Vertical;
                }
            }
            //Merge
            foreach (var item in excelModel.Merges)
            {
                using (ExcelRange range = _sheet.Cells[item.FromRow, item.FromColumn, item.ToRow, item.ToColumn])
                {
                    range.Merge = true;
                }
            }
            //Border
            foreach (var item in excelModel.Borders)
            {
                using (ExcelRange range = _sheet.Cells[item.FromRow, item.FromColumn, item.ToRow, item.ToColumn])
                {
                    range.Style.Border.Top.Style    = item.Border;
                    range.Style.Border.Right.Style  = item.Border;
                    range.Style.Border.Bottom.Style = item.Border;
                    range.Style.Border.Left.Style   = item.Border;
                }
            }
            //Set format text for all sheet
//            _sheet.Cells.Style.Numberformat.Format = "@";
            //Format specific cell
            foreach (var item in excelModel.Formatings)
            {
                _sheet.Cells[item.FromRow, item.FromColumn, item.ToRow, item.ToColumn].Style.Numberformat.Format = item.Format;
            }
            //
            _sheet.Cells.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
            _sheet.Cells.AutoFitColumns();
            _sheet.View.ShowGridLines                = excelModel.ShowGridLines;
            _sheet.Protection.IsProtected            = excelModel.IsProtected;
            _sheet.Protection.AllowSelectLockedCells = excelModel.AllowSelectLockedCells;
            return(_sheet);
        }