Excel 的超鏈接大小限制為 255?，F(xiàn)在我正在使用 Apache POI 以編程方式填寫 excel，但使用的 s3 預(yù)簽名 url 遠(yuǎn)遠(yuǎn)超過 255 個(gè)字符，長度超過 1350。當(dāng)我單擊在 excel 中創(chuàng)建的超鏈接時(shí)，它顯示如下警報(bào)：“發(fā)生意外錯(cuò)誤?！边@是我對應(yīng)的代碼：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貢獻(xiàn)1757條經(jīng)驗(yàn) 獲得超8個(gè)贊
您提到的限制是關(guān)于=HYPERLINK公式中的函數(shù)。Excel公式的長度不能超過 255 個(gè)字符。
默認(rèn)單元格超鏈接的 URL 長度沒有限制。以下代碼創(chuàng)建一個(gè)單元格，該單元格具有指向長度為 1554 個(gè)字符的 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正確打開文件。發(fā)生這種情況的確切 URL 長度似乎因 Excel版本而異。
添加回答
舉報(bào)
0/150
提交
取消