POI

LocalDateTime、String、Date

1.LocalDateTime获取毫秒数

//获取秒数
Long second = LocalDateTime.now().toEpochSecond(ZoneOffset.of("+8"));
//获取毫秒数
Long milliSecond = LocalDateTime.now().toInstant(ZoneOffset.of("+8")).toEpochMilli();

2.LocalDateTime与String互转

//时间转字符串格式化
 DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSS");
 String dateTime = LocalDateTime.now(ZoneOffset.of("+8")).format(formatter);
 
//字符串转时间
String dateTimeStr = "2018-07-28 14:11:15";
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
LocalDateTime dateTime = LocalDateTime.parse(dateTimeStr, df);

3.Date与LocalDateTime互转

//将java.util.Date 转换为java8 的java.time.LocalDateTime,默认时区为东8区
public static LocalDateTime dateConvertToLocalDateTime(Date date) {
    return date.toInstant().atOffset(ZoneOffset.of("+8")).toLocalDateTime();
}
 
//将java8 的 java.time.LocalDateTime 转换为 java.util.Date，默认时区为东8区
public static Date localDateTimeConvertToDate(LocalDateTime localDateTime) {
    return Date.from(localDateTime.toInstant(ZoneOffset.of("+8")));
}
 
/** 测试转换是否正确 */
@Test
public void testDateConvertToLocalDateTime() {
    Date date = DateUtils.parseDate("2018-08-01 21:22:22", DateUtils.DATE_YMDHMS);
    LocalDateTime localDateTime = DateUtils.dateConvertToLocalDateTime(date);
    Long localDateTimeSecond = localDateTime.toEpochSecond(ZoneOffset.of("+8"));
    Long dateSecond = date.toInstant().atOffset(ZoneOffset.of("+8")).toEpochSecond();
    Assert.assertTrue(dateSecond.equals(localDateTimeSecond));
}

读 resource下文件

第一种：

ClassPathResource classPathResource = new ClassPathResource("excleTemplate/test.xlsx");
InputStream inputStream =classPathResource.getInputStream();

第二种：

InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("excleTemplate/test.xlsx");

第三种：

InputStream inputStream = this.getClass().getResourceAsStream("/excleTemplate/test.xlsx");

第四种：

File file = ResourceUtils.getFile("classpath:excleTemplate/test.xlsx");
InputStream inputStream = new FileInputStream(file);

springboot获取resources路径（相对路径

第一种

System.getProperty("user.dir");

第二种

File directory = new File("src/main/resources");
String courseFile = directory.getCanonicalPath();这种方式也能获取到一样的结果

第三种

request.getServletContext().getRealPath("/")//获得的路径不是项目路径，而是c盘下一个tomcat目录路径）

POI-Excel

时间单元格

Creating Date Cells

Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow(0);
// Create a cell and put a date value in it.  The first cell is not styled
// as a date.
Cell cell = row.createCell(0);
cell.setCellValue(new Date());
// we style the second cell as a date (and time).  It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but other cells.
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
    createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
//you can also set date as java.util.Calendar
cell = row.createCell(2);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
// Write the output to a file
try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
    wb.write(fileOut);
}

水平及垂直对齐

alignment options

水平对齐（HorizontalAlignment ）和垂直对齐（VerticalAlignment） ==》枚举

Cell cell = row.createCell(column);
CellStyle cellStyle = wb.createCellStyle();
// GENERAL,LEFT,CENTER,RIGHT,FILL,JUSTIFY,CENTER_SELECTION,DISTRIBUTED;
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// TOP,CENTER,BOTTOM,JUSTIFY,DISTRIBUTED;
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
cell.setCellStyle(cellStyle);

边框及颜色

Working with borders

设置边框及边框颜色

Cell cell = row.createCell(column);

CellStyle style = wb.createCellStyle();
style.setBorderBottom(BorderStyle.THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(BorderStyle.THIN);
style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
style.setBorderRight(BorderStyle.THIN);
style.setRightBorderColor(IndexedColors.BLUE.getIndex());
style.setBorderTop(BorderStyle.MEDIUM_DASHED);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());

cell.setCellStyle(cellStyle);

迭代器

Iterate over rows and cells

情况一：迭代器，可以对workbook中的任意一个cell进行样式等的操作

for (Sheet sheet : wb) {
	for (Row row : sheet) {
		for (Cell cell : row) {
			CellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HorizontalAlignment.CENTER);
			cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
            cellStyle.setBorderBottom(BorderStyle.THIN); 
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cell.setCellStyle(cellStyle);
		}
	}
}

情况二：getCell(int，MissingCellPolicy)

//MissingCellPolicy{RETURN_NULL_AND_BLANK,RETURN_BLANK_AS_NULL,CREATE_NULL_AS_BLANK;}
Cell cell = row.getCell(0, Row.RETURN_BLANK_AS_NULL);

获取单元格中的内容

Getting the cell contents

// import org.apache.poi.ss.usermodel.*;
DataFormatter formatter = new DataFormatter();
Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
    for (Cell cell : row) {
        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
        System.out.print(cellRef.formatAsString());
        System.out.print(" - ");
        // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
        String text = formatter.formatCellValue(cell);
        System.out.println(text);
        // Alternatively, get the value and format it yourself
        switch (cell.getCellType()) {
            case CellType.STRING:
                System.out.println(cell.getRichStringCellValue().getString());
                break;
            case CellType.NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.println(cell.getDateCellValue());
                } else {
                    System.out.println(cell.getNumericCellValue());
                }
                break;
            case CellType.BOOLEAN:
                System.out.println(cell.getBooleanCellValue());
                break;
            case CellType.FORMULA:
                System.out.println(cell.getCellFormula());
                break;
            case CellType.BLANK:
                System.out.println();
                break;
            default:
                System.out.println();
        }
    }
}

ExcelExtractor 文本提取

ClassPathResource classPathResource = new ClassPathResource("static/hulei.xls");
InputStream inputStream = classPathResource.getInputStream();
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inputStream));
ExcelExtractor extractor = new ExcelExtractor(wb);
extractor.setFormulasNotResults(true);
extractor.setIncludeSheetNames(false);
String text = extractor.getText();
System.out.println(text);

Fills and colors 填充和颜色

合并单元格

使用字体