/**
*
* @return
* @throws IOException
*/
@ResponseBody
@RequestMapping("/importExcel")
public void importExcel(HttpServletRequest request,HttpServletResponse response) throws IOException{
try {
OutputStream os = response.getOutputStream();
response.reset();
String filename=new String(("信息导出").getBytes("GBK"),"ISO-8859-1");
response.addHeader("Content-Disposition", "attachment;filename="+filename+".xls");// 文件标题
response.setContentType("application/vnd.ms-excel; charset=gbk");// 导出的文件类型,次数为excel
Map<String,String> phoneMap = new HashMap();//用于保存手机号码和URl
String tempFileDir=request.getSession().getServletContext().getRealPath("/") + "xls\\";
File dirTempFile1 = new File(tempFileDir);
if (!dirTempFile1.exists()) {
dirTempFile1.mkdirs();
}
DiskFileItemFactory fileFactory = new DiskFileItemFactory();
ServletFileUpload fu = new ServletFileUpload(fileFactory);
List fileItems = fu.parseRequest(request);
Iterator iter = fileItems.iterator();
List<String> fileNames = new ArrayList<String>();
while (iter.hasNext()) {
FileItem item = (FileItem) iter.next();
if (!item.isFormField()) { // 文件
InputStream is= item.getInputStream();
Map<Integer, String> map = this.readExcelContent(is);
for (int i = 1; i <= map.size(); i++) {
String str=map.get(i);
if(str!=null){
Double d = new Double(str);
java.text.NumberFormat nf = java.text.NumberFormat.getInstance();
nf.setGroupingUsed(false);
phoneMap.put(nf.format(d), referService.queryReferForId(nf.format(d)));
}
}
}
}
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet1");
// 设置excel每列宽度
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 3500);
// 创建Excel的sheet的一行
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 500);// 设定行的高度
// 创建一个Excel的单元格
HSSFCell cell = row.createCell(0);
// 给Excel的单元格设置样式和赋值
cell.setCellValue("hello world");
int i=0;
for (String key : phoneMap.keySet()) {
System.out.println("key= "+ key + " and value= " + phoneMap.get(key));
row = sheet.createRow(i);
cell = row.createCell(0);
cell.setCellValue(key);
cell = row.createCell(1);
cell.setCellValue(phoneMap.get(key));
i++;
}
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取Excel数据内容
* @param InputStream
* @return Map 包含单元格数据内容的Map对象
*/
public Map<Integer, String> readExcelContent(InputStream is) {
Map<Integer, String> content = new HashMap<Integer, String>();
String str = "";
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
str += getCellFormatValue(row.getCell((short) j)).trim() + " ";
j++;
}
content.put(i, str);
str = "";
}
return content;
}
/**
* 根据HSSFCell类型设置数据
* @param cell
* @return
*/
private String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则,转化为Data格式
//方法2:这样子的data格式是不带带时分秒的:2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue =String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}
*
* @return
* @throws IOException
*/
@ResponseBody
@RequestMapping("/importExcel")
public void importExcel(HttpServletRequest request,HttpServletResponse response) throws IOException{
try {
OutputStream os = response.getOutputStream();
response.reset();
String filename=new String(("信息导出").getBytes("GBK"),"ISO-8859-1");
response.addHeader("Content-Disposition", "attachment;filename="+filename+".xls");// 文件标题
response.setContentType("application/vnd.ms-excel; charset=gbk");// 导出的文件类型,次数为excel
Map<String,String> phoneMap = new HashMap();//用于保存手机号码和URl
String tempFileDir=request.getSession().getServletContext().getRealPath("/") + "xls\\";
File dirTempFile1 = new File(tempFileDir);
if (!dirTempFile1.exists()) {
dirTempFile1.mkdirs();
}
DiskFileItemFactory fileFactory = new DiskFileItemFactory();
ServletFileUpload fu = new ServletFileUpload(fileFactory);
List fileItems = fu.parseRequest(request);
Iterator iter = fileItems.iterator();
List<String> fileNames = new ArrayList<String>();
while (iter.hasNext()) {
FileItem item = (FileItem) iter.next();
if (!item.isFormField()) { // 文件
InputStream is= item.getInputStream();
Map<Integer, String> map = this.readExcelContent(is);
for (int i = 1; i <= map.size(); i++) {
String str=map.get(i);
if(str!=null){
Double d = new Double(str);
java.text.NumberFormat nf = java.text.NumberFormat.getInstance();
nf.setGroupingUsed(false);
phoneMap.put(nf.format(d), referService.queryReferForId(nf.format(d)));
}
}
}
}
// 创建Excel的工作书册 Workbook,对应到一个excel文档
HSSFWorkbook wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet1");
// 设置excel每列宽度
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 3500);
// 创建Excel的sheet的一行
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 500);// 设定行的高度
// 创建一个Excel的单元格
HSSFCell cell = row.createCell(0);
// 给Excel的单元格设置样式和赋值
cell.setCellValue("hello world");
int i=0;
for (String key : phoneMap.keySet()) {
System.out.println("key= "+ key + " and value= " + phoneMap.get(key));
row = sheet.createRow(i);
cell = row.createCell(0);
cell.setCellValue(key);
cell = row.createCell(1);
cell.setCellValue(phoneMap.get(key));
i++;
}
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取Excel数据内容
* @param InputStream
* @return Map 包含单元格数据内容的Map对象
*/
public Map<Integer, String> readExcelContent(InputStream is) {
Map<Integer, String> content = new HashMap<Integer, String>();
String str = "";
try {
fs = new POIFSFileSystem(is);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
row = sheet.getRow(i);
int j = 0;
while (j < colNum) {
str += getCellFormatValue(row.getCell((short) j)).trim() + " ";
j++;
}
content.put(i, str);
str = "";
}
return content;
}
/**
* 根据HSSFCell类型设置数据
* @param cell
* @return
*/
private String getCellFormatValue(HSSFCell cell) {
String cellvalue = "";
if (cell != null) {
// 判断当前Cell的Type
switch (cell.getCellType()) {
// 如果当前Cell的Type为NUMERIC
case HSSFCell.CELL_TYPE_NUMERIC:
case HSSFCell.CELL_TYPE_FORMULA: {
// 判断当前的cell是否为Date
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// 如果是Date类型则,转化为Data格式
//方法2:这样子的data格式是不带带时分秒的:2011-10-12
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
}
// 如果是纯数字
else {
// 取得当前Cell的数值
cellvalue =String.valueOf(cell.getNumericCellValue());
}
break;
}
// 如果当前Cell的Type为STRIN
case HSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getRichStringCellValue().getString();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
} else {
cellvalue = "";
}
return cellvalue;
}