Excel 的超鏈接大小限制為 255?，F在我正在使用 Apache POI 以編程方式填寫 excel，但使用的 s3 預簽名 url 遠遠超過 255 個字符，長度超過 1350。當我單擊在 excel 中創建的超鏈接時，它顯示如下警報：“發生意外錯誤?！边@是我對應的代碼：import org.apache.logging.log4j.LogManager;import org.apache.logging.log4j.Logger;import org.apache.poi.common.usermodel.HyperlinkType;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.CreationHelper;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFHyperlink;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook; public void generateExcel(List<FunctionalTestCaseResult> data) { XSSFWorkbook workbook = new XSSFWorkbook(); CreationHelper createHelper = workbook.getCreationHelper(); XSSFSheet sheet = workbook.createSheet("Sheet1"); int rowNum = 0; Row row = sheet.createRow(rowNum++); int cellNum = 0; CellStyle captionStyle = workbook.createCellStyle(); captionStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); captionStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //create hyper link style XSSFCellStyle hlinkstyle = workbook.createCellStyle(); XSSFFont hlinkfont = workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(IndexedColors.BLUE.index); hlinkstyle.setFont(hlinkfont); Cell cell = row.createCell(cellNum++); XSSFHyperlink link = (XSSFHyperlink)createHelper.createHyperlink(HyperlinkType.URL); link.setAddress(recordingS3Url); cell.setHyperlink(link); cell.setCellValue("Recording url"); cell.setCellStyle(hlinkstyle); }
1 回答
陪伴而非守候
TA貢獻1757條經驗 獲得超8個贊
您提到的限制是關于=HYPERLINK公式中的函數。Excel公式的長度不能超過 255 個字符。
默認單元格超鏈接的 URL 長度沒有限制。以下代碼創建一個單元格，該單元格具有指向長度為 1554 個字符的 URL 的超鏈接。
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.common.usermodel.HyperlinkType;
class CreateExcelHyperlinkLongURL {
public static void main(String[] args) throws Exception {
String url = "https://www.google.de/search?&q=Lorem+ipsum+dolor+sit+amet%2C+consetetur+sadipscing+elitr%2C+sed+diam+nonumy+eirmod+tempor+invidunt+ut+labore+et+dolore+magna+aliquyam+erat%2C+sed+diam+voluptua.+At+vero+eos+et+accusam+et+justo+duo+dolores+et+ea+rebum.+Stet+clita+kasd+gubergren%2C+no+sea+takimata+sanctus+est+Lorem+ipsum+dolor+sit+amet.+Lorem+ipsum+dolor+sit+amet%2C+consetetur+sadipscing+elitr%2C+sed+diam+nonumy+eirmod+tempor+invidunt+ut+labore+et+dolore+magna+aliquyam+erat%2C+sed+diam+voluptua.+At+vero+eos+et+accusam+et+justo+duo+dolores+et+ea+rebum.+Stet+clita+kasd+gubergren%2C+no+sea+takimata+sanctus+est+Lorem+ipsum+dolor+sit+amet.&oq=Lorem+ipsum+dolor+sit+amet%2C+consetetur+sadipscing+elitr%2C+sed+diam+nonumy+eirmod+tempor+invidunt+ut+labore+et+dolore+magna+aliquyam+erat%2C+sed+diam+voluptua.+At+vero+eos+et+accusam+et+justo+duo+dolores+et+ea+rebum.+Stet+clita+kasd+gubergren%2C+no+sea+takimata+sanctus+est+Lorem+ipsum+dolor+sit+amet.+Lorem+ipsum+dolor+sit+amet%2C+consetetur+sadipscing+elitr%2C+sed+diam+nonumy+eirmod+tempor+invidunt+ut+labore+et+dolore+magna+aliquyam+erat%2C+sed+diam+voluptua.+At+vero+eos+et+accusam+et+justo+duo+dolores+et+ea+rebum.+Stet+clita+kasd+gubergren%2C+no+sea+takimata+sanctus+est+Lorem+ipsum+dolor+sit+amet.+Lorem+ipsum+dolor+sit+amet%2C+consetetur+sadipscing+elitr%2C+sed+diam+nonumy+eirmod+tempor+invidunt+ut+labore+et+dolore+magna+aliquyam+erat%2C+sed+diam+voluptua.+At+vero+eos+et+accusam+et+justo+duo+dolores+et+ea+rebum.+Stet+clita+kasd+gubergren%2C+no+sea+takimata+sanctus+est+Lorem+ipsum+dolor+sit+amet.";
int urlLength = url.length();
System.out.println(urlLength);
try (Workbook workbook = new XSSFWorkbook();
FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {
Sheet sheet = workbook.createSheet();
Hyperlink link = workbook.getCreationHelper().createHyperlink(HyperlinkType.URL);
link.setAddress(url);
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
String cellText = "Open link to long URL having length of " + urlLength + " characters.";
cell.setCellValue(cellText);
cell.setHyperlink(link);
sheet.setColumnWidth(0, cellText.length() * 256);
workbook.write(fileout);
}
}
}
這適用于我使用apache poi 4.1.0and Excel 2016。
如果 URL 太長，則某些Excel版本無法*.xlsx正確打開文件。發生這種情況的確切 URL 長度似乎因 Excel版本而異。
添加回答
舉報
0/150
提交
取消