java实现excel的导入_Java如何实现导入Excel功能，亲测有用！

packagecom.ilas.bookcase.common.utils;import org.apache.poi.hssf.usermodel.*;importorg.apache.poi.ss.usermodel.Cell;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.xssf.usermodel.XSSFWorkbook;importjava.io.IOException;importjava.io.InputStream;importjava.io.OutputStream;importjava.text.SimpleDateFormat;importjava.util.ArrayList;importjava.util.Date;importjava.util.List;public classExcelUtil {public static void createExcel(List header, List data, OutputStream out) throwsIOException {//创建一个Excel文件

HSSFWorkbook workbook = newHSSFWorkbook();//创建一个工作表

HSSFSheet sheet = workbook.createSheet("sheet1");//添加表头行

HSSFRow hssfRow = sheet.createRow(0);//设置单元格格式居中

HSSFCellStyle cellStyle =workbook.createCellStyle();

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//添加表头内容

for (int i = 0; i < header.size(); i++) {

HSSFCell headCell=hssfRow.createCell(i);

headCell.setCellValue(header.get(i));

headCell.setCellStyle(cellStyle);

}//添加数据内容

for (int i = 0; i < data.size(); i++) {

String[] strings=data.get(i);

hssfRow= sheet.createRow(i + 1);for (int j = 0; j < strings.length; j++) {

HSSFCell cell=hssfRow.createCell(j);

cell.setCellValue(strings[j]);

cell.setCellStyle(cellStyle);

}

}//单元格自适应

sheet.autoSizeColumn(2,true);//保存Excel文件

workbook.write(out);

}public static void createExcel(String title,List header, List data, OutputStream out) throwsIOException {//创建一个Excel文件

HSSFWorkbook workbook = newHSSFWorkbook();//创建一个工作表

HSSFSheet sheet = workbook.createSheet("sheet1");//添加表头行

HSSFRow hssfRow = sheet.createRow(0);

HSSFCell cellTitle= hssfRow.createCell(0);//设置标题外的单元格格式居中

HSSFCellStyle cellStyle =workbook.createCellStyle();

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置标题的样式

HSSFCellStyle titleCellStyle =workbook.createCellStyle();

titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

titleCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置标题字体的样式

HSSFFont font =workbook.createFont();

font.setFontHeightInPoints((short) 12);//设置字体大小

titleCellStyle.setFont(font);//标题设置(四个参数分别表示起始行，终止行，起始列，终止列)

cellTitle.setCellValue(title);int lastCol = header.size() > 1 ? header.size() : 2;

CellRangeAddress region1= new CellRangeAddress(0, 1, (short) 0, (short) lastCol - 1);

sheet.addMergedRegion(region1);

hssfRow= sheet.createRow(1);

hssfRow= sheet.createRow(2);

cellTitle.setCellStyle(titleCellStyle);//添加表头内容

for (int i = 0; i < header.size(); i++) {

HSSFCell headCell=hssfRow.createCell(i);

headCell.setCellValue(header.get(i));

headCell.setCellStyle(cellStyle);

}//添加数据内容

for (int i = 0; i < data.size(); i++) {

String[] strings=data.get(i);

hssfRow= sheet.createRow(i + 3);for (int j = 0; j < strings.length; j++) {

HSSFCell cell=hssfRow.createCell(j);

cell.setCellValue(strings[j]);

cell.setCellStyle(cellStyle);

}

}//保存Excel文件

workbook.write(out);

}/*** 读取Excel的内容

*

*@paramfileType 文件类型，xls或xlsx

*@paramstartRows 开始读取行数，比喻行头不需要读入 忽略的行数为1

*@paramignoreRowBlank 是否忽略空行

*@paramis 文件输入流

*@return读出的Excel中数据的内容

*@throwsIOException duxxxxx*/

public static List readData(String fileType, int startRows, boolean ignoreRowBlank, InputStream is) throwsIOException {

List result = new ArrayList<>();

Workbook wb=readExcel(fileType, is);for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {

Sheet sheet=wb.getSheetAt(sheetIndex);for (int rowIndex = startRows, z = sheet.getLastRowNum(); rowIndex <= z; rowIndex++) {

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

}int rowSize =row.getLastCellNum();

String[] values= newString[rowSize];boolean hasValue = false;for (int columnIndex = 0; columnIndex < rowSize; columnIndex++) {

String value= "";

Cell cell=row.getCell(columnIndex);if (cell != null) {//注意：一定要设成这个，否则可能会出现乱码,后面版本默认设置

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

value=cell.getStringCellValue();break;caseHSSFCell.CELL_TYPE_NUMERIC:if(HSSFDateUtil.isCellDateFormatted(cell)) {

Date date=cell.getDateCellValue();if (date != null) {

value= new SimpleDateFormat("yyyy-MM-dd")

.format(date);

}else{

value= "";

}

}else{//value = new DecimalFormat("0").format(cell.getNumericCellValue());

if(HSSFDateUtil.isCellDateFormatted(cell)) {

value=String.valueOf(cell.getDateCellValue());

}else{

cell.setCellType(Cell.CELL_TYPE_STRING);

String temp=cell.getStringCellValue();//判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串

if (temp.indexOf(".") > -1) {

value= String.valueOf(newDouble(temp)).trim();

}else{

value=temp.trim();

}

}

}break;caseHSSFCell.CELL_TYPE_FORMULA://导入时如果为公式生成的数据则无值

if (!cell.getStringCellValue().equals("")) {

value=cell.getStringCellValue();

}else{

value= cell.getNumericCellValue() + "";

}break;caseHSSFCell.CELL_TYPE_BLANK:break;caseHSSFCell.CELL_TYPE_ERROR:

value= "";break;caseHSSFCell.CELL_TYPE_BOOLEAN:

value= (cell.getBooleanCellValue() == true ? "Y":"N");break;default:

value= "";

}

}

values[columnIndex]=value;if (!value.isEmpty()) {

hasValue= true;

}

}if (!ignoreRowBlank || hasValue) {//不为忽略空行模式或不为空行

result.add(values);

}

}

}returnresult;

}//读取excel

private static Workbook readExcel(String fileType, InputStream is) throwsIOException {if ("xls".equals(fileType)) {return newHSSFWorkbook(is);

}else if ("xlsx".equals(fileType)) {return newXSSFWorkbook(is);

}else{throw new IllegalArgumentException("不支持的文件类型，仅支持xls和xlsx");

}

}/*** 去掉字符串右边的空格

*

*@paramstr 要处理的字符串

*@return处理后的字符串*/

private staticString rightTrim(String str) {if (str == null) {return "";

}int length =str.length();for (int i = length - 1; i >= 0; i--) {if (str.charAt(i) != 0x20) {break;

}

length--;

}return str.substring(0, length);

}

}