package api;
import java.awt.Color;
import java.awt.Font;
import java.io.File;
import java.lang.reflect.Field;
import javax.imageio.ImageIO;
import com.fr.base.CoreDecimalFormat;
import com.fr.base.Formula;
import com.fr.base.NameStyle;
import com.fr.base.Style;
import com.fr.base.TextFormat;
import com.fr.base.background.ColorBackground;
import com.fr.base.background.GradientBackground;
import com.fr.base.background.ImageBackground;
import com.fr.base.background.PatternBackground;
import com.fr.base.background.TextureBackground;
import com.fr.base.present.DictPresent;
import com.fr.base.present.FormulaPresent;
import com.fr.code.bar.core.BarcodeAttr;
import com.fr.data.impl.DatabaseDictionary;
import com.fr.data.impl.NameDatabaseConnection;
import com.fr.data.util.SortOrder;
import com.fr.general.FRFont;
import com.fr.io.TemplateWorkBookIO;
import com.fr.main.impl.WorkBook;
import com.fr.report.cell.DefaultTemplateCellElement;
import com.fr.report.cell.cellattr.BarcodePresent;
import com.fr.report.cell.cellattr.CellExpandAttr;
import com.fr.report.cell.cellattr.CellGUIAttr;
import com.fr.report.cell.cellattr.CellInsertPolicyAttr;
import com.fr.report.cell.cellattr.CellPageAttr;
import com.fr.report.cell.cellattr.CurrencyLineAttr;
import com.fr.report.cell.cellattr.CurrencyLinePresent;
import com.fr.report.stable.ReportConstants;
import com.fr.report.worksheet.WorkSheet;
import com.fr.stable.Constants;
import com.fr.stable.unit.OLDPIX;
/**
* 单元格属性
*
* @author Administrator
*/
public class CellAttribute {
public static void main(String[] args) {
try {
ServerConnector.remote();
WorkBook workbook = new WorkBook();
workbook.addReport("扩展", expandSheet());
workbook.addReport("样式", styleSheet());
workbook.addReport("形态", presentSheet());
workbook.addReport("其他", otherSheet());
TemplateWorkBookIO.writeTemplateWorkBook(workbook, "单元格属性.cpt");
} catch (Exception e) {
e.printStackTrace();
}
ServerConnector.disconnect();
}
/**
* 其他属性
*
* @return
* @throws Exception
*/
public static WorkSheet otherSheet() throws Exception {
DefaultTemplateCellElement cell = null;
int row = 0;
WorkSheet sheet = new WorkSheet();
cell = new DefaultTemplateCellElement(0, row, "跟随页面设置");
CellGUIAttr gui = new CellGUIAttr();
// 基本
gui.setAdjustMode(CellGUIAttr.ADJUST_MODE_DEFAULT);
// 预览单元格内容
gui.setPreviewContent(true);
// 打印/导出单元格内容
gui.setPrintContent(true);
// 打印/导出单元格背景
gui.setPrintBackground(true);
// 显示内容
gui.setShowAsDefault(true); // 默认
gui.setShowAsImage(false); // 用图片显示内容
gui.setShowAsHTML(false); // 用HTML显示内容
gui.setShowAsDownload(false); // 用下载链接显示二进制内容
// 内容提示
gui.setTooltipText("tip");
cell.setCellGUIAttr(gui);
// 插入行策略
CellInsertPolicyAttr policy = new CellInsertPolicyAttr();
policy.setInsertPolicy(CellInsertPolicyAttr.INSERT_POLICY_NULL); // 空值
policy.setInsertPolicy(CellInsertPolicyAttr.INSERT_POLICY_COPY); // 原值
policy.setInsertPolicy(CellInsertPolicyAttr.INSERT_POLICY_DEFAULT); // 默认值
policy.setDefaultInsertValue("123");
cell.setCellInsertPolicyAttr(policy);
// 分页
CellPageAttr page = new CellPageAttr();
// 行前分页
page.setPageBeforeRow(true);
// 行后分页
page.setPageAfterRow(true);
// 列前分页
page.setPageBeforeColumn(true);
// 列后分页
page.setPageAfterColumn(true);
// 分页时可用断开
page.setCanBreakOnPaginate(true);
// 分页断开时值重复显示
page.setRepeat(true);
cell.setCellPageAttr(page);
sheet.addCellElement(cell);
return sheet;
}
/**
* 形态
*
* @return
* @throws Exception
*/
public static WorkSheet presentSheet() throws Exception {
DefaultTemplateCellElement cell = null;
int row = 0;
WorkSheet sheet = new WorkSheet();
/*
* 数据字典
*/
DictPresent present = new DictPresent();
// 数据库表
DatabaseDictionary dd = new DatabaseDictionary();
// 数据库
dd.setDatabaseConnection(new NameDatabaseConnection("FRDemo"));
// 模式
dd.setSchema("");
// 选择数据库表
dd.setTableName("user");
// 实际值
dd.setKeyColumnIndex(-1); // 指定名称时必须为-1
dd.setKeyColumnName("user");
// 显示值
dd.setValueColumnIndex(-1); // 指定名称时必须为-1
dd.setValueColumnName("name");
// dd.setFormula(new Formula("$$$+2"));
present.setDictionary(dd);
cell = new DefaultTemplateCellElement(0, row, "sunlin");
cell.setPresent(present);
sheet.addCellElement(cell);
row ++;
/*
* 条形码
*/
cell = new DefaultTemplateCellElement(0, row, "123");
cell.setPresent(new BarcodePresent(new BarcodeAttr(BarcodeAttr.EAN128)));
sheet.addCellElement(cell);
row ++;
/*
* 公式形态
*/
cell = new DefaultTemplateCellElement(0, row, "123");
cell.setPresent(new FormulaPresent("$$$+1"));
sheet.addCellElement(cell);
row ++;
/*
* 金钱线
*/
cell = new DefaultTemplateCellElement(0, row, "123");
CurrencyLineAttr attr = new CurrencyLineAttr();
// 整数部分
attr.setintPart(4);
// 小数部分
attr.setdeciPart(2);
cell.setPresent(new CurrencyLinePresent(attr));
sheet.addCellElement(cell);
return sheet;
}
/**
* 样式
*
* @return
* @throws Exception
*/
public static WorkSheet styleSheet() throws Exception {
DefaultTemplateCellElement cell = null;
Style style = null;
WorkSheet sheet = new WorkSheet();
sheet.setColumnWidth(0, new OLDPIX(100));
int row = 0;
/*
* 预定义样式
*/
cell = new DefaultTemplateCellElement(0, row, 2, 1, "预定义样式:简绿");
cell.setStyle(NameStyle.getInstance("简绿"));
sheet.addCellElement(cell);
row ++;
/*
* 自定义样式 - 文本
*/
// 格式
// 数字
cell = new DefaultTemplateCellElement(0, row, 123);
cell.setStyle(Style.getInstance(new CoreDecimalFormat("#0.00")));
sheet.addCellElement(cell);
// 货币
cell = new DefaultTemplateCellElement(1, row, 123);
cell.setStyle(Style.getInstance(new CoreDecimalFormat("¤#0")));
sheet.addCellElement(cell);
// 百分比
cell = new DefaultTemplateCellElement(2, row, 0.123);
cell.setStyle(Style.getInstance(new CoreDecimalFormat("#0.0%")));
sheet.addCellElement(cell);
// 科学计数
cell = new DefaultTemplateCellElement(3, row, 12.3);
cell.setStyle(Style.getInstance(new CoreDecimalFormat("0.00E00")));
sheet.addCellElement(cell);
// 日期
cell = new DefaultTemplateCellElement(4, row, "1970-01-01");
cell.setStyle(Style.getInstance(new java.text.SimpleDateFormat("yyyy-MM-dd")));
sheet.addCellElement(cell);
// 时间
cell = new DefaultTemplateCellElement(5, row, "1970-01-01");
cell.setStyle(Style.getInstance(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss")));
sheet.addCellElement(cell);
// 文本类型
cell = new DefaultTemplateCellElement(6, row, "文本");
cell.setStyle(Style.getInstance(TextFormat.getInstance()));
sheet.addCellElement(cell);
row ++;
// 字体
cell = new DefaultTemplateCellElement(0, row, "黑体");
cell.setStyle(Style.getInstance(FRFont.getInstance().applyName("黑体")));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(1, row, "BOLD粗体");
cell.setStyle(Style.getInstance(FRFont.getInstance().applyStyle(Font.BOLD)));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(2, row, "20号字");
cell.setStyle(Style.getInstance(FRFont.getInstance().applySize(20)));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(3, row, "红色字");
cell.setStyle(Style.getInstance(FRFont.getInstance().applyForeground(Color.red)));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(4, row, "下划线");
cell.setStyle(Style.getInstance(FRFont.getInstance().applyUnderline(Constants.LINE_THIN)));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(5, row, "删除线");
cell.setStyle(Style.getInstance(FRFont.getInstance().applyStrikethrough(true)));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(6, row, "阴影");
cell.setStyle(Style.getInstance(FRFont.getInstance().applyShadow(true)));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(7, row, "上标");
cell.setStyle(Style.getInstance(FRFont.getInstance().applySuperscript(true)));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(8, row, "下标");
cell.setStyle(Style.getInstance(FRFont.getInstance().applySubscript(true)));
sheet.addCellElement(cell);
row ++;
cell = new DefaultTemplateCellElement(7, row, "");
sheet.addCellElement(cell);
sheet.setRowHeight(row, new OLDPIX(5));
row ++;
/*
* 自定义样式 - 单元格
*/
cell = new DefaultTemplateCellElement(0, row, "边框样式");
// 样式&颜色
cell.setStyle(Style.getInstance().deriveBorderRight(Constants.LINE_THIN, Color.RED));
sheet.addCellElement(cell);
row ++;
cell = new DefaultTemplateCellElement(0, row, "");
sheet.addCellElement(cell);
sheet.setRowHeight(row, new OLDPIX(5));
row ++;
int col = 0;
for (Field f : Constants.class.getDeclaredFields()) {
if (f.getName().startsWith("LINE_")) {
cell = new DefaultTemplateCellElement(col, row, f.getName());
style = Style.getInstance();
style = style.deriveBorderTop((int) f.get(Constants.class), Color.blue);
style = style.deriveBorderBottom((int) f.get(Constants.class), Color.red);
cell.setStyle(style);
sheet.addCellElement(cell);
}
col ++;
}
row ++;
cell = new DefaultTemplateCellElement(0, row, "");
sheet.addCellElement(cell);
sheet.setRowHeight(row, new OLDPIX(5));
row ++;
// 边框位置
cell = new DefaultTemplateCellElement(0, row, "全边框");
style = Style.getInstance().deriveBorder( // 全边框
Constants.LINE_THIN, Color.RED, // 上边框
Constants.LINE_THIN, Color.GREEN, // 下边框
Constants.LINE_THIN, Color.BLUE, // 左边框
Constants.LINE_THIN, Color.BLACK // 右边框
);
cell.setStyle(style);
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(2, row, "上边框");
cell.setStyle(Style.getInstance().deriveBorderTop(Constants.LINE_THIN, Color.BLACK));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(3, row, "右边框");
cell.setStyle(Style.getInstance().deriveBorderRight(Constants.LINE_THIN, Color.BLACK));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(4, row, "下边框");
cell.setStyle(Style.getInstance().deriveBorderBottom(Constants.LINE_THIN, Color.BLACK));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(5, row, "左边框");
cell.setStyle(Style.getInstance().deriveBorderLeft(Constants.LINE_THIN, Color.BLACK));
sheet.addCellElement(cell);
row ++;
cell = new DefaultTemplateCellElement(0, row, "");
sheet.addCellElement(cell);
sheet.setRowHeight(row, new OLDPIX(5));
row ++;
// 颜色背景
cell = new DefaultTemplateCellElement(0, row, 2, 2, "颜色背景");
cell.setStyle(Style.getInstance().deriveBackground(ColorBackground.getInstance(Color.YELLOW)));
sheet.addCellElement(cell);
row += 2;
// 纹理背景
cell = new DefaultTemplateCellElement(0, row, 2, 2, "纹理背景");
cell.setStyle(
Style.getInstance().deriveBackground(new TextureBackground(TextureBackground.FISH_FOSSIL_TEXTURE_PAINT)));
sheet.addCellElement(cell);
row += 2;
// 图案背景
cell = new DefaultTemplateCellElement(0, row, 2, 2, "图案背景");
cell.setStyle(Style.getInstance().deriveBackground(new PatternBackground(0, Color.BLUE, Color.YELLOW)));
sheet.addCellElement(cell);
row += 2;
// 图片背景
cell = new DefaultTemplateCellElement(0, row, 2, 2, "图片背景");
// 图片布局 平铺-IMAGE_TILED;居中-IMAGE_CENTER;拉伸-IMAGE_EXTEND;默认-IMAGE_DEFAULT;适应-IMAGE_ADJUST
cell.setStyle(Style.getInstance().deriveImageLayout(Constants.IMAGE_TILED)
.deriveBackground(new ImageBackground(ImageIO.read(new File("d:/系统.bmp")))));
sheet.addCellElement(cell);
row += 2;
// 渐变背景
cell = new DefaultTemplateCellElement(0, row, 2, 2, "渐变背景");
GradientBackground gbg = new GradientBackground();
// 设置渐变色的起始点
gbg.setBeginPlace(0f);
// 设置该渐变背景是否需要循环显示
gbg.setCycle(true);
// 设置颜色的渐变方向,分为从左到右和从上到下
gbg.setDirection(GradientBackground.TOP2BOTTOM);
// 设置渐变色的完成点
gbg.setFinishPlace(1f);
// 设置起始颜色
gbg.setStartColor(Color.yellow);
// 设置结束颜色
gbg.setEndColor(Color.red);
cell.setStyle(Style.getInstance(gbg));
sheet.addCellElement(cell);
row += 2;
/*
* 自定义样式 - 对齐
*/
// 基本
cell = new DefaultTemplateCellElement(0, row, "基本对齐");
style = Style.getInstance();
// 水平对齐
style = style.deriveHorizontalAlignment(Constants.LEFT);
// 垂直对齐
style = style.deriveVerticalAlignment(Constants.TOP);
cell.setStyle(style);
sheet.addCellElement(cell);
row ++;
// 高级
// 文本控制
// 自动换行
cell = new DefaultTemplateCellElement(0, row, "自动换行自动换行自动换行自动换行");
cell.setStyle(Style.getInstance().deriveTextStyle(Style.TEXTSTYLE_WRAPTEXT));
sheet.addCellElement(cell);
// 单行显示
cell = new DefaultTemplateCellElement(1, row, "单行显示单行显示单行显示单行显示");
cell.setStyle(Style.getInstance().deriveTextStyle(Style.TEXTSTYLE_SINGLELINE));
sheet.addCellElement(cell);
// 单行显示(调整字体)
cell = new DefaultTemplateCellElement(2, row, "单行显示(调整字体)");
cell.setStyle(Style.getInstance().deriveTextStyle(Style.TEXTSTYLE_SINGLELINEADJUSTFONT));
sheet.addCellElement(cell);
// 多行显示(调整字体)
cell = new DefaultTemplateCellElement(3, row, "多行显示(调整字体)");
cell.setStyle(Style.getInstance().deriveTextStyle(Style.TEXTSTYLE_MULTILINEADJUSTFONT));
sheet.addCellElement(cell);
row ++;
// 文本方向
// 文字竖排(从左向右)
cell = new DefaultTemplateCellElement(0, row, "文字竖排(从左向右)文字竖排(从左向右)文字竖排(从左向右)");
cell.setStyle(
Style.getInstance().deriveVerticalText(Style.VERTICALTEXT).deriveTextDirection(Style.LEFT_TO_RIGHT));
sheet.addCellElement(cell);
// 文字竖排(从右向左)
cell = new DefaultTemplateCellElement(1, row, "文字竖排(从右向左)文字竖排(从右向左)文字竖排(从右向左)");
cell.setStyle(
Style.getInstance().deriveVerticalText(Style.VERTICALTEXT).deriveTextDirection(Style.RIGHT_TO_LEFT));
sheet.addCellElement(cell);
// 自定义角度
cell = new DefaultTemplateCellElement(2, row, "自定义角度45度");
cell.setStyle(Style.getInstance().deriveVerticalText(Style.HORIZONTALTEXT).deriveRotation(45));
sheet.addCellElement(cell);
sheet.setRowHeight(row, new OLDPIX(40));
row ++;
// 缩进
// 左缩进
cell = new DefaultTemplateCellElement(0, row, "左缩进7");
cell.setStyle(Style.getInstance().derivePadding(7, 0));
sheet.addCellElement(cell);
// 右缩进
cell = new DefaultTemplateCellElement(1, row, "右缩进7");
cell.setStyle(Style.getInstance().derivePadding(0, 7));
sheet.addCellElement(cell);
row ++;
// 段间距
cell = new DefaultTemplateCellElement(0, row, "段间距前10段间距前10段间距前10\n段间距前10段间距前10段间距前10");
cell.setStyle(Style.getInstance().deriveSpacingBefore(10));
sheet.addCellElement(cell);
cell = new DefaultTemplateCellElement(1, row, "段间距后10段间距后10段间距后10\n段间距后10段间距后10段间距后10");
cell.setStyle(Style.getInstance().deriveSpacingAfter(10));
sheet.addCellElement(cell);
// 间距
cell = new DefaultTemplateCellElement(2, row, "行间距10行间距10行间距10行间距10行间距10行间距10行间距10行间距10");
cell.setStyle(Style.getInstance().deriveLineSpacing(10));
sheet.addCellElement(cell);
sheet.setRowHeight(row, new OLDPIX(40));
return sheet;
}
/**
* 扩展
*
* @return
* @throws Exception
*/
public static WorkSheet expandSheet() throws Exception {
CellExpandAttr expandAttr = new CellExpandAttr();
// 扩展方向 0-竖向;1-横向
expandAttr.setDirection((byte) ReportConstants.PORTRAIT);
// 左父格默认
expandAttr.setLeftParentDefault(true);
// 无左父格
// expandAttr.setLeftParentDefault(false);
// 设置左父格
// expandAttr.setLeftParentColumnRow(new com.fr.stable.ColumnRow(col, row));
// 上父格默认
expandAttr.setUpParentDefault(true);
// 无上父格默认
// expandAttr.setUpParentDefault(false);
// 设置上父格
// expandAttr.setUpParentColumnRow(new com.fr.stable.ColumnRow(col, row));
// 横向、纵向可扩展
expandAttr.setExtendable(CellExpandAttr.Both_EXTENDABLE);
// 排列顺序
expandAttr.setOrder(SortOrder.DESC);
// 排序公式
expandAttr.setSortFormula("$$$");
DefaultTemplateCellElement cell = new DefaultTemplateCellElement(0, 0, new Formula("range(10)"));
cell.setCellExpandAttr(expandAttr);
WorkSheet sheet = new WorkSheet();
sheet.addCellElement(cell);
return sheet;
}
}
单元格条件属性
最新推荐文章于 2022-04-11 09:30:00 发布