table in Jsp multi table header export excel file concrete implementation

  • 2020-11-25 07:28:29
  • OfStack

First introduce two copies of JS: copyhtmltoexcel.js and tableToExcel.js


/* 
 *  The default transformation implementation function should be extended if additional functionality is required 
 *  Parameters: 
 *      tableID : HTML In the Table object id Attribute values 
 *  See below for detailed usage  TableToExcel  Object definitions   
 */
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: HTML In the Table Object converted to Excel Generic object .
 *   The author: Jeva
 *   Time: 2006-08-09
 *   Parameters: tableID  HTML In the Table The object's ID Attribute values 
 *   Description: 
 *        Able to adapt to complexity HTML In the Table Automatic transformation of objects, which automatically extends information based on rows and columns 
 *        merge Excel The cell in which the client needs to have installed Excel
 *        For detailed property and method references, see: Excel the Microsoft Excel Visual Basic reference 
 *   Demonstration: 
 *       var tb = new TableToExcel('demoTable');
 *    tb.setFontStyle("Courier New");
 *    tb.setFontSize(10);  // The recommended values 10
 *    tb.setFontColor(6);  //1 Do not set it in general 
 *    tb.setBackGround(4);  //1 Do not set it in general 
 *    tb.setTableBorder(2);  // The recommended values 2
 *    tb.setColumnWidth(10);  // The recommended values 10
 *    tb.isLineWrap(false);
 *    tb.isAutoFit(true);
 *    
 *    tb.getExcelFile();
 *    If cell adaptation is set, the cell width is not set 
 *   Version: 1.0
 */
function TableToExcel(tableID) {
    this.tableBorder = -1; // Border type, -1 No border   desirable 1/2/3/4
    this.backGround = 0; // Background color: white     Color numbering in the palette is desirable  1/2/3/4....
    this.fontColor = 1;  // Font color: black 
    this.fontSize = 10;  // The font size 
    this.fontStyle = " Song typeface "; // Font type 
    this.rowHeight = -1; // Line height 
    this.columnWidth = -1; // Column width 
    this.lineWrap = true; // Whether to wrap lines automatically 
    this.textAlign = -4108; // Content alignment     The default is center 
    this.autoFit = false;  // Adaptive width or not 
    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 + 
           "\n\n If you are sure it is already installed on your computer Excel . "+
           " So please adjust IE Security level. \n\n Specific operation: \n\n"+
           " tool   -  Internet options   -   security   -   Custom level   -  ActiveX Controls and plug-ins  \n\n" +
       " -   To enable the  :  No, it's not marked safe ActiveX Control to initialize and run the script ");
        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 the 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 ;
// The search requires conversion Table Object to get the corresponding number of rows and columns 
    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 the 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;
};

copyhtmltoexcel.js


//elTalbeOut  This is the outer table of the exported content, mainly Settings border Something like that, elDiv It's the whole export html Part of the 
function onhtmlToExcel(elTableOut,elDiv){
 try{
  // Sets the data before the export and the format for the return after the export 
  var elDivStrBak = elDiv.innerHTML;
  // Set up the table the border=1 So that to excel I have the line in the table  ps: Thanks for the double-sided reminder 
  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");

  // Returns the content before the format change 
  elDiv.innerHTML = elDivStrBak;
  // The content data can be large, so empty 
  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;
}

Write the JS method on the JSP page


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

Note: Exporting excel using JS requires setting active for IE. Note that the best is the IE8 browser. As for the implementation of excel in the background of java has not been studied, I hope you can advise.


Related articles: