poi 宋体_POI读写EXCEL文件

下载jar包并导入

可以从官网下载。

常用类

名称

描述

HSSFWorkbook

工作簿，即一个excel

HSSFSheet

sheet

HSSFRow

一行

HSSFCell

一个格子单元

HSSFCellStyle

样式

HSSFFont

字体

导出excel步骤

// 创建文档对象

HSSFWorkbook wb = new HSSFWorkbook();

// 添加sheet

HSSFSheet sheet = wb.createSheet("sheetName");

// 创建行

HSSFRow row = sheet.createRow(0);

// 创建列

HSSFCell cell = row.createCell(0);

cell.setCellValue("hello world!");

// 输出文件

FileOutputStream outputStream = new FileOutputStream(new File("excelName.xls"));

wb.write(outputStream);

outputStream.flush();

简单示例

完整示例

public class WriteExcel {

// 创建workbook工作薄

private HSSFWorkbook workbook = new HSSFWorkbook();

// 创建sheet

private HSSFSheet sheet = workbook.createSheet("年假汇总表");

// 创建式样集合

private static Map styles;

// 行番号从2开始

private int rowNum = 2;

public HSSFWorkbook getVacationExcel(List vacations) {

initStyles();

initHeader();

setContent(vacations);

return workbook;

}

/**

* 表的内容

*/

private void setContent(List vacations) {

for (Vacation vacation : vacations) {

HSSFRow rowx = sheet.createRow(rowNum);

HSSFCell cx0 = rowx.createCell(0);

cx0.setCellValue(vacation.getCompany());

cx0.setCellStyle(styles.get("textcenterstyle"));

HSSFCell cx1 = rowx.createCell(1);

cx1.setCellValue(vacation.getDepartment());

cx1.setCellStyle(styles.get("textcenterstyle"));

HSSFCell cx2 = rowx.createCell(2);

cx2.setCellValue(vacation.getEmployeeId());

cx2.setCellStyle(styles.get("textcenterstyle"));

HSSFCell cx3 = rowx.createCell(3);

cx3.setCellValue(vacation.getEmployName());

cx3.setCellStyle(styles.get("textcenterstyle"));

HSSFCell cx4 = rowx.createCell(4);

cx4.setCellValue(vacation.getHour());

cx4.setCellStyle(styles.get("textnumstyle"));

rowNum++;

}

}

/**

* 初始化style

*/

private void initStyles() {

styles = new HashMap();

// 设置列宽度

sheet.setDefaultColumnWidth(20);

// 单独设置列宽

// sheet.setColumnWidth(0, 7000);

// 设置字体黑体 大小 加粗

HSSFFont headfont = workbook.createFont();

headfont.setFontName("黑体");

headfont.setFontHeightInPoints((short) 15);

headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// 设置字体宋体 大小 加粗

HSSFFont columnHeadFont = workbook.createFont();

columnHeadFont.setFontName("宋体");

columnHeadFont.setFontHeightInPoints((short) 10);

columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

// 标题样式

HSSFCellStyle headstyle = workbook.createCellStyle();

// 设置字体

headstyle.setFont(headfont);

// 左右居中

headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 上下居中

headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

styles.put("headstyle", headstyle);

// 设置表头样式 上下左右居中 有边框

HSSFCellStyle titlestyle = workbook.createCellStyle();

titlestyle.setFont(columnHeadFont);

// 左右居中

titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 上下居中

titlestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

// 下边框

titlestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

// 左边框

titlestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

// 上边框

titlestyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

// 右边框

titlestyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

// 自动换行

titlestyle.setWrapText(true);

styles.put("titlestyle", titlestyle);

// 设置text样式 有边框 上下居中，文本居右

HSSFCellStyle textcenterstyle = workbook.createCellStyle();

// 上下居中

textcenterstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

// 下边框

textcenterstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

// 左边框

textcenterstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

// 上边框

textcenterstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

// 右边框

textcenterstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

styles.put("textcenterstyle", textcenterstyle);

// 设置text样式 有边框 数字靠右

HSSFCellStyle textnumstyle = workbook.createCellStyle();

// 靠右

textnumstyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

// 下边框

textnumstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

// 左边框

textnumstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

// 上边框

textnumstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

// 右边框

textnumstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

styles.put("textnumstyle", textnumstyle);

}

/**

* 初始化标题、表头

*/

private void initHeader() {

// 创建行

HSSFRow row0 = sheet.createRow(0);

HSSFRow row1 = sheet.createRow(1);

// 创建列 标题

HSSFCell cell00 = row0.createCell(0);

cell00.setCellValue(new HSSFRichTextString("年假汇总表"));

cell00.setCellStyle(styles.get("headstyle"));

// 合并单元格

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

// 表头

HSSFCell c10 = row1.createCell(0);

c10.setCellValue(new HSSFRichTextString("公司"));

c10.setCellStyle(styles.get("titlestyle"));

HSSFCell c11 = row1.createCell(1);

c11.setCellValue(new HSSFRichTextString("部门"));

c11.setCellStyle(styles.get("titlestyle"));

HSSFCell c12 = row1.createCell(2);

c12.setCellValue(new HSSFRichTextString("员工编号"));

c12.setCellStyle(styles.get("titlestyle"));

HSSFCell c13 = row1.createCell(3);

c13.setCellValue(new HSSFRichTextString("员工姓名"));

c13.setCellStyle(styles.get("titlestyle"));

HSSFCell c14 = row1.createCell(4);

c14.setCellValue(new HSSFRichTextString("已休年假(H)"));

c14.setCellStyle(styles.get("titlestyle"));

}

}

// 调用

// 得到表

HSSFWorkbook workbook = writeExcel.getVacationExcel(vacations);

// 输出excel

FileOutputStream outputStream = new FileOutputStream(new File("年假汇总表.xls"));

workbook.write(outputStream);

outputStream.flush();

image.png

读取数据

// 读取

HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));

// 获取sheet

HSSFSheet sheet = wb.getSheetAt(0);

// 获取sheet名称

String sheetName = sheet.getSheetName();

// 获取第一行

HSSFRow row = sheet.getRow(1);

// 读取第一行第一列的内容

HSSFCell cell = row.getCell(0);

System.out.println(sheetName);

System.out.println(cell);

读取结果