java下载导出excel
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency>
通用读写excel工具类
import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; /** * POI解析Excel */ public class ExcelReaderUtil { /** * 根据fileType不同读取excel文件 * * @param path * @param path * @throws IOException */ public static List<List<String>> readExcel(String path) { String fileType = path.substring(path.lastIndexOf(".") + 1); // return a list contains many list List<List<String>> lists = new ArrayList<List<String>>(); //读取excel文件 InputStream is = null; try { is = new FileInputStream(path); //获取工作薄 Workbook wb = null; if (fileType.equals("xls")) { wb = new HSSFWorkbook(is); } else if (fileType.equals("xlsx")) { wb = new XSSFWorkbook(is); } else { return null; } //读取第一个工作页sheet Sheet sheet = wb.getSheetAt(0); //第一行为标题 for (Row row : sheet) { ArrayList<String> list = new ArrayList<String>(); for (Cell cell : row) { //根据不同类型转化成字符串 cell.setCellType(Cell.CELL_TYPE_STRING); list.add(cell.getStringCellValue()); } lists.add(list); } } catch (IOException e) { e.printStackTrace(); } finally { try { if (is != null) is.close(); } catch (IOException e) { e.printStackTrace(); } } return lists; } /** * 创建Excel.xls * @param lists 需要写入xls的数据 * @param titles 列标题 * @param name 文件名 * @return * @throws IOException */ public static Workbook creatExcel(List<List<String>> lists, String[] titles, String name) throws IOException { System.out.println(lists); //创建新的工作薄 Workbook wb = new HSSFWorkbook(); // 创建第一个sheet(页),并命名 Sheet sheet = wb.createSheet(name); // 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。 for(int i=0;i<titles.length;i++){ sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // 创建第一行 Row row = sheet.createRow((short) 0); // 创建两种单元格格式 CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // 创建两种字体 Font f = wb.createFont(); Font f2 = wb.createFont(); // 创建第一种字体样式(用于列名) f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // 创建第二种字体样式(用于值) f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // 设置第一种单元格的样式(用于列名) cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // 设置第二种单元格的样式(用于值) cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //设置列名 for(int i=0;i<titles.length;i++){ Cell cell = row.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(cs); } if(lists == null || lists.size() == 0){ return wb; } //设置每行每列的值 for (short i = 1; i <= lists.size(); i++) { // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的 // 创建一行,在页sheet上 Row row1 = sheet.createRow((short)i); for(short j=0;j<titles.length;j++){ // 在row行上创建一个方格 Cell cell = row1.createCell(j); cell.setCellValue(lists.get(i-1).get(j)); cell.setCellStyle(cs2); } } return wb; } public static void main(String[] args) { String path = "d:/software/test.xlsx"; // List<List<String>> lists = readExcel(path); // for (List<String> list : lists) { // for (String strs : list) { // System.out.println(strs); // } // } } }
导出公共方法
/** * @Description:excel导出公共方法 * @Author:SimonHu * @Date: 2019/11/19 16:51 * @param response 返回流 * @param lists 数据 * @param titles 列标题 * @param sheetName 页名 * @param fileName 文件名 * @return void */ public void exportExcelCommon(HttpServletResponse response, List<List<String>> lists,String[] titles, String sheetName, String fileName){ try { /*获取Excel输出流*/ ByteArrayOutputStream os = new ByteArrayOutputStream(); ExcelReaderUtil.creatExcel(lists, titles, sheetName).write(os); 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="+ new String((fileName+new Date().getTime() + ".xls").getBytes(), "iso-8859-1")); /*Make a InputStream And a OutputStream with buffered*/ ServletOutputStream out = response.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(is); 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 (final IOException e) { throw e; } finally { if (bis != null) bis.close(); if (bos != null) bos.close(); } } catch (IOException e) { logger.error("导出excel异常---",e); } }
导出service层,数据组装
public void exportCountGoldCoinConsume(HttpServletResponse response, String beginTime, String endTime) { String sheetName = "金币消费统计"; List<Map<String, Object>> orderList = goldCoinConsumeMapper.countConsumeByTime(beginTime, endTime); /*数据组装*/ List<List<String>> lists = new ArrayList<List<String>>(); String[] titles = {"订单笔数", "金币个数", "人数"}; for (int i = 0; i < orderList.size(); i++) { List list = new ArrayList(); Map<String, Object> orderMap = orderList.get(i); list.add(0, orderMap.get("singlecount") != null ? orderMap.get("singlecount").toString() : ""); list.add(1, orderMap.get("amountgoldcoin") != null ? orderMap.get("amountgoldcoin").toString() : ""); list.add(2, orderMap.get("usercount") != null ? orderMap.get("usercount").toString() : ""); lists.add(list); } commonService.exportExcelCommon(response, lists, titles, sheetName, sheetName); }
导出controller层
/** * @Description:金币消费统计 * @Author:SimonHu * @Date: 2019/11/19 17:15 * @param * @return */ @RequestMapping("/exportCountGoldCoinConsume") public void exportCountGoldCoinConsume(HttpServletResponse response,@RequestParam(value = "begin_time", required = true) String begin_time, @RequestParam(value = "end_time", required = true) String end_time) { String beginTime = begin_time + " 00:00:00"; String endTime = end_time + " 23:59:59"; goldCoinConsumeService.exportCountGoldCoinConsume( response,beginTime, endTime); }
下载按钮
<input type="button" value="导出" class="easyui-linkbutton c4" style="width:90px;height: 26px" onclick="Download()"/> function Download() { console.log(downType) var time1 = $("#time1").datebox('getValue'); var time2 = $("#time2").datebox('getValue'); if(time1=='' || time2 == ''){ alert("统计时间不能为空!"); return; } var param = 'begin_time='+time1 +'&end_time='+time2; if(downType==1){ window.open("/goldCoinConsume/exportCountBuyGoldCoin?" + param,"_blank"); }else if(downType==2){ window.open("/goldCoinConsume/exportCountGoldCoinConsume?" + param,"_blank"); } }
导出样式
补充:
//对已经生成好的work文件进行样式修改 public static void main(String[] args) throws IOException { String path = "d:/software"; List<String> list = new ArrayList<>(); List<String> list2 = new ArrayList<>(); list.add("Simon"); list.add("20"); list.add("男"); list2.add("HHH"); list2.add("19"); list2.add("女"); List<List<String>> lists = new ArrayList<>(); lists.add(list); lists.add(list2); Workbook workbook = new HSSFWorkbook(); creatExcel(workbook, lists, new String[]{"姓名", "年龄", "性别"}, "test"); creatExcel(workbook, lists, new String[]{"姓名2", "年龄2", "性别2"}, "test2"); int i = 2;//sheet页数量 int titlesSize = 3;//titile数量 File file = new File(path); if (!file.exists()) { file.mkdir(); } FileOutputStream output = new FileOutputStream(path + "/test.xlsx"); for (int y = 0; y < i; y++) { Sheet sheetAt = workbook.getSheetAt(y); // 创建两种单元格格式 CellStyle cs = workbook.createCellStyle(); //将标题左对齐 cs.setAlignment(CellStyle.ALIGN_LEFT); HSSFRow row = (HSSFRow) sheetAt.getRow(0); for (int j = 0; j < titlesSize; j++) { HSSFCell cell = row.getCell(j); cell.setCellStyle(cs); } } workbook.write(output); output.flush(); }
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步