1.实现流程
使用POI对Excel文件进行读写操作,同时需要导入poi-3.17.jar包。
2.代码实现
此处介绍xls格式的文件写操作,实现Excel的下载操作。首先,创建工作簿对象,然后对工作簿进行操作,将从数据库中读取的数据写入工作簿,并将完成写操作的工作簿写入输出流,通过前台浏览器下载页面进行下载。
2.1 前台JS调用:
location.href=url;//URL指向下载资源(此处为目标文件的输出数据流)
2.2 Controller层操作:
@Autowired
private HttpServletResponse response;
@Autowired
private ProductService productService;
/**
*
* @param 导出产品信息Excel
* @param yp
* @return
*/
@ResponseBody
@RequestMapping(value = "/expExcel")
public Boolean expExcel() {
Boolean flag;
flag = productService.EXPExcel(response);
return flag;
}
2.3 Service层Excel操作:
ProductService.java文件:
@Autowired
private ProductMapper productMapper;
@Autowired
private ProductTypeMapper productTypeMapper;
@Autowired
private SubmissionUnitMapper submissionUnitMapper;
@Autowired
private CommonService commonService;
/**
*
* @param 产品信息导出Excel
* @param yp
* @return
*/
public Boolean EXPExcel(HttpServletResponse response) {
List < Product > list = new ArrayList < Product > ();
list = productMapper.queryAll();
Boolean flag = true;
HSSFWorkbook wb = null; // 定义工作簿
try {
wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("产品信息表");
CellStyle styleT = commonService.setTBStyle(wb); //新建表格标题样式
CellStyle style = commonService.setCBStyle(wb); //新建表格内容样式
//增加表格标题
HSSFRow rowT = sheet.createRow(0);
commonService.createCell(wb, rowT, 0, "序号", styleT);
sheet.setColumnWidth(0, 1280); //表格宽度Set the width (in units of 1/256th of a character width)
commonService.createCell(wb, rowT, 1, "产品名称", styleT);
sheet.setColumnWidth(1, 5120);
commonService.createCell(wb, rowT, 2, "产品编号", styleT);
sheet.setColumnWidth(2, 5120);
commonService.createCell(wb, rowT, 3, "委托编号", styleT);
sheet.setColumnWidth(3, 5120);
commonService.createCell(wb, rowT, 4, "产品型号", styleT);
sheet.setColumnWidth(4, 5120);
commonService.createCell(wb, rowT, 5, "样本编号", styleT);
sheet.setColumnWidth(5, 5120);
commonService.createCell(wb, rowT, 6, "送检单位", styleT);
sheet.setColumnWidth(6, 5120);
commonService.createCell(wb, rowT, 7, "生产单位", styleT);
sheet.setColumnWidth(7, 5120);
//写入表格数据
HSSFRow row = null;
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 1); // 从第2行起填充内容,第1行为标题,序号为0
Product product = list.get(i);
commonService.createCell(wb, row, 0, Integer.toString(i + 1), style);
commonService.createCell(wb, row, 1, product.getName(), style);
commonService.createCell(wb, row, 2, product.getNumber(), style);
commonService.createCell(wb, row, 3, product.getCommissionNumber(), style);
commonService.createCell(wb, row, 4, product.getModel(), style);
commonService.createCell(wb, row, 5, product.getSampleNumber(), style);
String subunit = submissionUnitMapper.queryById(product.getSubunitId()).getName();
commonService.createCell(wb, row, 6, subunit, style);
commonService.createCell(wb, row, 7, product.getManufacturer(), style);
}
ByteArrayOutputStream os = new ByteArrayOutputStream(); // 获得输出流
wb.write(os);
os.flush();
os.close();
wb.close();
byte[] content = os.toByteArray();
InputStream fis = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
response.reset();
response.setContentType("application/octet-stream;charset=utf-8");
String filename = URLEncoder.encode("产品信息_" + commonService.getDate() + ".xls", "UTF-8"); // 文件名定义
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
ServletOutputStream servletOutputStream = response.getOutputStream();
byte[] b = new byte[1024 * 1024];
int i = 0;
while ((i = fis.read(b)) > 0) {
servletOutputStream.write(b, 0, i);
}
fis.close();
} catch(IOException e) {
// TODO Auto-generated catch block
System.out.println("下载出错!");
flag = false;
}
return flag;
}
CommonService.java文件:
package com.fc.service;
import java.util.Calendar;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.stereotype.Service;
/**
* 公共方法类
*
* @author yp
*
*/
@Service
public class CommonService {
/**
* 在excel模板中插入一个单元格
*
* @param col 列号,从0开始
* @param val 单元格的值
* @author yp
*
*/
public void createCell(HSSFWorkbook wb, HSSFRow row, int col, String val,CellStyle style) {
HSSFCell cell = row.createCell(col);
cell.setCellStyle(style);//设置表格样式
cell.setCellValue(val);
}
static int k=0;
/**
*
* 获取单元格内容样式
* @author yp
* @return 返回单元格样式
*/
public CellStyle setCBStyle(HSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);//设置文本位置横向居中
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
return style;
}
/**
*
* 获取单元格标题样式
* @author yp
* @return 返回单元格样式
*/
public CellStyle setTBStyle(HSSFWorkbook wb) {
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);//设置文本位置横向居中
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置单元格填充样式否则单元格颜色无效
style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());//设置单元格填充颜色
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
return style;
}
/**
*
* 获取当前日期
* @author yp
* @return 返回当前日期
*/
public String getDate() {
Calendar CD = Calendar.getInstance();
int YY = CD.get(Calendar.YEAR);
int MM = CD.get(Calendar.MONTH) + 1;
int DD = CD.get(Calendar.DATE);
String year = String.valueOf(YY);
String month = String.valueOf(MM);
String day = String.valueOf(DD);
if (month.length() < 2) {
month = "0" + month;
}
if (day.length() < 2) {
day = "0" + day;
}
String today = year + "-" + month + "-" + day;
return today;
}
}