java - 使用 Java 从现有 Excel 文件读取单元格并将其写入新的 Excel 文件

Question

因此，我目前正在创建一个程序，使用 JXL Java 库读取、编辑现有 Excel 文件中的数据并将其放入新的 Excel 文件中。在此阶段，我只想从现有 Excel 文件中读取数据并将其放入新文件中。似乎找不到一个好的方法来做到这一点，对于 future 的工作来说，编辑数据(具有某些列)并将其放入新的 Excel 文件中的最佳方法是什么。

抱歉，如果有任何不清楚的地方，只是掌握 Java 和 Stackoverflow。

import java.io.*;
import jxl.*;
import jxl.write.Number;
import jxl.write.*;

class Extraction {

private String inputFile;

String data;

public void setInputFile(String inputFile){

    this.inputFile = inputFile;

}

public void read() throws IOException {

    File spreadsheet = new File(inputFile);

    Workbook w;

    try {

        w = Workbook.getWorkbook(spreadsheet);

        Sheet page = w.getSheet(0);

        File f = new File("C:\\Users\\alex\\Desktop\\New_Export.xls");                                                                                      

            if (!f.exists()){

                String FileName = "C:\\Users\\alex\\Desktop\\New_Export.xls";

                WritableWorkbook excel = Workbook.createWorkbook(new File(FileName));

                WritableSheet sheet = excel.createSheet("Sheet1", 0);                                           

                    for (int y = 0; y < page.getRows(); y++){

                        for (int x = 0; x < page.getColumns(); x++){

                            Cell cell = page.getCell(x ,y +1);
                            CellType type = cell.getType();

                                if(type == CellType.LABEL || type == CellType.NUMBER){

                                    data = cell.getContents();

                                    System.out.println(data);




                                    //To now input the read data into the new Excel File

                                    sheet.addCell();

                                }

                        }

                        System.out.println(" ");

                    }   

                System.out.println("The File Has Successfully Been Created!");

                excel.write();

                excel.close();

                }

                else{

                    System.err.println("File is already created!");

                }

    }
    catch(Exception e){

        System.out.println(" ");  

    }       

}       

public static void main (String args[]) throws IOException{

    Extraction reading = new Extraction();

    reading.setInputFile("C:\\Users\\alex\\Desktop\\export.xls"); 

    reading.read();

}
}

Answer 1

使用apache的“poi”，您可以使用下面的代码读取excel(*.xls)

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

private void readingExcel(String fileName) {
    try (FileInputStream file = new FileInputStream(new File(fileName))) {
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        for (Row row : sheet) {
            Iterator<Cell> cellIterator = row.cellIterator();
            for (Cell cell : row) {
                System.out.println(cell.getStringCellValue());
            }
        }
    } catch (IOException ioException) {
        ioException.printStackTrace();
    }
}

用于写入 Excel

private File writingToExcel() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sample-sheet");
    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("My Sample Value");

    File file = new File(sheet.getSheetName());
    return file;
}

但是，如果要使用*.xlsx，则用来读取的类是

XSSFWorkbook workbook = new XSSFWorkbook (file); 
XSSFSheet sheet = workbook.getSheetAt(0);