package com.example11.demoly.excel; import org.apache.poi.common.usermodel.HyperlinkType; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFHyperlink; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; public class Dealexcel { public static void main(String[] args) throws IOException { try (FileInputStream fis = new FileInputStream("C:\\example\\Desktop\\3333.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(fis)) { CreationHelper creationHelper = workbook.getCreationHelper(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet sheet = workbook.getSheetAt(i); int nameColIndex = -1; // Find the "name" column index Row headerRow = sheet.getRow(0); if (headerRow != null) { for (Cell cell : headerRow) { if ("name".equalsIgnoreCase(cell.getStringCellValue())) { nameColIndex = cell.getColumnIndex(); break; } } } if (nameColIndex != -1) { // Create style for blue font XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle(); Font font = workbook.createFont(); font.setColor(IndexedColors.BLUE.getIndex()); style.setFont(font); for (int j = 1; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); if (row != null) { Cell nameCell = row.getCell(nameColIndex); if (nameCell != null) { String nameValue = nameCell.getStringCellValue(); // Create hyperlink XSSFHyperlink hyperlink = (XSSFHyperlink) creationHelper.createHyperlink(HyperlinkType.URL); hyperlink.setAddress("http://example.com/" + nameValue); // Apply hyperlink and style nameCell.setHyperlink(hyperlink); nameCell.setCellStyle(style); } } } } } // Save the changes try (FileOutputStream fos = new FileOutputStream("C:\\example\\Desktop\\updated_example.xlsx")) { workbook.write(fos); } } } }
使用 Apache POI 库来实现
- 加载 Excel 文件:使用
XSSFWorkbook
或HSSFWorkbook
加载文件。 - 遍历所有 Sheets:通过
getSheetAt()
方法遍历每个 sheet。 - 查找 "name" 列:遍历每一行,找到包含 "name" 的列。
- 设置超链接和样式:使用
CreationHelper.createHyperlink()
创建超链接,然后设置单元格样式为蓝色并应用超链接。 - 遍历所有的 sheets,查找名为 "name" 的列,并在该列的单元格中添加超链接和蓝色字体样式