废话不多说，直接上代码
1.pom.xml中添加依赖
1 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> 2 <dependency> 3 <groupId>org.apache.poi</groupId> 4 <artifactId>poi</artifactId> 5 <version>3.17</version> 6 </dependency> 7 8 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> 9 <dependency> 10 <groupId>org.apache.poi</groupId> 11 <artifactId>poi-ooxml</artifactId> 12 <version>3.17</version> 13 </dependency>
2.创建工具类
1 package com.ieou.capsule.util; 2 3 import org.apache.poi.hssf.usermodel.*; 4 import org.apache.poi.ss.usermodel.HorizontalAlignment; 5 6 import static org.apache.poi.ss.usermodel.VerticalAlignment.CENTER; 7 8 public class ExcelUtil { 9 10 /** 11 * 导出Excel 12 * @param sheetName sheet名称 13 * @param title 标题 14 * @param values 内容 15 * @param wb HSSFWorkbook对象 16 * @return 17 */ 18 public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values, int listSize, HSSFWorkbook wb){ 19 20 // 第一步，创建一个HSSFWorkbook，对应一个Excel文件 21 if(wb == null){ 22 wb = new HSSFWorkbook(); 23 } 24 25 // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet 26 HSSFSheet sheet = wb.createSheet(sheetName); 27 28 sheet.setColumnWidth(0,20 * 256);// 设置第一行为30个字符 29 sheet.setColumnWidth(1,25 * 256);// 设置第一行为30个字符 30 sheet.setColumnWidth(3,20 * 256);// 设置第一行为30个字符 31 sheet.setColumnWidth(6,13 * 256);// 设置第一行为30个字符 32 33 34 // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制 35 HSSFRow row = sheet.createRow(0); 36 37 // 第四步，创建单元格，并设置值表头 设置表头居中 38 HSSFCellStyle style = wb.createCellStyle(); 39 // style.setAlignment(CellStyle); // 创建一个居中格式 40 style.setVerticalAlignment(CENTER); //垂直 41 style.setAlignment(HorizontalAlignment.CENTER); 42 43 //声明列对象 44 HSSFCell cell = null; 45 46 //创建标题 47 for(int i=0;i<title.length;i++){ 48 cell = row.createCell(i); 49 cell.setCellValue(title[i]); 50 cell.setCellStyle(style); 51 52 } 53 54 //创建内容 55 for(int i=0;i<listSize;i++){ 56 row = sheet.createRow(i + 1); 57 for(int j=0;j<values[i].length;j++){ 58 //将内容按顺序赋给对应的列对象 59 row.createCell(j).setCellValue(values[i][j]); 60 } 61 } 62 return wb; 63 } 64 }
3.业务代码
1 /** 2 * 领取记录导出excel 3 * @return 4 * @throws Exception 5 */ 6 @Override 7 public String exportExcel(List<Integer> receiveRecordIdList) throws Exception { 8 //获取数据 9 ReceiveRecordExample receiveRecordExample = new ReceiveRecordExample(); 10 receiveRecordExample.or().andIdIn(receiveRecordIdList); 11 List<ReceiveRecord> list = receiveRecordMapper.selectByExample(receiveRecordExample); 12 13 //标题 14 String [] title = {"时间","公司","物料编号","商品名称","价格","领取人","手机号","数量"}; 15 //excel文件名 16 SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd HH:mm:ss SSS"); 17 String format = sdf.format(new Date()); 18 String fileName = "领取记录表"+format+".xls"; 19 //sheet名 20 String sheetName = "领取记录表"; 21 22 sdf = new SimpleDateFormat("YYYY-MM-dd HH:mm:ss"); 23 24 // String [][] content = new String[list.size() > title.length ? list.size() : title.length][]; 25 26 String [][] content = new String[list.size()][title.length]; 27 28 for (int i = 0; i < list.size(); i++) { 29 //定义每行有几列 30 // content[i] = new String[list.size() > title.length ? list.size() : title.length]; 31 32 ReceiveRecord obj = list.get(i); 33 content[i][0] = sdf.format(obj.getCreateTime()); 34 content[i][1] = obj.getCompanyName(); 35 content[i][2] = obj.getGoodsNo(); 36 content[i][3] = obj.getGoodsName(); 37 content[i][4] = obj.getPrice().toString(); 38 content[i][5] = obj.getName(); 39 content[i][6] = obj.getMobilePhone(); 40 content[i][7] = obj.getNum().toString(); 41 } 42 43 //创建HSSFWorkbook 44 HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, list.size(),null); 45 46 ByteArrayOutputStream stream = new ByteArrayOutputStream(); 47 wb.write(stream); 48 49 String upload = qiNiuService.upload(stream,fileName); 50 return upload; 51 }