引入
Excel 的两种形式:
- Excel2003 : Excel2003是一个特有的二进制格式,其核心结构是复合文档类型的结构,存储数据量较小,
后缀:xls
- Excel2007 :Excel2007的核心结构是XML类型的结构,采用的是基于xml的压缩方式,使其占用的空间更小,效率更高,
后缀:xlsx
poi操作文档结构:
HSSF
提供读写xls
格式档案的功能。XSSF
提供读写xlsx
格式档案的功能。
常用API:
- HSSFWorkbook(工作簿)2003版
- XSSFWorkbook(工作簿) 2007版
- Sheet - Excel的表单
- Row - Excel的行
- Cell - Excel的格子单元
- Font - Excel的字体
- CellStyle - 格子单元样式
代码:
案例一:创建一个最简单的表格
@Test
public void test() throws IOException {
// 创建workbook工作簿
Workbook workbook = new XSSFWorkbook();
// 创建表单Sheet
workbook.createSheet("testSheet");
// 文件流输入
FileOutputStream out = new FileOutputStream(new File("C:\\test\\test.xlsx"));
// 文件写出
workbook.write(out);
// 关闭流
out.close();
}
案例二:往表格中写入一个数据
@Test
public void test2() throws IOException {
Workbook wb = new XSSFWorkbook();
// 在工作簿里面创建sheet
Sheet sheet = wb.createSheet("testSheet");
// 在sheet里 创建行对象 下标从0开始
Row row = sheet.createRow(0);
// 在行里创建单元格Cell对象,下标从0开始
Cell cell = row.createCell(0);
// 设置单元格内容
cell.setCellValue("This is Cell Value .");
// 文件流输入
FileOutputStream out = new FileOutputStream(new File("C:\\test\\test2.xlsx"));
// 文件写出
wb.write(out);
// 关闭流
out.close();
}
案例三 : 我们在二的基础上 给这个单元格添加上样式
@Test
public void test2() throws IOException {
Workbook wb = new XSSFWorkbook();
// 创建单元格样式对象 注意:(是通过工作簿设置的)
CellStyle cellStyle = wb.createCellStyle();
// 设置边框 上下左右四条边框
cellStyle.setBorderBottom(BorderStyle.DOUBLE);
cellStyle.setBorderLeft(BorderStyle.DOUBLE);
cellStyle.setBorderTop(BorderStyle.DOUBLE);
cellStyle.setBorderRight(BorderStyle.DOUBLE);
// 设置字体
Font font = wb.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 32);
// 把我们的字体样式设置给单元格对象
cellStyle.setFont(font);
// 在工作簿里面创建sheet
Sheet sheet = wb.createSheet("testSheet");
// 在sheet里 创建行对象 下标从0开始
Row row = sheet.createRow(0);
// 在行里创建单元格Cell对象,下标从0开始
Cell cell = row.createCell(0);
// 最后把单元格样式对象设置给单元格对象
cell.setCellStyle(cellStyle);
// 设置单元格内容
cell.setCellValue("This is Cell Value .");
// 文件流输入
FileOutputStream out = new FileOutputStream(new File("C:\\test\\test2.xlsx"));
// 文件写出
wb.write(out);
// 关闭流
out.close();
}
在二基础上新增的内容
简单案例:
效果图如下:
@Test
public void createExcel() throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("test");
// 自适应列宽 只对数字与字母有效
// sheet.autoSizeColumn();
// 设置列宽
sheet.setColumnWidth(0, 12 * 256);
sheet.setColumnWidth(1, 16 * 256);
sheet.setColumnWidth(2, 16 * 256);
sheet.setColumnWidth(3, 37 * 256);
sheet.setColumnWidth(4, 37 * 256);
// 设置表头样式
CellStyle cellHeardStyle = workbook.createCellStyle();
// 设置边框
// cellHeardStyle.setBorderLeft(BorderStyle.THICK);
Font font = workbook.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 18);
font.setBold(true);
cellHeardStyle.setFont(font);
// 水平居中对齐
cellHeardStyle.setAlignment(HorizontalAlignment.CENTER);
// 垂直居中
cellHeardStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 表头部分
String[] tableHeard = new String[]{"id", "账号", "姓名", "邮箱", "创建时间"};
XSSFRow heardRow = sheet.createRow(0);
for (int i = 0; i < tableHeard.length; i++) {
XSSFCell cell = heardRow.createCell(i);
cell.setCellValue(tableHeard[i]);
if (i % 2 == 0) {
cellHeardStyle.setFillForegroundColor(IndexedColors.PINK1.getIndex());
} else {
cellHeardStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex());
}
cellHeardStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(cellHeardStyle);
}
List<Admin> list = adminDao.selectList("");
CellStyle cellStyle = getCellStyle(workbook);
// 自动换行
cellStyle.setWrapText(true);
for (int i = heardRow.getRowNum() + 1; i < list.size(); i++) {
XSSFRow row = sheet.createRow(i);
Admin admin = list.get(i);
for (int j = 0; j < 5; j++) {
XSSFCell cell = row.createCell(0);
cell.setCellValue(admin.getId());
cell.setCellStyle(cellStyle);
cell = row.createCell(1);
cell.setCellValue(admin.getAccount());
cell.setCellStyle(cellStyle);
cell = row.createCell(2);
cell.setCellValue(admin.getName());
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellValue(admin.getEmail());
cell.setCellStyle(cellStyle);
cell = row.createCell(4);
cell.setCellValue(admin.getCreateDateText());
cell.setCellStyle(cellStyle);
}
}
// 文件流输入
FileOutputStream out = new FileOutputStream(new File("C:\\test\\test3.xlsx"));
// 文件写出
workbook.write(out);
// 关闭流
out.close();
}
private CellStyle getCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
Font font = workbook.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 14);
cellStyle.setFont(font);
return cellStyle;
}
通过自定义模板导出数据
@Test
public void createExcel2() throws IOException, InvalidFormatException {
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("C:\\test\\test3.xlsx"));
XSSFSheet sheet = workbook.getSheetAt(0);
// 第一种style
XSSFRow row2 = sheet.getRow(2);
short row2LastCellNum = row2.getLastCellNum();
CellStyle[] row2Styles = new CellStyle[row2LastCellNum];
for (int i = 0; i < row2Styles.length; i++) {
Cell cell = row2.getCell(i);
row2Styles[i] = cell.getCellStyle();
}
// 第二种style
XSSFRow row3 = sheet.getRow(3);
short row3LastCellNum = row3.getLastCellNum();
CellStyle[] row3Styles = new CellStyle[row3LastCellNum];
for (int i = 0; i < row3Styles.length; i++) {
row3Styles[i] = row3.getCell(i).getCellStyle();
}
List<Admin> list = adminDao.selectList("");
for (int i = 0; i < list.size(); i++) {
XSSFRow row = sheet.createRow(i + 4);
Admin admin = list.get(i);
if (i % 2 == 0) {
for (int j = 0; j < row2Styles.length; j++) {
Cell cell = row.createCell(0);
cell.setCellStyle(row2Styles[0]);
cell.setCellValue(admin.getId());
cell = row.createCell(1);
cell.setCellStyle(row2Styles[1]);
cell.setCellValue(admin.getAccount());
cell = row.createCell(2);
cell.setCellStyle(row2Styles[2]);
cell.setCellValue(admin.getName());
cell = row.createCell(3);
cell.setCellStyle(row2Styles[3]);
cell.setCellValue(admin.getEmail());
cell = row.createCell(4);
cell.setCellStyle(row2Styles[4]);
cell.setCellValue(admin.getCreateDateText());
}
} else {
for (int j = 0; j < row3Styles.length; j++) {
Cell cell = row.createCell(0);
cell.setCellStyle(row3Styles[0]);
cell.setCellValue(admin.getId());
cell = row.createCell(1);
cell.setCellStyle(row3Styles[1]);
cell.setCellValue(admin.getAccount());
cell = row.createCell(2);
cell.setCellStyle(row3Styles[2]);
cell.setCellValue(admin.getName());
cell = row.createCell(3);
cell.setCellStyle(row3Styles[3]);
cell.setCellValue(admin.getEmail());
cell = row.createCell(4);
cell.setCellStyle(row3Styles[4]);
cell.setCellValue(admin.getCreateDateText());
}
}
}
// 文件流输入
FileOutputStream out = new FileOutputStream(new File("C:\\test\\test4.xlsx"));
// 文件写出
workbook.write(out);
// 关闭流
out.close();
}