根据搜索条件在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 {&nbsp; &nbsp; // Below method will give the excel data based on the passed row and the column number&nbsp; &nbsp; private static String getData(String fileName, String sheetName, int rowNumber, int columnNumber) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; Workbook workbook = WorkbookFactory.create(new File(fileName));&nbsp; &nbsp; &nbsp; &nbsp; Sheet sheet = workbook.getSheet(sheetName);&nbsp; &nbsp; &nbsp; &nbsp; Row row = sheet.getRow(rowNumber);&nbsp; &nbsp; &nbsp; &nbsp; return row.getCell(columnNumber).getStringCellValue().trim();&nbsp; &nbsp; }&nbsp; &nbsp; // Below method will return the row count&nbsp; &nbsp; private static int getRowCount(String fileName, String sheetName) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; return WorkbookFactory.create(new File(fileName)).getSheet(sheetName).getLastRowNum() + 1;&nbsp; &nbsp; }&nbsp; &nbsp; // Below method will store the data in excel sheet based on the passed row and column indexes&nbsp; &nbsp; private static void putData(String fileName, String sheetName, int rowNumber, int columnNumber, String cellValue) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; Workbook workbook = WorkbookFactory.create(new File(fileName));&nbsp; &nbsp; &nbsp; &nbsp; Sheet sheet = workbook.createSheet(sheetName);&nbsp; &nbsp; &nbsp; &nbsp; Row row = sheet.createRow(rowNumber);&nbsp; &nbsp; &nbsp; &nbsp; row.createCell(columnNumber).setCellValue(cellValue);&nbsp; &nbsp; }&nbsp; &nbsp; public static void main(String ...ali) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; // Retrieve data from the Database using some queries&nbsp; &nbsp; &nbsp; &nbsp; // Store the retrieved data into some excel sheet&nbsp; &nbsp; &nbsp; &nbsp; // After doing the above two steps, below code will retrieve previously stored emp id's and will store into an other excel sheet&nbsp; &nbsp; &nbsp; &nbsp; // Pass the corresponding absolute excel file path with name, sheet names in the below sample code&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; for(int i=0;i<getRowCount("SomeExcelFileName", "SomeExcelSheetName");i++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Get one by one emp id from excel&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String empID = getData("SomeExcelFileName", "SomeExcelSheetName", i, 0);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Search in the application and get invoice numbers list and store it&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List<String> invoiceDetails = null;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Store the invoice details list in the first column, here the row number is starting from 1 and column index is 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; putData("AntoherExcelFile", "AnotherExcelSheet", (i+1), 0, invoiceDetails.toString());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Store the related emp id in the second column, here the row number is starting from 1 and column index is 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; putData("AntoherExcelFile", "AnotherExcelSheet", (i+1), 1, empID);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }}如果程序成功执行且没有任何错误，那么您将在 excel 中获得以下示例格式的数据：|Invoice Details | Emp ID|| details 1&nbsp; &nbsp; &nbsp; | 3333&nbsp; || some details&nbsp; &nbsp;| 1306&nbsp; || Hello World!&nbsp; &nbsp;| 2019&nbsp; |我希望它有帮助...

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 {&nbsp; &nbsp; private WritableCellFormat timesBoldUnderline;&nbsp; &nbsp; private WritableCellFormat times;&nbsp; &nbsp; private WritableWorkbook workbook;&nbsp; &nbsp; private WritableSheet excelSheet;&nbsp; &nbsp; private String inputFile;&nbsp; &nbsp; private List<String> empIDs, invoiceDetails;&nbsp; &nbsp; public void setOutputFile(String inputFile) {&nbsp; &nbsp; &nbsp; &nbsp; this.inputFile = inputFile;&nbsp; &nbsp; }&nbsp; &nbsp; public void write(int SheetNumber) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; File file = new File(inputFile);&nbsp; &nbsp; &nbsp; &nbsp; WorkbookSettings wbSettings = new WorkbookSettings();&nbsp; &nbsp; &nbsp; &nbsp; wbSettings.setLocale(new Locale("en", "EN"));&nbsp; &nbsp; &nbsp; &nbsp; workbook = Workbook.createWorkbook(file, wbSettings);&nbsp; &nbsp; &nbsp; &nbsp; workbook.createSheet("Required", SheetNumber);&nbsp; &nbsp; &nbsp; &nbsp; excelSheet = workbook.getSheet(SheetNumber);&nbsp; &nbsp; &nbsp; &nbsp; createLabel(excelSheet);&nbsp; &nbsp; }&nbsp; &nbsp; private void createLabel(WritableSheet sheet) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; // Lets create a times font&nbsp; &nbsp; &nbsp; &nbsp; WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);&nbsp; &nbsp; &nbsp; &nbsp; // Define the cell format&nbsp; &nbsp; &nbsp; &nbsp; times = new WritableCellFormat(times10pt);&nbsp; &nbsp; &nbsp; &nbsp; // Lets automatically wrap the cells&nbsp; &nbsp; &nbsp; &nbsp; times.setWrap(true);&nbsp; &nbsp; &nbsp; &nbsp; // create create a bold font with unterlines&nbsp; &nbsp; &nbsp; &nbsp; WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false, UnderlineStyle.SINGLE);&nbsp; &nbsp; &nbsp; &nbsp; timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);&nbsp; &nbsp; &nbsp; &nbsp; // Lets automatically wrap the cells&nbsp; &nbsp; &nbsp; &nbsp; timesBoldUnderline.setWrap(true);&nbsp; &nbsp; &nbsp; &nbsp; CellView cv = new CellView();&nbsp; &nbsp; &nbsp; &nbsp; cv.setFormat(times);&nbsp; &nbsp; &nbsp; &nbsp; cv.setFormat(timesBoldUnderline);&nbsp; &nbsp; &nbsp; &nbsp; cv.setAutosize(true);&nbsp; &nbsp; &nbsp; &nbsp; // Write a few headers&nbsp; &nbsp; &nbsp; &nbsp; addCaption(sheet, 0, 0, "Invoice Details");&nbsp; &nbsp; &nbsp; &nbsp; addCaption(sheet, 1, 0, "Emp ID");&nbsp; &nbsp; }&nbsp; &nbsp; private void retrieveDataFromDBAndStoreItInExcel(WritableSheet sheet) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; // Handling data base part&nbsp; &nbsp; &nbsp; &nbsp; Connection con = null;&nbsp; &nbsp; &nbsp; &nbsp; Class.forName("driver name").newInstance();&nbsp; &nbsp; &nbsp; &nbsp; con = DriverManager.getConnection("URL", "UN", "PWD");&nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Connection Created");&nbsp; &nbsp; &nbsp; &nbsp; ResultSet rs = null;&nbsp; &nbsp; &nbsp; &nbsp; try{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Statement stmt = con.createStatement();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Statement Created");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String query = "query to get data from db";&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rs = stmt.executeQuery(query);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Query executed");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ResultSetMetaData metadata = rs.getMetaData();&nbsp; &nbsp; &nbsp; &nbsp; }catch (Exception e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; System.out.println("Error.......: "+e);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; // Storing the database data into the excel&nbsp; &nbsp; &nbsp; &nbsp; for(int i=0; rs.next();i++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // First column&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; addLabel(sheet, 0, i, rs.getString(1));&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; rs.close();&nbsp; &nbsp; }&nbsp; &nbsp; private void createContent(List<String> list, WritableSheet sheet, int columnNumber) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; for(int i=0; i<list.size();i++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // First column&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; addLabel(sheet, columnNumber, i, list.get(i).toString());&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; private void addCaption(WritableSheet sheet, int column, int row, String s) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; Label label;&nbsp; &nbsp; &nbsp; &nbsp; label = new Label(column, row, s, timesBoldUnderline);&nbsp; &nbsp; &nbsp; &nbsp; sheet.addCell(label);&nbsp; &nbsp; }&nbsp; &nbsp; private void addLabel(WritableSheet sheet, int column, int row, String s) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; Label label;&nbsp; &nbsp; &nbsp; &nbsp; label = new Label(column, row, s, times);&nbsp; &nbsp; &nbsp; &nbsp; sheet.addCell(label);&nbsp; &nbsp; }&nbsp; &nbsp; public void read() throws Exception&nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; File inputWorkbook = new File(inputFile);&nbsp; &nbsp; &nbsp; &nbsp; empIDs = new ArrayList<String>();&nbsp; &nbsp; &nbsp; &nbsp; Workbook w;&nbsp; &nbsp; &nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; w = Workbook.getWorkbook(inputWorkbook);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Get the first sheet&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sheet sheet = w.getSheet(0);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Loop over first column up to 10 rows&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for(int i=0;i<sheet.getRows();i++) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Cell cell = sheet.getCell(0, i);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CellType type = cell.getType();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if (type == CellType.LABEL) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; empIDs.add(cell.getContents());&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; } catch (BiffException e) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; e.printStackTrace();&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; public static void main(String[] args) throws Exception {&nbsp; &nbsp; &nbsp; &nbsp; OneMore test = new OneMore();&nbsp; &nbsp; &nbsp; &nbsp; // Retrieving Data from the Database and storing in the First Excel&nbsp; &nbsp; &nbsp; &nbsp; test.setOutputFile("C:\\NotBackedUp\\OxygenWorkspace\\HelloSelenium\\src\\main\\resources\\test\\FirstExcel.xls");&nbsp; &nbsp; &nbsp; &nbsp; test.write(0);&nbsp; &nbsp; &nbsp; &nbsp; test.retrieveDataFromDBAndStoreItInExcel(test.excelSheet);&nbsp; &nbsp; &nbsp; &nbsp; test.workbook.write();&nbsp; &nbsp; &nbsp; &nbsp; test.workbook.close();&nbsp; &nbsp; &nbsp; &nbsp; System.out.println("=> The First Excel Writing task completed...");&nbsp; &nbsp; &nbsp; &nbsp; // Reading data from the First Excel and storing it in empIDs ArrayList&nbsp; &nbsp; &nbsp; &nbsp; test.read();&nbsp; &nbsp; &nbsp; &nbsp; System.out.println("=> The Excel Data is : "+test.empIDs);&nbsp; &nbsp; &nbsp; &nbsp; // You use empIDs ArrayList which has emp ids for getting the invoice details from the Application and store it in the invoiceDetails ArrayList below&nbsp; &nbsp; &nbsp; &nbsp; test.invoiceDetails = new ArrayList<String>();&nbsp; &nbsp; &nbsp; &nbsp; test.invoiceDetails.add("Invoice Details from the Application");&nbsp; &nbsp; &nbsp; &nbsp; // Writing the Invoice Details and the emp id Data to the Second Excel&nbsp; &nbsp; &nbsp; &nbsp; test.setOutputFile("C:\\NotBackedUp\\OxygenWorkspace\\HelloSelenium\\src\\main\\resources\\test\\SecondExcel.xls");&nbsp; &nbsp; &nbsp; &nbsp; test.write(0);&nbsp; &nbsp; &nbsp; &nbsp; test.createContent(test.invoiceDetails, test.excelSheet, 0);&nbsp; &nbsp; &nbsp; &nbsp; test.createContent(test.empIDs, test.excelSheet, 1);&nbsp; &nbsp; &nbsp; &nbsp; test.workbook.write();&nbsp; &nbsp; &nbsp; &nbsp; test.workbook.close();&nbsp; &nbsp; }}我第一次尝试使用 jxl，所以我测试了将数据读取和写入系统中的 excel 并按预期工作。但是我还没有测试数据库部分和全部，所以尝试通过在 main() 方法中的注释来修改/执行。您不需要编写任何代码，只需要根据您的要求更改一些配置细节和 excel 文件名路径。我希望它有帮助......快乐编码......