0
# Data Conversion
1

2
Type conversion system for transforming between Java objects and Excel cell values. EasyExcel provides a pluggable converter system that handles automatic type detection and custom conversion logic for complex data transformations.
3

4
## Capabilities
5

6
### Core Converter Interface
7

8
Primary interface for implementing custom data converters.
9

10
```java { .api }
11
/**
12
 * Interface for converting between Java objects and Excel cell values
13
 * @param <T> Java type that this converter handles
14
 */
15
public interface Converter<T> {
16
    /**
17
     * Get the Java type that this converter supports
18
     * @return Class of supported Java type
19
     */
20
    Class<?> supportJavaTypeKey();
21
    
22
    /**
23
     * Get the Excel cell data type that this converter supports
24
     * @return CellDataTypeEnum of supported Excel type
25
     */
26
    CellDataTypeEnum supportExcelTypeKey();
27
    
28
    /**
29
     * Convert Excel cell data to Java object (for reading)
30
     * @param cellData Cell data from Excel
31
     * @param contentProperty Content property configuration
32
     * @param globalConfiguration Global configuration settings
33
     * @return Converted Java object
34
     * @throws Exception if conversion fails
35
     */
36
    T convertToJavaData(ReadCellData<?> cellData, 
37
                       ExcelContentProperty contentProperty, 
38
                       GlobalConfiguration globalConfiguration) throws Exception;
39
    
40
    /**
41
     * Convert Java object to Excel cell data (for writing)
42
     * @param value Java object to convert
43
     * @param contentProperty Content property configuration
44
     * @param globalConfiguration Global configuration settings
45
     * @return Cell data for Excel
46
     * @throws Exception if conversion fails
47
     */
48
    WriteCellData<?> convertToExcelData(T value, 
49
                                       ExcelContentProperty contentProperty, 
50
                                       GlobalConfiguration globalConfiguration) throws Exception;
51
}
52
```
53

54
### Built-in Converter Classes
55

56
Pre-built converters for automatic type handling.
57

58
```java { .api }
59
/**
60
 * Automatic converter that detects appropriate converter based on type
61
 */
62
public class AutoConverter implements Converter<Object> {
63
    @Override
64
    public Class<?> supportJavaTypeKey() {
65
        return null; // Supports all types
66
    }
67
    
68
    @Override
69
    public CellDataTypeEnum supportExcelTypeKey() {
70
        return null; // Supports all cell types
71
    }
72
    
73
    @Override
74
    public Object convertToJavaData(ReadCellData<?> cellData, 
75
                                   ExcelContentProperty contentProperty, 
76
                                   GlobalConfiguration globalConfiguration) throws Exception {
77
        // Automatic type detection and conversion
78
        return null; // Implementation handles detection
79
    }
80
    
81
    @Override
82
    public WriteCellData<?> convertToExcelData(Object value, 
83
                                              ExcelContentProperty contentProperty, 
84
                                              GlobalConfiguration globalConfiguration) throws Exception {
85
        // Automatic type detection and conversion
86
        return null; // Implementation handles detection
87
    }
88
}
89

90
/**
91
 * Converter for handling null values
92
 */
93
public class NullableObjectConverter implements Converter<Object> {
94
    @Override
95
    public Class<?> supportJavaTypeKey() {
96
        return Object.class;
97
    }
98
    
99
    @Override
100
    public CellDataTypeEnum supportExcelTypeKey() {
101
        return CellDataTypeEnum.EMPTY;
102
    }
103
    
104
    @Override
105
    public Object convertToJavaData(ReadCellData<?> cellData, 
106
                                   ExcelContentProperty contentProperty, 
107
                                   GlobalConfiguration globalConfiguration) {
108
        if (cellData == null || cellData.getData() == null) {
109
            return null;
110
        }
111
        // Delegate to appropriate converter
112
        return cellData.getData();
113
    }
114
    
115
    @Override
116
    public WriteCellData<?> convertToExcelData(Object value, 
117
                                              ExcelContentProperty contentProperty, 
118
                                              GlobalConfiguration globalConfiguration) {
119
        if (value == null) {
120
            return new WriteCellData<>("");
121
        }
122
        // Delegate to appropriate converter
123
        return new WriteCellData<>(value);
124
    }
125
}
126
```
127

128
### Converter Management
129

130
Classes for loading and managing converters.
131

132
```java { .api }
133
/**
134
 * Default converter loader and registry
135
 */
136
public class DefaultConverterLoader {
137
    /**
138
     * Load all default converters
139
     * @return Map of converter configurations
140
     */
141
    public static Map<String, Converter<?>> loadDefaultReadConverter() {
142
        // Returns map of all built-in read converters
143
        return null; // Implementation loads converters
144
    }
145
    
146
    /**
147
     * Load all default write converters
148
     * @return Map of converter configurations
149
     */
150
    public static Map<String, Converter<?>> loadDefaultWriteConverter() {
151
        // Returns map of all built-in write converters
152
        return null; // Implementation loads converters
153
    }
154
    
155
    /**
156
     * Load converters for specific type
157
     * @param clazz Java class to load converters for
158
     * @return Map of converters for the class
159
     */
160
    public static Map<String, Converter<?>> loadConverter(Class<?> clazz) {
161
        // Returns converters specific to the class
162
        return null; // Implementation loads converters
163
    }
164
}
165

166
/**
167
 * Converter holder for managing converter instances
168
 */
169
public class ConverterHolder {
170
    /**
171
     * Register a custom converter
172
     * @param converter Converter to register
173
     */
174
    public void registerConverter(Converter<?> converter);
175
    
176
    /**
177
     * Get converter for Java type and Excel type combination
178
     * @param javaType Java class
179
     * @param excelType Excel cell data type
180
     * @return Appropriate converter or null
181
     */
182
    public Converter<?> getConverter(Class<?> javaType, CellDataTypeEnum excelType);
183
    
184
    /**
185
     * Get all registered converters
186
     * @return Map of all converters
187
     */
188
    public Map<String, Converter<?>> getAllConverter();
189
}
190
```
191

192
### Cell Data Classes
193

194
Classes representing cell data during conversion.
195

196
```java { .api }
197
/**
198
 * Cell data structure used during reading operations
199
 * @param <T> Type of data contained in the cell
200
 */
201
public class ReadCellData<T> {
202
    /**
203
     * Cell data type
204
     */
205
    private CellDataTypeEnum type;
206
    
207
    /**
208
     * Cell data value
209
     */
210
    private T data;
211
    
212
    /**
213
     * Cell formula (if applicable)
214
     */
215
    private String formula;
216
    
217
    /**
218
     * String representation of cell value
219
     */
220
    private String stringValue;
221
    
222
    /**
223
     * Number representation of cell value
224
     */
225
    private BigDecimal numberValue;
226
    
227
    /**
228
     * Boolean representation of cell value
229
     */
230
    private Boolean booleanValue;
231
    
232
    /**
233
     * Date representation of cell value
234
     */
235
    private Date dateValue;
236
    
237
    // Getters and setters...
238
}
239

240
/**
241
 * Cell data structure used during writing operations
242
 * @param <T> Type of data to write to the cell
243
 */
244
public class WriteCellData<T> {
245
    /**
246
     * Cell data type
247
     */
248
    private CellDataTypeEnum type;
249
    
250
    /**
251
     * Cell data value
252
     */
253
    private T data;
254
    
255
    /**
256
     * Cell formula (if applicable)
257
     */
258
    private String formula;
259
    
260
    /**
261
     * Data format for the cell
262
     */
263
    private String dataFormat;
264
    
265
    /**
266
     * Data format index
267
     */
268
    private Short dataFormatIndex;
269
    
270
    // Constructors
271
    public WriteCellData();
272
    public WriteCellData(T data);
273
    public WriteCellData(String formula, T data);
274
    
275
    // Getters and setters...
276
}
277
```
278

279
### Content Property Classes
280

281
Classes providing configuration context for converters.
282

283
```java { .api }
284
/**
285
 * Excel content property containing field configuration
286
 */
287
public class ExcelContentProperty {
288
    /**
289
     * Field information
290
     */
291
    private Field field;
292
    
293
    /**
294
     * Date format configuration
295
     */
296
    private DateTimeFormatProperty dateTimeFormatProperty;
297
    
298
    /**
299
     * Number format configuration
300
     */
301
    private NumberFormatProperty numberFormatProperty;
302
    
303
    /**
304
     * Custom converter class
305
     */
306
    private Class<? extends Converter<?>> converter;
307
    
308
    /**
309
     * Column index
310
     */
311
    private Integer index;
312
    
313
    /**
314
     * Column order
315
     */
316
    private Integer order;
317
    
318
    // Getters and setters...
319
}
320

321
/**
322
 * Date/time format property
323
 */
324
public class DateTimeFormatProperty {
325
    /**
326
     * Date format pattern
327
     */
328
    private String format;
329
    
330
    /**
331
     * Use 1904 date windowing
332
     */
333
    private Boolean use1904windowing;
334
    
335
    // Getters and setters...
336
}
337

338
/**
339
 * Number format property
340
 */
341
public class NumberFormatProperty {
342
    /**
343
     * Number format pattern
344
     */
345
    private String format;
346
    
347
    /**
348
     * Number format index
349
     */
350
    private Short index;
351
    
352
    // Getters and setters...
353
}
354
```
355

356
## Usage Examples
357

358
### Custom Enum Converter
359
```java
360
import com.alibaba.excel.converters.Converter;
361
import com.alibaba.excel.enums.CellDataTypeEnum;
362
import com.alibaba.excel.metadata.GlobalConfiguration;
363
import com.alibaba.excel.metadata.data.ReadCellData;
364
import com.alibaba.excel.metadata.data.WriteCellData;
365
import com.alibaba.excel.metadata.property.ExcelContentProperty;
366

367
// Enum definition
368
public enum UserStatus {
369
    ACTIVE("Active"),
370
    INACTIVE("Inactive"),
371
    PENDING("Pending"),
372
    SUSPENDED("Suspended");
373
    
374
    private final String displayName;
375
    
376
    UserStatus(String displayName) {
377
        this.displayName = displayName;
378
    }
379
    
380
    public String getDisplayName() {
381
        return displayName;
382
    }
383
    
384
    public static UserStatus fromDisplayName(String displayName) {
385
        for (UserStatus status : values()) {
386
            if (status.displayName.equals(displayName)) {
387
                return status;
388
            }
389
        }
390
        throw new IllegalArgumentException("Unknown status: " + displayName);
391
    }
392
}
393

394
// Custom converter implementation
395
public class UserStatusConverter implements Converter<UserStatus> {
396
    @Override
397
    public Class<?> supportJavaTypeKey() {
398
        return UserStatus.class;
399
    }
400
    
401
    @Override
402
    public CellDataTypeEnum supportExcelTypeKey() {
403
        return CellDataTypeEnum.STRING;
404
    }
405
    
406
    @Override
407
    public UserStatus convertToJavaData(ReadCellData<?> cellData, 
408
                                       ExcelContentProperty contentProperty,
409
                                       GlobalConfiguration globalConfiguration) throws Exception {
410
        String cellValue = cellData.getStringValue();
411
        if (cellValue == null || cellValue.trim().isEmpty()) {
412
            return null;
413
        }
414
        
415
        try {
416
            return UserStatus.fromDisplayName(cellValue.trim());
417
        } catch (IllegalArgumentException e) {
418
            throw new ExcelDataConvertException(
419
                "Cannot convert '" + cellValue + "' to UserStatus", e);
420
        }
421
    }
422
    
423
    @Override
424
    public WriteCellData<?> convertToExcelData(UserStatus value, 
425
                                              ExcelContentProperty contentProperty,
426
                                              GlobalConfiguration globalConfiguration) throws Exception {
427
        if (value == null) {
428
            return new WriteCellData<>("");
429
        }
430
        return new WriteCellData<>(value.getDisplayName());
431
    }
432
}
433

434
// Data class using custom converter
435
public class UserData {
436
    @ExcelProperty("Name")
437
    private String name;
438
    
439
    @ExcelProperty(value = "Status", converter = UserStatusConverter.class)
440
    private UserStatus status;
441
    
442
    @ExcelProperty("Email")
443
    private String email;
444
    
445
    // Getters and setters...
446
}
447
```
448

449
### Custom Date Converter
450
```java
451
import com.alibaba.excel.converters.Converter;
452
import com.alibaba.excel.enums.CellDataTypeEnum;
453
import java.time.LocalDateTime;
454
import java.time.format.DateTimeFormatter;
455

456
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
457
    private static final DateTimeFormatter FORMATTER = 
458
        DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
459
    
460
    @Override
461
    public Class<?> supportJavaTypeKey() {
462
        return LocalDateTime.class;
463
    }
464
    
465
    @Override
466
    public CellDataTypeEnum supportExcelTypeKey() {
467
        return CellDataTypeEnum.STRING;
468
    }
469
    
470
    @Override
471
    public LocalDateTime convertToJavaData(ReadCellData<?> cellData, 
472
                                          ExcelContentProperty contentProperty,
473
                                          GlobalConfiguration globalConfiguration) throws Exception {
474
        String cellValue = cellData.getStringValue();
475
        if (cellValue == null || cellValue.trim().isEmpty()) {
476
            return null;
477
        }
478
        
479
        try {
480
            return LocalDateTime.parse(cellValue.trim(), FORMATTER);
481
        } catch (Exception e) {
482
            throw new ExcelDataConvertException(
483
                "Cannot parse date: " + cellValue, e);
484
        }
485
    }
486
    
487
    @Override
488
    public WriteCellData<?> convertToExcelData(LocalDateTime value, 
489
                                              ExcelContentProperty contentProperty,
490
                                              GlobalConfiguration globalConfiguration) throws Exception {
491
        if (value == null) {
492
            return new WriteCellData<>("");
493
        }
494
        return new WriteCellData<>(value.format(FORMATTER));
495
    }
496
}
497

498
// Usage in data class
499
public class EventData {
500
    @ExcelProperty("Event Name")
501
    private String name;
502
    
503
    @ExcelProperty(value = "Event Date", converter = LocalDateTimeConverter.class)
504
    private LocalDateTime eventDate;
505
    
506
    @ExcelProperty("Description")
507
    private String description;
508
    
509
    // Getters and setters...
510
}
511
```
512

513
### Complex Object Converter
514
```java
515
import com.alibaba.excel.converters.Converter;
516
import com.alibaba.excel.enums.CellDataTypeEnum;
517
import com.fasterxml.jackson.databind.ObjectMapper;
518

519
// Complex object to convert
520
public class Address {
521
    private String street;
522
    private String city;
523
    private String zipCode;
524
    private String country;
525
    
526
    // Constructors, getters, setters...
527
    
528
    @Override
529
    public String toString() {
530
        return street + ", " + city + ", " + zipCode + ", " + country;
531
    }
532
}
533

534
// JSON-based converter for complex objects
535
public class AddressConverter implements Converter<Address> {
536
    private static final ObjectMapper objectMapper = new ObjectMapper();
537
    
538
    @Override
539
    public Class<?> supportJavaTypeKey() {
540
        return Address.class;
541
    }
542
    
543
    @Override
544
    public CellDataTypeEnum supportExcelTypeKey() {
545
        return CellDataTypeEnum.STRING;
546
    }
547
    
548
    @Override
549
    public Address convertToJavaData(ReadCellData<?> cellData, 
550
                                    ExcelContentProperty contentProperty,
551
                                    GlobalConfiguration globalConfiguration) throws Exception {
552
        String cellValue = cellData.getStringValue();
553
        if (cellValue == null || cellValue.trim().isEmpty()) {
554
            return null;
555
        }
556
        
557
        try {
558
            // Try parsing as JSON first
559
            if (cellValue.startsWith("{")) {
560
                return objectMapper.readValue(cellValue, Address.class);
561
            }
562
            
563
            // Fallback to simple comma-separated format
564
            String[] parts = cellValue.split(",");
565
            if (parts.length >= 4) {
566
                Address address = new Address();
567
                address.setStreet(parts[0].trim());
568
                address.setCity(parts[1].trim());
569
                address.setZipCode(parts[2].trim());
570
                address.setCountry(parts[3].trim());
571
                return address;
572
            }
573
            
574
            throw new IllegalArgumentException("Invalid address format");
575
        } catch (Exception e) {
576
            throw new ExcelDataConvertException(
577
                "Cannot convert address: " + cellValue, e);
578
        }
579
    }
580
    
581
    @Override
582
    public WriteCellData<?> convertToExcelData(Address value, 
583
                                              ExcelContentProperty contentProperty,
584
                                              GlobalConfiguration globalConfiguration) throws Exception {
585
        if (value == null) {
586
            return new WriteCellData<>("");
587
        }
588
        
589
        // Write as JSON for full fidelity
590
        try {
591
            String json = objectMapper.writeValueAsString(value);
592
            return new WriteCellData<>(json);
593
        } catch (Exception e) {
594
            // Fallback to string representation
595
            return new WriteCellData<>(value.toString());
596
        }
597
    }
598
}
599
```
600

601
### Currency Converter with Localization
602
```java
603
import com.alibaba.excel.converters.Converter;
604
import com.alibaba.excel.enums.CellDataTypeEnum;
605
import java.math.BigDecimal;
606
import java.text.NumberFormat;
607
import java.util.Currency;
608
import java.util.Locale;
609

610
public class CurrencyConverter implements Converter<BigDecimal> {
611
    private final Currency currency;
612
    private final Locale locale;
613
    private final NumberFormat currencyFormat;
614
    
615
    public CurrencyConverter() {
616
        this(Currency.getInstance("USD"), Locale.US);
617
    }
618
    
619
    public CurrencyConverter(Currency currency, Locale locale) {
620
        this.currency = currency;
621
        this.locale = locale;
622
        this.currencyFormat = NumberFormat.getCurrencyInstance(locale);
623
        this.currencyFormat.setCurrency(currency);
624
    }
625
    
626
    @Override
627
    public Class<?> supportJavaTypeKey() {
628
        return BigDecimal.class;
629
    }
630
    
631
    @Override
632
    public CellDataTypeEnum supportExcelTypeKey() {
633
        return CellDataTypeEnum.STRING;  // Handle formatted currency strings
634
    }
635
    
636
    @Override
637
    public BigDecimal convertToJavaData(ReadCellData<?> cellData, 
638
                                       ExcelContentProperty contentProperty,
639
                                       GlobalConfiguration globalConfiguration) throws Exception {
640
        String cellValue = cellData.getStringValue();
641
        if (cellValue == null || cellValue.trim().isEmpty()) {
642
            return null;
643
        }
644
        
645
        try {
646
            // Remove currency symbols and parse
647
            String cleanValue = cellValue.replaceAll("[^\\d.,+-]", "");
648
            return new BigDecimal(cleanValue);
649
        } catch (Exception e) {
650
            throw new ExcelDataConvertException(
651
                "Cannot parse currency: " + cellValue, e);
652
        }
653
    }
654
    
655
    @Override
656
    public WriteCellData<?> convertToExcelData(BigDecimal value, 
657
                                              ExcelContentProperty contentProperty,
658
                                              GlobalConfiguration globalConfiguration) throws Exception {
659
        if (value == null) {
660
            return new WriteCellData<>("");
661
        }
662
        
663
        String formattedValue = currencyFormat.format(value);
664
        return new WriteCellData<>(formattedValue);
665
    }
666
}
667

668
// Usage with custom currency
669
public class SalesData {
670
    @ExcelProperty("Product")
671
    private String product;
672
    
673
    @ExcelProperty(value = "Price", converter = CurrencyConverter.class)
674
    private BigDecimal price;
675
    
676
    @ExcelProperty("Quantity")
677
    private Integer quantity;
678
    
679
    // Getters and setters...
680
}
681
```
682

683
### Registering Custom Converters Globally
684
```java
685
import com.alibaba.excel.EasyExcel;
686
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
687

688
// Register converters globally for reading
689
ExcelReaderBuilder readerBuilder = EasyExcel.read("data.xlsx", UserData.class, listener);
690

691
// Register custom converter (would be done through configuration in actual implementation)
692
// readerBuilder.registerConverter(new UserStatusConverter());
693

694
// For writing with custom converters
695
ExcelWriterBuilder writerBuilder = EasyExcel.write("output.xlsx", UserData.class);
696

697
// Register custom converter (would be done through configuration in actual implementation)  
698
// writerBuilder.registerConverter(new UserStatusConverter());
699

700
writerBuilder.sheet("Users").doWrite(userData);
701
```
702

703
### Conditional Conversion Logic
704
```java
705
import com.alibaba.excel.converters.Converter;
706
import com.alibaba.excel.enums.CellDataTypeEnum;
707

708
public class SmartNumberConverter implements Converter<Number> {
709
    @Override
710
    public Class<?> supportJavaTypeKey() {
711
        return Number.class;
712
    }
713
    
714
    @Override
715
    public CellDataTypeEnum supportExcelTypeKey() {
716
        return CellDataTypeEnum.STRING;
717
    }
718
    
719
    @Override
720
    public Number convertToJavaData(ReadCellData<?> cellData, 
721
                                   ExcelContentProperty contentProperty,
722
                                   GlobalConfiguration globalConfiguration) throws Exception {
723
        String cellValue = cellData.getStringValue();
724
        if (cellValue == null || cellValue.trim().isEmpty()) {
725
            return null;
726
        }
727
        
728
        cellValue = cellValue.trim();
729
        
730
        try {
731
            // Detect if it's a percentage
732
            if (cellValue.endsWith("%")) {
733
                double value = Double.parseDouble(cellValue.substring(0, cellValue.length() - 1));
734
                return value / 100.0; // Convert percentage to decimal
735
            }
736
            
737
            // Detect if it contains decimal point
738
            if (cellValue.contains(".")) {
739
                return Double.parseDouble(cellValue);
740
            }
741
            
742
            // Try as integer first
743
            long longValue = Long.parseLong(cellValue);
744
            if (longValue >= Integer.MIN_VALUE && longValue <= Integer.MAX_VALUE) {
745
                return (int) longValue;
746
            }
747
            return longValue;
748
            
749
        } catch (NumberFormatException e) {
750
            throw new ExcelDataConvertException(
751
                "Cannot parse number: " + cellValue, e);
752
        }
753
    }
754
    
755
    @Override
756
    public WriteCellData<?> convertToExcelData(Number value, 
757
                                              ExcelContentProperty contentProperty,
758
                                              GlobalConfiguration globalConfiguration) throws Exception {
759
        if (value == null) {
760
            return new WriteCellData<>("");
761
        }
762
        
763
        // Format based on type
764
        if (value instanceof Double || value instanceof Float) {
765
            BigDecimal bd = BigDecimal.valueOf(value.doubleValue());
766
            return new WriteCellData<>(bd.toPlainString());
767
        }
768
        
769
        return new WriteCellData<>(value.toString());
770
    }
771
}
772
```