java读写excel_JAVA 读写excel文件操作（POI）

POI excel基本教程可参考如下链接：

一、导出为excel文件(将数据库查询到的数据导出为excel文件)。

1.1、添加maven依赖包(guava，jackson可不用添加)

org.apache.poi

poi-ooxml

3.17

com.google.guava

guava

25.0-jre

com.fasterxml.jackson.core

jackson-databind

1.2、获取表的备注，列名及备注信息。

获取这些信息是为了赋值工作表的名称，行名称等。如果自己命名，可以忽略这段代码

MYSQL:

@Mapper

public interface TableInfoMapper {

/**

* 获取表名备注信息

* @param tableName 表名，区分大小写

* @return

*/

@Select("SELECT table_comment FROM information_schema.`tables` WHERE table_schema = 'test_target' AND table_name= #{tableName}")

String getTableComment(String tableName);

/**

* 获取列名和列类型

* @param tableName 表名，区分大小写

* @return

*/

@Select("SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS where table_schema = 'test_target' AND TABLE_NAME = #{tableName}")

@ResultType(Map.class)

List> getColumnInfo(String tableName);

/**

* 获取列名和列备注信息

* @param tableName 表名，区分大小写

* @return

*/

@Select("SELECT T.column_name, T.column_comment FROM information_schema.`COLUMNS` T WHERE table_schema = 'test_target' AND table_name = #{tableName}")

@ResultType(Map.class)

List> getColumnCommentInfo(String tableName);

}

Oracle(没写测试类，凑合看吧)：

select COLUMN_NAME, DATA_TYPE from user_tab_columns b "

+ "where b.table_name=upper('"+tableName+"') order by b.COLUMN_ID;

SELECT T.column_name, T.comments FROM user_col_comments T "

+ "where T.table_name = upper('" + tableName + "');

select comments from user_tab_comments t "

+ "where t.TABLE_NAME = upper('" + tableName + "');

1.3 具体操作代码

1.3.1 导出后缀名xlsx的excel文件

/**

* 从数据库中导出为后缀名为xlsx的excel文件

*

* @param listData 表数据

* @param columnNameCN 表的列名称

* @param filePath 保存路径

* @param sheetName workbook名称

* @throws IOException

*/

public static void exportXlsxFromSQL(List> listData, List columnNameCN, String filePath, String sheetName) throws IOException {

ObjectMapper om = new ObjectMapper();

XSSFWorkbook workbook = new XSSFWorkbook();

XSSFCellStyle cellStyle = workbook.createCellStyle();

// 时间类型格式，如果不设置这个，excel默认展示位number型

XSSFDataFormat format = workbook.createDataFormat();

cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd hh:mm:ss"));

XSSFSheet spreadsheet = workbook.createSheet(sheetName);

int rowNum = 0;

XSSFRow row = spreadsheet.createRow(rowNum);

XSSFCell cell;

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

cell = row.createCell(i);

cell.setCellValue(columnNameCN.get(i));

}

for (Object data : listData) {

rowNum++;

row = spreadsheet.createRow(rowNum);

int cellNum = 0;

@SuppressWarnings("unchecked")

Map map = om.convertValue(data, Map.class);

for (Map.Entry entry : map.entrySet()) {

cell = row.createCell(cellNum);

cellNum++;

if (entry.getValue() instanceof Integer) {

cell.setCellValue((Integer) entry.getValue());

continue;

}

if (entry.getValue() instanceof String) {

cell.setCellValue((String) entry.getValue());

continue;

}

if (entry.getValue() instanceof Date) {

cell.setCellStyle(cellStyle);

cell.setCellValue((Date) entry.getValue());

continue;

}

}

}

OutputStream out = new FileOutputStream(new File(filePath));

workbook.write(out);

out.close();

workbook.close();

log.info("==CREATE XLSX FILE SUCCESS!");

}

1.3.2 导出为后缀名为xls的excel文件

public static void exportXlsFromSQL(List> listData, List columnNameCN, String filePath, String sheetName) throws IOException {

ObjectMapper om = new ObjectMapper();

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFCellStyle cellStyle = workbook.createCellStyle();

// 时间类型格式，如果不设置这个，excel默认展示为number型

HSSFDataFormat format = workbook.createDataFormat();

cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd hh:mm:ss"));

HSSFSheet spreadsheet = workbook.createSheet(sheetName);

int rowNum = 0;

HSSFRow row = spreadsheet.createRow(rowNum);

HSSFCell cell;

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

cell = row.createCell(i);

cell.setCellValue(columnNameCN.get(i));

}

for (Object data : listData) {

rowNum++;

row = spreadsheet.createRow(rowNum);

int cellNum = 0;

@SuppressWarnings("unchecked")

Map map = om.convertValue(data, Map.class);

for (Map.Entry entry : map.entrySet()) {

cell = row.createCell(cellNum);

cellNum++;

if (entry.getValue() instanceof Integer) {

cell.setCellValue((Integer) entry.getValue());

continue;

}

if (entry.getValue() instanceof String) {

cell.setCellValue((String) entry.getValue());

continue;

}

if (entry.getValue() instanceof Date) {

cell.setCellStyle(cellStyle);

cell.setCellValue((Date) entry.getValue());

continue;

}

}

}

OutputStream out = new FileOutputStream(new File(filePath));

workbook.write(out);

out.close();

workbook.close();

log.info("==CREATE XLSX FILE SUCCESS!");

}

1.4、需要导出的表数据。

@Select("SELECT * FROM t_user")

List getAllUser();

POJO(set、get省略)：

private Integer userId;

private String userName;

private String password;

private Integer credits;

private Date lastVisit;

private String lastIp;

@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")

public Date getLastVisit() {

return lastVisit;

}

注：

使用mybatis查询数据时，如果数据库存储的是timestamp、datetime、date、time等时间类型，而Java bean也使用的是date类型，mybatis会自动将date类型转换为unix long时间eg:1503912320000，而不是时间格式。

解决方式有两种：

1.将Java bean 中的类型改为String类型。

2.在java bean 中date类型的get方法上加上注解@JsonFormat

jackson中有一个@JsonFormat注解，将它配置到Date类型的get方法上后，jackson就会按照配置的格式转换日期类型。如上。

1.5：单元测试

@Autowired

private TableInfoMapper tableInfoMapper;

@Autowired

private UserMapper userMapper;

@Test

public void createXlsForSQLTest() throws IOException {

String tableName = "t_user";

String filePath = "D:\\document\\" + tableName + ".xls";

String sheetName = tableInfoMapper.getTableComment(tableName);

List> columnNameList = tableInfoMapper.getColumnCommentInfo(tableName);

List columnNameCN = Lists.newArrayListWithCapacity(columnNameList.size());

for (Map map : columnNameList) {

String nameCN = map.get("column_comment");

if (StringUtils.isEmpty(nameCN)) {

columnNameCN.add(map.get("column_name"));

} else {

columnNameCN.add(nameCN);

}

}

List userList = userMapper.getAllUser();

XlsFileUtil.exportXlsFromSQL(userList, columnNameCN, filePath, sheetName);

}

1.6：结果

二、 读取excel的数据

2.1 code

/**

* 读取excel文档

* @param filePath 文件路径

* @throws IOException

*/

public static void readXlsData(String filePath) throws IOException {

InputStream is = new FileInputStream(new File(filePath));

// xls 后缀名

if (filePath.endsWith("xls")) {

HSSFWorkbook hworkBook = new HSSFWorkbook(is);

HSSFSheet sheet = hworkBook.getSheetAt(0);

Iterator rowList = sheet.iterator();

while (rowList.hasNext()) {

HSSFRow row = (HSSFRow) rowList.next();

Iterator cellList = row.cellIterator();

columnDataHandler(cellList);

hworkBook.close();

}

} else { // xlsx后缀名

XSSFWorkbook xworkBook = new XSSFWorkbook(is);

XSSFSheet sheet = xworkBook.getSheetAt(0);

Iterator rowList = sheet.iterator();

while (rowList.hasNext()) {

XSSFRow row = (XSSFRow) rowList.next();

Iterator cellList = row.cellIterator();

columnDataHandler(cellList);

xworkBook.close();

}

}

is.close();

}

private static void columnDataHandler(Iterator cellList) {

while (cellList.hasNext()) {

Cell cell = cellList.next();

switch (cell.getCellTypeEnum()) {

case NUMERIC:

System.out.println(cell.getNumericCellValue() + " \t\t ");

break;

case STRING:

System.out.println(cell.getStringCellValue() + " \t\t ");

break;

case BOOLEAN:

System.out.println(cell.getBooleanCellValue() + " \t\t ");

break;

default:

System.out.println(cell.getStringCellValue() + " \t\t ");

break;

}

}

}

2.2 单元测试及测试结果略