一、包
org.apache.poi poi3.15-beta2 org.apache.poi poi-ooxml3.15-beta2
二、导出
public static void exportExcel(HttpServletResponse response, String title, String[] headers, List list, List containBean) throws Exception {
HSSFWorkbook workbook = null;
try {
workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(title);
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
Iterator it = list.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
Field[] fields = t.getClass().getDeclaredFields();
if (CollectionUtils.isNotEmpty(containBean)) {
for (int j = 0; j < containBean.size(); j++) {
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
if (!field.getName().equals(containBean.get(j))) {
continue;
}
setCellValue(t, field, row, j);
}
}
} else {
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
setCellValue(t, field, row, i);
}
}
}
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + new String((title).getBytes(), "ISO8859-1") + ".xls");
workbook.write(response.getOutputStream());
} finally {
if (workbook != null) {
workbook.close();
}
}
}
private static void setCellValue(T t, Field field, HSSFRow row, int index) {
HSSFCell cell = row.createCell(index);
Object value = invoke(t, field);
String textValue = null;
if (value != null) {
if (value instanceof Date) {
Date date = (Date) value;
textValue = DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
} else {
textValue = value.toString();
}
}
if (textValue != null) {
cell.setCellValue(textValue);
}
}
private static Object invoke(T t, Field field) {
try {
String fieldName = field.getName();
PropertyDescriptor pd = new PropertyDescriptor(fieldName, t.getClass());
Method method = pd.getReadMethod();
return method.invoke(t);
} catch (Exception e) {
return null;
}
}
参数分别是：response、title（文件名称）、headers（表格头中文）、list（数据集合）、containBean（表格头英文，与实体类中对应）
测试：
String title = "测试导出用户数据";
String[] headers = {"用户名","密码","账号","真实名称","权限","状态","权限id","创建时间"};
List userList = transactionService.queryUser();
List listColumn = Arrays.asList("username","password","id","realName","roleId","status","orgId","createTime");
try {
PortExcelUtil.exportExcel(response,title,headers,userList,listColumn);
} catch (Exception e) {
e.printStackTrace();
}
三、导入
public static JSonArray readExcel(String fileName, FileInputStream fins, importParams params, String[] columName, String[] headerName) throws IOException {
String extension = fileName.lastIndexOf('.') == -1 ? "" : fileName.substring(fileName.lastIndexOf('.') + 1);
if ("xls".equals(extension)) {
return readExcel2003(fins, params, columName, headerName);
} else if ("xlsx".equals(extension)) {
return readExcel2007(fins, params, columName, headerName);
} else {
throw new IOException("不支持的文件类型");
}
}
private static int readExcel2003TotalRow(FileInputStream fins, importParams params) throws IOException {
HSSFWorkbook hwb = new HSSFWorkbook(fins);
HSSFSheet sheet = hwb.getSheetAt(0);
return sheet.getPhysicalNumberOfRows();
}
private static int readExcel2007TotalRow(FileInputStream fins, importParams params) throws IOException {
XSSFWorkbook xwb = new XSSFWorkbook(fins);
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(params.getSheetNum());
return sheet.getPhysicalNumberOfRows();
}
private static JSonArray readExcel2003(FileInputStream fins, importParams params, String[] colunName, String[] headerName) throws IOException {
HSSFWorkbook hwb = new HSSFWorkbook(fins);
HSSFSheet sheet = hwb.getSheetAt(0);
HSSFRow row = sheet.getRow(params.getStartRows());
HSSFCell cell = row.getCell(params.getCellNum());
JSonArray jsonArray = new JSonArray();
boolean flag = true;
int totalNum = 0;
int rowNum = 0;
if (cell == null) {
return jsonArray;
}
for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
Sheet sheetData = hwb.getSheetAt(i);
if (sheetData == null) {
continue;
}
if (sheetData.getLastRowNum() > 0) {
totalNum += sheetData.getLastRowNum();
}
rowNum = row.getPhysicalNumberOfCells();
Row rowsHeader = sheetData.getRow(0);
for (int k = 0; k < rowNum; k++) {
Cell cellData = rowsHeader.getCell(k);
if (!cellData.toString().equals(headerName[k])) {
flag = false;
}
}
if (flag) {
for (int j = 1; j < sheetData.getPhysicalNumberOfRows(); j++) {
Cell cellData = null;
JSonObject jsonObject = new JSonObject();
for (int k = 0; k < rowNum; k++) {
Row rows = sheetData.getRow(j);
cellData = rows.getCell(k);
jsonObject.put(colunName[k], cellData.toString());
if (cellData == null) {
continue;
}
}
jsonArray.add(jsonObject);
}
}
}
return jsonArray;
}
@JSonField(serialize = false)
private static JSonArray readExcel2007(FileInputStream fins, importParams params, String[] colunName, String[] headerName) throws IOException {
XSSFWorkbook xwb = new XSSFWorkbook(fins);
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(params.getSheetNum());
Object value = null;
XSSFRow row = sheet.getRow(params.getStartRows());
XSSFCell cell = row.getCell(params.getCellNum());
JSonArray jsonArray = new JSonArray();
boolean flag = true;
int totalNum = 0;
int rowNum = 0;
for (int i = 0; i < xwb.getNumberOfSheets(); i++) {
Sheet sheetData = xwb.getSheetAt(i);
if (sheetData == null) {
continue;
}
if (sheetData.getLastRowNum() > 0) {
totalNum += sheetData.getLastRowNum();
}
rowNum = row.getPhysicalNumberOfCells();
Row rowsHeader = sheetData.getRow(0);
for (int k = 0; k < rowNum; k++) {
Cell cellData = rowsHeader.getCell(k);
if (!cellData.toString().equals(headerName[k])) {
flag = false;
}
}
if (flag) {
for (int j = 1; j < sheetData.getPhysicalNumberOfRows(); j++) {
Cell cellData = null;
JSonObject jsonObject = new JSonObject();
Row rows = sheetData.getRow(j);
for (int k = 0; k < rowNum; k++) {
cellData = rows.getCell(k);
jsonObject.put(colunName[k], cellData.toString());
if (cellData == null) {
continue;
}
}
jsonArray.add(jsonObject);
}
}
}
return jsonArray;
}
返回的是JSONArray。
测试：
public static void main(String[] args) throws IOException {
File file = new File("D:\test\tses1.xlsx");
FileInputStream fileInputStream = new FileInputStream(file);
String filename = file.getName();
importParams importParams = new importParams();
String[] columName = {"username","password","id","realName","roleId","status","orgId","createTime"};
String[] headerName = {"用户名","密码","账号","真实名称","权限","状态","权限id","创建时间"};
JSonArray jsonArray = ReadExcel.readExcel(filename, fileInputStream, importParams,columName,headerName);
for(int i=0; i
注意：1、如果表格中的字段与headerName不一致，jsonObject 会为null；
2、headerName要与columName 和excel的表头相同；
3、JSonObject jsonObject = (JSONObject) jsonArray.get(i); UserEnty userEnty = jsonObject.toJavaObject(UserEnty.class);
这个是将jsonObject 变成一个实体类，便于插入。
