VF实现Excel/CSV的导入导出
- 2019 年 10 月 8 日
- 筆記
1.先贴上VF页面的代码通过在<apex:page contenttype="application/x-excel# GenExcel.xls>可以实现生成Excel
实现Excel的导出VF页面代码如下
<apex:page controller="BookController" contenttype="application/x-excel# GenExcel.xls" showheader="false">
<head>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
</head>
<apex:dataTable value="{!acclist}" var="acc" border="1">
<apex:column >
<apex:facet name="header">Name</apex:facet>
{!acc.Name}
</apex:column>
<apex:column >
<apex:facet name="header">Phone</apex:facet>
{!acc.Phone}
</apex:column>
<apex:column >
<apex:facet name="header">AccountNumber</apex:facet>
{!acc.AccountNumber}
</apex:column>
</apex:dataTable>
</apex:page>
apex类后台控制器代码
public class BookController {
public List<Account> acclist{get;set;}
public List<Account> getBookWithIndex() {
String fetchAllGoods = 'SELECT Name,Phone,AccountNumber FROM Account';
system.debug(fetchAllGoods);
acclist = Database.query(fetchAllGoods);
return acclist;
}
}
二 、实现CSV、EXcel的导入都是同样的方法
VF页面代码
<apex:page controller="importDataFromCSVController">
<apex:form >
<apex:pagemessages />
<apex:pageBlock >
<apex:pageBlockSection columns="4">
<apex:inputFile value="{!csvFileBody}" filename="{!csvAsString}"/>
<apex:commandButton value="Import Account" action="{!importCSVFile}"/>
</apex:pageBlockSection>
</apex:pageBlock>
<apex:pageBlock >
<apex:pageblocktable value="{!accList}" var="acc">
<apex:column value="{!acc.Name}" />
<apex:column value="{!acc.Phone}" />
<apex:column value="{!acc.AccountNumber}" />
</apex:pageblocktable>
</apex:pageBlock>
</apex:form>
</apex:page>
Apex类控制后台代码实现
public class importDataFromCSVController {
public Blob csvFileBody{get;set;}
public string csvAsString{get;set;}
public String[] csvFileLines{get;set;}
public List<Account> acclist{get;set;}
public importDataFromCSVController(){
csvFileLines = new String[]{};
acclist = New List<Account>();
}
public void importCSVFile(){
system.debug('ssss');
try{
csvAsString = bitToString(csvFileBody,'gb2312');
system.debug(csvAsString);
csvFileLines = csvAsString.split('n');
system.debug(csvFileLines.size());
for(Integer i=1;i<csvFileLines.size();i++){
system.debug('Account');
Account accObj = new Account() ;
string[] csvRecordData = csvFileLines[i].split(',');
system.debug(csvRecordData);
accObj.Name = csvRecordData[0] ;
accObj.Phone=csvRecordData[1];
accObj.AccountNumber=csvRecordData[2];
acclist.add(accObj);
system.debug('gg'+ accObj.AccountNumber);
}
system.debug(acclist);
insert acclist;
ApexPages.Message successMsg = new ApexPages.Message(ApexPages.severity.INFO,'import success');
ApexPages.addMessage(successMsg);
}
catch (Exception e)
{
ApexPages.Message errorMessage = new ApexPages.Message(ApexPages.severity.ERROR,'An error has occured while importin data Please make sure input csv file is correct');
ApexPages.addMessage(errorMessage);
}
}
public static String bitToString(Blob input, String inCharset){
//转换成16进制
String hex = EncodingUtil.convertToHex(input);
//一个String类型两个字节 32位（bit）,则一个String长度应该为两个16进制的长度，所以此处向右平移一个单位，即除以2
//向右平移一个单位在正数情况下等同于除以2，负数情况下不等
//eg 9 00001001 >>1 00000100 结果为49
final Integer bytesCount = hex.length() >> 1;
// //声明String数组，长度为16进制转换成字符串的长度1
String[] bytes = new String[bytesCount];
for(Integer i = 0; i < bytesCount; ++i) {
//将相邻两位的16进制字符串放在一个String中
bytes[i] = hex.mid(i << 1, 2);
}
//解码成指定charset的字符串
return EncodingUtil.urlDecode('%' + String.join(bytes, '%'), inCharset);
}
}
