springmvc实现excel文件导出
记一次springmvc实现excel文件导出，代码直接复制简单修改即可用。
第一步：excel pom依赖包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
第二步：controller层
// 导出excel相关
@RequestMapping("/orderStaticExcel")
public void excelOrderStatic(@RequestBody FinanceReportsCommonRequest financeReportsCommonRequest, HttpServletResponse response) throws IOException {
// 设置response参数，可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(("订单统计" + ".xls").getBytes(), StandardCharsets.ISO_8859_1));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(new ByteArrayInputStream(omsReportStatisticsService.excelByte(financeReportsCommonRequest)));
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (IOException e) {
logger.error("出现其他异常：", e);
} finally {
if (bis != null) {
bis.close();
}
if (bos != null) {
bos.close();
}
}
}
第三步：接口层
public interface OmsReportStatisticsService {
byte[] excelByte(FinanceReportsCommonRequest financeReportsCommonRequest);
}
第四步：接口实现层
@Service("omsReportStatisticsServiceImpl")
public class OmsReportStatisticsServiceImpl implements OmsReportStatisticsService {
private static final Logger logger = LoggerFactory.getLogger(OmsReportStatisticsServiceImpl.class);
@Override
public byte[] excelByte(FinanceReportsCommonRequest financeReportsCommonRequest) {
ByteArrayOutputStream baos = null;
try {
// excel标题
String[] title = {"订单号", "客户手机号", "支付日期", "实际支付日期", "期数", "应付租金", "已付租金", "应付增值费用","实付增值费用", "应付保险金额", "实付保险金额", "应付运费", "实付运费", "折扣金额", "实际折扣金额", "应付其他费用", "实付其他费用","应减免金额", "实际减免金额", "应付罚息", "实付罚息", "应付押金", "实付押金", "押金转租金", "应付买断费", "实付买断费","用户已支付（不包含押金）", "用户已支付（包含押金）"};
// 列的宽度
int[] length = {
20, 20, 20, 20, 20, 20, 20,
20, 20, 20, 20, 20, 20, 20,
20, 20, 20, 20, 20, 20, 20,
20, 20, 20, 20, 20, 20, 20};
// sheet名
String sheetName = "订单统计报表";
String[][] content = null;
PageInfo<OrderStaticReportResultDTO> pageInfo = this.orderFinanceStaticReport(financeReportsCommonRequest);
content = new String[title.length][title.length];
for (int i = 0; i < pageInfo.getList().size(); i++) {
OrderStaticReportResultDTO repayBalance = pageInfo.getList().get(i);
content[i][0] = repayBalance.getOrderNo();
content[i][1] = repayBalance.getMobilePhoneNo();
content[i][2] = repayBalance.getPayDate();
content[i][3] = repayBalance.getFinishDate();
content[i][4] = repayBalance.getCnt().toString();
content[i][5] = repayBalance.getPayRentAmt().toString();
content[i][6] = repayBalance.getActualPayRentAmt().toString();
content[i][7] = repayBalance.getPayRaiseAmt().toString();
content[i][8] = repayBalance.getActualPayRaiseAmt().toString();
content[i][9] = repayBalance.getPayInsureAmt().toString();
content[i][10] = repayBalance.getActualPayInsureAmt().toString();
content[i][11] = repayBalance.getPayLogisticsAmt().toString();
content[i][12] = repayBalance.getActualPayLogisticsAmt().toString();
content[i][13] = repayBalance.getPayDiscountAmt().toString();
content[i][14] = repayBalance.getActualPayDiscountAmt().toString();
content[i][15] = repayBalance.getPayOtherAmt().toString();
content[i][16] = repayBalance.getActualPayOtherAmt().toString();
content[i][17] = repayBalance.getPayReductionAmt().toString();
content[i][18] = repayBalance.getActualPayReductionAmt().toString();
content[i][19] = repayBalance.getPayFineAmt().toString();
content[i][20] = repayBalance.getActualPayFineAmt().toString();
content[i][21] = repayBalance.getPayDepositAmt().toString();
content[i][22] = repayBalance.getActualPayDepositAmt().toString();
content[i][23] = repayBalance.getLimitDepositAmt().toString();
content[i][24] = repayBalance.getBuyoutAmt().toString();
content[i][25] = repayBalance.getActualBuyoutAmt().toString();
content[i][26] = repayBalance.getCustActualExcludeDepositAmt().toString();
content[i][27] = repayBalance.getCustActualIncludeDepositAmt().toString();
}
logger.info("to there !");
// 创建HSSFWorkbook
HSSFWorkbook wb = ExcelUtils.getHSSFWorkbook(sheetName, title, content, null, length);
baos = new ByteArrayOutputStream();
wb.write(baos);
logger.info("生成文件");
baos.flush();
// return new ByteArrayInputStream(baos.toByteArray());
return baos.toByteArray();
} catch (Exception e) {
logger.error("generateStreamByTrans err", e);
} finally {
if (baos != null) {
try {
baos.close();
} catch (IOException e) {
logger.error("close baos err", e);
}
}
}
return null;
}
}
第五、ExcelUtils工具类
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.util.List;
public class ExcelUtils {
/**
* 导出Excel
*
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @param wb HSSFWorkbook对象
* @return HSSFWorkbook
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb, int[] length) {
// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
}
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(HSSFColor.WHITE.index);
// 创建一个居中格式
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFCellStyle cellStyle = wb.createCellStyle();
// 创建一个居中格式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//声明列对象
HSSFCell cell = null;
//创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(titleStyle);
sheet.setColumnWidth(i, length[i] * 256);
}
if (values != null && values.length > 0) {
//创建内容
for (int i = 0; i < values.length; i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < values[i].length; j++) {
//将内容按顺序赋给对应的列对象
cell = row.createCell(j);
cell.setCellValue(values[i][j]);
cell.setCellStyle(cellStyle);
}
}
}
return wb;
}
/**
* 导出多个sheet的Excel表
*
* @param sheetNames sheet名称
* @param titles 标题
* @param values 内容
* @param wb HSSFWorkbook对象
* @return
*/
public static HSSFWorkbook getHSSFWorkbookWithMoreSheet(List<String> sheetNames, List<String[]> titles, List<String[][]> values, HSSFWorkbook wb, List<int[]> lengths) {
// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
}
for (int m = 0; m < sheetNames.size(); m++) {
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetNames.get(m));
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
HSSFRow englishRow = sheet.createRow(1);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
//声明列对象
HSSFCell cell = null;
int[] length = lengths.get(m);
String[] title = titles.get(m);
String[] englishTitle = titles.get(m + 2);
//创建标题
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(titleStyle);
sheet.setColumnWidth(i, length[i] * 256);
}
//创建标题
for (int i = 0; i < englishTitle.length; i++) {
cell = englishRow.createCell(i);
cell.setCellValue(englishTitle[i]);
cell.setCellStyle(titleStyle);
sheet.setColumnWidth(i, length[i] * 256);
}
if (values != null) {
String[][] value = values.get(m);
//创建内容
if (value != null && value.length > 0) {
for (int i = 0; i < value.length; i++) {
row = sheet.createRow(i + 2);
for (int j = 0; j < value[i].length; j++) {
//将内容按顺序赋给对应的列对象
cell = row.createCell(j);
cell.setCellValue(value[i][j]);
cell.setCellStyle(cellStyle);
}
}
}
}
}
return wb;
}
/**
* @Author jason
* @Description 发送响应流方法
* @UpdateRemark: 修改内容
* @Param [response, fileName]
**/
public static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
