JAVA导出excel方法

来源：互联网发布：淘宝网升级企业店铺编辑：程序博客网时间：2024/06/03 09:03

JAVA导出excel方法

ExcelUtil导出Excel文档工具类

import java.io.BufferedInputStream;import java.io.BufferedOutputStream;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.IOException;import java.io.InputStream;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import java.util.Map;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.DataFormat;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;/** * 导出Excel文档工具类 * [@author] ghs * [@date] 2017-5-8 * */public class ExcelUtil {     /**     * 创建excel文档，     * [@param] list 数据     * @param keys list中map的key数组集合     * @param columnNames excel的列名     * */    public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) {        // 创建excel工作簿        Workbook wb = new HSSFWorkbook();        // 创建第一个sheet（页），并命名        Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。        for(int i=0;i<keys.length;i++){            sheet.setColumnWidth((short) i, (short) (35.7 * 150));        }        // 创建第一行        Row row = sheet.createRow((short) 0);        // 创建两种单元格格式        CellStyle cs = wb.createCellStyle();        CellStyle cs2 = wb.createCellStyle();        DataFormat format = wb.createDataFormat();        // 创建两种字体        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);        cs2.setDataFormat(format.getFormat("yyyy年MM月dd日"));        //设置列名        for(int i=0;i<columnNames.length;i++){            Cell cell = row.createCell(i);            cell.setCellValue(columnNames[i]);            cell.setCellStyle(cs);        }        //设置每行每列的值        for (short i = 1; i < list.size(); i++) {            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的            // 创建一行，在页sheet上            Row row1 = sheet.createRow((short) i);            // 在row行上创建一个方格            for(short j=0;j<keys.length;j++){                Cell cell = row1.createCell(j);                Object value = list.get(i).get(keys[j]);                if(value instanceof Date){                    Date date = (Date) value;                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");                    cell.setCellValue(value == null?" ": sdf.format(date));                }                else{                    cell.setCellValue(value == null?" ": value.toString());                }                cell.setCellStyle(cs2);            }        }        return wb;    }    public static void excleExport(HttpServletRequest request,HttpServletResponse response,            ByteArrayOutputStream os,String fileName) throws IOException{         byte[] content = os.toByteArray();         InputStream is = new ByteArrayInputStream(content);         // 设置response参数，可以打开下载页面         response.reset();         response.setContentType("application/vnd.ms-excel;charset=utf-8");         response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName + ".xls").getBytes(), "iso-8859-1"));         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();         }    }}

serviceImpl类业务处理

//导出并下载到excel    @Override    public String downloadExcel(HttpServletRequest request,            HttpServletResponse response,BusinessVo businessVo) throws IOException{        String fileName="excel文件";        Map<String, Object> map = commonService1.beansToMap(businessVo);        //填充projects数据        //获取所需数据        List<BusinessVo> projects=businessdao.getBusRank(map);        List<Map<String,Object>> list=createExcelRecord(projects);//定义需要导出的excel的数据        String columnNames[]={"业务ID","业务名称","平均处理时长","团队优秀处理时长"};//列名        String keys[]    =     {"busId","busName","handTime","minHandTime"};//map中的key        ByteArrayOutputStream os = new ByteArrayOutputStream();        try {            ExcelUtil.createWorkBook(list,keys,columnNames).write(os);        } catch (IOException e) {            e.printStackTrace();        }        ExcelUtil.excleExport(request,response,os,fileName);        return null;    }    //定义需要导出的excel的数据    private List<Map<String, Object>> createExcelRecord(List<BusinessVo> businessVo) {        List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();        Map<String, Object> map = new HashMap<String, Object>();        map.put("sheetName", "sheet1");        listmap.add(map);        BusinessVo business=null;        for (int j = 0; j < businessVo.size(); j++) {            business=businessVo.get(j);            Map<String, Object> mapValue = new HashMap<String, Object>();            mapValue.put("busId", business.getBusId());            mapValue.put("busName", business.getBusName());            mapValue.put("handTime", business.getHandTime());            mapValue.put("minHandTime", business.getMinHandTime());            listmap.add(mapValue);        }        return listmap;    }

阅读全文

0 0