/// <summary>
/// Constructor. Writes metadata and Id column header to excel.
/// </summary>
public ExcelPresenterBase(string type, IExcelFile file, Views.IExportDialog dialog)
{
Views.Excel.Excel excel = new Views.Excel.Excel();
this._dialog = dialog;
this._file = file;
this._type = type;
this._dialog.Title = this._type;
this._dialog.Label1 = string.Empty;
this._dialog.Label2 = string.Empty;
this._dialog.StartButtonEnabled = false;
// create styles
// headers and metadata
var namedStyle = excel.Package.Workbook.Styles.CreateNamedStyle(HEADER_AND_META_STYLE);
namedStyle.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
namedStyle.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 0, 0, 170));
namedStyle.Style.Font.Bold = true;
namedStyle.Style.Font.Color.SetColor(Color.FromArgb(255, 255, 255, 255));
namedStyle.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
// even data row
namedStyle = excel.Package.Workbook.Styles.CreateNamedStyle(EVEN_ROW_STYLE);
namedStyle.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
namedStyle.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 239, 239, 255));
// odd data row
namedStyle = excel.Package.Workbook.Styles.CreateNamedStyle(ODD_ROW_STYLE);
namedStyle.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
namedStyle.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 207, 207, 255));
// write metadata
excel.Worksheet = excel.Package.Workbook.Worksheets.Add(type);
excel.Worksheet.Cells["A1"].Value = "MyLibrary";
excel.Worksheet.Cells[1, 1, 1, 2].Merge = true;
excel.Worksheet.Cells["A2"].Value = "Type";
excel.Worksheet.Cells["B2"].Value = this._type + "s";
excel.Worksheet.Cells["A3"].Value = "App Version:";
excel.Worksheet.Cells["B3"].Value = Configuration.APP_VERSION.ToString();
excel.Worksheet.Cells["A4"].Value = "Extracted At:";
excel.Worksheet.Cells["B4"].Value = DateTime.Now.ToString("dddd, dd MMMM yyyy HH:mm:ss");
excel.Worksheet.Cells["A1:B4"].StyleName = HEADER_AND_META_STYLE;
excel.Worksheet.Cells["A1:B4"].AutoFitColumns();
this._excel = excel;
// write Id column header
WriteHeaderCell("A", "Id");
// set the current row to the first data row, ready for writing entities
this._currRow = HEADER_ROW + 1;
// subscribe to dialog view's events
this._dialog.BrowseButtonClicked += BrowseButtonClicked;
this._dialog.StartButtonClicked += (async(sender, args) =>
{
await HandleStartButtonClicked(sender, args);
});
this._dialog.Cancelled += Closed;
this._dialog.Title = "Export " + this._type + "s";
}
/// <summary>
/// Creates Excel file based on T and saves it to specified path.
/// </summary>
public void Export <T>(IEnumerable <T> collection, List <string> columnNames, string path)
{
if (IsCollectionNullOrEmpty(collection) || AreColumnNamesNullOrEmpty(columnNames) || string.IsNullOrEmpty(path))
{
throw new ArgumentException("One of the arguments is null or empty.");
}
FileInfo fileInfo = CreateFileInfo(path);
using (IExcelFile excelFile = _excelCreator.Create(collection, sheetName, columnNames))
{
excelFile.Save(fileInfo);
}
}
/// <summary>
/// Returns list of T based on *.xlsx worksheet.
/// </summary>
public IEnumerable <T> Import(string path)
{
if (string.IsNullOrEmpty(path))
{
throw new ArgumentException("Path cannot be null or empty");
}
IEnumerable <T> collectionToReturn = new List <T>();
using (FileStream fileStream = _fileStreamWrapper.Init(path, FileMode.Open))
using (IExcelFile excelFile = _excelCreator.Create(fileStream))
{
collectionToReturn = new List <T>(excelFile.ConvertToObjects <T>());
}
return(collectionToReturn);
}
public void Test_Create_Should_Create_ExcelFile()
{
List <TestExcelObject> testExcelObjects = new List <TestExcelObject>
{
new TestExcelObject("name1", 1),
new TestExcelObject("name2", 2)
};
List <string> columnNames = new List <string>
{
"name",
"value"
};
string sheetName = "sheetName";
IExcelFile excelFile = _excelCreator.Create(testExcelObjects, sheetName, columnNames);
Assert.IsNotNull(excelFile);
}
//Get FirstSheetName
public static string GetFirstSheetName(string pathfile)
{
string firstsheet = "";
bool is2007 = false;
if (IsExcelFile(pathfile, out is2007))
{
IExcelFile excelfile = null;
if (is2007)
{
excelfile = new Excel2007(pathfile);
}
else
{
excelfile = new Excel2003(pathfile);
}
firstsheet = excelfile.GetFirstSheetName();
}
return(firstsheet);
}
//Get DataSheet
public static System.Data.DataTable LoadDataSheet(string pathfile, string sheetname)
{
System.Data.DataTable tbl = null;
bool is2007 = false;
if (IsExcelFile(pathfile, out is2007))
{
IExcelFile excelfile = null;
if (is2007)
{
excelfile = new Excel2007(pathfile);
}
else
{
excelfile = new Excel2003(pathfile);
}
tbl = excelfile.GetDataTable_SheetName(sheetname);
}
return(tbl);
}
//Get number of rows data in file
public static Int64 NumberRows(string pathfile, string sheetname)
{
int num = 0;
bool is2007 = false;
if (IsExcelFile(pathfile, out is2007))
{
IExcelFile excelfile = null;
if (is2007)
{
excelfile = new Excel2007(pathfile);
}
else
{
excelfile = new Excel2003(pathfile);
}
num = excelfile.GetNumberRows(sheetname);
}
return(num);
}
//Load Format of data in dataTable
public static System.Data.DataTable LoadTableColumns(string pathfile)
{
System.Data.DataTable tbl = null;
bool is2007 = false;
if (IsExcelFile(pathfile, out is2007))
{
IExcelFile excelfile = null;
if (is2007)
{
excelfile = new Excel2007(pathfile);
}
else
{
excelfile = new Excel2003(pathfile);
}
tbl = excelfile.GetDataTable_Format();
}
return(tbl);
}
/// <summary>
/// 获取office文档内容
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
private static string _GetContent(IOfficeFile file)
{
if (file is IWordFile)
{
IWordFile wordFile = file as IWordFile;
return(wordFile.ParagraphText);
}
else if (file is IPowerPointFile)
{
IPowerPointFile pptFile = file as IPowerPointFile;
return(pptFile.AllText);
}
else if (file is ITextFile)
{
ITextFile textFile = file as ITextFile;
return(textFile.CommentText);
}
else if (file is IPDFFile)
{
IPDFFile pdfFile = file as IPDFFile;
return(pdfFile.CommentText);
}
else if (file is IHtmlFile)
{
IHtmlFile htmlFile = file as IHtmlFile;
return(htmlFile.CommentText);
}
else if (file is IExcelFile)
{
IExcelFile excelFile = file as IExcelFile;
return(excelFile.CommentText);
}
else
{
return(String.Format("无法解析"));
}
}
public ExcelClusterPattern(IExcelFile _myFile)
{
myFile = _myFile;
}
public ExcelStaticFileCluster(IExcelFile _myFile) : base(_myFile)
{
}
public ExcelOpenedFileCluster(IExcelFile _myFile) : base(_myFile)
{
}
public TagExcelPresenter(ITagService tagService, IExcelFile file, Views.IExportDialog dialog)
: base("Tag", file, dialog)
{
this._tagService = tagService;
WriteHeaders();
}
public MockPresenter(IWishlistService wishlistService, IExcelFile file, IExportDialog dialog)
: base(wishlistService, file, dialog)
{
}
public MockPresenter(IMediaItemService itemService, IExcelFile file, IExportDialog dialog)
: base(itemService, file, dialog)
{
}
public BookExcelPresenter(IBookService bookService, IExcelFile file, Views.IExportDialog dialog)
: base("Book", file, dialog)
{
this._bookService = bookService;
WriteHeaders();
}
public WishlistExcelPresenter(IWishlistService wishlistService, IExcelFile file, Views.IExportDialog dialog)
: base("Wishlist item", file, dialog)
{
this._wishlistService = wishlistService;
WriteHeaders();
}
public MockPresenter(IAuthorService authorService, IExcelFile file, IExportDialog dialog)
: base(authorService, file, dialog)
{
}
protected override void OnDeactivate(bool close)
{
base.OnDeactivate(close);
if (close)
{
if (excelFile != null)
{
excelFile.Dispose();
excelFile = null;
}
}
}
public void Browse()
{
var dialog = new OpenFileDialog();
dialog.FileName = Filename;
dialog.Filter = null;
var result = dialog.ShowDialog() ?? false;
if (result)
{
Filename = dialog.FileName;
if (excelFile != null)
{
excelFile.Dispose();
excelFile = null;
}
excelFile = excelService.Open(filename);
Sheets.Clear();
var worksheets = excelFile.Worksheets;
foreach (var sheet in worksheets)
{
Sheets.Add(sheet.Name);
}
}
}
public MockPresenter(IBookService bookService, IExcelFile file, IExportDialog dialog)
: base(bookService, file, dialog)
{
}
public AuthorExcelPresenter(IAuthorService authorService, IExcelFile file, Views.IExportDialog dialog)
: base("Author", file, dialog)
{
this._authorService = authorService;
WriteHeaders();
}
public MediaItemExcelPresenter(IMediaItemService tagService, IExcelFile file, Views.IExportDialog dialog)
: base("Media item", file, dialog)
{
this._mediaItemService = tagService;
WriteHeaders();
}
public MockPresenter(IPublisherService publisherService, IExcelFile file, IExportDialog dialog)
: base(publisherService, file, dialog)
{
}
public MockPresenter(ITagService tagService, IExcelFile file, IExportDialog dialog)
: base(tagService, file, dialog)
{
}
public PublisherExcelPresenter(IPublisherService publisherService, IExcelFile file, Views.IExportDialog dialog)
: base("Publisher", file, dialog)
{
this._publisherService = publisherService;
WriteHeaders();
}