注意：编码前先导入poi相关jar包
1 /**
* 读excel 到list
*
* @param file excel file
* @param fields 字段数组
* @return
* example OfficeHandle.readExcel("d:/test/test.xls",
* new String[]{"id","num","name"})
*/
public static JSONArray readExcel(String file,String[] fields){
if(null == file || null == fields)
return null; JSONArray jarr = new JSONArray();
FileInputStream fis = null;
int cols = 0;
try {
/************************读取本地文件（如d:/test/test.xls）********************************************/
fis = new FileInputStream(new File(file));//读取本地文件（如d:/test/test.xls）
HSSFWorkbook workbook = new HSSFWorkbook(fis);
/**********************读取服务器文件(file="http://你的地址")******************************************/
URL url = new URL(file); //file="http://你的地址"
URLConnection connection = url.openConnection();
InputStream is = connection.getInputStream();
HSSFWorkbook workbook = new HSSFWorkbook(is);
/**************************************************************************************************/
HSSFSheet sheet = workbook.getSheetAt(0);
if(sheet != null){
HSSFRow row = sheet.getRow(0);
if(row != null)
cols = row.getLastCellNum(); for(int i=1,len=sheet.getLastRowNum();i<=len;i++){
row = sheet.getRow(i);
if(row != null){
JSONObject jo = new JSONObject();
for(int j=0;j<cols;j++){
HSSFCell cell = row.getCell(j);
if(cell != null){
Object v=null;
HSSFCellStyle type = cell.getCellStyle();
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
v = cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING:
v = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
v = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_FORMULA:
v = cell.getCellFormula();
break;
default:
System.out.println("unsuported sell type");
break;
}
jo.put(fields[j], v); }
}
jarr.add(jo);
}
}
}
} catch (FileNotFoundException e ) { }catch(IOException e){ }finally{
try {
fis.close();
} catch (IOException e) { }
}
return jarr;
} /**
* 从list生成excel
*
* @param lstData json array data
* @param fieldEn 字段英文名
* @param fieldZh 生成字段名
* @return
* example OfficeHandle.exportExcel(lstdata,
* new String[]{"schoolId","schoolno","schoolName","address","remarks","linkMobile","linkMan"},
* new String[]{"学校编号","","","","","",""},
* "d:/test/exel1.xls");
*/
public static String exportExcel(JSONArray lstData,String[] fieldEn,String[] fieldZh,String fname){
if(null == lstData || null == fieldEn)
return null; int fieldLen = fieldEn.length;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(0);
for(int i=0;i<fieldLen;i++){
String fn = fieldEn[i];
if(null != fieldZh && !StringUtils.isEmpty(fieldZh[i])){
fn = fieldZh[i];
}
HSSFCell cell = row.createCell(i);
cell.setCellValue(fn);
}
for(int i=0,len=lstData.size();i<len;i++){
row = sheet.createRow(i+1);
for(int j=0;j<fieldLen;j++){
JSONObject jo = lstData.getJSONObject(i);
if(jo != null){
HSSFCell cell = row.createCell(j);
if(jo.containsKey(fieldEn[j])){
cell.setCellValue(jo.getString(fieldEn[j]));
}
}
}
}
FileOutputStream fos = null;
try {
fos = new FileOutputStream(fname);
workbook.write(fos); } catch (FileNotFoundException e) { e.printStackTrace();
}catch (IOException e) { }finally{
try {
fos.close();
} catch (IOException e) { }
}
return fname;
}
案例展示
/**
* 导入excel数据
*/
public void importExcel(){
String x = null;
JSONArray jar = new JSONArray();
School sc = new School();
String[] fields = new String[]{"schoolId","schoolno","schoolName","address","remarks","linkMobile","linkMan"}; try{ jar = officHandle.readExcel(filePath, fields,true);
for(int i=0,len=jar.size();i<len;i++){
JSONObject ob = JSONObject.fromObject(jar.get(i));
String schoolno = ob.getString("schoolno");
if(!school.isExist(schoolno)){//根据学校编号判断，若不存在就添加否则更新
sc = (School)school.addRecord((School)JSONObject.toBean(ob,School.class));
}else{
School sch = (School)school.findByProperty("School", new String[]{"schoolno"}, new Object[]{schoolno}).get(0);//获取存在的记录id
ob.put("schoolId", sch.getSchoolId());
sc = (School)school.editRecord((School)JSONObject.toBean(ob,School.class));
}
}
x = sc.getSchoolId().toString();
}catch(Exception e){
x = errorHandle.handleErr(e);
}
servletHandle.writeToClient1(ServletActionContext.getResponse(), x);
}
/**
* 导出数据到Excel
*/
public void exportData(){
String x = null;
String[] idArr = model.getIds().split(",");
List<School> schoolList = new ArrayList<School>();
JSONArray jar = new JSONArray();//数据list
String[] fieldEn = new String[]{"schoolno","schoolName","address","linkMobile","linkMan","remarks"};
String[] fieldCn = new String[]{"学校编号","学校名称","学校地址","联系电话","联系人","备注"};
try{
if(StringUtils.isEmpty(model.getIds())){//全部导出
x = school.findByProperty("School", "*",
"json", true,null, null, null, null, 0, 0);
schoolList = (List<School>) JSONObject.fromObject(x).get("rows");
if(schoolList.size() > 0){
for(int i=0,len=schoolList.size();i<len;i++){
jar.add(schoolList.get(i));
}
} }else{
for(int i=0,len=idArr.length;i<len;i++){//导出选择记录
schoolList = school.findByProperty("School", new String[]{"schoolId"}, new Object[]{Long.parseLong(idArr[i])});
if(schoolList.size() > 0)
jar.add(schoolList.get(0));
}
}
String basePath = ServletActionContext.getServletContext().getRealPath("/");//获取服务器文件存放地址
String path = "/assets/export/" + UUID.randomUUID().toString().replaceAll("-", "") + ".xls";//拼接随机生成文件名，用于写入excel数据流
String fn = basePath + path;
officHandle.exportExcel(jar, fieldEn, fieldCn, fn);//传入数据list，字段名及保存文件名
x = CommonConfig.domainName + CommonConfig.contextPath + path;//获取文件路径返回，location.href = x（浏览器自动下载文件）
}catch(Exception e){
x = errorHandle.handleErr(e);
}
servletHandle.writeToClient1(ServletActionContext.getResponse(), x);
}