1.引入依赖
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2.POI EXCEL文档结构类
HSSFWorkbook excel文档对象
HSSFSheet excel的sheet
HSSFRow excel的行
HSSFCell excel的单元格
HSSFFont excel字体
HSSFName 名称
HSSFDataFormat 日期格式
HSSFHeader sheet头
HSSFFooter sheet尾
HSSFCellStyle cell样式
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
3.POI EXCEL文档结构类 用法
(1)获取导入的.xls文件的工作簿
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:/test.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(i);
HSSFCell cell = row.getCell((short) j);
cellStyle = cell.getCellStyle();
(2)新建工作簿
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short)0);
cellStyle = wb.createCellStyle();
row.createCell((short)0).setCellStyle(cellStyle).setCellValue(1);
(3)获取行列数量
HSSFWorkbook wb= new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheetName");
wb.setSheetName(0,"updateName");
HSSFSheet sheet = wb.getSheetAt(0);
String sheetName = hssfWorkbook.getSheetAt(0).getSheetName();
int rowcount = sheet.getLastRowNum();
int cellNum = row.getLastCellNum();
sheet.getAllocatedRange().get("A1:E14").autoFitRows();
sheet.getAllocatedRange().get("A1:E14").autoFitColumns();
sheet.getAllocatedRange().autoFitRows();
sheet.getAllocatedRange().autoFitColumns();
CellRange cell = (CellRange) sheet.get("A1");
cell.getStyle().setShrinkToFit(true);
(4)设置行高列宽
sheet.setDefaultRowHeight((short) (2 * 256));
sheet.setDefaultColumnWidth(17);
sheet.setColumnWidth((short)0,(short)(2 * 256));
row.setHeight((short)12);
sheet.getAllocatedRange().get("A1:E14").autoFitRows();
sheet.getAllocatedRange().get("A1:E14").autoFitColumns();
sheet.getAllocatedRange().autoFitRows();
sheet.getAllocatedRange().autoFitColumns();
CellRange cell = (CellRange) sheet.get("A1");
cell.getStyle().setShrinkToFit(true);
(5)设置单元格内容和类型
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:ss");
LocalDateTime now = LocalDateTime.now();
row.createCell(0).setCellValue(df.format(now));
row.getCell(0).setCellValue(new Date());
cell.getNumericCellValue()、cell.getDateCellValue()、cell.getDateCellValue()、getBooleanCellValue
(6)合并单元格
CellRangeAddress cra = new CellRangeAddress(0, 1, 3, 4);
sheetAt.addMergedRegion(cra);
int mergedRegionNum = sheet.getNumMergedRegions()
(7)保存Excel文件
FileOutputStream fileOut = new FileOutputStream(path);
wb.write(fileOut);
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wb.write(bos);
(8)POI获取Excel单元格各种类型的值
4.POI EXCEL文档示例
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("物联网卡");
XSSFFont topFont = wb.createFont();
topFont.setBold(true);
topFont.setFontName("Arial");
topFont.setFontHeightInPoints((short) 11);
sheet.setDefaultColumnWidth(4);
sheetAt.setDefaultRowHeight(font.getFontHeight());
XSSFCellStyle topStyle = wb.createCellStyle();
topStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
topStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
topStyle.setAlignment(HorizontalAlignment.CENTER);
topStyle.setVerticalAlignment(VerticalAlignment.CENTER);
topStyle.setBorderLeft(BorderStyle.THIN);
topStyle.setBorderRight(BorderStyle.THIN);
topStyle.setBorderTop(BorderStyle.THIN);
topStyle.setBorderBottom(BorderStyle.THIN);
topStyle.setFont(topFont);
XSSFFont contentFont = wb.createFont();
contentFont.setFontName("Arial");
contentFont.setFontHeightInPoints((short) 11);
XSSFCellStyle firstStyle = wb.createCellStyle();
firstStyle.setAlignment(HorizontalAlignment.LEFT);
firstStyle.setVerticalAlignment(VerticalAlignment.CENTER);
firstStyle.setWrapped(true);
firstStyle.setBorderLeft(BorderStyle.THIN);
firstStyle.setBorderRight(BorderStyle.THIN);
firstStyle.setBorderTop(BorderStyle.THIN);
firstStyle.setBorderBottom(BorderStyle.THIN);
firstStyle.setFont(contentFont);
XSSFRow row = sheet.createRow(0);
row.createCell(0).setCellValue("加油站名称");
row.createCell(1).setCellValue("设备名称");
row.createCell(2).setCellValue("设备ID");
row.createCell(3).setCellValue("系统ID");
row.createCell(4).setCellValue("物联网卡ID");
row.createCell(5).setCellValue("信息更新时间");
XSSFRow row1 = sheet.createRow(1);
CellRangeAddress cra = new CellRangeAddress(1, 1, 0, 1);
sheet.addMergedRegion(cra);
row1.createCell(0).setCellValue("考核对象");
cra = new CellRangeAddress(1, 1, 2, 3);
sheet.addMergedRegion(cra);
row1.createCell(2).setCellValue("考核名称");
sheet.setColumnWidth(0, 10000);
sheet.setColumnWidth(1, 3500);
sheet.setColumnWidth(2, 3500);
sheet.setColumnWidth(3, 3500);
sheet.setColumnWidth(4, 7000);
sheet.setColumnWidth(5, 7000);
if (CollectionUtils.isNotEmpty(list)) {
int rowNum = 1;
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for (DeviceRelevantVO vo : list) {
row = sheet.getRow(rowNum);
if (row == null) {
row = sheet.createRow(rowNum);
}
row.createCell(0).setCellValue(vo.getGasStationName());
row.createCell(1).setCellValue(vo.getDeviceName());
row.createCell(2).setCellValue(vo.getDeviceId());
row.createCell(3).setCellValue(vo.getSystemId());
row.createCell(4).setCellValue(vo.getIccId());
row.createCell(5).setCellValue(df.format(vo.getUpdateTime()));
Integer cellWidth = sheetAt.getColumnWidth(10) * 6;
short fontHeight = topFont.getFontHeight();
double cellContentWidth = vo.getName().trim().getBytes().length * 256;
double stringNeedsRows = (double)cellContentWidth / cellWidth;
if(stringNeedsRows <= 1.0){
double stringNeedsHeight;
stringNeedsRows = 1.0;
stringNeedsHeight = fontHeight*1.5 * stringNeedsRows;
row4.setHeight((short)stringNeedsHeight);
}
rowNum++;
}
}
for (int i = 0; i <= sheetAt.getLastRowNum(); i++) {
Row row = sheetAt.getRow(i);
if (row == null) {
row = sheetAt.createRow(i);
}
int cellNum = row.getLastCellNum();
for (int j = 0; j <= cellNum; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
}
cell.setCellStyle(cellStyle);
if ((i == 0 && j == 0) || ((i == 2 || i == 8) && (j == 0 || j == 1))) {
cell.setCellStyle(topStyle);
}
}
}
ServletOutputStream out = null;
try {
String fileName = "物联网卡";
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
out = response.getOutputStream();
wb.write(out);
} catch (Exception e) {
log.error(e.getMessage(), e);
throw new ServiceException(ResponseStatus.FILE_DOWNLOAD_FAIL);
}finally {
if (out != null) {
try {
out.flush();
out.close();
}catch (Exception e){
log.error(e.getMessage(), e);
}
}
}
ByteArrayOutputStream bos = new ByteArrayOutputStream();
hssfWorkbook.write(bos);
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentLength(bos.toByteArray().length);
headers.setContentDispositionFormData("trainingCheck", "考核训练模板" + ".xls");
ResponseEntity<byte[]> responseEntity = new ResponseEntity<>(bos.toByteArray(), headers, HttpStatus.OK);
bos.close();
return responseEntity;
5. POI Excel打印设置
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("自定义sheet名字");
//这个是sheet缩放设置, 默认打印一页要true
sheet.setAutobreaks(true);
// 页边距(下)
sheet.setMargin(Sheet.BottomMargin,( double ) 0.5 );
// 页边距(左)
sheet.setMargin(Sheet.LeftMargin,( double ) 0.1 );
// 页边距(右)
sheet.setMargin(Sheet.RightMargin,( double ) 0.1 );
// 页边距(上)
sheet.setMargin(Sheet.TopMargin,( double ) 0.5 );
//设置打印页面为水平居中
sheet.setHorizontallyCenter(true);
//设置打印页面为垂直居中
sheet.setVerticallyCenter(true);
//使用POI输出Excel时打印页面
//启用“适合页面”打印选项的标志。(默认选择的是“将工作表调整为一页”)
sheet.setFitToPage(true);
XSSFPrintSetup printSetup = sheet.getPrintSetup();
// 打印方向,true:横向,false:纵向
printSetup.setLandscape(false);
//纸张大小,自选A4
printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
6. 实战 – 按行导出(无一对多的数据)
/**
* 请假记录流水导出
* @param list
* @return
*/
public static ResponseEntity<byte[]> leaveUserCarStepImport(List<LeaveUserCarStepVO> list) throws Exception{
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
//获取第一个工作簿
HSSFSheet sheetAt = hssfWorkbook.createSheet("Sheet1");
//设置表头样式
HSSFCellStyle topStyle = hssfWorkbook.createCellStyle();
topStyle.setAlignment(HorizontalAlignment.CENTER);
topStyle.setVerticalAlignment(VerticalAlignment.CENTER);
topStyle.setWrapText(true);
//字体
HSSFFont topFont = hssfWorkbook.createFont();
topFont.setBold(true);
topFont.setFontName(Constants.DownEnum.SONG_TI.getValue());
topStyle.setFont(topFont);
//设置内容样式
HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
//水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//自动换行
cellStyle.setWrapText(true);
//字体
HSSFFont cellFont = hssfWorkbook.createFont();
cellFont.setFontName(Constants.DownEnum.SONG_TI.getValue());
cellStyle.setFont(cellFont);
//表头 占两行 合并单元格
HSSFRow row0 = sheetAt.createRow(0);
CellRangeAddress cra = new CellRangeAddress(0, 1, 0, 0);
sheetAt.addMergedRegion(cra);
row0.createCell(0).setCellValue("序号");
cra = new CellRangeAddress(0, 1, 1, 3);
sheetAt.addMergedRegion(cra);
row0.createCell(1).setCellValue("单位名称");
cra = new CellRangeAddress(0, 1, 4, 5);
sheetAt.addMergedRegion(cra);
row0.createCell(4).setCellValue("申请人");
cra = new CellRangeAddress(0, 1, 6, 7);
sheetAt.addMergedRegion(cra);
row0.createCell(6).setCellValue("用车类型");
cra = new CellRangeAddress(0, 1, 8, 10);
sheetAt.addMergedRegion(cra);
row0.createCell(8).setCellValue("开始时间");
cra = new CellRangeAddress(0, 1, 11, 13);
sheetAt.addMergedRegion(cra);
row0.createCell(11).setCellValue("结束时间");
cra = new CellRangeAddress(0, 1, 14, 16);
sheetAt.addMergedRegion(cra);
row0.createCell(14).setCellValue("用车时长");
cra = new CellRangeAddress(0, 1, 17, 19);
sheetAt.addMergedRegion(cra);
row0.createCell(17).setCellValue("车牌号");
cra = new CellRangeAddress(0, 1, 20, 21);
sheetAt.addMergedRegion(cra);
row0.createCell(20).setCellValue("司机");
cra = new CellRangeAddress(0, 1, 22, 23);
sheetAt.addMergedRegion(cra);
row0.createCell(22).setCellValue("审批人");
cra = new CellRangeAddress(0, 1, 24, 26);
sheetAt.addMergedRegion(cra);
row0.createCell(24).setCellValue("同行人员");
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
//内容
for (int i = 0; i < list.size(); i++) {
int hangNum = i + 2;
HSSFRow row = sheetAt.getRow(hangNum);
if (row == null) {
row = sheetAt.createRow(hangNum);
}
LeaveUserCarStepVO vo = list.get(i);
if (vo == null) {
continue;
}
//序号
row.createCell(0).setCellValue(i+1);
//单位名称
cra = new CellRangeAddress(hangNum, hangNum, 1, 3);
sheetAt.addMergedRegion(cra);
row.createCell(1).setCellValue(vo.getCampName() == null ? "" : vo.getCampName().trim());
//申请人
cra = new CellRangeAddress(hangNum, hangNum, 4, 5);
sheetAt.addMergedRegion(cra);
row.createCell(4).setCellValue(vo.getEmployeeName() == null ? "" : vo.getEmployeeName().trim());
//用车类型
cra = new CellRangeAddress(hangNum, hangNum,6, 7);
sheetAt.addMergedRegion(cra);
row.createCell(6).setCellValue(vo.getUserCarType() == null ? "" : vo.getUserCarType().trim());
//开始时间
cra = new CellRangeAddress(hangNum, hangNum, 8, 10);
sheetAt.addMergedRegion(cra);
row.createCell(8).setCellValue(vo.getBeginTime() == null ? "" : df.format(vo.getBeginTime()));
//结束时间
cra = new CellRangeAddress(hangNum, hangNum, 11, 13);
sheetAt.addMergedRegion(cra);
row.createCell(11).setCellValue(vo.getEndTime() == null ? "" : df.format(vo.getEndTime()));
//用车时长
cra = new CellRangeAddress(hangNum, hangNum, 14, 16);
sheetAt.addMergedRegion(cra);
row.createCell(14).setCellValue(vo.getLeaveDuration() == null ? "" : vo.getLeaveDuration().trim());
//车牌号
cra = new CellRangeAddress(hangNum, hangNum, 17, 19);
sheetAt.addMergedRegion(cra);
row.createCell(17).setCellValue(vo.getLicensePlate() == null ? "" : vo.getLicensePlate().trim());
//司机
cra = new CellRangeAddress(hangNum, hangNum,20, 21);
sheetAt.addMergedRegion(cra);
row.createCell(20).setCellValue(vo.getDriverName() == null ? "" : vo.getDriverName().trim());
//审批人
cra = new CellRangeAddress(hangNum, hangNum,22, 23);
sheetAt.addMergedRegion(cra);
row.createCell(22).setCellValue(vo.getApprovalName() == null ? "" : vo.getApprovalName().trim());
//同行人员
cra = new CellRangeAddress(hangNum, hangNum, 24, 26);
sheetAt.addMergedRegion(cra);
row.createCell(24).setCellValue(vo.getTogetherEmployees() == null ? "" : vo.getTogetherEmployees().trim());
}
//单元格样式
for (int i = 0; i <= sheetAt.getLastRowNum(); i++) {
Row row = sheetAt.getRow(i);
if (row == null) {
row = sheetAt.createRow(i);
}
int cellNum = row.getLastCellNum();
for (int j = 0; j <= cellNum; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
}
cell.setCellStyle(cellStyle);
if (i == 0) {
cell.setCellStyle(topStyle);
}
}
}
//将excel转为byte输出
ByteArrayOutputStream bos = new ByteArrayOutputStream();
//将workbook转成byte数组
hssfWorkbook.write(bos);
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentLength(bos.toByteArray().length);
headers.setContentDispositionFormData("leaveUserCarStepImport", "leaveUserCarStepImport" + ".xls");
//response.setContentType(MIME)的作用是使客户端浏览器,区分不同种类的数据,
// 并根据不同的MIME调用浏览器内不同的程序嵌入模块来处理相应的数据。
ResponseEntity<byte[]> responseEntity = new ResponseEntity<>(bos.toByteArray(), headers, HttpStatus.OK);
bos.close();
return responseEntity;
}
7. 实战 - 按行读取word文档数据
try {
FileInputStream fis = new FileInputStream(new File("test.docx"));
XWPFDocument document = new XWPFDocument(fis);
List<XWPFParagraph> paragraphs = document.getParagraphs();
int i = 1;
for (XWPFParagraph paragraph : paragraphs) {
System.out.println("*****************段落:" + i);
System.out.println(paragraph.getText());
i++;
}
List<XWPFTable> tables = document.getTables();
for (XWPFTable table : tables) {
List<XWPFTableRow> rows = table.getRows();
for (XWPFTableRow row : rows) {
List<XWPFTableCell> cells = row.getTableCells();
for (XWPFTableCell cell : cells) {
System.out.println(cell.getText());
}
}
}
System.out.println(paragraphs);
fis.close();
} catch (IOException e) {
e.printStackTrace();
}