private void SQLiteButton_Click(object sender, EventArgs e)
{
SqliteSeeder.SeedDatabase();
string password = Prompt.ShowDialog("Please enter MySQL password: ", string.Empty);
var mysqlDb = new MySqlDb(password);
MySqlSeeder.SeedDatabase(new SqlServerDb(), mysqlDb);
var fileInfo = new FileInfo(ExcelOutputReports);
ExcelReporter.CreateReports(mysqlDb, new SqliteDb(), fileInfo);
MessageBox.Show(ReportsFromSQLiteAndMySQLCreatedSuccessfully, string.Empty, MessageBoxButtons.OK);
}
public void Verify_Report_Output_Two_Hotels_With_Multiple_Ratings()
{
IReporter reporter = new ExcelReporter();
var input = JsonConvert.DeserializeObject <HotelCollection>(File.ReadAllText("input2.json", Encoding.Default));
// get dummy input 2
byte[] output = reporter.CreateReport(input.HotelList);
// verify package
using (ExcelPackage p = new ExcelPackage(new MemoryStream(output)))
{
var sheet = p.Workbook.Worksheets.FirstOrDefault(s => s.Name.Equals("Hotel 1"));
Assert.IsNotNull(sheet);
Assert.AreEqual(sheet.Name, "Hotel 1");
Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[2, 1].Value).ToString(), @"14/03/2016 23:00:00");
Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[2, 2].Value).ToString(), @"15/03/2016 23:00:00");
Assert.AreEqual(sheet.Cells[2, 3].Value, 116.1);
Assert.AreEqual(sheet.Cells[2, 4].Value, "EUR");
Assert.AreEqual(@"Name 1", (String)sheet.Cells[2, 5].Value);
Assert.AreEqual(2.0, sheet.Cells[2, 6].Value);
Assert.AreEqual(sheet.Cells[2, 7].Value, false);
sheet = p.Workbook.Worksheets.FirstOrDefault(s => s.Name.Equals("Hotel 2"));
Assert.IsNotNull(sheet);
Assert.AreEqual(sheet.Name, "Hotel 2");
Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[2, 1].Value).ToString(), @"14/03/2016 23:00:00");
Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[2, 2].Value).ToString(), @"15/03/2016 23:00:00");
Assert.AreEqual(sheet.Cells[2, 3].Value, 78.5);
Assert.AreEqual(sheet.Cells[2, 4].Value, "EUR");
Assert.AreEqual(@"Name 2", (String)sheet.Cells[2, 5].Value);
Assert.AreEqual(2.0, sheet.Cells[2, 6].Value);
Assert.AreEqual(sheet.Cells[2, 7].Value, true);
Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[3, 1].Value).ToString(), @"14/03/2016 23:00:00");
Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[3, 2].Value).ToString(), @"15/03/2016 23:00:00");
Assert.AreEqual(sheet.Cells[3, 3].Value, 116.1);
Assert.AreEqual(sheet.Cells[3, 4].Value, "EUR");
Assert.AreEqual(@"Name 3", (String)sheet.Cells[3, 5].Value);
Assert.AreEqual(1.0, sheet.Cells[3, 6].Value);
Assert.AreEqual(sheet.Cells[3, 7].Value, false);
}
}
static async Task Main(string[] args)
{
var data = Enumerable
.Range(0, 1000)
.Select(x => new User
{
Name = $"{nameof(User.Name)} {x}",
Role = x % 2 == 0 ? Role.Admin : Role.Guest,
IsLocked = x % 2 != 0
})
.ToArray();
var reporter = new ExcelReporter <User>();
var report = reporter.Export(data, new ReportOptions());
await using var s = File.Create("data.xlsx");
await s.WriteAsync(report);
}
private void Export() => ExcelReporter.SaveAsExcell(this);
/// <summary>
/// Set hyperlinks.
/// </summary>
/// <param name="cell">The cell to work on.</param>
/// <param name="hyperlink">The hyperlinks to be associated in the given cell.</param>
private void SetHyperlinks(Range cell, ExcelReporter.Hyperlink hyperlink)
{
// set the value
cell.Hyperlinks.Add(cell, hyperlink.Address, Type.Missing, Type.Missing, hyperlink.TextToDisplay);
}
/// <summary>
/// Highlight specific text segement .
/// </summary>
/// <param name="cell">The cell to work on.</param>
/// <param name="coloredScript">The colored script.</param>
private void SetColoredTextScript(Range cell, ExcelReporter.ColoredScript coloredScript)
{
// set the value
cell.Value = coloredScript.Text;
// set the color
foreach (ExcelReporter.ColoredTextSegment segment in coloredScript.ColoredTextSegments)
{
Characters characters = cell.get_Characters(segment.StartIndex, segment.Length);
characters.Font.ColorIndex = segment.Color;
}
}
/// <summary>
/// Create new sheet in currently active workbook.
/// </summary>
/// <param name="strSheetName">The sheet name.</param>
/// <param name="sheetContent">The sheet content.</param>
public void AddNewSheet(string strSheetName,
ExcelReporter.SheetContent sheetContent)
{
Helper.ThrowIfNull(_workbook);
if (_currentSheetIndex > 0)
{
_worksheet = (_Worksheet)_workbook.Worksheets.Add(Missing.Value,
_workbook.ActiveSheet,
1,
XlSheetType.xlWorksheet);
}
_worksheet.Name = strSheetName;
string finalColumnIndex = string.Empty;
string finalCellIndex = string.Empty;
string finalRawIndex = string.Empty;
string categoryTitle = string.Empty;
Collection<string> columnTitles = new Collection<string>();
int inColumn = 1;
foreach (ExcelReporter.ColumnContent column in sheetContent.Columns)
{
_worksheet.Cells[1, inColumn] = column.Title;
if (string.IsNullOrEmpty(categoryTitle))
{
categoryTitle = column.Title;
}
else
{
columnTitles.Add(column.Title);
}
int rawEndIndex = column.RecordCount + 1;
string columnIndex = GetColumnIndex(inColumn);
string endIndex = columnIndex + rawEndIndex.ToString();
switch (column.Type)
{
case ExcelReporter.ContentTypeCode.ColoredScript:
for (int rawIndex = 2; rawIndex <= rawEndIndex; rawIndex++)
{
SetColoredTextScript(SelectCell(_worksheet, rawIndex, inColumn),
(ExcelReporter.ColoredScript)column.RecordCollection[rawIndex - 2]);
}
break;
case ExcelReporter.ContentTypeCode.HyperLink:
for (int rawIndex = 2; rawIndex <= rawEndIndex; rawIndex++)
{
SetHyperlinks(SelectCell(_worksheet, rawIndex, inColumn),
(ExcelReporter.Hyperlink)column.RecordCollection[rawIndex - 2]);
}
break;
default:
for (int rawIndex = 2; rawIndex <= rawEndIndex; rawIndex++)
{
_worksheet.Cells[rawIndex, inColumn] = column.RecordCollection[rawIndex - 2];
}
break;
}
finalRawIndex = rawEndIndex.ToString();
finalColumnIndex = columnIndex;
finalCellIndex = endIndex;
if (column.Width == ExcelReporter.Width.AutoFit)
{
_worksheet.get_Range(finalColumnIndex + "1", finalCellIndex).EntireColumn.AutoFit();
}
else if (column.Width == ExcelReporter.Width.Narrow)
{
_worksheet.get_Range(finalColumnIndex + "1", finalCellIndex).ColumnWidth = 50;
}
else if (column.Width == ExcelReporter.Width.Medium)
{
_worksheet.get_Range(finalColumnIndex + "1", finalCellIndex).ColumnWidth = 100;
}
else if (column.Width == ExcelReporter.Width.Wide)
{
_worksheet.get_Range(finalColumnIndex + "1", finalCellIndex).ColumnWidth = 150;
}
if (column.WarpText)
{
_worksheet.get_Range(finalColumnIndex + "1", finalCellIndex).EntireColumn.WrapText = true;
}
inColumn++;
}
string finalTitleIndex = finalColumnIndex + "1";
_worksheet.get_Range("A1", finalTitleIndex).Font.Bold = true;
_worksheet.get_Range("A1", finalTitleIndex).Font.ColorIndex = 1;
_worksheet.get_Range("A1", finalTitleIndex).Font.Size = 12;
_worksheet.get_Range("A1", finalTitleIndex).VerticalAlignment = XlVAlign.xlVAlignCenter;
_worksheet.get_Range("A1", finalTitleIndex).HorizontalAlignment = XlHAlign.xlHAlignCenter;
_worksheet.get_Range("A1", finalTitleIndex).EntireRow.WrapText = true;
_worksheet.get_Range("A2", finalCellIndex).Font.Size = 11;
_worksheet.get_Range("A2", finalCellIndex).VerticalAlignment = XlVAlign.xlVAlignCenter;
_worksheet.get_Range("A2", finalCellIndex).HorizontalAlignment = XlHAlign.xlHAlignLeft;
switch (sheetContent.RowHeight)
{
case ExcelReporter.Height.Short:
_worksheet.get_Range("A2", finalCellIndex).EntireRow.RowHeight = 18;
break;
case ExcelReporter.Height.Medium:
_worksheet.get_Range("A2", finalCellIndex).EntireRow.RowHeight = 28;
break;
case ExcelReporter.Height.Tall:
_worksheet.get_Range("A2", finalCellIndex).EntireRow.RowHeight = 38;
break;
default:
break;
}
if (sheetContent.DrawChart)
{
DrawChartOnSheet(strSheetName,
categoryTitle,
"values",
columnTitles.Count,
finalRawIndex,
finalColumnIndex,
finalCellIndex,
columnTitles);
}
_currentSheetIndex++;
}
