java excel 工具类_java读取excel的工具类-POI解析excel

importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.OutputStream;importjava.text.DecimalFormat;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.LinkedList;importjava.util.List;importorg.apache.commons.collections.CollectionUtils;importorg.apache.poi.hssf.usermodel.HSSFCellStyle;importorg.apache.poi.hssf.usermodel.HSSFDateUtil;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.CellValue;importorg.apache.poi.ss.usermodel.Font;importorg.apache.poi.ss.usermodel.FormulaEvaluator;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.ss.util.CellRangeAddress;importorg.apache.poi.ss.util.WorkbookUtil;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importcom.zkingsoft.common.tools.FileUtil;/*** excel工具类 提供读取和写入excel的功能

*

*@authorJIANGYOUYAO

* @email 935090232@qq.com

* @date 2017年12月20日*/

public classExcelUtil {/*** 标题样式*/

private final static String STYLE_HEADER = "header";/*** 表头样式*/

private final static String STYLE_TITLE = "title";/*** 数据样式*/

private final static String STYLE_DATA = "data";/*** 存储样式*/

private static final HashMap cellStyleMap = new HashMap<>();/*** 读取excel文件里面的内容 支持日期，数字，字符，函数公式，布尔类型

*

*@authorJIANGYOUYAO

* @email 935090232@qq.com

* @date 2017年12月20日

*@paramfile

*@paramrowCount

*@paramcolumnCount

*@return*@throwsFileNotFoundException

*@throwsIOException*/

public static ListreadExcel(File file, Integer rowCount, Integer columnCount)throwsFileNotFoundException, IOException {//根据后缀名称判断excel的版本

String extName =FileUtil.getFileExtName(file);

Workbook wb= null;if(ExcelVersion.V2003.getSuffix().equals(extName)) {

wb= new HSSFWorkbook(newFileInputStream(file));

}else if(ExcelVersion.V2007.getSuffix().equals(extName)) {

wb= new XSSFWorkbook(newFileInputStream(file));

}else{//无效后缀名称，这里之能保证excel的后缀名称，不能保证文件类型正确，不过没关系，在创建Workbook的时候会校验文件格式

throw new IllegalArgumentException("Invalid excel version");

}//开始读取数据

List sheetPOs = new ArrayList<>();//解析sheet

for (int i = 0; i < wb.getNumberOfSheets(); i++) {

Sheet sheet=wb.getSheetAt(i);

List> dataList = new ArrayList<>();

ExcelSheetPO sheetPO= newExcelSheetPO();

sheetPO.setSheetName(sheet.getSheetName());

sheetPO.setDataList(dataList);int readRowCount = 0;if (rowCount == null || rowCount >sheet.getPhysicalNumberOfRows()) {

readRowCount=sheet.getPhysicalNumberOfRows();

}else{

readRowCount=rowCount;

}//解析sheet 的行

for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) {

Row row=sheet.getRow(j);if (row == null) {continue;

}if (row.getFirstCellNum() < 0) {continue;

}int readColumnCount = 0;if (columnCount == null || columnCount >row.getLastCellNum()) {

readColumnCount= (int) row.getLastCellNum();

}else{

readColumnCount=columnCount;

}

List rowValue = new LinkedList();//解析sheet 的列

for (int k = 0; k < readColumnCount; k++) {

Cell cell=row.getCell(k);

rowValue.add(getCellValue(wb, cell));

}

dataList.add(rowValue);

}

sheetPOs.add(sheetPO);

}returnsheetPOs;

}private staticObject getCellValue(Workbook wb, Cell cell) {

Object columnValue= null;if (cell != null) {

DecimalFormat df= new DecimalFormat("0");//格式化 number//String//字符

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//格式化日期字符串

DecimalFormat nf = new DecimalFormat("0.00");//格式化数字

switch(cell.getCellType()) {caseCell.CELL_TYPE_STRING:

columnValue=cell.getStringCellValue();break;caseCell.CELL_TYPE_NUMERIC:if ("@".equals(cell.getCellStyle().getDataFormatString())) {

columnValue=df.format(cell.getNumericCellValue());

}else if ("General".equals(cell.getCellStyle().getDataFormatString())) {

columnValue=nf.format(cell.getNumericCellValue());

}else{

columnValue=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));

}break;caseCell.CELL_TYPE_BOOLEAN:

columnValue=cell.getBooleanCellValue();break;caseCell.CELL_TYPE_BLANK:

columnValue= "";break;caseCell.CELL_TYPE_FORMULA://格式单元格

FormulaEvaluator evaluator =wb.getCreationHelper().createFormulaEvaluator();

evaluator.evaluateFormulaCell(cell);

CellValue cellValue=evaluator.evaluate(cell);

columnValue=cellValue.getNumberValue();break;default:

columnValue=cell.toString();

}

}returncolumnValue;

}/*** 在硬盘上写入excel文件

*

*@authorJIANGYOUYAO

* @email 935090232@qq.com

* @date 2017年12月20日

*@paramversion

*@paramexcelSheets

*@paramfilePath

*@throwsIOException*/

public static void createWorkbookAtDisk(ExcelVersion version, ListexcelSheets, String filePath)throwsIOException {

FileOutputStream fileOut= newFileOutputStream(filePath);

createWorkbookAtOutStream(version, excelSheets, fileOut,true);

}/*** 把excel表格写入输出流中，输出流会被关闭

*

*@authorJIANGYOUYAO

* @email 935090232@qq.com

* @date 2017年12月20日

*@paramversion

*@paramexcelSheets

*@paramoutStream

*@paramcloseStream

* 是否关闭输出流

*@throwsIOException*/

public static void createWorkbookAtOutStream(ExcelVersion version, ListexcelSheets,

OutputStream outStream,boolean closeStream) throwsIOException {if(CollectionUtils.isNotEmpty(excelSheets)) {

Workbook wb=createWorkBook(version, excelSheets);

wb.write(outStream);if(closeStream) {

outStream.close();

}

}

}private static Workbook createWorkBook(ExcelVersion version, ListexcelSheets) {

Workbook wb=createWorkbook(version);for (int i = 0; i < excelSheets.size(); i++) {

ExcelSheetPO excelSheetPO=excelSheets.get(i);if (excelSheetPO.getSheetName() == null) {

excelSheetPO.setSheetName("sheet" +i);

}//过滤特殊字符

Sheet tempSheet =wb.createSheet(WorkbookUtil.createSafeSheetName(excelSheetPO.getSheetName()));

buildSheetData(wb, tempSheet, excelSheetPO, version);

}returnwb;

}private static voidbuildSheetData(Workbook wb, Sheet sheet, ExcelSheetPO excelSheetPO, ExcelVersion version) {

sheet.setDefaultRowHeight((short) 400);

sheet.setDefaultColumnWidth((short) 10);

createTitle(sheet, excelSheetPO, wb, version);

createHeader(sheet, excelSheetPO, wb, version);

createBody(sheet, excelSheetPO, wb, version);

}private static voidcreateBody(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) {

List> dataList =excelSheetPO.getDataList();for (int i = 0; i < dataList.size() && i < version.getMaxRow(); i++) {

List values =dataList.get(i);

Row row= sheet.createRow(2 +i);for (int j = 0; j < values.size() && j < version.getMaxColumn(); j++) {

Cell cell=row.createCell(j);

cell.setCellStyle(getStyle(STYLE_DATA, wb));

cell.setCellValue(values.get(j).toString());

}

}

}private static voidcreateHeader(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) {

String[] headers=excelSheetPO.getHeaders();

Row row= sheet.createRow(1);for (int i = 0; i < headers.length && i < version.getMaxColumn(); i++) {

Cell cellHeader=row.createCell(i);

cellHeader.setCellStyle(getStyle(STYLE_HEADER, wb));

cellHeader.setCellValue(headers[i]);

}

}private static voidcreateTitle(Sheet sheet, ExcelSheetPO excelSheetPO, Workbook wb, ExcelVersion version) {

Row titleRow= sheet.createRow(0);

Cell titleCel= titleRow.createCell(0);

titleCel.setCellValue(excelSheetPO.getTitle());

titleCel.setCellStyle(getStyle(STYLE_TITLE, wb));//限制最大列数

int column = excelSheetPO.getDataList().size() > version.getMaxColumn() ?version.getMaxColumn()

: excelSheetPO.getDataList().size();

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, column - 1));

}private staticCellStyle getStyle(String type, Workbook wb) {if(cellStyleMap.containsKey(type)) {returncellStyleMap.get(type);

}//生成一个样式

CellStyle style =wb.createCellStyle();

style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

style.setBorderRight(HSSFCellStyle.BORDER_THIN);

style.setBorderTop(HSSFCellStyle.BORDER_THIN);

style.setWrapText(true);if (STYLE_HEADER ==type) {

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

Font font=wb.createFont();

font.setFontHeightInPoints((short) 16);

font.setBoldweight(Font.BOLDWEIGHT_BOLD);

style.setFont(font);

}else if (STYLE_TITLE ==type) {

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

Font font=wb.createFont();

font.setFontHeightInPoints((short) 18);

font.setBoldweight(Font.BOLDWEIGHT_BOLD);

style.setFont(font);

}else if (STYLE_DATA ==type) {

style.setAlignment(HSSFCellStyle.ALIGN_LEFT);

Font font=wb.createFont();

font.setFontHeightInPoints((short) 12);

style.setFont(font);

}

cellStyleMap.put(type, style);returnstyle;

}private staticWorkbook createWorkbook(ExcelVersion version) {switch(version) {caseV2003:return newHSSFWorkbook();caseV2007:return newXSSFWorkbook();

}return null;

}

}