Java tutorial
/************************************************************************* * * Copyright 2009 by bBreak Systems. * * ExCella Core - ExcelJava????? * * $Id: PoiUtil.java 162 2014-08-11 10:10:29Z kamisono_bb $ * $Revision: 162 $ * * This file is part of ExCella Core. * * ExCella Core is free software: you can redistribute it and/or modify * it under the terms of the GNU Lesser General Public License version 3 * only, as published by the Free Software Foundation. * * ExCella Core is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Lesser General Public License version 3 for more details * (a copy is included in the COPYING.LESSER file that accompanied this code). * * You should have received a copy of the GNU Lesser General Public License * version 3 along with ExCella Core. If not, see * <http://www.gnu.org/licenses/lgpl-3.0-standalone.html> * for a copy of the LGPLv3 License. * ************************************************************************/ package org.bbreak.excella.core.util; import java.io.FileOutputStream; import java.io.IOException; import java.math.BigDecimal; import java.util.ArrayList; import java.util.Collections; import java.util.Date; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Set; import java.util.SortedSet; import java.util.TreeSet; import java.util.regex.Pattern; import org.apache.poi.common.usermodel.HyperlinkType; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFHyperlink; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.SpreadsheetVersion; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Hyperlink; 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.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFHyperlink; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.helpers.ColumnHelper; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; /** * POI? * * @since 1.0 */ public final class PoiUtil { /** * */ private PoiUtil() { } /** ?? */ public static final String TMP_SHEET_NAME = "-@%delete%_tmpSheet"; /** * ??? ??????<br> * <br> * ?[CELL_TYPE_ERROR]??<br> * xls? ?HSSFErrorConstants?<br> * xlsx? Excel??ex.#DIV/0!?#N/A?#REF! * * @param cell * @return */ public static Object getCellValue(Cell cell) { Object value = null; if (cell != null) { switch (cell.getCellTypeEnum()) { case BLANK: break; case BOOLEAN: value = cell.getBooleanCellValue(); break; case ERROR: value = cell.getErrorCellValue(); break; case NUMERIC: // ?? if (isCellDateFormatted(cell)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } break; case STRING: value = cell.getStringCellValue(); break; case FORMULA: FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper() .createFormulaEvaluator(); // ? CellValue cellValue = evaluator.evaluate(cell); CellType cellType = cellValue.getCellTypeEnum(); // ???? switch (cellType) { case BLANK: break; case BOOLEAN: value = cell.getBooleanCellValue(); break; case ERROR: if (cell instanceof XSSFCell) { // XSSF?????? XSSFCell xssfCell = (XSSFCell) cell; CTCell ctCell = xssfCell.getCTCell(); value = ctCell.getV(); } else if (cell instanceof HSSFCell) { // HSSF?????? value = cell.getErrorCellValue(); } break; case NUMERIC: // ?? if (isCellDateFormatted(cell)) { value = cell.getDateCellValue(); } else { value = cell.getNumericCellValue(); } break; case STRING: value = cell.getStringCellValue(); break; default: break; } default: break; } } return value; } /** * DateUtil?Localize??(,,?)????????? * ?""???????? * DateUtil???????? * Bug 47071???? * * @param cell */ public static boolean isCellDateFormatted(Cell cell) { if (cell == null) { return false; } boolean bDate = false; double d = cell.getNumericCellValue(); if (DateUtil.isValidExcelDate(d)) { CellStyle style = cell.getCellStyle(); if (style == null) { return false; } int i = style.getDataFormat(); String fs = style.getDataFormatString(); if (fs != null) { // And '"any"' into '' while (fs.contains("\"")) { int beginIdx = fs.indexOf("\""); if (beginIdx == -1) { break; } int endIdx = fs.indexOf("\"", beginIdx + 1); if (endIdx == -1) { break; } fs = fs.replaceFirst(Pattern.quote(fs.substring(beginIdx, endIdx + 1)), ""); } } bDate = DateUtil.isADateFormat(i, fs); } return bDate; } /** * double??Date??? * * @param excelDate double? * @return Date? */ public static Date getJavaDate(double excelDate) { return DateUtil.getJavaDate(excelDate); } /** * ????? * * @param sheet * @param rowIndex * @param columnIndex * @return ??? */ public static Object getCellValue(Sheet sheet, int rowIndex, int columnIndex) { Object value = null; Row row = sheet.getRow(rowIndex); if (row != null) { Cell cell = row.getCell(columnIndex); if (cell != null) { value = getCellValue(cell); } } return value; } /** * ????????????? * * @param cell ? * @param propertyClass ??Java? * @return ??? */ public static Object getCellValue(Cell cell, Class<?> propertyClass) { if (cell.getCellTypeEnum() == CellType.BLANK) { // ? return null; } else if (cell.getCellTypeEnum() == CellType.STRING && StringUtil.isEmpty(cell.getStringCellValue())) { // ??????null? return null; } if (Object.class.isAssignableFrom(propertyClass)) { if (Number.class.isAssignableFrom(propertyClass)) { Number number = (Number) cell.getNumericCellValue(); // if (propertyClass.equals(Short.class)) { return number.shortValue(); } else if (propertyClass.equals(Integer.class)) { return number.intValue(); } else if (propertyClass.equals(Long.class)) { return number.longValue(); } else if (propertyClass.equals(Float.class)) { return number.floatValue(); } else if (propertyClass.equals(Double.class)) { return number.doubleValue(); } else if (propertyClass.equals(BigDecimal.class)) { return new BigDecimal(number.doubleValue()); } else if (propertyClass.equals(Byte.class)) { return new Byte(number.byteValue()); } else { return number; } } else if (Date.class.isAssignableFrom(propertyClass)) { // return cell.getDateCellValue(); } else if (String.class.isAssignableFrom(propertyClass)) { // Object value = getCellValue(cell); if (value == null) { return null; } String strValue = null; if (value instanceof String) { strValue = (String) value; } if (value instanceof Double) { // Double -> String??????? strValue = String.valueOf(((Double) value).intValue()); } else { strValue = value.toString(); } return strValue; } else if (Boolean.class.isAssignableFrom(propertyClass) || boolean.class.isAssignableFrom(propertyClass)) { // Boolean Object value = getCellValue(cell); if (value == null) { return null; } if (value instanceof String) { return Boolean.valueOf((String) value); } return value; } } else { // Object value = getCellValue(cell); if (value == null) { return null; } if (value instanceof Double) { if (byte.class.isAssignableFrom(propertyClass)) { int intValue = Double.valueOf((Double) value).intValue(); value = Byte.valueOf(String.valueOf(intValue)); } else if (short.class.isAssignableFrom(propertyClass)) { value = Double.valueOf((Double) value).shortValue(); } else if (int.class.isAssignableFrom(propertyClass)) { value = Double.valueOf((Double) value).intValue(); } else if (long.class.isAssignableFrom(propertyClass)) { value = Double.valueOf((Double) value).longValue(); } else if (float.class.isAssignableFrom(propertyClass)) { value = Double.valueOf((Double) value).floatValue(); } else if (double.class.isAssignableFrom(propertyClass)) { value = Double.valueOf((Double) value).doubleValue(); } } return value; } return null; } /** * ????? * * @param cell * @return ?? */ public static String getSheetName(Cell cell) { Sheet sheet = cell.getSheet(); return getSheetName(sheet); } /** * ???? * * @param sheet * @return ?? */ public static String getSheetName(Sheet sheet) { Workbook workbook = sheet.getWorkbook(); int sheetIndex = workbook.getSheetIndex(sheet); return workbook.getSheetName(sheetIndex); } /** * ????? * * @param workbook * @param filename ?? * @throws IOException ???? */ public static void writeBook(Workbook workbook, String filename) throws IOException { // FileOutputStream fileOut = new FileOutputStream(filename); try { workbook.write(fileOut); } finally { fileOut.close(); } } /** * ? * * @param fromCell * @param toCell */ public static void copyCell(Cell fromCell, Cell toCell) { if (fromCell != null) { // CellType cellType = fromCell.getCellTypeEnum(); switch (cellType) { case BLANK: break; case FORMULA: String cellFormula = fromCell.getCellFormula(); toCell.setCellFormula(cellFormula); break; case BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); break; case ERROR: toCell.setCellErrorValue(fromCell.getErrorCellValue()); break; case NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); break; case STRING: toCell.setCellValue(fromCell.getRichStringCellValue()); break; default: } // if (fromCell.getCellStyle() != null && fromCell.getSheet().getWorkbook().equals(toCell.getSheet().getWorkbook())) { toCell.setCellStyle(fromCell.getCellStyle()); } // if (fromCell.getCellComment() != null) { toCell.setCellComment(fromCell.getCellComment()); } } } /** * ? * * @param fromSheet * @param rangeAddress * @param toSheet * @param toRowNum * @param toColumnNum * @param clearFromRange */ public static void copyRange(Sheet fromSheet, CellRangeAddress rangeAddress, Sheet toSheet, int toRowNum, int toColumnNum, boolean clearFromRange) { if (fromSheet == null || rangeAddress == null || toSheet == null) { return; } int fromRowIndex = rangeAddress.getFirstRow(); int fromColumnIndex = rangeAddress.getFirstColumn(); int rowNumOffset = toRowNum - fromRowIndex; int columnNumOffset = toColumnNum - fromColumnIndex; // CellRangeAddress toAddress = new CellRangeAddress(rangeAddress.getFirstRow() + rowNumOffset, rangeAddress.getLastRow() + rowNumOffset, rangeAddress.getFirstColumn() + columnNumOffset, rangeAddress.getLastColumn() + columnNumOffset); Workbook fromWorkbook = fromSheet.getWorkbook(); Sheet baseSheet = fromSheet; Sheet tmpSheet = null; // ????? if (fromSheet.equals(toSheet) && crossRangeAddress(rangeAddress, toAddress)) { // ? tmpSheet = fromWorkbook.getSheet(TMP_SHEET_NAME); if (tmpSheet == null) { tmpSheet = fromWorkbook.createSheet(TMP_SHEET_NAME); } baseSheet = tmpSheet; int lastColNum = getLastColNum(fromSheet); for (int i = 0; i <= lastColNum; i++) { tmpSheet.setColumnWidth(i, fromSheet.getColumnWidth(i)); } copyRange(fromSheet, rangeAddress, tmpSheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(), false); // ? if (clearFromRange) { clearRange(fromSheet, rangeAddress); } } // ???? Set<CellRangeAddress> targetCellSet = getMergedAddress(baseSheet, rangeAddress); // ??? clearRange(toSheet, toAddress); // ??? for (CellRangeAddress mergeAddress : targetCellSet) { toSheet.addMergedRegion(new CellRangeAddress(mergeAddress.getFirstRow() + rowNumOffset, mergeAddress.getLastRow() + rowNumOffset, mergeAddress.getFirstColumn() + columnNumOffset, mergeAddress.getLastColumn() + columnNumOffset)); } for (int i = rangeAddress.getFirstRow(); i <= rangeAddress.getLastRow(); i++) { // Row fromRow = baseSheet.getRow(i); if (fromRow == null) { continue; } Row row = toSheet.getRow(i + rowNumOffset); if (row == null) { row = toSheet.createRow(i + rowNumOffset); row.setHeight((short) 0); } // ?????? int fromRowHeight = fromRow.getHeight(); int toRowHeight = row.getHeight(); if (toRowHeight < fromRowHeight) { row.setHeight(fromRow.getHeight()); } ColumnHelper columnHelper = null; if (toSheet instanceof XSSFSheet) { XSSFSheet xssfSheet = (XSSFSheet) toSheet.getWorkbook() .getSheetAt(toSheet.getWorkbook().getSheetIndex(toSheet)); CTWorksheet ctWorksheet = xssfSheet.getCTWorksheet(); columnHelper = new ColumnHelper(ctWorksheet); } for (int j = rangeAddress.getFirstColumn(); j <= rangeAddress.getLastColumn(); j++) { Cell fromCell = fromRow.getCell(j); if (fromCell == null) { continue; } int maxColumn = SpreadsheetVersion.EXCEL97.getMaxColumns(); if (toSheet instanceof XSSFSheet) { maxColumn = SpreadsheetVersion.EXCEL2007.getMaxColumns(); } if (j + columnNumOffset >= maxColumn) { break; } Cell cell = row.getCell(j + columnNumOffset); if (cell == null) { cell = row.createCell(j + columnNumOffset); if (toSheet instanceof XSSFSheet) { // XSSF?????????? CTCol col = columnHelper.getColumn(cell.getColumnIndex(), false); if (col == null || !col.isSetWidth()) { toSheet.setColumnWidth(cell.getColumnIndex(), baseSheet.getColumnWidth(j)); } } } // ? copyCell(fromCell, cell); // ?????? int fromColumnWidth = baseSheet.getColumnWidth(j); int toColumnWidth = toSheet.getColumnWidth(j + columnNumOffset); if (toColumnWidth < fromColumnWidth) { toSheet.setColumnWidth(j + columnNumOffset, baseSheet.getColumnWidth(j)); } } } if (tmpSheet != null) { // fromWorkbook.removeSheetAt(fromWorkbook.getSheetIndex(tmpSheet)); } else if (clearFromRange) { // ???? clearRange(fromSheet, rangeAddress); } } /** * ??? * * @param sheet * @param rangeAddress */ public static void insertRangeDown(Sheet sheet, CellRangeAddress rangeAddress) { // ?? int rangeLastRowNum = getLastRowNum(sheet, rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()); // if (rangeLastRowNum != -1 && rangeAddress.getFirstRow() <= rangeLastRowNum) { CellRangeAddress fromAddress = new CellRangeAddress(rangeAddress.getFirstRow(), rangeLastRowNum, rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()); copyRange(sheet, fromAddress, sheet, rangeAddress.getLastRow() + 1, rangeAddress.getFirstColumn(), true); } } /** * ?????? * * @param sheet * @param firstColumnIndex * @param lastColmunIndex * @return ? */ public static int getLastRowNum(Sheet sheet, int firstColumnIndex, int lastColmunIndex) { // ?? int sheetLastRowNum = sheet.getLastRowNum(); int rangeLastRowNum = -1; // ??? for (int i = sheetLastRowNum; 0 <= i; i--) { Row row = sheet.getRow(i); if (row == null) { continue; } Iterator<Cell> rowIterator = row.iterator(); while (rowIterator.hasNext()) { Cell cell = rowIterator.next(); if (cell != null) { if (firstColumnIndex <= cell.getColumnIndex() && cell.getColumnIndex() <= lastColmunIndex) { rangeLastRowNum = i; break; } } } if (rangeLastRowNum != -1) { break; } } return rangeLastRowNum; } /** * ???? * * @param sheet * @param rangeAddress */ public static void insertRangeRight(Sheet sheet, CellRangeAddress rangeAddress) { int rangeLastColumn = getLastColumnNum(sheet, rangeAddress.getFirstRow(), rangeAddress.getLastRow()); // if (rangeLastColumn != -1 && rangeAddress.getFirstColumn() <= rangeLastColumn) { CellRangeAddress fromAddress = new CellRangeAddress(rangeAddress.getFirstRow(), rangeAddress.getLastRow(), rangeAddress.getFirstColumn(), rangeLastColumn); copyRange(sheet, fromAddress, sheet, rangeAddress.getFirstRow(), rangeAddress.getLastColumn() + 1, true); } } /** * ?????? * * @param sheet * @param firstRowIndex * @param lastRowIndex * @return ? */ public static int getLastColumnNum(Sheet sheet, int firstRowIndex, int lastRowIndex) { // ?? int rangeLastColumn = -1; for (int i = firstRowIndex; i <= lastRowIndex; i++) { Row row = sheet.getRow(i); if (row == null) { continue; } Iterator<Cell> rowIterator = row.iterator(); while (rowIterator.hasNext()) { Cell cell = rowIterator.next(); if (cell != null) { if (rangeLastColumn < cell.getColumnIndex()) { rangeLastColumn = cell.getColumnIndex(); } } } } return rangeLastColumn; } /** * ??? * * @param sheet * @param rangeAddress */ public static void deleteRangeUp(Sheet sheet, CellRangeAddress rangeAddress) { int rangeLastRowNum = getLastRowNum(sheet, rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()); // if (rangeLastRowNum != -1 && rangeAddress.getFirstRow() <= rangeLastRowNum) { CellRangeAddress fromAddress = new CellRangeAddress(rangeAddress.getLastRow() + 1, rangeLastRowNum, rangeAddress.getFirstColumn(), rangeAddress.getLastColumn()); copyRange(sheet, fromAddress, sheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(), true); } } /** * ??? * * @param sheet * @param rangeAddress */ public static void deleteRangeLeft(Sheet sheet, CellRangeAddress rangeAddress) { int rangeLastColumn = getLastColumnNum(sheet, rangeAddress.getFirstRow(), rangeAddress.getLastRow()); // if (rangeLastColumn != -1 && rangeAddress.getFirstColumn() <= rangeLastColumn) { CellRangeAddress fromAddress = new CellRangeAddress(rangeAddress.getFirstRow(), rangeAddress.getLastRow(), rangeAddress.getLastColumn() + 1, rangeLastColumn); copyRange(sheet, fromAddress, sheet, rangeAddress.getFirstRow(), rangeAddress.getFirstColumn(), true); } } /** * ???????? * * @param sheet * @param rangeAddress * @return ?????? */ private static Set<CellRangeAddress> getMergedAddress(Sheet sheet, CellRangeAddress rangeAddress) { // ?? Set<CellRangeAddress> targetCellSet = new HashSet<CellRangeAddress>(); int fromSheetMargNums = sheet.getNumMergedRegions(); for (int i = 0; i < fromSheetMargNums; i++) { CellRangeAddress mergedAddress = null; if (sheet instanceof XSSFSheet) { mergedAddress = ((XSSFSheet) sheet).getMergedRegion(i); } else if (sheet instanceof HSSFSheet) { mergedAddress = ((HSSFSheet) sheet).getMergedRegion(i); } // fromAddress???? if (crossRangeAddress(rangeAddress, mergedAddress)) { if (!containCellRangeAddress(rangeAddress, mergedAddress)) { throw new IllegalArgumentException("There are crossing merged regions in the range."); } // OK targetCellSet.add(mergedAddress); } } return targetCellSet; } /** * ? * * @param sheet * @param rangeAddress */ public static void clearRange(Sheet sheet, CellRangeAddress rangeAddress) { clearMergedRegion(sheet, rangeAddress); clearCell(sheet, rangeAddress); } /** * ?? * * @param sheet * @param rangeAddress */ public static void clearCell(Sheet sheet, CellRangeAddress rangeAddress) { int fromRowIndex = rangeAddress.getFirstRow(); int fromColumnIndex = rangeAddress.getFirstColumn(); int toRowIndex = rangeAddress.getLastRow(); int toColumnIndex = rangeAddress.getLastColumn(); // ??? List<Row> removeRowList = new ArrayList<Row>(); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (fromRowIndex <= row.getRowNum() && row.getRowNum() <= toRowIndex) { Set<Cell> removeCellSet = new HashSet<Cell>(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (fromColumnIndex <= cell.getColumnIndex() && cell.getColumnIndex() <= toColumnIndex) { removeCellSet.add(cell); } } for (Cell cell : removeCellSet) { row.removeCell(cell); } } if (row.getLastCellNum() == -1) { removeRowList.add(row); } } for (Row row : removeRowList) { sheet.removeRow(row); } } /** * ???? * * @param sheet * @param rangeAddress */ public static void clearMergedRegion(Sheet sheet, CellRangeAddress rangeAddress) { // ???? Set<CellRangeAddress> clearMergedCellSet = getMergedAddress(sheet, rangeAddress); // ??? SortedSet<Integer> deleteIndexs = new TreeSet<Integer>(Collections.reverseOrder()); int fromSheetMargNums = sheet.getNumMergedRegions(); for (int i = 0; i < fromSheetMargNums; i++) { CellRangeAddress mergedAddress = null; if (sheet instanceof XSSFSheet) { mergedAddress = ((XSSFSheet) sheet).getMergedRegion(i); } else if (sheet instanceof HSSFSheet) { mergedAddress = ((HSSFSheet) sheet).getMergedRegion(i); } for (CellRangeAddress address : clearMergedCellSet) { if (mergedAddress.formatAsString().equals(address.formatAsString())) { // deleteIndexs.add(i); break; } } } for (Integer index : deleteIndexs) { sheet.removeMergedRegion(index); } } /** * ??????<BR> * Cell?CELL_TYPE_BLANK??????????????? * * @see Workbook#cloneSheet(int) cloneSheet(int) * @param sheet * @deprecated poi-3.5-beta7-20090607.jar?? */ public static void prepareCloneSheet(Sheet sheet) { Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { cell.setCellValue(""); } } } } /** * ???????? * * @param baseAddress * @param targetAddress * @return ??????true????false */ public static boolean crossRangeAddress(CellRangeAddress baseAddress, CellRangeAddress targetAddress) { if (baseAddress.getFirstRow() <= targetAddress.getLastRow() && baseAddress.getLastRow() >= targetAddress.getFirstRow()) { if (baseAddress.getFirstColumn() <= targetAddress.getLastColumn() && baseAddress.getLastColumn() >= targetAddress.getFirstColumn()) { return true; } } return false; } /** * ??????? * * @param baseAddress * @param targetAddress * @return ???????true????false */ public static boolean containCellRangeAddress(CellRangeAddress baseAddress, CellRangeAddress targetAddress) { if (baseAddress.getFirstRow() <= targetAddress.getFirstRow() && baseAddress.getLastRow() >= targetAddress.getLastRow()) { if (baseAddress.getFirstColumn() <= targetAddress.getFirstColumn() && baseAddress.getLastColumn() >= targetAddress.getLastColumn()) { return true; } } return false; } /** * ??? * * @param cell * @param type * @param address ? * @see org.apache.poi.common.usermodel.Hyperlink */ public static void setHyperlink(Cell cell, HyperlinkType hyperlinkType, String address) { Workbook wb = cell.getRow().getSheet().getWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Hyperlink link = createHelper.createHyperlink(hyperlinkType); if (link instanceof HSSFHyperlink) { ((HSSFHyperlink) link).setTextMark(address); } else if (link instanceof XSSFHyperlink) { ((XSSFHyperlink) link).setAddress(address); } cell.setHyperlink(link); } /** * ?? * * @param cell * @param value */ public static void setCellValue(Cell cell, Object value) { CellStyle style = cell.getCellStyle(); if (value != null) { if (value instanceof String) { cell.setCellValue((String) value); } else if (value instanceof Number) { Number numValue = (Number) value; if (numValue instanceof Float) { Float floatValue = (Float) numValue; numValue = new Double(String.valueOf(floatValue)); } cell.setCellValue(numValue.doubleValue()); } else if (value instanceof Date) { Date dateValue = (Date) value; cell.setCellValue(dateValue); } else if (value instanceof Boolean) { Boolean boolValue = (Boolean) value; cell.setCellValue(boolValue); } } else { cell.setCellType(CellType.BLANK); cell.setCellStyle(style); } } /** * ???? ??? * A0???????-1? * * @param sheet * @return ???? */ public static int getLastColNum(Sheet sheet) { int lastColNum = 0; for (int i = 0; i <= sheet.getLastRowNum(); i++) { if (sheet.getRow(i) == null) { continue; } int tmpColNum = sheet.getRow(i).getLastCellNum(); if (lastColNum < tmpColNum) { lastColNum = tmpColNum; } } return lastColNum - 1; } }