adding values to code from a spreadsheet

Question

I have some code below:

<div class="product-page-wrapper">
    <div class="product-page-img">
        <img alt="" width="137" height="125" src="/Portals/6/aggProd.png" />
    </div>
    <div class="product-page-title">**D-077**</div>
    <div class="product-page-desc">
        <br />
        <br />
        <br />
    </div>
    <div class="product-page-sep">
        <img alt="" width="732" height="1" src="/Portals/6/spec-sep.png" />
    </div>
</div>
<div class="product-page-spec-title">
    Specifications
</div>
<div class="product-page-spec-desc">
    <br />
    Type - 
    <span style="color: #1296d3;">
        **D-077**
    </span>
    <br />
    <br />
    Weight lbs. (kg) - <br />
    <br />
    Dimensions (mm) -<br />
    <span style="color: #1296d3;">
        L - **207**   W - **175**    H - **175**
    </span>
</div>
<div class="product-page-spec-button">
    <a target="_blank">
        <img alt="" width="215" height="64" src="/Portals/6/spec-button.png" />
    </a>
</div>

At the moment i am only changing the Product Page Title, Type, Dimemsion values. I have a massive spread sheet with these values and wanted to know if i could input these values into my code automatically.

This would save me alot of time.

what would be the best way?

I am using DNN 5.6.2

Even if i could reproduce the code i could then paste it into my CMS editor.

score 2 · Accepted Answer · edited May 23 '17 at 12:04

There are a lot of ways to do this, depending on the tools at your disposal.

One way is to create an ASP.NET page with a repeater in it, and use the OLE DB driver for Excel to use the spreadsheet as a datasource. You can see this question for more details as to how to do it. You could even build such a control into a DotNetNuke module if you want to, then you could have the content update to match the spreadsheet dynamically if the spreadsheet ever changes.

Another approach you could use if you really just want to copy-and-paste the values from the spreadsheet into the CMS, is to use Excel to generate the code. For example, if you're using columns A-E in Excel, you could put a formula like the following in column F:

=CONCATENATE("<div class=""product-page-wrapper""><div class=""product-page-img""><img alt="""" width=""137"" height=""125"" src=""/Portals/6/aggProd.png"" /></div><div class=""product-page-title"">",A2,"</div>", "...etc...")

(This is just an example. You'd have to flesh out the rest of the code, and you might have to use multiple columns to get past string length limitations in Excel)

Then you could paste that formula into every row, and then copy the resulting values from Excel into the CMS text field. This is really a bootleg way of doing things, and writing a DNN module is definitely more ideal, but using Excel functions to generate the code is an option if this is one of those "I need it in 5 minutes and then I'm never going to use it again" situations.

Thanks for this, currently just doing the Excel spreadsheet method but im thinking for futures sake i may implement the DNN module. — PD24, Aug 02 '11 at 09:05
I want to use a repeater control and then the query has to be based on the unique identifier which is the part number. Is it possible to read the part number from the page, how would i filter the tech specs on the page without manually amending the query on page load? — PD24, Aug 02 '11 at 09:06
@PD24 - If I'm understanding you correctly, you would use a parameter in your query. Something like `OleDbCommand myCommand = new OleDbCommand("SELECT * FROM [Sheet1] WHERE [PartNumber] = ?", XlsConnection); OleDbCommand.Parameters.AddWithValue("PartNumber", Request["PartNumber"]); OleDbReader XlsReader = OleDbCommand.ExecuteReader();` (This may not be completely correct. I'm typing code entirely from memory here). — Joshua Carmody, Aug 03 '11 at 01:53

adding values to code from a spreadsheet

1 Answers1