java 读txt 写excel、csv文件

读txt写excel

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

package files;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;

/**
 * @author wmy
 * @create 2023/7/31 16:33
 */
public class ExcelDemo {

    public static void main(String argv[]){
        //读取的txt文件路径
        String txtFilePath = "C:\\Users\\wmy\\Downloads\\aaa.txt";
        //生成的excel文件路径
        String excelFilePath = "C:\\Users\\wmy\\Desktop\\aaa.xls";
        //编码格式
        String encoding = "utf-8";
        readAndWrite(txtFilePath,excelFilePath,encoding);
    }

    public static void readAndWrite(String filePath,String excelFilePath,String encoding){
        try{
            File file = new File(filePath);
            File tempFile = new File(excelFilePath);
            //判断文件是否存在
            if (!file.isFile() || !file.exists()){
                System.out.println("找不到指定的文件");
            }
            InputStreamReader read = new InputStreamReader(new FileInputStream(file), encoding);
            BufferedReader bufferedReader = new BufferedReader(read);
            WritableWorkbook workbook = Workbook.createWorkbook(tempFile);



            //设置字体为宋体，11号
            WritableFont headerFont = new WritableFont(WritableFont.createFont("宋体"), 11,
                    WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
            WritableCellFormat headerFormat = new WritableCellFormat (headerFont);

            //一些临时变量，用于写到excel中
            String lineTxt = null;
            int i = 0;
            int sheetNum = 1;
            WritableSheet sheet = workbook.createSheet("Sheet1", 0);
            while ((lineTxt = bufferedReader.readLine()) != null){
                try {
                    String[] list =  lineTxt.split("\t");
                    if(i == 65536){
                        i = 0;
                        sheetNum ++;
                        sheet = workbook.createSheet("Sheet"+sheetNum, 0);
                    }
                    for (int f=0;f<list.length;f++){
                        sheet.addCell(new Label(f, i, list[f], headerFormat));
                    }
                    i++;
                    //判断内容是否为空行，如果是，则转行
                    if("\\r".equals(lineTxt)){
                        continue;
                    }
                } catch (WriteException e) {
                    System.out.println("超出sheet范围数据:"+lineTxt);
                    e.printStackTrace();
                }
            }
            //写入文件
            workbook.write();
            //关闭文件
            workbook.close();
            read.close();
        }catch (Exception e){
            System.out.println("读取文件内容出错");
            e.printStackTrace();
        }
    }

}

读txt写csv

package files;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;

import java.io.*;
import java.util.HashSet;

/**
 * @author wmy
 * @create 2023/9/18 11:37
 */
public class TxtToCsv {
    public static void main(String argv[]){
        //读取的txt文件路径
        String txtFilePath = "C:\\Users\\wmy\\Downloads\\20230831.txt";
        //生成的excel文件路径
        String excelFilePath = "C:\\Users\\wmy\\Desktop\\20230831.csv";
        //编码格式
        String encoding = "utf-8";
        readAndWrite(txtFilePath,excelFilePath,encoding);
    }

    public static void readAndWrite(String filePath,String excelFilePath,String encoding){
        try{
            File file = new File(filePath);
            //判断文件是否存在
            if (!file.isFile() || !file.exists()){
                System.out.println("找不到指定的文件");
            }
            InputStreamReader read = new InputStreamReader(new FileInputStream(file), encoding);
            BufferedReader bufferedReader = new BufferedReader(read);

            BufferedWriter out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(excelFilePath), encoding));

            //一些临时变量，用于写到excel中
            String lineTxt = null;
            while ((lineTxt = bufferedReader.readLine()) != null){
                try {
                    if(lineTxt.contains("贵州")){
                        JSONObject jsonData = JSON.parseObject(lineTxt);
                        String website_name = jsonData.getString("website_name");
                        String snapshot_url = jsonData.getString("snapshot_url");
                        String url = jsonData.getString("url");
                        String data_type = jsonData.getString("data_type");
                        String type = "";
                        switch (data_type){
                            case "ad_search":
                                type = "搜索";
                                break;
                            case "ad_dsp":
                                type = "展示";
                                break;
                            case "wechat":
                                type = "微信公众号";
                                break;
                            case "ad_wechat":
                                type = "微信公众号";
                                break;
                            case "app":
                                type = "移动";
                                break;
                            case "ad_app":
                                type = "移动";
                                break;
                            default:
                                type = data_type;
                                break;
                        }
                        out.write(website_name);
                        out.write(",");
                        out.write("贵州");
                        out.write(",");
                        out.write(snapshot_url);
                        out.write(",");
                        out.write(url);
                        out.write(",");
                        out.write(type);
                        out.newLine();
                    }
                } catch (Exception e) {
                    System.out.println("写入异常:"+lineTxt);
                    e.printStackTrace();
                }
            }
            out.flush();
            out.close();
            read.close();
        }catch (Exception e){
            System.out.println("读取文件内容出错");
            e.printStackTrace();
        }
    }
}

txt大文件转excel

package files;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

/**
 * @create 2023/9/21 11:03
 * 输出大文件excel  输出多个excel
 */
public class TxtToExcelBigFile {
    public static void main(String[] args) {
        //读取的txt文件路径
        String txtFilePath = "C:\\Users\\wumengyang\\Desktop\\xcx.txt";
        //生成的excel文件路径
        String excelFilePath = "E:\\tmp\\test\\xcx";
        //编码格式
        String encoding = "utf-8";

        readAndWrite(txtFilePath,excelFilePath, encoding);

    }

    public static void readAndWrite(String filePath,String excelFilePath, String encoding){
        try{
            File file = new File(filePath);
            //判断文件是否存在
            if (!file.isFile() || !file.exists()){
                System.out.println("找不到指定的文件");
            }
            InputStreamReader read = new InputStreamReader(new FileInputStream(file), encoding);
            BufferedReader bufferedReader = new BufferedReader(read);

            File tempFile = new File(excelFilePath+".xls");
            WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
            WritableSheet sheet = workbook.createSheet("Sheet1", 0);
            //设置字体为宋体，11号
            WritableFont headerFont = new WritableFont(WritableFont.createFont("宋体"), 11,
                    WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
            WritableCellFormat headerFormat = new WritableCellFormat (headerFont);
            //一些临时变量，用于写到excel中
            int rowNum = 0;
            int sheetRowNum = 0;
            int fileNum = 0;
            int sheetNum = 1;
            String lineTxt = null;
            while ((lineTxt = bufferedReader.readLine()) != null){
                if(rowNum == 20000){
                    //写入文件
                    workbook.write();
                    //关闭文件
                    workbook.close();

                    rowNum = 0;
                    fileNum ++;
                    sheetNum = 1;
                    tempFile = new File(excelFilePath+fileNum+".xls");
                    workbook = Workbook.createWorkbook(tempFile);
                    sheet = workbook.createSheet("Sheet1", 0);
                }

                String[] list =  lineTxt.split("\t");
//                if(sheetRowNum == 65536){
//                    sheetRowNum = 0;
//                    sheetNum ++;
//                    sheet = workbook.createSheet("Sheet"+sheetNum, 0);
//                }
                for (int f=0;f<list.length;f++){
                    sheet.addCell(new Label(f, rowNum, list[f], headerFormat));
                }
//                sheetRowNum ++;
                rowNum ++;
                //判断内容是否为空行，如果是，则转行
                if("\\r".equals(lineTxt)){
                    continue;
                }
            }
            //写入文件
            workbook.write();
            //关闭文件
            workbook.close();
            read.close();
        }catch (Exception e){
            System.out.println("读取文件内容出错");
            e.printStackTrace();
        }
    }


}

切分大csv文件

package files;

/**
 * 可能会失败
 * @create 2023/11/3 17:21
 */
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.csv.CSVRecord;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class CSVSplitter {
    public static void main(String[] args) {
        // 输入大CSV文件和输出目录
        String inputFilePath = "C:\\Users\\wumengyang\\Desktop\\未命名文件夹\\未命名文件夹\\xac.csv";
        String outputDirectory = "E:\\tmp\\test3\\xac";

        // 行数限制
        int maxRowsPerFile = 50000;

        try {
            // 读取大CSV文件
            Reader reader = new FileReader(inputFilePath);
            CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT);
            List<CSVRecord> records = csvParser.getRecords();

            // 切分为多个小CSV文件
            List<CSVRecord> currentBatch = new ArrayList<>();
            int fileCount = 1;

            for (CSVRecord record : records) {
                currentBatch.add(record);

                if (currentBatch.size() >= maxRowsPerFile) {
                    // 写入一个小CSV文件
                    String outputFileName = outputDirectory + "output_" + fileCount + ".csv";
                    writeCSV(outputFileName, currentBatch);
                    currentBatch.clear();
                    fileCount++;
                }
            }

            // 处理剩余的记录
            if (!currentBatch.isEmpty()) {
                String outputFileName = outputDirectory + "output_" + fileCount + ".csv";
                writeCSV(outputFileName, currentBatch);
            }

            // 关闭资源
            csvParser.close();
            reader.close();

            System.out.println("CSV文件切分完成。");
        } catch (IOException e) {
            e.printStackTrace(); // 打印异常信息
            // 可以添加适当的处理逻辑，然后继续执行
            // 例如，记录异常并继续切分，或者跳过包含错误的行
        }

    }

    private static void writeCSV(String outputFileName, List<CSVRecord> records) throws IOException {
        FileWriter writer = new FileWriter(outputFileName);
        CSVFormat csvFormat = CSVFormat.DEFAULT;

        try (CSVPrinter csvPrinter = new CSVPrinter(writer, csvFormat)) {
            for (CSVRecord record : records) {
                csvPrinter.printRecord(record);
            }
        }
    }
}