第一次动手写这个,采的坑有些多,所以记录下!
结果:
1、样式不起作用
印象最深的就是那个样式,死活不起作用。想将表头文字设置成上面样子。合并单元格,文字居中,宋体加粗显示,字体18号。
创建sheet页面合并单元格:
// 声明一个工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 创建sheet页
XSSFSheet sheet = wb.createSheet(sheetName);
// 设置列宽:19字节
sheet.setDefaultColumnWidth(19);
// 表头
XSSFRow rowReportTitle = sheet.createRow(0);
Cell cell1 = rowReportTitle.createCell(0);// 0列
// 设置表头的值
cell1.setCellValue(sheetName);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, listName.size() - 1));
rowReportTitle.setHeight((short) 600); // 行高
上面的代码一次就写成功了,但是居中和字体样式怎么改都不起作用。在网上查找代码,都是一句
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
我就很纳闷,为啥我写上就不起作用呢?
设置边框代码如下,令我意外的是,边框中行,一直看不到头上都有边框?
XSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(BorderStyle.THIN); //下边框
style.setBorderLeft(BorderStyle.THIN);//左边框
style.setBorderTop(BorderStyle.THIN);//上边框
style.setBorderRight(BorderStyle.THIN);//右边框
XSSFRow row = sheet.createRow(1);
row.setRowStyle(cellStyle);
ennn……突然发现了什么?
肿么肥四?
好像有了点想法,为啥延伸到了无限远?这是把样式设置给了行
正确的代码:
// 全局加线样式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
// 标题
XSSFRow row = sheet.createRow(1);
for (int i = 0; i < listName.size(); i++) {
row.setHeight((short) 450);
XSSFCell cell = row.createCell(i);
cell.setCellValue(listName.get(i));
cell.setCellStyle(cellStyle); // 将样式添加给列
}
2、字体样式不起作用
同上理由,将字体样式添加给列
// 合并表头
XSSFRow rowReportTitle = sheet.createRow(0);
Cell cell1 = rowReportTitle.createCell(0); // 0列
// 设置值
cell1.setCellValue(sheetName);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, listName.size() - 1));
rowReportTitle.setHeight((short) 600); // 行高
//设置表头字体
Font headFont = wb.createFont();
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 18);// 字体大小
CellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
// 头部样式添加
cell1.setCellStyle(headStyle);
下面给上完整代码!!!
正在学习,共同进步!加油!!
package com.paladin.monitor.tuil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;
import java.util.prefs.BackingStoreException;
/**
* @author cxt
* @date 2020/10/29
*/
public class ExcelPortUtil {
/**
* @param sheetName 工作表名,文件名,头部信息
* @param listName 列名
* @param list 需要写入的数据
* @param response 返回
*/
public static void excelPort(String sheetName, List<String> listName, List<List<String>> list, HttpServletResponse response) {
try {
if (list.size() == 0) {
throw new BackingStoreException("数据为空");
}
// 声明一个工作簿
XSSFWorkbook wb = new XSSFWorkbook();
// 创建sheet页
XSSFSheet sheet = wb.createSheet(sheetName);
sheet.setDefaultColumnWidth(19);
// 合并表头
XSSFRow rowReportTitle = sheet.createRow(0);
Cell cell1 = rowReportTitle.createCell(0);
// 设置值
cell1.setCellValue(sheetName);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, listName.size() - 1));
rowReportTitle.setHeight((short) 600); // 行高
//设置表头字体
Font headFont = wb.createFont();
headFont.setFontName("宋体");
headFont.setFontHeightInPoints((short) 18);// 字体大小
CellStyle headStyle = wb.createCellStyle();
headStyle.setFont(headFont);
headStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
// 头部样式添加
cell1.setCellStyle(headStyle);
// 全局加线样式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
cellStyle.setBorderTop(BorderStyle.THIN);//上边框
cellStyle.setBorderRight(BorderStyle.THIN);//右边框
// 标题
XSSFRow row = sheet.createRow(1);
for (int i = 0; i < listName.size(); i++) {
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
row.setHeight((short) 450);
XSSFCell cell = row.createCell(i);
cell.setCellValue(listName.get(i));
cell.setCellStyle(cellStyle);
}
// 写入数据
int ind = 2;
for (List<String> a : list) {
XSSFRow r = sheet.createRow(ind++);
int cell = 0;
for (String l : a) {
XSSFCell cell2 = r.createCell(cell++);
cell2.setCellValue(l);
cell2.setCellStyle(cellStyle);
}
}
// 输出Excel文件
OutputStream output = response.getOutputStream();
response.reset();
// 设置文件头
response.setHeader("Content-Disposition",
"attchement;filename=" + new String((sheetName + ".xls").getBytes("gb2312"), "ISO8859-1"));
response.setContentType("application/msexcel");
wb.write(output);
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}