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();
    }
复制代码

 

posted @   748573200000  阅读(54)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示