POI文件excel文件导入导出

POI依赖的jar包

org.apache.poi
poi-ooxml
3.14

前台(使用easyUI框架)

//导出
    $("#button-export").click(function(){
        window.location.href="../../area_batchExport.action";                   
    });

//导入
$(“#button-import”).upload({ action: ‘../../area_batchImport.action’, onSelect:function(){ this.autoSubmit = false; var filename = this.filename(); // 只能导入指定格式的文件 var regex = /^.*.(xls|xlsx)$/ ;
if(regex.test(filename)){
this.submit();
}else{
$.messager.alert(“警告”,”选择的文件格式不正确”,”warnin”);
}
},
onComplete : function(response){
alert(“文件上传成功！”);
}
});

后台文件导出代码编写

package sy.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class ExcelUtil{
public static String NO_DEFINE = “no_define”;//未定义的字段
public static String DEFAULT_DATE_PATTERN=”yyyy年MM月dd日”;//默认日期格式
public static int DEFAULT_COLOUMN_WIDTH = 17;
/**
* 导出Excel 97(.xls)格式 ，少量数据
* @param title 标题行
* @param headMap 属性-列名
* @param jsonArray 数据集
* @param datePattern 日期格式，null则用默认日期格式
* @param colWidth 列宽 默认 至少17个字节
* @param out 输出流
*/
public static void exportExcel(String title,Map

后台文件导入代码编写

// 批量区域数据导入
//HSSFWorkbook是以xls结束的文件,即97-07版本文件,若是07以后的版本,将HSSFWorkbook,换为SXSSFWorkbook

@Action(value = "area_batchImport")
public String batchImport() throws IOException {
    List<Area> areas = new ArrayList<Area>();

    // 1、 加载Excel文件对象
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));
    // 2、 读取一个sheet
    HSSFSheet sheet = hssfWorkbook.getSheetAt(0);
    // 3、 读取sheet中每一行
    for (Row row : sheet) {
        if (row.getRowNum() == 0) {
            // 第一行 跳过
            continue;
        }
        // 跳过空行
        if (row.getCell(0) == null
                || StringUtils.isBlank(row.getCell(0).getStringCellValue())) {
            continue;
        }
        Area area = new Area();
        area.setId(row.getCell(0).getStringCellValue());
        area.setProvince(row.getCell(1).getStringCellValue());
        area.setCity(row.getCell(2).getStringCellValue());
        area.setDistrict(row.getCell(3).getStringCellValue());
        area.setPostcode(row.getCell(4).getStringCellValue());
        // 基于pinyin4j生成城市编码和简码
        String province = area.getProvince();
        String city = area.getCity();
        String district = area.getDistrict();
        province = province.substring(0, province.length() - 1);
        city = city.substring(0, city.length() - 1);
        district = district.substring(0, district.length() - 1);
        // 简码
        String[] headArray = PinYin4jUtils.getHeadByString(province + city
                + district);
        StringBuffer buffer = new StringBuffer();
        for (String headStr : headArray) {
            buffer.append(headStr);
        }
        String shortcode = buffer.toString();
        area.setShortcode(shortcode);
        // 城市编码
        String citycode = PinYin4jUtils.hanziToPinyin(city, "");
        area.setCitycode(citycode);

        areas.add(area);
    }
    //调用业务层保存数据
    areaService.saveBatch(areas);

    return NONE;
}