java 读取excel写入txt并压缩文件

声明：本文纯属个人随手笔记，如果对您有参考价值我十分开心，如果有存在错误，或者有更好的解决办法也麻烦您留言告诉我，大家共同成长，切勿恶言相。 欢迎加入资源共享QQ群：275343679，一起发现知识、了解知识、学习知识、分享知识。网站：www.itlantian.top 

=======================================================================

本文是读取excel写入txt并压缩文件：

1.引入架包：

       <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
        <!-- 处理excel和上面功能是一样的-->
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.10</version>
        </dependency>

2.读取excel写入txt代码如下：

package com.xxxx.cpos.modules.utils;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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;

/**
 * @ClassName ExcelUtil
 * @Description
 * @Author zhangs
 * @Date 2019年3月5日 下午2:28:26
 */
public class ExcelUtil {

    private static final String excel_xls = "xls";
    private static final String excel_xlsx = "xlsx";
    private static final String excel = "excel";
    private static final String txt = "txt";
    private static final String log = "log";
    private static final String zip = "zip";
    private static final String ftpRemote = "ftpRemote";

    public static void main(String[] args) {
        // CPOS_MD_D_2010_20180611223042.log
        // CPOS_MD_D_2010_20180611223042.TXT
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");// 设置日期格式
        String dateStr = df.format(new Date());// new Date()为获取当前系统时间
        Map<String, String> pathMap = new HashMap<String, String>();
        pathMap.put("excel", "D:\\cposMD.xlsx");
        pathMap.put("txt", "D:\\MD\\CPOS_MD_D_3100_" + dateStr + ".TXT.txt");
        pathMap.put("log", "D:\\MD\\CPOS_MD_D_3100_" + dateStr + ".log.txt");
        pathMap.put("zip", "D:\\MD\\CPOS_MD_D_3100_" + dateStr + ".zip");
        pathMap.put("ftpRemote", "");
        // 根据下标取excel字段
        int columns[] = { 9, 5, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,20, 21 };
        readExcel(pathMap, 2, columns);
    }

    /**
     * 读取excel
     * 
     * @param file
     * @return
     */
    public static List readExcel(Map<String, String> pathMap, int beginRowNum, int columns[]) {
        File file = new File(pathMap.get(excel));
        Workbook wb = getWorkBook(file);
        Sheet sheet = null;
        Row row = null;
        List<String> list = null;
        String cellData = null;
        StringBuilder sb = new StringBuilder();
        if (wb != null) {
            // 用来存放表中数据
            list = new ArrayList<String>();
            int sheetNum = wb.getNumberOfSheets();
            for (int s = 0; s < sheetNum; s++) {
                // 获取第一个sheet
                sheet = wb.getSheetAt(s);
                // 获取最大行数
                int rownum = sheet.getPhysicalNumberOfRows();
                // 获取第一行
                row = sheet.getRow(0);
                // 获取最大列数
                int colnum = row.getPhysicalNumberOfCells();
                for (int i = beginRowNum; i < rownum; i++) {
                    Map<String, String> map = new LinkedHashMap<String, String>();
                    row = sheet.getRow(i);
                    if (row != null) {
                        if (columns.length == 0) {
                            for (int c = 0; c < colnum; c++) {
                                cellData = (String) getCellFormatValue(row
                                        .getCell(c));
                                if (sb.length() == 0) {
                                    sb.append(cellData);
                                } else {
                                    sb.append("\t").append(cellData);
                                }
                            }
                        } else {
                            for (int c = 0; c < columns.length; c++) {
                                cellData = (String) getCellFormatValue(row
                                        .getCell(columns[c]));
                                if (sb.length() == 0) {
                                    sb.append(cellData);
                                } else {
                                    sb.append("\t").append(cellData);
                                }
                            }
                        }
                    } else {
                        break;
                    }
                    list.add(sb.toString());
                    sb.setLength(0);
                }

            }
        }

        // 遍历解析出来的list
        for (String s : list) {
            System.out.println(s);
        }

        writeFile(list, pathMap.get(txt));
        writeFile(null, pathMap.get(log));

        File[] srcFiles = { new File(pathMap.get(txt)),
                new File(pathMap.get(log)) };
        File zipFile = new File(pathMap.get(zip));

        ZipMultiFileUtil.zipFiles(srcFiles, zipFile);
        return null;

    }

    // 读取excel
    public static Workbook getWorkBook(File file) {
        Workbook wb = null;
        try {
            FileInputStream in = new FileInputStream(file);
            if (file.getName().endsWith(excel_xls)) { // Excel&nbsp;2003
                wb = new HSSFWorkbook(in);
            } else if (file.getName().endsWith(excel_xlsx)) { // Excel 2007/2010
                wb = new XSSFWorkbook(in);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    @SuppressWarnings("deprecation")
    public static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: {
                DecimalFormat df = new DecimalFormat("0");
                cellValue = df.format(cell.getNumericCellValue());
                break;
            }
            case Cell.CELL_TYPE_FORMULA: {
                // 判断cell是否为日期格式
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 转换为日期格式YYYY-mm-dd
                    cellValue = cell.getDateCellValue();
                } else {
                    // 数字
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            case Cell.CELL_TYPE_STRING: {
                cellValue = cell.getRichStringCellValue().getString();
                break;
            }
            default:
                cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }

    /**
     * 写入TXT文件
     */
    public static void writeFile(List<String> list, String filePath) {
        try {
            File writeName = new File(filePath); // 相对路径，如果没有则要建立一个新的output.txt文件
            writeName.createNewFile(); // 创建新文件,有同名的文件的话直接覆盖
            try {
                FileWriter writer = new FileWriter(writeName);
                BufferedWriter out = new BufferedWriter(writer);
                for (String md : list) {
                    out.write(md + "\r\n");
                }
                out.flush(); // 把缓存区内容压入文件
            } catch (Exception e) {
                // TODO: handle exception
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

 

3.压缩文件代码如下：

package com.xxxx.cpos.modules.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * @ClassName ZipMultiFileUtil
 * @Description
 * @Author zhangs
 * @Date 2019年3月5日 下午5:58:27
 */
public class ZipMultiFileUtil {

    public static void main(String[] args) {
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");// 设置日期格式
        String dateStr = df.format(new Date());// new Date()为获取当前系统时间
        File[] srcFiles = {
                new File("D:\\MD\\CPOS_MD_D_3100_20190305171508.TXT.txt"),
                new File("D:\\MD\\CPOS_MD_D_3100_20190305171508.log.txt") };
        File zipFile = new File("D:\\MD\\CPOS_MD_D_3100_" + dateStr + ".zip");
        // 调用压缩方法
        zipFiles(srcFiles, zipFile);
    }

    public static void zipFiles(File[] srcFiles, File zipFile) {
        // 判断压缩后的文件存在不，不存在则创建
        if (!zipFile.exists()) {
            try {
                zipFile.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        // 创建 FileOutputStream 对象
        FileOutputStream fileOutputStream = null;
        // 创建 ZipOutputStream
        ZipOutputStream zipOutputStream = null;
        // 创建 FileInputStream 对象
        FileInputStream fileInputStream = null;

        try {
            // 实例化 FileOutputStream 对象
            fileOutputStream = new FileOutputStream(zipFile);
            // 实例化 ZipOutputStream 对象
            zipOutputStream = new ZipOutputStream(fileOutputStream);
            // 创建 ZipEntry 对象
            ZipEntry zipEntry = null;
            // 遍历源文件数组
            for (int i = 0; i < srcFiles.length; i++) {
                // 将源文件数组中的当前文件读入 FileInputStream 流中
                fileInputStream = new FileInputStream(srcFiles[i]);
                // 实例化 ZipEntry 对象，源文件数组中的当前文件
                zipEntry = new ZipEntry(srcFiles[i].getName());
                zipOutputStream.putNextEntry(zipEntry);
                // 该变量记录每次真正读的字节个数
                int len;
                // 定义每次读取的字节数组
                byte[] buffer = new byte[1024];
                while ((len = fileInputStream.read(buffer)) > 0) {
                    zipOutputStream.write(buffer, 0, len);
                }
            }
            zipOutputStream.closeEntry();
            zipOutputStream.close();
            fileInputStream.close();
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}