导出Excel具体实现
/**
* 导出Excel
* @param request
* @return
*/
@RequestMapping("/toExportExcel")
@ResponseBody
public ResultJsonInfo reportExcel(ProductOrderQueryParam param, HttpServletRequest request, HttpServletResponse response) throws IOException {
//将需要生成为excel表格的数据查询出来
PageResult<ProductOrderVO> order = this.getBaseService().findPage(param);
List<ProductOrderVO> dataList = order.getDataList();
//定义表头
String[] rowName = { "订单编号", "产品类型", "产品名称", "订单金额", "订单状态", "申请时间", "用户姓名" };
//放入数据
ExcelExportUtils excelUtil = new ExcelExportUtils();/*这里使用的是下面的utils*/
HSSFWorkbook workbook = excelUtil.createNewWorkbook("订单", rowName);//excel表名
if (workbook != null) {
int dataNo = 0;
List<String> excelDataList = new ArrayList<String>();
for (int i = 0; i < dataList.size(); i++) {
ProductOrderVO dataVo = dataList.get(i);
dataNo++;
excelDataList.clear();
//添加数据 订单编号、产品类型、产品名称、订单金额、订单状态、申请时间、用户姓名
excelDataList.add(dataVo.getCode() != null ? dataVo.getCode() + "" : "");//订单编号
excelDataList.add(dataVo.getProductCategory() != null ? dataVo.getProductCategory() + "" : "");//产品类型
excelDataList.add(dataVo.getProductName() != null ? dataVo.getProductName() + "" : "");//产品名称
excelDataList.add(dataVo.getOrderAmount() != null ? dataVo.getOrderAmount() + "" : "");//订单金额 orderAmount
excelDataList.add(dataVo.getOrderState() != null ? dataVo.getOrderState() + "" : "");//订单状态
excelDataList.add(dataVo.getApplicationTime() != null ? DateUtils.formatDateTime(dataVo.getApplicationTime()) + "" : "");//申请时间
excelDataList.add(dataVo.getUserName() != null ? dataVo.getUserName() + "" : "");//用户姓名
excelUtil.genDataRow(dataNo, excelDataList, false);
//每次最多导出10000条记录
if (dataNo >= 10000) {
break;
}
if (dataList.size() > 10000) {
dataNo++;
excelDataList.clear();
excelDataList.add("共" + dataList.size() + "条记录");
excelDataList.add("本次导出10000条");
excelUtil.genDataRow(dataNo, excelDataList, false);
}
}
excelUtil.configColumnWidth(rowName.length + 1);
OutputStream out = null;
try {
//文件名
String fileName = "xxxx-productOrder-Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
String headStr = "attachment; filename=\"" + fileName + "\"";
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
out = response.getOutputStream();
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
} else {
throw new RmtBusinessException(null, "导出异常");
}
return new ResultJsonInfo(true, "导出成功");
}
excel导出通用接口(由于是2016年的时候写的,可能在之后的excel版本中会出现某些不兼容的情况,同时也不保证能兼容之前所有的版本)
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import java.util.List;
/**
* <b>功能:</b>excel导出通用接口<br>
* <b>日期:</b> 2016年6月17日 <br>
*/
public class ExcelExportUtils {
private HSSFWorkbook workbook = null;
private HSSFSheet sheet = null;
private HSSFCellStyle columnTopStyle = null;
private HSSFCellStyle dataStyle = null;
public ExcelExportUtils(){
}
/*
* 生成一个新的workbook
* */
public HSSFWorkbook createNewWorkbook(String title, String[] rowName){
try{
workbook = new HSSFWorkbook(); // 创建工作簿对象
sheet = workbook.createSheet(title); // 创建工作表
// 定义所需列数
int columnNum = rowName.length;
// 产生表格标题行
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTiltle = rowm.createCell(0);
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
columnTopStyle = this.getColumnTopStyle(workbook); //获取列头样式对象
dataStyle = this.getStyle(workbook); //单元格样式对象
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (columnNum-1)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(title);
HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for(int n=0;n<columnNum;n++){
HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text); //设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式
}
}catch(Exception e){
e.printStackTrace();
}
return workbook;
}
//导出每行的数据
public void genDataRow(int dataNo, List<String> columnDataList, boolean needNo) {
HSSFRow row = sheet.createRow(dataNo+2);//创建所需的行数
HSSFCell cell = null; //设置单元格的数据类型
int beginNo =0;
if(needNo){
cell = row.createCell(beginNo,HSSFCell.CELL_TYPE_NUMERIC);
beginNo++;
cell.setCellValue(dataNo);
cell.setCellStyle(dataStyle);
}
for(String data: columnDataList){
cell = row.createCell(beginNo,HSSFCell.CELL_TYPE_STRING);
beginNo++;
if(data != null){
cell.setCellValue(data); //设置单元格的值
}
cell.setCellStyle(dataStyle); //设置单元格样式
}
}
//设置每列的长度
public void configColumnWidth(int columnNum) {
//让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
}
}
}
/*
* 列头单元格样式
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short)11);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/*
* 列数据信息单元格样式
*/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
}
定义的表格