技术汇总:第一章:使用poi实现表单下载成xls文件并打印

业务需求:

点击下载

第一种方式:

实现代码

      @RequestMapping("/ad/downExcel")
      public String downExcel(HttpSession session, HttpServletResponse response) {
          try {
              List<Ad> adlist = adService.getAdList();
              String fileName="广告管理表";
              List<Map<String,Object>> list=createExcelRecord(adlist);
              String columnNames[]={"标题","链接","权重"};//列名
              String keys[]    =     {"title","link","weight"};//map中的key
              ByteArrayOutputStream os = new ByteArrayOutputStream();
              try {
                  ExcelUtil.createWorkBook(list,keys,columnNames).write(os);
              } catch (IOException e) {
                  e.printStackTrace();
              }
              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();
              }
          }catch (Exception e){
     
          }
          return null;
      }
     
      private List<Map<String, Object>> createExcelRecord(List<Ad> adList) {
          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);
          Map<String, Object> mapValue = null;
          for (int j = 0; j < adList.size(); j++) {
              Ad ad=adList.get(j);
              mapValue = new HashMap<String, Object>();
              mapValue.put("title", ad.getTitle());
              mapValue.put("link",ad.getLink());
              mapValue.put("weight",ad.getWeight());
              listmap.add(mapValue);
          }
          return listmap;
      }

工具类

package org.imooc.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.util.List;
import java.util.Map;

public class ExcelUtil {
    /**
     * 创建excel文档,
     *  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 * 200));
        }

        // 创建第一行
        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());

//        Font f3=wb.createFont();
//        f3.setFontHeightInPoints((short) 10);
//        f3.setColor(IndexedColors.RED.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<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);
                cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString());
                cell.setCellStyle(cs2);
            }
        }
        return wb;
    }
}

第二种方式:

先准备好一个空的名称为new的xls文件(名称和代码一致)

核心代码:

      @RequestMapping("downExcel")
        public void download() throws IOException{
            List<Ad> adList = adService.getAdList();
            String filePath = "D:\\new.xls";
            File file = new File(filePath);
            //事先要准备好一个名称为new的xls文件
            InputStream  is=new FileInputStream(file);
            Workbook wb=new HSSFWorkbook(is);
            Sheet sheet = wb.getSheetAt(0);
            Row nRow=null;
            Cell nCell=null;
            //行号
            int rowNo=0;
            // 列号
            int colNo=1;
            //声明样式对象和字体对象
            CellStyle nStyle=wb.createCellStyle();
            Font font = wb.createFont();
            sheet.setColumnWidth(0,2*300);
            sheet.setColumnWidth(1,26*300);
            sheet.setColumnWidth(2,12*300);
            //大标题,合并单元格
            sheet.addMergedRegion(new CellRangeAddress(0,0,1,3));  //开始行,结束行,开始列,结束列
            // 合并单元格的内容写在合并前第一个单元格中
            nRow=sheet.createRow(rowNo++);
            nRow.setHeightInPoints(36);
            nCell=nRow.createCell(1);
     
            nCell.setCellValue("标题");
            nCell.setCellStyle(this.bigTitle(wb, nStyle, font));
            String[] title=new  String[]{"id","标题","权重"};
            nRow=sheet.createRow(rowNo++);
            nRow.setHeightInPoints(26.25f);
            //初始化
            nStyle=wb.createCellStyle();
            font=wb.createFont();
            for(int  i=0;i<title.length;i++){
                nCell=nRow.createCell(i+1);
                nCell.setCellValue(title[i]);
                nCell.setCellStyle(this.Title(wb, nStyle, font));
            }
            //初始化
            nStyle=wb.createCellStyle();
            font=wb.createFont();
            for (int i = 0; i < adList.size(); i++) {
                Ad ad = adList.get(i);
                colNo=1;
                nRow=sheet.createRow(rowNo++);
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(ad.getId());
                nCell.setCellStyle(this.Text(wb, nStyle, font));
     
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(ad.getTitle());
                nCell.setCellStyle(this.Text(wb, nStyle, font));
     
                nCell=nRow.createCell(colNo++);
                nCell.setCellValue(ad.getWeight());
                nCell.setCellStyle(this.Text(wb, nStyle, font));
            }
            OutputStream  os=new FileOutputStream(file);
            wb.write(os);
            os.flush();
            os.close();
        }

各种样式代码

     //大标题的样式
        public CellStyle bigTitle(Workbook wb,CellStyle nStyle,Font font){
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 16);
            //字体加粗
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            //横向居中
            nStyle.setAlignment(CellStyle.ALIGN_CENTER);
            //纵向居中
            nStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            //单元格垂直居中
            nStyle.setFont(font); return nStyle;
        }
            //标题样式
        public CellStyle Title(Workbook wb,CellStyle nStyle,Font font){
            font.setFontName("黑体");
            font.setFontHeightInPoints((short) 12);
            //横向居中
            nStyle.setAlignment(CellStyle.ALIGN_CENTER);
            //纵向居中
            nStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//单元格垂直居中
            //表格线
            nStyle.setBorderTop(CellStyle.BORDER_THICK);
            //粗实线
           nStyle.setBorderBottom(CellStyle.BORDER_THIN);
            // 实线
            nStyle.setBorderLeft(CellStyle.BORDER_THIN);
            // 比较粗实线
            nStyle.setBorderRight(CellStyle.BORDER_THIN);
            // 实线
            nStyle.setFont(font); return nStyle;
     
        }
            // 文字样式
            public CellStyle Text(Workbook wb,CellStyle nStyle,Font font){
                font.setFontName("Times New Roman");
                font.setFontHeightInPoints((short) 10);
                //横向居中
                nStyle.setAlignment(CellStyle.ALIGN_CENTER);
                //纵向居中
                nStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);     //单元格垂直居中
                // 表格线
                nStyle.setBorderBottom(CellStyle.BORDER_THIN);
                //实线
                nStyle.setBorderLeft(CellStyle.BORDER_THIN);
                // 比较粗实线
                nStyle.setBorderRight(CellStyle.BORDER_THIN);
                // 实线
                nStyle.setFont(font); return nStyle;
            }

完成下载去原来的new.xls文件查看