java Excel工具类，导入导出Excel数据
java Excel工具类，导入导出Excel数据，导入数据对合并表格有判断获取数据；
导出数据到Excel，Excel文件不存在会创建。
使用的是poi处理，兼容Excel。
对反射不够理解，目前先用map处理，后续想好了，再处理。
代码：
package com.gx.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @ClassName: ExcelUtilDeal
* @Description: Excel工具类，导入导出Excel数据
* @author zhoujie
* @date 2018年7月21日 上午11:08:13
* @version V1.0
*/
public class ExcelUtilDeal {
public static void main(String[] args) {
//获取Excel文件数据
/*List<Map<String, Object>> list = getExcelData("用户表1.xls", "用户表");
System.out.println("listsize:"+list.size());
for(Map<String, Object> map : list){
for (Object key : map.keySet()) {
System.out.println(key + " ：" + map.get(key));
}
} */
//数据导出到Excel
List<Map<String, Object>> list = new ArrayList<>();
String[] headers = {"用户名","密码","邮箱","性别","年龄"};
for (int i = 0; i < 2; i++) {
Map<String, Object> map = new HashMap<>();
for (int j = 0; j < headers.length; j++) {
map.put(headers[j], i+""+j+"");
}
list.add(map);
}
fillExcelData(list, "用户表1.xls", "用户表", headers);
}
/**
* 数据导出到Excel，单个Excel
* @param list 数据
* @param excelFile Excel文件：用户表.xlsx
* @param headers 行头标题字符串数组
* @param sheetname Excel的sheet名称
* @return int 导出数量
* @throws Exception
*/
public static int fillExcelData(List<Map<String, Object>> list, String excelFile, String sheetname, String[] headers) {
// 无Excel文件先创建Excel文件，再读取Excel文件
String cpath = ExcelUtilDeal.class.getClassLoader().getResource("").getPath();
String rootPath = cpath.substring(0, cpath.indexOf("/WEB-INF/"));
File dirfile = new File(rootPath+"\\excel\\"); //判断文件夹是否存在
if(!dirfile.exists()){
dirfile.mkdirs(); //创建文件夹
}
String path = rootPath+"\\excel\\"+excelFile;
File file = new File(path);
FileOutputStream out = null;
FileInputStream fileinp = null;
Workbook wb = null;
try {
if(!file.exists()){ //判断文件是否存在
createExcel(path, sheetname, headers); //创建Excel文件
}
fileinp = new FileInputStream(file);
wb = WorkbookFactory.create(fileinp); //兼容模式打开Excel
Sheet sheet = wb.getSheet(sheetname); //获取sheet
Row row = null; //行
if(sheet == null){
sheet = wb.createSheet(sheetname); //创建sheet
row = sheet.createRow(0); //首行
// 先填充行头
for (int i = 0; i < headers.length; i++) {
row.createCell(i).setCellValue(headers[i]);
}
}
// 再填充数据
int rowIndex = 1;
Iterator<Map<String, Object>> iterator = list.iterator();
while (iterator.hasNext()) {
row = sheet.createRow(rowIndex++);
Map<String, Object> map = iterator.next(); //获取对象
setRowCellValue(map, headers, row); //设置值
}
out = new FileOutputStream(path);
wb.write(out);
System.out.println("数据导出到Excel文件");
return rowIndex;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
wb.close();
fileinp.close();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return 0;
}
/**
* 设置Excel一行数据
* @param map 数据集合
* @param row Excel行
* @throws NoSuchMethodException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public static void setRowCellValue(Map<String, Object> map, String[] headers, Row row) throws NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
Object value = null;
for (int i = 0; i < headers.length; i++) {
value = map.get(headers[i]);
if(value instanceof Double){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
value = sdf.format(value).toString();
}else if(value instanceof Boolean){
if((boolean) value){
value = "是";
}else{
value = "否";
}
}
row.createCell(i).setCellValue(value.toString()); //单元格值设置
}
}
/**
* 获取Excel文件数据
* 填充数据到excel文件
* @param excelFile Excel文件名称
* @return int 导入数量
* @throws Exception
*/
public static List<Map<String, Object>> getExcelData(String excelFile, String sheetname) {
List<Map<String, Object>> list = new ArrayList<>();
// 本地磁盘读取excel文件,然后读取sheet，再读取所有数据，循环sheet
FileInputStream fileinp = null;
Workbook wb = null;
try {
String cpath = ExcelUtilDeal.class.getClassLoader().getResource("").getPath();
String rootPath = cpath.substring(0, cpath.indexOf("/WEB-INF/"));
String path = rootPath+"\\excel\\"+excelFile;
File file = new File(path);
if(!file.exists()){
file.mkdirs();
}
fileinp = new FileInputStream(file);
wb = WorkbookFactory.create(fileinp); //兼容模式打开Excel
Sheet sheet = wb.getSheet(sheetname); //获取对应sheet
Row firstrow = sheet.getRow(0); //获取首行数据即标题;
int rows = sheet.getPhysicalNumberOfRows(); //获取sheet表格数据行数
int cells = firstrow.getPhysicalNumberOfCells();//获取表头单元格个数
String[] headers = new String[cells]; //行头
for (int i = 0; i < cells; i++) {
Cell firstrowCell = firstrow.getCell(i);
headers[i] = firstrowCell.getStringCellValue();
}
Row row; //初始化行
Cell cell; //初始化单元格
for (int i = 1; i < rows; i++) {
row = sheet.getRow(i); //从第二行开始读数据
Map<String, Object> map = new HashMap<>();
for (int j = 0; j < cells; j++) {
if(isMergedRegion(sheet, i, j)){ //判断是否是合并单元格
System.out.println("发现合并单元格");
cell = getMergedCell(sheet, row, j); //获取合并单元格
}else{
cell = row.getCell(j);
}
Object value = formatCellData(cell); //获取格式化单元格值
map.put(headers[j], value);
}
list.add(map);
}
System.out.println("获取Excel文件数据");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
wb.close();
fileinp.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return list;
}
/**
* 创建新excel.
* @param fileDir excel的路径
* @param sheetName 要创建的表格索引
* @param headers excel的第一行即表格头
*/
public static void createExcel(String fileDir,String sheetName,String headers[]) {
//创建workbook
Workbook workbook = null;
try{
workbook = new HSSFWorkbook();
}catch(Exception e){
workbook = new XSSFWorkbook();
}
//添加Worksheet（不添加sheet时生成的xlsx文件打开时会报错)
workbook.createSheet(sheetName);
//输出流
FileOutputStream out = null;
try {
//添加表头
Row row = workbook.getSheet(sheetName).createRow(0); //sheet创建第一行
for(short i = 0;i < headers.length;i++){
row.createCell(i).setCellValue(headers[i]);
}
out = new FileOutputStream(fileDir); //创建文件
workbook.write(out); //写入sheet
System.out.println("创建Excel");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
workbook.close();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 返回格式化各类型单元格值
* @param cell 单元格
* @return Object 返回值
*/
@SuppressWarnings("deprecation")
public static Object formatCellData(Cell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) { // 日期
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
}
return cell.getNumericCellValue(); // 数字
case Cell.CELL_TYPE_STRING: // 字符串
return cell.getStringCellValue();
case Cell.CELL_TYPE_FORMULA: // 公式
return cell.getCellFormula();
case Cell.CELL_TYPE_BLANK: // 空白
return "";
case Cell.CELL_TYPE_BOOLEAN: // 布尔取值
return cell.getBooleanCellValue();
case Cell.CELL_TYPE_ERROR: // 错误类型
return cell.getErrorCellValue();
}
return "";
}
/**
* 判断是否合并单元格
* 先获取所有合并单元格，通过传入行列索引判断是否在合并单元格内
* @param sheet
* @param row
* @param column
* @return
*/
public static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i); //循环获取所有合并单元格
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) { //判断是否在合并单元格中
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
/**
* 获取合并单元格有值单元格
* 合并单元格值保存在第一个合并单元格内
* @param sheet
* @param row
* @param column
* @return
*/
public static Cell getMergedCell(Sheet sheet, Row row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
Cell fCell = null;
int rowIndex = row.getRowNum();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (rowIndex >= firstRow && rowIndex <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
fCell = fRow.getCell(firstColumn);
}
}
}
if (fCell == null) {
fCell = row.getCell(column);
}
return fCell;
}
/**
* 截取文件后缀
* @param path
* @return String 返回类型
*/
public static String getSuffix(String path) {
String substring = path.substring(path.lastIndexOf(".") + 1);
return substring;
}
}
说明：Excel文件导出在根目录下的excel目录。
