数据库数据按指定格式导出到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的所有内容，贴的东西比较多，如果朋友看的时候找重点看代码，不用一行一行瞅。