如何使用Java POI生成Excel表格?
程序员文章站
2022-06-28 20:26:37
如何使用Java POI生成Excel表格?一. 生成Excel截图:二. 代码如下:Java POI提供三种Workbook:HSSFWorkbook: 对应Excel2003及以前的版本(.xls)XSSFWorkbook:对应Excel2007后的版本(.xlsx)SXSSFWorkbook: 对应Excel2007后的版本(.xlsx)import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi....
如何使用Java POI生成Excel表格?
一. 生成Excel截图:
二. 代码如下:
Java POI提供三种Workbook:
HSSFWorkbook: 对应Excel2003及以前的版本(.xls)
XSSFWorkbook:对应Excel2007后的版本(.xlsx)
SXSSFWorkbook: 对应Excel2007后的版本(.xlsx)
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
/**
* Description: .
*
* @author : ys.
* @date : 2021-01-15 16:11
*/
public class MarkDown {
private static final Logger LOGGER = LoggerBuilder.getLogger(MarkDown.class);
private ExcelFormatUtil formatUtil = ExcelFormatUtil.getInstance();
public Workbook createWorkBook(ExcelVersion version) {
long startTime = System.currentTimeMillis();
Workbook workbook;
if (version.equals(ExcelVersion.SXSSFWORKBOOK)) {
workbook = new SXSSFWorkbook(new XSSFWorkbook(), -1);
} else {
workbook = new HSSFWorkbook();
}
//创建table工作薄
Sheet sheet = workbook.createSheet("sheetname");
CellStyle title = formatUtil.setCellStyle(workbook);
CellStyle content = formatUtil.setContentCellStyle(workbook);
for (int rowIndex = 0; rowIndex < 10; rowIndex++) {
//创建表格行
Row row = sheet.createRow(rowIndex);
for (int colIndex = 0; colIndex < 5; colIndex++) {
//根据表格行创建单元格
Cell cell = row.createCell(colIndex);
//设置单元格格式
if (rowIndex == 0) {
cell.setCellStyle(title);
cell.setCellValue("标题");
} else {
cell.setCellStyle(content);
//设置单元格内容
cell.setCellValue("内容");
}
}
}
long endTime = System.currentTimeMillis();
long usedTime = endTime - startTime;
LOGGER.info("生成Excel耗时: " + usedTime);
return workbook;
}
public void exportExcel(Workbook workbook, String filename) {
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(filename);
workbook.write(fileOutputStream);
LOGGER.info("导出成功!");
} catch (IOException ex) {
LOGGER.info("导出失败!");
} finally {
try {
if (fileOutputStream != null) {
workbook.close();
fileOutputStream.close();
}
} catch (IOException e) {
LOGGER.error("关闭流出错!", e);
}
}
}
}
三. 单元格格式设置
* 设置单元格边框,背景颜色,对齐方式,字体风格与大小
public CellStyle setCellStyle(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
//边框
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
//背景颜色
cellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//对齐方式
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//字体设置
Font font = workbook.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 13);
cellStyle.setFont(font);
return cellStyle;
}
四. 设置行冻结与列筛选
/**
* Description: 设置Excel表格指定行以前的所有行冻结,并给每一列都添加筛选.
* @author : ys.
**/
public void setFreezeAndFilter(Sheet sheet, int rowIndex) {
//清除冻结
sheet.createFreezePane(0, 0, 0, 0);
/**
* colSplit: 拆分单元格的列 rowSplit: 拆分单元格的行
* leftmostColumn: 右边区域可见的左边列数 topRow: 下面区域可见的首行
*/
//设置冻结行
sheet.createFreezePane(0, rowIndex + 1, 0, rowIndex + 1);
int count = sheet.getRow(0).getPhysicalNumberOfCells();
if (count > 0) {
CellRangeAddress cellAddresses = new CellRangeAddress(rowIndex, rowIndex,
0, count - 1);
//添加筛选
sheet.setAutoFilter(cellAddresses);
}
}
五. 其他格式设置
合并单元格
//合并指定范围单元格
sheet.addMergedRegion(new CellRangeAddress(firstrow, lastrown, firstcolu, lastcolu));
设置行高
//设置首行行高为25磅
sheet.getRow(0).setHeightInPoints(25);
设置列宽
//设置列宽自适应
sheet.autoSizeColumn(columnIndex, true);
//设置给定列宽, width in units of 1/256th of a character width (单位: 字符的1/256)
sheet.setColumnWidth(columnIndex, width);
本文地址:https://blog.csdn.net/weixin_44942995/article/details/112680793