1.引入java POI依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
2.读取Excel文件内容
file为Excel文件名,index为Excel中工作表顺序(从0开始),IO流读写文件后必须关闭流,不然会出现内存溢出等问题。
此处使用XSSFWorkBook 对象,Excel文件格式为 .xlsx,读取.xls格式的换成HSSFWorkbook 即可
Excel文件内容存放在list集合中,对list集合进行下一步操作即可。
public List<Object[]> readForeignExcel(File file, Integer index) {
ArrayList<Object[]> list = Lists.newArrayList();
FileInputStream fis = null;
try {
fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheetAt(index);
//第一行为标题
int firstRowIndex = sheet.getFirstRowNum() + 1; //第1行是列名,不读
int lastRowIndex = sheet.getLastRowNum();
for (int i = firstRowIndex; i <= lastRowIndex; i++) {
Object[] obj = new Object[5];
Row row = sheet.getRow(i);
if (row != null) {
obj[0] = (row.getCell(0).toString().trim());
list.add(obj);
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (fis != null) {
fis.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return list;
}
3.写入并下载Excel文件
try {
String desc = Category.valueOf(category).getDesc();
//Excel 表名
String fileName = bname+ desc + name + "gdi" + "-" + format + "-"+ ".xls";
ByteArrayOutputStream os = showManageService.getInfoExcel(name,category,sta,date);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
ServletOutputStream out = response.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (IOException e) {
return "失败";
} finally {
try {
if (bis != null)
bis.close();
if (bos != null) ;
bos.close();
} catch (Exception e) {
return "in error";
}
}
name,category,sta等都是数据库查询条件,可自行更改。
public ByteArrayOutputStream getInfoExcel(String name,String category,Integer sta,Date day) throws IOException {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
List<Show> shows = Lists.newArrayList();
if (sta == 0) {
shows = showRepo.findByCategoryAndOfflineDateAndReleaseStatus(category);
}
if (sta == 1 || sta == 2 || sta == 3) {
shows = showRepo.findByCategoryAndReleaseStatus(category, sta);
}
List<Integer> ids = shows.stream().map(i -> i.getId()).collect(Collectors.toList());
HSSFWorkbook wb = new HSSFWorkbook();
String sheetName = "信息表";
HSSFSheet sheet = wb.createSheet(sheetName);
String[] title = "a","b","c","d"}; //列名
//获取表头行
HSSFRow titleRow = sheet.createRow(0);
//创建单元格,设置style居中,字体,单元格大小等
CellStyle style = wb.createCellStyle();
Cell cell = null;
//把已经写好的标题行写入excel文件中
for (int i = 0; i < title.length; i++) {
cell = titleRow.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//设置单元格宽度自适应,在此基础上把宽度调至1.5倍
for (int i = 0; i < title.length; i++) {
sheet.autoSizeColumn(i, true);
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 15 / 10);
}
//数据库查询结果 作为Excel内容
List<ShowLogs> showList = Lists.newArrayList();
showList = this.findByCategoryAndDayAndShowId(category, day,ids);
//把从数据库中取得的数据一一写入excel文件中
HSSFRow data = null;
for (int i = 0; i < showList .size(); i++) {
//创建list.size()行数据
data = sheet.createRow(i+1);
//把值一一写进单元格里
//设置第一列为自动递增的序号
data.createCell(0).setCellValue(showList .get(i).getxxxx());
data.createCell(1).setCellValue(showList .get(i).getxxxx());
data.createCell(2).setCellValue(showList .get(i).getxxxx());
data.createCell(3).setCellValue(showList .get(i).getxxxx());
data.createCell(4).setCellValue(showList .get(i).getxxxx());
//把时间转换为指定格式的字符串再写入excel文件中
if (showList.get(i).getDay() != null) {
data.createCell(5).setCellValue(sdf.format(showList.get(i).getDay()));
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
return os;
}