Merging Excel

• We are generating a workbook(Open.xlsx) with multiple excel sheets(Sheet 1, Sheet 2, Sheet 3, Sheet 4 etc.) using open XML(C# Code) from the dataset .
• A dataset contains multiple data tables, for each data table we create a new Sheet to the workbook.
• While creating a new sheet, depends upon the value present in a column we’ll change the properties of the excel cell(cell merging, cell background color, font color, font style, cell format, borders etc.).
• At the same time, we will do the conditional formatting for the cells like cell rules(greater than or less than cell values etc.) and Icon Sets(Ratings, Shapes, Indicators etc.).

I want to add some new features to the created work book.

• We have another one work book(New.xlsx) with a sheet(NewSheet 1) with headers, contents, cell properties(mentioned above) and conditional formatting.
• We want to merge a sheet(NewSheet 1) from a workbook(New.xlsx) to the already created open xml workbook(Open.xlsx).
• Final Output contains a single workbook with sheets(Sheet 1, Sheet 2, Sheet 3, Sheet 4, NewSheet 1), without affecting their individual styles, formats, conditional formatting etc.

I had attached the two sample Excel sheet, If the above scenario is possible please update us with the same example code for requested above
Thank you in advance for your help
NOTE- Cant able to upload Excel file

@Ramzhunk,

Thanks for providing us some details.

I think you may copy the worksheet (“NewSheet 1”) from New.xlsx file to your workbook, i.e., “Open.xlsx” via Aspose.Cells APIs and then re-save the Open.xlsx file, see the document with example code for your reference:

Also, I could not find your attachments, please zip your files and attach it here (if you still could not figure out your issue/task).

Thanks.

I am trying to convert the excel file created via Aspose to Html file. It does convert but I do see a directory containing stylesheets, images along with the html file. Is it possible to create an html file which is self contained (meaning have styles and images embedded inside the html file).

I am looking for 1 single html file output instead of a html and a supporting directory. This is urgent if someone can respond would be great.

By the way may I know which time zone is your support team working to get quicker response.

-Sunitha

@sunithaprabhu,

Well, Aspose.Cells follows Ms Excel standards and specifications in rendering Excel to HTML file format, so by default it will create folder containing the resource files against worksheets in the workbook. But you may still choose the following option/approach to accomplish the task and cope with it:

Try to export every worksheet (in the workbook) to single HTML and then group these individual HTMLs to one (final) HTML by yourselves via e.g some tag control or using your own code. In a loop, you may set active for each sheet and then render separate HTML file (based on every worksheet) via Aspose.Cells APIs. Please note, when exporting every worksheet to separate HTML, you would need to export image as base64 format (you will use HtmlSaveOptions class here) otherwise it will create folders. See the sample code for your reference:
e.g
Sample code:

 //Load your sample workbook
 Workbook wb = new Workbook(“e:\test2\Book1.xlsx”);

 //Specify HtmlSaveOptions
 //Export image as bytes (base 64) as inline images
 //Export active worksheet only
 HtmlSaveOptions opts = new HtmlSaveOptions();
 opts.ExportImagesAsBase64 = true;
 opts.ExportActiveWorksheetOnly = true;//You may activate one sheet at a time.

 //Save the workbook in HTML format with above HtmlSaveOptions
 wb.Save(“e:\test2\out1.html”, opts);

Hope, this helps a bit.

It worked like a charm. Thank you so much.

Couple of more questions during html creation I would like to do some customizations like

  1. The note in each cell (as you know we can create note in excel which shows as red mark), I want it to be converted as text (within the tag) in html.
  2. There are some range defined in the worksheets with some name, I want those range to be surrounded with some tag I specify like (<#if> </#if>) in the html. This is to have those sections identified in html for further processing.
  3. Is it possible to tweak the way the html being generated?

    thanks in advance
    Sunitha

@sunithaprabhu,

Good to know that the suggested code works for your needs.

  1. Do you mean comments? If true, you may try to use HtmlSaveOptions.IsExportComments Boolean attribute for the purpose. See the sample code for your reference:

     Workbook workbook = new Workbook("e:\\test2\\demoExcel.xlsx");
                 HtmlSaveOptions options = new HtmlSaveOptions();
                 options.Encoding = System.Text.Encoding.UTF8;
                 options.ExportImagesAsBase64 = true;
                 options.ExportHiddenWorksheet = true;
                 
                 options.IsExportComments = true;
        
     ..........
    
  2. This is not supported. If you want to accomplish your custom task, you have to write your own code and logic to do that.

Hi

1.I tried that exportComments option exports the comments at the end of the html. What I want is to export at the same place where it is commented in excel. Say in excel if I create a comment in a particular cell, in html I expect the comment to be placed in the same place. Is it possible?

  1. Basically the range created in excel sheet need to be propagated in some form to html. If I open the html source, I should be able to identify that this section of code is grouped within that range. Currently that range information is not propagated to html.

For the second question if there is way for me to identify that range in html, then I can parse the html and put my custom tags around that range (section).

Another option for second question, I am basically inserting each range from a sheet into a new sheet. Is it possible to add some kind of marker (to indicate the start of range) in target excel and then insert that range and then add a marker again (to indicate the end of range) ? This way we can identify that range as a group. But what kind of marker to add in that case which would also propagate to html.

@sunithaprabhu,

  1. Please note, in MS Excel comments are separate entities and are not linked or attached with cells, so we cannot do that.

  2. As I told you this is not supported in Aspose.Cells and even in MS Excel. One thing Aspose.Cells does support is Aspose.Cells can render the HTML based on the printable area set in its PageSetup for the worksheet. So, if it may help you may create your desired range (e,g A1:C10) and set your printable area based on the devised range (via code or MS Excel). Now use HtmlSaveOptions.ExportPrintAreaOnly to true and you will get range/area only in the output HTML.

For your information, Aspose.Cells follows MS Excel standards and specifications in parsing or rendering HTML files, so if something is not supported in MS Excel, it is also not supported in Aspose.Cells.

Screen Shot 2020-04-23 at 11.16.35 AM.png (100.8 KB)

For point 1, I was talking about Excel notes which can be attached to specific cell. Please find the attached screenshot. Each cell having such notes are shown as red mark in the top right corner of that cell. For your clarity the attachment shows one of the notes in yellow.

If you search online ‘difference between excel comment and note’ it explains. Sorry for the confusion, what I meant is notes (which can be added for each cell). This is supported in MS Excel

I will wait for your response on the excel notes. If Aspose supports everything in MS Excel, I believe it should support Excel notes as well. But I do not see it in the api docs of Cell.

@sunithaprabhu,
Please note that Ms Excel Comment is implemented as Threaded Comments and Note is implemented using Comments. Please have a look at the following articles for more details and working samples.

  1. Threaded Comments
  2. Managing Comments

Thanks. In the attached image, you can notice ('New Note) right below the ‘New Comment’. That’s what we are using. Is this same as comment?

Screen Shot 2020-04-24 at 8.49.14 AM.png (242.4 KB)

@sunithaprabhu,
Yes, you are right. New Note corresponds to the article Managing Comments above.

Thanks. But there is no way to have these notes propagated to html during html conversion on corresponding place where it was defined in excel? Say note at cell E5 propagates to appropriate td tag?

I know there is an option to exportComments to Html but that exports all comments at the end of html not at the appropriate place.

@sunithaprabhu,
This is similar to the behavior of MS Excel. You may save the Excel file to HTML and see it also renders the comments at the end. There is no option in Aspose.Cells and MS Excel to render the comments at the corresponding place where they were defined.

Is there a way to access the comment in a cell? I do not see any api in Cell class to access the comment (note) in a cell. Screenshot of how the comment looks like is attached in my previous msg (Apr 23).

@sunithaprabhu,

See the following sample code (examples) for your reference:

//Check if a cell has comment or not.
 Workbook workbook = new Workbook("Book1.xlsx");
            Worksheet worksheet = workbook.Worksheets[0];
            //Check if a cell has comment attached to it or not
            Comment comment = worksheet.Comments["A1"];
            if (comment == null)
            {
                MessageBox.Show("The cell does not have comment");
            }
            else
            {
                MessageBox.Show("The cell has comment");
            
            }
//Scan worksheet comments for the cells in the worksheet and retrieve them
Workbook workbook = new Workbook("e:\\test2\\AsposeExcelTest.xlsx");
            Worksheet worksheet = workbook.Worksheets[0];
            List<Aspose.Cells.Cell> rangeList = new List<Aspose.Cells.Cell>();
            foreach (Comment comment in worksheet.Comments)
            {
                string note = comment.Note;
                Debug.WriteLine(note);
                //Get the comment cell.
                Aspose.Cells.Cell cell = worksheet.Cells[comment.Row, comment.Column];
                rangeList.Add(cell);


            }
            IEnumerator cells = rangeList.GetEnumerator();
            while (cells.MoveNext())
            {
                Cell cell = cells.Current as Cell;
                Debug.WriteLine("Cell: " + cell.Name + " Value:" + cell.StringValue);

            } 

Hope, this helps a bit.