直接上代码
POM.XML 这里只是主要的jar
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
/**
* 导出excel
* @param fileName 要导出的文件名(包含路径)
* @param data
* @return
*/
public boolean exportToExcel(String fileName, List<String[]> data) {
log.info("导出excel开始,fileName: " + fileName + ", data个数:" + data.size());
if (!fileName.endsWith(".xlsx")){
log.error("fileName有误,需要以xlsx结尾");
return false;
}
// 声明一个工作薄
Workbook workBook = null;
boolean result = false;
ByteArrayOutputStream ops = null;
ByteArrayInputStream in = null;
File file = new File(fileName);
try {
if (file.exists()) {
file.delete();
}
file = new File(fileName);
//重新复制模版文件
FileUtils.copyFile(new File(ExcelExportUtil.class.getResource("/template/policy_Template.xlsx").getFile()),file);
// 声明一个工作薄
workBook = new XSSFWorkbook(new FileInputStream(file));
// 生成一个表格
Sheet sheet = workBook.getSheetAt(0);
if (sheet == null) {
sheet = workBook.createSheet("AutoPolicy");
}
//最新Excel列索引,从0开始
int lastRowIndex = sheet.getLastRowNum();
lastRowIndex++;
// 设置表格默认列宽度
sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
// 产生表格表头列标题行
Row row ;
NumberFormat decimalFormat = new DecimalFormat("###,###.00");
DecimalFormat zeroFormat = new DecimalFormat("0.00");
// 遍历集合数据,产生数据行,前两行为标题行与表头行
for (String[] dataRow : data) {
row = sheet.createRow(lastRowIndex);
lastRowIndex++;
for (int j = 0; j < dataRow.length; j++) {
Cell contentCell = row.createCell(j);
String dataObject = dataRow[j];
if (dataObject != null) {
if (isNumeric(dataObject)){
contentCell.setCellType(CellType.NUMERIC);
if (dataObject.contains(".")){
if(new BigDecimal(dataObject).compareTo(BigDecimal.ZERO) == 0){
contentCell.setCellValue(zeroFormat.format(0));
}else {
contentCell.setCellValue(decimalFormat.format(new BigDecimal(dataObject)));
}
}else {
contentCell.setCellValue(dataObject);
}
} else {
contentCell.setCellType(CellType.STRING);
contentCell.setCellValue(dataObject);
}
} else {
// 设置单元格内容为字符型
contentCell.setCellValue("");
}
}
}
ops = new ByteArrayOutputStream();
workBook.write(ops);
byte[] b = ops.toByteArray();
in = new ByteArrayInputStream(b);
fileStreamUpload.uploadFile(in, fileName);
result = true;
ops.flush();
file.delete();
System.out.println("success");
} catch (Exception e) {
log.error("导出excel失败,失败原因:" + e.getMessage());
e.printStackTrace();
}finally {
IOUtils.closeQuietly(workBook);
IOUtils.closeQuietly(ops);
IOUtils.closeQuietly(in);
}
return result;
}
其中:
注意点: 1、fileName 为文件名(.xlsx)。
2、只需要生成本地文件,只需要修改如下:
ops = new FileOutputStream(file);
workBook.write(ops);
ops.flush();
result = true;
3、policy_Template.xlsx 为模板文件(放着我们生成的excel文件的Title)
4、NumberFormat decimalFormat = new DecimalFormat("###,###.00"); 格式化金额
5、FTP 上传代码:
public void uploadFile(InputStream inputStream,String fileName) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
byte[] buffer = new byte[1024];
int len;
try {
while ((len = inputStream.read(buffer)) > -1) {
baos.write(buffer, 0, len);
}
baos.flush();
} catch (IOException e) {
e.printStackTrace();
}
InputStream stream1 = new ByteArrayInputStream(baos.toByteArray());
InputStream stream2 = new ByteArrayInputStream(baos.toByteArray());
BufferedInputStream inBuf = null;
String ftpPath = CommUtil.fetchSystemDate("yyyy-MM-dd") + "/export_policy/";
// 数据库存入地址
try {
inBuf = new BufferedInputStream(stream1);
// 开始上传文件
FileFtp ftp = new FileFtp();
ftp.login(ftpIp, ftpUser, ftpPassWord);
ftp.CreateDirecroty(ftpPath);
ftp.uploadFile(inBuf, fileName);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (stream1 != null)
stream1.close();
if (stream2 != null)
stream2.close();
if (stream2 != null)
inputStream.close();
if (inBuf != null)
inBuf.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
如有疑问,评论或加QQ+41717972@qq.com
补充FileFtp代码:
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPReply;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
public class FileFtp {
FileFtp instance;
private FTPClient ftpClient = null;
public synchronized FileFtp getInstance() {
if (instance == null) {
instance = new FileFtp();
}
return instance;
}
public void login(String s_url, String uname, String pass) {
ftpClient = new FTPClient();
try {
// 连接
ftpClient.connect(s_url);
ftpClient.login(uname, pass);
// 检测连接是否成功
int reply = ftpClient.getReplyCode();
if (!FTPReply.isPositiveCompletion(reply)) {
this.closeCon();
}
} catch (Exception ex) {
ex.printStackTrace();
// 关闭
this.closeCon();
}
}
public boolean uploadFile(InputStream in, String targetFname) {
boolean flag = false;
if (ftpClient != null) {
BufferedInputStream buff = new BufferedInputStream(in);
try {
// 设置上传目录
ftpClient.setBufferSize(1024);
ftpClient.setControlEncoding("UTF-8");
// 设置文件类型(二进制)
ftpClient.setFileType(FTPClient.BINARY_FILE_TYPE);
// 重新开放新端口
ftpClient.enterLocalPassiveMode();
// 上传
flag = ftpClient.storeFile(targetFname, buff);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(buff != null)
buff.close();
if(in != null)
in.close();
this.closeCon();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return flag;
}
public void closeCon() {
if (ftpClient != null) {
if (ftpClient.isConnected()) {
try {
ftpClient.logout();
ftpClient.disconnect();
} catch (IOException e) {
e.printStackTrace();
}
}
ftpClient = null;
}
}
public boolean removeFile(String srcFname){
boolean flag = false;
if( ftpClient!=null ){
try {
flag = ftpClient.deleteFile(srcFname);
} catch (IOException e) {
e.printStackTrace();
}finally{
this.closeCon();
}
}
return flag;
}
public boolean CreateDirecroty(String remote) throws IOException {
boolean success = true;
String directory = remote.substring(0, remote.lastIndexOf("/") + 1);
// 设置初始地址
ftpClient.changeWorkingDirectory("/");
// 如果远程目录不存在,则递归创建远程服务器目录
if (!directory.equalsIgnoreCase("/") && !ftpClient.changeWorkingDirectory(new String(directory))) {
int start = 0;
int end = 0;
if (directory.startsWith("/")) {
start = 1;
} else {
start = 0;
}
end = directory.indexOf("/", start);
while (true) {
String subDirectory = new String(remote.substring(start, end));
if (!ftpClient.changeWorkingDirectory(subDirectory)) {
if (ftpClient.makeDirectory(subDirectory)) {
ftpClient.changeWorkingDirectory(subDirectory);
} else {
success = false;
}
}
start = end + 1;
end = directory.indexOf("/", start);
// 检查所有目录是否创建完毕
if (end <= start) {
break;
}
}
}
return success;
}
}