component:
<apex:component controller="DataImportCtrl">
<apex:stylesheet value="{!URLFOR($Resource.JQWidget, '/jqwidgets/styles/jqx.base.css')}" />
<apex:includeScript value="{!URLFOR($Resource.xlsx)}" />
<apex:includeScript value="{!URLFOR($Resource.JQWidget, '/scripts/jquery-1.11.1.min.js')}" />
<apex:includeScript value="{!URLFOR($Resource.JQWidget, '/jqwidgets/jqxcore.js')}" />
<apex:includeScript value="{!URLFOR($Resource.JQWidget, '/jqwidgets/jqxbuttons.js')}" />
<apex:includeScript value="{!URLFOR($Resource.JQWidget, 'jqwidgets/jqxfileupload.js')}" />
<apex:attribute default="Contact.xlsx" name="fileName" type="String" description="" />
<apex:attribute default="{'FirstName':'名字','LastName':'姓氏','Email':'邮箱'}" name="field2label" type="Object" description="" />
<apex:attribute default="['FirstName','LastName','Email']" name="exportHeader" type="List" description="" />
<apex:attribute default="Contact" name="sobjectAPI" type="String" description="" />
<style>
.a-upload {
padding: 4px 10px;
height: 20px;
line-height: 20px;
position: relative;
cursor: pointer;
color: #000;
background: #efefef;
border: 1px solid #aaa;
border-radius: 4px;
overflow: hidden;
display: inline-block;
*display: inline;
*zoom: 1
}
.a-upload input {
position: absolute;
font-style: normal;
font-size: 13px;
right: 0;
top: 0;
opacity: 0;
filter: alpha(opacity=0);
cursor: pointer
}
.a-upload:hover {
color: #444;
background: #eee;
border-color: #ccc;
text-decoration: none
}
</style>
<script>
//文件解析
function fileChange(obj)
{
var file=$(obj)[0].files[0];
if(file)
{
var reader = new FileReader();
reader.onload = function(e)
{
//读取sheet数据
var workbook = XLSX.read(e.target.result, {type: 'binary',});
console.log('***************************workbook==>');
console.log(workbook);
//通过工作簿名称获取对应数据
var worksheet=workbook.Sheets[workbook.SheetNames[0]];
console.log('***************************worksheet==>');
console.log(worksheet);
const range=XLSX.utils.decode_range(worksheet['!ref']);
//替换表头变更为后台可序列的字段API
for(let c=range.s.c;c<=range.e.c;c++)
{
const header=XLSX.utils.encode_col(c)+'1';
console.log(header);
worksheet[header].w = label2field[worksheet[header].w];
}
//sheet数据转为JSON
var excelDataJson=XLSX.utils.sheet_to_json(worksheet);
if(excelDataJson.length==0)
{
alert("请检查您的excel文件,当前无可上传数据!");
return;
}
//添加可直接用于后台序列化Sobject的元素
for(var i=0;i<excelDataJson.length;i++)
{
console.log('***************************excelDataJson[i]==>');
console.log(excelDataJson[i]);
excelDataJson[i].attributes={"type":"{!sobjectAPI}"};
}
excelDataJson=JSON.stringify(excelDataJson);
console.log("**************************excelDataJson==>");
console.log(excelDataJson);
//调用后台方法进行数据dml操作
Visualforce.remoting.Manager.invokeAction(
'{!$RemoteAction.DataImportCtrl.insertRecord}',
excelDataJson,
function(result,event){
if(event.status)
{
console.log("**************************result==>");
console.log(result);
if(result.isError==true)
{
if(result.msgError!=undefined&&result.msgError.indexOf("ERROR:")==0)
{
alert('系统后台报错,请联系系统管理员。报错消息为:'+result);
}
else
{
var exportData=[];
exportData[0]={};
exportData[0].sheetLabel='ERROR';
exportData[0].sheetItems=[];
for(var i=0;i<result.rows.length;i++)
{
var item={};
item=result.rows[i].record;
if(undefined!=item.attributes) delete item.attributes;
if(undefined!=item.Id) delete item.Id;
item.ERROR=result.rows[i].msgError;
exportData[0].sheetItems.push(item);
}
var header={};
header.header=[];
//设置header顺序
for(var i=0;i<exportHeader.length;i++)
{
header.header[i]=exportHeader[i];
}
header.header[exportHeader.length]="ERROR";
download(exportData,"ERROR.xlsx",header);
}
}
else
{
alert("数据导入成功!");
loaderAllData();
}
$(".showFileName").html("");
obj.value="";
}
})
};
reader.onerror = function (e) {console.log("error reading file");};
//文件加载结束的时候
reader.onloadend = function (e) {console.log("onloadend");};
reader.readAsBinaryString(file);
}
}
//文件下载
function download(excelDataJson,fileName,fields)
{
var wb = XLSX.utils.book_new();
for(var i=0;i<excelDataJson.length;i++)
{
const ws = XLSX.utils.json_to_sheet(excelDataJson[i].sheetItems,fields);
const range = XLSX.utils.decode_range(ws['!ref'])
for(let c = range.s.c; c <= range.e.c; c++) {
const header = XLSX.utils.encode_col(c) + '1';
if(ws[header].v=="ERROR") continue;
ws[header].v = field2label[ws[header].v];
}
XLSX.utils.book_append_sheet(wb,ws,excelDataJson[i].sheetLabel);
}
const workbookBlob = workbook2blob(wb);
openDownloadDialog(workbookBlob,fileName);
}
function workbook2blob(workbook)
{
// 生成excel的配置项
var wopts =
{
// 要生成的文件类型
bookType: "xlsx",
// // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
bookSST: false,
type: "binary"
};
var wbout = XLSX.write(workbook, wopts);
// 将字符串转ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
return buf;
}
var blob = new Blob([s2ab(wbout)], {type: "application/octet-stream"});
return blob;
}
//将blob对象创建bloburl,然后用a标签实现弹出下载框
function openDownloadDialog(blob, fileName) {
//创建blob地址
if (typeof blob == "object" && blob instanceof Blob) { blob = URL.createObjectURL(blob); }
var aLink = document.createElement("a");
aLink.href = blob;
//HTML5新增的属性,指定保存文件名,可以不要后缀,注意,有时候 file:///模式下不会生效
aLink.download = fileName || "";
var event;
if (window.MouseEvent) event = new MouseEvent("click");
//移动端
else {
event = document.createEvent("MouseEvents");
event.initMouseEvent( "click", true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null );
}
aLink.dispatchEvent(event);
}
</script>
<div>
<table>
<tr>
<td>
<a href="javascript:;" class="a-upload" style="background-color:#5bc0de;color:#FFFFFF;font-weight:bold;">点击这里上传文件
<input type="file" accept=".xlsx,.xls,.csv" id="uploadFile" />
</a>
<span class="showFileName"></span>
<span class="fileerrorTip"></span>
</td>
<td>
<div >
<button id="downLoadTemplate" style="background-color:#5bc0de;color:#FFFFFF;height: 30px;">DownLoad Template</button>
</div>
</td></tr>
</table>
</div>
<script>
var exportHeader = "{!exportHeader}";
var field2label = "{!field2label}";
var label2field = {};
$(document).ready(function() {
//导出数据模板Header
exportHeader = exportHeader.replace(new RegExp("'", "g"), '"');
exportHeader = JSON.parse(exportHeader);
console.log('***************************exportHeader==>');
console.log(exportHeader);
//field2label
field2label = field2label.replace(new RegExp("'", "g"), '"');
field2label = JSON.parse(field2label);
console.log('***************************field2label==>');
console.log(field2label);
//转field2label映射为label2field映射
for (var key in field2label) {
label2field[field2label[key]] = key;
}
console.log('***************************label2field==>');
console.log(label2field);
//文件导入方法
$(".a-upload").on("change", "input[type='file']", function() {
var filePath = $(this).val();
if (filePath.indexOf("xlsx") != -1 || filePath.indexOf("csv") != -1 || filePath.indexOf("xls") != -1) {
$(".fileerrorTip").html("").hide();
var arr = filePath.split('\\');
var fileName = arr[arr.length - 1];
$(".showFileName").html(fileName);
fileChange(this);
} else {
$(".showFileName").html("");
$(".fileerrorTip").html("您未上传文件,或者您上传文件类型有误!").show();
return false
}
});
$("#downLoadTemplate").jqxButton();
//导入模板下载
$('#downLoadTemplate').on('click', function() {
var excelDataJson = [];
excelDataJson[0] = {};
excelDataJson[0].sheetLabel = "{!fileName}";
excelDataJson[0].sheetItems = [];
var header = {};
header.header = [];
//设置header顺序
for (var i = 0; i < exportHeader.length; i++) {
header.header[i] = exportHeader[i];
}
console.log('***************************header==>');
console.log(header);
download(excelDataJson, "{!fileName}", header);
});
});
</script>
</apex:component>
Controller:
public with sharing class DataImportCtrl {
@RemoteAction
public static ExcelData insertRecord(String jsonStr) {
ExcelData excelData = new ExcelData();
try {
Savepoint sp = Database.setSavepoint();
Sobject[] records = (Sobject[])JSON.deserializeStrict(jsonStr, Sobject[].class);
excelData.rows = new RowRecord[]{};
Database.SaveResult[] results = Database.insert(records, false);
excelData.isError = false;
for (Integer i = 0; i < results.size(); i++) {
RowRecord row = new RowRecord();
row.record = records[i];
if (!results[i].isSuccess()) {
row.msgError = '';
for (Database.Error e : results[i].errors) {
if (row.msgError == '') {
row.msgError += e.getMessage();
}
else {
row.msgError += ';' + e.getMessage();
}
}
System.debug(String.valueOf(results[i].errors));
excelData.isError = true;
}
excelData.rows.add(row);
}
if (excelData.isError) {
Database.rollback(sp);
}
return excelData;
} catch (Exception e) {
excelData.isError = true;
excelData.msgError = 'ERROR:' + e.getMessage();
return excelData;
}
}
public class ExcelData {
public RowRecord[] rows;
public Boolean isError;
public String msgError;
}
public class RowRecord {
public Sobject record;
public String msgError;
}
}