Excel工具类
以仓位明细为例：
package com.wintech.wdtmom.tool;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Component;
@Component(“PositionExcelDownload”)
public final class PositionExcelDownload {
// 下载方法
public Boolean download(HttpServletResponse response, String fileName, String path) {
File file = new File(path);
// 1、构建流的对象
InputStream is = null;
OutputStream out = null;
// 2、设置响应头
// 2、设置响应头
try {
response.setHeader(“content-disposition”, “attachment;filename=” + URLEncoder.encode(fileName, “UTF-8”));
} catch (Exception e) {
e.printStackTrace();
}
// 3、设置响应的数据类型
response.setContentType(“multipart/form-data”);
// 4、边读边写
byte[] bt = new byte[1024];
int len;
try {
is = new FileInputStream(path);
out = response.getOutputStream();
while ((len = is.read(bt)) != -1) {
out.write(bt, 0, len);
}
out.flush();
System.out.println(“表下载成功”);
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
// 5、关闭资源
if (out != null) {
try {
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (is != null) {
try {
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 6、删除源文件
file.delete();
// System.out.println(“删除文件”);
return false;
}
}
package com.wintech.wdtmom.tool;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import com.wintech.wdtmom.vo.PosiDetInfo;
@Component(“PositionExcel”)
public class PositionExcel {
/**
*
* 仓位明细excel
*
*/
public int createPositionDetailsExcel(HttpServletRequest request, HttpServletResponse response,
List positionList, String path) {
// 表头数据
String[] header = { “序号”, “所属仓库”, “储位代码”, “储位名称”, “储位属性”, “是否可用” };
File target = new File(path);
OutputStream out = null;
Workbook workbook = null;
try {
out = new FileOutputStream(target);
// 声明一个工作簿
workbook = new XSSFWorkbook();
// 生成一个表格，设置表格名称为"仓位明细表"
Sheet sheet = workbook.createSheet("仓位明细表");
// 创建行
int rowNum = 0;
Row row = sheet.createRow(rowNum++);
// 创建第一行的单元格并设置值
int coulumNum = 0;
for (int i = 0; i <= header.length - 1; i++) {
row.createCell(coulumNum++).setCellValue(header[i]);
}
for (int i = 0; i <= positionList.size() - 1; i++) {
PosiDetInfo PosiDetInfo = positionList.get(i);
coulumNum = 0;
row = sheet.createRow(rowNum++);
row.createCell(coulumNum++).setCellValue(PosiDetInfo.getId());
row.createCell(coulumNum++).setCellValue(PosiDetInfo.getStoreName());
row.createCell(coulumNum++).setCellValue(PosiDetInfo.getPositionCode());
row.createCell(coulumNum++).setCellValue(PosiDetInfo.getPositionName());
row.createCell(coulumNum++).setCellValue(PosiDetInfo.getAttributeName());
row.createCell(coulumNum++).setCellValue(PosiDetInfo.getIsUseable());
}
Sheet sheet1 = workbook.createSheet("sheet1");
Sheet sheet2 = workbook.createSheet("sheet2");
// 保存文件
workbook.write(out);
System.out.println("创建表成功");
return 1;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return 0;
}
}
控制层代码
package com.wintech.wdtmom.controller;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import com.wintech.wdtmom.service.PositionService;
import com.wintech.wdtmom.tool.PositionExcelDownload;
import com.wintech.wdtmom.vo.PosiDetInfo;
@RestController
public class PositionDownloadExcelController {
@Autowired
PositionService positionService;
@RequestMapping(value = "/positonDownLoadExcel", method = RequestMethod.GET)
@ResponseBody
public String downloadPositionDetails(HttpServletRequest request, HttpServletResponse response,
@RequestParam(required = false) String[] idArr) {
// 如果未传参数获取所有,否则根据id集合查询positionList
List<PosiDetInfo> positionList = (idArr == null || idArr.length == 0)
? positionService.queryPoInfo()
: positionService.selPosiById(idArr);
String[] st = positionService.getPositionExcel(request, response, positionList);
String newFileName = st[0];
String path = st[1];
if (path != null && newFileName != null) {
if (new PositionExcelDownload().download(response, newFileName, path)) {
System.out.println("文件已删除");
return "成功";
}
}
return "失败";
}
}
实现类代码：
@Override
public String[] getPositionExcel(HttpServletRequest request, HttpServletResponse response,
List positionList) {
// 获取跟目录
File path = null;
try {
path = new File(ResourceUtils.getURL(“classpath:”).getPath());
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (!path.exists()) {
path = new File(“”);
}
File upload = new File(path.getAbsolutePath(), “templates/excel/”);
if (!upload.exists()) {
upload.mkdirs();
}
String uploadPath = upload + “\”;
// long timetmp = new Date().getTime();
String newFileName = DateUtil.convertUtilDateToString(new Date()) + “.xlsx”;
String filePath = uploadPath + newFileName;
String[] st = new String[2];
if (new PositionExcel().createPositionDetailsExcel(request, response, positionList, filePath) == 1) {
st[0] = newFileName;
st[1] = filePath;
}
return st;
}
– 太无聊了了吗？