예제 #1
0
        /// <summary>
        /// Generates a cell style for date formatting.
        /// </summary>
        /// <param name="book">excel sheet.</param>
        /// <param name="options">excel sheet options.</param>
        /// <returns>cell style.</returns>
        private ICellStyle createCellStyleForDateTime(IWorkbook book, ExcelSpreadSheetOptions options)
        {
            var style = book.CreateCellStyle();

            style.DataFormat = book.CreateDataFormat().GetFormat(options.DataTimeCellFormat.Format);
            return(style);
        }
예제 #2
0
        /// <summary>
        /// Export to a specified spreadsheet.
        /// </summary>
        /// <typeparam name="T">POCO with no internal List, Array or Class</typeparam>
        /// <param name="exportCollections">export collections</param>
        /// <param name="options">excel options.</param>
        /// <exception cref="ArgumentNullException"></exception>
        /// <exception cref="IOException"></exception>
        public void Export <T>(IList <T> exportCollections, ISpreadSheetOptions options = null)
        {
            if (exportCollections == null)
            {
                throw new ArgumentNullException(nameof(exportCollections));
            }

            if (!exportCollections.Any())
            {
                return;
            }

            if (File.Exists(filePath))
            {
                throw new IOException($"{filePath} already exists.");
            }

            if (options == null)
            {
                options = new ExcelSpreadSheetOptions();
            }

            ExcelSpreadSheetOptions excelOpt = null;

            if (options != null)
            {
                if (!(options is ExcelSpreadSheetOptions))
                {
                    throw new ArgumentException($"The only Options that this class can receive are {nameof(ExcelSpreadSheetOptions)}.");
                }

                excelOpt = options as ExcelSpreadSheetOptions;
            }

            IWorkbook book;
            var       extension = Path.GetExtension(filePath);

            if (extension == HSSF_EXTENSION)
            {
                book = new HSSFWorkbook();
            }
            else if (extension == XSSF_EXTENSION)
            {
                book = new XSSFWorkbook();
            }
            else
            {
                throw new ApplicationException($@"Invalid extension has been specified. Valid extensions are either '{HSSF_EXTENSION}' or '{XSSF_EXTENSION}'.");
            }

            if (TypeUtil.IsPrimitive(typeof(T)))
            {
                PrimitiveTypeExport(book, exportCollections, excelOpt);
            }
            else
            {
                ClassTypeExport(book, exportCollections, excelOpt);
            }

            using (var fs = new FileStream(filePath, FileMode.Create))
            {
                book.Write(fs);
            }
        }
예제 #3
0
        /// <summary>
        /// Outputs class type.
        /// </summary>
        /// <typeparam name="T">any class types.</typeparam>
        /// <param name="book">excel sheet.</param>
        /// <param name="exportCollections">export any collections.</param>
        /// <param name="options">excel sheet options.</param>
        protected virtual void ClassTypeExport <T>(IWorkbook book, IList <T> exportCollections, ExcelSpreadSheetOptions options)
        {
            var rowIndex    = 0;
            var columnIndex = 0;
            var gType       = typeof(T);

            if (options != null)
            {
                rowIndex    = (int)options.StartRowIndex;
                columnIndex = (int)options.StartColumnIndex;
            }

            var sheet = book.CreateSheet(settings.SheetName);
            var style = createCellStyleForDateTime(book, options);

            var hColIndex = columnIndex;

            if (ReadHeaderInfo.Property == settings.ReadHeaderInfo || ReadHeaderInfo.PropertyAndField == settings.ReadHeaderInfo)
            {
                foreach (var prop in gType.GetProperties())
                {
                    var row  = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                    var cell = row.GetCell(hColIndex) ?? row.CreateCell(hColIndex);
                    cell.SetCellValue(prop.Name);
                    hColIndex++;
                }
            }
            if (ReadHeaderInfo.Field == settings.ReadHeaderInfo || ReadHeaderInfo.PropertyAndField == settings.ReadHeaderInfo)
            {
                foreach (var field in gType.GetFields())
                {
                    var row  = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                    var cell = row.GetCell(hColIndex) ?? row.CreateCell(hColIndex);
                    cell.SetCellValue(field.Name);
                    hColIndex++;
                }
            }

            rowIndex = rowIndex + 1;
            foreach (var value in exportCollections)
            {
                var colIndex = columnIndex;
                if (ReadHeaderInfo.Property == settings.ReadHeaderInfo || ReadHeaderInfo.PropertyAndField == settings.ReadHeaderInfo)
                {
                    foreach (var prop in gType.GetProperties())
                    {
                        setCellValue(sheet: sheet, style: style, rowIndex: rowIndex, columnIndex: colIndex, prop.GetValue(value));
                        colIndex++;
                    }
                }
                if (ReadHeaderInfo.Field == settings.ReadHeaderInfo || ReadHeaderInfo.PropertyAndField == settings.ReadHeaderInfo)
                {
                    foreach (var field in gType.GetFields())
                    {
                        setCellValue(sheet: sheet, style: style, rowIndex: rowIndex, columnIndex: colIndex, field.GetValue(value));
                        colIndex++;
                    }
                }
                rowIndex++;
            }
        }
예제 #4
0
        /// <summary>
        /// Outputs primitive type.
        /// </summary>
        /// <typeparam name="T">any primitive types.</typeparam>
        /// <param name="book">excel sheet.</param>
        /// <param name="exportCollections">export any collections.</param>
        /// <param name="options">excel sheet options.</param>
        protected virtual void PrimitiveTypeExport <T>(IWorkbook book, IList <T> exportCollections, ExcelSpreadSheetOptions options)
        {
            var rowIndex    = 0;
            var columnIndex = 0;

            if (options != null)
            {
                rowIndex    = (int)options.StartRowIndex;
                columnIndex = (int)options.StartColumnIndex;
            }

            var sheet = book.CreateSheet(settings.SheetName);
            var style = createCellStyleForDateTime(book, options);

            foreach (var value in exportCollections)
            {
                setCellValue(sheet: sheet, style: style, rowIndex: rowIndex, columnIndex: columnIndex, value);
                rowIndex++;
            }
        }