使用poi将数据导出到excel

第一种方式：代码中自定义样式（不推荐）

        //获取数据
        List<OutProductVO> outProductList = this.outProductService.query(inputDate);

        Workbook wb = new HSSFWorkbook();//创建一个工作簿
        Sheet sheet = wb.createSheet("出货表");//创建一个工作表
        CellStyle cellStyle= wb.createCellStyle();
        Font nFont = wb.createFont();
        int rowNo = 0;//行号
        int colNo = 1;//列号
        Row nRow = null;//sheet.createRow(2);
        Cell nCell = null;//nRow.createCell(1);
        //设置大标题
        sheet.addMergedRegion(new CellRangeAddress(0,0,1,9));
        nRow = sheet.createRow(rowNo++);
        nCell = nRow.createCell(colNo++);
        nRow.setHeightInPoints(36); 
        nCell.setCellValue(inputDate.replace("-0", "年").replace("-","年")+"月份出货表");
        nCell.setCellStyle(bigTitleStyle(cellStyle, nFont));

        //设置列宽
        sheet.setColumnWidth(0, 2*272);     //列宽
        sheet.setColumnWidth(1, 26*272);    //列宽 BUG，API底层设置不够精确 256；272近似
        sheet.setColumnWidth(2, 12*272);
        sheet.setColumnWidth(3, 29*272);
        sheet.setColumnWidth(4, 10*272);
        sheet.setColumnWidth(5, 12*272);
        sheet.setColumnWidth(6, 8*272);
        sheet.setColumnWidth(7, 10*272);
        sheet.setColumnWidth(8, 10*272);
        sheet.setColumnWidth(9, 10*272);

        //设置列标题
        String[] title = new String[]{"客户","订单号","货号","数量","工厂","附件","工厂交期","船期","贸易条款"};
        nRow = sheet.createRow(rowNo++);

        nCell=nRow.createCell(colNo++);

        nRow.setHeightInPoints(26.25f);//设置行高
        colNo=1;//列号置一

        cellStyle= wb.createCellStyle();
        nFont = wb.createFont();
        for(int i=0;i<title.length;i++){

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(title[i]);
            nCell.setCellStyle(titleStyle(cellStyle,nFont));
        }

         cellStyle= wb.createCellStyle();
         nFont = wb.createFont();

        for(int i=0;i<outProductList.size();i++){
            colNo=1;
            nRow = sheet.createRow(rowNo++);
            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getCustomName());
            nCell.setCellStyle(textStyle(cellStyle, nFont));

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getContractNo());
            nCell.setCellStyle(textStyle(cellStyle, nFont));

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getContractNo());
            nCell.setCellStyle(textStyle(cellStyle, nFont));

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getCnumber());
            nCell.setCellStyle(textStyle(cellStyle, nFont));

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getFactoryName());
            nCell.setCellStyle(textStyle(cellStyle, nFont));

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getExts());
            nCell.setCellStyle(textStyle(cellStyle, nFont));

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getDeliveryPeriod());
            nCell.setCellStyle(textStyle(cellStyle, nFont));

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getShipTime());
            nCell.setCellStyle(textStyle(cellStyle, nFont));

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getTradeTerms());
            nCell.setCellStyle(textStyle(cellStyle, nFont));

        }

//      FileOutputStream fileOut = new FileOutputStream("D://出货表.xls");
//      
//      wb.write(fileOut);
//      
//      fileOut.close();
        //下载
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        wb.write(bos);
        DownloadUtil.download(bos, response, "出货表.xls");;
        bos.close();

第二种方式：采用模板方式，样式在模板文件中通过手动方式设置好

        //获取打印数据
        List<OutProductVO> outProductList = this.outProductService.query(inputDate);
        //获取打印模板路径
        String path =request.getSession().getServletContext().getRealPath("/");//服务器的根路径
        String filePath = path+"/make/xlsprint/tOUTPRODUCT.xls";
        //获取打印模板
        Workbook wb = new HSSFWorkbook(new FileInputStream(new File(filePath)));
        //获取工作表
        Sheet sheet = wb.getSheetAt(0);
        Row row = null;
        Cell cell =null;        
        row= sheet.getRow(0);
        cell= row.getCell(1);
        //设置大标题
        cell.setCellValue(inputDate.replace("-0","年").replace("-","年")+"月份出货表");
        //获取列样式
        row = sheet.getRow(2);
        cell=row.getCell(1);
        CellStyle customStyle = cell.getCellStyle();

        row = sheet.getRow(2);
        cell=row.getCell(2);
        CellStyle contactNOStyle = cell.getCellStyle();

        row = sheet.getRow(2);
        cell=row.getCell(3);
        CellStyle productNoStyle = cell.getCellStyle();

        row = sheet.getRow(2);
        cell=row.getCell(4);
        CellStyle cnumberStyle = cell.getCellStyle();

        row = sheet.getRow(2);
        cell=row.getCell(5);
        CellStyle factoryStyle = cell.getCellStyle();

        row = sheet.getRow(2);
        cell=row.getCell(6);
        CellStyle extsStyle = cell.getCellStyle();

        row = sheet.getRow(2);
        cell=row.getCell(7);
        CellStyle deliveryStyle = cell.getCellStyle();

        row = sheet.getRow(2);
        cell=row.getCell(8);
        CellStyle shiptimeStyle = cell.getCellStyle();

        row = sheet.getRow(2);
        cell=row.getCell(9);
        CellStyle tradetermsStyle = cell.getCellStyle();
        //设置主题内容
        int rowNo=2;
        int colNo=1;
        Row nRow=null;
        Cell nCell=null;
        for(int i=0;i<outProductList.size();i++){
            colNo=1;
            nRow = sheet.createRow(rowNo++);
            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getCustomName());
            nCell.setCellStyle(customStyle);

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getContractNo());
            nCell.setCellStyle(contactNOStyle);

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getContractNo());
            nCell.setCellStyle(productNoStyle);

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getCnumber());
            nCell.setCellStyle(cnumberStyle);

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getFactoryName());
            nCell.setCellStyle(factoryStyle);

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getExts());
            nCell.setCellStyle(extsStyle);

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getDeliveryPeriod());
            nCell.setCellStyle(deliveryStyle);

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getShipTime());
            nCell.setCellStyle(shiptimeStyle);

            nCell=nRow.createCell(colNo++);
            nCell.setCellValue(outProductList.get(i).getTradeTerms());
            nCell.setCellStyle(tradetermsStyle);
        }
        //下载文件
        ByteArrayOutputStream bos = new ByteArrayOutputStream();

        wb.write(bos);

        DownloadUtil.download(bos, response, "出货表.xls");

        bos.close();