What is the best practice code to display excel data of multiple worksheets

GroupDocs.Viewer Product Family
1

What is the best way to display excel data of multiple worksheet

2

@philipthomas32,

Thanks for using GroupDoc.Viewer and posting on our forum. GroupDocs.Viewer provides a couple of ways to render the Excel worksheets.

  • Rendering one page per sheet - In this way, each worksheet of the Excel document is considered as a page and the API renders each worksheet as one Html/image page. This method could be more suitable in the situation when the sheets are not big enough. You can use HtmlOptions.CellsOptions.OnePagePerSheet = true for rendering one page per sheet.
  • Rendering multiple pages per sheet - There can be the scenario when the sheets may contain the huge number of rows. In that case, you can perform the partial rendering of the sheets and set the number of rows that will appear on each rendered Html/image page. The benefit of this feature is that you can fix the number of rows and each Html/image page will contain same number of rows. For more details on this, please visit here.

Since version 17.8.0, we have also introduced a feature of ignoring empty rows in Excel worksheets that allows you to exclude the empty rows from the rendering results. For details, please visit this documentation article.

3

This is how we display paging for excel.Do you have any sample code that show multi sheet excel data with paging?

DocumentInfoOptions documentInfoOptions = new DocumentInfoOptions();
if (isExcelFile)
{
documentInfoOptions.CellsOptions.OnePagePerSheet = false;
documentInfoOptions.CellsOptions.CountRowsPerPage = 1;
}
DocumentInfoContainer documentInfoContainer = _htmlHandler.GetDocumentInfo(previewDocumentInfo.RawDocumentPath, documentInfoOptions);
pagesOrRowsCount = documentInfoContainer.Pages.Count;
var htmlOptions = new HtmlOptions
{
IsResourcesEmbedded = true,
CellsOptions =
{
ShowGridLines = true,
CountRowsPerPage = ExcelPageSizeMedium,
OnePagePerSheet = false
}
};
htmlOptions.PageNumber = previewDocRequest.StartPage;
htmlOptions.CountPagesToRender = previewDocRequest.TotalPages;
if (previewDocRequest.RotationAngle != 0)
htmlOptions.Transformations = Transformation.Rotate;
SetHtmlPages(previewDocumentInfo.RawDocumentPath, htmlOptions);

4

@philipthomas32,

Thanks for your query. Would you please elaborate your requirement for paging Excel sheet in details?

5

now it shows data of first default sheet in excel

If i have multiple sheets, i want to display date of first sheet , then data of second sheet etc.I want to include paging also, i understand paging of second sheet come after first sheet

6

6sheetj2gj - Copy.zip (8.2 KB)

what is the code to retrieve data from sheet2 and sheet 3 in the attached file.For me its geting only data from sheet1

7

@philipthomas32,

Thanks for sharing the details.

I am afraid that we don’t any sample code to demonstrate the usage of paging when rendering Excel sheets. However, your code seems to work fine to fulfill your requirement. In your provided code, you have set the DocumentInfoOptions and the documentInfoContainer.Pages.Count property will give you the total number of the pages that will be rendered and you can create the paging list using this count. We have prepared this sample application for you that demonstrates a simple way to implement paging using a dropdown list. Hope it helps.

We have checked your provided document at our end and all the sheets are rendering into Html pages. The API will render all the sheets of the Excel document as Html pages if you have not specified the PageNumber in HtmlOptions. You can get the sheet 2 and 3 separately by specifying the PageNumber in HtmlOptions as shown in the below code snippet.

HtmlOptions options = new HtmlOptions(); 

options.PageNumber = 2; // You can specify the page number here
options.CountPagesToRender = 1;

// get pages
List<PageHtml> pages = htmlHandler.GetPages("sample.xlsx", options);

Please note that if you have set OnePagePerSheet = false then the number of the rendered Html pages may differ from the number of sheets in the source document. In that case, you can set the PageNumber accordingly.

8

thanks let me play with the sample

9

this file is showing row page count 6 instead of 3.My issue is mainlysamgj.zip (8.2 KB)
due to wrong page count

Why 3 rows from 3 worksheets are shown as 6 pages?
Also what is the best way to merge all rows from multiple sheet to one page?

10

@philipthomas32,

Thanks for sharing the details.

You are getting the page count 6 because you have set the following options.

documentInfoOptions.CellsOptions.OnePagePerSheet = false;
documentInfoOptions.CellsOptions.CountRowsPerPage = 1;

The above settings tell the API to render one row per HTML page. The source document contains 3 sheets and each sheet contains 2 rows. Hence, the total number of rows in the whole document is 6 and therefore, you get the page count as 6 after setting CountRowsPerPage = 1;. Please note that you will have to set the same values of CellsOptions.OnePagePerSheet and CellsOptions.CountRowsPerPage for the HtmlOptions as well before GetPages function.

You can merge the HTML content of the all the pages/sheets into one HTML page. Following is one of the possible ways to achieve this.

//. . . 
//. . .
List<PageHtml> pages= htmlHandler.GetPages("sample.xlsx", options);
string allContent = "";

foreach (PageHtml page in pages)
{                     
   allContent += page.HtmlContent;
}

String output = Path.Combine(directory, "output.html"); // directory is the output folder where output.html will be saved
System.IO.File.WriteAllText(output, allContent);

Hope it helps.

11

Thanks
What is the property from viewer to check IF EXCEL has more than one worksheet?

12

@philipthomas32,

Thanks for your response.

If you haven’t set DocumentInfoOptions.CellsOptions.OnePagePerSheet to false, the DocumentInfoContainer.Pages.Count property will return the number of sheets in the source Excel document.

13

Remember then my excel that i send yesterday with 3 worksheet shows page count 6 know

14

@philipthomas32,

Thanks for your response.

As I mentioned in the previous post, you will get the page count equal to the number of sheets if you haven’t set DocumentInfoOptions.CellsOptions.OnePagePerSheet to false which means that each sheet of the Excel document will be represented by a separate page.

You were getting the count 6 becasue of using the following options.

documentInfoOptions.CellsOptions.OnePagePerSheet = false;
documentInfoOptions.CellsOptions.CountRowsPerPage = 1;

Using the above settings, the API breaks down each sheet to render 1 row on each page. As a result, we get 6 pages as the total number of rows in the Excel document is 6. In case you want to get the page count same as the number of sheets, you can use the following code sample.

DocumentInfoContainer docInfo = htmlHandler.GetDocumentInfo("sample.xlsx");
Console.WriteLine(String.Format("Pages: {0}", docInfo.Pages.Count));