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");
}
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);
}
}
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);
}
}
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();
}
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));
}
}
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("~/")));
}
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"));
}
public async Task TestingExcelCreator()
{
ExcelFormatter test = new ExcelFormatter(@"C:\Users\Home\Desktop", "");
await test.ExcelCreator();
}
public void TestingJSONDeserializer()
{
ExcelFormatter test = new ExcelFormatter(@"C:\Users\Home\Desktop", "");
test.PersonsJSONDeserializer();
}
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);
}
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);
}