一、excel表格导出(搭配环境是idea的maven下,项目名为:FilmManager_parent)
1、新建号maven的项目,并且能够显示全部页面;
2、在jsp页面新建一个"JAVA POI导出 EXCEL"按钮,通过id触发jquery的excelExport2事件
3、jquery 中添加/ 导出方法/事件
function exportFile() {
window.location.href = “${ctx}/export/ajaxExport.html”;
}
4、导入poi包
父类pom中添加:(子类的dao层pom中同样添加下面代码,下面代码添加后会将poi需要的其他包同时导入)
org.apache.poi
poi-ooxml
3.8
org.apache.poi
poi-scratchpad
3.8
5、在控制器里编写export下的ajaxExport.html方法
1)//使用java poi 实现数据库导出(excel)
@Controller
@RequestMapping("/export")
public class ExportExcelContrller extends BaseController {
@RequestMapping("/ajaxExport.html")
public String ajaxExport(HttpServletResponse response){
try {
System.out.println("通过jquery 实现导出数据");
OutputStream os=null;
Workbook wb=null;//工作簿
List<Emp> list=empBiz.getEmps();
//导出excel文件数据,这里需要引用到“ExportExcelUtil”工具类
ExportExcelUtil util=new ExportExcelUtil();
//文件地址书写要正确
//文件在项目中的地址为这个,存放在WEB-INF下面:FilmManager_parent\FileManager_page\src\main\webapp\WEB-INF\classes
File file=util.getExcelDemoFile("excelDemoFile/22.xls");
System.out.println("file="+file);
//这只工作表
String sheetName="Sheet1";
wb=util.writeNewExcel(file, sheetName, list);
//设置下载参数
String fileName="员工列表.xls";
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+URLEncoder.encode(fileName, "UTF-8"));
os=response.getOutputStream();
wb.write(os);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}
2)ExportExcelUtil工具类:
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
//判断要下载到Excel的模板文件是否存在
public File getExcelDemoFile(String fileDir) throws Exception{
String classDir = null;
String fileBaseDir = null;
File file = null;
//这里模板文件的地址要注意,,下面两个双引号里都为空,来设置地址
classDir = Thread.currentThread().getContextClassLoader().getResource("").getPath();
System.out.println(classDir);
fileBaseDir = classDir.substring(0, classDir.lastIndexOf(""));
System.out.println(fileBaseDir);
file = new File(fileBaseDir+fileDir);
System.out.println(file);
if(!file.exists()){
throw new Exception("模板文件不存在!");
}
return file;
}
//判断要下载到Excel的模板文件的文件格式
public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析的文件格式有误!");
}
return wb;
}
//循环插入数据
public Workbook writeNewExcel(File file,String sheetName,List<Emp> lis) throws Exception{
Workbook wb = null;
Row row = null;
Cell cell = null;
FileInputStream fis = new FileInputStream(file);
wb = new ExportExcelUtil().getWorkbook(fis, file.getName());//获取工作薄
Sheet sheet = wb.getSheet(sheetName);
//循环插入数据
CellStyle cs = setSimpleCellStyle(wb);
int lastRow = sheet.getLastRowNum(); //插入数据的数据ROW
row = sheet.createRow(lastRow);//Excel单元格样式
cell = row.createCell(0);
//这里设置的是表头
cell.setCellValue("员工信息表");
cell.setCellStyle(cs);
row = sheet.createRow(lastRow+1);
//Excel单元格样式,下面开始设置的是表格的列头,可不全部下载,可截取需要的内容导出
cell = row.createCell(0);
cell.setCellValue("员工编号");
cell.setCellStyle(cs);
cell = row.createCell(1);
cell.setCellValue("员工姓名");
cell.setCellStyle(cs);
cell = row.createCell(2);
cell.setCellValue("职位");
cell.setCellStyle(cs);
cell = row.createCell(3);
cell.setCellValue("薪资");
cell.setCellStyle(cs);
cell = row.createCell(4);
cell.setCellValue("升职日期");
cell.setCellStyle(cs);
cell = row.createCell(5);
cell.setCellValue("福利");
cell.setCellStyle(cs);
//合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
sheet.addMergedRegion(new CellRangeAddress(0,0,0,5));
for (int i = 0; i < lis.size(); i++) {
row = sheet.createRow(lastRow+i+2); //创建新的ROW,用于数据插入
//按项目实际需求,在该处将对象数据插入到Excel中 CellRangeAddress(0,0,0,4)
Emp emp = lis.get(i);
if(null==emp){
break;
}
//Cell赋值开始
cell = row.createCell(0);
cell.setCellValue(emp.getEmpno());
cell.setCellStyle(cs);
cell = row.createCell(1);
cell.setCellValue(emp.getEname());
cell.setCellStyle(cs);
cell = row.createCell(2);
cell.setCellValue(emp.getJob());
cell.setCellStyle(cs);
cell = row.createCell(3);
cell.setCellValue(emp.getSal());
cell.setCellStyle(cs);
cell = row.createCell(4);
//如果是时间,需要对时间进行格式化
SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
cell.setCellValue(format.format(emp.getHiredate()));
cell.setCellStyle(cs);
cell = row.createCell(5);
cell.setCellValue(emp.getComm());
cell.setCellStyle(cs);
}
return wb;
}
/**
* 描述:设置简单的Cell样式
* @return
*/
public CellStyle setSimpleCellStyle(Workbook wb){
CellStyle cs = wb.createCellStyle();
cs.setBorderBottom(CellStyle.BORDER_THIN); //下边框
cs.setBorderLeft(CellStyle.BORDER_THIN);//左边框
cs.setBorderTop(CellStyle.BORDER_THIN);//上边框
cs.setBorderRight(CellStyle.BORDER_THIN);//右边框
cs.setAlignment(CellStyle.ALIGN_CENTER); // 居中
return cs;
}
<!--success-->
二、excel表格内容批量导入(搭配环境是idea的maven下)
1、在jsp页面添加:
//首先设置模板文件下载,这里需要将模板文件放入:FilmManager_parent\FileManager_page\src\main\webapp\WEB-INF\classes,这个文件夹下,可以
再新建一个文件夹,将example.xls此文件放在其下面,再新建一个
<form id="upfileForm" method="post" enctype="multipart/form-data">
<input id="upfile" type="file" name="upfile" />//选择需要导入的文件按钮
<input type="button" value="Excel导入" id="btnUpfile" />//导入文件按钮
</form>
</p>
2、在jquery中:
//批量导入文件
$('#btnUpfile').click(function() {
if (checkData()) {//验证是否有选择文件,获取判断选取的是否为xsl文件
$('#upfileForm').ajaxSubmit({
url : '${ctx}/upload.html',
dataType : 'text',
success : resutlMsg,
error : errorMsg
});
function resutlMsg(msg) {
alert("导入成功");
location.reload();
}
function errorMsg() {
alert("导入excel出错!");
}
}
});
jquery中的checkData()方法:
//验证是否有文件传入:
function checkData() {
var fileDir = $("#upfile").val();
var suffix = fileDir.substr(fileDir.lastIndexOf("."));
if ("" == fileDir) {
alert("选择需要导入的Excel文件!");
return false;
}
if (".xls" != suffix && ".xlsx" != suffix) {
alert("选择Excel格式的文件导入!");
return false;
}
return true;
}
3、下载模板文件:进入到含有downloadExcel.html的控制器中:
//filename>>>computerExperimentCompletion.xls
@RequestMapping("/downloadExcel.html")
public String download(String filename,HttpServletResponse response){
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-disposition", "attachment;filename="+URLEncoder.encode(filename, "UTF-8"));
String path=Thread.currentThread().getContextClassLoader().getResource("").getPath();
System.out.println(path);
//这里也需要配置模板文件的地址,
String newPath=path.substring(0,path.lastIndexOf(""))+"downloadFile"+File.separator+filename;
System.out.println(newPath);
InputStream inputStream=new FileInputStream(newPath);
OutputStream os=response.getOutputStream();
byte[] b=new byte[1024];
int len;
while((len=inputStream.read(b))!=-1){
os.write(b,0,len);
}
os.close();
inputStream.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
4、导入的模板文件下载好后,在模板文件中填写好需要导入的数据,然后操作导入功能,,进入到含有upload.html',的控制其中:
@RequestMapping(value="/upload.html")
@ResponseBody
public void UploadComputerExperimentCompletion(
HttpServletRequest request,
HttpServletResponse response) throws Exception {
MultipartHttpServletRequest multipartRequest =
(MultipartHttpServletRequest) request;
//首先判断文件是否存在
InputStream in =null;
List<List<Object>> listob = null;
MultipartFile file = multipartRequest.getFile("upfile");
if(file.isEmpty()){
throw new Exception("文件不存在!");
}
//创建输入流 读取文件
in = file.getInputStream();
listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename());
//该处可调用service相应方法进行数据保存到数据库中,现只对数据输出
for (int i = 0; i < listob.size(); i++) {
List<Object> lo = listob.get(i);
//将上传的数据进行封装
Emp vo = new Emp();
vo.setEname(lo.get(0).toString());
vo.setJob(lo.get(1).toString());
vo.setMgr(Integer.parseInt(lo.get(2).toString()));
//如果是时间需要对时间进行格式化:new SimpleDateFormat("yyyy-MM-dd").parse(lo.get(3).toString())
vo.setHiredate(new SimpleDateFormat("yyyy-MM-dd").parse(lo.get(3).toString()));
vo.setSal(Integer.parseInt(lo.get(4).toString()));
vo.setComm(Integer.parseInt(lo.get(5).toString()));
Dept dept=new Dept();
dept.setDeptno(Integer.parseInt(lo.get(6).toString()));
vo.setDept(dept);
vo.setPhoto(lo.get(7).toString());
int result=0;
try {
result = empBiz.addEmp(vo);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println(result);
}
PrintWriter out = null;
response.setCharacterEncoding("utf-8"); //防止ajax接受到的中文信息乱码
out = response.getWriter();
out.print("文件导入成功!");
out.flush();
out.close();
}
5、导入时需要用到ImportExcelUtil()工具类:
//导入excel工具类
public class ImportExcelUtil {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
* 描述:获取IO流中的数据,组装成List<List<Object>>对象
* @param in,fileName
* @return
* @throws IOException
*/
public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = this.getWorkbook(in,fileName);
if(null == work){
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if(sheet==null){continue;}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if(row==null||row.getFirstCellNum()==j){continue;}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(this.getCellValue(cell));
}
list.add(li);
}
}
//work.close();
return list;
}
/**
* 描述:根据文件后缀,自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = new XSSFWorkbook(inStr); //2007+
}else{
throw new Exception("解析的文件格式有误!");
}
return wb;
}
/**
* 描述:对表格中数值进行格式化
* @param cell
* @return
*/
public Object getCellValue(Cell cell){
Object value = null;
DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if("General".equals(cell.getCellStyle().getDataFormatString())){
value = df.format(cell.getNumericCellValue());
}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
value = sdf.format(cell.getDateCellValue());
}else{
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
}