• 实现文件批量导入导出实践(兼容xls,xlsx)


    1、介绍

    java实现文件的导入导出数据库,目前在大部分系统中是比较常见的功能了,今天写个小demo来理解其原理,没接触过的同学也可以看看参考下。

    目前我所接触过的导入导出技术主要有POI和iReport,poi主要作为一些数据批量导入数据库,iReport做报表导出。另外还有jxl类似poi的方式,不过貌似很久没跟新了,2007之后的office好像也不支持,这里就不说了。

    2、POI使用详解

    2.1 什么是Apache POI?

    Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

    2.2 POI的jar包导入

    <!-- poi的包 3.15版本后单元格类型获取方式有调整 -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.14</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.14</version>
            </dependency>

    2.3 POI的API讲解

    2.3.1 结构

    HSSF - 提供读写Microsoft Excel格式档案的功能。
    XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。
    HWPF - 提供读写Microsoft Word格式档案的功能。
    HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
    HDGF - 提供读写Microsoft Visio格式档案的功能。

    2.3.2 对象

    本文主要介绍HSSF和XSSF两种组件,简单的讲HSSF用来操作Office 2007版本前excel.xls文件,XSSF用来操作Office 2007版本后的excel.xlsx文件,注意二者的后缀是不一样的。

    HSSF在org.apache.poi.hssf.usermodel包中。它实现了Workbook 接口,用于Excel文件中的.xls格式

    常用组件:

    HSSFWorkbook : excel的文档对象
    HSSFSheet : excel的表单
    HSSFRow : excel的行
    HSSFCell : excel的格子单元
    HSSFFont : excel字体
    HSSFDataFormat: 日期格式
    HSSFHeader : sheet头
    HSSFFooter : sheet尾(只有打印的时候才能看到效果)

    样式:

    HSSFCellStyle : cell样式

    辅助操作包括:

    HSSFDateUtil : 日期
    HSSFPrintSetup : 打印
    HSSFErrorConstants : 错误信息表

    XSSF在org.apache.xssf.usemodel包,并实现Workbook接口,用于Excel文件中的.xlsx格式

    常用组件:

    XSSFWorkbook : excel的文档对象
    XSSFSheet: excel的表单
    XSSFRow: excel的行
    XSSFCell: excel的格子单元
    XSSFFont: excel字体
    XSSFDataFormat : 日期格式

    和HSSF类似;

    2.3.3 两个组件共同的字段类型描述

    其实两个组件就是针对excel的两种格式,大部分的操作都是相同的。

    2.3.4 操作步骤

    以HSSF为例,XSSF操作相同。

    首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(HSSFSheet)组成,一个sheet是由多个row(HSSFRow)组成,一个row是由多个cell(HSSFCell)组成。

    1、用HSSFWorkbook打开或者创建“Excel文件对象”

    2、用HSSFWorkbook对象返回或者创建Sheet对象

    3、用Sheet对象返回行对象,用行对象得到Cell对象

    4、对Cell对象读写。

    3、代码操作

    3.1 效果图

    惯例,贴代码前先看效果图

    Excel文件两种格式各一个:

                           

      

     导入后:(我导入了两遍,没做校验)

     

     导出效果:

    3.2 代码详解

    这里我以Spring+SpringMVC+Mybatis为基础,扩展:SpringBoot+Mybatis多模块(module)项目搭建教程

    Controller:

    package com.allan.controller;
    
    import java.util.List;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.ss.formula.functions.Mode;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.ui.Model;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.ResponseBody;
    import org.springframework.web.multipart.MultipartFile;
    import org.springframework.web.servlet.ModelAndView;
    
    import com.allan.pojo.Student;
    import com.allan.service.StudentService;
    /**
     * 
     * @author 小卖铺的老爷爷
     *
     */
    @Controller
    public class StudentController {
        @Autowired
        private StudentService studentService;
        /**
         * 批量导入表单数据
         * 
         * @param request
         * @param myfile
         * @return
         */
    
        @RequestMapping(value="/importExcel",method=RequestMethod.POST)
        public String importExcel(@RequestParam("myfile") MultipartFile myFile) {
            ModelAndView modelAndView = new ModelAndView();
            try {
                Integer num = studentService.importExcel(myFile);
            } catch (Exception e) {
                modelAndView.addObject("msg", e.getMessage());
                return "index";
            }
            modelAndView.addObject("msg", "数据导入成功");
    
            return "index";
        }
    
        @RequestMapping(value="/exportExcel",method=RequestMethod.GET)
        public void exportExcel(HttpServletResponse response) {    
            try {
                studentService.exportExcel(response);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
    
    
    }

    Service

    package com.allan.service.impl;
    
    import java.io.OutputStream;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.commons.lang.StringUtils;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.web.multipart.MultipartFile;
    
    import com.allan.mapper.StudentMapper;
    import com.allan.pojo.Student;
    import com.allan.service.StudentService;
    /**
     * 
     * @author 小卖铺的老爷爷
     *
     */
    @Service
    public class StudentServiceImpl implements StudentService{
        private final static String XLS = "xls";  
        private final static String XLSX = "xlsx"; 
        @Autowired
        private StudentMapper studentMapper;
        /**
         * 导入Excel,兼容xls和xlsx
         */
        @SuppressWarnings("resource")
        public Integer importExcel(MultipartFile myFile) throws Exception {
            //        1、用HSSFWorkbook打开或者创建“Excel文件对象”
            //
            //        2、用HSSFWorkbook对象返回或者创建Sheet对象
            //
            //        3、用Sheet对象返回行对象,用行对象得到Cell对象
            //
            //        4、对Cell对象读写。
            //获得文件名  
            Workbook workbook = null ;
            String fileName = myFile.getOriginalFilename(); 
            if(fileName.endsWith(XLS)){  
                //2003  
                workbook = new HSSFWorkbook(myFile.getInputStream());  
            }else if(fileName.endsWith(XLSX)){  
                //2007  
                workbook = new XSSFWorkbook(myFile.getInputStream());  
            }else{
                throw new Exception("文件不是Excel文件");
            }
    
            Sheet sheet = workbook.getSheet("Sheet1");
            int rows = sheet.getLastRowNum();// 指的行数,一共有多少行+
            if(rows==0){
                throw new Exception("请填写数据");
            }
            for (int i = 1; i <= rows+1; i++) {
                // 读取左上端单元格
                Row row = sheet.getRow(i);
                // 行不为空
                if (row != null) {
                    // **读取cell**
                    Student student = new Student();
                    //姓名
                    String name = getCellValue(row.getCell(0));
                    student.setName(name);
                    //班级
                    String classes = getCellValue(row.getCell(1));
                    student.setClasses(classes);
                    //分数
                    String scoreString = getCellValue(row.getCell(2));
                    if (!StringUtils.isEmpty(scoreString)) {
                        Integer score = Integer.parseInt(scoreString);
                        student.setScore(score);
                    }
                    //考试时间
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//小写的mm表示的是分钟  
                    String dateString = getCellValue(row.getCell(3));  
                    if (!StringUtils.isEmpty(dateString)) {
                        Date date=sdf.parse(dateString);  
                        student.setTime(date);
                    }
                    studentMapper.insert(student);
                }
            }
            return rows-1;
        }
    
        /**
         * 获得Cell内容
         * 
         * @param cell
         * @return
         */
        public String getCellValue(Cell cell) {
            String value = "";
            if (cell != null) {
                // 以下是判断数据的类型
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    value = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
                }
            }
            return value.trim();
        }
        /**
         * 导出excel文件
         */
        public void exportExcel(HttpServletResponse response) throws Exception {
            // 第一步,创建一个webbook,对应一个Excel文件  
            HSSFWorkbook wb = new HSSFWorkbook();  
            // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet  
            HSSFSheet sheet = wb.createSheet("Sheet1");  
            // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
            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);  
    
            // 第五步,写入实体数据 实际应用中这些数据从数据库得到,  
            List<Student> list = studentMapper.selectAll();  
    
            for (int i = 0; i < list.size(); i++){  
                row = sheet.createRow(i + 1);  
                Student stu = list.get(i);  
                // 第四步,创建单元格,并设置值  
                row.createCell(0).setCellValue(stu.getName());  
                row.createCell(1).setCellValue(stu.getClasses());  
                row.createCell(2).setCellValue(stu.getScore());  
                cell = row.createCell(3);  
                cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(stu.getTime()));  
            }          
            //第六步,输出Excel文件
            OutputStream output=response.getOutputStream();
            response.reset();
            long filename = System.currentTimeMillis();
            SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
            String fileName = df.format(new Date());// new Date()为获取当前系统时间
            response.setHeader("Content-disposition", "attachment; filename="+fileName+".xls");
            response.setContentType("application/msexcel");        
            wb.write(output);
            output.close();
        }  
    
    }

    3.3 导出文件api补充

    大家可以看到上面service的代码只是最基本的导出。

    在实际应用中导出的Excel文件往往需要阅读和打印的,这就需要对输出的Excel文档进行排版和样式的设置,主要操作有合并单元格、设置单元格样式、设置字体样式等。

    3.3.1 单元格合并

    使用HSSFSheet的addMergedRegion()方法

    public int addMergedRegion(CellRangeAddress region)

    参数CellRangeAddress 表示合并的区域,构造方法如下:依次表示起始行,截至行,起始列, 截至列

    CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

    3.3.2 设置单元格的行高和列宽

    HSSFSheet sheet=wb.createSheet();
    sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽
    //设置指定列的列宽,256 * 50这种写法是因为width参数单位是单个字符的256分之一
    sheet.setColumnWidth(cell.getColumnIndex(), 256 * 50);

    3.3.3 设置单元格样式

    1、创建HSSFCellStyle

    HSSFCellStyle cellStyle=wkb.createCellStyle()

    2、设置样式

    // 设置单元格的横向和纵向对齐方式,具体参数就不列了,参考HSSFCellStyle
      cellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
      cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      /* 设置单元格的填充方式,以及前景颜色和背景颜色
       三点注意:
       1.如果需要前景颜色或背景颜色,一定要指定填充方式,两者顺序无所谓;
       2.如果同时存在前景颜色和背景颜色,前景颜色的设置要写在前面;
       3.前景颜色不是字体颜色。
      */
      //设置填充方式(填充图案)
      cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);
      //设置前景色
      cellStyle.setFillForegroundColor(HSSFColor.RED.index);
      //设置背景颜色
      cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);
      // 设置单元格底部的边框及其样式和颜色
      // 这里仅设置了底边边框,左边框、右边框和顶边框同理可设
      cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);
      cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index);
      //设置日期型数据的显示样式
      cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

    3、将样式应用于单元格

      cell.setCellStyle(cellStyle);
      //将样式应用到行,但有些样式只对单元格起作用
      row.setRowStyle(cellStyle);

    3.3.4设置字体样式

    1、创建HSSFFont对象(调用HSSFWorkbook 的createFont方法)

    HSSFWorkbook wb=new HSSFWorkbook();
    HSSFFont  fontStyle=wb.createFont();
    HSSFWorkbook wb=new HSSFWorkbook ();

    2、设置字体各种样式

    //设置字体样式
      fontStyle.setFontName("宋体");  
      //设置字体高度
      fontStyle.setFontHeightInPoints((short)20);  
      //设置字体颜色
      font.setColor(HSSFColor.BLUE.index);
      //设置粗体
      fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
      //设置斜体
      font.setItalic(true);
      //设置下划线
      font.setUnderline(HSSFFont.U_SINGLE);

    3、将字体设置到单元格样式

    //字体也是单元格格式的一部分,所以从属于HSSFCellStyle
    // 将字体对象赋值给单元格样式对象
    cellStyle.setFont(font);
    // 将单元格样式应用于单元格
    cell.setCellStyle(cellStyle);
  • 相关阅读:
    python定义函数的三种形式
    python函数的返回值
    python函数的调用
    python函数的定义
    python文件操作
    Python2和3字符编码的区别
    python的字符编码
    python异常处理
    python深浅拷贝
    python色彩缤纷的python(改变字体颜色以及样式)
  • 原文地址:https://www.cnblogs.com/sxw123/p/14034672.html
Copyright © 2020-2023  润新知