public ExcelWorksheet AddComment(ExcelWorksheet excelWorksheet, ExcelRange excelRange, string comment, string author)
{
//Adding a comment to a Cell
ExcelRange commentCell = excelWorksheet.Cells[excelRange.FullAddress];
commentCell.AddComment(comment, author);
return(excelWorksheet);
}
public byte[] Write(DatasetDefinition data)
{
if (data == null)
{
throw new ArgumentNullException(nameof(data), "Null dataset definition was provided");
}
if (data.TableDefinitions == null || !data.TableDefinitions.Any())
{
return(null);
}
using (ExcelPackage package = new ExcelPackage())
{
foreach (TableDefinition tableDefinition in data.TableDefinitions)
{
int fieldCount = tableDefinition.FieldDefinitions.Count();
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(tableDefinition.Name);
IEnumerable <ExcelCellModel> headers = CreateHeaders(tableDefinition.FieldDefinitions);
for (int i = 1; i <= headers.Count(); i++)
{
ExcelCellModel excelCellModel = headers.ElementAt(i - 1);
ExcelRange cell = workSheet.Cells[1, i];
cell.Value = excelCellModel.Value;
cell.AddComment(excelCellModel.Comment, "Calculate Funding");
cell.Style.Font.Bold = true;
}
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
}
return(package.GetAsByteArray());
}
}
public static void WriteSpreadsheet(InternalSpreadsheet spreadsheet, string outputPath, bool retainMarkup, IWebSocketProgress progress = null)
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("BloomBook");
worksheet.DefaultColWidth = languageColumnWidth;
for (int i = 1; i <= spreadsheet.StandardLeadingColumns.Length; i++)
{
worksheet.Column(i).Width = standardLeadingColumnWidth;
}
var imageSourceColumn = spreadsheet.GetColumnForTag(InternalSpreadsheet.ImageSourceColumnLabel);
var imageThumbnailColumn = spreadsheet.GetColumnForTag(InternalSpreadsheet.ImageThumbnailColumnLabel);
// Apparently the width is in some approximation of 'characters'. This empirically determined
// conversion factor seems to do a pretty good job.
worksheet.Column(imageThumbnailColumn + 1).Width = defaultImageWidth / 6.88;
int r = 0;
foreach (var row in spreadsheet.AllRows())
{
r++;
for (var c = 0; c < row.Count; c++)
{
// Enhance: Excel complains about cells that contain pure numbers
// but are created as strings. We could possibly tell it that cells
// that contain simple numbers can be treated accordingly.
// It might be helpful for some uses of the group-on-page-index
// if Excel knew to treat them as numbers.
var sourceCell = row.GetCell(c);
var content = sourceCell.Content;
// Parse xml for markdown formatting on language columns,
// Display formatting in excel spreadsheet
ExcelRange currentCell = worksheet.Cells[r, c + 1];
if (!string.IsNullOrEmpty(sourceCell.Comment))
{
// Second arg is supposed to be the author.
currentCell.AddComment(sourceCell.Comment, "Bloom");
}
if (!retainMarkup &&
IsWysiwygFormattedColumn(row, c) &&
IsWysiwygFormattedRow(row))
{
MarkedUpText markedUpText = MarkedUpText.ParseXml(content);
if (markedUpText.HasFormatting)
{
currentCell.IsRichText = true;
foreach (MarkedUpTextRun run in markedUpText.Runs)
{
if (!run.Text.Equals(""))
{
ExcelRichText text = currentCell.RichText.Add(run.Text);
text.Bold = run.Bold;
text.Italic = run.Italic;
text.UnderLine = run.Underlined;
if (run.Superscript)
{
text.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;
}
}
}
}
else
{
currentCell.Value = markedUpText.PlainText();
}
}
else
{
// Either the retainMarkup flag is set, or this is not book text. It could be header or leading column.
// Generally, we just want to blast our cell content into the spreadsheet cell.
// However, there are cases where we put an error message in an image thumbnail cell when processing the image path.
// We don't want to overwrite these. An easy way to prevent it is to not overwrite any cell that already has content.
// Since export is creating a new spreadsheet, cells we want to write will always be empty initially.
if (currentCell.Value == null)
{
currentCell.Value = content;
}
}
//Embed any images in the excel file
if (c == imageSourceColumn)
{
var imageSrc = sourceCell.Content;
// if this row has an image source value that is not a header
if (imageSrc != "" && !row.IsHeader)
{
var sheetFolder = Path.GetDirectoryName(outputPath);
var imagePath = Path.Combine(sheetFolder, imageSrc);
//Images show up in the cell 1 row greater and 1 column greater than assigned
//So this will put them in row r, column imageThumbnailColumn+1 like we want
var rowHeight = embedImage(imagePath, r - 1, imageThumbnailColumn);
worksheet.Row(r).Height = rowHeight * 72 / 96 + 3; //so the image is visible; height seems to be points
}
}
}
if (row is HeaderRow)
{
using (ExcelRange rng = GetRangeForRow(worksheet, r))
rng.Style.Font.Bold = true;
}
if (row.Hidden)
{
worksheet.Row(r).Hidden = true;
SetBackgroundColorOfRow(worksheet, r, InternalSpreadsheet.HiddenColor);
}
else if (row.BackgroundColor != default(Color))
{
SetBackgroundColorOfRow(worksheet, r, row.BackgroundColor);
}
}
worksheet.Cells[1, 1, r, spreadsheet.ColumnCount].Style.WrapText = true;
int embedImage(string imageSrcPath, int rowNum, int colNum)
{
int finalHeight = 30; // a reasonable default if we don't manage to embed an image.
try
{
using (Image image = Image.FromFile(imageSrcPath))
{
string imageName = Path.GetFileNameWithoutExtension(imageSrcPath);
var origImageHeight = image.Size.Height;
var origImageWidth = image.Size.Width;
int finalWidth = defaultImageWidth;
finalHeight = (int)(finalWidth * origImageHeight / origImageWidth);
var size = new Size(finalWidth, finalHeight);
using (Image thumbnail = ImageUtils.ResizeImageIfNecessary(size, image, false))
{
var excelImage = worksheet.Drawings.AddPicture(imageName, thumbnail);
excelImage.SetPosition(rowNum, 2, colNum, 2);
}
}
}
catch (Exception)
{
string errorText;
if (!RobustFile.Exists(imageSrcPath))
{
errorText = "Missing";
}
else if (Path.GetExtension(imageSrcPath).ToLowerInvariant().Equals(".svg"))
{
errorText = "Can't display SVG";
}
else
{
errorText = "Bad image file";
}
progress?.MessageWithoutLocalizing(errorText + ": " + imageSrcPath);
worksheet.Cells[r, imageThumbnailColumn + 1].Value = errorText;
}
return(Math.Max(finalHeight, 30));
}
foreach (var iColumn in spreadsheet.HiddenColumns)
{
// This is pretty yucky... our internal spreadsheet is all 0-based, but the EPPlus library is all 1-based...
var iColumn1Based = iColumn + 1;
worksheet.Column(iColumn1Based).Hidden = true;
SetBackgroundColorOfColumn(worksheet, iColumn1Based, InternalSpreadsheet.HiddenColor);
}
try
{
RobustFile.Delete(outputPath);
var xlFile = new FileInfo(outputPath);
package.SaveAs(xlFile);
}
catch (IOException ex) when((ex.HResult & 0x0000FFFF) == 32) //ERROR_SHARING_VIOLATION
{
Console.WriteLine("Writing Spreadsheet failed. Do you have it open in another program?");
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
progress?.Message("Spreadsheet.SpreadsheetLocked", "",
"Bloom could not write to the spreadsheet because another program has it locked. Do you have it open in another program?",
ProgressKind.Error);
}
catch (Exception ex)
{
progress?.Message("Spreadsheet.ExportFailed", "",
"Export failed: " + ex.Message,
ProgressKind.Error);
}
}
}
public byte[] Write(DatasetDefinition datasetDefinition, IEnumerable <TableLoadResult> data = null)
{
if (datasetDefinition == null)
{
throw new ArgumentNullException(nameof(datasetDefinition), "Null dataset definition was provided");
}
if (datasetDefinition.TableDefinitions == null || !datasetDefinition.TableDefinitions.Any())
{
return(null);
}
bool haveData = data != null;
using (ExcelPackage package = new ExcelPackage())
{
foreach (TableDefinition tableDefinition in datasetDefinition.TableDefinitions)
{
int fieldCount = tableDefinition.FieldDefinitions.Count();
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(tableDefinition.Name);
IEnumerable <ExcelCellModel> headers = CreateHeaders(tableDefinition.FieldDefinitions);
for (int i = 1; i <= headers.Count(); i++)
{
ExcelCellModel excelCellModel = headers.ElementAt(i - 1);
ExcelRange cell = workSheet.Cells[1, i];
cell.Value = excelCellModel.Value;
cell.AddComment(excelCellModel.Comment, "Calculate Funding");
cell.Style.Font.Bold = true;
}
if (haveData)
{
TableLoadResult tableData = data.FirstOrDefault(x => x.TableDefinition.Name == tableDefinition.Name);
if (tableData != null)
{
int rowNumber = 2;
foreach (RowLoadResult row in tableData.Rows)
{
for (int i = 1; i <= headers.Count(); i++)
{
ExcelCellModel excelCellModel = headers.ElementAt(i - 1);
object fieldValue = row.Fields.FirstOrDefault(x => x.Key == excelCellModel.Value.ToString()).Value;
if (fieldValue != null)
{
workSheet.Cells[rowNumber, i].Value = fieldValue;
}
}
rowNumber++;
}
}
}
workSheet.Cells[workSheet.Dimension.Address].AutoFitColumns();
}
return(package.GetAsByteArray());
}
}
private void RenderCell(ExcelWorksheet sheet, HtmlNode cellNode, ref int row, ref int col)
{
ExcelRange cell = sheet.Cells[row, col];
cell.Value = cellNode.InnerText.SafeTrim();
if (cellNode.Name == "th")
{ // Set font bold for th elements
cell.Style.Font.Bold = true;
}
var styles = ParseStyles(cellNode);
if (styles != null)
{
ApplyStyles(cell, styles);
}
if (cellNode.HasAttributes)
{
HtmlAttribute boldAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-bold");
if (boldAttribute != null)
{
if (bool.TryParse(boldAttribute.Value, out bool isBold))
{
cell.Style.Font.Bold = isBold;
}
}
HtmlAttribute hyperlinkAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-hyperlink");
if (hyperlinkAttribute != null)
{
if (Uri.TryCreate(hyperlinkAttribute.Value, UriKind.Absolute, out Uri uri))
{
cell.Hyperlink = uri;
cell.Style.Font.Color.SetColor(System.Drawing.Color.Blue);
cell.Style.Font.UnderLine = true;
}
else
{
cell.AddComment("Unable to parse hyperlink: " + hyperlinkAttribute.Value, "TowerSoft.HtmlToExcel");
}
}
HtmlAttribute commentAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-comment");
if (commentAttribute != null && !string.IsNullOrWhiteSpace(commentAttribute.Value))
{
string author = "System";
HtmlAttribute authorAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-comment-author");
if (authorAttribute != null && !string.IsNullOrWhiteSpace(authorAttribute.Value))
{
author = authorAttribute.Value;
}
cell.AddComment(commentAttribute.Value, author);
}
}
int colspan = cellNode.GetAttributeValue("colspan", 1);
if (colspan > 1)
{
sheet.Cells[row, col, row, col + colspan - 1].Merge = true;
_hasMergedCells = true;
col += colspan;
}
else
{
col++;
}
}
public IActionResult Index(string startDate, string endDate)
{
var title = "";
if (startDate == null)
{
bills = _statisticalRepository.GetAllBills();
products = _statisticalRepository.GetTopProducts();
cataloges = _statisticalRepository.GetTopCataloges();
}
else
{
if (startDate.Equals(endDate))
{
title = " NGÀY " + Convert.ToDateTime(startDate).ToString("dd-MM-yyyy");
}
else
{
title = " TỪ " + Convert.ToDateTime(startDate).ToString("dd-MM-yyyy") + " ĐẾN " + Convert.ToDateTime(endDate).ToString("dd-MM-yyyy");
}
bills = _statisticalRepository.GetAllBillsByDate(startDate, endDate);
products = _statisticalRepository.GetTopProductsByDate(startDate, endDate);
cataloges = _statisticalRepository.GetTopCatalogesByDate(startDate, endDate);
}
byte[] fileContents;
using (var package = new ExcelPackage())
{
var ws = package.Workbook.Worksheets.Add("Sheet1");
var titlePosition = "B2:F3";
var datePosition = "B5:D5";
var tablePosition = 6;
var tableFirstRow = 7;
//===========================================Bang doanh thu==================================================
ws.Cells["B" + tablePosition].LoadFromCollection(bills, true, TableStyles.Medium14);
var tableLastRow = ws.Dimension.End.Row;
var tbBillLastRow = tableLastRow + 1;
ws.Cells[titlePosition].Merge = true;
ws.Cells[titlePosition].Value = "THỐNG KÊ DOANH THU" + title;
ws.Cells[titlePosition].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[titlePosition].Style.Font.Size = 20;
ws.Cells[titlePosition].Style.Font.Bold = true;
ws.Cells[datePosition].Merge = true;
ws.Cells[datePosition].Value = "Ngày tạo: " + DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss");
for (int i = tableFirstRow; i < tbBillLastRow; i++)
{
using (ExcelRange Rng = ws.Cells["D" + i])
{
if (!string.IsNullOrEmpty(Rng.Text))
{
ExcelComment cmd = Rng.AddComment(Rng.Value.ToString(), "Apt. Notes");
}
}
}
;
ws.Column(5).Style.Numberformat.Format = "dd-MM-yyyy HH:mm:ss";
ws.Column(6).Style.Numberformat.Format = "#,##0 [$VND]";
ws.Cells["B" + tbBillLastRow].Value = "Tổng Cộng";
ws.Cells["F" + tbBillLastRow].Formula = "=SUM(F" + tableFirstRow + ":F" + (tbBillLastRow - 1) + ")";
ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(112, 173, 71));
ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Font.Color.SetColor(Color.White);
ws.Cells["B" + tbBillLastRow + ":F" + tbBillLastRow].Style.Font.Bold = true;
ws.Cells["B" + tablePosition].Value = "#";
for (int i = 0; i < bills.Count(); i++)
{
ws.Cells["B" + (tableFirstRow + i)].Value = i + 1;
}
//===========================================Bang cataloges==================================================
var titleCataPosition = "H4:J4";
var tbCataLastRow = tablePosition + cataloges.Count();
var totalCata = tbCataLastRow + 1;
ws.Cells["I" + tablePosition].LoadFromCollection(cataloges, true);
ws.Cells["H" + tablePosition].Value = "#";
for (int i = 0; i < cataloges.Count(); i++)
{
ws.Cells["H" + (tableFirstRow + i)].Value = i + 1;
}
ws.Cells[titleCataPosition].Value = "TOP DANH MỤC";
ws.Cells[titleCataPosition].Style.Font.Size = 14;
ws.Cells[titleCataPosition].Style.Font.Bold = true;
ws.Cells[titleCataPosition].Merge = true;
ExcelRange range = ws.Cells["H" + tablePosition + ":J" + tbCataLastRow];
ExcelTable tab = ws.Tables.Add(range, "TopCataloges");
tab.TableStyle = TableStyles.Light9;
ws.Cells["H" + totalCata].Value = "Tổng Cộng";
ws.Cells["J" + totalCata].Formula = "=SUM(J" + tableFirstRow + ":J" + tbCataLastRow + ")";
ws.Cells["H" + totalCata + ":J" + totalCata].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells["H" + totalCata + ":J" + totalCata].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(155, 194, 230));
ws.Cells["H" + totalCata + ":J" + totalCata].Style.Font.Color.SetColor(Color.White);
ws.Cells["H" + totalCata + ":J" + totalCata].Style.Font.Bold = true;
//===========================================Bang product==================================================
var titleProductPosition = "L4:P4";
ws.Cells["M" + tablePosition].LoadFromCollection(products, true);
ws.Cells["L" + tablePosition].Value = "#";
for (int i = 0; i < products.Count(); i++)
{
ws.Cells["L" + (7 + i)].Value = i + 1;
}
ws.Cells[titleProductPosition].Value = "TOP SẢN PHẨM";
ws.Cells[titleProductPosition].Style.Font.Size = 14;
ws.Cells[titleProductPosition].Style.Font.Bold = true;
ws.Cells[titleProductPosition].Merge = true;
ExcelRange range2 = ws.Cells["L" + tablePosition + ":" + ws.Dimension.End.Address];
ExcelTable tab2 = ws.Tables.Add(range2, "TopProducts");
tab2.TableStyle = TableStyles.Medium4;
ws.Column(14).Style.Numberformat.Format = "#,##0 [$VND]";
ws.Column(16).Style.Numberformat.Format = "#,##0 [$VND]";
//================================================================================================
ws.Cells["B" + tableFirstRow + ":B" + tbBillLastRow].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Cells["E" + tableFirstRow + ":E" + tbBillLastRow].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
ws.Row(6).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Row(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[ws.Dimension.Address].AutoFitColumns();
for (int col = 1; col <= ws.Dimension.End.Column; col++)
{
ws.Column(col).Width = ws.Column(col).Width + 2;
}
ws.Cells["D7"].AutoFitColumns(30.00, 40.00);
ws.Column(1).Width = 4;
// Send to browser
fileContents = package.GetAsByteArray();
}
if (fileContents == null || fileContents.Length == 0)
{
return(NotFound());
}
return(File(
fileContents: fileContents,
contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
fileDownloadName: "report.xlsx"
));
}
public static void Render(this ICell cell, int renderIndex, ExcelRange range)
{
var cellValue = cell.GetValue(renderIndex);
if (cellValue != null && cellValue.Value != null)
{
if (cellValue.Value is double dValue)
{
if (dValue != 0)
{
range.Value = cellValue.Value;
}
}
else if (cellValue.Value is int iValue)
{
if (iValue != 0)
{
range.Value = cellValue.Value;
}
}
else if (cellValue.Value is string sValue)
{
range.Style.WrapText = true;
range.Value = cellValue.Value;
}
else
{
range.Value = cellValue.Value;
}
if (cellValue is DoubleValue doubleValue)
{
range.Style.Numberformat.Format = doubleValue.NumberFormat;
}
else if (cellValue.Value is DateTime)
{
range.Style.Numberformat.Format = "dd/MM/yyyy";
}
}
cell.Styles.Cast <ISheetStyle>().ToList().ForEach(style =>
{
if (style != null)
{
style.Render(range, cell, cell.GetValue(renderIndex));
if (style.NumberFormat != null)
{
range.Style.Numberformat.Format = style.NumberFormat;
}
}
});
if (cell.CanPopup())
{
range.AddComment($"{cell.PopupTitle}:\n{cell.PopupText}", "mustafa.dagci");
range.Comment.AutoFit = true;
}
if (cell is ICustomSheetCell customCell)
{
customCell.Render(range, cell.Parent, cell);
}
}
private void RenderCell(ExcelWorksheet sheet, IElement cellNode, ref int row, ref int col)
{
ExcelRange cell = sheet.Cells[row, col];
cell.Value = cellNode.ChildNodes.OfType <IText>().Select(m => m.Text).FirstOrDefault();
if (cellNode.NodeName == "th") // Set font bold for th elements
{
cell.Style.Font.Bold = true;
}
if (cellNode.Attributes != null && cellNode.Attributes.Any())
{
IAttr boldAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-bold");
if (boldAttribute != null)
{
if (bool.TryParse(boldAttribute.Value, out bool isBold))
{
cell.Style.Font.Bold = isBold;
}
}
IAttr hyperlinkAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-hyperlink");
if (hyperlinkAttribute != null)
{
if (Uri.TryCreate(hyperlinkAttribute.Value, UriKind.Absolute, out Uri uri))
{
cell.Hyperlink = uri;
cell.Style.Font.Color.SetColor(Color.Blue);
cell.Style.Font.UnderLine = true;
}
else
{
cell.AddComment("Unable to parse hyperlink: " + hyperlinkAttribute.Value, "TowerSoft.HtmlToExcel");
}
}
IAttr commentAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-comment");
if (commentAttribute != null && !string.IsNullOrWhiteSpace(commentAttribute.Value))
{
string author = "System";
IAttr authorAttribute = cellNode.Attributes.SingleOrDefault(x => x.Name == "data-excel-comment-author");
if (authorAttribute != null && !string.IsNullOrWhiteSpace(authorAttribute.Value))
{
author = authorAttribute.Value;
}
cell.AddComment(commentAttribute.Value, author);
}
}
if (int.TryParse(cellNode.GetAttribute("colspan"), out int colspan))
{
if (colspan > 1)
{
sheet.Cells[row, col, row, col + colspan - 1].Merge = true;
_hasMergedCells = true;
col += colspan;
}
else
{
col++;
}
}
else
{
col++;
}
}