POI导出Excel时一些合并排版之类

前端时候有这样的需求了，在网上找了下，又查询下javadoc，贴一段代码共享下。

 

POI 3.1 final

 

import org.apache.poi.hssf.usermodel.*
import org.apache.poi.hssf.util.*

class ExportModel {
	String sheetTitle

	// 复合表头
	List columnLabel = []
	List columnLabel2 = []
	

	Map columnWidth = [:]
	// merge
	List range = []

	static final short defaultColumnWidth = 22
	static final short columnWidthUnit = 35
	
	static final short rowHeadFontSize = 12

	byte[] export(List ll){
		HSSFWorkbook wb = new HSSFWorkbook()
		HSSFSheet outputSheet = wb.createSheet(sheetTitle)
		outputSheet.setDefaultColumnWidth(defaultColumnWidth)

		// column width setting
		if(columnWidth){
			columnWidth.each{k, v ->
				outputSheet.setColumnWidth((short)k, (short)(columnWidthUnit * v)); 
			}
		}

		// head
		HSSFFont font = wb.createFont()
		font.setFontHeightInPoints(rowHeadFontSize)
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
		font.setColor(HSSFColor.AQUA.index)

		HSSFCellStyle style = wb.createCellStyle()
		style.setFont(font)
//		style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index)
//		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER)
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)

		HSSFRow row = outputSheet.createRow(0)

		columnLabel.eachWithIndex{label, i ->
			HSSFCell cell = row.createCell((short)i) 
			cell.setCellValue(label)
			cell.setCellStyle(style)
		}

		if(columnLabel2){
			HSSFRow row2 = outputSheet.createRow(1)

			columnLabel2.eachWithIndex{label, i ->
				HSSFCell cell = row2.createCell((short)i) 
				cell.setCellValue(label)
				cell.setCellStyle(style)
			}
		}

		if(range){
			range.each{
				// row from col from row to col to
				outputSheet.addMergedRegion(new Region(it[0], (short)it[1], it[2], (short)it[3]))
			}
		}

		if(ll){
			int startRow = columnLabel2?2:1
			for(one in ll){
				int rowIndex = startRow + ll.indexOf(one)
				HSSFRow subRow = outputSheet.createRow(rowIndex)

				one.eachWithIndex{val, i ->
					HSSFCell cell = subRow.createCell((short)i) 
					cell.setCellValue(val)
				}
			}
		}

		ByteArrayOutputStream os = new ByteArrayOutputStream()
		wb.write(os)
		os.close()

		return os.toByteArray()
	}
}

 

评论

1 楼 yilv99 2010-09-16  

支持哈，付出了劳动的都是好的。。。