private static void ExportToExcelDemo()
{
var outputPathForExcelFiles = "..\\..\\Documents\\NewExcelFiles\\";
var newFileName = "Pilots.xlsx";
var contextSqLite = new ContextSQLite();
var newExcelFile = ExcelExporter.ExportDbToExcel(outputPathForExcelFiles, newFileName, contextSqLite);
Console.WriteLine(newExcelFile);
}
private static void SQLiteDemo()
{
ContextSQLite contextSqLite = new ContextSQLite();
contextSqLite.Pilots.Add(new Pilot
{
Name = "Ivan",
Position = "Senior Pilot"
});
contextSqLite.Pilots.Add(new Pilot
{
Name = "Dragan",
Position = "Junior Pilot"
});
contextSqLite.Pilots.Add(new Pilot
{
Name = "Misho",
Position = "Interim Pilot"
});
contextSqLite.Pilots.Add(new Pilot
{
Name = "Stamat",
Position = "Senior Pilot"
});
contextSqLite.SaveChanges();
Console.WriteLine(contextSqLite.Pilots.First().Name);
}
public static string ExportDbToExcel(string directoryPath, string fileName, ContextSQLite contextSqLite)
{
if (directoryPath != null && !Directory.Exists(directoryPath))
{
Directory.CreateDirectory(directoryPath);
}
FileInfo newFile = new FileInfo(directoryPath + fileName);
if (newFile.Exists)
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(directoryPath + fileName);
}
var pilots = contextSqLite.Pilots.ToList();
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Pilots");
//Add the headers
worksheet.Cells[1, 1].Value = "ID";
worksheet.Cells[1, 2].Value = "Name";
worksheet.Cells[1, 3].Value = "Position";
//Add some items...
int currentRow = 2;
foreach (var pilot in pilots)
{
worksheet.Cells[currentRow, 1].Value = pilot.Id;
worksheet.Cells[currentRow, 2].Value = pilot.Name;
worksheet.Cells[currentRow, 3].Value = pilot.Position;
currentRow++;
}
//Ok now format the values;
using (var range = worksheet.Cells[1, 1, 1, 3])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
//worksheet.Cells["A4:E4"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
//worksheet.Cells["A4:E4"].Style.Font.Bold = true;
//Create an autofilter for the range
worksheet.Cells["A1:C1"].AutoFilter = true;
worksheet.Cells["A1:C1"].AutoFitColumns(0);
//worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
//worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];
// Change the sheet view to show it in page layout mode
worksheet.View.PageLayoutView = true;
// set some document properties
package.Workbook.Properties.Title = "Pilots";
package.Workbook.Properties.Author = "Astatine Team";
package.Workbook.Properties.Comments = "Pilots in database";
// set some extended property values
package.Workbook.Properties.Company = "Astatine Team";
// set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("Database", "SQLite");
// save our new workbook and we are done!
package.Save();
}
return newFile.FullName;
}