依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
controller层
@ApiOperation("导出")
@GetMapping(value = "/exportShopping")
public Object exportQuestions(HttpServletRequest request, HttpServletResponse response,Integer orderId) throws IOException, ParseException {
//其中的orderId是我查询的参数
Object result = orderShoppingService.ExcelExport(request,response,orderId);
return result;
}
service层
public Object ExcelExport(HttpServletRequest request, HttpServletResponse response, Integer orderId) throws IOException {
//根据订单的id查询出所有的商品信息
OrderShoppingQueryParam queryParam = new OrderShoppingQueryParam();
queryParam.setPs(1000000);
queryParam.setOrderId(orderId);
List<OrderShopping> list = orderShoppingMapper.listQuery(queryParam);
//指定模板的路径，webapp下的update文件夹放的文件
String tempPath = request.getSession().getServletContext().getRealPath("/") + "upload/" + "eng_model.xlsx";
//指定生成文件的路径
String path = request.getSession().getServletContext().getRealPath("/") + "upload/";
//下面的是util包下的自己写的方法
ExcelUtils ex = new ExcelUtils();
ex.exportExcel(tempPath, path, response, list);
return null;
mybatis的查询省略不写了。
下面是util下的内容
import com.navi.user.dao.OrderShoppingMapper;
import com.navi.user.entity.OrderShopping;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;
@Component
public class ExcelUtils {
//声明一个该工具类的静态的内部对象
private static ExcelUtils excelUtils;
/**
*tempPath 模板文件路径
*path 文件路径
*list 集合数据
*/
public void exportExcel(String tempPath, String path, HttpServletResponse response, List<OrderShopping> list) {
//创建一个新的file
File newFile = createNewFile(tempPath, path);
InputStream is = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
workbook = new XSSFWorkbook(is);// 创建个workbook，
/* Row borderRow = sheet.createRow(2);
Cell borderCell = borderRow.createCell(1);*/
// 获取第一个sheet
sheet = workbook.getSheetAt(0);
} catch (Exception e1) {
e1.printStackTrace();
}
if (sheet != null) {
try {
// 写数据
FileOutputStream fos = new FileOutputStream(newFile);
XSSFRow row = sheet.getRow(0);
if (row == null) {
row = sheet.createRow(0);
}
XSSFCell cell = row.getCell(0);
if (cell == null) {
cell = row.createCell(0);
}
for (int i = 0; i < list.size(); i++) {
//我的模板是需要在第八行开始写，注意Excel的行跟列都是从下标0开始
row = sheet.createRow(i+7); //从第8行开始
//根据excel模板格式写入数据....
//编码
createRowAndCell(list.get(i).getCode(), row, cell, 1,workbook);
//名称
createRowAndCell(list.get(i).getShoppingName(), row, cell, 2,workbook);
//件数
createRowAndCell(list.get(i).getNumber(), row, cell, 3,workbook);
//单位
createRowAndCell(list.get(i).getUnit(), row, cell, 4,workbook);
//单价
createRowAndCell(list.get(i).getPrice(), row, cell, 5,workbook);
createRowAndCell(list.get(i).getNumber()*Integer.parseInt(list.get(i).getPrice()), row, cell, 6,workbook);
}
workbook.write(fos);
fos.flush();
fos.close();
// 下载
InputStream fis = new BufferedInputStream(new FileInputStream(
newFile));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
response.reset();
response.setContentType("text/html;charset=UTF-8");
OutputStream toClient = new BufferedOutputStream(
response.getOutputStream());
response.setContentType("application/x-msdownload");
String newName = URLEncoder.encode(
"in_voice"+ ".xlsx",
"UTF-8");
response.addHeader("Content-Disposition",
"attachment;filename=\"" + newName + "\"");
response.addHeader("Content-Length", "" + newFile.length());
toClient.write(buffer);
toClient.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != is) {
is.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
// 删除创建的新文件
this.deleteFile(newFile);
}
/**
*根据当前row行，来创建index标记的列数,并赋值数据
*/
private void createRowAndCell(Object obj, XSSFRow row, XSSFCell cell, int index,XSSFWorkbook workbook) {
cell = row.getCell(index);
if (cell == null) {
cell = row.createCell(index);
//设置边框
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cell.setCellStyle(cellStyle);
}
if (obj != null)
cell.setCellValue(obj.toString());
else
cell.setCellValue("");
}
/**
* 复制文件
*
* @param s
* 源文件
* @param t
* 复制到的新文件
*/
public void fileChannelCopy(File s, File t) {
try {
InputStream in = null;
OutputStream out = null;
try {
in = new BufferedInputStream(new FileInputStream(s), 1024);
out = new BufferedOutputStream(new FileOutputStream(t), 1024);
byte[] buffer = new byte[1024];
int len;
while ((len = in.read(buffer)) != -1) {
out.write(buffer, 0, len);
}
} finally {
if (null != in) {
in.close();
}
if (null != out) {
out.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取excel模板，并复制到新文件中供写入和下载
*
* @return
*/
public File createNewFile(String tempPath, String rPath) {
// 读取模板，并赋值到新文件
// 文件模板路径
String path = (tempPath);
File file = new File(path);
// 保存文件的路径
String realPath = rPath;
// 新的文件名，导出的时候的文件名称
String newFileName = "in_voice" + ".xlsx";
// 判断路径是否存在
File dir = new File(realPath);
if (!dir.exists()) {
dir.mkdirs();
}
// 写入到新的excel
File newFile = new File(realPath, newFileName);
try {
newFile.createNewFile();
// 复制模板到新文件
fileChannelCopy(file, newFile);
} catch (Exception e) {
e.printStackTrace();
}
return newFile;
}
/**
* 下载成功后删除
*
* @param files
*/
private void deleteFile(File... files) {
for (File file : files) {
if (file.exists()) {
file.delete();
}
}
}
}
到此下载就结束了。
扫一扫
2729
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
