Пример #1
0
        public async Task <IActionResult> DownloadProductionDataAsync(ProductionSheetData productiondata)
        {
            if (productiondata == null)
            {
                throw new ArgumentNullException(nameof(productiondata));
            }
            else
            {
                string fileName = Path.GetTempFileName();
                var    data     = productionDataSheetWriter.GenerateExcelSheet(productiondata);
                SpreadsheetWriter.Write(fileName, data);

                var memory = new MemoryStream();



                using (var fileStream = new FileStream(fileName, FileMode.Open))
                {
                    await fileStream.CopyToAsync(memory);
                }

                memory.Position = 0;

                Response.Headers.Add("Content-Disposition",
                                     "attachment; filename=ExcelFile.xlsx");
                return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
            }
        }
Пример #2
0
        protected async override void OnExecute(object parameter)
        {
            try
            {
                if (parameter is ISelector selector && selector.SelectedItem is ListBoxItemViewModel viewModel && viewModel.Target is IDataBase dataBase)
                {
                    if (viewModel is IInfoProvider provider)
                    {
                        var props        = provider.Info;
                        var revision     = (string)props["Revision"];
                        var dataBaseName = await dataBase.Dispatcher.InvokeAsync(() => dataBase.Name);

                        var dialog = new CommonSaveFileDialog();
                        dialog.Filters.Add(new CommonFileDialogFilter("excel file", "*.xlsx"));
                        dialog.DefaultFileName  = $"{dataBaseName}_{revision}";
                        dialog.DefaultExtension = "xlsx";

                        if (dialog.ShowDialog() == CommonFileDialogResult.Ok)
                        {
                            var dataSet = await dataBase.GetDataSetAsync(this.authenticator, CremaDataSetFilter.Default, revision);

                            var writer = new SpreadsheetWriter(dataSet);
                            writer.Write(dialog.FileName);
                            await AppMessageBox.ShowErrorAsync(Resources.Message_Exported);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                await AppMessageBox.ShowErrorAsync(e);
            }
        }
Пример #3
0
        protected async override void OnExecute(object parameter)
        {
            try
            {
                if (parameter is ListBoxItemViewModel viewModel && viewModel.Target is ITableItem tableItem)
                {
                    if (viewModel is IInfoProvider provider)
                    {
                        var props     = provider.Info;
                        var revision  = (long)props["Revision"];
                        var tableName = await tableItem.Dispatcher.InvokeAsync(() => tableItem.Name);

                        var dialog = new CommonSaveFileDialog();
                        dialog.Filters.Add(new CommonFileDialogFilter("excel file", "*.xlsx"));
                        dialog.DefaultFileName  = $"{tableName}_{revision}";
                        dialog.DefaultExtension = "xlsx";

                        if (dialog.ShowDialog() == CommonFileDialogResult.Ok)
                        {
                            var dataSet = await tableItem.Dispatcher.InvokeAsync(() => tableItem.GetDataSet(this.authenticator, revision));

                            var writer = new SpreadsheetWriter(dataSet);
                            writer.Write(dialog.FileName);
                            AppMessageBox.Show(Resources.Message_Exported);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                AppMessageBox.ShowError(e);
            }
        }
Пример #4
0
        public void Write(IEnumerable <IRow> rows)
        {
            foreach (var row in rows)
            {
                var cellDfns = new List <CellDfn>();
                var rowDef   = new RowDfn {
                    Cells = cellDfns
                };
                cellDfns.AddRange(_fields.Select(field => new CellDfn {
                    CellDataType = field.ToCellDataType(),
                    Value        = field.Type == "guid" ? row[field].ToString() : row[field],
                    FormatCode   = field.Format.Replace("tt", "AM/PM")
                }));
                _rowDfns.Add(rowDef);
            }

            SpreadsheetWriter.Write(_fileInfo.FullName, _workbook);
        }
Пример #5
0
        public void Export(string itemPath, CremaDataSet dataSet)
        {
            if (dataSet.Tables.Any() == false)
            {
                return;
            }

            var spreadsheetWriterSettings = new SpreadsheetWriterSettings()
            {
                OmitAttribute     = this.settings.OmitAttribute,
                OmitSignatureDate = this.settings.OmitSignatureDate,
            };

            var writer   = new SpreadsheetWriter(dataSet, spreadsheetWriterSettings);
            var filename = this.OutputPath;

            if (this.settings.IsSeparable == true)
            {
                var outputPath = itemPath.Trim(PathUtility.SeparatorChar).Replace(PathUtility.SeparatorChar, '.');
                if (outputPath == string.Empty)
                {
                    outputPath = this.cremaAppHost.DataBaseName;
                }
                if (this.settings.IsIncludeDate)
                {
                    //------------------------------------------------------
                    // TableName_2017-02-17_05_11.xlsx
                    string szDate = DateTime.Now.ToString($"{this.settings.OutputDateFormat}");
                    filename = FileUtility.Prepare(this.outputPath, $"{outputPath}_{szDate}.xlsx");
                }
                else
                {
                    filename = FileUtility.Prepare(this.outputPath, outputPath + ".xlsx");
                }
            }

            using (var stream = new FileStream(filename, FileMode.Create))
            {
                writer.Write(stream);
            }
            this.configs.Commit(this.settings);
            this.configs.Commit(this);
        }
Пример #6
0
        private void WriteTables(CremaDataSet dataSet, string filename)
        {
            var path     = Path.Combine(this.CommandContext.BaseDirectory, filename);
            var settings = new SpreadsheetWriterSettings()
            {
                OmitAttribute     = this.OmitAttribute,
                OmitSignatureDate = this.OmitSignatureDate,
                Tags = (TagInfo)TagsProperties.Tags,
            };
            var progress = new ConsoleProgress(this.Out)
            {
                Style = ConsoleProgressStyle.None
            };

            using (var writer = new SpreadsheetWriter(dataSet, settings))
            {
                writer.Write(path, progress);
            }
            this.Out.WriteLine($"export: \"{path}\"");
        }
Пример #7
0
        public void ExportToExcel()
        {
            var dialog = new CommonSaveFileDialog();

            dialog.Filters.Add(new CommonFileDialogFilter("excel file", "*.xlsx"));
            dialog.DefaultExtension = "xlsx";

            if (dialog.ShowDialog() != CommonFileDialogResult.Ok)
            {
                return;
            }

            try
            {
                var dataTable = this.tableViewModel.Source.ExportTable2();
                var writer    = new SpreadsheetWriter(dataTable.DataSet);
                writer.Write(dialog.FileName);
            }
            catch (Exception e)
            {
                AppMessageBox.ShowError(e);
            }
        }
        private static void Main()
        {
            var n      = DateTime.Now;
            var tempDi = new DirectoryInfo(string.Format("ExampleOutput-{0:00}-{1:00}-{2:00}-{3:00}{4:00}{5:00}", n.Year - 2000, n.Month, n.Day, n.Hour, n.Minute, n.Second));

            tempDi.Create();

            var wb = new WorkbookDfn
            {
                Worksheets = new WorksheetDfn[]
                {
                    new WorksheetDfn
                    {
                        Name           = "MyFirstSheet",
                        ColumnHeadings = new CellDfn[]
                        {
                            new CellDfn
                            {
                                Value = "DataType",
                                Bold  = true,
                            },
                            new CellDfn
                            {
                                Value = "Value",
                                Bold  = true,
                                HorizontalCellAlignment = HorizontalCellAlignment.Right,
                            },
                        },
                        Rows = new RowDfn[]
                        {
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Boolean",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Boolean,
                                        Value        = true,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Boolean",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Boolean,
                                        Value        = false,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "String",
                                    },
                                    new CellDfn {
                                        CellDataType            = CellDataType.String,
                                        Value                   = "A String",
                                        HorizontalCellAlignment = HorizontalCellAlignment.Right,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "int",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 100,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "int?",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (int?)100,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "int? (is null)",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = null,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "uint",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (uint)101,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "long",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = long.MaxValue,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "float",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (float)123.45,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "double",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 123.45,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "decimal",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (decimal)123.45,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.Date,
                                        Value        = new DateTime(2012, 1, 8),
                                        FormatCode   = "mm-dd-yy",
                                    },
                                    new CellDfn {
                                        CellDataType            = CellDataType.Date,
                                        Value                   = new DateTime(2012, 1, 9),
                                        FormatCode              = "mm-dd-yy",
                                        Bold                    = true,
                                        HorizontalCellAlignment = HorizontalCellAlignment.Center,
                                    },
                                }
                            },
                        }
                    }
                }
            };

            SpreadsheetWriter.Write(Path.Combine(tempDi.FullName, "Test2.xlsx"), wb);
        }
        static void Main(string[] args)
        {
            var n      = DateTime.Now;
            var tempDi = new DirectoryInfo(string.Format("ExampleOutput-{0:00}-{1:00}-{2:00}-{3:00}{4:00}{5:00}", n.Year - 2000, n.Month, n.Day, n.Hour, n.Minute, n.Second));

            tempDi.Create();

            WorkbookDfn wb = new WorkbookDfn
            {
                Worksheets = new WorksheetDfn[]
                {
                    new WorksheetDfn
                    {
                        Name           = "MyFirstSheet",
                        TableName      = "NamesAndRates",
                        ColumnHeadings = new CellDfn[]
                        {
                            new CellDfn
                            {
                                Value = "Name",
                                Bold  = true,
                            },
                            new CellDfn
                            {
                                Value = "Age",
                                Bold  = true,
                                HorizontalCellAlignment = HorizontalCellAlignment.Left,
                            },
                            new CellDfn
                            {
                                Value = "Rate",
                                Bold  = true,
                                HorizontalCellAlignment = HorizontalCellAlignment.Left,
                            }
                        },
                        Rows = new RowDfn[]
                        {
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Eric",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 50,
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (decimal)45.00,
                                        FormatCode   = "0.00",
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Bob",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 42,
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (decimal)78.00,
                                        FormatCode   = "0.00",
                                    },
                                }
                            },
                        }
                    }
                }
            };

            SpreadsheetWriter.Write(Path.Combine(tempDi.FullName, "Test1.xlsx"), wb);
        }
        static void Main(string[] args)
        {
            WorkbookDfn wb = new WorkbookDfn
            {
                Worksheets = new WorksheetDfn[]
                {
                    new WorksheetDfn
                    {
                        Name           = "MyFirstSheet",
                        ColumnHeadings = new CellDfn[]
                        {
                            new CellDfn
                            {
                                Value = "DataType",
                                Bold  = true,
                            },
                            new CellDfn
                            {
                                Value = "Value",
                                Bold  = true,
                                HorizontalCellAlignment = HorizontalCellAlignment.Right,
                            },
                        },
                        Rows = new RowDfn[]
                        {
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Boolean",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Boolean,
                                        Value        = true,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Boolean",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Boolean,
                                        Value        = false,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "String",
                                    },
                                    new CellDfn {
                                        CellDataType            = CellDataType.String,
                                        Value                   = "A String",
                                        HorizontalCellAlignment = HorizontalCellAlignment.Right,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "int",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (int)100,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "int?",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (int?)100,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "int? (is null)",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = null,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "uint",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (uint)101,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "long",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = Int64.MaxValue,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "float",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (float)123.45,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "double",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (double)123.45,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "decimal",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (decimal)123.45,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.Date,
                                        Value        = new DateTime(2012, 1, 8),
                                        FormatCode   = "mm-dd-yy",
                                    },
                                    new CellDfn {
                                        CellDataType            = CellDataType.Date,
                                        Value                   = new DateTime(2012, 1, 9),
                                        FormatCode              = "mm-dd-yy",
                                        Bold                    = true,
                                        HorizontalCellAlignment = HorizontalCellAlignment.Center,
                                    },
                                }
                            },
                        }
                    }
                }
            };

            SpreadsheetWriter.Write("Test2.xlsx", wb);
        }
        public void SW002_AllDataTypes()
        {
            var wb = new WorkbookDfn
            {
                Worksheets = new WorksheetDfn[]
                {
                    new WorksheetDfn
                    {
                        Name           = "MyFirstSheet",
                        ColumnHeadings = new CellDfn[]
                        {
                            new CellDfn
                            {
                                Value = "DataType",
                                Bold  = true,
                            },
                            new CellDfn
                            {
                                Value = "Value",
                                Bold  = true,
                                HorizontalCellAlignment = HorizontalCellAlignment.Right,
                            },
                        },
                        Rows = new RowDfn[]
                        {
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Boolean",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Boolean,
                                        Value        = true,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Boolean",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Boolean,
                                        Value        = false,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "String",
                                    },
                                    new CellDfn {
                                        CellDataType            = CellDataType.String,
                                        Value                   = "A String",
                                        HorizontalCellAlignment = HorizontalCellAlignment.Right,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "int",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 100,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "int?",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 100,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "int? (is null)",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = null,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "uint",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 101,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "long",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = long.MaxValue,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "float",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (float)123.45,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "double",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 123.45,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "decimal",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (decimal)123.45,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "date (t:str)",
                                    },
                                    new CellDfn {
                                        Value      = new DateTime(2012, 1, 8).ToOADate(),
                                        FormatCode = "mm-dd-yy",
                                        Bold       = true,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "date (t:str)",
                                    },
                                    new CellDfn {
                                        Value      = new DateTime(2012, 1, 9).ToOADate(),
                                        FormatCode = "mm-dd-yy",
                                        Bold       = true,
                                        HorizontalCellAlignment = HorizontalCellAlignment.Center,
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "date (t:d)",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Date,
                                        Value        = new DateTime(2012, 1, 11).ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fff"),
                                    },
                                }
                            },
                        }
                    }
                }
            };
            var outXlsx = new FileInfo(Path.Combine(TestUtil.TempDir.FullName, "SW002-DataTypes.xlsx"));

            SpreadsheetWriter.Write(outXlsx.FullName, wb);
            Validate(outXlsx);
        }
        public void SW001_Simple()
        {
            var wb = new WorkbookDfn
            {
                Worksheets = new WorksheetDfn[]
                {
                    new WorksheetDfn
                    {
                        Name           = "MyFirstSheet",
                        TableName      = "NamesAndRates",
                        ColumnHeadings = new CellDfn[]
                        {
                            new CellDfn
                            {
                                Value = "Name",
                                Bold  = true,
                            },
                            new CellDfn
                            {
                                Value = "Age",
                                Bold  = true,
                                HorizontalCellAlignment = HorizontalCellAlignment.Left,
                            },
                            new CellDfn
                            {
                                Value = "Rate",
                                Bold  = true,
                                HorizontalCellAlignment = HorizontalCellAlignment.Left,
                            }
                        },
                        Rows = new RowDfn[]
                        {
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Eric",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 50,
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (decimal)45.00,
                                        FormatCode   = "0.00",
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Bob",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 42,
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (decimal)78.00,
                                        FormatCode   = "0.00",
                                    },
                                }
                            },
                        }
                    }
                }
            };
            var outXlsx = new FileInfo(Path.Combine(TestUtil.TempDir.FullName, "SW001-Simple.xlsx"));

            SpreadsheetWriter.Write(outXlsx.FullName, wb);
            Validate(outXlsx);
        }
        static void Main(string[] args)
        {
            WorkbookDfn wb = new WorkbookDfn
            {
                Worksheets = new WorksheetDfn[]
                {
                    new WorksheetDfn
                    {
                        Name           = "MyFirstSheet",
                        TableName      = "NamesAndRates",
                        ColumnHeadings = new CellDfn[]
                        {
                            new CellDfn
                            {
                                Value = "Name",
                                Bold  = true,
                            },
                            new CellDfn
                            {
                                Value = "Age",
                                Bold  = true,
                                HorizontalCellAlignment = HorizontalCellAlignment.Left,
                            },
                            new CellDfn
                            {
                                Value = "Rate",
                                Bold  = true,
                                HorizontalCellAlignment = HorizontalCellAlignment.Left,
                            }
                        },
                        Rows = new RowDfn[]
                        {
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Eric",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 50,
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (decimal)45.00,
                                        FormatCode   = "0.00",
                                    },
                                }
                            },
                            new RowDfn
                            {
                                Cells = new CellDfn[]
                                {
                                    new CellDfn {
                                        CellDataType = CellDataType.String,
                                        Value        = "Bob",
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = 42,
                                    },
                                    new CellDfn {
                                        CellDataType = CellDataType.Number,
                                        Value        = (decimal)78.00,
                                        FormatCode   = "0.00",
                                    },
                                }
                            },
                        }
                    }
                }
            };

            SpreadsheetWriter.Write("../../Test1.xlsx", wb);
        }
Пример #14
0
        public async Task <bool> SaveLargeExcel2Async(string folder, ObservableCollection <CompositBDO> composite, IntakeYearsBDO intake)
        {
            AllScores = composite;
            // string path = Path.GetDirectoryName(myfile);
            bool   isgen    = false;
            string n        = " n = " + AllScores.Count() + ".xlsx";
            string filename = intake.Year.ToString();

            filename += " intake cycle Logistics Composite ";
            // Get the date today
            DateTime dt = new DateTime();

            dt = DateTime.Now;
            string myDay = dt.Year + dt.Month.ToString("00") + dt.Day.ToString("00") + "_" + dt.Hour.ToString("00") + dt.Minute.ToString("00");

            filename = filename + myDay + n;

            filename = Path.Combine(folder, filename);

            await LoadNBTVenuesAsync(); // loads all venues into memory

            //generate a collection of Logistics objects
            await CompositToLogisticsCompositeAsync();

            composite.Clear();
            AllScores.Clear();

            // column heading for excel
            string[] ColumnHeadings = new[]
            {
                "Test Session ID", "NBT Reference", "Surname", "First Name",
                "Middle Initials", "South African ID", "Foreign ID", "Date of Birth", "Wrote AL",
                "Wrote QL", "Wrote Maths", "Student Number", "Faculty", "Programme", "Date_of_Test",
                "Venue", "Gender", "AL Score",
                "AL Performance", "QL Score", "QL Performance", "Maths Score", "Maths Performance",
                "AQL TEST LANGUAGE", "MATHS TEST LANGUAGE", "Test Type", "Venue Province",
            };

            // Size of spreadsheet
            var data = new[]
            {
                new {
                    SheetName  = "Logistics Composite",
                    NumberCols = ColumnHeadings.Count(),
                    Numberrows = LComposite.Count(),
                },
            };

            // generate the  workbook and spreadsheets
            WorkbookDfn wb = new WorkbookDfn
            {
                Worksheets = data
                             .Select(d => new WorksheetDfn
                {
                    Name           = d.SheetName,
                    ColumnHeadings = Enumerable.Range(0, d.NumberCols)
                                     .Select(c => new CellDfn
                    {
                        Value = ColumnHeadings[c],
                        Bold  = true,
                    }),
                    Rows = Enumerable.Range(0, d.Numberrows)
                           .Select(r => new RowDfn
                    {
                        Cells = Enumerable.Range(0, d.NumberCols)
                                .Select(c =>

                                        new CellDfn
                        {
                            Value =
                                c == 0 ? LComposite.ElementAt(r).SessionId :
                                c == 1 ? LComposite.ElementAt(r).NBT :
                                c == 2 ? LComposite.ElementAt(r).Surname :
                                c == 3 ? LComposite.ElementAt(r).Name :
                                c == 4 ? LComposite.ElementAt(r).MiddleInitials :
                                c == 5 ? LComposite.ElementAt(r).SouthAfricanID :
                                c == 6 ? LComposite.ElementAt(r).Passport :
                                c == 7 ? LComposite.ElementAt(r).Birth :
                                c == 8 ? LComposite.ElementAt(r).WroteAL :
                                c == 9 ? LComposite.ElementAt(r).WroteQL :
                                c == 10 ? LComposite.ElementAt(r).WroteMat :
                                c == 11 ? LComposite.ElementAt(r).StudentNo :
                                c == 12 ? LComposite.ElementAt(r).Faculty :
                                c == 13 ? LComposite.ElementAt(r).Programme :
                                c == 14 ? LComposite.ElementAt(r).TestDate :
                                c == 15 ? LComposite.ElementAt(r).Venue :
                                c == 16 ? LComposite.ElementAt(r).Sex :

                                c == 17 ? LComposite.ElementAt(r).ALScore :
                                c == 18 ? LComposite.ElementAt(r).ALLevel :
                                c == 19 ? LComposite.ElementAt(r).QLScore :
                                c == 20 ? LComposite.ElementAt(r).QLLevel :
                                c == 21 ? LComposite.ElementAt(r).MatScore :
                                c == 22 ? LComposite.ElementAt(r).MatLevel :
                                c == 23 ? LComposite.ElementAt(r).AQLLanguage :
                                c == 24 ? LComposite.ElementAt(r).MatLanguage :
                                c == 25 ? LComposite.ElementAt(r).TestType :
                                c == 26 ? LComposite.ElementAt(r).VenueProvince : "",

                            HorizontalCellAlignment = c == 4 ? HorizontalCellAlignment.Center :
                                                      c == 8 ? HorizontalCellAlignment.Center :
                                                      c == 9 ? HorizontalCellAlignment.Center :
                                                      c == 10 ? HorizontalCellAlignment.Center :
                                                      c == 16 ? HorizontalCellAlignment.Center :
                                                      c == 17 ? HorizontalCellAlignment.Center :
                                                      c == 19 ? HorizontalCellAlignment.Center :
                                                      c == 21 ? HorizontalCellAlignment.Center :
                                                      c == 23 ? HorizontalCellAlignment.Center :
                                                      c == 24 ? HorizontalCellAlignment.Center : HorizontalCellAlignment.Left,
                            // FormatCode = c == 5 ? "0000000000000" :"",
                        })
                    }),
                })
            };

            // stream write to excel file
            SpreadsheetWriter.Write(filename, wb);
            // SpreadsheetWriter.Write()
            isgen = true;
            LComposite.Clear();
            LComposite = null;
            return(isgen);
        }
Пример #15
0
        // Open document for saving excel data
        public async Task <bool> SaveLargeExcelAsync(string folder, ObservableCollection <CompositBDO> composite, IntakeYearsBDO intake)
        {
            AllScores = composite;

            // string path = Path.GetDirectoryName(folder);
            bool   isgen    = false;
            string n        = " n = " + AllScores.Count() + ".xlsx";
            string filename = intake.Year.ToString();

            filename += " intake cycle Composite ";
            // Get the date today
            DateTime dt = new DateTime();

            dt = DateTime.Now;
            string myDay = dt.Year + dt.Month.ToString("00") + dt.Day.ToString("00") + "_" + dt.Hour.ToString("00") + dt.Minute.ToString("00");

            filename = filename + myDay + n;

            filename = Path.Combine(folder, filename);
            // make a copy of the template
            //File.Copy("intake cycle Composite.xlsx", filename, true);



            //using (SpreadsheetDocument SpDoc = SpreadsheetDocument.Open(filename, true))
            //{
            await CompositToFullCompositeAsync();

            composite.Clear();
            AllScores.Clear();

            // column heading for excel
            string[] ColumnHeadings = new[] {
                "Ref No", "Barcode", "Last Name", "First_Name", "INITIALS", "ID NUMBER",
                "ID_Foreign", "Date of Birth", "ID Type", "Citizenship", "Classification",
                "Gender 1", "Faculty 1", "DATE", "Test Centre Code", "Venue Name",
                "Home Lang", "GR12 Language", "AQL LANG", "AQL CODE", "MAT LANG", "MAT CODE",
                "Faculty 2", "Faculty 3", "Test Session ID", "NBT Reference", "Surname", "First Name",
                "Middle Initials", "South African ID", "Foreign ID", "Date of Birth", "Wrote AL",
                "Wrote QL", "Wrote Maths", "Student Number", "Faculty", "Programme", "Date_of_Test",
                "Venue", "Gender", "Street and Number", "Street Name", "Suburb", "City/Town", "Province/Region",
                "Postal Code", "e-mail Address", "Landline Number", "Mobile Number", "AL Score",
                "AL Performance", "QL Score", "QL Performance", "Maths Score", "Maths Performance",
                "AQL TEST LANGUAGE", "MATHS TEST LANGUAGE",
            };

            // Size of spreadsheet
            var data = new[]
            {
                new {
                    SheetName  = "Composite",
                    NumberCols = ColumnHeadings.Count(),
                    Numberrows = FComposite.Count(),
                },
            };

            // generate the  workbook and spreadsheets
            WorkbookDfn workbook = new WorkbookDfn
            {
                Worksheets = data
                             .Select(d => new WorksheetDfn
                {
                    Name           = d.SheetName,
                    ColumnHeadings = Enumerable.Range(0, d.NumberCols)
                                     .Select(c => new CellDfn
                    {
                        Value = ColumnHeadings[c],
                        Bold  = true,
                    }),
                    Rows = Enumerable.Range(0, d.Numberrows)
                           .Select(r => new RowDfn
                    {
                        Cells = Enumerable.Range(0, d.NumberCols)
                                .Select(c =>

                                        new CellDfn
                        {
                            Value = c == 0 ? FComposite.ElementAt(r).RefNo :
                                    c == 1 ? FComposite.ElementAt(r).Barcode :
                                    c == 2 ? FComposite.ElementAt(r).LastName :
                                    c == 3 ? FComposite.ElementAt(r).FirstName :
                                    c == 4 ? FComposite.ElementAt(r).Initials :
                                    c == 5 ? FComposite.ElementAt(r).SAID :
                                    c == 6 ? FComposite.ElementAt(r).FID :
                                    c == 7 ? FComposite.ElementAt(r).DOB :
                                    c == 8 ? FComposite.ElementAt(r).IDType :
                                    c == 9 ? FComposite.ElementAt(r).Citizenship :
                                    c == 10 ? FComposite.ElementAt(r).Classification :
                                    c == 11 ? FComposite.ElementAt(r).Gender :
                                    c == 12 ? FComposite.ElementAt(r).Faculty1 :
                                    c == 13 ? FComposite.ElementAt(r).DOT :
                                    c == 14 ? FComposite.ElementAt(r).VenueCode :
                                    c == 15 ? FComposite.ElementAt(r).VenueName :
                                    c == 16 ? FComposite.ElementAt(r).HomeLanguage :
                                    c == 17 ? FComposite.ElementAt(r).SchLanguage :
                                    c == 18 ? FComposite.ElementAt(r).AQLLang :
                                    c == 19 ? FComposite.ElementAt(r).AQLCode :
                                    c == 20 ? FComposite.ElementAt(r).MATLang :
                                    c == 21 ? FComposite.ElementAt(r).MATCode :
                                    c == 22 ? FComposite.ElementAt(r).Faculty2 :
                                    c == 23 ? FComposite.ElementAt(r).Faculty3 :
                                    c == 24 ? FComposite.ElementAt(r).SessionId :
                                    c == 25 ? FComposite.ElementAt(r).NBT :
                                    c == 26 ? FComposite.ElementAt(r).Surname :
                                    c == 27 ? FComposite.ElementAt(r).Name :
                                    c == 28 ? FComposite.ElementAt(r).MiddleInitials :
                                    c == 29 ? FComposite.ElementAt(r).SouthAfricanID :
                                    c == 30 ? FComposite.ElementAt(r).Passport :
                                    c == 31 ? FComposite.ElementAt(r).Birth :
                                    c == 32 ? FComposite.ElementAt(r).WroteAL :
                                    c == 33 ? FComposite.ElementAt(r).WroteQL :
                                    c == 34 ? FComposite.ElementAt(r).WroteMat :
                                    c == 35 ? FComposite.ElementAt(r).StudentNo :
                                    c == 36 ? FComposite.ElementAt(r).Faculty :
                                    c == 37 ? FComposite.ElementAt(r).Programme :
                                    c == 38 ? FComposite.ElementAt(r).TestDate :
                                    c == 39 ? FComposite.ElementAt(r).Venue :
                                    c == 40 ? FComposite.ElementAt(r).Sex :
                                    c == 41 ? FComposite.ElementAt(r).Street1 :
                                    c == 42 ? FComposite.ElementAt(r).Street2 :
                                    c == 43 ? FComposite.ElementAt(r).Suburb :
                                    c == 44 ? FComposite.ElementAt(r).City :
                                    c == 45 ? FComposite.ElementAt(r).Province :
                                    c == 46 ? FComposite.ElementAt(r).Postal :
                                    c == 47 ? FComposite.ElementAt(r).Email :
                                    c == 48 ? FComposite.ElementAt(r).Landline :
                                    c == 49 ? FComposite.ElementAt(r).Mobile :
                                    c == 50 ? FComposite.ElementAt(r).ALScore :
                                    c == 51 ? FComposite.ElementAt(r).ALLevel :
                                    c == 52 ? FComposite.ElementAt(r).QLScore :
                                    c == 53 ? FComposite.ElementAt(r).QLLevel :
                                    c == 54 ? FComposite.ElementAt(r).MatScore :
                                    c == 55 ? FComposite.ElementAt(r).MatLevel :
                                    c == 56 ? FComposite.ElementAt(r).AQLLanguage :
                                    c == 57 ? FComposite.ElementAt(r).MatLanguage : "",
                            CellDataType =
                                c == 7 ? CellDataType.String :
                                c == 2 ? CellDataType.String :
                                c == 3 ? CellDataType.String : CellDataType.String,
                            HorizontalCellAlignment = c == 4 ? HorizontalCellAlignment.Center :
                                                      c == 33 ? HorizontalCellAlignment.Center :
                                                      c == 34 ? HorizontalCellAlignment.Center :
                                                      c == 32 ? HorizontalCellAlignment.Center :
                                                      c == 40 ? HorizontalCellAlignment.Center :
                                                      c == 18 ? HorizontalCellAlignment.Center :
                                                      c == 20 ? HorizontalCellAlignment.Center :
                                                      c == 50 ? HorizontalCellAlignment.Center :
                                                      c == 52 ? HorizontalCellAlignment.Center :
                                                      c == 54 ? HorizontalCellAlignment.Center :
                                                      c == 56 ? HorizontalCellAlignment.Center :
                                                      c == 57 ? HorizontalCellAlignment.Center : HorizontalCellAlignment.Left,
                            // FormatCode = c == 5 ? "0000000000000" :"",
                        })
                    }),
                })
            };

            // stream write to excel file
            SpreadsheetWriter.Write(filename, workbook);
            // SpreadsheetWriter.Write()
            isgen = true;
            FComposite.Clear();
            FComposite = null;
            return(isgen);
        }