java poi导入Excel、导出excel

java poi导入Excel、导出excel

导出meven架包

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

导入Excel

    public void uploadFile(HttpServletRequest request, HttpServletResponse response, @RequestParam(value="file",required = false) MultipartFile file) {
        return import(request, response, file);
    }

	public void import(HttpServletRequest request, HttpServletResponse response, MultipartFile file) {
        long startTime = System.currentTimeMillis();
        //解析Excel
        List<DTO> excelInfo = ReadPatientExcelUtil.getExcelInfo(file);
        if(excelInfo!=null && excelInfo.size()>0){
            for (int i = 0; i < excelInfo.size(); i++) {
                DTO dto = excelInfo.get(i);
            }
        }else{
            System.out.println("导入失败，请注意参数格式！");
        }
        long endTime = System.currentTimeMillis();
        String time = String.valueOf((endTime - startTime) / 1000);
        log.info("导入数据用时:"+time+"秒");
        return ResponseMsg.success("导入成功！");
    }

	    

ReadPatientExcelUtil

import com.ly.directserver.dto.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/***
 * 解析导入Excel数据
 * @author manba
 */
@Slf4j
public class ReadPatientExcelUtil {

    //总行数
    private static int totalRows = 0;
    //总条数
    private static int totalCells = 0;
    //错误信息接收器
    private static String errorMsg;
    
    /***
     * 读取Excel
     * @param mFile
     * @return
     */
    public static List<DTO> getExcelInfo(MultipartFile mFile) {
        String fileName = mFile.getOriginalFilename();//获取文件名
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            List<DTO> agentList = getExcel(mFile.getInputStream(), isExcel2003);
            return agentList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

	    public static List<DTO> getAgentExcel(InputStream is, boolean isExcel2003) {
        try {
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            List<DTO> DTOS = readExcelValue(wb);// 读取Excel里面客户的信息
            return DTOS;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

	private static List<DTO> readExcelValue(Workbook wb) {
        //默认会跳过第一行标题
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<DTO> DTOS = new ArrayList<>();
        // 循环Excel行数
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            DTO DTO = new DTO();
            // 循环Excel的列
            for (int c = 0; c < totalCells ; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 0) {           //第一列
                        //如果是纯数字
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.NUMERIC);
                            int a =(int) cell.getNumericCellValue();
                        }
                    }  else if (c == 1) {
                    //如果是double
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.NUMERIC);
                        }
                        double stringCellValue = cell.getNumericCellValue();
                    } else if (c == 2) {
                        if (cell.getCellTypeEnum() == CellType.STRING) {
                            cell.setCellType(CellType.STRING);
                            //如果是字符串
                            String str = cell.getStringCellValue();
                        }else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellType(CellType.NUMERIC);
                            //如果是数字，需要转换为字符串
                            String desc = NumberToTextConverter.toText(cell.getNumericCellValue());
                        }
                    }
                }
            }
            //将excel解析出来的数据赋值给对象添加到list中
            // 添加到list
            DTOS.add(DTO);
        }
        return DTOS;
    }
    }

导出Excel

	    public void exportCollectRecord(HttpServletResponse res){
        File file = createExcelFile();
        FileUtils.downloadFile(res, file, file.getName());
    }

	public static File createExcelFile(List<DTO> list) {
        Workbook workbook = new XSSFWorkbook();
        //创建一个sheet，括号里可以输入sheet名称，默认为sheet0
        Sheet sheet = workbook.createSheet();
        Row row0 = sheet.createRow(0);
        int columnIndex = 0;
        row0.createCell(columnIndex).setCellValue("xxx");
        row0.createCell(++columnIndex).setCellValue("xxx");
        row0.createCell(++columnIndex).setCellValue("xxx");

        for (int i = 0; i < list.size(); i++) {
            DTO dto = list.get(i);

            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < columnIndex + 2; j++) {
                row.createCell(j);
            }
            columnIndex = 0;
            row.getCell(columnIndex).setCellValue("xxx");
            row.getCell(++columnIndex).setCellValue("xxx");
            row.getCell(++columnIndex).setCellValue("xxx");
           
            
        }
        //调用PoiUtils工具包
        return PoiUtils.createExcelFile(workbook, DateUtils.fmtDateToStr(new Date(), "yyyy-MM-dd HH_mm_ss") );
    }

	
	

PoiUtils

import org.apache.poi.ss.usermodel.Workbook;
import java.io.*;

public class PoiUtils {

    /**
     * 生成Excel文件
     * @param workbook
     * @param fileName
     * @return
     */
    public static File createExcelFile(Workbook workbook, String fileName) {
        OutputStream stream = null;
        File file = null;
        try {
            //用了createTempFile，这是创建临时文件，系统会自动给你的临时文件编号，所以后面有号码，你用createNewFile的话就完全按照你指定的名称来了
            file = File.createTempFile(fileName, ".xlsx");
            stream = new FileOutputStream(file.getAbsoluteFile());
            workbook.write(stream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            //这里调用了IO工具包控制开关
            IOUtils.closeQuietly(workbook);
            IOUtils.closeQuietly(stream);
        }
        return file;
    }

}

FileUtils

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import java.io.*;
import java.lang.reflect.Method;
import java.security.MessageDigest;

public class FileUtils {

    /**
     * 下载文件
     * @param response
     * @param file
     * @param newFileName
     */
    public static void downloadFile(HttpServletResponse response, File file, String newFileName) {
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + new String(newFileName.getBytes("ISO-8859-1"), "UTF-8"));
            BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
            InputStream is = new FileInputStream(file.getAbsolutePath());
            BufferedInputStream bis = new BufferedInputStream(is);
            int length = 0;
            byte[] temp = new byte[1 * 1024 * 10];
            while ((length = bis.read(temp)) != -1) {
                bos.write(temp, 0, length);
            }
            bos.flush();
            bis.close();
            bos.close();
            is.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 将输入流使用指定编码转化为字符串
    public static String inputStream2String(InputStream inputStream, String charset) throws Exception {
        // 建立输入流读取类
        InputStreamReader reader = new InputStreamReader(inputStream, charset);
        // 设定每次读取字符个数
        char[] data = new char[512];
        int dataSize = 0;
        // 循环读取
        StringBuilder stringBuilder = new StringBuilder();
        while ((dataSize = reader.read(data)) != -1) {
            stringBuilder.append(data, 0, dataSize);
        }
        return stringBuilder.toString();
    }

    private static DocumentBuilderFactory documentBuilderFactory = null;

    public static <T> T parseXml2Obj(String xml, Class<T> tclass) throws Exception {
        if (isEmpty(xml)) throw new NullPointerException("要解析的xml字符串不能为空。");
        if (documentBuilderFactory == null) { // 文档解析器工厂初始
            documentBuilderFactory = DocumentBuilderFactory.newInstance();
        }
        // 拿到一个文档解析器。
        DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder();
        // 准备数据并解析。
        byte[] bytes = xml.getBytes("UTF-8");
        Document parsed = documentBuilder.parse(new ByteArrayInputStream(bytes));
        // 获取数据
        T obj = tclass.newInstance();
        Element documentElement = parsed.getDocumentElement();
        NodeList childNodes = documentElement.getChildNodes();
        for (int i = 0; i < childNodes.getLength(); i++) {
            Node item = childNodes.item(i);
            // 节点类型是 ELEMENT 才读取值
            // 进行此判断是因为如果xml不是一行，而是多行且有很好的格式的，就会产生一些文本的node，这些node内容只有换行符或空格
            // 所以排除这些换行符和空格。
            if (item.getNodeType() == Node.ELEMENT_NODE) {
                String key = item.getNodeName();
                String value = item.getTextContent();
                // 拿到设置值的set方法。
                Method declaredMethod = tclass.getDeclaredMethod("set" + key, String.class);
                if (declaredMethod != null) {
                    declaredMethod.setAccessible(true);
                    declaredMethod.invoke(obj, value); // 设置值
                }
            }
        }
        return obj;
    }


    // 将二进制数据转换为16进制字符串。
    public static String byte2HexString(byte[] src) {
        StringBuilder stringBuilder = new StringBuilder();
        if (src == null || src.length <= 0) {
            return null;
        }
        for (byte b : src) {
            String hv = Integer.toHexString(b & 0xFF);
            if (hv.length() < 2) {
                stringBuilder.append(0);
            }
            stringBuilder.append(hv);
        }
        return stringBuilder.toString();
    }
    // 对字符串进行sha1加签
    public static String sha1(String context) throws Exception {
        // 获取sha1算法封装类
        MessageDigest sha1Digest = MessageDigest.getInstance("SHA-1");
        // 进行加密
        byte[] digestResult = sha1Digest.digest(context.getBytes("UTF-8"));
        // 转换为16进制字符串
        return byte2HexString(digestResult);
    }
    public static boolean isEmpty(String value) {
        return value == null || value.length() == 0;
    }

}