public static InternalSpreadsheet ReadFromFile(string path, IWebSocketProgress progress = null)
{
progress = progress ?? new NullWebSocketProgress();
var result = new InternalSpreadsheet(false);
try
{
SpreadsheetIO.ReadSpreadsheet(result, path);
}
catch (InvalidDataException e)
{
Bloom.Utils.MiscUtils.SuppressUnusedExceptionVarWarning(e);
progress.MessageWithoutLocalizing(
"The input does not appear to be a valid Excel spreadsheet. Import failed.", ProgressKind.Error);
return(null);
}
catch (SpreadsheetException se)
{
progress.MessageWithoutLocalizing(se.Message, ProgressKind.Error);
return(null);
}
catch (Exception e)
{
progress.MessageWithoutLocalizing("Something went wrong reading the input file. Import failed. " + e.Message, ProgressKind.Error);
return(null);
}
return(result);
}
public static InternalSpreadsheet ReadFromFile(string path)
{
var result = new InternalSpreadsheet();
SpreadsheetIO.ReadSpreadsheet(result, path);
return(result);
}
public Header(InternalSpreadsheet spreadsheet)
{
this.Spreadsheet = spreadsheet;
HeaderRows = new List <HeaderRow>()
{
new HeaderRow(Spreadsheet),
new HeaderRow(Spreadsheet)
};
ColumnIdRow.Hidden = true;
}
public void ImportWithProgress(string inputFilepath)
{
Debug.Assert(_pathToBookFolder != null,
"Somehow we made it into ImportWithProgress() without a path to the book folder");
var mainShell = Application.OpenForms.Cast <Form>().FirstOrDefault(f => f is Shell);
BrowserProgressDialog.DoWorkWithProgressDialog(_webSocketServer, "spreadsheet-import", () =>
new ReactDialog("progressDialogBundle",
// props to send to the react component
new
{
title = "Importing Spreadsheet",
titleIcon = "", // enhance: add icon if wanted
titleColor = "white",
titleBackgroundColor = Palette.kBloomBlueHex,
webSocketContext = "spreadsheet-import",
showReportButton = "if-error"
}, "Import Spreadsheet")
// winforms dialog properties
{
Width = 620, Height = 550
}, (progress, worker) =>
{
var hasAudio = _destinationDom.GetRecordedAudioSentences(_pathToBookFolder).Any();
var cannotImportEnding = " For this reason, we need to abandon the import. Instead, you can import into a blank book.";
if (hasAudio)
{
progress.MessageWithoutLocalizing($"Warning: Spreadsheet import cannot currently preserve Talking Book audio that is already in this book." + cannotImportEnding, ProgressKind.Error);
return(true); // leave progress window up so user can see error.
}
var hasActivities = _destinationDom.HasActivityPages();
if (hasActivities)
{
progress.MessageWithoutLocalizing($"Warning: Spreadsheet import cannot currently preserve quizzes, widgets, or other activities that are already in this book." + cannotImportEnding, ProgressKind.Error);
return(true); // leave progress window up so user can see error.
}
var sheet = InternalSpreadsheet.ReadFromFile(inputFilepath, progress);
if (sheet == null)
{
return(true);
}
if (!Validate(sheet, progress))
{
return(true); // errors already reported to progress
}
progress.MessageWithoutLocalizing($"Making a backup of the original book...");
var backupPath = BookStorage.SaveCopyBeforeImportOverwrite(_pathToBookFolder);
progress.MessageWithoutLocalizing($"Backup completed (at {backupPath})");
Import(sheet, progress);
return(true); // always leave the dialog up until the user chooses 'close'
}, null, mainShell);
}
public static void ReadSpreadsheet(InternalSpreadsheet spreadsheet, string path)
{
var info = new FileInfo(path);
using (var package = new ExcelPackage(info))
{
var worksheet = package.Workbook.Worksheets[0];
var rowCount = worksheet.Dimension.Rows;
var colCount = worksheet.Dimension.Columns;
// Enhance: eventually we should detect any rows that are not ContentRows,
// and either drop them or make plain SpreadsheetRows.
ReadRow(worksheet, 0, colCount, spreadsheet.Header);
for (var r = 1; r < rowCount; r++)
{
var row = new ContentRow();
ReadRow(worksheet, r, colCount, row);
spreadsheet.AddRow(row);
}
}
}
public static void WriteSpreadsheet(InternalSpreadsheet spreadsheet, string path)
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("BloomBook");
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.
worksheet.Cells[r, c + 1].Value = row.GetCell(c).Content;
}
}
// Review: is this helpful? Excel typically makes very small cells, so almost
// nothing of a cell's content can be seen, and that only markup. But it also
// starts out with very narrow cells, so WrapText makes them almost unmanageably tall.
worksheet.Cells[1, 1, r, spreadsheet.ColumnCount].Style.WrapText = true;
try
{
RobustFile.Delete(path);
var xlFile = new FileInfo(path);
package.SaveAs(xlFile);
}
catch (Exception ex)
{
Console.WriteLine("Writing Spreadsheet failed. Do you have it open in Excel?");
Console.WriteLine(ex.Message);
Console.WriteLine(ex.StackTrace);
}
}
}
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);
}
/// <summary>
/// Import the spreadsheet into the dom
/// </summary>
/// <returns>a list of warnings</returns>
public List <string> Import(InternalSpreadsheet sheet, IWebSocketProgress progress = null)
{
_sheet = sheet;
_progress = progress ?? new NullWebSocketProgress();
Progress("Importing spreadsheet...");
_warnings = new List <string>();
_inputRows = _sheet.ContentRows.ToList();
_pages = _destinationDom.GetPageElements().ToList();
_bookIsLandscape = _pages[0]?.Attributes["class"]?.Value?.Contains("Landscape") ?? false;
_currentRowIndex = 0;
_currentPageIndex = -1;
_groupsOnPage = new List <XmlElement>();
_imageContainersOnPage = new List <XmlElement>();
_destLayout = Layout.FromDom(_destinationDom, Layout.A5Portrait);
while (_currentRowIndex < _inputRows.Count)
{
var currentRow = _inputRows[_currentRowIndex];
string rowTypeLabel = currentRow.MetadataKey;
if (rowTypeLabel == InternalSpreadsheet.PageContentRowLabel)
{
bool rowHasImage = !string.IsNullOrWhiteSpace(currentRow.GetCell(InternalSpreadsheet.ImageSourceColumnLabel).Text);
bool rowHasText = RowHasText(currentRow);
if (rowHasImage && rowHasText)
{
AdvanceToNextGroupAndImageContainer();
}
else if (rowHasImage)
{
AdvanceToNextImageContainer();
}
else if (rowHasText)
{
AdvanceToNextGroup();
}
if (rowHasImage)
{
PutRowInImage(currentRow);
}
if (rowHasText)
{
PutRowInGroup(currentRow, _currentGroup);
}
}
else if (rowTypeLabel.StartsWith("[") && rowTypeLabel.EndsWith("]")) //This row is xmatter
{
string dataBookLabel = rowTypeLabel.Substring(1, rowTypeLabel.Length - 2); //remove brackets
UpdateDataDivFromRow(currentRow, dataBookLabel);
}
_currentRowIndex++;
}
// This section is necessary to make sure changes to the dom are recorded.
// If we run SS Importer from the CLI (without CollectionSettings), BringBookUpToDate()
// will happen when we eventually open the book, but the user gets an updated thumbail and preview
// if we do it here for the main production case where we DO have both the CollectionSettings
// and the Book itself. Testing is the other situation (mostly) that doesn't use CollectionSettings.
if (_collectionSettings != null && _book != null)
{
_book.BringBookUpToDate(new NullProgress());
}
Progress("Done");
return(_warnings);
}
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 SpreadsheetRow(InternalSpreadsheet spreadsheet)
{
spreadsheet.AddRow(this);
}
public SpreadsheetImporter(HtmlDom dest, InternalSpreadsheet sheet)
{
_dest = dest;
_sheet = sheet;
}
public HeaderRow(InternalSpreadsheet sheet) : base(sheet)
{
}
public ContentRow(InternalSpreadsheet sheet) : base(sheet)
{
}