JAVA POI导出EXCEL设置自定义样式（线框加粗，合并指定行，合计求和，冻结行）
前面部分是当时查询的记录：
实现Excel文件单元格合并、冻结和文件导出
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("sheet1");
Row row = null;
Cell cell = null;
//创建表头单元格样式
CellStyle cs_header = wb.createCellStyle();
Font boldFont = wb.createFont();
boldFont.setFontName("Consolas");
boldFont.setFontHeightInPoints((short)14);
cs_header.setFont(boldFont);
cs_header.setBorderBottom((short)1);
cs_header.setBorderLeft((short)1);
cs_header.setBorderRight((short)1);
cs_header.setBorderTop((short)1);
//第一行，时间
row = sheet.createRow((short)0);
row.setHeightInPoints((short)24);
createMultiCell(row,cell,cs_header,0,31,"访问日期："+time1+" to "+time2);
//第二行，大标题
row = sheet.createRow((short)1);
row.setHeightInPoints((short)24);
createMultiCell(row,cell,cs_header,0,6," ");
createMultiCell(row,cell,cs_header,6,25,"访问路径");
//第三行，列标题
row = sheet.createRow((short)2);
row.setHeightInPoints((short)24);
String[] headers = new String[]{"访问时间","地域","来源","关键字","进入页","停留时间","访问页数",
"1时间","1停留","1页面","2时间","2停留","2页面","3时间","3停留","3页面","4时间","4停留","4页面",
"5时间","5停留","5页面","6时间","6停留","6页面","7时间","7停留","7页面","8时间","8停留","8页面"};
for(int i=0; i<headers.length; i++){
cell = row.createCell((short)i);cell.setCellValue(headers[i]);cell.setCellStyle(cs_header);
}
//创建文本单元格样式
CellStyle cs_text = wb.createCellStyle();
Font textFont = wb.createFont();
textFont.setFontName("Consolas");
textFont.setFontHeightInPoints((short)10);
cs_text.setFont(textFont);
cs_text.setBorderBottom((short)1);
cs_text.setBorderLeft((short)1);
cs_text.setBorderRight((short)1);
cs_text.setBorderTop((short)1);
//将数据写入表格
for(int i=0; i<list.size(); i++){
row = sheet.createRow((short)(i+3));
Object[] rw = list.get(i);
for(int j=0; j<rw.length; j++){
cell = row.createCell((short)j);
cell.setCellValue(rw[j].toString());
cell.setCellStyle(cs_text);
}
}
//合并第1行1-32列
sheet.addMergedRegion(new CellRangeAddress((short)0, (short)0, (short)0, (short)31));
//合并第2行1-6列
sheet.addMergedRegion(new CellRangeAddress((short)1, (short)1, (short)0, (short)6));
//合并第2行7-32行
sheet.addMergedRegion(new CellRangeAddress((short)1, (short)1, (short)7, (short)31));
//冻结7X3(宽,高)区域中的单元格
sheet.createFreezePane(7, 3);
try {
//将workbook写到输入流(下载时候，这个输出流可能是ServletOutStream，写入文件是FileOutputStream，等等)
wb.write(os);
} catch (IOException e) {
e.printStackTrace();
}
上文原文链接：原文地址
导出Excel每列求和,POI导出excel执行自动求和
for(int i = 0; i < arrSize; i++) {
JSONObject item = dataListArr.getJSONObject(i);
HSSFRow row = sheet.createRow(i + 1);
HSSFCell cell0 = row.createCell(0);
cell0.setCellValue(i + 1);
HSSFCell cell1 = row.createCell(1);
cell1.setCellValue(item.get("farmerName") != null ? item.get("farmerName").toString() : "");
HSSFCell cell2 = row.createCell(2);
//0对应的是double型数值 新版本此方法过期可以用新方法
cell2.setCellType(0);
cell2.setCellValue(item.get("crabCultivationSpecification") != null ? Double.valueOf(item.get("crabCultivationSpecification").toString()) : 0.0);
...
HSSFCell cell8 = row.createCell(8);
cell8.setCellValue(item.get("cultivationTime") != null && !"".equals(item.get("cultivationTime").toString())? BusinessService.formatTime(item.get("cultivationTime").toString().substring(0, 8)) : "");
}
//合计功能 sum也可以替换成其他的函数
HSSFRow row = sheet.createRow(arrSize+1);
HSSFCell cell = row.createCell(0);
cell.setCellValue("合计");
for (int i = 2; i < 8; i++) { //i代表是第几列
cell = row.createCell(i);//设置公式前，一定要先建立表格
String colString = CellReference.convertNumToColString(i); //长度转成ABC列
String sumstring = "SUM(" + colString + "2:" + colString + (arrSize+1) + ")";//求和公式
sheet.getRow(arrSize+1).getCell(i).setCellFormula(sumstring);
}
需要注意的是:
arrSize代表是行数,在求和公式中arrSize需要+1,不然统计的行数不正确,因为合计是占一行的(想不明白就自己新建个excel),同理求和函数也可以换成excel中其他的函数.
String colString = CellReference.convertNumToColString(i);
//长度转成ABC列这句话就是把行列转成ABCD那种格式,想想求和公式=SUM(A1:A4)就是转成里面的A这种,实在想不明白就别想了,记住要加就行.
期间遇到一个问题就是,数据库导出的数据写到excel中是String格式,后来百度了一下才知道,源数据是什么类型写到excel中就是什么类型,用cell2.setCellType(0);就行.
原文：原文
本人在实际运用时：
ExcelWriter writer = ExcelUtil.getBigWriter();
//为大标题小标题先添加行
if(StrUtil.isNotBlank(titleName)) {
rows.add(0, Collections.emptyList());
y++;
}else {
if (StrUtil.isNotBlank(description)) {
for (int i = 0; i < y; i++) {
rows.add(i, Collections.emptyList());
}
}
}
if(StrUtil.isNotBlank(minTitle)){
rows.add(0, Collections.emptyList());
y++;
}
//添加字段-标题到首行
titleList.addAll(multiDataTitle.values());
rows.add(y, titleList);
//添加需要合计的列
int rowNum = rows.size() + 1;
if(!CollectionUtils.isEmpty(sumCellList)){
rowNum = this.handelSumColum(writer, rows, sumCellList, columnList, y);
}
//合并计算出来的合并对象
writer.write(rows,true);
if(StrUtil.isNotBlank(minTitle)){
//设置查询时间
writer.merge(1, 1, 0, Math.max(titleList.size()-1,x), new HSSFRichTextString(minTitle), false);
}
if(StrUtil.isNotBlank(titleName)){
writeTitle(titleName, writer, Math.max(titleList.size()-1,x), rowNum, minTitle, y);
}else {
if (StrUtil.isNotBlank(description)) {
writeDescription(excelFile.description(), writer, Math.max(titleList.size()-1,x), y);
}
}
private int handelSumColum(ExcelWriter writer, List<List<Object>> rows, List<Integer> sumCellList, List<Object> columnList, int y){
Row row = writer.getSheet().createRow(rows.size());
for (Integer i : sumCellList) {
Cell cell = row.createCell(i-1);
String colString = CellReference.convertNumToColString(i-1);//长度转成ABC列
//y为标题行，y+1为 数据开始行
String sumString = "SUM(" + colString + (y+1) +":" + colString + rows.size() + ")";
cell.setCellFormula(sumString);// 把公式塞入合计列
CellStyle cellStyle = cell.getCellStyle();
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
}
//获取合计list中第一个值为columnList的下标，即为第一个要合计的值在excel中的实际列
int index = columnList.indexOf(sumCellList.stream().findFirst().get()) - 1;
//设置合计列
if(index > 0){
writer.merge(row.getRowNum(), row.getRowNum(), 0, index, new HSSFRichTextString("合计"), false);
}else{
writer.writeCellValue(index, row.getRowNum(), "合计");
}
return row.getRowNum();
}
private void writeTitle(String title, ExcelWriter writer, int x, int rowNum, String minTitle, int y){
//设置标题头
writer.merge(0, 0, 0, x, new HSSFRichTextString(title), true);
StyleSet style = writer.getStyleSet();
CellStyle cellStyle = style.getHeadCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = writer.createFont();
font.setFontName("MS Sans Serif");
font.setBold(true);
font.setFontHeightInPoints((short) 24); //字体大小
cellStyle.setFont(font);
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
writer.setStyle(cellStyle, 0, 0);
//设置表头样式
CellStyle headStyle = style.getHeadCellStyle();
Font headFont = writer.createFont();
headFont.setBold(true);
headFont.setFontName("微软雅黑");
headFont.setFontHeightInPoints((short) 10);
headStyle.setFont(headFont);
headStyle.setAlignment(HorizontalAlignment.CENTER);
for (int i = 0; i <= x ; i++) {
headStyle.setBorderRight(BorderStyle.THIN);
writer.setStyle(headStyle, i, y);
}
//设置表格外框线加粗
Sheet sheet = writer.getSheet();
CellRangeAddress cra =new CellRangeAddress(0, 0, 0, x);
CellRangeAddress lastCra =new CellRangeAddress(0, rowNum, 0, x);
CellRangeAddress rightCra =new CellRangeAddress(1, rowNum, 0, x);
CellRangeAddress leftCra =new CellRangeAddress(1, rowNum, 0, x);
RegionUtil.setBorderBottom(BorderStyle.THICK, cra, sheet);
RegionUtil.setBorderBottom(BorderStyle.THICK, lastCra, sheet);
RegionUtil.setBorderRight(BorderStyle.THICK, rightCra, sheet);
RegionUtil.setBorderLeft(BorderStyle.THICK, leftCra, sheet);
//冻结x*y(宽,高)区域中的单元格
//y+1，y表示添加的标题行，+1表示数据抬头行
sheet.createFreezePane(0, y+1);
}
最终样式：
这里前三行是冻结了的，
x为列，y为行，+1 or -1 是因为有下标的原因，为达到指定的列or行。
主要用hutool集成下的方法，所以上边部分方法是来自hutool，方法都差不多。
后期需要设置指定列的样式，如对齐等
先在自定义注解中引用且默认设置为居中
HorizontalAlignment alignmentStyle() default HorizontalAlignment.CENTER;
在模板中使用时，设置该列对齐方式。alignmentStyle = HorizontalAlignment.RIGHT
/**
* name
*/
@ExcelRow(hand = "name", column = 1,alignmentStyle = HorizontalAlignment.RIGHT)
private String name;
/**
* num
*/
@ExcelRow(hand = "num", column = 2, isSum = true, alignmentStyle = HorizontalAlignment.LEFT)
private Integer num;
再获取要自定义设置的列和样式，用MAP记录。
Map<Integer, HorizontalAlignment> columStyle = new HashMap<>();
if(null != excelRow && null != excelRow.alignmentStyle()){
columStyle.put(excelRow.column(),excelRow.alignmentStyle());
}
再将指定的单元格进行设置样式。
Sheet sheet1 = writer.getSheet();
Workbook workbook = sheet1.getWorkbook();
if(!columStyle.isEmpty()){
for (int i = y+1; i < rows.size(); i++) {
for (int j = 0; j <= Math.max(titleList.size(),x); j++) {
if(columStyle.containsKey(j)){
HorizontalAlignment horizontalAlignment = columStyle.get(j);
generateRowAndCell(sheet1,workbook,i,j-1,horizontalAlignment, VerticalAlignment.BOTTOM);
}
}
}
}
private void generateRowAndCell(Sheet sheet, Workbook workbook, int rowIndex, int cellIndex,
HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment) {
// 创建row，获取行
Row row = sheet.getRow(rowIndex);
// 创建cell, 设置样式，获取列
Cell cell = row.getCell(cellIndex);
CellStyle cellStyle = workbook.createCellStyle();
// 水平对齐
cellStyle.setAlignment(horizontalAlignment);
// 垂直对齐
cellStyle.setVerticalAlignment(verticalAlignment);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cell.setCellStyle(cellStyle);
}
版权声明：本文为Mou_O原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。