static void Main(string[] args)

        {
            Console.WriteLine("Welcome to sun heat calculator alpha-1.01!");
            Console.WriteLine("Made by WHIZX-IT");
            var input = new DataInput();

            input.ReadData();
            Stopwatch      stopwatch = Stopwatch.StartNew();
            var            calc      = new TotalSunHeat(input);
            ExcelFormatter formatter = new ExcelFormatter(calc, input);

            formatter.GenerateAll();

            /*
             * Console.WriteLine(Formatting.FormatOutput("QDIR_0", calc.CalculateQDIR0().ToString()));
             * Console.WriteLine(Formatting.FormatOutput("D", calc.CalculateDeclination()+ " graden"));
             * Console.WriteLine(Formatting.FormatHeightOfSun(calc));
             * Console.WriteLine(Formatting.FormatEntryAngles(calc));
             * Console.WriteLine(Formatting.FormatSunRayHorizontal(calc));
             * Console.WriteLine(Formatting.FormatSunRayOnVertical(calc));
             * Console.WriteLine(Formatting.FormatSunRayOnTilted(calc));
             * Console.WriteLine(Formatting.FormatTotal(calc));*/
            stopwatch.Stop();
            Console.WriteLine(stopwatch.ElapsedMilliseconds);
            Console.WriteLine("Done");
        }
Beispiel #2
0
        public void TestWrite()
        {
            string file = Path.GetTempFileName() + ".xls";

            using (ExcelDocument doc = ExcelDocument.CreateDocument())
            {
                ExcelWorksheet worksheet = doc.CreateWorksheet("Test");

                ExcelCell cell = worksheet.GetCellAt(new ExcelCellName(new ColumnLetter(Letter.A), 1));
                cell.Text = "Hello World";

                ExcelFormatter formatter = worksheet.GetFormatterAt(
                    new ExcelCellName(new ColumnLetter(Letter.A), 1),
                    new ExcelCellName(new ColumnLetter(Letter.C), 3));
                formatter.Background  = Color.Red;
                formatter.Foreground  = Color.Yellow;
                formatter.BorderColor = Color.Blue;
                formatter.Font        = new Font("Courier New", 12, FontStyle.Underline | FontStyle.Bold);
                formatter.Merge();
                formatter.ColumnWidth = 100;
                formatter.RowHeight   = 100;

                Assert.AreEqual(Color.FromArgb(0, 255, 0, 0), formatter.Background);
                Assert.AreEqual(Color.FromArgb(0, 255, 255, 0), formatter.Foreground);
                //Assert.AreEqual(Color.FromArgb(0, 0, 0, 255), formatter.BorderColor); TODO

                doc.Save(file);
            }
        }
Beispiel #3
0
            public void ThenTheExcelResultHasRowData(
                List <CommitmentAgreement> agreements,
                ExcelFormatter sut)
            {
                var byteResult = sut.Format(TestHelper.Clone(agreements));

                using (var memoryStream = new MemoryStream(byteResult))
                {
                    var workbook  = new XLWorkbook(memoryStream);
                    var worksheet = workbook.Worksheets.Worksheet("Agreements");

                    worksheet.Column(1).Cell(2).GetValue <string>().Should().Be(agreements[0].OrganisationName);
                    worksheet.Column(2).Cell(2).GetValue <string>().Should().Be(agreements[0].CohortID);
                    worksheet.Column(3).Cell(2).GetValue <string>().Should().Be(agreements[0].AgreementID);
                }
            }
Beispiel #4
0
        public void TestOpen()
        {
            ExcelDocument doc = null;

            using (MemoryStream ms = new MemoryStream(EXCEL.ToByteArrayFromBase64String()))
            {
                doc = ExcelDocument.OpenDocument(ms);
                Assert.AreEqual(4, doc.GetAllWorksheets().Length);
            }

            ExcelWorksheet worksheet = doc.GetAllWorksheets()[0];
            ExcelFormatter formatter = worksheet.GetFormatterAt(
                new ExcelCellName(new ColumnLetter(Letter.A), 1),
                new ExcelCellName(new ColumnLetter(Letter.C), 3));

            Assert.AreEqual(Color.FromArgb(0, 255, 0, 0), formatter.Background);
            Assert.AreEqual(Color.FromArgb(0, 255, 255, 0), formatter.Foreground);

            doc.Dispose();
        }
Beispiel #5
0
            public void ThenTheExcelResultHasColumnHeaders(
                List <CommitmentAgreement> agreements,
                ExcelFormatter sut)
            {
                var byteResult = sut.Format(TestHelper.Clone(agreements));

                using (var memoryStream = new MemoryStream(byteResult))
                {
                    var workbook = new XLWorkbook(memoryStream);

                    workbook.Worksheets.Worksheet("Agreements").Should().NotBeNull();

                    var worksheet = workbook.Worksheets.Worksheet("Agreements");

                    // note the offset of 1... VB (vomit emoji)
                    worksheet.Column(1).Cell(1).GetValue <string>().Should().Be(nameof(CommitmentAgreement.OrganisationName));
                    worksheet.Column(2).Cell(1).GetValue <string>().Should().Be(nameof(CommitmentAgreement.CohortID));
                    worksheet.Column(3).Cell(1).GetValue <string>().Should().Be(nameof(CommitmentAgreement.AgreementID));
                }
            }
Beispiel #6
0
        public ActionResult Export(ExportParameters model)
        {
            var records = _repository.GetAll();

            if (model.PagingEnabled)
            {
                records = records.Skip((model.CurrentPage - 1) * model.PageSize)
                          .Take(model.PageSize);
            }
            if (model.OutputType.Equals(Output.Excel))
            {
                var excelFormatter = new ExcelFormatter(records);
                return(new ExcelResult(excelFormatter.CreateXmlWorksheet(), "Sample.xlsx"));
            }

            if (model.OutputType.Equals(Output.Csv))
            {
                return(new CsvResult(records, "Sample.csv"));
            }

            return(Redirect(Url.Content("~/")));
        }
Beispiel #7
0
        public ActionResult DownloadStudentSubmittedProposalsSpreadsheet()
        {
            int           courseId      = 1;
            uint          i             = 2;
            string        cellReference = "A2";
            string        fileName      = "StudentProposalsSpreadsheet_" + DateTime.Now.ToString("G") + ".xlsx";
            List <string> headerRow     = new List <string>();
            MemoryStream  memoryStream  = new MemoryStream();

            DocumentFormat.OpenXml.Spreadsheet.SheetData   sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
            List <DocumentFormat.OpenXml.Spreadsheet.Font> fonts     = new List <DocumentFormat.OpenXml.Spreadsheet.Font>
            {
                new DocumentFormat.OpenXml.Spreadsheet.Font(new Bold()),
                new DocumentFormat.OpenXml.Spreadsheet.Font()
            };

            List <ForegroundColor> foregroundColors = new List <ForegroundColor>();

            foregroundColors.Add(new ForegroundColor {
                Rgb = HexBinaryValue.FromString("FFD633")
            });
            foregroundColors.Add(new ForegroundColor {
                Rgb = HexBinaryValue.FromString("FFFFFF")
            });

            List <BackgroundColor> backgroundColors = new List <BackgroundColor>();

            backgroundColors.Add(new BackgroundColor {
                Indexed = 64
            });
            backgroundColors.Add(new BackgroundColor {
                Indexed = 64
            });

            List <DocumentFormat.OpenXml.Spreadsheet.Alignment> alignments = new List <DocumentFormat.OpenXml.Spreadsheet.Alignment>();

            alignments.Add(new DocumentFormat.OpenXml.Spreadsheet.Alignment());
            alignments.Add(new DocumentFormat.OpenXml.Spreadsheet.Alignment {
                WrapText = true, Vertical = VerticalAlignmentValues.Top
            });

            SpreadsheetDocument spreadsheetDocument;
            Stylesheet          stylesheet;

            stylesheet          = ExcelFormatter.CreateStyles(fonts, foregroundColors, backgroundColors, alignments);
            spreadsheetDocument = ExcelFormatter.CreateExcelSpreadsheet(ref memoryStream);
            ExcelFormatter.InitializeSpreadsheet(ref spreadsheetDocument, stylesheet);

            List <List <string> > spreadsheetData = _projectctx.GetSelfInitiatedProjectsSpreadsheetData(courseId);

            headerRow.AddRange(new string[] { "S/N", "Project Background", "Project Title", "Project Brief", "Technology Specification" });
            sheetData.Append(ExcelFormatter.CreateRow("A1", 1, headerRow, 1));
            foreach (List <string> l in spreadsheetData)
            {
                sheetData.Append(ExcelFormatter.CreateRow(cellReference, i, l, 2));
                ++i;
                cellReference = "A" + i;
            }
            ExcelFormatter.FinalizeSpreadsheetWriting(ref spreadsheetDocument, ref sheetData);
            memoryStream.Seek(0, SeekOrigin.Begin);
            Response.Headers.Add("Content-Disposition", "attachement; filename=" + fileName);
            Response.Cookies.Append("completedDownloadToken", "downloaded", new CookieOptions()
            {
                Expires = DateTime.Now.AddSeconds(8)
            });
            return(new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
        }
Beispiel #8
0
 public async Task TestingExcelCreator()
 {
     ExcelFormatter test = new ExcelFormatter(@"C:\Users\Home\Desktop", "");
     await test.ExcelCreator();
 }
Beispiel #9
0
        public void TestingJSONDeserializer()
        {
            ExcelFormatter test = new ExcelFormatter(@"C:\Users\Home\Desktop", "");

            test.PersonsJSONDeserializer();
        }
Beispiel #10
0
        public ActionResult DownloadProjectSelectionSpreadsheet()
        {
            int           courseId      = 1;
            uint          i             = 2;
            string        cellReference = "A2";
            string        fileName      = "ProjectSelectionSpreadsheet_" + DateTime.Now.ToString("G") + ".xlsx";
            List <string> headerRow     = new List <string>();

            IEnumerable <Project> allAvailableProjects = new List <Project>();
            MemoryStream          memoryStream         = new MemoryStream();

            DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();

            List <Font> fonts = new List <Font>();

            fonts.Add(new Font(new Bold()));
            fonts.Add(new Font());

            List <ForegroundColor> foregroundColors = new List <ForegroundColor>();

            foregroundColors.Add(new ForegroundColor {
                Rgb = HexBinaryValue.FromString("FFD633")
            });
            foregroundColors.Add(new ForegroundColor {
                Rgb = HexBinaryValue.FromString("FFFFFF")
            });

            List <BackgroundColor> backgroundColors = new List <BackgroundColor>();

            backgroundColors.Add(new BackgroundColor {
                Indexed = 64
            });
            backgroundColors.Add(new BackgroundColor {
                Indexed = 64
            });

            List <Alignment> alignments = new List <Alignment>();

            alignments.Add(new Alignment());
            alignments.Add(new Alignment());

            SpreadsheetDocument spreadsheetDocument;
            Stylesheet          stylesheet;

            stylesheet          = ExcelFormatter.CreateStyles(fonts, foregroundColors, backgroundColors, alignments);
            spreadsheetDocument = ExcelFormatter.CreateExcelSpreadsheet(ref memoryStream);
            ExcelFormatter.InitializeSpreadsheet(ref spreadsheetDocument, stylesheet);

            List <List <string> > spreadsheetData = groupRepository.GetGroupProjectSelectionSpreadsheetData(courseId);

            allAvailableProjects = projectRepository.GetAvailableProjects(courseId);
            headerRow.AddRange(new string[] { "Group#", "Student ID", "Student Name", "Mobile", "Personal Email" });
            foreach (Project p in allAvailableProjects)
            {
                headerRow.Add(p.project_title);
            }
            sheetData.Append(ExcelFormatter.CreateRow("A1", 1, headerRow, 1));
            foreach (List <string> l in spreadsheetData)
            {
                sheetData.Append(ExcelFormatter.CreateRow(cellReference, i, l, 2));
                ++i;
                cellReference = "A" + i;
            }
            ExcelFormatter.FinalizeSpreadsheetWriting(ref spreadsheetDocument, ref sheetData);

            memoryStream.Seek(0, SeekOrigin.Begin);
            Response.Headers.Add("Content-Disposition", "attachment; filename=" + fileName);
            CookieOptions options = new CookieOptions();

            options.Expires = DateTime.Now.AddMilliseconds(8000);
            Response.Cookies.Append("completedDownloadToken", "downloaded", options);
            return(new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
        }
        public static DataTable GenerateReport(ReportConfig config, ICollection <Resource> resources)
        {
            List <Column> columns = new List <Column>();

            foreach (ReportConfigColumn configColumn in config.Columns)
            {
                List <ReportFieldNode> nodes = ReportFieldNodes.ExtractNodePath(configColumn.Path);
                if (nodes != null && nodes.Count > 0)
                {
                    Func <ReportArray, ReportArray>[]   functions = nodes.Select(x => x.Extractor).ToArray();
                    Func <ReportNodeInfos, ReportArray> extractor = x => functions.Aggregate(new ReportArray(new object[] { x }), (current, function) => function(current));

                    columns.Add(new Column
                    {
                        Config    = configColumn,
                        Extactor  = extractor,
                        Formatter = ExcelFormatter.GetFormat(nodes.Last().Type)
                    });
                }
                else
                {
                    throw new Exception("Path " + configColumn.Path + " not found");
                }
            }

            DataTable table = new DataTable(config.Name);

            foreach (Column column in columns)
            {
                table.AddColumn(column.Config.Name, column.Formatter.TargetType, column.Config.Width, column.Formatter.StringFormat);
            }

            switch (config.Type)
            {
            case ReportType.ListResources:
                foreach (Resource resource in resources)
                {
                    ReportNodeInfos infos = new ReportNodeInfos
                    {
                        Resource = resource
                    };

                    AddRow(table, columns, infos);
                }
                break;

            case ReportType.ListReferences:
                foreach (Resource resource in resources)
                {
                    foreach (ResourceReference reference in resource.References)
                    {
                        ReportNodeInfos infos = new ReportNodeInfos
                        {
                            Resource  = resource,
                            Reference = reference
                        };

                        AddRow(table, columns, infos);
                    }
                }
                break;

            case ReportType.ListContent:
                foreach (Resource resource in resources)
                {
                    foreach (ResourceContent content in resource.Content)
                    {
                        ReportNodeInfos infos = new ReportNodeInfos
                        {
                            Resource = resource,
                            Content  = content
                        };

                        AddRow(table, columns, infos);
                    }
                }
                break;

            case ReportType.ListErrors:
                foreach (Resource resource in resources)
                {
                    foreach (ResourceError error in resource.Errors)
                    {
                        ReportNodeInfos infos = new ReportNodeInfos
                        {
                            Resource = resource,
                            Error    = error
                        };

                        AddRow(table, columns, infos);
                    }
                }
                break;

            default:
                throw new ArgumentOutOfRangeException();
            }

            return(table);
        }
Beispiel #12
0
        public DataTable ImportExcelBase(HttpPostedFileBase file, out Dictionary <int, PicturesInfo> ImgList, Dictionary <string, ExcelFormatter> formatColumn = null)
        {
            var datatable = new DataTable();

            ImgList = new Dictionary <int, PicturesInfo>();
            if (file.FileName.IndexOf("xlsx") > -1)
            {
                NPOI.XSSF.UserModel.XSSFWorkbook Upfile = new NPOI.XSSF.UserModel.XSSFWorkbook(file.InputStream);
                var sheet    = Upfile.GetSheetAt(0);
                var firstRow = sheet.GetRow(0);
                var buffer   = new byte[file.ContentLength];
                for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++)
                {
                    datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string));
                }
                bool imgForm = true;
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow datarow = datatable.NewRow();
                    var     row     = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }
                    bool con = true;
                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                    {
                        if (formatColumn != null && formatColumn.Values.Where(x => x.ColumnName == firstRow.GetCell(j).StringCellValue).Any())
                        {
                            ExcelFormatter excelFormatter = formatColumn.Values.Where(x => x.ColumnName == firstRow.GetCell(j).StringCellValue).FirstOrDefault();
                            if (excelFormatter.ColumnTrans.Equals(EnumColumnTrans.ConvertImportImage))
                            {
                                if (imgForm)
                                {
                                    ImgList = NPOIExtendImg.GetAllPictureInfos(sheet);
                                    imgForm = false;
                                }
                                datarow[j] = GetImgsUrl(ImgList, i - 1);
                                continue;
                            }
                        }
                        var cell = row.GetCell(j);
                        if (cell == null)
                        {
                            datarow[j] = "";
                            continue;
                        }
                        switch (cell.CellType)
                        {
                        case CellType.Numeric:
                            datarow[j] = cell.NumericCellValue;
                            break;

                        case CellType.String:
                            datarow[j] = cell.StringCellValue;
                            break;

                        case CellType.Blank:
                            datarow[j] = "";
                            break;

                        case CellType.Formula:
                            switch (row.GetCell(j).CachedFormulaResultType)
                            {
                            case CellType.String:
                                string strFORMULA = row.GetCell(j).StringCellValue;
                                if (strFORMULA != null && strFORMULA.Length > 0)
                                {
                                    datarow[j] = strFORMULA.ToString();
                                }
                                else
                                {
                                    datarow[j] = null;
                                }
                                break;

                            case CellType.Numeric:
                                datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                break;

                            case CellType.Boolean:
                                datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                break;

                            case CellType.Error:
                                datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                break;

                            default:
                                datarow[j] = "";
                                break;
                            }
                            break;

                        default:
                            con = false;
                            break;
                        }
                        if (!con)
                        {
                            break;
                        }
                    }
                    if (con)
                    {
                        datatable.Rows.Add(datarow);
                    }
                }
            }
            else
            {
                NPOI.HSSF.UserModel.HSSFWorkbook Upfile = new NPOI.HSSF.UserModel.HSSFWorkbook(file.InputStream);
                var sheet    = Upfile.GetSheetAt(0);
                var firstRow = sheet.GetRow(0);
                var buffer   = new byte[file.ContentLength];
                for (int cellIndex = firstRow.FirstCellNum; cellIndex < firstRow.LastCellNum; cellIndex++)
                {
                    datatable.Columns.Add(firstRow.GetCell(cellIndex).StringCellValue, typeof(string));
                }
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow datarow = datatable.NewRow();
                    var     row     = sheet.GetRow(i);
                    if (row == null)
                    {
                        continue;
                    }
                    bool con = true;
                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                    {
                        var cell = row.GetCell(j);
                        if (cell == null)
                        {
                            datarow[j] = "";
                            continue;
                        }
                        switch (cell.CellType)
                        {
                        case CellType.Numeric:
                            datarow[j] = cell.NumericCellValue;
                            break;

                        case CellType.String:
                            datarow[j] = cell.StringCellValue;
                            break;

                        case CellType.Blank:
                            datarow[j] = "";
                            break;

                        case CellType.Formula:
                            switch (row.GetCell(j).CachedFormulaResultType)
                            {
                            case CellType.String:
                                string strFORMULA = row.GetCell(j).StringCellValue;
                                if (strFORMULA != null && strFORMULA.Length > 0)
                                {
                                    datarow[j] = strFORMULA.ToString();
                                }
                                else
                                {
                                    datarow[j] = null;
                                }
                                break;

                            case CellType.Numeric:
                                datarow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                break;

                            case CellType.Boolean:
                                datarow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                break;

                            case CellType.Error:
                                datarow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                break;

                            default:
                                datarow[j] = "";
                                break;
                            }
                            break;

                        default:
                            con = false;
                            break;
                        }
                        if (!con)
                        {
                            break;
                        }
                    }
                    if (con)
                    {
                        datatable.Rows.Add(datarow);
                    }
                }
            }
            #region 清除最后的空行
            for (int i = datatable.Rows.Count - 1; i > 0; i--)
            {
                bool isnull = true;
                for (int j = 0; j < datatable.Columns.Count; j++)
                {
                    if (datatable.Rows[i][j] != null)
                    {
                        if (datatable.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
                {
                    datatable.Rows[i].Delete();
                }
            }
            #endregion
            return(datatable);
        }