java maven excel导出,Java Excel文件导入导出

1.maven依赖

首先引入POI包依赖

org.apache.poi

poi

4.1.2

org.apache.poi

poi-ooxml

4.1.2

2.API

大家需要了解一下相关的API类，便于后面理解代码逻辑和排查问题，重写相关逻辑实现自己的业务需求。

1.import org.apache.poi.ss.usermodel.Workbook,对应Excel文档；

2.import org.apache.poi.hssf.usermodel.HSSFWorkbook，对应xls格式的Excel文档；

3.import org.apache.poi.xssf.usermodel.XSSFWorkbook，对应xlsx格式的Excel文档；

4.import org.apache.poi.ss.usermodel.Sheet，对应Excel文档中的一个sheet；

5.import org.apache.poi.ss.usermodel.Row，对应一个sheet中的一行；

6.import org.apache.poi.ss.usermodel.Cell，对应一个单元格。

3.导入

public class ImportExcelUtil {

private static final String EXCEL_XLS_SUFFIX = ".xls";

private static final String EXCEL_XLSX_SUFFIX = ".xlsx";

/**

* 读取Excel数据内容

*

* @param rowIndex    指定行号

* @param columnIndex 指定列号

* @return Map 包含单元格数据内容的Map对象

*/

public static List> readExcelContent(String filepath, Integer rowIndex, Integer columnIndex) throws Exception {

List> returnList = new LinkedList<>();

Workbook wb = null;

Sheet sheet;

Row row;

try {

InputStream is = new FileInputStream(filepath);

if (filepath.endsWith(EXCEL_XLS_SUFFIX)) {

wb = new HSSFWorkbook(is);

} else if (filepath.endsWith(EXCEL_XLSX_SUFFIX)) {

wb = new XSSFWorkbook(is);

}

if (wb == null) {

throw new Exception("Workbook对象为空！");

}

sheet = wb.getSheetAt(0);

//解析文件总行数、总列数

int rowNum = rowIndex != null ? rowIndex : sheet.getLastRowNum();

row = sheet.getRow(0);

int colNum = columnIndex != null ? columnIndex : row.getLastCellNum();

//循环列

for (int colIndex = colNum; colIndex > 0; colIndex--) {

Cell cell = row.getCell(colIndex);

if (cell != null && !"".equals(cell.toString())) {

colNum = colIndex;

break;

}

}

logger.info("have data col:{}", colNum);

// 正文内容应该从第二行开始,第一行为表头的标题

for (int i = 0; i <= rowNum; i++) {

row = sheet.getRow(i);

int j = 0;

int size = (int) (colNum / .75f) + 1;

//存储单元格数据

Map cellValue = new LinkedHashMap<>(size);

if (row == null) {

continue;

}

while (j <= colNum) {

Cell cell = row.getCell(j);

String value = "";

//日期单元格需格式化日期

if (cell != null) {

if (cell.getCellType() == CellType.NUMERIC) {

if (HSSFDateUtil.isCellDateFormatted(cell)) {

Date d = cell.getDateCellValue();

SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");

value = formatter.format(d);

} else if (cell.toString().contains("E")) {

DecimalFormat nf = new DecimalFormat("0");

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

} else {

value = cell.toString().endsWith(".0") ? cell.toString().replace(".0", "") : cell.toString().trim();

}

} else if (cell.getCellType() == CellType.FORMULA) {

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

} else {

value = cell.toString().trim();

}

}

cellValue.put(j, value);

j++;

}

returnList.add(cellValueMap);

}

wb.close();

} catch (FileNotFoundException e) {

logger.error("FileNotFoundException", e);

} catch (IOException e) {

logger.error("IOException", e);

} finally {

if (wb != null) {

wb.close();

}

}

return returnList;

}

}

4.导出

public class ExportExcelUtil {

/**

* 导出excel文件，表头为一维数组表示不用合并单元格

* @param sheetName

* @param excelTitle

* @param dataCollection

* @param

* @return

*/

public static HSSFWorkbook exportExcel(String sheetName, String[] excelTitle, Collection dataCollection) {

//创建一个Excel文件

HSSFWorkbook workbook = new HSSFWorkbook();

//创建一个Sheet表格工作空间

HSSFSheet sheet = workbook.createSheet(sheetName);

HSSFCellStyle style = workbook.createCellStyle();

//设置表格默认宽度

sheet.setDefaultColumnWidth(20);

//设置表格的表头

HSSFCell cellHeader;

HSSFRow row = sheet.createRow(0);

for (int i = 0; i < excelTitle.length; i++) {

//创建单元格表头

cellHeader = row.createCell(i);

cellHeader.setCellValue(new HSSFRichTextString(excelTitle[i]));

}

//匹配表头设置单元格的值

setWorkBookValue(sheet, dataCollection,0, style);

return workbook;

}

/**

* (根据自定义)把具体数据写入到excel中

* @param sheet

* @param dataCollection

* @param index

* @param style

* @param

*/

@SuppressWarnings("unchecked")

private static void setWorkBookValue(HSSFSheet sheet,Collection dataCollection, int index,HSSFCellStyle style){

T t;

Object[] fields;

String fieldName;

String getMethodName;

HSSFCell cell;

HSSFRow row;

Class tClass;

Method getMethod;

Object value;

//遍历集合设置单元格值

Iterator it = dataCollection.iterator();

while(it.hasNext()){

//创建一行单元格

index ++;

row = sheet.createRow(index);

//获取数据

t = it.next();

//利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值

fields = t.getClass().getDeclaredFields();

for(int i = 0; i < fields.length; i++){

cell = row.createCell(i);

style.setAlignment(HorizontalAlignment.LEFT);

cell.setCellStyle(style);

//利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值

Field[] newFields = t.getClass().getDeclaredFields();

fieldName = newFields[i].getName();

getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

try {

tClass = t.getClass();

getMethod = tClass.getMethod(getMethodName, new Class[]{});

value = getMethod.invoke(t, new Object[]{});

setCellValue(value,cell);

} catch (Exception e) {

e.printStackTrace();

}

}

}

}

/**

* value格式校验

*/

private static void setCellValue(Object value,HSSFCell cell){

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

String textValue = null;

Pattern pattern = Pattern.compile(RULE);

Matcher matcher;

HSSFRichTextString richTextString;

if (!StringUtils.isEmpty(value)){

//value进行类型转换

if (value instanceof Integer) {

cell.setCellValue((Integer) value);

} else if (value instanceof Float) {

textValue = String.valueOf(value);

cell.setCellValue(textValue);

} else if (value instanceof Double) {

textValue = String.valueOf(value);

cell.setCellValue(textValue);

} else if (value instanceof Long) {

cell.setCellValue((Long) value);

} else if (value instanceof Boolean) {

textValue = "是";

if (!(Boolean) value) {

textValue = "否";

}

} else if (value instanceof Date) {

textValue = sdf.format((Date) value);

} else {

// 其它数据类型都当作字符串简单处理

textValue = value.toString();

}

if (textValue != null) {

matcher = pattern.matcher(textValue);

if (matcher.matches()) {

// 是数字当作double处理

cell.setCellValue(Double.parseDouble(textValue));

} else {

richTextString = new HSSFRichTextString(textValue);

cell.setCellValue(richTextString);

}

}

}

}

/**

* excel 导出文件

* @param response

* @param workbook

* @param fileName

* @throws IOException

*/

public static void exportExcelFile(HttpServletResponse response, HSSFWorkbook workbook, String fileName) throws IOException {

if (workbook != null) {

response.reset();

//指定下载的文件名

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");

String filePrefix = sdf.format(new Date());

response.setContentType("application/vnd.ms-excel");

response.setCharacterEncoding("utf-8");

fileName = URLEncoder.encode(filePrefix + "_" + fileName, "UTF-8").replaceAll("\\+", "%20");

response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

BufferedOutputStream bufferedOutput = null;

try {

bufferedOutput = new BufferedOutputStream(response.getOutputStream());

workbook.write(bufferedOutput);

bufferedOutput.flush();

} catch (IOException e) {

e.printStackTrace();

throw e;

} finally {

if (bufferedOutput != null) {

try {

bufferedOutput.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

}

}

}

4 调用导出

public void exportFile(String objectJson, HttpServletResponse response) throws Exception {

//省略业务代码

HSSFWorkbook workbook = ExportExcelUtil.exportExcel(SHEET_NAME, TITLE_LINE, xxxList);

ExportExcelUtil.exportExcelFile(response, workbook, FILE_NAME);

}