下载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<String, HSSFCellStyle> styles;
// 行番号从2开始
private int rowNum = 2;
public HSSFWorkbook getVacationExcel(List<Vacation> vacations) {
initStyles();
initHeader();
setContent(vacations);
return workbook;
}
/**
* 表的内容
*/
private void setContent(List<Vacation> 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<String, HSSFCellStyle>();
// 设置列宽度
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);
读取结果
网友评论