Javascript 將table數據導出到Excel – Javascript教程_JS教程_技術文章 – 程式設計聯盟

直接將源代碼拷貝過來的

<html>

<body>
<script language="javascript">      
//導出excel
        function TableToExcelUtil(tableID) {
         this.tableBorder = -1; //邊框類型,-1沒有邊框 可取1/2/3/4
         this.backGround = 4; //背景顏色:白色 可取調色板中的顏色編號 1/2/3/4….
         this.fontColor = 1; //字體顏色:黑色
         this.fontSize = 10; //字體大小
         this.fontStyle = "宋體"; //字體類型
         this.rowHeight = 20; //行高
         this.columnWidth = -1; //列寬
         this.lineWrap = true; //是否自動換行
         this.textAlign = -4108; //內容對齊方式 默認為居中
         this.autoFit = true; //是否自適應寬度
         this.tableID = tableID;
     }
         TableToExcelUtil.prototype.setTableBorder = function (excelBorder) {
             this.tableBorder = excelBorder ;
         };
         TableToExcelUtil.prototype.setBackGround = function (excelColor) {
             this.backGround = excelColor;
         };
         TableToExcelUtil.prototype.setFontColor = function (excelColor) {
             this.fontColor = excelColor;
         };
         TableToExcelUtil.prototype.setFontSize = function (excelFontSize) {
             this.fontSize = excelFontSize;
         };
         TableToExcelUtil.prototype.setFontStyle = function (excelFont) {
             this.fontStyle = excelFont;
         };
         TableToExcelUtil.prototype.setRowHeight = function (excelRowHeight) {
             this.rowHeight = excelRowHeight;
         };
         TableToExcelUtil.prototype.setColumnWidth = function (excelColumnWidth) {
             this.columnWidth = excelColumnWidth;
         };
         TableToExcelUtil.prototype.isLineWrap = function (lineWrap) {
             if (lineWrap == false || lineWrap == true) {
                 this.lineWrap = lineWrap;
             }
         };
         TableToExcelUtil.prototype.setTextAlign = function (textAlign) {
             this.textAlign = textAlign;
         };
         TableToExcelUtil.prototype.isAutoFit = function(autoFit){
             if(autoFit == true || autoFit == false)this.autoFit = autoFit ;
         }
         //文件轉換主函數
         TableToExcelUtil.prototype.getExcelFile = function () {
         var jXls, myWorkbook, myWorksheet, myHTMLTableCell, myExcelCell, myExcelCell2;
         var myCellColSpan, myCellRowSpan;
         try {
             jXls = new ActiveXObject('Excel.Application');
         }catch (e) {
             alert("無法啟動Excel!\n\n如果您確信您的電腦中已經安裝瞭Excel,"+"那麼請調整IE的安全級別。\n\n具體操作:\n\n"+"工具 → Internet選項 → 安全 → 自定義級別 → 對沒有標記為安全的ActiveX進行初始化和腳本運行 → 啟用");
             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;
         //設置行高、列寬
         if(this.columnWidth != -1)
             myWorksheet.Columns.ColumnWidth = this.columnWidth;
         else
             myWorksheet.Columns.ColumnWidth = 7;
         if(this.rowHeight != -1)
             myWorksheet.Rows.RowHeight = this.rowHeight ;
         //搜索需要轉換的Table對象,獲取對應行、列數
         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;
                 }
             }
         }
         //開始構件模擬表格
         var excelTable = new Array();
         for (i = 0; i <= totalRow; i++) {
             excelTable[i] = new Array();
             for (t = 0; t <= totalCol; t++) {
                 excelTable[i][t] = false;
             }
         }
         //開始轉換表格
         for (z = 0; z < obj[tabNum].rows.length; z++) {
             readRow = z + 1;
             readCol = 0;
             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;
     };
     function exportExcel(tid)
     {
     var tb = new TableToExcelUtil(tid);
     tb.setFontStyle("Courier New");
     tb.setFontSize(10); //推薦取值10
     tb.setFontColor(6); //一般情況不用設置
     tb.setBackGround(2); //一般情況不用設置
     tb.setTableBorder(2); //推薦取值2
     tb.setColumnWidth(10); //推薦取值10
     tb.isLineWrap(false);
     tb.isAutoFit(true);
    
     tb.getExcelFile();
    }
</script>    
<input type="button" onClick="exportExcel('tab01')" value="導出表格">
fgsghsh
<table width="200" border="1">
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
<p></p>
<p id="tabEnterNpDiv">
 <table border="1" style="display:block" id="tab01">
 <tr bgcolor="tomato">
  <td align="middle">省份</td>
  <td id = "province" colspan="2" align="middle">天津</td>
  <td colspan="4" align="middle">攜入</td>
 </tr>
 <tr bgcolor="turquoise">
  <td rowspan="2" align="middle">時間</td>
  <td align="middle">開始時間</td>
  <td align="middle">結束時間</td>
  <td align="middle">電信</td>
  <td align="middle">移動</td>
  <td align="middle">聯通</td>
  <td align="middle">攜出合計</td>
 </tr>
 <tr bgcolor="mistyrose">
  <td id = "sDate" align="middle">2010-10-17</td>
  <td id = "eDate" align="middle">2010-11-17</td>
  <td align="middle">成功量</td>
  <td align="middle">成功量</td>
  <td align="middle">成功量</td>
  <td align="middle">成功量</td>
 </tr>
 <tr bgcolor="loralwhite">
  <td rowspan="4" align="middle">攜出</td>
  <td colspan="2" align="middle">電信</td>
  <td align="middle">–</td>
  <td id = "s001002" align="middle">500</td>
  <td id = "s001003" align="middle">700</td>
  <td id = "out001" align="middle">1200</td>
 </tr>
 <tr bgcolor="indigo">
  <td colspan="2" align="middle">移動</td>
  <td id = "s002001" align="middle">3000</td>
  <td align="middle">–</td>
  <td id = "s002003" align="middle">2500</td>
  <td id = "out002" align="middle">5500</td>
 </tr>
 <tr bgcolor="Gray">
  <td colspan="2" align="middle">聯通</td>
  <td id = "s003001" align="middle">3000</td>
  <td id = "s003002" align="middle">500</td>
  <td align="middle">–</td>
  <td id = "out003" align="middle">3500</td>
 </tr>
 <tr bgcolor="tan">
  <td colspan="2" align="middle">攜入合計</td>
  <td id = "in001" align="middle">6000</td>
  <td id = "in002" align="middle">1000</td>
  <td id = "in003" align="middle">3200</td>
  <td id = "inout" align="middle">10200</td>
 </tr>
</table>
</p>
<p></p><table width="200" border="1">
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>

</body>
</html>

摘自 Core_Star的專欄

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。