Java POIツールクラス-POJOのクラスフィールドに基づいて、Excelインポートエクスポートをカスタマイズします.

1、EXcelを定義してエクスポート注記類(ExcelAnnotation)を導入し、POJO類とのやり取りを便利にする

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();
		}
		// }

	}