Demo

Jan 29, 2013 at 8:34 AM
Edited Jan 29, 2013 at 8:35 AM

 

using System;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using Inz.Forms;
using Inz.Identity;
using Inz.Reports;
using OfficeOpenXml;
using OfficeOpenXml.Style;
        private bool hdr = false;
        private bool frt = false;
        private bool hdc = false;
        private int firstRowIndex
        {
            get
            {
                int result = 1;

                if (hdr && frt)
                    result = 3;
                else if (hdr || frt)
                    result = 2;
                else if (!hdr && !frt)
                    result = 1;

                return result;
            }
        }
        private int firstColIndex
        {
            get
            {
                int result = 1;

                if (hdc)
                    result = 2;
                else
                    result = 1;

                return result;
            }
        }
        string _reportTitle = string.Empty;
        string _reportDescpiption = string.Empty;
        protected DataTable _reportDataTable;
        protected ReportDataSet _reportDataSet;
        public ReportDataSet ReportDSet
        {
            get { return this._reportDataSet; }
            set
            {
                value.DataSetName = this._reportTitle;
                value.ExtendedProperties.Add("_reportDescpiption", this._reportDescpiption);
                this._reportDataSet = value;

                this._reportDataTable = this._reportDataSet.Tables[0];
                this._reportDataTable.TableName = this._reportTitle;
                this._reportDataTable.ExtendedProperties.Add("_reportDescpiption", this._reportDescpiption);
            }
        }
        public DataTable ReportDTable
        {
            get { return this._reportDataSet.Tables[0]; }
            set
            {
                this._reportDataTable = value;

                if (this._reportDataSet.Tables[0] != null)
                {
                    this._reportDataSet.Tables[0].Clear();
                    foreach (DataColumn item in value.Columns)
                    {
                        this._reportDataSet.Tables[0].Columns.Add(item);
                    }
                    foreach (DataRow item in value.Rows)
                    {
                        this._reportDataSet.Tables[0].Rows.Add(item);
                    }
                }
                else
                {
                    value.TableName = this._reportTitle;
                    value.ExtendedProperties.Add("_reportDescpiption", this._reportDescpiption);

                    this._reportDataSet.Tables.Add(value);
                }
            }
        }
        public string ReportTitle
        {
            get { return this._reportTitle; }
            set
            {
                this._reportTitle = value;
                this._reportDataSet.DataSetName = this._reportTitle;
            }
        }
        public string ReportDescription
        {
            get { return this._reportDescpiption; }
            set
            {
                this._reportDescpiption = value;
                if (this._reportDataSet.ExtendedProperties.Count == 0 || !this._reportDataSet.ExtendedProperties.Contains("_reportDescpiption"))
                {
                    this._reportDataSet.ExtendedProperties.Add("_reportDescpiption", this._reportDescpiption);
                }
                else
                {
                    this._reportDataSet.ExtendedProperties["_reportDescpiption"] = this._reportDescpiption;
                }
            }
        }
        
        public int RowsCount
        {
            get { return this._reportDataTable.Rows.Count; }
        }
        public int ColumnsCount
        {
            get { return this._reportDataTable.Columns.Count; }
        }

 

 

private string EscapeSheetName(string sheetName)
        {
            var escapedSheetName = sheetName
                                        .Replace("/", "-")
                                        .Replace("\\", " ")
                                        .Replace("?", string.Empty)
                                        .Replace("*", string.Empty)
                                        .Replace("[", string.Empty)
                                        .Replace("]", string.Empty)
                                        .Replace(":", string.Empty);

            if (escapedSheetName.Length > MaximumSheetNameLength)
                escapedSheetName = escapedSheetName.Substring(0, MaximumSheetNameLength);

            return escapedSheetName;
        }
        private string SetNumberFormat(Type type)
        {
            string result = "@";

            if (type == typeof(string))
                result = "@";
            else if (type == typeof(sbyte))
                result = "##0";
            else if (type == typeof(byte))
                result = "##0";
            else if (type == typeof(bool))
                result = "0";
            else if (type == typeof(short))
                result = "####0";
            else if (type == typeof(ushort))
                result = "####0";
            else if (type == typeof(int))
                result = "#,##0";
            else if (type == typeof(uint))
                result = "#,##0";
            else if (type == typeof(long))
                result = "#,##0";
            else if (type == typeof(ulong))
                result = "#,##0";
            else if (type == typeof(float))
                result = ".#######";
            else if (type == typeof(double))
                result = ".################";
            else if (type == typeof(decimal))
                result = "₪ .00##########################";
            else if (type == typeof(DateTime))
                result = "dd/mm/yyyy";

            return result;
        }
        private bool ExcelBuilder(ExcelPackage excelPackage)
        {
            hdr = false;
            frt = false;
            hdc = false;

            #region ExcelPackageSetup
            //Step 4 : (Optional) Set the file properties like title, author and subject
            excelPackage.Workbook.Properties.Title = this._reportTitle;
            excelPackage.Workbook.Properties.Author = "Inz";
            excelPackage.Workbook.Properties.Subject = this._reportDescpiption;
            excelPackage.Workbook.Properties.Company = "Inz";
            #endregion

            #region ExcelWorksheetSetup
            //Step 2 : Add a new worksheet to ExcelPackage object and give a suitable name
            excelPackage.Workbook.Worksheets.Add(this.EscapeSheetName(this._reportTitle));
            ExcelWorksheet ws = excelPackage.Workbook.Worksheets[1];
            //Setting Sheet's name
            ws.Name = this._reportDataTable.TableName;
            //ws.BackgroundImage.SetFromFile(new FileInfo("SemelInzBlack.png"));

            //Default style for whole sheet
            //ws.Cells.Style.Font.SetFromFont(new Font("Calibri", 11, FontStyle.Regular));
            ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
            ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet
            ws.Cells.Style.Font.Color.SetColor(Color.Black);
            ws.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
            ws.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            //Setting borders of cell
            ws.Cells.Style.Border.Left.Style = ws.Cells.Style.Border.Right.Style = ExcelBorderStyle.None;
            ws.Cells.Style.Numberformat.Format = "General";

            //Step 5 : Save all changes to ExcelPackage object which will create Excel 2007 file.
            // lets set the header text 
            ws.HeaderFooter.OddHeader.CenteredText = "&" + 11 + "&U&\"" + "Calibri" + ",Regular Bold\" " + ws.Name;
            // add the page number to the footer plus the total number of pages
            ws.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
            // add the sheet name to the footer
            ws.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
            // add the file path to the footer
            //ws.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
            // Set RTL
            ws.View.RightToLeft = true;
            ws.View.ShowGridLines = true;
            ws.View.ShowHeaders = true;
            #endregion

            #region CenterTopTitle
            //Merging cells and create a center heading for out table
            ws.Cells[firstRowIndex, firstColIndex].Value = this.ReportDescription;
            ws.Cells[firstRowIndex, firstColIndex, 1, this.ColumnsCount].Merge = true;
            ws.Cells[firstRowIndex, firstColIndex, 1, this.ColumnsCount].Style.Font.Bold = true;
            ws.Cells[firstRowIndex, firstColIndex, 1, this.ColumnsCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            frt = true;
            #endregion

            #region Setting Headers
            int colIndex = firstColIndex;
            int rowIndex = firstRowIndex;

            //Creating Headings
            foreach (DataColumn dc in this._reportDataTable.Columns)
            {
                var cell = ws.Cells[rowIndex, colIndex];

                //Setting font of header cells
                var font = cell.Style.Font;
                font.SetFromFont(new Font("Calibri", 11, FontStyle.Bold));
                font.Color.SetColor(Color.White);

                //Setting the background color of header cells to Gray
                var fill = cell.Style.Fill;
                fill.PatternType = ExcelFillStyle.Solid;
                fill.BackgroundColor.SetColor(Color.CadetBlue);


                //Setting Top/left,right/bottom borders.
                var border = cell.Style.Border;
                border.Bottom.Style =
                    border.Top.Style =
                    border.Left.Style =
                    border.Right.Style = ExcelBorderStyle.Double;

                //Setting Value in cell
                cell.Value = /*"pre_" +*/ dc.ColumnName;

                colIndex++;
            }

            hdr = true;

            #endregion

            #region LoadingData
            //ws.Cells["A3"].LoadFromDataTable(this._reportDataTable, false, TableStyles.Dark1);

            colIndex = firstColIndex;
            rowIndex = firstRowIndex;

            foreach (DataRow dr in this._reportDataTable.Rows) // Adding Data into rows
            {
                foreach (object value in dr.ItemArray)
                {
                    var cell = ws.Cells[rowIndex, colIndex];
                    //Setting Value in cell
                    cell.Value = value; // Convert.ChangeType(value, value.GetType());

                    var style = cell.Style;
                    style.Numberformat.Format = SetNumberFormat(value.GetType());
                    // this.dataFormats[colIndex - 1];
                    //style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    //style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    //style.Font.SetFromFont(new Font("Calibri", 11, FontStyle.Regular));
                    //style.Font.Color.SetColor(Color.Black);

                    ////Setting borders of cell
                    //var border = cell.Style.Border;
                    //border.Left.Style =
                    //    border.Right.Style = ExcelBorderStyle.Thin;
                    colIndex++;
                }
                colIndex = firstColIndex;
                rowIndex++;

            }

            #endregion

            //Create an autofilter for the range
            if (this.RowsCount > 0 && this.ColumnsCount > 0)
            {
                ws.Cells[(firstRowIndex - 1), firstColIndex, ((firstRowIndex - 1) + this.RowsCount), this.ColumnsCount].AutoFilter = true;
            }

            //Autofit columns for all cells            
            ws.Cells.AutoFitColumns(0);

            ExcelPrinterSettings ps = ws.PrinterSettings;
            ps.BlackAndWhite = true;
            ps.Draft = true;
            ps.FitToPage = true;
            ps.PaperSize = ePaperSize.A4;
            ps.PrintArea = ws.Cells[1, 1, (this.RowsCount - 1) + this.firstRowIndex, this.ColumnsCount];

            return true;
        }