Java POIツールクラス-POJOのクラスフィールドに基づいて、Excelインポートエクスポートをカスタマイズします.
1、EXcelを定義してエクスポート注記類(ExcelAnnotation)を導入し、POJO類とのやり取りを便利にする
2、POJOフィールドに注記(@ExcelAnnotation)を入れる
3、Excelインポートツールクラス(ExcelImportUtils)
4、Excelエクスポートツールクラス(ExcelExportUtils)
添付:Excelインポート画像サポート
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* excel ， Annotation ，
*/
@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAnnotation {
/** excel */
public String exportName();
/** , yyyy-MM-dd HH:mm:ss */
public String pattern() default "";
/** excel */
public int order() default 0;
/** ,wutiancai **/
public String dictName() default "";
/** ,wutiancai **/
public int columnWidth() default 0;
}
2、POJOフィールドに注記(@ExcelAnnotation)を入れる
package com.bdxh.framework.commons.excel;
import java.util.Date;
/**
* excel , excel
*/
public class Testpojo {
@ExcelAnnotation(exportName = " ", order = 4)
String username;
@ExcelAnnotation(exportName = " ", order = 3)
String loginname;
@ExcelAnnotation(exportName = " ", order = 2)
Integer age;
@ExcelAnnotation(exportName = " ", order = 1)
Long money;
@ExcelAnnotation(exportName = " ", pattern = "yyyy-MM-dd HH:mm:ss", order = 0)
Date createtime;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getLoginname() {
return loginname;
}
public void setLoginname(String loginname) {
this.loginname = loginname;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Long getMoney() {
return money;
}
public void setMoney(Long money) {
this.money = money;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
}
3、Excelインポートツールクラス(ExcelImportUtils)
import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.commons.util.DateUtil;
import org.springframework.web.multipart.MultipartFile;
/**
* EXCEL , pojo
*/
public class ExcekImportUtils<T> {
private static final Logger logger = LoggerFactory.getLogger(ExcekImportUtils.class);
public ExcekImportUtils() {
}
/**
* excel
*/
public List<T> importExcel(File file, Class<T> clazz) {
List<T> dist = new ArrayList<T>();
try {
Field filed[] = clazz.getDeclaredFields();
// Annotation ， , map
Map<String, Object[]> fieldmap = new HashMap<String, Object[]>();
//
for (int i = 0; i < filed.length; i++) {
Field f = filed[i];
ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
if (exa != null) {
// Annotation Setter
String fieldname = f.getName();
String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
// method，
Method setMethod = clazz.getMethod(setMethodName, new Class[] { f.getType() });
String pattern = exa.pattern();
// method Annotaion key 。
fieldmap.put(exa.exportName(), new Object[] { setMethod, pattern });
}
}
FileInputStream in = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(in);
Sheet sheet = workbook.getSheetAt(0); // sheet
// XSSFWorkbook xwb = new XSSFWorkbook(in);
// XSSFSheet sheet = xwb.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
Row title = rows.next(); //
Iterator<Cell> cellTitle = title.cellIterator(); //
Map<Integer, String> titlemap = new HashMap<Integer, String>();// map 。
//
for (int i = 0; cellTitle.hasNext(); i++) {
Cell cell = cellTitle.next();
String value = cell.getStringCellValue();
titlemap.put(i, value);
}
//
while (rows.hasNext()) {
Row rown = rows.next();
T tObject = clazz.newInstance(); //
//
for (int j = 0; j < rown.getLastCellNum(); j++) {
Cell cell = rown.getCell(j, Row.RETURN_BLANK_AS_NULL);
String titleString = (String) titlemap.get(j);//
// Annotation ， set ，
if (fieldmap.containsKey(titleString)) {
Method setMethod = (Method) fieldmap.get(titleString)[0];
Type[] ts = setMethod.getGenericParameterTypes();// setter
String xclass = ts[0].toString(); //
//
if (xclass.equals("class java.lang.String")) {
setMethod.invoke(tObject, this.getCellValue(cell));
} else if (xclass.equals("class java.util.Date")) {
String pattern = (String) fieldmap.get(titleString)[1];
if (StringUtils.isBlank(pattern))
pattern = "yyyy-MM-dd HH:mm:ss";
setMethod.invoke(tObject, DateUtil.DateFormatString(this.getCellValue(cell), pattern));
} else if (xclass.equals("class java.lang.Boolean")) {
Boolean boolname = true;
if (this.getCellValue(cell).equals(" ")) {
boolname = false;
}
setMethod.invoke(tObject, boolname);
} else if (xclass.equals("class java.lang.Integer")) {
setMethod.invoke(tObject, Integer.parseInt(this.getCellValue(cell)));
} else if (xclass.equals("class java.lang.Long")) {
setMethod.invoke(tObject, new Long(this.getCellValue(cell)));
} else if (xclass.equals("double")){
setMethod.invoke(tObject, new Double(this.getCellValue(cell)));
}
}
}
dist.add(tObject);
}
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage(), e);
return null;
}
return dist;
}
/**
* excel
*/
public List<T> importExcel(MultipartFile multipartFile, Class<T> clazz) {
List<T> dist = new ArrayList<T>();
try {
Field filed[] = clazz.getDeclaredFields();
// Annotation ， , map
Map<String, Object[]> fieldmap = new HashMap<String, Object[]>();
//
for (int i = 0; i < filed.length; i++) {
Field f = filed[i];
ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
if (exa != null) {
// Annotation Setter
String fieldname = f.getName();
String setMethodName = "set" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
// method，
Method setMethod = clazz.getMethod(setMethodName, new Class[] { f.getType() });
String pattern = exa.pattern();
// method Annotaion key 。
fieldmap.put(exa.exportName(), new Object[] { setMethod, pattern });
}
}
Workbook workbook = WorkbookFactory.create(multipartFile.getInputStream());
Sheet sheet = workbook.getSheetAt(0); // sheet
sheet.getSheetName();
// XSSFWorkbook xwb = new XSSFWorkbook(in);
// XSSFSheet sheet = xwb.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
Row title = rows.next(); //
Iterator<Cell> cellTitle = title.cellIterator(); //
Map<Integer, String> titlemap = new HashMap<Integer, String>();// map 。
//
for (int i = 0; cellTitle.hasNext(); i++) {
Cell cell = cellTitle.next();
String value = cell.getStringCellValue().trim();
titlemap.put(i, value);
}
//
while (rows.hasNext()) {
Row rown = rows.next();
T tObject = clazz.newInstance(); //
//
for (int j = 0; j < rown.getLastCellNum(); j++) {
Cell cell = rown.getCell(j, Row.RETURN_BLANK_AS_NULL);
String titleString = (String) titlemap.get(j);//
// Annotation ， set ，
if (fieldmap.containsKey(titleString)) {
Method setMethod = (Method) fieldmap.get(titleString)[0];
Type[] ts = setMethod.getGenericParameterTypes();// setter
String xclass = ts[0].toString(); //
//
if (xclass.equals("class java.lang.String")) {
setMethod.invoke(tObject, this.getCellValue(cell));
} else if (xclass.equals("class java.util.Date")) {
String pattern = (String) fieldmap.get(titleString)[1];
if (StringUtils.isBlank(pattern))
pattern = "yyyy-MM-dd HH:mm:ss";
setMethod.invoke(tObject, DateUtil.DateFormatString(this.getCellValue(cell), pattern));
} else if (xclass.equals("class java.lang.Boolean")) {
Boolean boolname = true;
if (this.getCellValue(cell).equals(" ")) {
boolname = false;
}
setMethod.invoke(tObject, boolname);
} else if (xclass.equals("class java.lang.Integer")) {
setMethod.invoke(tObject, Integer.parseInt(this.getCellValue(cell)));
} else if (xclass.equals("class java.lang.Long")) {
setMethod.invoke(tObject, new Long(this.getCellValue(cell)));
}
}
}
dist.add(tObject);
}
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage(), e);
return null;
}
return dist;
}
public String getCellValue(Cell cell) {
if(cell==null){
return null;
}
cell.setCellType(Cell.CELL_TYPE_STRING);
return cell.getStringCellValue().trim();
}
public static void main(String[] args) {
ExcekImportUtils<Testpojo> test = new ExcekImportUtils<Testpojo>();
File file = new File("c:\\ .xls");
Long befor = System.currentTimeMillis();
List<Testpojo> result = (ArrayList<Testpojo>) test.importExcel(file, Testpojo.class);
Long after = System.currentTimeMillis();
System.out.println(" ：" + (after - befor) + " ");
for (int i = 0; i < result.size(); i++) {
Testpojo testpojo = result.get(i);
System.out.println(testpojo.getUserName());
}
System.out.println(" List ：" + result.size());
}
}
4、Excelエクスポートツールクラス(ExcelExportUtils)
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.commons.lang3.time.DateFormatUtils;
public class ExcelExportUtils {
private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtils.class);
/**
* Excel
*/
public <T> HSSFWorkbook exportExcel(String title, List<T> dataset) {
HSSFWorkbook workbook = new HSSFWorkbook();
try {
//
if (dataset == null || title == null) {
throw new Exception(" ！");
}
Iterator<T> its = dataset.iterator();
if (!its.hasNext()) {
throw new Exception(" ！");
}
T ts = its.next();
HSSFSheet sheet = workbook.createSheet(title);
// sheet.setDefaultColumnWidth(5);// 15
HSSFCellStyle headStyle = workbook.createCellStyle();
headStyle = ExcelStyle.setHeadStyle(workbook, headStyle);
HSSFCellStyle bodyStyle = workbook.createCellStyle();
bodyStyle = ExcelStyle.setBodyStyle(workbook, bodyStyle);
Field filed[] = ts.getClass().getDeclaredFields();
List<Object[]> exportMetas = new ArrayList<>();
// filed
for (int i = 0; i < filed.length; i++) {
Field f = filed[i];
ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
// annottion
if (exa != null) {
String exprot = exa.exportName();
String pattern = exa.pattern();
Integer order = Integer.valueOf(exa.order());
//
exportMetas.add(new Object[] { f.getName(), exprot, pattern, order });
int columnWidth = exa.columnWidth();
if(columnWidth > 0)
sheet.setColumnWidth(order, columnWidth);
else {
sheet.setColumnWidth(order, 6000);
}
}
}
// exportMetas
Collections.sort(exportMetas, new Comparator<Object[]>() {
/** order */
public int compare(Object[] o1, Object[] o2) {
Integer order1 = (Integer) o1[3];
Integer order2 = (Integer) o2[3];
return order1.compareTo(order2);
}
});
//
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < exportMetas.size(); i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(headStyle);
HSSFRichTextString text = new HSSFRichTextString((String) exportMetas.get(i)[1]);
cell.setCellValue(text);
}
//
for (int i = 0; i < dataset.size(); i++) {
row = sheet.createRow(i + 1); // , 1 excel
T t = dataset.get(i);
for (int k = 0; k < exportMetas.size(); k++) {
HSSFCell cell = row.createCell(k);
String fieldname = (String) exportMetas.get(k)[0];
String getMethodName = "get" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
Class<?> tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
String textValue = getValue(value, exportMetas.get(k));
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
cell.setCellStyle(bodyStyle);
}
}
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage(), e);
}
return workbook;
}
/**
* Excel - ( cellColor )
*/
public <T> HSSFWorkbook exportExcelByColor(String title, List<T> dataset) {
HSSFWorkbook workbook = new HSSFWorkbook();
try {
//
if (dataset == null || title == null) {
throw new Exception(" ！");
}
Iterator<T> its = dataset.iterator();
if (!its.hasNext()) {
throw new Exception(" ！");
}
T ts = its.next();
HSSFSheet sheet = workbook.createSheet(title);
// sheet.setDefaultColumnWidth(5);// 15
HSSFCellStyle headStyle = workbook.createCellStyle();
headStyle = ExcelStyle.setHeadStyle(workbook, headStyle);
HSSFCellStyle bodyStyle = workbook.createCellStyle();
bodyStyle = ExcelStyle.setBodyStyle(workbook, bodyStyle);
Field filed[] = ts.getClass().getDeclaredFields();
List<Object[]> exportMetas = new ArrayList<>();
// filed
for (int i = 0; i < filed.length; i++) {
Field f = filed[i];
ExcelAnnotation exa = f.getAnnotation(ExcelAnnotation.class);
// annottion
if (exa != null) {
String exprot = exa.exportName();
String pattern = exa.pattern();
Integer order = Integer.valueOf(exa.order());
//
exportMetas.add(new Object[] { f.getName(), exprot, pattern, order });
int columnWidth = exa.columnWidth();
if(columnWidth > 0)
sheet.setColumnWidth(order, columnWidth);
else {
sheet.setColumnWidth(order, 6000);
}
}
}
// exportMetas
Collections.sort(exportMetas, new Comparator<Object[]>() {
/** order */
public int compare(Object[] o1, Object[] o2) {
Integer order1 = (Integer) o1[3];
Integer order2 = (Integer) o2[3];
return order1.compareTo(order2);
}
});
//
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < exportMetas.size(); i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(headStyle);
HSSFRichTextString text = new HSSFRichTextString((String) exportMetas.get(i)[1]);
cell.setCellValue(text);
}
//
for (int i = 0; i < dataset.size(); i++) {
row = sheet.createRow(i + 1); // , 1 excel
T t = dataset.get(i);
for (int k = 0; k < exportMetas.size(); k++) {
HSSFCell cell = row.createCell(k);
String fieldname = (String) exportMetas.get(k)[0];
String getMethodName = "get" + fieldname.substring(0, 1).toUpperCase() + fieldname.substring(1);
Class<?> tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
String textValue = getValue(value, exportMetas.get(k));
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
try{
//
Method getCellColor = tCls.getMethod("getCellColor", new Class[]{});
Object color = getCellColor.invoke(t, new Object[]{});
if(color==null){
continue;
}
HSSFCellStyle myStyle = workbook.createCellStyle();
myStyle = ExcelStyle.setMyStyle(workbook, myStyle , (short)color);
// myStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell.setCellStyle(myStyle);
}catch (Exception e){
logger.warn(" ， 。");
cell.setCellStyle(bodyStyle);
}
}
}
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage(), e);
}
return workbook;
}
public String getValue(Object value, Object[] meta) {
String textValue = "";
if (value == null)
return textValue;
if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
textValue = " ";
if (!bValue) {
textValue = " ";
}
} else if (value instanceof Date) {
String pattern = (String) meta[2];
if (StringUtils.isBlank(pattern))
pattern = "yyyy-MM-dd";
textValue = .format((Date) value, pattern);
} else {
textValue = DateFormatUtils.format((Date) value, pattern);
}
return textValue;
}
public static void main(String[] args) throws Exception {
List<Testpojo> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Testpojo testpojo = new Testpojo();
testpojo.setAge(new Integer(i));
testpojo.setCreatetime(new Date());
testpojo.setLoginname("chenld_" + i);
testpojo.setAge(Integer.valueOf(i));
testpojo.setUsername("chenliangdeng_" + i);
list.add(testpojo);
}
OutputStream out = new FileOutputStream("C:\\1585188680526.xls");
Long l = System.currentTimeMillis();
ExcelExportUtils ex = new ExcelExportUtils();
HSSFWorkbook workbook = ex.exportExcel(" ", list);
workbook.write(out);
out.close();
Long s = System.currentTimeMillis();
System.out.println(" ：" + (s - l));
}
}
添付:Excelインポート画像サポート
/**
*
*
* @param workbook
*
* @param sheetIndex
* sheet
* @return
*/
public static Map<Integer, List<HSSFPictureData>> getSheetPicList(HSSFWorkbook workbook, int sheetIndex) {
Map<Integer, List<HSSFPictureData>> map = new HashMap<Integer, List<HSSFPictureData>>();
HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
List<HSSFPictureData> picturelist = workbook.getAllPictures();
for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
//
int rowIndex = anchor.getRow1();
if (shape instanceof HSSFPicture) {
Integer rowmark = rowIndex;
HSSFPicture picture = (HSSFPicture) shape;
int pictureIndex = picture.getPictureIndex() - 1;
List<HSSFPictureData> piclist = map.get(rowmark);
if (piclist == null || piclist.size() > 0)
piclist = new ArrayList<HSSFPictureData>();
piclist.add(picturelist.get(pictureIndex));
map.put(rowmark, piclist);
}
}
return map;
}
/**
*
*
* @param workbook
*
* @param sheetIndex
* sheet
* @return
*/
public static Map<String, PictureData> getSheetPicList2007(XSSFWorkbook workbook, int sheetIndex) {
Map<String, PictureData> map = new HashMap<String, PictureData>();
XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
List<POIXMLDocumentPart> list = sheet.getRelations();
for (POIXMLDocumentPart part : list) {
if (part instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) part;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor anchor = picture.getPreferredSize();
CTMarker marker = anchor.getFrom();
String key = marker.getRow() + "-" + marker.getCol();
System.out.println(key);
map.put(key, picture.getPictureData());
}
}
}
return map;
}
//
public static void writeImg(Map<String, PictureData> sheetList) throws IOException {
//for (Map map : sheetList) {
Object key[] = sheetList.keySet().toArray();
for (int i = 0; i < sheetList.size(); i++) {
//
PictureData pic = sheetList.get(key[i]);
//
String picName = key[i].toString();
//
String ext = pic.suggestFileExtension();
byte[] data = pic.getData();
//
FileOutputStream out = new FileOutputStream("G:\\pic" + picName + "." + ext);
out.write(data);
out.close();
}
// }
}