数据库数据按指定格式导出到Excel

数据库数据按指定格式导出到Excel

---

一.博客目的：
工作需要做一个关于列表打印的功能，不适用第三方，有图片，又表体，表格上下有额外的信息，表格行数不确定，多的时候可能上万行。第一次通过画jsp使用ie浏览器的window.print进行打印。
这样一次只能打印当前显示的页面，数据量过大，分页会出现断层，然后自己使用js做了一个分页，基本功能已经实现，但是还存在一个问题，如果数据量上万条，分页分了一百多页，一页一页的打印，是不是想想都比较恐怖。
所以经过综合考虑，决定将数据导出到Excel，通过excel进行打印，别说，效果出奇的好，而且不用考虑分页。讲此实现记录下来方便自己回顾。
二.使用技术：
通过poi组件操作Excel。
三.详细的做法：
首先要搭建poi环境，也就是下载jar包，我是在csdn上下别人分享的，poi 3.7 beta2,所以就不附链接了，地球人都能找到。
页面就加了一个按钮，将后台需要的传了过去，师太是使用servlet写的。

• 倒入的类
  下面先列出项目用到的类，下次导的时候分不清可以做参考：
  import org.apache.poi.hssf.usermodel.HSSFCell;
  import org.apache.poi.hssf.usermodel.HSSFCellStyle;
  import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
  import org.apache.poi.hssf.usermodel.HSSFFont;
  import org.apache.poi.hssf.usermodel.HSSFPatriarch;
  import org.apache.poi.hssf.usermodel.HSSFRow;
  import org.apache.poi.hssf.usermodel.HSSFSheet;
  import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  import org.apache.poi.hssf.util.HSSFColor;
  import org.apache.poi.hssf.util.Region;
• 设计思路
  思路很简单，短短几句话就能概括：创建sheet页，创建行，创建单元格向单元格set内容，设计样式。
  这样问题就很简单了，就那其中的行举例说明：行通过什么创建，方法是什么，创建的行要多高，边框怎么设置。其他的也都这样思考，挨着考虑完，基本写的过程就出来了。、

• 使用到的方法
  创建Excel工作簿对象：createSheet()；
  设置工作簿名字：setSheetName(int sheetIx, “名字”)；
  创建行：.createRow(int n),创建第n行；
  设置行高：row.setHeightInPoints(int n)；
  合并单元格：addMergedRegion(new Region(1, (short) 0, 1, (short) cellNum));1,3,参数是行，2,4,参数是列；
  加载图片：
  workbook.addPicture(Byte[] imgBytes, workbook.PICTURE_TYPE_PNG);第一个参数是图片的字节数组，第二个是图片类型；
  创建绘制图片区域：
  HSSFClientAnchor anchoranchor = new HSSFClientAnchor(int x1, int y1, int x2, int y2, (short) col1, row1, (short) col2, row 2)
  x1,y1是图片区域左上角的位置，x2,y2是右下角位置，row1和row2是区域上方和下方所在行，col1,col2是取悦左右所在列；
  创建用于绘画的对象：createDrawingPatriarch()；
  将图片画到Excel指定区域：createPicture(anchor, pacIndex);
  创建单元格：createCell(int n);n为第几个单元格
  给单元格set值：setCellValue();方法里可以传不同的数据类型
  设置单元格样式：setCellStyle();

• 代码实现（为了我以后看起来方便，所以代码全罗列，别人看的时候可以跳着看，代码考下来改一下可以直接使用）

//。。。。。。。查询数据省略。。。。。。private void jxlBuildExcel(Map installMap, String policyNo, String riskCode,HttpServletResponse response) throws IOException {    HSSFWorkbook workbook = new HSSFWorkbook();    // 使用HSSFWorkbook对象创建Excel工作簿对象    HSSFSheet sheet = workbook.createSheet();    // 可以设置一个工作表名称，也就是excel打开后下方sheet页的名字    workbook.setSheetName(0, policyNo);    //因为我要做五个模板，所以我先定义一个变量cellNum 设置列数    int cellNum = 16;        if ("1113".equals(riskCode)) {            cellNum = 16;        } else if ("1152".equals(riskCode)) {            cellNum = 17;        } else if ("1177".equals(riskCode)) {            if(maxCount == 0){                cellNum = 15;            }else{                cellNum = 19;            }        } else if ("1164".equals(riskCode)) {            cellNum = 16;        } else {            if(maxCount == 0){                cellNum = 12;            }else{                cellNum = 17;            }        }        // 在工作表里创建对象，第一行放图片，位置中心，每个模板列不同，每个列的宽度不一样，所以画图区域的自己慢慢调。        HSSFRow row1 = sheet.createRow(0);        row1.setHeightInPoints(60);//设置一下第一列的高度，因为要加载图片，所以高度设置的大了写。        // 将图片传入Excle        String path = this.getServletContext().getRealPath("/");        FileInputStream fis = new FileInputStream(path + "common/images/pm_logo.png");        byte[] imgBytes = new byte[fis.available()];        // 添加图片字节数据到工作簿对象中，addPicture方法第一个参数是图片字节数组，第二个是图片类型，我的图片是.png        int pacIndex = workbook.addPicture(imgBytes, workbook.PICTURE_TYPE_PNG);        //此处创建画图区域        HSSFClientAnchor anchor;        //图片的区域根据实际列数来确定大小        if ("1113".equals(riskCode)) {            anchor = new HSSFClientAnchor(100, 30, 900, 200, (short) 7, 0, (short) 11, 0);        } else if ("1152".equals(riskCode)) {            anchor = new HSSFClientAnchor(0, 30, 500, 200, (short) 8, 0, (short) 12, 0);        } else if ("1177".equals(riskCode)) {            if(maxCount == 2){                anchor = new HSSFClientAnchor(500, 30, 100, 200, (short) 7, 0, (short) 13, 0);            }else{//有一个受益人或法定                anchor = new HSSFClientAnchor(100, 30, 900, 200, (short) 6, 0, (short) 10, 0);            }        } else if ("1164".equals(riskCode)) {            anchor = new HSSFClientAnchor(800, 30, 300, 200, (short) 6, 0, (short) 11, 0);        } else {            if(maxCount == 0){                anchor = new HSSFClientAnchor(900, 80, 920, 200, (short) 4, 0, (short) 7, 0);            }else{                anchor = new HSSFClientAnchor(100, 30, 900, 200, (short) 7, 0, (short) 11, 0);            }        }        // 创建用于绘画的对象        HSSFPatriarch patri = sheet.createDrawingPatriarch();        // 将图片画到Excel指定区域        patri.createPicture(anchor, pacIndex);        // logo        int middleCell = cellNum/2;        HSSFRow row2 = sheet.createRow(1);        sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) cellNum));        HSSFCell titleCell = row2.createCell(0);        titleCell.setCellValue("团体保险被保险人清单");        titleCell.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_CENTER, 0));        HSSFRow row3 = sheet.createRow(2);        sheet.addMergedRegion(new Region(2, (short) 0, 2, (short) middleCell));        sheet.addMergedRegion(new Region(2, (short) (middleCell+1), 2, (short) cellNum));        HSSFCell appliNameCell = row3.createCell(0);        HSSFCell policyNoCell = row3.createCell(middleCell+1);        appliNameCell.setCellValue("投保人名称：" + appliName);        appliNameCell.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_LEFT, 0));        policyNoCell.setCellValue("保单号码：" + policyNo);        policyNoCell.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_LEFT, 0));        HSSFRow row4 = sheet.createRow(3);        sheet.addMergedRegion(new Region(3, (short) 0, 3, (short) middleCell));        sheet.addMergedRegion(new Region(3, (short) (middleCell+1), 3, (short) cellNum));        HSSFCell createDateCell = row4.createCell(0);        HSSFCell printDateCell = row4.createCell(middleCell+1);        createDateCell.setCellValue("制表日期：" + sdf.format(new Date()));        createDateCell.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_LEFT, 0));        printDateCell.setCellValue("打印日期：" + sdf.format(new Date()));        printDateCell.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_LEFT, 0));        HSSFRow row5 = sheet.createRow(4);        sheet.addMergedRegion(new Region(4, (short) 0, 4, (short) cellNum));        HSSFCell currencyCell = row5.createCell(0);        currencyCell.setCellValue("单位：人民币");        currencyCell.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, 0));        // 表体----------------start--------------------int rowNum = 0;// 用来统计最大行下标,方便后面行的创建        if ("1113".equals(riskCode)) {            rowNum = surfaceBody1113(sheet, workbook, list1, rowNum, policyNo);        } else if ("1152".equals(riskCode)) {            rowNum = surfaceBody1152(sheet, workbook, list1, rowNum, policyNo);        } else if ("1177".equals(riskCode)) {            rowNum = surfaceBody1177(sheet, workbook, list1, rowNum, policyNo,benifitList,maxCount);        } else if ("1164".equals(riskCode)) {            rowNum = surfaceBody1164(sheet, workbook, list1, rowNum, policyNo);        } else {            rowNum = surfaceBodyCommon(sheet, workbook, list1, rowNum, policyNo,benifitList,maxCount);        }        // 表体----------------end--------------------        // 表尾----------------start--------------------        HSSFRow rowOne = sheet.createRow((rowNum) + 1);        sheet.addMergedRegion(new Region(rowNum + 1, (short) 0, rowNum + 1, (short) 3));        HSSFCell personCountCell = rowOne.createCell(0);        personCountCell.setCellValue("人数");        personCountCell.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_CENTER, 0));        rowNum = rowNum + 1;        int sum = 0;// 被保险人人数        for (int i = 0; i < list3.size(); i++) {            Map map3 = (Map) list3.get(i);            sum += Integer.valueOf(map3.get("quantity").toString());            HSSFRow rowTwo = sheet.createRow(rowNum + 1 + i);            sheet.addMergedRegion(new Region(rowNum + 1 + i, (short) 0, rowNum + 1 + i, (short) 1));            sheet.addMergedRegion(new Region(rowNum + 1 + i, (short) 2, rowNum + 1 + i, (short) 3));            sheet.addMergedRegion(new Region(rowNum + 1 + i, (short) 4, rowNum + 1 + i, (short) cellNum));            HSSFCell cellCell;            for (int k = 0; k <= cellNum; k++) {                cellCell = rowTwo.createCell(k);                if (k == 0) {                    cellCell.setCellValue(map3.get("projectname").toString());                    if(i == list3.size()-1){                        cellCell.setCellStyle(createStraightLine(workbook, HSSFCellStyle.ALIGN_LEFT, 0));                    }else{                        cellCell.setCellStyle(createStraightLine(workbook, HSSFCellStyle.ALIGN_LEFT, 3));                    }                } else if (k == 2) {                    cellCell.setCellValue(map3.get("quantity").toString());                    if(i == list3.size()-1){                        cellCell.setCellStyle(createStraightLine(workbook, HSSFCellStyle.ALIGN_RIGHT, 0));                    }else{                        cellCell.setCellStyle(createStraightLine(workbook, HSSFCellStyle.ALIGN_RIGHT, 3));                    }                } else {                    if(i == list3.size()-1){                        cellCell.setCellStyle(createStraightLine(workbook, HSSFCellStyle.ALIGN_RIGHT, 0));                    }else{                        cellCell.setCellStyle(createStraightLine(workbook, HSSFCellStyle.ALIGN_RIGHT, 3));                    }                }            }            rowNum = rowNum + 1 + i;        }        HSSFRow rowTri = sheet.createRow((rowNum) + 1);        sheet.addMergedRegion(new Region(rowNum + 1, (short) 0, rowNum + 1, (short) 1));        sheet.addMergedRegion(new Region(rowNum + 1, (short) 2, rowNum + 1, (short) 3));        HSSFCell secondCell;        for (int m = 0; m <= cellNum; m++) {            secondCell = rowTri.createCell(m);            if (m == 0) {                secondCell.setCellValue("人数合计");                secondCell.setCellStyle(createStraightLine(workbook, HSSFCellStyle.ALIGN_LEFT, 1));            } else if (m == 2) {                secondCell.setCellValue(sum);                secondCell.setCellStyle(createStraightLine(workbook, HSSFCellStyle.ALIGN_RIGHT, 1));            } else {                secondCell.setCellStyle(createStraightLine(workbook, HSSFCellStyle.ALIGN_RIGHT, 1));            }        }        rowNum = rowNum + 1;        HSSFRow rowNotice = sheet.createRow((rowNum) + 1);        sheet.addMergedRegion(new Region(rowNum + 1, (short) 0, rowNum + 1, (short) cellNum));        HSSFCell noticeCell = rowNotice.createCell(0);        noticeCell.setCellValue("注意事项：");        noticeCell.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_LEFT, 0));        rowNum = rowNum + 1;        HSSFRow notice1 = sheet.createRow((rowNum) + 1);        sheet.addMergedRegion(new Region(rowNum + 1, (short) 0, rowNum + 1, (short) cellNum));        HSSFCell noticeCell1 = notice1.createCell(0);        noticeCell1.setCellValue("1.******************。");        noticeCell1.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_LEFT, 0));        rowNum = rowNum + 1;        HSSFRow notice2 = sheet.createRow((rowNum) + 1);        sheet.addMergedRegion(new Region(rowNum + 1, (short) 0, rowNum + 1, (short) cellNum));        HSSFCell noticeCell2 = notice2.createCell(0);        noticeCell2.setCellValue("2.****************。");        noticeCell2.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_LEFT, 0));        // 表尾----------------end----------------------        //设置列宽        if ("1113".equals(riskCode)) {            setUpColumnStyle1113(sheet);        } else if ("1152".equals(riskCode)) {            setUpColumnStyle1152(sheet);        } else if ("1177".equals(riskCode)) {            setUpColumnStyle1177(sheet,maxCount);        } else if ("1164".equals(riskCode)) {            setUpColumnStyle1164(sheet);        } else {            setUpColumnStyleCommon(sheet,maxCount);        }        String dirName =path + File.separator+"excelLoad";        File dir = new File(dirName);        if(!dir.exists()){            if (!dirName.endsWith(File.separator)) {                  dirName = dirName + File.separator;              }            dir.mkdirs();        }        File xlsFile = new File(dirName+File.separator+"被保险人清单.xls");        byte[]  filebate=workbook.getBytes();        synchronized (xlsFile) {            FileOutputStream fos = new FileOutputStream(xlsFile);            workbook.write(fos);            fis.close();            fos.close();        }        response.setContentType("multipart/form-data");        response.setHeader("Content-Disposition", "attachment;fileName="+"groupInsuredList.xls");         ServletOutputStream out=null;        synchronized (xlsFile) {            FileInputStream inputStream = new FileInputStream(xlsFile);               //3.通过response获取ServletOutputStream对象(out)               out = response.getOutputStream();               int b = 0;               byte[] buffer = new byte[1024];              while (b != -1){                   b = inputStream.read(buffer);                   //4.写到输出流(out)中                   out.write(buffer);               }              out.flush();              out.close(); }

• 表体（只列出一个模板，剩下的几个都一个样子）

public int surfaceBody1113(HSSFSheet sheet, HSSFWorkbook workbook, List list1, int rowNum, String policyNo) {        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");        //表第一行---------------start----------------------        HSSFRow headRow = sheet.createRow(5);        headRow.setHeightInPoints((short) 17);        HSSFCell serialNoHead = headRow.createCell(0);        serialNoHead.setCellValue("序号");        serialNoHead.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_CENTER, 2));        /*        此处省略很多列的创建        *******************        此处省略很多列的创建        */        // 保险期间        HSSFCell duringPeriodOfInsuranceCellHead = headRow.createCell(16);        duringPeriodOfInsuranceCellHead.setCellValue("保险期间");        duringPeriodOfInsuranceCellHead.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_CENTER, 2));        // 表第一行----------------end----------------------        // 表体----------------start--------------------        //循环创建行，有多少条信息创建多少行        for (int i = 0; i < list1.size(); i++) {            Map map1 = (Map) list1.get(i);            //计算每个被保险人的保险期间            int duringPeriodOfInsurance = periodOfInsured(map1);            HSSFRow dataRow = sheet.createRow(i + 6);            dataRow.setHeightInPoints((short) 17);            rowNum = i + 6;            HSSFCell serialNo = dataRow.createCell(0);            serialNo.setCellValue(i + 1);            serialNo.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_CENTER, 1));            /*            此处省略很多列的创建            */            // 保险期间            HSSFCell duringPeriodOfInsuranceCell = dataRow.createCell(16);            duringPeriodOfInsuranceCell.setCellValue(duringPeriodOfInsurance+"天");            duringPeriodOfInsuranceCell.setCellStyle(createStyle(workbook, HSSFCellStyle.ALIGN_CENTER, 1));        }        // 表体----------------end----------------------        return rowNum;    }

• 设置单元格样式

// 单元格对其方式 lock为1时显示边框,为0不显示,2显示边框并字体加粗public static HSSFCellStyle createStyle(HSSFWorkbook wb, short align, int lock) {    HSSFCellStyle cellStyle = wb.createCellStyle();    cellStyle.setAlignment(align);    if (lock == 1 || lock == 2) {    cellStyle.setVerticalAlignment(        HSSFCellStyle.VERTICAL_CENTER);cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);   cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);    cellStyle.setRightBorderColor(HSSFColor.BLACK.index);   cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);cellStyle.setTopBorderColor(HSSFColor.BLACK.index);    }    if (lock == 2) {        //设置字体加粗        HSSFFont font = wb.createFont();        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        cellStyle.setFont(font);    }        return cellStyle;    }

• 设置表后面的那条橫线

// 设置表尾的那条横线,lock 0:显示下边框，1:显示上边框,3不显示边框    public HSSFCellStyle createStraightLine(HSSFWorkbook wb, short align, int lock) {        HSSFCellStyle cellStyle = wb.createCellStyle();        cellStyle.setAlignment(align);        if (lock == 0) {    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);   cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);        }        if (lock == 1) {    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);cellStyle.setTopBorderColor(HSSFColor.BLACK.index);        }        return cellStyle;    }

• 设置表列宽

public void setUpColumnStyle1113(HSSFSheet sheet) {    sheet.setColumnWidth(0, 256 * 3 + 184);    sheet.setColumnWidth(1, 256 * 7 + 184);    /*    省略很多列的设置    */    sheet.setColumnWidth(16, 256 * 7 + 184);    }

四.倒出来的效果
截图上是我们的测试数据，上面有关公司信息已经涂去，所以丑了点，见谅。

• 以上基本是我使用poi组件操作Excel的所有内容，贴的东西比较多，如果朋友看的时候找重点看代码，不用一行一行瞅。