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 单元测试及测试结果略