在实际项目中，用到的导入导出excel太多了，而且对于web管理系统更为常见，因此封装了导入导出的工具类。代码中依赖了slf4j日志包，commons-io包的IOUtils关闭流，commons-lang和commons-collections包等包。
1. 导出Excel封装的工具类:ExcelExporter
在实际中导出excel非常常见,于是自己封装了一个导出数据到excel的工具类，先附上代码，最后会写出实例和解释。支持03和07两个版本的 excel。
HSSF导出的是xls的excel，XSSF导出的是xlsx的excel，SXSSF导出的也是xlsx的excel，只不过这个用于处理数据量大的情况，生成文件之后数据不会留在内存中。
package cn.qs.utils.export; import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; import org.apache.commons.collections.MapUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory; public class ExcelExporter { public static enum OfficeVersion {
OFFICE_03, OFFICE_07;
} private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExporter.class); private String[] headerNames;
private Workbook workBook;
private Sheet sheet; /**
*
* @param headerNames
* 表头
* @param sheetName
* sheet的名称
* @param excelVerson
* excel的版本
*/
public ExcelExporter(String[] headerNames, String sheetName, OfficeVersion officeVersion) {
this.headerNames = headerNames;
// 创建一个工作簿
if (OfficeVersion.OFFICE_07.equals(officeVersion)) {
// workBook = new XSSFWorkbook();//处理07版本excel
workBook = new SXSSFWorkbook();// 处理07版本，但是适用于大数据量，导出之后数据不会占用内存
} else if (OfficeVersion.OFFICE_03.equals(officeVersion)) {
workBook = new HSSFWorkbook();
}
// 创建一个工作表sheet
sheet = workBook.createSheet(sheetName);
initHeader();
} /**
* 初始化表头信息
*/
private void initHeader() {
// 创建第一行
Row row = sheet.createRow(0);
Cell cell = null;
// 创建表头
for (int i = 0; i < headerNames.length; i++) {
cell = row.createCell(i);
cell.setCellValue(headerNames[i]);
setCellStyle(cell);
}
} /**
* 设置单元格样式
*
* @param cell
* 单元格
*/
public void setCellStyle(Cell cell) {
// 设置样式
CellStyle cellStyle = workBook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置字体居中
// 设置字体
Font font = workBook.createFont();
font.setFontName("宋体");
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
font.setFontHeightInPoints((short) 13); cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
} /**
* 创建行内容(每一行的数据装在list中)
*
* @param datas
* 每一行的数据
* @param rowIndex
* 行号(从1开始)
*/
public void createTableRow(List<String> datas, int rowIndex) {
// 创建第i行
Row row = sheet.createRow(rowIndex);
Cell cell = null;
// 写入数据
for (int index = 0, length = datas.size(); index < length; index++) {
// 参数代表第几列
cell = row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(datas.get(index));
}
} /**
*
* @param datas
* 数据,每一个map都是一行
* @param keys
* key[i]代表从map中获取keys[i]的值作为第i列的值,如果传的是null默认取表头
*/
public void createTableRows(List<Map<String, Object>> datas, String[] keys) {
for (int i = 0, length_1 = datas.size(); i < length_1; i++) {
if (ArrayUtils.isEmpty(keys)) {
keys = headerNames;
}
// 创建行(从第二行开始)
Map<String, Object> data = datas.get(i);
Row row = sheet.createRow(i + 1);
Cell cell = null;
for (int j = 0, length_2 = keys.length; j < length_2; j++) {
// 单元格获取map中的key
String key = keys[j];
String value = MapUtils.getString(data, key, ""); cell = row.createCell(j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(value);
} }
} /**
* 根据表头自动调整列宽度
*/
public void autoAllSizeColumn() {
if (sheet instanceof SXSSFSheet) {// 如果是SXSSFSheet，需要调用trackAllColumnsForAutoSizing方法一次
SXSSFSheet tmpSheet = (SXSSFSheet) sheet;
tmpSheet.trackAllColumnsForAutoSizing();
}
for (int i = 0, length = headerNames.length; i < length; i++) {
sheet.autoSizeColumn(i);
}
} /**
* 将数据写出到excel中
*
* @param outputStream
*/
public void exportExcel(OutputStream outputStream) {
// 导出之前先自动设置列宽
this.autoAllSizeColumn();
try {
workBook.write(outputStream);
} catch (IOException e) {
LOGGER.error(" exportExcel error", e);
} finally {
IOUtils.closeQuietly(outputStream);
}
} /**
* 合并单元格(起始行列都包括在里面)
*
* @param startRow
* 起始行
* @param endRow
* 结束行
* @param startCol
* 起始列
* @param endCol
* 结束列
*/
public void mergeCell(int startRow, int endRow, int startCol, int endCol) {
CellRangeAddress region = new CellRangeAddress(startRow, endRow, startCol, endCol);
sheet.addMergedRegion(region);
} /**
* 将数据写出到excel中
*
* @param outputFilePath
*/
public void exportExcel(String outputFilePath) {
// 导出之前先自动设置列宽
this.autoAllSizeColumn();
FileOutputStream outputStream = null;
try {
outputStream = new FileOutputStream(outputFilePath);
workBook.write(outputStream);
} catch (IOException e) {
LOGGER.error(" exportExcel error", e);
} finally {
IOUtils.closeQuietly(outputStream);
}
} public static void main(String[] args) {
test2();
} private static void test2() {
ExcelExporter hssfWorkExcel = new ExcelExporter(new String[] { "姓名", "年龄" }, "人员基本信息", OfficeVersion.OFFICE_03);
List<Map<String, Object>> datas = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Map data = new HashMap<>();
data.put("name", "tttttttttttttt" + i);
data.put("age", "age" + i);
datas.add(data);
}
hssfWorkExcel.createTableRows(datas, new String[] { "name", "age" });
hssfWorkExcel.mergeCell(1, 2, 0, 1); try {
hssfWorkExcel.exportExcel(new FileOutputStream(new File("e:/test1.xls")));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
}
上面的代码逻辑非常简单,创建实例的时候就初始化表头信息和创建sheet。
向excel中填充数据的方式有两种，就是上面的createTableRow方法和createTableRows方法。
createTableRow(List,int)方法就是多次调用此方法，list中数据，int是行号。list中数据依次作为第i行的列数据。
createTableRows(List<Map>,String[])这个方法应该是非常常用的一种方式。我们从数据库查询到的数据大多数映射为Map放入list中，因此上面的方法就比较常用。List<Map>参数就是所有的数据，一个Map代表一行，String[]是Map中的key，也就是数组的第一个元素对应的key作为第一列，第二个元素是作为第二列。如果map数据的key正好与表头一致我们可以传一个null。(因为Map是基于数组+链表，且存入的是无序的,所以无法直接通过map中的key确定列。除非传的数据是LinkedHashMap)。这个方法封装的比较好,如果一条数据中没有对应的值会将此单元格设为空。
下面是自己测试代码:
（1）测试List<String>写入数据，导出03版本的excel
package cn.qs.utils.export; import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List; public class FileTest { public static void main(String[] args) {
ExcelExporter hssfWorkExcel = new ExcelExporter(new String[] { "姓名", "年龄" }, "人员基本信息",
ExcelExporter.OfficeVersion.OFFICE_03);
for (int i = 0; i < 10; i++) {
List<String> data = new ArrayList<>();
data.add("namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss" + i);
data.add("" + (i + 20));
hssfWorkExcel.createTableRow(data, i + 1);
}
try {
hssfWorkExcel.exportExcel(new FileOutputStream(new File("e:/test.xls")));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
} }
结果:
(2)测试List<Map>写入数据，导出07版本的excel
package cn.qs.utils.export; import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List; public class FileTest { public static void main(String[] args) {
ExcelExporter hssfWorkExcel = new ExcelExporter(new String[] { "姓名", "年龄" }, "人员基本信息",
ExcelExporter.OfficeVersion.OFFICE_07);
for (int i = 0; i < 10; i++) {
List<String> data = new ArrayList<>();
data.add("namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss" + i);
data.add("" + (i + 20));
hssfWorkExcel.createTableRow(data, i + 1);
}
try {
hssfWorkExcel.exportExcel(new FileOutputStream(new File("e:/test.xlsx")));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
} }
结果:
2.读取Excel封装的工具类:ExcelReader
此工具类可以读取指定sheet的数据，可以指定每一列对应的header，如果不传默认以第一行作为head；也可以读取所有sheet的数据，以所有sheet的第一行作为head。最终的返回值是List<Map>类型，map的key是表头header。
代码:
package cn.qs.utils.export; import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map; import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory; public class ExcelReader { private static final Logger LOGGER = LoggerFactory.getLogger(ExcelReader.class); private Workbook workBook;
private Map<Sheet, String[]> sheetHeaders; public ExcelReader(String filePath) {
this(new File(filePath));
} public ExcelReader(File file) {
// 解决版本问题，HSSFWorkbook是97-03版本的xls版本，XSSFWorkbook是07版本的xlsx
try {
workBook = new XSSFWorkbook(new FileInputStream(file));
LOGGER.debug("是03版本的excel，采用XSSFWorkbook读取");
} catch (Exception e) {
try {
workBook = new HSSFWorkbook(new FileInputStream(file));
LOGGER.debug("是07版本的excel，采用HSSFWorkbook读取");
} catch (Exception e1) {
LOGGER.error("Excel格式不正确", e1);
throw new RuntimeException(e1);
}
}
} public ExcelReader(InputStream inputStream) {
// 解决版本问题，HSSFWorkbook是97-03版本的xls版本，XSSFWorkbook是07版本的xlsx
try {
workBook = new XSSFWorkbook(inputStream);
LOGGER.debug("是03版本的excel，采用XSSFWorkbook读取");
} catch (Exception e) {
try {
workBook = new HSSFWorkbook(inputStream);
LOGGER.debug("是07版本的excel，采用HSSFWorkbook读取");
} catch (Exception e1) {
LOGGER.error("Excel格式不正确", e1);
throw new RuntimeException(e1);
}
}
} /**
* 初始化sheet和表头信息，默认以每个sheet的第一行作为表头
*/
private void initDefaultSheetHeaders() {
sheetHeaders = new LinkedHashMap<>(); if (workBook == null) {
return;
} int numberOfSheets = workBook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workBook.getSheetAt(i);
String sheetName = workBook.getSheetName(i);
LOGGER.debug("sheetName[{}]: {}", i, sheetName); // 默认以第一行作为表头
Row row = sheet.getRow(0);
if (row == null) {
sheetHeaders.put(sheet, new String[] {});
continue;
} String[] headers = new String[] {};
short lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
String cellValue = getCellValue(row.getCell(j));
headers = (String[]) ArrayUtils.add(headers, cellValue);
}
sheetHeaders.put(sheet, headers);
}
} public List<Map<String, Object>> readAllSheetDatas() {
List<Map<String, Object>> result = new LinkedList<>();
int numberOfSheets = workBook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
List<Map<String, Object>> datas = readSheetDatas(i);
if (CollectionUtils.isNotEmpty(datas)) {
result.addAll(datas);
}
} return result;
} public List<Map<String, Object>> readSheetDatas(int sheetIndex) {
if (sheetHeaders == null) {
initDefaultSheetHeaders();
} Sheet sheet = workBook.getSheetAt(sheetIndex);
return readSheetDatas(sheetIndex, sheetHeaders.get(sheet), 1);
} public List<Map<String, Object>> readSheetDatas(int sheetIndex, String[] headers) {
return readSheetDatas(sheetIndex, headers, 0);
} /**
* 读取指定sheet数据
*
* @param sheetIndex
* sheet的下标
* @param headers
* 表头
* @param startRow
* 起始行数
* @return
*/
public List<Map<String, Object>> readSheetDatas(int sheetIndex, String[] headers, int startRow) {
List<Map<String, Object>> result = new LinkedList<>();
if (ArrayUtils.isEmpty(headers)) {
return result;
} Sheet sheet = workBook.getSheetAt(sheetIndex);
if (sheet == null) {
return result;
} int lastRowNum = sheet.getLastRowNum();
for (int i = startRow; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
} Map<String, Object> rowMap = new LinkedHashMap<>();
result.add(rowMap);
for (int j = 0, length_1 = headers.length; j < length_1; j++) {
String cellValue = getCellStringValue(row.getCell(j));
String header = headers[j];
rowMap.put(header, cellValue);
}
} return result;
} /**
* 读取cell的值
*
* @param cell
* 需要读取的cell
* @param defaultValue
* 默认值
* @return
*/
private String getCellValue(Cell cell) {
if (cell == null) {
return "";
} cell.setCellType(CellType.STRING);
return StringUtils.defaultIfBlank(cell.getStringCellValue(), "");
} /**
* POI3.15之后的读取方法(建议用这个)
*
* @param cell
* @return
*/
private String getCellStringValue(Cell cell) {
if (cell == null) {
return "";
} String cellValue = null;
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
cellValue = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd");
} else {
NumberFormat nf = NumberFormat.getInstance();
cellValue = String.valueOf(nf.format(cell.getNumericCellValue())).replace(",", "");
}
} else if (cell.getCellTypeEnum() == CellType.STRING) {
cellValue = cell.getStringCellValue();
} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
cellValue = String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellTypeEnum() == CellType.ERROR) {
cellValue = "错误类型";
} else {
cellValue = "";
} return cellValue;
} }
测试代码:
package cn.qs.utils.export; import java.util.List;
import java.util.Map; public class FileTest { public static void main(String[] args) {
ExcelReader excelExporter = new ExcelReader("e:/test.xls");
System.out.println("==========读取所有sheet数据，默认以第一行作为header==========");
List<Map<String, Object>> readAllSheetDatas2 = excelExporter.readAllSheetDatas();
System.out.println(readAllSheetDatas2);
} }
结果:
19:44:44.003 [main] DEBUG cn.qs.utils.export.ExcelReader - 是07版本的excel，采用HSSFWorkbook读取
==========读取第一个sheet(指定表头和起始行)==========
[{name=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss0, age=20}, {name=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss1, age=21}, {name=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss2, age=22}, {name=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss3, age=23}, {name=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss4, age=24}, {name=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss5, age=25}, {name=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss6, age=26}, {name=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss7, age=27}, {name=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss8, age=28}]
==========读取所有sheet数据，默认以第一行作为header==========
19:44:44.013 [main] DEBUG cn.qs.utils.export.ExcelReader - sheetName[0]: 人员基本信息
[{姓名=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss0, 年龄=20}, {姓名=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss1, 年龄=21}, {姓名=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss2, 年龄=22}, {姓名=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss3, 年龄=23}, {姓名=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss4, 年龄=24}, {姓名=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss5, 年龄=25}, {姓名=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss6, 年龄=26}, {姓名=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss7, 年龄=27}, {姓名=namesssssssssssssss水水水水水水水水水水水水水水水水水水水ssssssssssssssss8, 年龄=28}]