POI使用整理

在结合前辈大牛，以及自己亲自实践下，整理了一下POI组Excel并导出的简单操作

代码如下：

/**========= EXCEL 表格设置 BEGIN =========**/

/** 创建一个workbook，对应一个Excel文件 **/

HSSFWorkbook wb = new HSSFWorkbook();

//设置打印是每页自动打印表头wb.SetRepeatingRowsAndColumns(a, m, n, x, y);

// m:起始列号；n:结束列号；x:起始行号；y:结束行号；即：可打印行属性，也可打印列属性

wb.SetRepeatingRowsAndColumns(0, -1, -1, 2, 3);

 

/** 在workbook中添加一个sheet，对应Excel中的一个sheet */

HSSFSheet sheet = wb.createSheet("Sheet名称");

//设置是否显示excel网格线 

sheet.setDisplayGridlines(false);

//设置是否打印excel网格线 

sheet.setPrintGridlines(false);

//设置页边距

sheet.setMargin(HSSFSheet.TopMargin, (double)0.5/3); //设置上边距

sheet.setMargin(HSSFSheet.BottomMargin, (double)0.5/3); //设置下边距

sheet.setMargin(HSSFSheet.LeftMargin,(double)0.5); //设置左边距

sheet.setMargin(HSSFSheet.RightMargin,(double)-50); //设置右边距

//设置列宽 setColumnWidth（colindex, width）

// colindex- 要设置的列（从0开始）

// width - 以字符宽度的1 / 256为单位的宽度

// 如果将列宽设置为八个字符宽，setColumnWidth（columnIndex，8 * 256）

sheet.setColumnWidth(0, 8* 256);

sheet.setColumnWidth(1, 37* 256);

sheet.setColumnWidth(2, 15* 256);

sheet.setColumnWidth(3, 15* 256);

sheet.setColumnWidth(4, 25* 256);

sheet.setColumnWidth(5, 31* 256);

sheet.setColumnWidth(6, 8* 256);

//设置单元格合并 CellRangeAddress(m, n, (short) x,(short) y)

//m:起始行号；n:结束行号；x:起始列号；y:结束列号

CellRangeAddress region = new CellRangeAddress(0, 0, (short) 0,(short) 6);

CellRangeAddress region1 = new CellRangeAddress(2, 3, (short) 0, (short) 0);

CellRangeAddress region4 = new CellRangeAddress(2, 3, (short) 1, (short) 1);

CellRangeAddress region5 = new CellRangeAddress(2, 2, (short) 2, (short) 3);

CellRangeAddress region6 = new CellRangeAddress(2, 3, (short) 4, (short) 4);

CellRangeAddress region7 = new CellRangeAddress(2, 3, (short) 5, (short) 5);

CellRangeAddress region8 = new CellRangeAddress(2, 3, (short) 6, (short) 6);

//合并样式写入

sheet.addMergedRegion(region);

sheet.addMergedRegion(region1);

sheet.addMergedRegion(region4);

sheet.addMergedRegion(region5);

sheet.addMergedRegion(region6);

sheet.addMergedRegion(region7);

sheet.addMergedRegion(region8);

 

/** 打印设置 **/

HSSFPrintSetup printSetup = sheet.getPrintSetup();

printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //设置纸张类型(这里是A4)

printSetup.setLandscape(true); // 设置打印方向，true：横向，false：纵向(默认)

 

/** 字体设置 **/

HSSFFont font = wb.createFont();

font.setColor(HSSFColor.BLACK.index); //字体颜色

font.setFontName("宋体"); //字体类型

font.setFontHeightInPoints((short) 11); //字体大小

 

/** 设置单元格样式 **/

HSSFCellStyle style = wb.createCellStyle();

style.setWrapText(true); //设置自动换行

style.setAlignment(CellStyle.ALIGN_LEFT); //水平居中 style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //垂直居中 style.setFont(font); //引入字体设置

style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框 style.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框 style.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框

 

HSSFRow row = sheet.createRow(0); //创建行 HSSFCell cell = row.createCell(0); //创建第0行，第0列

row.setHeight((short)((40 * 15.625))); //设置行高

cell.setCellStyle(style); //使用样式 /**========= EXCEL 表格设置 END =========**/

 

/**========= EXCEL 表格导出 BEGIN =========**/

SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmss");

String dateString = sf.format(new Date()); //获取时间，并格式化；用于文件名区别 String name = "xxxx-" + (dateString); //组装文件名

ByteArrayOutputStream os = new ByteArrayOutputStream();

wb.write(os);

byte[] content = os.toByteArray();

InputStream is = new ByteArrayInputStream(content);

// 设置response参数，可以打开下载页面 response.reset();

response.setContentType("application/vnd.ms-excel");

response.setCharacterEncoding("UTF-8");

response.setHeader("Content-Disposition", "attachment;filename="

+ new String(name.getBytes(),"iso-8859-1") + ".xls");

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 (Exception e) {

e.printStackTrace();

logger.error("Export UserInfo Error:" + e);

} finally {

if (bis != null)

bis.close();

if (bos != null)

bos.close();

if (out != null) {

out.flush();

out.close();

}

if (os != null) {

os.close();

}

} /**========= EXCEL 表格导出 END=========**/

转载于:https://my.oschina.net/u/3664105/blog/1525051