day04导出excel、导入excel_e4a操作excel-优快云博客

导出excel

在这里插入图片描述

添加jar包

<!-- excel导出-->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.16</version>
</dependency>

设置客户端以下载的方式导出excel
以下载的方式导出excel必须要以同步的方式，不能以异步（ajax）下载
后端方法返回值String 方法最后return null;

//在客户端下载的方式保存
 String fileName = "customer.xls";//客户端下载的文件名
 response.setHeader("Content-disposition", "attachment;filename="+new String(fileName.getBytes("gb2312"), "ISO8859-1"));//设置文件头编码格式
 response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");//设置类型
 response.setHeader("Cache-Control", "no-cache");//设置头
 response.setDateHeader("Expires", 0);//设置日期头

return null;

创建excel表、行、列

//创建一个excel表格
Workbook wb = new HSSFWorkbook();
//创建一个sheet
Sheet sheet= wb.createSheet("客户信息表");//带参构造器
//指定第三列列宽
sheet.setColumnWidth(3,9000);//第一个参数是列的索引，第二个是宽度。我们这里指定的是第三行日期的列宽
//创建标题行
Row row = sheet.createRow(0);//0表示第一行,1是第二行,以此类推
//用行对象创建列
//创建一个5列的单元格，在遍历,创建列,设置列名
HSSFCell[] titleCell = new HSSFCell[5];
for (int i = 0; i < 5; i++) {
    titleCell[i] = (HSSFCell) row.createCell(i);
}
titleCell[0].setCellValue("序号");
titleCell[1].setCellValue("联系人");
titleCell[2].setCellValue("公司名称");
titleCell[3].setCellValue("添加时间");
titleCell[4].setCellValue("联系电话");

添加数据到表的行和列

//把查询出来的数据写到excel表中
List<Customer> customerList = customerService.getCustomerList();
//格式日期
SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");

for (int i = 0; i < customerList.size(); i++) {
    Customer customer = customerList.get(i);//获取到集合中的customer数据
    //创建第二行（标题是第一行）
    Row rowi = sheet.createRow(i + 1);
    //行对象创建列
    HSSFCell[] dataCell = new HSSFCell[5];
    for (int j = 0; j < 5; j++) {
        dataCell[j] = (HSSFCell) rowi.createCell(j);
    }
    //为每一列设置数据
    dataCell[0].setCellValue(i+1);
    dataCell[1].setCellValue(customer.getCompanyperson());
    dataCell[2].setCellValue(customer.getComname());
    dataCell[3].setCellValue(sdf.format( customer.getAddtime()));
    dataCell[4].setCellValue(customer.getComphone());
}

输出流输出、关闭资源

ServletOutputStream outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
wb.close();
outputStream.close();

完整代码

//导出Excel
@RequestMapping("/export")
public String exportExcel(HttpServletResponse response) throws IOException {
    //在客户端下载的方式保存
    String fileName = "customer.xls";//客户端下载的文件名
    response.setHeader("Content-disposition", "attachment;filename="+new String(fileName.getBytes("gb2312"), "ISO8859-1"));//设置文件头编码格式
    response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");//设置类型
    response.setHeader("Cache-Control", "no-cache");//设置头
    response.setDateHeader("Expires", 0);//设置日期头
    //创建一个excel表格
    Workbook wb = new HSSFWorkbook();
    //创建一个sheet
    Sheet sheet= wb.createSheet("客户信息表");//带参构造器
    //指定第三列列宽
    sheet.setColumnWidth(3,9000);//第一个参数是列的索引，第二个是宽度。我们这里指定的是第三行日期的列宽
    //创建标题行
    Row row = sheet.createRow(0);//0表示第一行,1是第二行,以此类推
    //用行对象创建列
    //创建一个5列的单元格，在遍历,创建列,设置列名
    HSSFCell[] titleCell = new HSSFCell[5];
    for (int i = 0; i < 5; i++) {
        titleCell[i] = (HSSFCell) row.createCell(i);
    }
    titleCell[0].setCellValue("序号");
    titleCell[1].setCellValue("联系人");
    titleCell[2].setCellValue("公司名称");
    titleCell[3].setCellValue("添加时间");
    titleCell[4].setCellValue("联系电话");
    //把查询出来的数据写到excel表中
    List<Customer> customerList = customerService.getCustomerList();
    //格式日期
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");

    for (int i = 0; i < customerList.size(); i++) {
        Customer customer = customerList.get(i);//获取到集合中的customer数据
        //创建第二行（标题是第一行）
        Row rowi = sheet.createRow(i + 1);
        //行对象创建列
        HSSFCell[] dataCell = new HSSFCell[5];
        for (int j = 0; j < 5; j++) {
            dataCell[j] = (HSSFCell) rowi.createCell(j);
        }
        //为每一列设置数据
        dataCell[0].setCellValue(i+1);
        dataCell[1].setCellValue(customer.getCompanyperson());
        dataCell[2].setCellValue(customer.getComname());
        dataCell[3].setCellValue(sdf.format( customer.getAddtime()));
        dataCell[4].setCellValue(customer.getComphone());
    }
    //保存到本地磁盘
    //FileOutputStream fos = new FileOutputStream(new File("C:\\Users\\yang\\Desktop\\customer.xls"));
    ServletOutputStream outputStream = response.getOutputStream();
    wb.write(outputStream);
    outputStream.flush();
    wb.close();
    outputStream.close();
    //fos.close();
    return null;
}

导入excel

前端
点击a标签弹出input file文件上传对话框

<span id="uploadImg">
   <input type="file" id="file" size="1" onclick="importExcel()" >
   <a href="javascript:void(0)" class="coolbg">导入Excel</a>
</span>

css样式

<style>
   #uploadImg{
       font-size:16px;
       overflow:hidden;
       position:absolute
   }
   #file{
       position:absolute;
       z-index:100;
       margin-left:-180px;
       font-size:60px;
       opacity:0;
       filter:alpha(opacity=0); margin-top:-5px;
   }
</style>

js部分

function importExcel() {
//设置循环器，检查是否选中上传文件，选中则发送ajax请求
var result = setInterval(function () {
               var file = $("#file")[0].files[0];
               if (file!=undefined){
                   clearInterval(result);//停止循环
                   //创建表单对象存储上传的文件
                   var formdata = new FormData();
                   formdata.append("file",file);
                   $.ajax({
                       type:'post',
                       url:'${pageContext.request.contextPath}/cust/import',
                       data:formdata,
                       contentType:false,//防止jquery修改文件类型
                       processData:false,//防止jQuery对文件内容进行修改
                       success:function (msg) {
                           if(msg.statusCode == 200){
                               alert(msg.message);
                           }
                       }
                   })
               }
           },1000)
}

后端
导入jar包

<!--excel导入-->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml-schemas</artifactId>
  <version>3.16</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-excelant</artifactId>
  <version>3.16</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-examples</artifactId>
  <version>3.16</version>
</dependency>

controller层

//导入excel
@RequestMapping("import")
@ResponseBody
public Map<String,Object> importExcel(MultipartFile file) throws IOException, InvalidFormatException, ParseException {
    //获取上传的excel文件
    Workbook workbook = WorkbookFactory.create(file.getInputStream());
    //创建保存数据的customer集合
    List<Customer> customers = new ArrayList<Customer>();
    //日期格式化
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
    //遍历excel表里的sheet
    for (int i = 0; i <workbook.getNumberOfSheets(); i++) {
        //获取一个sheet对象
        Sheet sheet = workbook.getSheetAt(i);
        //遍历表里的行,第一行是标题，从第二行开始
        for (int j = sheet.getFirstRowNum()+1; j < sheet.getLastRowNum(); j++) {
            //获取行对象
            Row row = sheet.getRow(j);
            //获取行内列的数据值
            String comperson = row.getCell(1).getStringCellValue();
            String compnayName=row.getCell(2).getStringCellValue();
            String addtime=row.getCell(3).getStringCellValue();
            String phone=row.getCell(4).getStringCellValue();
            Date date = sdf.parse(addtime);
            //创建一个数据的接收对象
            Customer customer = new Customer();
            //把表中的行内数据封装进接收对象
            customer.setCompanyperson(comperson);
            customer.setComname(compnayName);
            customer.setAddtime(date);
            customer.setComphone(phone);
            //将接收对象添加至接收对象列表
            customers.add(customer);
        }
    }
    customerService.batchInsert(customers);
    Map<String,Object> map = new HashMap<String, Object>();
    map.put("statusCode", 200);
    map.put("message","导入成功");
    return map;
}

dao层

<insert id="batchInsert">
  insert into customer(comname,companyperson,comphone,addtime) values
  <foreach collection="list" item="cust" separator=",">
    (#{cust.comname},#{cust.companyperson},#{cust.comphone},#{cust.addtime})
  </foreach>
</insert>