facebooktwittermenuarrow-up

GemBox Support Forum

Appending HTML to a Spreadsheet

GemBox.Spreadsheet
#1

Once the spreadsheet is created, how do I append a new tab with other html markup code.
Here is what I have thus far. It creates only one tab and over wirtes it with the new html markup code. What am I do wrong?

internal static void AddHTMLToExcelFile(string HtmlData, string tabText = "", string filePath = "")
{
    SpreadsheetInfo.SetLicense(SpreadsheetLicense);

    ExcelFile workbook;
    ExcelWorksheet worksheet;

    HtmlLoadOptions htmlOptions = new HtmlLoadOptions();
    MemoryStream htmlStream = new MemoryStream(htmlOptions.Encoding.GetBytes(HtmlData));

    workbook = ExcelFile.Load(htmlStream, htmlOptions);

    worksheet = workbook.Worksheets.Add(tabText);

    int columnCount = worksheet.CalculateMaxUsedColumns();
    for (int i = 0; i < columnCount; i++)
        worksheet.Columns[i].AutoFit(1, worksheet.Rows[1], worksheet.Rows[worksheet.Rows.Count - 1]);

    workbook.Save(filePath);
}
#2

If I understood you correctly, you want to create an Excel file that has multiple sheets, each sheet being generated by its own HTML content.

In that case, what you’ll need to do is load each HTML content to its own ExcelFile, each will have one ExcelWorksheet object. Then combine those ExcelWorksheet objects into one ExcelFile.

For example, something like this:

static void Main()
{
    var workbook = new ExcelFile();

    AddHtmlSheetToExcelFile(workbook, "<p>Sample1</p>", "Sheet1");
    AddHtmlSheetToExcelFile(workbook, "<p>Sample2</p><p>Sample2</p>", "Sheet2");

    workbook.Save("output.xlsx");
}

static void AddHtmlSheetToExcelFile(ExcelFile workbook, string htmlData, string sheetName)
{
    var htmlOptions = new HtmlLoadOptions();
    var htmlStream = new MemoryStream(htmlOptions.Encoding.GetBytes(htmlData));

    var htmlSheet = ExcelFile.Load(htmlStream, htmlOptions).Worksheets[0];
    htmlSheet = workbook.Worksheets.AddCopy(sheetName, htmlSheet);

    int columnCount = htmlSheet.CalculateMaxUsedColumns();
    for (int i = 0; i < columnCount; i++)
        htmlSheet.Columns[i].AutoFit();
}

I hope this helps.