packagejavax.utils;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.InputStream;importjava.io.OutputStream;importjava.math.BigDecimal;importjava.text.DateFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.DateUtil;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;/*** Excel 工具类(兼容xls和xlsx)
*
*@authorLogan
*@version1.0.0
* @createDate 2019-03-07
**/
public classExcelUtils {private static final String XLS = "xls";private static final String XLSX = "xlsx";private static final DateFormat FORMAT = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");/*** 输出数据到自定义模版的Excel输出流
*
*@paramexcelTemplate 自定义模版文件
*@paramdata 数据
*@paramoutputStream Excel输出流
*@throwsIOException 错误时抛出异常，由调用者处理*/
public static void writeDataToTemplateOutputStream(File excelTemplate, List> data, OutputStream outputStream) throwsIOException {
Workbook book=ExcelUtils.getWorkbookFromExcel(excelTemplate);
ExcelUtils.writeDataToWorkbook(null, data, book, 0);
ExcelUtils.writeWorkbookToOutputStream(book, outputStream);
}/*** 从Excel文件获取Workbook对象
*
*@paramexcelFile Excel文件
*@returnWorkbook对象
*@throwsIOException 错误时抛出异常，由调用者处理*/
public static Workbook getWorkbookFromExcel(File excelFile) throwsIOException {try(
InputStream inputStream= newFileInputStream(excelFile);
) {if(excelFile.getName().endsWith(XLS)) {return newHSSFWorkbook(inputStream);
}else if(excelFile.getName().endsWith(XLSX)) {return newXSSFWorkbook(inputStream);
}else{throw new IOException("文件类型错误");
}
}
}/*** 把Workbook对象内容输出到Excel文件
*
*@parambook Workbook对象
*@paramfile Excel文件
*@throwsFileNotFoundException 找不到文件异常，文件已创建，实际不存在该异常
*@throwsIOException 输入输出异常*/
public static void writeWorkbookToFile(Workbook book, File file) throwsFileNotFoundException, IOException {if (!file.exists()) {if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
file.createNewFile();
}try(
OutputStream outputStream= newFileOutputStream(file);
) {
writeWorkbookToOutputStream(book, outputStream);
}
}/*** 把Workbook对象输出到Excel输出流
*
*@parambook Workbook对象
*@paramoutputStream Excel输出流
*@throwsIOException 错误时抛出异常，由调用者处理*/
public static void writeWorkbookToOutputStream(Workbook book, OutputStream outputStream) throwsIOException {
book.write(outputStream);
}/*** 输出数据到Workbook对象中指定页码
*
*@paramtitle 标题，写在第一行，可传null
*@paramdata 数据
*@parambook Workbook对象
*@parampage 输出数据到Workbook指定页码的页面数*/
public static void writeDataToWorkbook(List title, List> data, Workbook book, intpage) {
Sheet sheet=book.getSheetAt(page);
Row row= null;
Cell cell= null;//设置表头
if (null != title && !title.isEmpty()) {
row= sheet.getRow(0);if (null ==row) {
row= sheet.createRow(0);
}for (int i = 0; i < title.size(); i++) {
cell=row.getCell(i);if (null ==cell) {
cell=row.createCell(i);
}
cell.setCellValue(title.get(i));
}
}
List rowData = null;for (int i = 0; i < data.size(); i++) {
row= sheet.getRow(i + 1);if (null ==row) {
row= sheet.createRow(i + 1);
}
rowData=data.get(i);if (null ==rowData) {continue;
}for (int j = 0; j < rowData.size(); j++) {
cell=row.getCell(j);if (null ==cell) {
cell=row.createCell(j);
}
setValue(cell, rowData.get(j));
}
}
}/*** 读取Excel文件第一页
*
*@parampathname 文件路径名
*@return第一页数据集合
*@throwsIOException 错误时抛出异常，由调用者处理*/
public static List> readExcelFirstSheet(String pathname) throwsIOException {
File file= newFile(pathname);returnreadExcelFirstSheet(file);
}/*** 读取Excel文件第一页
*
*@paramfile Excel文件
*@return第一页数据集合
*@throwsIOException 错误时抛出异常，由调用者处理*/
public static List> readExcelFirstSheet(File file) throwsIOException {try(
InputStream inputStream= newFileInputStream(file);
) {if(file.getName().endsWith(XLS)) {returnreadXlsFirstSheet(inputStream);
}else if(file.getName().endsWith(XLSX)) {returnreadXlsxFirstSheet(inputStream);
}else{throw new IOException("文件类型错误");
}
}
}/*** 读取xls格式Excel文件第一页
*
*@paraminputStream Excel文件输入流
*@return第一页数据集合
*@throwsIOException 错误时抛出异常，由调用者处理*/
public static List> readXlsFirstSheet(InputStream inputStream) throwsIOException {
Workbook workbook= newHSSFWorkbook(inputStream);returnreadExcelFirstSheet(workbook);
}/*** 读取xlsx格式Excel文件第一页
*
*@paraminputStream Excel文件输入流
*@return第一页数据集合
*@throwsIOException 错误时抛出异常，由调用者处理*/
public static List> readXlsxFirstSheet(InputStream inputStream) throwsIOException {
Workbook workbook= newXSSFWorkbook(inputStream);returnreadExcelFirstSheet(workbook);
}/*** 读取Workbook第一页
*
*@parambook Workbook对象
*@return第一页数据集合*/
public static List>readExcelFirstSheet(Workbook book) {return readExcel(book, 0);
}/*** 读取指定页面的Excel
*
*@parambook Workbook对象
*@parampage 页码
*@return指定页面数据集合*/
public static List> readExcel(Workbook book, intpage) {
List> list = new ArrayList<>();
Sheet sheet=book.getSheetAt(page);for (int i = 0; i <= sheet.getLastRowNum(); i++) {
Row row=sheet.getRow(i);//如果当前行为空，则加入空，保持行号一致
if (null ==row) {
list.add(null);continue;
}
List columns = new ArrayList<>();for (int j = 0; j < row.getLastCellNum(); j++) {
Cell cell=row.getCell(j);
columns.add(getValue(cell));
}
list.add(columns);
}returnlist;
}/*** 解析单元格中的值
*
*@paramcell 单元格
*@return单元格内的值*/
private staticObject getValue(Cell cell) {if (null ==cell) {return null;
}
Object value= null;switch(cell.getCellType()) {caseBOOLEAN:
value=cell.getBooleanCellValue();break;caseNUMERIC://日期类型，转换为日期
if(DateUtil.isCellDateFormatted(cell)) {
value=cell.getDateCellValue();
}//数值类型
else{//默认返回double，创建BigDecimal返回准确值
value = newBigDecimal(cell.getNumericCellValue());
}break;default:
value=cell.toString();break;
}returnvalue;
}/*** 设置单元格值
*
*@paramcell 单元格
*@paramvalue 值*/
private static voidsetValue(Cell cell, Object value) {if (null ==cell) {return;
}if (null ==value) {
cell.setCellValue((String)null);
}else if (value instanceofBoolean) {
cell.setCellValue((Boolean) value);
}else if (value instanceofDate) {
cell.setCellValue(FORMAT.format((Date) value));
}else if (value instanceofDouble) {
cell.setCellValue((Double) value);
}else{
cell.setCellValue(value.toString());
}
}
}
486
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
