Excel导入导出以及导出压缩文件

项目中有大量的文件解析和文件导出的功能,所有弄了一堆工具类,总结一下

1 导出实体类

package org.atm.dc.app.entry;

import java.io.Serializable;
import java.util.List;

/**
 * 导入对象实体
 * Created by Administrator on 2019/5/9.
 */
public class ExcelData implements Serializable {
    private static final long serialVersionUID = 6133772627258154184L;
    /**
     * 表头
     */
    private List<String> titles;

    /**
     * 数据
     */
    private List<List<Object>> rows;

    /**
     * 页签名称
     */
    private String name;

    public List<String> getTitles() {
        return titles;
    }

    public void setTitles(List<String> titles) {
        this.titles = titles;
    }

    public List<List<Object>> getRows() {
        return rows;
    }

    public void setRows(List<List<Object>> rows) {
        this.rows = rows;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

 

2 工具类

package org.atm.dc.app.util;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;
import org.atm.dc.app.entry.ExcelData;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.concurrent.locks.ReentrantLock;

/**
 * Created by Administrator on 2019/5/9.
 */
public class ExcelUtils {

    private static  ReentrantLock lock=new ReentrantLock();
    /**
     * 使用浏览器选择路径下载
     *
     * @param response
     * @param fileName
     * @param data
     * @throws Exception
     */
    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {

        response.setContentType("application/ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename="
                .concat(String.valueOf(URLEncoder.encode(fileName + ".xls", "UTF-8"))));
        exportExcel(data, response.getOutputStream());
    }

    public static   int generateExcel(ExcelData excelData, String path) throws Exception {
        File f = new File(path);
        FileOutputStream out = new FileOutputStream(f);
        return exportExcel(excelData, out);
    }

    private static synchronized int exportExcel(ExcelData data, OutputStream out) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        int rowIndex = 0;
        try {
            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            rowIndex = writeExcel(wb, sheet, data);
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            out.flush();
            //此处需要关闭 wb 变量
            out.close();
        }
        return rowIndex;
    }


    /**
     * 表显示字段
     *
     * @param wb
     * @param sheet
     * @param data
     * @return
     */
    private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
        int rowIndex = 0;
        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
        rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
        autoSizeColumns(sheet, data.getTitles().size() + 1);
        return rowIndex;
    }

    /**
     * 设置表头
     *
     * @param wb
     * @param sheet
     * @param titles
     * @return
     */
    private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
        int rowIndex = 0;
        int colIndex = 0;
        Font titleFont = wb.createFont();
        //设置字体
        titleFont.setFontName("simsun");
        //设置粗体
        titleFont.setBoldweight(Short.MAX_VALUE);
        //设置字号
        titleFont.setFontHeightInPoints((short) 14);
        //设置颜色
        titleFont.setColor(IndexedColors.BLACK.index);
        XSSFCellStyle titleStyle = wb.createCellStyle();
        //水平居中
        titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        //设置图案颜色
        titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
        //设置图案样式
        titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
        Row titleRow = sheet.createRow(rowIndex);
        titleRow.setHeightInPoints(25);
        colIndex = 0;
        for (String field : titles) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }
        rowIndex++;
        return rowIndex;
    }

    /**
     * 设置内容
     *
     * @param wb
     * @param sheet
     * @param rows
     * @param rowIndex
     * @return
     */
    private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex;
        Font dataFont = wb.createFont();
        dataFont.setFontName("simsun");
        dataFont.setFontHeightInPoints((short) 14);
        dataFont.setColor(IndexedColors.BLACK.index);

        XSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        dataStyle.setFont(dataFont);
        setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
        for (List<Object> rowData : rows) {
            Row dataRow = sheet.createRow(rowIndex);
            dataRow.setHeightInPoints(25);
            colIndex = 0;
            for (Object cellData : rowData) {
                Cell cell = dataRow.createCell(colIndex);
                if (cellData != null) {
                    cell.setCellValue(cellData.toString());
                } else {
                    cell.setCellValue("");
                }
                cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    /**
     * 自动调整列宽
     *
     * @param sheet
     * @param columnNumber
     */
    private static void autoSizeColumns(Sheet sheet, int columnNumber) {
        for (int i = 0; i < columnNumber; i++) {
            int colWidth = sheet.getColumnWidth(i) * 2;
            if (colWidth < 255 * 256) {
                sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
            } else {
                sheet.setColumnWidth(i, 6000);
            }

//            int orgWidth = sheet.getColumnWidth(i);
//            int newWidth = (int) (sheet.getColumnWidth(i) + 100);
//            if (newWidth > orgWidth) {
//                sheet.setColumnWidth(i, newWidth);
//            } else {
//                sheet.setColumnWidth(i, orgWidth);
//            }
            sheet.autoSizeColumn(i, true);
        }
    }

    /**
     * 设置边框
     *
     * @param style
     * @param border
     * @param color
     */
    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(BorderSide.TOP, color);
        style.setBorderColor(BorderSide.LEFT, color);
        style.setBorderColor(BorderSide.RIGHT, color);
        style.setBorderColor(BorderSide.BOTTOM, color);
    }

    /**
     * 读取sheet第一页
     *
     * @param file
     * @return
     */
    public static Sheet readSheet(MultipartFile file) {
        lock.lock();
        Sheet sheet=null;
        try {
            String fileName = file.getOriginalFilename();
            InputStream is = null;
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }
            Workbook wb = null;
            sheet = null;
            is = file.getInputStream();
            sheet = readSheet(is, isExcel2003, 0);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            lock.unlock();
        }
        return sheet;
    }

    private static Sheet readSheet(InputStream inputStream, boolean isExcel2003, int index) {
        Workbook wb = null;
        Sheet sheet = null;
        try {
            if (isExcel2003) {
                wb = new HSSFWorkbook(inputStream);
            } else {
                wb = new XSSFWorkbook(inputStream);
            }
            sheet = wb.getSheetAt(index);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sheet;
    }

    public static Sheet readSheet(File file, int index) {
        Sheet sheet = null;
        try {
            String fileName = file.getName();
            FileInputStream fileInputStream = new FileInputStream(file);
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }
            sheet=readSheet(fileInputStream,isExcel2003,index);
            } catch (IOException e) {
                e.printStackTrace();
            }
        return sheet;
    }

    public static int getNumberOfSheets(File file) {
        try {
            String fileName = file.getName();
            boolean isExcel2003 = true;
            if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                isExcel2003 = false;
            }
            InputStream inputStream = readSheet(file);
            Workbook wb = null;
            try {
                if (isExcel2003) {
                    wb = new HSSFWorkbook(inputStream);
                } else {
                    wb = new XSSFWorkbook(inputStream);
                }
                return wb.getNumberOfSheets();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return 0;
    }

    private static InputStream readSheet(File file) throws FileNotFoundException {
        return new FileInputStream(file);
    }

    /**
     * 获取cell值
     *
     * @param row
     * @param i
     * @return
     */
    public static String getCellValue(Row row, int i) {
        Cell cell = row.getCell(i);
        if (cell == null) {
            return "";
        }
        row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
        return cell.getStringCellValue();
    }
}

上面这个工具类可以进行流的读入和Excel文件的输出(输出到浏览器或者输出到本地)

3 压缩工具类

package org.atm.dc.app.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hwpf.extractor.WordExtractor;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import java.io.FileInputStream;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Paragraph;
import org.apache.poi.hwpf.usermodel.Range;
import org.apache.poi.hwpf.usermodel.Table;
import org.apache.poi.hwpf.usermodel.TableCell;
import org.apache.poi.hwpf.usermodel.TableIterator;
import org.apache.poi.hwpf.usermodel.TableRow;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
/**
 * Created by Administrator on 2019/5/13.
 */
public class IOUtils {
    /**
     * 将服务器文件存到压缩包中
     * @param files
     * @param outputStream
     * @throws IOException
     * @throws ServletException
     */
    public static void zipFile(List<File> files, ZipOutputStream outputStream) throws IOException, ServletException {
        try {
            int size = files.size();
            // 压缩列表中的文件
            for (int i = 0; i < size; i++) {
                File file = (File) files.get(i);
                zipFile(file, outputStream);
            }
        } catch (IOException e) {
            throw e;
        }
    }
    public static void zipFile(File inputFile, ZipOutputStream outputstream) throws IOException, ServletException {
        try {
            if (inputFile.exists()) {
                if (inputFile.isFile()) {
                    FileInputStream inStream = new FileInputStream(inputFile);
                    BufferedInputStream bInStream = new BufferedInputStream(inStream);
                    ZipEntry entry = new ZipEntry(inputFile.getName());
                    outputstream.putNextEntry(entry);

                    final int MAX_BYTE = 10 * 1024 * 1024; // 最大的流为10M
                    long streamTotal = 0; // 接受流的容量
                    int streamNum = 0; // 流需要分开的数量
                    int leaveByte = 0; // 文件剩下的字符数
                    byte[] inOutbyte; // byte数组接受文件的数据

                    streamTotal = bInStream.available(); // 通过available方法取得流的最大字符数
                    streamNum = (int) Math.floor(streamTotal / MAX_BYTE); // 取得流文件需要分开的数量
                    leaveByte = (int) streamTotal % MAX_BYTE; // 分开文件之后,剩余的数量

                    if (streamNum > 0) {
                        for (int j = 0; j < streamNum; ++j) {
                            inOutbyte = new byte[MAX_BYTE];
                            // 读入流,保存在byte数组
                            bInStream.read(inOutbyte, 0, MAX_BYTE);
                            outputstream.write(inOutbyte, 0, MAX_BYTE); // 写出流
                        }
                    }
                    // 写出剩下的流数据
                    inOutbyte = new byte[leaveByte];
                    bInStream.read(inOutbyte, 0, leaveByte);
                    outputstream.write(inOutbyte);
                    outputstream.closeEntry(); // Closes the current ZIP entry
                    // and positions the stream for
                    // writing the next entry
                    bInStream.close(); // 关闭
                    inStream.close();
                }
            } else {
                throw new ServletException("文件不存在!");
            }
        } catch (IOException e) {
            throw e;
        }
    }

    public static void downloadFile(File file, HttpServletResponse response, boolean isDelete) {
        try {
            // 以流的形式下载文件。
            BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file.getPath()));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(file.getName().getBytes("UTF-8"),"ISO-8859-1"));
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
            if(isDelete)
            {
                file.delete();        //是否将生成的服务器端文件删除
            }
        }
        catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    public static ArrayList<File> getFiles(String path) throws Exception {
        //目标集合fileList
        ArrayList<File> fileList = new ArrayList<File>();
        File file = new File(path);
        if(file.isDirectory()){
            File []files = file.listFiles();
            for(File fileIndex:files){
                //如果这个文件是目录,则进行递归搜索
                if(fileIndex.isDirectory()){
                    getFiles(fileIndex.getPath());
                }else {
                    //如果文件是普通文件,则将文件句柄放入集合中
                    fileList.add(fileIndex);
                }
            }
        }
        return fileList;
    }
  
}

4 测试

1 导出excel(导出到浏览器)

  public void export(HttpServletResponse response) {
        String path = "";
        File file = new File(path);
        Sheet sheet = ExcelUtils.readSheet(file, 0);
        //查询人员表
        List<JSONObject> person_list = mongodb.findAll("person");
       //excel表头列
        List<String> titles_list = new ArrayList<>();
        titles_list.add("姓名");
        titles_list.add("年龄");
        //数据集合
        List<List<Object>> rows = new ArrayList<>();
        for (int i = 0; i < person_list.size(); i++) {
            JSONObject jsonObject = person_list.get(i);

            List<Object> row_list = new ArrayList<>();
            row_list.add(jsonObject.getString("name"));
            row_list.add(jsonObject.getString("age"));
            rows.add(row_list);
        }
        //写到excel
        ExcelData excelData = new ExcelData();
        //sheet名称
        excelData.setName("人员表");
        excelData.setRows(rows);
        excelData.setTitles(titles_list);
        try {
            ExcelUtils.exportExcel(response, "人员表", excelData);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

2 导出压缩文件

先把文件导出到服务器某个位置下 然后再将这些文件压缩成一个zip返回给浏览器,再把这些导出文件删除即可

   public void export(HttpServletResponse response) {
        //存到压缩文件的路径
        String DOWNLOAD_ZIP_PATH="";
        //存放excel文件的地方
        String DOWNLOAD_EXCEL_PATH="";
        
        String path = "";
        File file = new File(path);
        Sheet sheet = ExcelUtils.readSheet(file, 0);
        //查询人员表
        List<JSONObject> person_list = mongodb.findAll("person");
       //excel表头列
        List<String> titles_list = new ArrayList<>();
        titles_list.add("姓名");
        titles_list.add("年龄");
        //数据集合
        List<List<Object>> rows = new ArrayList<>();
        for (int i = 0; i < person_list.size(); i++) {
            JSONObject jsonObject = person_list.get(i);

            List<Object> row_list = new ArrayList<>();
            row_list.add(jsonObject.getString("name"));
            row_list.add(jsonObject.getString("age"));
            rows.add(row_list);
        }
        //写到excel
        ExcelData excelData = new ExcelData();
        //sheet名称
        excelData.setName("人员表");
        excelData.setRows(rows);
        excelData.setTitles(titles_list);
        try {
            //ExcelUtils.exportExcel(response, "人员表", excelData);
            //导出到服务器指定文件夹
            ExcelUtils.generateExcel(excelData,DOWNLOAD_EXCEL_PATH+"人员表.xls");
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            //将导出excel转为zip进行导出
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            String zipName = "人员表" + ".zip";
            String outFilePath = DOWNLOAD_ZIP_PATH;
            File file2 = new File(outFilePath);
            if (!file2.exists()) {
                file2.mkdirs();
            }
            File fileZip = new File(outFilePath + zipName);
            fileZip.createNewFile();
            //所有的Excel文件
            ArrayList<File> fileList = IOUtils.getFiles(DOWNLOAD_EXCEL_PATH);
            FileOutputStream outStream = new FileOutputStream(fileZip);
            ZipOutputStream toClient = new ZipOutputStream(outStream);
            IOUtils.zipFile(fileList, toClient);
            toClient.close();
            outStream.close();
            //导出zip到浏览器
            IOUtils.downloadFile(fileZip, response, false);
            //删除文件夹
            org.atm.dc.utils.FileUtils.delete(DOWNLOAD_EXCEL_PATH);
            org.atm.dc.utils.FileUtils.delete(DOWNLOAD_ZIP_PATH);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

3 导入excel文件

直接调用上述工具类中的readsheet即可 

            @RequestMapping("/read2")
            public void read2(MultipartFile file) {
                Sheet sheet = ExcelUtils.readSheet(file);
                int nums = sheet.getLastRowNum();
                for (int i = 0; i <= nums; i++) {
                    Row row = sheet.getRow(i);
                    String cellValue1 = ExcelUtils.getCellValue(row, 0);//第一个cell
                    String cellValue2 = ExcelUtils.getCellValue(row, 1);//第二个cell
                }
            }

若是我有使用错误或者描述不清楚的地方还请麻烦各位指点出来。。。。。

  • 2
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值