com.fastchar.extjs.utils.POIUtils Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of fastchar-extjs Show documentation
Show all versions of fastchar-extjs Show documentation
FastChar-ExtJs is a Java Web framework that uses extjs libraries.Quickly build a background management system
package com.fastchar.extjs.utils;
import com.fastchar.core.FastChar;
import com.fastchar.extjs.core.enums.FastEnumInfo;
import com.fastchar.extjs.interfaces.IFastExtEnum;
import com.fastchar.utils.FastDateUtils;
import com.fastchar.utils.FastStringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.safety.Safelist;
import java.awt.Color;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class POIUtils {
private static Color parseColor(String colorValue) {
Color color = ColorUtils.RgbToColor(colorValue);
if (color != null) {
return color;
}
return ColorUtils.HexToColor(colorValue);
}
public static short getColorIndex(HSSFSheet sheet, String colorValue) {
Color color = parseColor(colorValue);
HSSFPalette palette = sheet.getWorkbook().getCustomPalette();
HSSFColor similarColor = palette.findSimilarColor(color.getRed(), color.getGreen(), color.getBlue());
return similarColor.getIndex();
}
public static void calcSizeColumn(HSSFSheet sheet, int column, int beginRow, int endRow) {
double width = SheetUtil.getColumnWidth(sheet, column, true, beginRow, endRow);
if (width != -1) {
width *= 256;
int maxColumnWidth = 255 * 256; // The maximum column width for an individual cell is 255 characters
if (width > maxColumnWidth) {
width = maxColumnWidth;
}
sheet.setColumnWidth(column, (int) (width));
}
}
public static void calcSizeColumn(HSSFSheet sheet, int column, int row) {
HSSFRow hssfRow = sheet.getRow(row);
if (hssfRow != null) {
HSSFCell cell = hssfRow.getCell(column);
String stringCellValue = cell.getStringCellValue();
if (FastStringUtils.isEmpty(stringCellValue)) {
sheet.setColumnWidth(column, (int) SheetUtil.getColumnWidth(sheet, column, true, 0, row));
return;
}
int width = stringCellValue.getBytes().length * 256;
sheet.setColumnWidth(column, width);
}
}
public static void setBorderColor(HSSFSheet sheet, HSSFCellStyle cellStyle, String colorValue) {
short colorIndex = getColorIndex(sheet, colorValue);
setBorderColor(cellStyle, colorIndex);
}
public static void setBorderColor(HSSFCellStyle cellStyle, short colorIndex) {
cellStyle.setBottomBorderColor(colorIndex);
cellStyle.setTopBorderColor(colorIndex);
cellStyle.setLeftBorderColor(colorIndex);
cellStyle.setRightBorderColor(colorIndex);
}
public static void setBorderRangeColor(HSSFSheet sheet, CellRangeAddress borderRange, String colorValue) {
short colorIndex = getColorIndex(sheet, colorValue);
setBorderRangeColor(sheet, borderRange, colorIndex);
}
public static void setBorderRangeColor(HSSFSheet sheet, CellRangeAddress borderRange, short colorIndex) {
RegionUtil.setBottomBorderColor(colorIndex, borderRange, sheet);
RegionUtil.setTopBorderColor(colorIndex, borderRange, sheet);
RegionUtil.setLeftBorderColor(colorIndex, borderRange, sheet);
RegionUtil.setRightBorderColor(colorIndex, borderRange, sheet);
}
public static void createTitleRow(HSSFCellStyle cellStyle, int[] rowIndex, HSSFSheet sheet,
java.util.List titles) {
int maxLevel = 0;
int maxColumn = titles.size();
for (String value : titles) {
String[] valueArray = value.split("@");
maxLevel = Math.max(maxLevel, valueArray.length);
}
Map> rowValueMap = new LinkedHashMap<>();
for (int level = 0; level < maxLevel; level++) {
int currRowNum = rowIndex[0]++;
HSSFRow row = sheet.createRow(currRowNum);
java.util.List rowValue = new ArrayList<>();
for (int cellNum = 0; cellNum < titles.size(); cellNum++) {
String value = titles.get(cellNum);
String[] valueArray = value.split("@");
HSSFCell cell = row.createCell(cellNum);
String realTitle = valueArray[valueArray.length - 1];
if (level < valueArray.length) {
realTitle = valueArray[level];
}
cell.setCellValue(realTitle);
cell.setCellStyle(cellStyle);
rowValue.add(realTitle);
}
rowValueMap.put(currRowNum, rowValue);
}
//合并单列的同行
for (int cellNum = 0; cellNum < maxColumn; cellNum++) {
Integer beginMarginRowIndex = null;
Integer lastMarginRowIndex = null;
for (Map.Entry> integerListEntry : rowValueMap.entrySet()) {
Integer rowNum = integerListEntry.getKey();
if (beginMarginRowIndex == null) {
beginMarginRowIndex = rowNum;
}
List cellValues = integerListEntry.getValue();
String upRowCellValue = rowValueMap.get(beginMarginRowIndex).get(cellNum);
String cellValue = cellValues.get(cellNum);
if (!cellValue.equalsIgnoreCase(upRowCellValue)) {
if (rowNum - beginMarginRowIndex > 1) {
CellRangeAddress cellAddresses = new CellRangeAddress(beginMarginRowIndex, rowNum, cellNum, cellNum);
sheet.addMergedRegionUnsafe(cellAddresses);
beginMarginRowIndex = rowNum + 1;
} else {
beginMarginRowIndex = rowNum;
}
}
lastMarginRowIndex = rowNum;
}
if (beginMarginRowIndex != null && lastMarginRowIndex - beginMarginRowIndex >= 1) {
CellRangeAddress cellAddresses = new CellRangeAddress(beginMarginRowIndex, lastMarginRowIndex, cellNum, cellNum);
sheet.addMergedRegionUnsafe(cellAddresses);
}
}
//合并单行的同列
for (Map.Entry> integerListEntry : rowValueMap.entrySet()) {
Integer rowNum = integerListEntry.getKey();
List cellValues = integerListEntry.getValue();
int beginMarginCellIndex = 0;
Integer lastMarginCellIndex = null;
for (int cellNum = 0; cellNum < cellValues.size(); cellNum++) {
String value = cellValues.get(cellNum);
String beginValue = cellValues.get(beginMarginCellIndex);
if (!beginValue.equals(value)) {
if (cellNum - beginMarginCellIndex > 1) {
CellRangeAddress cellAddresses = new CellRangeAddress(rowNum, rowNum, beginMarginCellIndex, cellNum - 1);
sheet.addMergedRegionUnsafe(cellAddresses);
}
beginMarginCellIndex = cellNum;
}
lastMarginCellIndex = cellNum;
}
if (lastMarginCellIndex != null && lastMarginCellIndex - beginMarginCellIndex >= 1) {
CellRangeAddress cellAddresses = new CellRangeAddress(rowNum, rowNum, beginMarginCellIndex, lastMarginCellIndex);
sheet.addMergedRegionUnsafe(cellAddresses);
}
}
}
public static String cleanPreserveLineBreaks(String bodyHtml) {
String prettyPrintedBodyFragment = Jsoup.clean(bodyHtml, "", Safelist.none().addTags("br", "p"), new Document.OutputSettings().prettyPrint(true));
return Jsoup.clean(prettyPrintedBodyFragment, "", Safelist.none(), new Document.OutputSettings().prettyPrint(false));
}
public static void setCellComment(HSSFSheet sheet, HSSFCell cell, String content) {
if (FastStringUtils.isEmpty(content) || cell == null) {
return;
}
content = Jsoup.parse("" + content + "").text();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFRichTextString commentRich = new HSSFRichTextString(cleanPreserveLineBreaks(content));
HSSFClientAnchor clientAnchor = new HSSFClientAnchor();
clientAnchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
clientAnchor.setDx1(0);
clientAnchor.setDx2(0);
clientAnchor.setDy1(0);
clientAnchor.setDy2(0);
clientAnchor.setCol1(cell.getColumnIndex());
clientAnchor.setRow1(cell.getRowIndex());
clientAnchor.setCol2(cell.getColumnIndex() + 3);
clientAnchor.setRow2(cell.getRowIndex() + 3);
HSSFComment comment = patriarch.createCellComment(clientAnchor);
HSSFFont font = sheet.getWorkbook().createFont();
font.setFontHeightInPoints((short) 14);
font.setColor(IndexedColors.WHITE.getIndex());
commentRich.applyFont(font);
comment.setString(commentRich);
comment.setAuthor("详情说明");
Color color = parseColor("#000000");
comment.setFillColor(color.getRed(), color.getGreen(), color.getBlue());
Color color2 = parseColor("#FC001A");
comment.setLineStyleColor(color2.getRed(), color2.getGreen(), color2.getBlue());
comment.setLineStyle(HSSFShape.LINESTYLE_SOLID);
cell.setCellComment(comment);
}
public static Object getCellValue(Workbook workbook, Cell cell) {
if (workbook == null) {
return null;
}
if (cell == null) {
return null;
}
return takeCellValue(workbook, cell, cell.getCellType());
}
private static Object takeCellValue(Workbook workbook, Cell cell, CellType cellType) {
try {
if (workbook == null) {
return null;
}
if (cell == null) {
return null;
}
if (cellType == CellType.BLANK || cellType == CellType.STRING) {
return cell.getStringCellValue();
} else if (cellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
return FastDateUtils.format(cell.getDateCellValue(), FastChar.getConstant().getDateFormat());
} else {
return NumberToTextConverter.toText(cell.getNumericCellValue());
}
} else if (cellType == CellType.BOOLEAN) {
return cell.getBooleanCellValue();
} else if (cellType == CellType.FORMULA) {
return takeCellValue(workbook, cell, cell.getCachedFormulaResultType());
}
return cell.getStringCellValue();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void setEnumCell(HSSFSheet sheet, String enumName, int cellIndex, String cellTitle) {
try {
IFastExtEnum enumClass = FastChar.getOverrides().singleInstance(false, IFastExtEnum.class, enumName);
if (enumClass != null) {
HSSFWorkbook workbook = sheet.getWorkbook();
List enums = enumClass.getEnums();
if (!enums.isEmpty()) {
HSSFSheet enumSheet = workbook.getSheet(enumName);
if (enumSheet == null) {
enumSheet = workbook.createSheet(enumName);
for (int i1 = 0; i1 < enums.size(); i1++) {
HSSFRow row = enumSheet.createRow(i1);
row.createCell(0).setCellValue(enums.get(i1).getText());
}
Name namedCell = workbook.createName();
namedCell.setNameName(enumName);
namedCell.setRefersToFormula(enumName + "!$A$1:$A$" + enums.size());
}
DVConstraint constraint = DVConstraint.createFormulaListConstraint(enumName);
CellRangeAddressList cellRegions = new CellRangeAddressList(1, Integer.MAX_VALUE, cellIndex, cellIndex);
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.createErrorBox("输入不合法", "请输入有效的" + cellTitle);
workbook.setSheetHidden(workbook.getSheetIndex(enumSheet), true);
sheet.addValidationData(dataValidate);
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy