Java 互转 Text, CSV, Excel

Excel xlsx 转为 Text

把C:\\test\\test.xlsx 转为 C:\\test\\test.txt, 逗号分隔符来分隔单元格。 excel文件不能有合并的单元格，否则txt文件会为空

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;
import java.math.RoundingMode;
 
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
public class ExcelToTxt {
 
    static Logger logger = LoggerFactory.getLogger(ExcelToTxt.class);
 
    public static void main(String[] args) {
        String excelPath = "C:\\test\\test.xlsx";
        String txtPath = "C:\\test\\test.txt";
        excelToTxt(excelPath, txtPath);
    }
 
    public static void excelToTxt(String excelPath, String txtPath) {
 
        StringBuffer sb = new StringBuffer();
        Workbook workbook = readExcel(excelPath);
        Sheet sheet = workbook.getSheetAt(0);
 
        // 获取最大行数
        int rownum = sheet.getLastRowNum();
        System.out.println(rownum);
        // 获取最大列数
        Row row = sheet.getRow(rownum);
        int colnum = row.getLastCellNum();
        for (int i = 0; i <= rownum; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < colnum; j++) {
                    sb.append(getCellFormatValue(row.getCell(j)) + ",");
                }
            } else {
                break;
            }
            sb.append("\r\n");
        }
 
        WriteToFile(sb.toString(), txtPath);
 
    }
 
    // 读取excel
    private static Workbook readExcel(String filePath) {
        Workbook workbook = null;
        try {
            InputStream is = new FileInputStream(filePath);
 
            if (filePath.contains("xlsx")) {
                workbook = new XSSFWorkbook(is);
            } else {
                workbook = new HSSFWorkbook(is);
            }
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        return workbook;
    }
 
    private static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            // 判断cell类型
            switch (cell.getCellType()) {
            case NUMERIC: {
                Double cellValue1 = cell.getNumericCellValue();
                cellValue = new BigDecimal(cellValue1).setScale(2, RoundingMode.HALF_UP);
                break;
            }
            case STRING: {
                cellValue = cell.getStringCellValue();
                break;
            }
            default:
                cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }
 
    /**
     * 生成文件
     */
    private static void WriteToFile(String str, String filePath) {
        File file = new File(filePath);
        if (file.exists()) {
            file.delete();
        }
        BufferedWriter bw = null;
        try {
            FileOutputStream out = new FileOutputStream(filePath, true);// true,表示:文件追加内容，不重新生成,默认为false
            bw = new BufferedWriter(new OutputStreamWriter(out, "GBK"));
            bw.write(str += "\r\n");// 换行
            bw.flush();
            bw.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
 
    }
}

CSV 转为 Excel

csv 不能有空行，最后一行不能是空行

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;

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.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class CSVUtils {

    private static Logger logger = LoggerFactory.getLogger(CSVUtils.class);
    private static String csvFile = "C:\\test\\test.csv";
    private static String excelFile = "C:\\test\\test.xlsx";

    public static void main(String[] args) {

       // 调用csv2excel
        csv2excel(csvFile, excelFile);
    }

 

    /**
     * 将数据写入到excel中
     */
    public static void csv2excel(String csvFile, String excelFile) {

        // 创建Excel
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("name");
        cell = row.createCell(1);
        cell.setCellValue("age");
        cell = row.createCell(2);
        cell.setCellValue("hobby");

        try {
            // 逐行读取csv文件
            DataInputStream in = new DataInputStream(new FileInputStream(csvFile));
            BufferedReader reader = new BufferedReader(new InputStreamReader(in, "utf-8"));
            reader.readLine();// 第一行信息，为标题信息，不用,如果需要，注释掉
            String line = null;
            for (int i = 0; (line = reader.readLine()) != null; i++) {

                // CSV格式文件为逗号分隔符文件，这里根据逗号切分
                String item[] = line.split(",");

                Row row1 = sheet.createRow(i + 1);
                row1.createCell(0).setCellValue(item[0]);
                row1.createCell(1).setCellValue(item[1]);
                row1.createCell(2).setCellValue(item[2]);

            }

            // 将文件保存到指定的位置
            FileOutputStream fos = new FileOutputStream(excelFile);
            workbook.write(fos);
            logger.info("csv 2 excel successfully");
            fos.close();
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }

    }
}