POI Excel导入导出 - milan's

var exportCondition={};//导出条件
//导出功能
function outputData(){
$.ajax({
type: "POST",
url: path+"/main/inputAndOutput/output",
data: exportCondition,
success: function (fileName) {
var downUrl = path+"/main/inputAndOutput/download?fileName=" + fileName;
window.location = downUrl;
}
});
}

   //查询功能
   function search(){
     //按条件进行查询数据，首先我们得到数据的值
     //得到用户输入的参数，取值有几种方式：$("#id").combobox('getValue'), $("#id").datebox('getValue'), $("#id").val()
       //字段增加search_前缀字符，避免传递如URL这样的Request关键字冲突
       var queryData = {
           search_type: $("#search_type").combobox('getValue'),
           search_address: $("#search_address").combotree("tree").tree("getSelected")!=null?$("#search_address").combotree("tree").tree("getSelected").id:"",
           search_name: $("#search_name").textbox('getValue'),
           search_year: $("#search_year").textbox('getValue'),
           search_publicType: $("#search_publicType").textbox('getValue'),
           search_publicName: $("#search_publicName").textbox('getValue'),
           search_layout: $("#search_layout").combobox('getValue'),
           search_status: $("#search_status").combobox('getValue')
       }
       //将值传递给
       initGrid(queryData);
       
       //将查询条件传递给导出
       exportCondition = queryData;
   }

@RequestMapping(value="output",method=RequestMethod.POST)
@ResponseBody
public String output(HttpServletRequest request,HttpServletResponse response){
Map<String, Object> param = new HashMap<String, Object>();
List<YellowPagesResourceModel> list = new ArrayList<YellowPagesResourceModel>();
String fileName="";
try {

//获取查询条件
param = getQueryParam(request);

list = this.yellowpageResService.QueryAllForGridData(param);
if(list.size()>0){
//创建webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();

//设置表头及样式
HSSFSheet sheet = defineHeader(wb);

//填充数据
writeData(list, sheet);

//数据写入文件
fileName=writeToFile(wb);
}
} catch (Exception e) {
e.printStackTrace();
}
return fileName;
}

/**
* 设置表头和样式
* @param wb
* @return
*/
private HSSFSheet defineHeader(HSSFWorkbook wb) {
//添加sheet,对应Excel文件中sheet
HSSFSheet sheet = wb.createSheet("黄页资源(一)");

//创建表头
HSSFRow row = sheet.createRow(0);

//创建单元格，设置表头值
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中格式

HSSFCell cell = row.createCell(0);
cell.setCellValue("序号");
cell.setCellStyle(style);

cell = row.createCell(1);
cell.setCellValue("所属地市");
cell.setCellStyle(style);

cell = row.createCell(2);
cell.setCellValue("地区");
cell.setCellStyle(style);

cell = row.createCell(3);
cell.setCellValue("年份");
cell.setCellStyle(style);

cell = row.createCell(4);
cell.setCellValue("书名");
cell.setCellStyle(style);

cell = row.createCell(5);
cell.setCellValue("刊式代码");
cell.setCellStyle(style);

cell = row.createCell(6);
cell.setCellValue("刊式名称");
cell.setCellStyle(style);

cell = row.createCell(7);
cell.setCellValue("刊式尺寸");
cell.setCellStyle(style);

cell = row.createCell(8);
cell.setCellValue("价格");
cell.setCellStyle(style);

cell = row.createCell(9);
cell.setCellValue("版面");
cell.setCellStyle(style);
return sheet;
}

/**
* 写入数据到excel
* @param list
* @param sheet
*/
private void writeData(List<YellowPagesResourceModel> list, HSSFSheet sheet) {
HSSFRow row = null;
//写入数据
YellowPagesResourceModel model = null;
for(int i=0;i<list.size();i++){
row = sheet.createRow(i+1);
model = list.get(i);
row.createCell(0).setCellValue(i+1);
row.createCell(1).setCellValue(model.getAddress());
row.createCell(2).setCellValue(model.getAddressStr());
row.createCell(3).setCellValue(model.getPagesYear());
row.createCell(4).setCellValue(model.getPagesName());
row.createCell(5).setCellValue(model.getPublicCode());
row.createCell(6).setCellValue(model.getPublicName());
row.createCell(7).setCellValue(model.getPublicType());
row.createCell(8).setCellValue(model.getPrice().toString());
row.createCell(9).setCellValue(model.getLayout()==1?"普通版面":"特殊版面");
}
}

/**
* 数据写入磁盘文件
* @param wb
* @throws IOException
* @throws FileNotFoundException
*/
private String writeToFile(HSSFWorkbook wb) throws IOException,
FileNotFoundException {
// 读取配置文件获取实际保存路径
Properties props = PropertiesLoaderUtils.loadAllProperties("otherCfg.properties");
//实际保存路径
String saveDir = props.getProperty("PathToYellowPagesResFile_DEV");
File fileDir = new File(saveDir.toString());
if(!fileDir.exists()){
fileDir.mkdirs();
}
String fileName = new java.text.SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + "-" +RandomUtils.nextInt();
String filePath = saveDir+File.separator+fileName+".xls";
FileOutputStream fout = new FileOutputStream(filePath);
ByteArrayOutputStream ostream = new ByteArrayOutputStream();
wb.write(ostream);
fout.write(ostream.toByteArray());
fout.flush();
ostream.close();
fout.close();
return fileName+".xls";
}

/**
* 解决附件下载名称乱码
* @param request
* @param response
* @param params
* @throws UnsupportedEncodingException
*/
private void solveGarbled(HttpServletRequest request,
HttpServletResponse response,
Map<String, String> params
) throws UnsupportedEncodingException{
if (request.getHeader("User-Agent").toLowerCase().indexOf("msie") > -1){//IE浏览器
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(params.get("realFileName"), "iso-8859-1"));
}else if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > -1){//firefox浏览器
response.setContentType("application/x-xls");
response.addHeader("content-disposition", "attachment;filename=\"" + params.get("realFileName") + "\"");
}else{//其他浏览器
response.setContentType("application/x-xls");
response.addHeader("content-disposition", "attachment;filename=" + params.get("realFileName"));
}
}

<div id="inputDlg" class="easyui-dialog" style="width:380px;height:220px;"
data-options="buttons: '#inputDlg-buttons',closed:true,modal: true">
<form id="uploadForm" method="post" enctype="multipart/form-data">
<table cellpadding="8">
<tr class="fitem">
<td>
<input id="uploadExcel" name="uploadExcel" class="easyui-filebox" style="width:250px;"
data-options="prompt:'请选择.xls文件...'">
</td>
</tr>
<tr class="fitem">
<td>
<label>黄页类型:</label><input id="uploadType" name="uploadType" class="easyui-combobox"/>
</td>
</tr>
</table>
</form>
<p style="color:red;font-size:12px;text-align:center;">请注意导入的Excel数据字段和
<a href="${pageContext.request.contextPath}/main/inputAndOutput/downloadTemplate">Excel模板</a>一致
</p>
</div>
<div id="inputDlg-buttons">
<a href="javascript:void(0)" class="easyui-linkbutton"
data-options="iconCls:'icon-save'" onclick="uploadFile()">上传</a>
<a id="uploadBtn" href="javascript:void(0)" class="easyui-linkbutton"
data-options="iconCls:'icon-cancel'"
onclick="javascript:$('#inputDlg').dialog('close')">取消</a>
</div>

//文件上传
function uploadFile(){
//得到文件路径
var filePath = $('#uploadExcel').filebox('getValue');
if(filePath!=""){
//对文件格式进行验证(简单验证)
var d1=/\.[^\.]+$/.exec(filePath);
if(d1==".xls"){
$('#uploadForm').form('submit',{
url: path+'/main/inputAndOutput/upload',
success: function(data){
if (data){
$('#inputDlg').dialog('close');
$("#grid").datagrid('reload');
} else {
$.messager.alert('操作提示',"导入失败，请检查数据是否正确!",'error');
}
}
});
}else{
$.messager.alert('温馨提示','请选择.xls文件!','warning');
}
}else{
$.messager.alert('温馨提示','请选择.xls文件!','warning');
}
}

@RequestMapping(value="upload")
@ResponseBody
public String upload(HttpServletRequest request,HttpServletResponse response) throws IOException{
String result=null;
//文件上传到磁盘
Map<String,String> map = uploadExcel(request);

String fileName = map.get("fileName");
Integer type =Integer.parseInt(map.get("type"));
List<String> dataList = new ArrayList<String>();
Properties props = PropertiesLoaderUtils.loadAllProperties("otherCfg.properties");
String saveDir = props.getProperty("PathToYellowPagesResFile_DEV");
String filePath = saveDir+File.separator+fileName;
try {

//.xls转换为.csv
XLS2CSVmra xls2csv = new XLS2CSVmra(filePath+".xls", filePath+".csv");
xls2csv.process();
//删除.xls
File file = new File(filePath+".xls");
if(file.exists())file.delete();

//获取.csv中数据
File csvFile = new File(filePath+".csv");
dataList = CSVUtils.importCsv(csvFile);

//删除.csv
csvFile.delete();
for(String s:dataList){
System.out.println(s);
}

if(dataList.size()>0){
//.csv中数据转换为entity
List<YellowPagesResource> instances = new ArrayList<YellowPagesResource>();
instances = convertToEntity(type, dataList);
//保存至数据库
this.yellowpageResService.createOrModify(instances);
result = "成功导入:"+instances.size()+"条数据.";
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
}

public static List<String> importCsv(File file){
List<String> dataList=new ArrayList<String>();
FileInputStream in = null;
BufferedReader br=null;
try {
in = new FileInputStream(file);
br = new BufferedReader(new InputStreamReader(in, "GBK"));//设置编码集
String line = "";
while ((line = br.readLine()) != null) {
dataList.add(line);
}
}catch (Exception e) {
}finally{
if(br!=null){
try {
br.close();
br=null;
} catch (IOException e) {
e.printStackTrace();
}
}
}

return dataList;
}

/**
* 将从csv中获取的数据转换成Entity
* @param type 资源类型
* @param dataList
*/
private List<YellowPagesResource> convertToEntity(Integer type, List<String> dataList) {
String[] cells = null;
List<YellowPagesResource> instances = new ArrayList<YellowPagesResource>();
for(int i=0;i<dataList.size();i++){
cells = dataList.get(i).replace("\"", "").split(",");
if(cells.length<10){
continue;
}else{
YellowPagesResource ypr = new YellowPagesResource();
ypr.setPagesName(cells[4]);
ypr.setPagesYear(cells[3]);
ypr.setAddress(cells[1]);
ypr.setPublicCode(cells[5]);
ypr.setPublicName(cells[6]);
ypr.setPublicType(cells[7]);
ypr.setStatus(2);//未销售
ypr.setCreator(SecurityUserHolder.getCurrentUser().getName());
ypr.setProductId("402881ea4c5e43fd014c60660ffd0000");//这个暂时写死的
ypr.setType(type);
if("特殊版面".equals(cells[9])){
ypr.setLayout(2);
}else if("普通版面".equals(cells[9])){
ypr.setLayout(1);
}else{
continue;
}
ypr.setPrice(BigDecimal.valueOf(Double.parseDouble(cells[8])));
instances.add(ypr);
}
}
return instances;
}