0

When I tried saving the excel sheet in HTML and displaying I've got few problems:

  1. The numbers in each cell are distorted
  2. Hidden rows and columns are displayed (I want them to keep hidden)
  3. Top rows of my Excel sheet are freezed, I want them to keep freezed.
  4. The Sheet is updated daily and I want the changes to be reflected in Web as well.

Could you suggest a way other than saving excel sheet in HTML? Any other way I could start this project?

P.S: I hold the knowledge of HTML, CSS, JavaScript.

Do I need to learn any additional skill set to get pull this project?

Imran Ali
  • 2,223
  • 2
  • 28
  • 41
Srikanth Anamarlapudi
  • 1
  • 3
  • Can you run server-side code? If so, what is the environment? (IIS? Apache?) How huge is huge? Thousands of cells? Millions of cells? The silly answer I could give to your questions "Yes", but I can't say more without more information about your development environment :) – Becca Dee Oct 03 '17 at 04:11
  • Thank you for the reply, I'm working on an excel sheet of about 40-50 MB to be displayed in Web. The workbook can go upto millions of cells as we add data everyday. I am working just on HTML, CSS JavaScript and probably will include bootstrap elements. – Srikanth Anamarlapudi Oct 03 '17 at 04:20
  • I did put up an answer that I think might work, but given that you might have millions of cells, you might want to reconsider the architecture of your solution. I don't know that showing an HTML table with millions of rows will meet your user's needs or is even technically advisable. – Becca Dee Oct 03 '17 at 04:38
  • What is the source of the Excel file? Are people just adding in data over time and it has grown to millions of cells, or does the data come from another system? – Becca Dee Oct 03 '17 at 04:39
  • If people are entering data over time and it has grown, you might want to consider developing a whole new web application that provides the same features as the Excel file. – Becca Dee Oct 03 '17 at 04:39
  • Source of the Excel file: Currently the Excel file is saved in a share-drive(only accessible internally by employees). People add the dump in the excel sheet and based on the formulas defined the main sheet gives the output. Dump is added in one work sheet and output is show in another worksheet. My intentions are to only project the outputs not the dump(which actually has million cells), however the output page has few thousands of cells. – Srikanth Anamarlapudi Oct 03 '17 at 04:53
  • Ah, okay. A few thousand is big for a web page but not so intractably huge. – Becca Dee Oct 03 '17 at 04:54
  • Do you have any information about your web server? – Becca Dee Oct 03 '17 at 04:55
  • And thank you for your replies. – Becca Dee Oct 03 '17 at 04:58
  • Yes, I've got someone who can help me with Web-server. I currently don't know the exact details of how it works, but yeah I can info if needed – Srikanth Anamarlapudi Oct 03 '17 at 04:58

1 Answers1

1

If you are running IIS or can otherwise run .NET code on your server, you can use the Open XML SDK. There are plenty of examples of using the SDK. Apparently, it even works with Mono if you're using Apache.

If not, if you've got some machine that can get to the Excel file and copy files to your web server and from which you can run a scheduled task, you can schedule a task on that machine that using the Open XML SDK to translate the Excel file to HTML, then upload that HTML to the server.

It doesn't have to be a complete page; it can just be the parts of the HTML you need. You can then have some JavaScript on the page that fires off an AJAX request to get the HTML file. This is true regardless of whether your serving content dynamically generated by code running under IIS or a static file generated by code elsewhere and pushed to the server.

You might have something like:

<!-- page where the spreadsheet should go -->
<div id="put-excel-worksheet-here"></div>

Then:

$.ajax({
    url: "/path/to/converted-data-partial-html.html"
  , success: function(excelHtml) {
        $("#put-excel-worksheet-here").append($(excelHtml));
    }
});

And your ASP.NET page response or scheduled task would convert your Excel file to something like <table><thead><tr><th id='column-one-th'>....

Either way, I'd recommend you work with a copy of the file rather than the original, since I've found Office can get a little finicky with files being open in two places at once.

As for freezing the top rows, here's a fiddle with an example.

Good luck!

edit: As an alternative to putting millions of cells worth of HTML onto a single page, it might make sense load the Excel data into a relational database like MySQL or Microsoft SQL Server and do some custom web development to pull the data out of that with pagination and filters and other nice reporting features.

If the data in the Excel file is coming OUT of another system, you might be able to set up a system-to-system integration. If not through an integration, though, the Open XML SDK is how I'd do it. You don't have millions of rows to put up per your comments above, so this suggestion doesn't make sense.

edit: Oh, and I recommend your scheduled task run during a time when people aren't likely going to be using the system. If, for example, your users are all in a few adjacent time zones, have the task run at 3:00 AM in the Eastern-most time zone.

Becca Dee
  • 1,530
  • 1
  • 24
  • 51