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++; } }