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);