1. poi3.15版本之前
package com.xxx.excel
import grails.transaction.Transactional
import org.apache.poi.hssf.usermodel.HSSFCellStyle
import org.apache.poi.hssf.usermodel.HSSFFont
import org.apache.poi.hssf.util.HSSFColor
import org.apache.poi.ss.usermodel.CellStyle
import org.apache.poi.ss.usermodel.Font
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.ss.util.RegionUtil
import org.apache.poi.xssf.streaming.SXSSFCell
import org.apache.poi.xssf.streaming.SXSSFRow
import org.apache.poi.xssf.streaming.SXSSFSheet
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFCellStyle
import org.apache.poi.xssf.usermodel.XSSFRichTextString
import javax.servlet.http.HttpServletResponse
@Transactional
class ExcelUtil {
/**
* Excel导出
* @param title String 标题
* @param headers ArrayList<String> 表头
* @param fileName String 导出文件名
* @param datas ArrayList<Map> map类型集合数据
* @param out 输出流
*/
static void export(title, headers, fileName, datas, HttpServletResponse response) throws Exception {
if (!datas) {
return
}
int rownum = 0 //声明行
int rowaccess = 100//100条
OutputStream out = response.outputStream
SXSSFWorkbook workbook = new SXSSFWorkbook(1000)//声明一个工作薄
Sheet sheet = (SXSSFSheet) workbook.createSheet(title)//生成一个表格
sheet.setDefaultColumnWidth(20)//设置表格默认列宽度为15个字节
CellStyle style = workbook.createCellStyle()//生成一个样式
//设置这些样式
style.setFillForegroundColor(HSSFColor.WHITE.index)
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
style.setBorderBottom(HSSFCellStyle.BORDER_THIN)
style.setBorderLeft(HSSFCellStyle.BORDER_THIN)
style.setBorderRight(HSSFCellStyle.BORDER_THIN)
style.setBorderTop(HSSFCellStyle.BORDER_THIN)
style.setAlignment(HSSFCellStyle.ALIGN_CENTER)
//生成一个字体
Font font = workbook.createFont()
font.setColor(HSSFColor.BLACK.index)
font.setFontHeightInPoints((short) 12)
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
//把字体应用到当前的样式
style.setFont(font)
//生成并设置另一个样式
CellStyle style2 = workbook.createCellStyle()
style2.setFillForegroundColor(HSSFColor.WHITE.index)
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN)
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN)
style2.setBorderRight(HSSFCellStyle.BORDER_THIN)
style2.setBorderTop(HSSFCellStyle.BORDER_THIN)
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER)
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)
//生成另一个字体
Font font2 = workbook.createFont()
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL)
//把字体应用到当前的样式
style2.setFont(font2)
//产生表格标题行
SXSSFRow row = sheet.createRow(0)
for (int i = 0; i < headers.size(); i++) {
SXSSFCell cell = row.createCell(i)
cell.setCellStyle(style)
XSSFRichTextString text = new XSSFRichTextString(headers[i])
text.applyFont(font)
cell.setCellValue(text)
}
//遍历集合数据,产生数据行
datas.eachWithIndex { d, index ->
//循环省略每循环一次rownum++
if (rownum % rowaccess == 0) {
try {
((SXSSFSheet) sheet).flushRows()
} catch (IOException e) {
e.printStackTrace()
}
}
row = sheet.createRow(index + 1)
Set set = d.entrySet()
for (int i = 0; i < set.si