根据搜索条件在excel中存储多个值

Question

我通过查询从数据库中获取 emp id，并将 emp id 存储在 excel 表中。例如，将 10 个 emp id 存储在名为 empid 详细信息的 excel 中。从excel中获取一个emp id并在应用程序中搜索后，我们得到员工发票编号列表，需要将这些发票编号列表存储在第一列中，并将相关emp id存储在另一个名为invoice details.only的excel中的第二列中我们需要存储在 excel 中的 10 个发票号码列表。我已经完成了用于存储单个 emp Id 发票详细信息的编码部分。任何人都可以帮助我如何实现 n Num of emp Id 的代码并使用 emp id 存储他们的发票。

Answer 1

根据您的要求替换 Excel 文件名、工作表名称、行号、列号和存储值，并按照注释进行解释。

试试下面的代码：

import java.io.File;

import java.util.List;

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.ss.usermodel.WorkbookFactory;

public class Answer {

    // Below method will give the excel data based on the passed row and the column number

    private static String getData(String fileName, String sheetName, int rowNumber, int columnNumber) throws Exception {

        Workbook workbook = WorkbookFactory.create(new File(fileName));

        Sheet sheet = workbook.getSheet(sheetName);

        Row row = sheet.getRow(rowNumber);

        return row.getCell(columnNumber).getStringCellValue().trim();

    }

    // Below method will return the row count

    private static int getRowCount(String fileName, String sheetName) throws Exception {

        return WorkbookFactory.create(new File(fileName)).getSheet(sheetName).getLastRowNum() + 1;

    }

    // Below method will store the data in excel sheet based on the passed row and column indexes

    private static void putData(String fileName, String sheetName, int rowNumber, int columnNumber, String cellValue) throws Exception {

        Workbook workbook = WorkbookFactory.create(new File(fileName));

        Sheet sheet = workbook.createSheet(sheetName);

        Row row = sheet.createRow(rowNumber);

        row.createCell(columnNumber).setCellValue(cellValue);

    }

    public static void main(String ...ali) throws Exception {

        // Retrieve data from the Database using some queries

        // Store the retrieved data into some excel sheet

        // After doing the above two steps, below code will retrieve previously stored emp id's and will store into an other excel sheet

        // Pass the corresponding absolute excel file path with name, sheet names in the below sample code 

        for(int i=0;i<getRowCount("SomeExcelFileName", "SomeExcelSheetName");i++) {

            // Get one by one emp id from excel

            String empID = getData("SomeExcelFileName", "SomeExcelSheetName", i, 0);

            // Search in the application and get invoice numbers list and store it

            List<String> invoiceDetails = null;

            // Store the invoice details list in the first column, here the row number is starting from 1 and column index is 0

            putData("AntoherExcelFile", "AnotherExcelSheet", (i+1), 0, invoiceDetails.toString());

            // Store the related emp id in the second column, here the row number is starting from 1 and column index is 1

            putData("AntoherExcelFile", "AnotherExcelSheet", (i+1), 1, empID);

        }

    }

}

如果程序成功执行且没有任何错误，那么您将在 excel 中获得以下示例格式的数据：

|Invoice Details | Emp ID|

| details 1      | 3333  |

| some details   | 1306  |

| Hello World!   | 2019  |

我希望它有帮助...

Answer 2

根据您的要求，我将代码从 Apache POI 更改为 Java Excel API，用于从 Excel 中检索和存储数据。

试试下面的代码，如果您遇到任何问题，请告诉我...

import java.io.File;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import java.util.Locale;

import jxl.Cell;

import jxl.CellType;

import jxl.CellView;

import jxl.Sheet;

import jxl.Workbook;

import jxl.WorkbookSettings;

import jxl.format.UnderlineStyle;

import jxl.read.biff.BiffException;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

public class OneMore {

    private WritableCellFormat timesBoldUnderline;

    private WritableCellFormat times;

    private WritableWorkbook workbook;

    private WritableSheet excelSheet;

    private String inputFile;

    private List<String> empIDs, invoiceDetails;

    public void setOutputFile(String inputFile) {

        this.inputFile = inputFile;

    }

    public void write(int SheetNumber) throws Exception {

        File file = new File(inputFile);

        WorkbookSettings wbSettings = new WorkbookSettings();

        wbSettings.setLocale(new Locale("en", "EN"));

        workbook = Workbook.createWorkbook(file, wbSettings);

        workbook.createSheet("Required", SheetNumber);

        excelSheet = workbook.getSheet(SheetNumber);

        createLabel(excelSheet);

    }

    private void createLabel(WritableSheet sheet) throws Exception {

        // Lets create a times font

        WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);

        // Define the cell format

        times = new WritableCellFormat(times10pt);

        // Lets automatically wrap the cells

        times.setWrap(true);

        // create create a bold font with unterlines

        WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false, UnderlineStyle.SINGLE);

        timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);

        // Lets automatically wrap the cells

        timesBoldUnderline.setWrap(true);

        CellView cv = new CellView();

        cv.setFormat(times);

        cv.setFormat(timesBoldUnderline);

        cv.setAutosize(true);

        // Write a few headers

        addCaption(sheet, 0, 0, "Invoice Details");

        addCaption(sheet, 1, 0, "Emp ID");

    }

    private void retrieveDataFromDBAndStoreItInExcel(WritableSheet sheet) throws Exception {

        // Handling data base part

        Connection con = null;

        Class.forName("driver name").newInstance();

        con = DriverManager.getConnection("URL", "UN", "PWD");

        System.out.println("Connection Created");

        ResultSet rs = null;

        try{

            Statement stmt = con.createStatement();

            System.out.println("Statement Created");

            String query = "query to get data from db";

            rs = stmt.executeQuery(query);

            System.out.println("Query executed");

            ResultSetMetaData metadata = rs.getMetaData();

        }catch (Exception e) {

            System.out.println("Error.......: "+e);

        }

        // Storing the database data into the excel

        for(int i=0; rs.next();i++) {

            // First column

            addLabel(sheet, 0, i, rs.getString(1));

        }

        rs.close();

    }

    private void createContent(List<String> list, WritableSheet sheet, int columnNumber) throws Exception {

        for(int i=0; i<list.size();i++) {

            // First column

            addLabel(sheet, columnNumber, i, list.get(i).toString());

        }

    }

    private void addCaption(WritableSheet sheet, int column, int row, String s) throws Exception {

        Label label;

        label = new Label(column, row, s, timesBoldUnderline);

        sheet.addCell(label);

    }

    private void addLabel(WritableSheet sheet, int column, int row, String s) throws Exception {

        Label label;

        label = new Label(column, row, s, times);

        sheet.addCell(label);

    }

    public void read() throws Exception  {

        File inputWorkbook = new File(inputFile);

        empIDs = new ArrayList<String>();

        Workbook w;

        try {

            w = Workbook.getWorkbook(inputWorkbook);

            // Get the first sheet

            Sheet sheet = w.getSheet(0);

            // Loop over first column up to 10 rows

            for(int i=0;i<sheet.getRows();i++) {

                Cell cell = sheet.getCell(0, i);

                CellType type = cell.getType();

                if (type == CellType.LABEL) {

                    empIDs.add(cell.getContents());

                }

            }

        } catch (BiffException e) {

            e.printStackTrace();

        }

    }

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

        OneMore test = new OneMore();

        // Retrieving Data from the Database and storing in the First Excel

        test.setOutputFile("C:\\NotBackedUp\\OxygenWorkspace\\HelloSelenium\\src\\main\\resources\\test\\FirstExcel.xls");

        test.write(0);

        test.retrieveDataFromDBAndStoreItInExcel(test.excelSheet);

        test.workbook.write();

        test.workbook.close();

        System.out.println("=> The First Excel Writing task completed...");

        // Reading data from the First Excel and storing it in empIDs ArrayList

        test.read();

        System.out.println("=> The Excel Data is : "+test.empIDs);

        // You use empIDs ArrayList which has emp ids for getting the invoice details from the Application and store it in the invoiceDetails ArrayList below

        test.invoiceDetails = new ArrayList<String>();

        test.invoiceDetails.add("Invoice Details from the Application");

        // Writing the Invoice Details and the emp id Data to the Second Excel

        test.setOutputFile("C:\\NotBackedUp\\OxygenWorkspace\\HelloSelenium\\src\\main\\resources\\test\\SecondExcel.xls");

        test.write(0);

        test.createContent(test.invoiceDetails, test.excelSheet, 0);

        test.createContent(test.empIDs, test.excelSheet, 1);

        test.workbook.write();

        test.workbook.close();

    }

}

我第一次尝试使用 jxl，所以我测试了将数据读取和写入系统中的 excel 并按预期工作。

但是我还没有测试数据库部分和全部，所以尝试通过在 main() 方法中的注释来修改/执行。您不需要编写任何代码，只需要根据您的要求更改一些配置细节和 excel 文件名路径。

我希望它有帮助......快乐编码......