透過Javascript將資料匯出到外部Excel文件的函數程式碼_javascript技巧

複製程式碼 程式碼如下:

function AutomateExcel() { > Start Excel and get Application object.
var oXL;
try
{
oXL = new ActiveXObject("Excel.Application");
}
catch(e)
{
alert("無法啟動Excel!nn如果您確信您的電腦中已經安裝了Excel，" "那麼請調整IE的安全等級。 n具體操作： n" "工具→ Internet選項→ 安全性→ 自訂等級→ 對沒有標記為安全的ActiveX進行初始化和腳本運行→ 啟用");
return false;
}
//Get a new workbook .
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var table = $("#GridView1")[0];
var rows = table. rows;
var columns = table.rows(0).cells;
var codes = "";
//設定標題
var name = "第 頁";
oXL.Caption = name;
oSheet.Name = name;
//設定表頭
oSheet.Cells(1, 1).Value = "申請單編號";
oSheet .Cells(1, 2).Value = "出貨金額";
oSheet.Cells(1, 3).Value = "行銷部門";
oSheet.Cells(1, 4).Value = "業務人員";
oSheet.Cells(1, 5).Value = "內部合約號碼";
oSheet.Cells(1, 6).Value = "客戶名稱";
oSheet.Cells( 1, 7).Value = "幣種";
oSheet.Cells(1, 8).Value = "客戶船期";
oSheet.Cells(1, 9).Value = "國別" ;
oSheet.Cells(1, 10).Value = "核銷單號";
oSheet.Cells(1, 11).Value = "發票號碼";
oSheet.Cells(1, 12).Value = "報關日期";
//取得目前頁申請單編號
for (var i = 2; i codes = "'" rows (i - 1).cells(0).innerText "',";
}
codes = "''";
//取得資料並填入EXCEL
$.post( "../Handlers/ShippingApplyHandler.ashx",
{ Action: "ExportData", ExportCondition: codes },
function (views) {
if (views != null) {
var beginindex = 1;
var endindex = 1;
for (var i = 0; i endindex ;
oSheet.Cells(i 2, 1).Value = views[i].SACode;
oSheet.Cells(i 2, 2).Value = views[i].AmountSum;
oSheet.Cells(i 2, 3).Value = views[i].Department ;
oSheet.Cells(i 2, 4).Value = views[i].SalesName;
oSheet.Cells(i 2, 5).Value = views[i].ContractNo;
oSheet. Cells(i 2, 6).Value = views[i].CustomerName;
oSheet.Cells(i 2, 7).Value = views[i].CurrencyCode;
if (views[i].CustomerSchedule != null) {
oSheet.Cells(i 2, 8).Value = ConvertToJSDate(views[i].CustomerSchedule).Format("yyyy-MM-dd");
}
oSheet. Cells(i 2, 9).Value = views[i].Country;
oSheet.Cells(i 2, 10).Value = views[i].VerificationNumber;
oSheet.Cells(i 2, 11 ).Value = views[i].InvoiceNumber;
if (views[i].CustomsDate != null) {
oSheet.Cells(i 2, 12).Value = ConvertToJSDate(views[i].CustomsDate ).Format("yyyy-MM-dd");
}
if (i > 0 && views[i - 1].SACode == views[i].SACode) {
oSheet.Range (oSheet.Cells(beginindex, 1), oSheet.Cells(endindex, 1)).Merge();
oSheet.Range(oSheet.Cells(beginindex, 2), oSheet.Cells(endindex, 2)). Merge();
oSheet.Range(oSheet.Cells(beginindex, 3), oSheet.Cells(endindex, 3)).Merlls();
oSheet.Range(oSheet.Cells(beginindex, 4), oSheet.Cells(endindex, 4)).Merge();
oSheet.Range(oSheet.Cells(beginindex, 5), oSheet.Cells(endindex, 5)).Merge();
oSheet.Range (oSheet.Cells(beginindex, 6), oSheet.Cells(endindex, 6)).Merge();
oSheet.Range(oSheet.Cells(beginindex, 7), oSheet.Cells(endindex, 7)). Merge();
oSheet.Range(oSheet.Cells(beginindex, 8), oSheet.Cells(endindex, 8)).Merge();
beginindex = endindex;
}
else {
beginindex ;
}
}
}
}, "json");
//設定自動列寬
oSheet.Columns.AutoFit();
//設定excel為可見
oXL.Visible = true;
//將Excel交由使用者控制
oXL.UserControl = true;
//禁止提示
oXL.DisplayAlerts = false ;
//釋放資源
//oXL = null;
//oWB = null;
//oSheet = null;
}
catch (e) {
}
}