需要的jar
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
注解使用
@ExcelTableName(tableName = “人员数据”)//设置一个工作表名称,也可以不设置
@Excel注解有三个属性值name:表格当前列的列名,width:当前列的宽度;skip:是否忽略当前字段转换
@ExcelTableName(tableName = "人员数据")
public class User {
@Excel(name = "姓名")
private String name;
@Excel(name = "年龄", width = 3)
private String age;
@Excel(skip = true)
private String password;
@Excel(name = "xx", width = 6)
private Double xx;
@Excel(name = "yy", width = 18)
private Date yy;
@Excel(name = "锁定", width = 5)
private Boolean locked;
@Excel(name = "金额")
private BigDecimal db;
//省略get和set...
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Double getXx() {
return xx;
}
public void setXx(Double xx) {
this.xx = xx;
}
public Date getYy() {
return yy;
}
public void setYy(Date yy) {
this.yy = yy;
}
public Boolean getLocked() {
return locked;
}
public void setLocked(Boolean locked) {
this.locked = locked;
}
public BigDecimal getDb() {
return db;
}
public void setDb(BigDecimal db) {
this.db = db;
}
}
测试代码
public class Test {
public static void main(String[] args) throws Exception {
List<User> list = new ArrayList<User>();
User u = new User();
u.setAge("3");
u.setName("fdsafdsa");
u.setXx(123.23D);
u.setYy(new Date());
u.setLocked(false);
u.setDb(new BigDecimal(123));
list.add(u);
u = new User();
u.setAge("23");
u.setName("张珊");
u.setXx(123.23D);
u.setYy(new Date());
u.setLocked(true);
u.setDb(new BigDecimal(234));
list.add(u);
u = new User();
u.setAge("123");
u.setName("fdsafdsa");
u.setXx(123.23D);
u.setYy(new Date());
u.setLocked(false);
u.setDb(new BigDecimal(2344));
list.add(u);
u = new User();
u.setAge("22");
u.setName("fdsafdsa");
u.setXx(123.23D);
u.setYy(new Date());
u.setLocked(true);
u.setDb(new BigDecimal(908));
list.add(u);
//设置数据替换:例如Bean里面 sex字段表示性别: 值: 0:表示男; 1:表示女
//在这里我们可以设置
//map.put("0","男")
//map.put("1","女")
//def.set("sex",map)
//这样导出的表格值性别这一列将会以男或者女展示
ExcelDataFormatter edf = new ExcelDataFormatter();
Map<String, String> map = new HashMap<String, String>();
map.put("true", "未锁");
map.put("false", "锁定");
edf.set("locked", map);
//导出表格
ExcelUtils excelUtils = new ExcelUtils();
excelUtils.setExcelDataFormatter(edf);
excelUtils.writeToFile("D:\\x.xlsx", list,list1,list2....);//可以转入多个list对象,将会在一个文件里面创建多张表单
//将表格转出bean对象
List<User> xx = new ExcelUtils(new User()).readFromFile(new File("D:\\x.xlsx"));
System.out.println(JSON.toJSONString(xx));
}
}
注解类
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface Excel {
//列名
String name() default "";
//宽度
int width() default 4;
//列名的层级,设置的数必须大于0,相同的数值为同一个层级,从小到大排序
//忽略该字段
boolean skip() default false;
}
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface ExcelTableName {
String tableName() default "";
}
处理代码
public class ExcelDataFormatter {
private Map<String,Map<String,String>> formatter=new HashMap<String, Map<String,String>>();
public void set(String key,Map<String,String> map){
formatter.put(key, map);
}
public Map<String,String> get(String key){
return formatter.get(key);
}
}
public class ExcelUtils<E> {
private E e;
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private int etimes = 0;
private static ExcelDataFormatter edf;
public void setExcelDataFormatter(ExcelDataFormatter edf) {
this.edf = edf;
};
public ExcelUtils(E e) {
this.e = e;
}
public ExcelUtils() {
}
public E get() throws InstantiationException, IllegalAccessException {
return (E) e.getClass().newInstance();
}
/**
* 将数据写入到EXCEL文档
*
* @param lists
* 可以传递多个数据集合
*
* 数据格式化，比如有些数字代表的状态，像是0:女，1：男，或者0：正常，1：锁定，变成可读的文字
* 该字段仅仅针对Boolean,Integer两种类型作处理
* @param filePath
* 文件路径
* @throws Exception
*/
public static void writeToFile ( String filePath,List<?>... lists) throws Exception {
// 创建并获取工作簿对象
Workbook wb = new SXSSFWorkbook();
// FileOutputStream out = null;
for (List<?> beans:lists) {
wb = getWorkBook(beans,edf,wb);
// 写入到文件
}
FileOutputStream out = new FileOutputStream(filePath);
wb.write(out);
out.close();
}
/**
* 获得Workbook对象
*
* @param list
* 数据集合
* @param edf
* @return Workbook
* @throws Exception
*/
private static <T> Workbook getWorkBook(List<T> list, ExcelDataFormatter edf, Workbook wb) throws Exception {
if (list == null || list.size() == 0){
return wb;
}
Class clsaa = list.get(0).getClass();
// 创建一个工作表sheet
ExcelTableName excelTableName = (ExcelTableName) clsaa.getAnnotation(ExcelTableName.class);
String tableName = excelTableName.tableName();
if ("".equals(tableName)){
tableName = null;
}
Sheet sheet = wb.createSheet(tableName);
// 申明行
Row row = sheet.createRow(0);
// 申明单元格
Cell cell = null;
CreationHelper createHelper = wb.getCreationHelper();
Field[] fields =clsaa.getDeclaredFields();
XSSFCellStyle titleStyle = (XSSFCellStyle) wb.createCellStyle();
titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 设置前景色
titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(159, 213, 183)));
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setColor(HSSFColor.BROWN.index);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 设置字体
titleStyle.setFont(font);
int columnIndex = 0;
Excel excel = null;
for (Field field : fields) {
field.setAccessible(true);
excel = field.getAnnotation(Excel.class);
if (excel == null || excel.skip() == true) {
continue;
}
// 列宽注意乘256
sheet.setColumnWidth(columnIndex, excel.width() * 400);
// 写入标题
cell = row.createCell(columnIndex);
cell.setCellStyle(titleStyle);
cell.setCellValue(excel.name());
columnIndex++;
}
int rowIndex = 1;
CellStyle cs = wb.createCellStyle();
for (T t : list) {
row = sheet.createRow(rowIndex);
columnIndex = 0;
Object o = null;
for (Field field : fields) {
field.setAccessible(true);
// 忽略标记skip的字段
excel = field.getAnnotation(Excel.class);
if (excel == null || excel.skip() == true) {
continue;
}
// 数据
cell = row.createCell(columnIndex);
o = field.get(t);
// 如果数据为空,填充""
if (o == null){
cell.setCellValue("");
}else if (o instanceof Date) {// 处理日期类型
cs.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
cell.setCellStyle(cs);
cell.setCellValue((Date) field.get(t));
} else if (o instanceof Double || o instanceof Float) {
cell.setCellValue((Double) field.get(t));
} else if (o instanceof Boolean) {
Boolean bool = (Boolean) field.get(t);
if (ExcelUtils.edf == null) {
cell.setCellValue(bool);
} else {
String str = field.getName();
Map<String, String> map = ExcelUtils.edf.get(field.getName());
if (map == null) {
cell.setCellValue(bool);
} else {
cell.setCellValue(map.get(bool.toString().toLowerCase()));
}
}
} else if (o instanceof Integer) {
Integer intValue = (Integer) field.get(t);
if (ExcelUtils.edf == null) {
cell.setCellValue(intValue);
} else {
Map<String, String> map = ExcelUtils.edf.get(field.getName());
if (map == null) {
cell.setCellValue(intValue);
} else {
cell.setCellValue(map.get(intValue.toString()));
}
}
} else {
cell.setCellValue(field.get(t).toString());
}
columnIndex++;
}
rowIndex++;
}
return wb;
}
/**
* 从文件读取数据，最好是所有的单元格都是文本格式，日期格式要求yyyy-MM-dd HH:mm:ss,布尔类型0：真，1：假
*
* @param file
* Excel文件，支持xlsx后缀，xls的没写，基本一样
* @return
* @throws Exception
*/
public List<E> readFromFile(File file) throws Exception {
Field[] fields = e.getClass().getDeclaredFields();
Map<String, String> textToKey = new HashMap<String, String>();
Excel _excel = null;
for (Field field : fields) {
_excel = field.getAnnotation(Excel.class);
if (_excel == null || _excel.skip() == true) {
continue;
}
textToKey.put(_excel.name(), field.getName());
}
InputStream is = new FileInputStream(file);
Workbook wb = new XSSFWorkbook(is);
Sheet sheet = wb.getSheetAt(0);
Row title = sheet.getRow(0);
// 标题数组，后面用到，根据索引去标题名称，通过标题名称去字段名称用到 textToKey
String[] titles = new String[title.getPhysicalNumberOfCells()];
for (int i = 0; i < title.getPhysicalNumberOfCells(); i++) {
titles[i] = title.getCell(i).getStringCellValue();
}
List<E> list = new ArrayList<E>();
E e = null;
int rowIndex = 0;
int columnCount = titles.length;
Cell cell = null;
Row row = null;
for (Iterator<Row> it = sheet.rowIterator(); it.hasNext();) {
row = it.next();
if (rowIndex++ == 0) {
continue;
}
if (row == null) {
break;
}
e = get();
for (int i = 0; i < columnCount; i++) {
cell = row.getCell(i);
etimes = 0;
readCellContent(textToKey.get(titles[i]), fields, cell, e, edf);
}
list.add(e);
}
return list;
}
/**
*
* @param key
* 当前单元格对应的Bean字段
* @param fields
* Bean所有的字段数组
* @param cell
* 单元格对象
* @param e
* @throws Exception
*/
private void readCellContent(String key, Field[] fields, Cell cell, E e, ExcelDataFormatter edf) throws Exception {
Object o = null;
try {
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_BOOLEAN:
o = cell.getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
o = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
o = DateUtil.getJavaDate(cell.getNumericCellValue());
}
break;
case XSSFCell.CELL_TYPE_STRING:
o = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_ERROR:
o = cell.getErrorCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
o = null;
break;
case XSSFCell.CELL_TYPE_FORMULA:
o = cell.getCellFormula();
break;
default:
o = null;
break;
}
if (o == null || "".equals(o))
return;
for (Field field : fields) {
field.setAccessible(true);
if (field.getName().equals(key)) {
Boolean bool = true;
Map<String, String> map = null;
if (edf == null) {
bool = false;
} else {
map = edf.get(field.getName());
if (map == null) {
bool = false;
}
}
if (field.getType().equals(Date.class)) {
if (o.getClass().equals(Date.class)) {
field.set(e, o);
} else {
field.set(e, sdf.parse(o.toString()));
}
} else if (field.getType().equals(String.class)) {
if (o.getClass().equals(String.class)) {
field.set(e, o);
} else {
field.set(e, o.toString());
}
} else if (field.getType().equals(Long.class)) {
if (o.getClass().equals(Long.class)) {
field.set(e, o);
} else {
field.set(e, Long.parseLong(o.toString()));
}
} else if (field.getType().equals(Integer.class)) {
if (o.getClass().equals(Integer.class)) {
field.set(e, o);
} else {
// 检查是否需要转换
if (bool) {
field.set(e, map.get(o.toString()) != null ? Integer.parseInt(map.get(o.toString())) : Integer.parseInt(o.toString()));
} else {
field.set(e, Integer.parseInt(o.toString()));
}
}
} else if (field.getType().equals(BigDecimal.class)) {
if (o.getClass().equals(BigDecimal.class)) {
field.set(e, o);
} else {
field.set(e, BigDecimal.valueOf(Double.parseDouble(o.toString())));
}
} else if (field.getType().equals(Boolean.class)) {
if (o.getClass().equals(Boolean.class)) {
field.set(e, o);
} else {
// 检查是否需要转换
if (bool) {
field.set(e, map.get(o.toString()) != null ? Boolean.parseBoolean(map.get(o.toString())) : Boolean.parseBoolean(o.toString()));
} else {
field.set(e, Boolean.parseBoolean(o.toString()));
}
}
} else if (field.getType().equals(Float.class)) {
if (o.getClass().equals(Float.class)) {
field.set(e, o);
} else {
field.set(e, Float.parseFloat(o.toString()));
}
} else if (field.getType().equals(Double.class)) {
if (o.getClass().equals(Double.class)) {
field.set(e, o);
} else {
field.set(e, Double.parseDouble(o.toString()));
}
}
}
}
} catch (Exception ex) {
ex.printStackTrace();
// 如果还是读到的数据格式还是不对，只能放弃了
if (etimes > 7) {
throw ex;
}
etimes++;
if (o == null) {
readCellContent(key, fields, cell, e, edf);
}
}
}
}
扫一扫
672
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
