如何用Apache Poi读取CSV文件并在Apache Solr中建立索引？

Hey there! This is totally feasible—let me walk you through a step-by-step solution that combines Apache POI for CSV reading and SolrJ (the official Java client for Solr) to index the data. I’ve built similar pipelines before, so here’s a practical breakdown with code to get you up and running.

First, add these Maven dependencies to your project to pull in Apache POI and SolrJ:

<!-- Apache POI for CSV handling -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.5</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version>
</dependency>

<!-- SolrJ for Solr integration -->
<dependency>
    <groupId>org.apache.solr</groupId>
    <artifactId>solr-solrj</artifactId>
    <version>9.4.0</version>
</dependency>

While POI is primarily built for Excel files, it can auto-detect and process CSVs via WorkbookFactory. Here’s how to read your CSV and map rows to key-value pairs (matching headers to data):

import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

public class CsvPoiReader {
    public static Map<String, String> readCsvRow(Row dataRow, Row headerRow) {
        Map<String, String> rowData = new HashMap<>();
        for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) {
            Cell headerCell = headerRow.getCell(i);
            Cell dataCell = dataRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            
            String header = headerCell.getStringCellValue().trim();
            String value = getCellValue(dataCell);
            
            rowData.put(header, value);
        }
        return rowData;
    }

    private static String getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            default:
                return "";
        }
    }

    public static void processCsvFile(String filePath) throws IOException {
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = WorkbookFactory.create(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            Row headerRow = sheet.getRow(0);
            
            // Skip header row, process data rows
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row dataRow = sheet.getRow(rowNum);
                if (dataRow == null) continue;
                
                Map<String, String> rowData = readCsvRow(dataRow, headerRow);
                // Pass row data to Solr indexer
                try {
                    SolrIndexer.indexDocument(rowData);
                } catch (Exception e) {
                    System.err.println("Failed to index row " + rowNum + ": " + e.getMessage());
                }
            }
        }
    }
}

Next, create a helper class to send the CSV data to your Solr core. Make sure your Solr core’s schema has fields matching your CSV headers (e.g., if your CSV has a product_name column, your Solr schema should have a corresponding field).

import org.apache.solr.client.solrj.SolrClient;
import org.apache.solr.client.solrj.SolrServerException;
import org.apache.solr.client.solrj.impl.HttpSolrClient;
import org.apache.solr.common.SolrInputDocument;
import java.io.IOException;
import java.util.Map;

public class SolrIndexer {
    // Replace with your Solr core URL
    private static final String SOLR_CORE_URL = "http://localhost:8983/solr/your_core_name";

    public static void indexDocument(Map<String, String> rowData) throws SolrServerException, IOException {
        // Auto-close the client with try-with-resources
        try (SolrClient solrClient = new HttpSolrClient.Builder(SOLR_CORE_URL).build()) {
            SolrInputDocument doc = new SolrInputDocument();
            
            // Map CSV columns to Solr fields
            for (Map.Entry<String, String> entry : rowData.entrySet()) {
                String fieldName = entry.getKey();
                String fieldValue = entry.getValue();
                
                // Adjust type handling if needed (e.g., parse numbers/dates explicitly)
                doc.addField(fieldName, fieldValue);
            }
            
            // Add document to Solr
            solrClient.add(doc);
            // Commit to make changes visible (use batch commits for large files!)
            solrClient.commit();
        }
    }
}

If you’re working with a big CSV file, batch processing is way more efficient than committing after every row. Modify the processing code to collect documents and commit in batches (e.g., every 1000 rows):

// Inside processCsvFile()
List<SolrInputDocument> docs = new ArrayList<>();
int batchSize = 1000;

for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
    Row dataRow = sheet.getRow(rowNum);
    if (dataRow == null) continue;
    
    Map<String, String> rowData = readCsvRow(dataRow, headerRow);
    SolrInputDocument doc = new SolrInputDocument();
    rowData.forEach(doc::addField);
    docs.add(doc);
    
    if (docs.size() >= batchSize) {
        try (SolrClient solrClient = new HttpSolrClient.Builder(SOLR_CORE_URL).build()) {
            solrClient.add(docs);
            solrClient.commit();
            docs.clear();
        }
    }
}

// Commit any remaining documents
if (!docs.isEmpty()) {
    try (SolrClient solrClient = new HttpSolrClient.Builder(SOLR_CORE_URL).build()) {
        solrClient.add(docs);
        solrClient.commit();
    }
}

• Schema Alignment: Double-check that your Solr core’s schema has fields matching your CSV headers (correct names and data types). For example, a numeric price column in CSV should map to a float or int field in Solr.
• Error Handling: Add more robust checks for missing fields, invalid data types, or Solr connection issues to avoid crashes mid-processing.
• Alternative CSV Reader: If you find POI overkill for CSV, use Apache Commons CSV instead—it’s lighter and purpose-built for CSV files. The Solr integration logic stays almost identical; you just swap the CSV reading part.
• Solr Validation: Use the Solr Admin UI to verify that documents are being indexed correctly after running your code.

内容的提问来源于stack exchange，提问作者Mustafa Berkan Demir