0

I would like to add a button to export excel from ASP gridview.

  1. Group INV into "data" sheet
  2. Change header from INV to REF & LINE to LN into "details" sheet
  3. Add One column header "FLAG" into "details" sheet
  4. Add row 2

Who can help me to modify below code by VB?

        Protected Sub btnconvert_Click(sender As Object, e As EventArgs) Handles btnconvert.Click
            Dim strDateTime As String = Format(Now(), "yyyyMMdd-HHmmss")
            Response.ClearContent()
            Response.AddHeader("content-disposition", "attachment; filename=HRS-SO-" & strDateTime & ".xls")
            Response.ContentType = "application/excel"
            Dim sw As New System.IO.StringWriter()
            Dim htw As New HtmlTextWriter(sw)
            Response.Write(sw.ToString())
            Response.[End]()
       End sub

Gridview1
INV          LINE QTY
===============================================
TESTINV001   001  200
TESTINV001   002  200
TESTINV002   001  1000

"data"
REF
INV
===============================================
TESTINV001
TESTINV002

"details"
REF          LN   QTY  FLAG
INV          LINE QTY  FLAG
===============================================
TESTIN001    001  200  
TESTIN001    002  200  
TESTIN002    001  1000 

<div>
    <table class="auto-style4" cellspacing="0" rules="all" border="1" id="GridView1" style="border-collapse:collapse;">
        <tbody><tr>
            <th scope="col">INV</th><th scope="col">LINE</th><th scope="col">QTY</th>
        </tr><tr>
            <td>TESTINV001</td><td>001</td><td>200</td>
        </tr><tr>
            <td>TESTINV001</td><td>002</td><td>200</td>
        </tr><tr>
            <td>tESTINV002</td><td>001</td><td>1000</td>
        </tr>
    </tbody></table>
</div>

var tablesToExcel = (function () {
    var uri = 'data:application/vnd.ms-excel;base64,'
    , tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
      + '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>'
      + '<Styles>'
      + '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>'
      + '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>'
      + '</Styles>'
      + '{worksheets}</Workbook>'
    , tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>'
    , tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>'
    , base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
    , format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
    return function (tables, wsnames, wbname, appname) {
        var ctx = "";
        var workbookXML = "";
        var worksheetsXML = "";
        var rowsXML = "";

        for (var i = 0; i < tables.length; i++) {
            if (!tables[i].nodeType) tables[i] = document.getElementById(tables[i]);
            for (var j = 0; j < tables[i].rows.length; j++) {
                rowsXML += '<Row>'
                for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
                    var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
                    var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
                    var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
                    dataValue = (dataValue) ? dataValue : tables[i].rows[j].cells[k].innerHTML;
                    var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
                    dataFormula = (dataFormula) ? dataFormula : (appname == 'Calc' && dataType == 'DateTime') ? dataValue : null;
                    ctx = {
                        attributeStyleID: (dataStyle == 'Currency' || dataStyle == 'Date') ? ' ss:StyleID="' + dataStyle + '"' : ''
                           , nameType: (dataType == 'Number' || dataType == 'DateTime' || dataType == 'Boolean' || dataType == 'Error') ? dataType : 'String'
                           , data: (dataFormula) ? '' : dataValue
                           , attributeFormula: (dataFormula) ? ' ss:Formula="' + dataFormula + '"' : ''
                    };
                    rowsXML += format(tmplCellXML, ctx);
                }
                rowsXML += '</Row>'
            }
            ctx = { rows: rowsXML, nameWS: wsnames[i] || 'Sheet' + i };
            worksheetsXML += format(tmplWorksheetXML, ctx);
            rowsXML = "";
        }

        ctx = { created: (new Date()).getTime(), worksheets: worksheetsXML };
        workbookXML = format(tmplWorkbookXML, ctx);

        var link = document.createElement("A");
        link.href = uri + base64(workbookXML);
        link.download = wbname || 'Workbook.xls';
        link.target = '_blank';
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    }
})();

4
  • You might want to check out stackoverflow.com/questions/24636956/… in order to get muliple worksheets Commented Oct 6, 2015 at 17:04
  • Thanks for your comment! I can export to two worksheet. But how to can I change header? Commented Oct 9, 2015 at 4:29
  • I don't have enough information about how you are building the HTML that you are sending to Excel, just that you are using Excel's feature of converting HTML <table> content into a worksheet (hence the link I suggested). I'd have to see what your code that creates the HTML looks like before I could suggest anymore. Commented Oct 9, 2015 at 21:33
  • Please have a look. I have added html code. Commented Oct 14, 2015 at 13:03

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.