简介
Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。
步骤
1、 导入 jquery.ocupload-1.1.2.js 到项目中
页面上设置可选择文件上传类型为EXCEL
$("input[name='file']").attr("accept","application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
进行解析
生成城市简码和城市编码
简码 :PinYin4jUtils.getHeadByString(province+city+district);
城时简码: PinYin4jUtils.hanziToPinyin(city);
// 批量区域数据导入
@Action(value = "area_batchImport")
public String batchImport() throws IOException {
List<Area> areas = new ArrayList<Area>();
// 编写解析代码逻辑
// 基于.xls 格式解析 HSSF
// 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);
解析数据库数据,放入excel表格下载
@Action(value="area_batchExport")
public String export() {
//查询所有
List<Area> areas=areaService.findAll();
HSSFWorkbook workbook=new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
int totalNum=(int) Math.ceil(areas.size()*1.0/5000);
for(int j=0;j<totalNum;j++) {
HSSFRow firstRow = sheet.createRow(0);
firstRow.createCell(0).setCellValue("区域编码");
firstRow.createCell(1).setCellValue("省份");
firstRow.createCell(2).setCellValue("城市");
firstRow.createCell(3).setCellValue("区域");
firstRow.createCell(4).setCellValue("邮编");
firstRow.createCell(5).setCellValue("简码");
firstRow.createCell(6).setCellValue("城市编码");
//int totalNum=Math.ceil(areas.size()*1.0/5000);
for (int i = 0; i < areas.size(); i++) {
HSSFRow row=sheet.createRow(sheet.getLastRowNum()+1);
Area area=areas.get(i);
row.createCell(0).setCellValue(area.getId());
row.createCell(1).setCellValue(area.getProvince());
row.createCell(2).setCellValue(area.getCity());
row.createCell(3).setCellValue(area.getDistrict());
row.createCell(4).setCellValue(area.getPostcode());
row.createCell(5).setCellValue(area.getShortcode());
row.createCell(6).setCellValue(area.getCitycode());
}
}
//文件下载
/*
* 两个头:
* content-Type:
* content-disposition:指定客户端打开文件的方式
* * 默认:inline,在浏览器中打开
* * 下载: attachment;filename = xxx
* 一个流:
* response输出流:response.getOutputStream()
*/
try {
HttpServletResponse response=ServletActionContext.getResponse();
String filename="area.xls";
String mimeType = ServletActionContext.getServletContext().getMimeType(filename);
//设置文件类型
response.setContentType(mimeType);
response.setHeader("content-disposition", "attachment;filename="+filename);
workbook.write(response.getOutputStream());
}catch(IOException e) {
e.printStackTrace();
}
return NONE;
}