免费源码下载（提取码：qdhy）
工程结构目录
所需JAR包
<dependencies><!-- JUNIT 测试 --><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>3.8.1</version><scope>test</scope></dependency><!-- POI 表格文件处理--><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version></dependency><!-- JSON 解析--><dependency><groupId>net.sf.json-lib</groupId><artifactId>json-lib</artifactId><version>2.4</version><classifier>jdk15</classifier></dependency></dependencies>
User.java（导入对象类）
package com.zyq.excel;public class User {@ExcelDesc("姓名")private String name;@ExcelDesc("年龄")private int age;@ExcelDesc("备注")private String remark;public String getName() {return name;}public int getAge() {return age;}public String getRemark() {return remark;}public void setName(String name) {this.name = name;}public void setAge(int age) {this.age = age;}public void setRemark(String remark) {this.remark = remark;}@Overridepublic String toString() {return "User [name=" + name + ", age=" + age + ", remark=" + remark + "]";}}
ExcelDesc.java（属性注解，对应导入表格的表头）
package com.zyq.excel;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface ExcelDesc{String value();}
ExcelTool.java（Excel导入导出工具类）
package com.excel.test;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.math.BigDecimal;import java.math.RoundingMode;import .URL;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Collections;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import net.sf.json.JSONArray;import net.sf.json.JSONObject;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Drawing;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFClientAnchor;import org.apache.poi.xssf.usermodel.XSSFWorkbook;/*** Excel 导入导出工具类* * @author Yuanqiang.Zhang* @since 5月18日*/public class ExcelUtils {private static final String XLSX = ".xlsx";private static final String XLS = ".xls";// 导出图片高度public static final short IMG_HEIGTH = 30;// 导出图片宽度public static final short IMG_WIDTH = 30;// 导出指定位置（如：F:\\data\\excel\\，不写即本工程目录下）public static final String PATH = "";/*** 读取文件数据* * @param file_Excel文件（.xls 或 .xlsx）* @return 文件数据*/public static JSONArray readExcel(File file) {JSONArray array = null;try {String fileName = file.getName().toLowerCase();Workbook book = null;if (fileName.endsWith(XLSX)) {book = new XSSFWorkbook(new FileInputStream(file));} else if (fileName.endsWith(XLS)) {POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));book = new HSSFWorkbook(poifsFileSystem);} else {return array;}array = read(book);} catch (Exception e) {e.printStackTrace();}// POI 3.9乃自动关闭，故而无book.close()方法return array;}/*** 将文件的数据解析为JSON* * @param book_Workbook对象* @return 文件内容（以JSONArray返回）* @throws IOException 文件解析异常*/private static JSONArray read(Workbook book) throws IOException {// 获取 Excel 文件第一个 Sheet 页面Sheet sheet = book.getSheetAt(0);// 首行下标int rowStart = sheet.getFirstRowNum();// 尾行下标int rowEnd = sheet.getLastRowNum();// 获取表头行Row headRow = sheet.getRow(rowStart);int cellStart = headRow.getFirstCellNum();int cellEnd = headRow.getLastCellNum();Map<Integer, String> keyMap = new HashMap<Integer, String>();for (int j = cellStart; j < cellEnd; j++) {// 表头遇到空格停止解析String val = getValue(headRow.getCell(j));if (val == null || val.trim().length() == 0) {cellEnd = j;break;}keyMap.put(j, val);}// 如果表头没有数据则不进行解析if (keyMap.isEmpty()) {return (JSONArray) Collections.emptyList();}// 获取每行JSON对象的值JSONArray array = new JSONArray();// 如果首行与尾行相同，表明只有一行，返回表头数据if (rowStart == rowEnd) {JSONObject object = new JSONObject();for (int i : keyMap.keySet()) {object.put(keyMap.get(i), "");}array.add(object);return array;}for (int i = rowStart + 1; i <= rowEnd; i++) {Row eachRow = sheet.getRow(i);JSONObject obj = new JSONObject();StringBuffer sb = new StringBuffer();for (int k = cellStart; k < cellEnd; k++) {if (eachRow != null) {String val = getValue(eachRow.getCell(k));// 所有数据添加到里面，用于判断该行是否为空sb.append(val);obj.put(keyMap.get(k), val);}}if (sb.length() > 0) {array.add(obj);}}return array;}/*** 获取单元格数据* * @param cell 每个单元格数据* @return 单元格具体内容* @throws IOException*/private static String getValue(Cell cell) throws IOException {// 空白或空if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {return "";}// String类型if (cell.getCellType() == Cell.CELL_TYPE_STRING) {String val = cell.getStringCellValue();if (val == null || val.trim().length() == 0) {return "";}return val.trim();}// 数字类型if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {// 时间戳类型if (HSSFDateUtil.isCellDateFormatted(cell)) {Date date = cell.getDateCellValue();DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");return df.format(date);}// 科学计数法类型cell.setCellType(Cell.CELL_TYPE_STRING);String val = cell.getStringCellValue() + "";val = val.toUpperCase();if (val.contains("E")) {val = val.split("E")[0].replace(".", "");}return val;}// 布尔值类型if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {return cell.getBooleanCellValue() + "";}// 公式类型if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {return cell.getCellFormula();}// 错误类型return "";}/*** 获取每个对象的数据*/private static <T> T getBean(Class<T> c, JSONObject obj) throws Exception {T t = c.newInstance();Field[] fields = c.getDeclaredFields();for (int i = 0; i < fields.length; i++) {Field field = fields[i];// 获取ExcleDesc注解属性ExcelDesc excelDesc = field.getAnnotation(ExcelDesc.class);if (excelDesc != null) {String cname = excelDesc.value();if (cname == null || cname.trim().length() == 0) {continue;}String val = null;if (obj.has(cname)) {val = obj.getString(cname);}// 获取具体值field.setAccessible(true);// 其余情况根据类型赋值String fieldClassName = field.getType().getSimpleName();try {if ("String".equalsIgnoreCase(fieldClassName)) {field.set(t, val);} else if ("boolean".equalsIgnoreCase(fieldClassName)) {field.set(t, obj.getBoolean(cname));} else if ("int".equalsIgnoreCase(fieldClassName) || "Integer".equals(fieldClassName)) {field.set(t, obj.getInt(cname));} else if ("double".equalsIgnoreCase(fieldClassName)) {field.set(t, obj.getDouble(cname));} else if ("long".equalsIgnoreCase(fieldClassName)) {field.set(t, obj.getLong(cname));} else if ("BigDecimal".equalsIgnoreCase(fieldClassName)) {field.set(t, new BigDecimal(val));}} catch (Exception e) {e.printStackTrace();}}}return t;}/*** 将excel文件解析为指定对象集合* * @param <T> 需要解析的Java对象（对象属性需要打@ExcleDesc注解，注解属性value与表头相对应）* @param c 泛型对象的实例* @param file Excel文件* @return List<T> Java对象集合*/public static <T> List<T> getBeanList(Class<T> c, File file) {// 解析上传文件为JsonArrayJSONArray arr = readExcel(file);if (arr == null) {return Collections.emptyList();}// 解析List<Bean>List<T> list = new ArrayList<T>();for (int i = 0; i < arr.size(); i++) {try {list.add(getBean(c, (JSONObject) arr.get(i)));} catch (Exception e) {}}return list;}/*** Excel 表格导出* * @param title 表名称* @param rowList 导出每行数据*/public static void export(String title, List<List<Object>> rowList) {if (rowList == null) {rowList = Collections.emptyList();}SXSSFWorkbook book = new SXSSFWorkbook();Sheet sheet = book.createSheet(title);Drawing patriarch = sheet.createDrawingPatriarch();CellStyle style = book.createCellStyle();// 数据居左style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 写数据for (int i = 0; i < rowList.size(); i++) {List<Object> row = rowList.get(i);Row sr = sheet.createRow(i);for (int j = 0; j < row.size(); j++) {// 如果要导出图片的话, 链接需要传递 URL 对象if (row.get(j) != null && row.get(j) instanceof URL) {URL url = (URL) row.get(j);sr.setHeight((short) (IMG_WIDTH * IMG_HEIGTH));setExcelImg(book, patriarch, i, j, url);} else {setExcelValue(sr.createCell(j), row.get(j), style);}}}try {if (PATH.length() > 0) {File dir = new File(PATH);if (!dir.exists()) {dir.mkdirs();}}File file = new File(PATH + title + XLSX);if (!file.exists()) {file.createNewFile();}FileOutputStream fos = new FileOutputStream(file);ByteArrayOutputStream ops = new ByteArrayOutputStream();book.write(ops);fos.write(ops.toByteArray());fos.close();} catch (Exception e) {e.printStackTrace();}}/*** 导出写图片* * @param wb SXSSFWorkbook对象* @param patriarch Drawing对象* @param rowIndex 行数* @param cloumIndex 列数* @param url 图片链接*/private static void setExcelImg(SXSSFWorkbook wb, Drawing patriarch, int rowIndex, int cloumIndex, URL url) {// （jdk1.7版本try中定义流可自动关闭）try (InputStream is = url.openStream(); ByteArrayOutputStream outputStream = new ByteArrayOutputStream();) {byte[] buff = new byte[1024];int rc = 0;while ((rc = is.read(buff, 0, 1024)) > 0) {outputStream.write(buff, 0, rc);}// 设置图片位置XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, cloumIndex, rowIndex, cloumIndex + 1,rowIndex + 1);anchor.setAnchorType(0);patriarch.createPicture(anchor, wb.addPicture(outputStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));outputStream.close();} catch (Exception e) {e.printStackTrace();}}/*** 导出写数据* * @param cell 单元格* @param value 数据* @param style 单元格类型*/public static void setExcelValue(Cell cell, Object value, CellStyle style) {// 写数据if (value == null) {cell.setCellValue("");} else {if (value instanceof Integer || value instanceof Long) {cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(Long.valueOf(value.toString()));} else if (value instanceof BigDecimal) {cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(((BigDecimal) value).setScale(3, RoundingMode.HALF_UP).doubleValue());} else {cell.setCellValue(value.toString());}cell.setCellStyle(style);}}}
AppTest.java（测试类，测试代码及其效果如下）
测试结果：
测试xls格式导入
测试xlsx格式导入
测试导出
/*** Excel 导出测试*/public static void exportTest(){String title = "abc";List<List<Object>> rowList = new ArrayList<List<Object>>();// 表头List<Object> head = new ArrayList<Object>();head.add("头像");head.add("姓名");head.add("年龄");head.add("备注");head.add("空间");rowList.add(head);// 表数据List<Object> row = new ArrayList<Object>();try {row.add(new URL("https://cdn-/png/11002/1100254.gif"));} catch (Exception e) {}row.add("孙悟空");row.add("18");row.add("唐僧大徒弟");row.add("");rowList.add(row);List<Object> row2 = new ArrayList<Object>();try {row2.add(new URL("https://cdn-/png/5467/546720.gif"));} catch (Exception e) {}row2.add("猪八戒");row2.add("18");row2.add("唐僧儿徒弟");row2.add("");rowList.add(row2);ExcelTool.export(title, rowList);System.out.println("导出完成，请刷新工程，查看文件....");}
