How to export excel file from multi header table in JSP

Copy codeThe code is as follows:

/*
* the default conversion implementation function, if you need other functions, you need to expand
* parameters:
* tableid: table object ID attribute value in HTML
* Please refer to the following tabletoexcel object definition for detailed usage
 */
function saveAsExcel(tableID){
 var tb = new TableToExcel(tableID);
  tb.setFontStyle(“Courier New”);
  tb.setFontSize(10);
  tb.setTableBorder(2);
  tb.setColumnWidth(7);
  tb.isLineWrap(false);
  tb.isAutoFit(true);
  tb.getExcelFile();
}

/*
* function: convert table object in HTML to excel general object
* Author: jeva
* time: August 9, 2006
* parameter: ID attribute value of table object in tableid HTML
* Description:
* it can adapt to the automatic conversion of table objects in complex HTML, and can automatically expand information according to columns and columns
* to merge cells in Excel, the client needs to have excel installed
* Please refer to Microsoft Excel Visual Basic reference of Excel for detailed description of properties and methods
* demonstration:
 *       var tb = new TableToExcel(‘demoTable’);
 *    tb.setFontStyle(“Courier New”);
 *    tb.setFontSize (10) ; / / the recommended value is 10
 *    tb.setFontColor (6) ; / / you do not need to set it in general
 *    tb.setBackGround (4) ; / / you do not need to set it in general
 *    tb.setTableBorder (2) ; / / the recommended value is 2
 *    tb.setColumnWidth (10) ; / / the recommended value is 10
 *    tb.isLineWrap(false);
 *    tb.isAutoFit(true);
 *   
 *    tb.getExcelFile();
* if cell adaptation is set, setting cell width is invalid
* version: 1.0
 */
function TableToExcel(tableID) {
    this.tableBorder =- 1; / / border type, – 1 without border can be 1 / 2 / 3 / 4
    this.backGround =0; / / background color: white color can be selected from the color palette 1 / 2 / 3 / 4
    this.fontColor =1; / / font color: Black
    this.fontSize =10; / / font size
    this.fontStyle =Font type; / / font type
    this.rowHeight =- 1; / / row height
    this.columnWidth =- 1; / / column width
    this.lineWrap =True; / / whether to wrap lines automatically
    this.textAlign =- 4108; / / the content alignment is centered by default
    this.autoFit =False; / / adaptive width
    this.tableID = tableID;
}

TableToExcel.prototype.setTableBorder = function (excelBorder) {
    this.tableBorder = excelBorder ;
};

TableToExcel.prototype.setBackGround = function (excelColor) {
    this.backGround = excelColor;
};

TableToExcel.prototype.setFontColor = function (excelColor) {
    this.fontColor = excelColor;
};

TableToExcel.prototype.setFontSize = function (excelFontSize) {
    this.fontSize = excelFontSize;
};

TableToExcel.prototype.setFontStyle = function (excelFont) {
    this.fontStyle = excelFont;
};

TableToExcel.prototype.setRowHeight = function (excelRowHeight) {
    this.rowHeight = excelRowHeight;
};

TableToExcel.prototype.setColumnWidth = function (excelColumnWidth) {
    this.columnWidth = excelColumnWidth;
};

TableToExcel.prototype.isLineWrap = function (lineWrap) {
    if (lineWrap == false || lineWrap == true) {
        this.lineWrap = lineWrap;
    }
};

TableToExcel.prototype.setTextAlign = function (textAlign) {
    this.textAlign = textAlign;
};

TableToExcel.prototype.isAutoFit = function(autoFit){
 if(autoFit == true || autoFit == false)
  this.autoFit = autoFit ;
}

//File conversion main function
TableToExcel.prototype.getExcelFile = function () {
    var jXls, myWorkbook, myWorksheet, myHTMLTableCell, myExcelCell, myExcelCell2;
    var myCellColSpan, myCellRowSpan;

    try {
        jXls = new ActiveXObject(‘Excel.Application’);
    }
    catch (e) {
Alert (“unable to start Excel) \ \ n \ \ n” + e.message+
“If you are sure excel is installed on your computer+
“Then please adjust the security level of IE. \Specific operations are as follows:+
“Tools → Internet Options → security → custom level → ActiveX controls and plug-ins”+
“→ enable: initialize and script ActiveX controls that are not marked as safe”);
        return false;
    }

    jXls.Visible = true;
    myWorkbook = jXls.Workbooks.Add();
    jXls.DisplayAlerts = false;
    myWorkbook.Worksheets(3).Delete();
    myWorkbook.Worksheets(2).Delete();
    jXls.DisplayAlerts = true;
    myWorksheet = myWorkbook.ActiveSheet;

    var  readRow = 0,  readCol = 0;
    var totalRow = 0, totalCol = 0;
    var   tabNum = 0;

//Set row height and column width
    if(this.columnWidth != -1)
     myWorksheet.Columns.ColumnWidth = this.columnWidth;
    else
     myWorksheet.Columns.ColumnWidth = 7;
    if(this.rowHeight != -1)
     myWorksheet.Rows.RowHeight = this.rowHeight ;

//Search the table object to be converted and get the corresponding row and column number
    var obj = document.all.tags(“table”);
    for (x = 0; x < obj.length; x++) {
        if (obj[x].id == this.tableID) {
            tabNum = x;
            totalRow = obj[x].rows.length;
            for (i = 0; i < obj[x].rows[0].cells.length; i++) {
                myHTMLTableCell = obj[x].rows(0).cells(i);
                myCellColSpan = myHTMLTableCell.colSpan;
                totalCol = totalCol + myCellColSpan;
            }
        }
    }

//Start component simulation table
    var excelTable = new Array();
    for (i = 0; i <= totalRow; i++) {
        excelTable[i] = new Array();
        for (t = 0; t <= totalCol; t++) {
            excelTable[i][t] = false;
        }
    }

//Start converting tables
    for (z = 0; z < obj[tabNum].rows.length; z++) {
        readRow = z + 1;
        readCol = 1;
        for (c = 0; c < obj[tabNum].rows(z).cells.length; c++) {
            myHTMLTableCell = obj[tabNum].rows(z).cells(c);
            myCellColSpan = myHTMLTableCell.colSpan;
            myCellRowSpan = myHTMLTableCell.rowSpan;
            for (y = 1; y <= totalCol; y++) {
                if (excelTable[readRow][y] == false) {
                    readCol = y;
                    break;
                }
            }
            if (myCellColSpan * myCellRowSpan > 1) {
                myExcelCell = myWorksheet.Cells(readRow, readCol);
                myExcelCell2 = myWorksheet.Cells(readRow + myCellRowSpan – 1, readCol + myCellColSpan – 1);
                myWorksheet.Range(myExcelCell, myExcelCell2).Merge();
                myExcelCell.HorizontalAlignment = this.textAlign;
                myExcelCell.Font.Size = this.fontSize;
                myExcelCell.Font.Name = this.fontStyle;
                myExcelCell.wrapText = this.lineWrap;
                myExcelCell.Interior.ColorIndex = this.backGround;
                myExcelCell.Font.ColorIndex = this.fontColor;
                if(this.tableBorder != -1){
                 myWorksheet.Range(myExcelCell, myExcelCell2).Borders(1).Weight = this.tableBorder ;
                 myWorksheet.Range(myExcelCell, myExcelCell2).Borders(2).Weight = this.tableBorder ;
                 myWorksheet.Range(myExcelCell, myExcelCell2).Borders(3).Weight = this.tableBorder ;
                 myWorksheet.Range(myExcelCell, myExcelCell2).Borders(4).Weight = this.tableBorder ;
                }

                myExcelCell.Value = myHTMLTableCell.innerText;
                for (row = readRow; row <= myCellRowSpan + readRow – 1; row++) {
                    for (col = readCol; col <= myCellColSpan + readCol – 1; col++) {
                        excelTable[row][col] = true;
                    }
                }

                readCol = readCol + myCellColSpan;
            } else {
                myExcelCell = myWorksheet.Cells(readRow, readCol);
                myExcelCell.Value = myHTMLTableCell.innerText;
                myExcelCell.HorizontalAlignment = this.textAlign;
                myExcelCell.Font.Size = this.fontSize;
                myExcelCell.Font.Name = this.fontStyle;
                myExcelCell.wrapText = this.lineWrap;
                myExcelCell.Interior.ColorIndex = this.backGround;
                myExcelCell.Font.ColorIndex = this.fontColor;
                if(this.tableBorder != -1){
                 myExcelCell.Borders(1).Weight = this.tableBorder ;
                 myExcelCell.Borders(2).Weight = this.tableBorder ;
                 myExcelCell.Borders(3).Weight = this.tableBorder ;
                 myExcelCell.Borders(4).Weight = this.tableBorder ;
                }              
                excelTable[readRow][readCol] = true;
                readCol = readCol + 1;
            }
        }
    }
    if(this.autoFit == true)
     myWorksheet.Columns.AutoFit;

    jXls.UserControl = true;
    jXls = null;
    myWorkbook = null;
    myWorksheet = null;
};

Copy codeThe code is as follows:

//Eltailbeout is the outer table of the exported content. It is mainly used to set the styles such as border. Eldiv is the HTML part of the whole export
function onhtmlToExcel(elTableOut,elDiv){
 try{
/ / set the data before export, and set the return format after export
  var elDivStrBak = elDiv.innerHTML;
/ / set border = 1 in the table, so that there will be a table line PS in Excel: Thank you for reminding me on both sides
  elTableOut.border=1;
/ / filter eldiv content
  var elDivStr = elDiv.innerHTML;
  elDivStr = replaceHtml(elDivStr,”<A”,”>”);
  elDivStr = replaceHtml(elDivStr,”</A”,”>”);
  elDiv.innerHTML=elDivStr; 

  var oRangeRef = document.body.createTextRange();
  oRangeRef.moveToElementText( elDiv );
  oRangeRef.execCommand(“Copy”);

/ / return the content before the format change
  elDiv.innerHTML = elDivStrBak;
/ / the content data may be large, so empty it
  elDivStrBak = “”;
  elDivStr = “”;

  var oXL = new ActiveXObject(“Excel.Application”)
  var oWB = oXL.Workbooks.Add ;
  var oSheet = oWB.ActiveSheet ;
  oSheet.Paste();
  oSheet.Cells.NumberFormatLocal = “@”;
  oSheet.Columns(“D:D”).Select
  oXL.Selection.ColumnWidth = 20
  oXL.Visible = true;  
  oSheet = null;
  oWB = null;
  appExcel = null;
 }catch(e){
  alert(e.description)
 }
}

function replaceHtml(replacedStr,repStr,endStr){  
  var replacedStrF = “”;  
  var replacedStrB = “”;  
  var repStrIndex = replacedStr.indexOf(repStr);  
  while(repStrIndex != -1){  
      replacedStrF = replacedStr.substring(0,repStrIndex);  
      replacedStrB = replacedStr.substring(repStrIndex,replacedStr.length);  
      replacedStrB = replacedStrB.substring(replacedStrB.indexOf(endStr)+1,replacedStrB.length);  
      replacedStr = replacedStrF + replacedStrB;  
      repStrIndex = replacedStr.indexOf(repStr);  
  }  
  return replacedStr;
}

Copy codeThe code is as follows:

/ / generate Excel
  function onTableToExcel(){
   var elTableOut = document.getElementById(“elTableOut”);
   var elDiv = document.getElementById(“elDiv”);
   onhtmlToExcel(elTableOut,elDiv);
  }