java技术poi技术导入导出工具类

1、poi的maven文件

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

2、导入ExcelExport<T>

package com.data.encrypt.crypto.utils.test;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

public class ExcelExport<T> {

    /**
     * 利用了JAVA的反射机制，将放置在JAVA集合中并且符合一定条件的数据以EXCEL 的形式输出到指定IO设备上
     * @param title   表格标题名
     * @param headers 表格属性列名数组
     * @param dataSet 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *                javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */

    @SuppressWarnings({"unchecked", "rawtypes"})
    public void exportExcel(String title, String[] headers,
                            Collection<T> dataSet, OutputStream out, String pattern) {
        // 声明一个工作薄，将1000条写入内存，其余在硬盘，以防止内存溢出
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        // 生成一个表格
        Sheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为10个字节
        sheet.setDefaultColumnWidth((short) 10);
        //设置表格头样式
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);//设置背景色
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 产生表格标题行
        Row row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        // 设置表格内容样式
        CellStyle style2 = workbook.createCellStyle();
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        Font font2 = workbook.createFont();
        font.setFontName("仿宋_GB2312");
        font.setColor((short) 64);//黑色
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 遍历集合数据，产生数据行
        Iterator<T> it = dataSet.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (short i = 0; i < fields.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(style2);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                try {
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(t, new Object[]{});
                    // 根据javabean的方法类型，判断值的类型后进行强制类型转换（具体需要自己根据情况修改）
                    String textValue = null;
                    //javabean里边的get和set方法为Integer时做处理
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue(intValue);
                    }
                    else if (value instanceof Double) {
                        double intValue = (Double) value;
                        cell.setCellValue(intValue);
                    }
                    else if (value instanceof Boolean) {
                        boolean bValue = (Boolean) value;
                        textValue = bValue ? "是" : "否";
                        cell.setCellValue(textValue);
                    }
                    else if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                        cell.setCellValue(textValue);
                    } else if (value instanceof byte[]) {
                        // 声明一个画图的顶级管理器
                        Drawing drawing= sheet.createDrawingPatriarch();
                        row.setHeightInPoints(60);
                        sheet.setColumnWidth(i, (short) (35.7 * 80));
                        byte[] bsValue = (byte[]) value;
                        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, i, index, i+1, index+1);
                        anchor.setAnchorType(ClientAnchor.AnchorType.byId(0));
                        drawing.createPicture(anchor, workbook.addPicture(bsValue, XSSFWorkbook.PICTURE_TYPE_JPEG));
                    } else {
                        if (value != null) {
                            textValue = value.toString();
                        } else {
                            textValue = "";
                        }
                        cell.setCellValue(textValue);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

3、ExcelImport

package com.data.encrypt.crypto.utils.test;

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 java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelImport {
    /**
     *
     * @param fileName 文件
     * @param is  文件流
     * @param pattern 返回日期格式
     * @param isExistTitle 是否存在表头
     * @return map
     */
    public Map<Integer, List<List<Object>>> getExcelRow(String fileName, InputStream is,String pattern, boolean isExistTitle){
        List<List<Object>> list;
        Map<Integer,List<List<Object>>> map = null;
        Workbook workbook = null;
        boolean isExcel03 = fileName.matches("^.+\\.(?i)(xls)$");
        boolean isExcel07 = fileName.matches("^.+\\.(?i)(xlsx)$");
        try {
            if (isExcel03) {
                workbook = new HSSFWorkbook(is);
            }else if (isExcel07) {
                workbook = new XSSFWorkbook(is);
            }else{
                return null;
            }
            int numberOfSheets = workbook.getNumberOfSheets();
            map = new HashMap<Integer,List<List<Object>>>();
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheetAt = workbook.getSheetAt(i);
                int lastRowNum = sheetAt.getLastRowNum();//行数从0开始
                list = new ArrayList<List<Object>>();
                for (int j = 0; j <= lastRowNum; j++) {
                    if (j == 0 && isExistTitle) {
                        j++;
                        continue;
                    }
                    Row row = sheetAt.getRow(j);
                    short lastCellNum = row.getLastCellNum();
                    List<Object> li = new ArrayList<Object>();
                    for (int k = 0; k < lastCellNum; k++) {
                        li.add(getCellValue(row.getCell(k), pattern));
                    }
                    list.add(li);
                }
                map.put(i, list);
            }
        } catch(Exception e){
            e.printStackTrace();
        }
        return  map;
    }

    private Object getCellValue(Cell cell,String pattern){
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case 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 BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }
}

4、 Goods

package com.data.encrypt.crypto.utils.test;

import java.util.Date;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Goods {
                private Integer goodsID;
                private String goodsName;
                private Double price;
                private Date effectiveDate;
                private byte[] image;
                private Boolean show;

}

5、Test测试

package com.data.encrypt.crypto.utils.test;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.data.encrypt.crypto.Crypto;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

public class Test {
    @org.junit.Test
    public void poiExport(){
        try {
            OutputStream os = new FileOutputStream(new File("D://test01.xlsx"));
            List<Goods> list = new ArrayList<Goods>();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            FileInputStream fis = new FileInputStream(new File("D://chutong/12.jpg"));
            int j;
            byte[] data = new byte[1024];
            while ((j = fis.read(data))!=-1){
                bos.write(data,0,j);
            }
            fis.close();
            bos.close();
            byte[] img = bos.toByteArray();
            for(int i = 0;i<10;i++){
                Goods goods = new Goods(i,"商品"+i,20.500+i,new Date(),img,true);
                list.add(goods);
            }
            String str[] = new String[]{"商品id","商品名称","商品价格","有效期","商品图片","是否显示"};
            ExcelExport<Goods> export = new ExcelExport<Goods>();
            export.exportExcel("测试导出工具类",str,list,os,"yyyy-MM-dd HH:mm:ss");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @org.junit.Test
    public void importTest() {
        FileInputStream fis;
        ExcelImport excelImport = new ExcelImport();
        try {
            fis = new FileInputStream(new File("D://test01.xlsx"));
            Map<Integer,List<List<Object>>> map = excelImport.getExcelRow("test01.xlsx", fis,"yyyy年MM月dd日",false);
            if(map!=null) {
                List<List<Object>> lists = map.get(0);
                System.out.println(lists.toString());
                //将获取的参数在导出
                String str[] = new String[]{"商品id","商品名称","商品价格","有效期","商品图片","是否显示"};
                ExcelExport<Goods> export = new ExcelExport<Goods>();
                OutputStream os01 = new FileOutputStream(new File("D://test01-01.xlsx"));
                lists.remove(0);
                //System.out.println("objectsList-->>"+objectsList);
                ArrayList<Goods> goodsList = new ArrayList<Goods>();
                for (List<Object> tempList: lists) {
                    Goods goods = new Goods();
                    goods.setGoodsID(Integer.parseInt(tempList.get(0)+""));
                    goods.setPrice(Double.parseDouble(tempList.get(0)+""));
                    goods.setGoodsName(Crypto.generateCryptoSM3Data("abc"));
                    goodsList.add(goods);
                }
                export.exportExcel("测试导出工具类",str,goodsList,os01,"yyyy-MM-dd HH:mm:ss");
            }else{
                System.out.println("导入excel文件格式错误");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}

6、导出的execl文件

7、导出在导入