public bool Validate(InternalSpreadsheet sheet, IWebSocketProgress progress) { // An export would have several others. But none of them is absolutely required except this one. // (We could do without it, too, by assuming the first column contains them. But it's helpful to be // able to recognize spreadsheets created without any knowledge at all of the expected content.) // Note: depending on row content, this problem may be detected earlier in SpreadsheetIO while // converting the file to an InternalSpreadsheet. var rowTypeColumn = sheet.GetColumnForTag(InternalSpreadsheet.RowTypeColumnLabel); if (rowTypeColumn < 0) { progress.MessageWithoutLocalizing(MissingHeaderMessage, ProgressKind.Error); return(false); } var inputRows = sheet.ContentRows.ToList(); if (!inputRows.Any(r => r.GetCell(rowTypeColumn).Content.StartsWith("["))) { progress.MessageWithoutLocalizing("This spreadsheet has no data that Bloom knows how to import. Did you follow the standard format for Bloom spreadsheets?", ProgressKind.Warning); // Technically this isn't a fatal error. We could just let the main loop do nothing. But reporting it as one avoids creating a spurious backup. return(false); } return(true); }
private void UpdateDataDivFromRow(ContentRow currentRow, string dataBookLabel) { if (dataBookLabel.Contains("branding")) { return; // branding data-div elements are complex and difficult and determined by current collection state } // Only a few of these are worth reporting string whatsUpdated = null; switch (dataBookLabel) { case "coverImage": whatsUpdated = "the image on the cover"; break; case "bookTitle": whatsUpdated = "the book title"; break; case "copyright": whatsUpdated = "copyright information"; break; } if (whatsUpdated != null) { Progress($"Updating {whatsUpdated}."); } var xPath = "div[@data-book=\"" + dataBookLabel + "\"]"; var matchingNodes = _dataDivElement.SelectNodes(xPath); XmlElement templateNode; bool templateNodeIsNew = false; if (matchingNodes.Count > 0) { templateNode = (XmlElement)matchingNodes[0]; } else { templateNodeIsNew = true; templateNode = _destinationDom.RawDom.CreateElement("div"); templateNode.SetAttribute("data-book", dataBookLabel); } var imageSrcCol = _sheet.GetColumnForTag(InternalSpreadsheet.ImageSourceColumnLabel); var imageSrc = imageSrcCol >= 0 ? currentRow.GetCell(imageSrcCol).Content : null; // includes "images" folder var imageFileName = Path.GetFileName(imageSrc); bool specificLanguageContentFound = false; bool asteriskContentFound = false; //Whether or not a data-book div has a src attribute, we found that the innerText is used to set the //src of the image in the actual pages of the document, though we haven't found a case where they differ. //So during export we put the innerText into the image source column, and want to put it into //both src and innertext on import, unless the element is in the noSrcAttribute list if (imageFileName.Length > 0) { templateNode.SetAttribute("lang", "*"); templateNode.InnerText = imageFileName; if (!SpreadsheetExporter.DataDivImagesWithNoSrcAttributes.Contains(dataBookLabel)) { templateNode.SetAttribute("src", imageFileName); } if (templateNodeIsNew) { AddDataBookNode(templateNode); } if (_pathToSpreadsheetFolder != null) { // Make sure the image gets copied over too. var fullSpreadsheetPath = Path.Combine(_pathToSpreadsheetFolder, imageSrc); CopyImageFileToDestination(imageFileName, fullSpreadsheetPath); } } else //This is not an image node { if (dataBookLabel.Equals("coverImage")) { Warn("No cover image found"); } foreach (string lang in _sheet.Languages) { var langVal = currentRow.GetCell(_sheet.GetRequiredColumnForLang(lang)).Content; var langXPath = "div[@data-book=\"" + dataBookLabel + "\" and @lang=\"" + lang + "\"]"; var langMatchingNodes = _dataDivElement.SelectNodes(langXPath).Cast <XmlElement>(); if (!string.IsNullOrEmpty(langVal)) { //Found content in spreadsheet for this language and row if (lang.Equals("*")) { asteriskContentFound = true; } else { specificLanguageContentFound = true; } if (langMatchingNodes.Count() > 0) //Found matching node in dom. Update node. { XmlElement matchingNode = langMatchingNodes.First(); matchingNode.InnerXml = langVal; if (langMatchingNodes.Count() > 1) { Warn("Found more than one " + dataBookLabel + " element for language " + lang + " in the book dom. Only the first will be updated."); } } else //No node for this language and data-book. Create one from template and add. { XmlElement newNode = (XmlElement)templateNode.CloneNode(deep: true); newNode.SetAttribute("lang", lang); newNode.InnerXml = langVal; AddDataBookNode(newNode); } } else //Spreadsheet cell for this row and language is empty. Remove the corresponding node if present. { foreach (XmlNode n in langMatchingNodes.ToArray()) { _dataDivElement.RemoveChild(n); } } } if (RemoveOtherLanguages) { HtmlDom.RemoveOtherLanguages(matchingNodes.Cast <XmlElement>().ToList(), _dataDivElement, _sheet.Languages); } if (asteriskContentFound && specificLanguageContentFound) { Warn(dataBookLabel + " information found in both * language column and other language column(s)"); } } }
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 void SetCell(string columnName, string content) { int index = Spreadsheet.GetColumnForTag(columnName); SetCell(index, content); }