Excel导入功能
导入功能是将excel中的数据转换成对应的javaBean。
实现思路
- 使用poi读取excel表格
- 获取每行表格中的单元格信息,将单元格中的数据存入到对应的javaBean中。
- 写一个注解类来标注javaBean中的那个属性要与Excel中的数据进行对应。
- 运用java反射来获取javaBean中的注解属性。
代码实现
public class ImportExcelUtil {
private final Logger logger = LoggerFactory.getLogger(ImportExcelUtil.class);
private static final int DEFAULT_COUNT=20000;
private static final int DEFAULT_START_LINE=0;
public static <T> List<T> convertSheetToList(InputStream in,Class<T> clazz, boolean isExcel2003,
int startLine,int maxCount) throws Exception {
List<T> list = new ArrayList<>();
/** 根据版本选择创建Workbook的方式 */
Workbook wb;
if (isExcel2003) {
wb = new HSSFWorkbook(in);
} else {
wb = new XSSFWorkbook(in);
}
if(null != wb){
//获取第0个工作表格
Sheet sheet = wb.getSheetAt(0);
int count = sheet.getLastRowNum();
if(maxCount == 0){
maxCount = DEFAULT_COUNT;
}
if(count > maxCount){
throw new Exception("导入失败,excel数据控制在"+maxCount+"条之内!");
}
//遍历excel表格并将每一行中的数据转换成对象
if(startLine < 0){
startLine = DEFAULT_START_LINE;
}
for(int i=startLine;i<=count;i++){
Row row = sheet.getRow(i);
if(row==null){
continue;
}
T obj = convertLineToObj(clazz,row);
if(obj==null){
continue;
}
list.add(obj);
}
}
return list;
}
//行遍历
private static <T> T convertLineToObj(Class<T> clazz, Row row) throws Exception {
T obj = clazz.newInstance();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields){
ExcelImport annotation = field.getAnnotation(ExcelImport.class);
if(annotation!=null && row.getLastCellNum() >= annotation.columnIndex()){
//每行对应的单元格遍历
Cell cell = row.getCell(annotation.columnIndex());
if(cell==null)
throw new Exception("请使用正确的excel模板");
field.setAccessible(true);
field.set(obj,getCellValue(cell));
}
}
return obj;
}
private static Object getCellValue(Cell cell) {
if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
return String.valueOf(cell.getBooleanCellValue());
}else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
DecimalFormat df = new DecimalFormat("0.00");
String str = df.format(cell.getNumericCellValue());
return str;
}
return String.valueOf(cell.getStringCellValue());
}
public static void main(String[] args) {
try {
FileInputStream in = new FileInputStream("/Users/vobile_lzl/Downloads/tvids(2).xlsx");
// List<TvIdPresent> list = convertSheetToList(in, TvIdPresent.class,false,1);
// System.out.println("list = " + list);
} catch (Exception e) {
e.printStackTrace();
}
}
}
注解类()
该类主要用来识别excel字段中对应那个javaBean的属性。
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.METHOD, ElementType.ANNOTATION_TYPE})
public @interface ExcelImport {
int columnIndex() default 0;
}
- 例子
public class ShopResourceExcelBo {
@ExcelImport(columnIndex = 0)
private String resourceId;
@ExcelImport(columnIndex = 1)
private String resourceName;
@ExcelImport(columnIndex = 2)
private String priceStr;
@ExcelImport(columnIndex = 3)
private String inventoryNumStr;
}
...省去set和get方法
调用方法
List<ShopResourceExcelBo> excelList = ImportExcelUtil.convertSheetToList(inputStream,
ShopResourceExcelBo.class,isExcel2003,1,500);
导出Excel
将List集合中的对象转换过成excel单元格数据
- 同样是利用poi的导出功能来实现。
- 利用注解来说明javaBean的属性对应excel的单元格信息。
- 利用java反射将注解类的属性描述写入到excel的头信息上。
- 利用java反射将注解类中的属性值写入到excel对应的单元格。
代码实现
public class ExportExcelUtil {
private final Logger logger = LoggerFactory.getLogger(ExportExcelUtil.class);
private OutputStream outputStream;
private Workbook workbook;
private HSSFSheet sheet;
private int index;
public ExportExcelUtil(String fileName){
try {
this.init(new FileOutputStream(fileName));
} catch (FileNotFoundException e) {
logger.error("导出excel出错");
e.printStackTrace();
}
}
public ExportExcelUtil(OutputStream outputStream){
try {
this.init(outputStream);
} catch (Exception e) {
logger.error("导出excel出错");
e.printStackTrace();
}
}
private void init(OutputStream fileOutputStream) {
this.outputStream = fileOutputStream;
this.workbook = new HSSFWorkbook();
this.index = 0;
}
public ExportExcelUtil writeHead(Class<?> tClass){
if (tClass==null){
return this;
}
List<String> list = new ArrayList<>();
for (Field filed : getFields(tClass)) {
ExcelExport excelExport = filed.getAnnotation(ExcelExport.class);
list.add(excelExport.columnName());
}
createData(list);
return this;
}
/**
* 获取带有ExcelExport注解的属性
* @param tClass
* @return
*/
private List<Field> getFields(Class<?> tClass){
Field[] fields = tClass.getDeclaredFields();
List<Field> list = new ArrayList<>(fields.length);
for (Field f: fields) { //遍历tClass中的属性
if(f.isAnnotationPresent(ExcelExport.class)){ //有ExcelExport注解的信息属性保留
list.add(f);
}
}
return list;
}
/**
* 行列填充数据
* @param list
*/
private void createData(List<String> list) {
if(sheet == null){
sheet = (HSSFSheet) workbook.createSheet();
}
HSSFRow row = sheet.createRow(index++);
HSSFCell[] cells = new HSSFCell[list.size()];
for(int i=0;i<cells.length;i++) {
cells[i] = row.createCell(i);
cells[i].setCellValue(list.get(i));
}
}
public <T> ExportExcelUtil writeList(List<T> list){
if(list!=null && list.size()>0){
for ( Object obj: list) {
writeObject(obj);
}
}
return this;
}
private void writeObject(Object obj) {
Class clazz = obj.getClass();
List<String> list = new ArrayList<>();
for (Field f: getFields(clazz)) {
f.setAccessible(true);
try {
Object o = f.get(obj);
if(o instanceof Date){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
list.add(sdf.format(o));
}else if(o instanceof BigDecimal){
list.add(String.valueOf(((BigDecimal) o).setScale(2,BigDecimal.ROUND_CEILING)));
}else{
list.add(String.valueOf(o));
}
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error("格式化obj失败");
}
}
if(!list.isEmpty()){
createData(list);
}
}
public void exportData(){
try {
workbook.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
logger.error("创建excel失败");
}
}
}
注解类
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.METHOD, ElementType.ANNOTATION_TYPE})
public @interface ExcelExport {
String columnName() default "";
}
- 例子
public class OperationRecordVO{
@ExcelExport(columnName = "用户名")
private String username;
@ExcelExport(columnName = "角色")
private String roleName;
@ExcelExport(columnName = "IP地址")
private String ip;
@ExcelExport(columnName = "操作内容")
private String context;
private Integer type;
@ExcelExport(columnName = "操作类型")
private String typeStr;
private String objectType;
private String objectId;
@ExcelExport(columnName = "操作时间")
@JsonSerialize(converter = JacksonDateConverter.BacklashDateTimeConverter.class)
private Date createTime;
}
...省去get和set方法
//调用实例
public void exportRecords(String startTime,String endTime,HttpServletResponse response){
List<OperationRecordVO> list = operationRecordService.exportRecordsByDate(startTime,endTime);
//导出excel
String fileName = "操作日志.xls";
try {
response.setContentType("application/octet-stream;");
response.setHeader("Content-disposition",
"attachment; filename=" + new String(fileName.getBytes("UTF-8"), "iso8859-1"));
ExcelExportUtil excelExportUtil = new ExcelExportUtil(response.getOutputStream());
//excel第一列表头信息
excelExportUtil.writeHeader(OperationRecordVO.class);
//写入每一行信息
excelExportUtil.writeObjects(list);
excelExportUtil.exportData();
} catch (IOException e) {
logger.error("导出excel失败...");
e.printStackTrace();
}
}