/**
* 设置列对齐方式
*
* @param rowset
* @param style
* @param align
* @param columnId
*/
private void setColumnAlign(XSSFCellStyle style, String align) {
if (align.equals("LEFT")) {
style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
} else if (align.equals("RIGHT")) {
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 水平居右
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
} else if (align.equals("RIGHT")) {
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
}
}
/**
* 创建副标题
*
* @param workbook
*/
private void createSubHeads(XSSFWorkbook workbook, List subHeadList) {
XSSFCellStyle style = null;
int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2; // 副标题行数
int fristCol = 0;
int columnCount = 26;
String title = "";
XSSFRow row = null;
XSSFCell cell = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
/** 副标题每行放两个，一个居左，一个居右，所以除以2，多出来的一个，另起一行， */
for (int i = 0; i < rowCount; i++) {
row = workbook.getSheetAt(0).createRow(i + 1); // 创建一个行对象 ,加1是因为有一行头标题
for (int j = 0; j < 2; j++) {
if (subHeadList.size() <= (i * 2 + j)) break;
style = workbook.createCellStyle();
title = subHeadList.get(i * 2 + j).toString();
font.setFontHeightInPoints((short) 9); // 字号
style.setFont(font);
if (j == 0) {
/** 因为每行两个副标题，所以第一个标题占一半 */
fristCol = columnCount / 2;
cell = row.createCell(j); // 创建单元格
cell.setCellValue(title);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(new CellRangeAddress(i + 1, i + 1, 0, fristCol - 1)); // 合并单元格
} else {
/** 因为每行两个副标题，第一个标题占一半，第二个占剩下的所有单元格 */
fristCol = columnCount - (columnCount / 2) - (columnCount % 2);
cell = row.createCell(columnCount / 2); // 创建单元格
cell.setCellValue(title);
// 字号
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 水平居右
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(
new CellRangeAddress(i + 1, i + 1, fristCol, columnCount - 1)); // 合并单元格
}
}
}
}
/**
* 设置表头的单元格样式
*
* @return
*/
public XSSFCellStyle getHeadStyle() {
// 创建单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格的背景颜色为淡蓝色
cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
XSSFFont font = wb.createFont();
// 设置字体加粗
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成 1行のみ描画する
*
* @param workbook ワークブック
* @param sheet シート
* @param nRow 行
* @param nColumn 列
* @param isBold 太字フラグ
* @param fontSize 文字サイズ
* @param fontHeight 行高
*/
public static void setCellStyleForLabel(
XSSFWorkbook workbook,
XSSFSheet sheet,
int nRow,
int nColumn,
boolean isBold,
short fontSize,
float fontHeight) {
assert sheet != null;
// style設定
XSSFCellStyle style = workbook.createCellStyle();
XSSFFont font = workbook.createFont();
if (isBold) {
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 文字太字
}
font.setFontHeightInPoints((short) fontSize); // 文字サイズ
font.setFontName(DEFAULT_FONT_NAME);
style.setFont(font); // 文字太字 と 文字サイズ
style.setAlignment(CellStyle.ALIGN_GENERAL); // 水平方向の標準
style.setVerticalAlignment(CellStyle.VERTICAL_TOP); // 垂直方向の上詰め
style.setWrapText(true); // 折り返して全体を表示する
// セルに罫線を描画
XSSFRow row = getRowAnyway(sheet, nRow);
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellStyle(style);
row.setHeightInPoints(fontHeight); // 行高設定
}
private void copyDefaultCellStyle(XSSFDataFormat format, Cell cell, XSSFCellStyle cs, int i) {
cs.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
cs.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
cs.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
cs.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
cs.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
cs.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
if (i == 1) cs.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:s"));
if (i == 2) cs.setDataFormat(format.getFormat("0"));
cell.setCellStyle(cs);
}
/**
* 创建副标题
*
* @param workbook
*/
private void createSubHeads(XSSFWorkbook workbook, EFRowSet applyForm) {
XSSFCellStyle style = null;
XSSFRow row = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
style = workbook.createCellStyle();
font.setFontHeightInPoints((short) 9); // 字号
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 水平居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
row = workbook.getSheetAt(0).createRow(1);
createSubHeadCell(workbook, row, style, "项目编号：", 1, 0);
createSubHeadCell(workbook, row, style, applyForm.getString("F_XMBH", ""), 1, 2);
createSubHeadCell(workbook, row, style, "项目名称：", 1, 4);
createSubHeadCell(workbook, row, style, applyForm.getString("F_XMMC", ""), 1, 6);
createSubHeadCell(workbook, row, style, "申请日期：", 1, 8);
createSubHeadCell(workbook, row, style, applyForm.getString("F_SQSJ", ""), 1, 10);
createSubHeadCell(workbook, row, style, "项目单位：", 1, 12);
createSubHeadCell(workbook, row, style, applyForm.getString("F_SQDW", ""), 1, 14);
row = workbook.getSheetAt(0).createRow(2);
createSubHeadCell(workbook, row, style, "申请人：", 2, 0);
createSubHeadCell(workbook, row, style, applyForm.getString("F_SQRMC", ""), 2, 2);
createSubHeadCell(workbook, row, style, "供应中心：", 2, 4);
createSubHeadCell(workbook, row, style, applyForm.getString("F_GYZXMC", ""), 2, 6);
createSubHeadCell(workbook, row, style, "单位领导名称：", 2, 8);
createSubHeadCell(workbook, row, style, applyForm.getString("F_DWLDMC", ""), 2, 10);
createSubHeadCell(workbook, row, style, "分管领导名称：", 2, 12);
createSubHeadCell(workbook, row, style, applyForm.getString("F_FGLDMC", ""), 2, 14);
row = workbook.getSheetAt(0).createRow(3);
createSubHeadCell(workbook, row, style, "主管领导名称：", 3, 0);
createSubHeadCell(workbook, row, style, applyForm.getString("F_ZGLDMC", ""), 3, 2);
createSubHeadCell(workbook, row, style, "材料需求时间：", 3, 4);
date = (Date) applyForm.getObject("F_CLXQSJ", "");
createSubHeadCell(workbook, row, style, formatter.format(date), 3, 6);
createSubHeadCell(workbook, row, style, "项目状态：", 3, 8);
if (applyForm.getString("F_XMZT", "0").equals("0")) {
createSubHeadCell(workbook, row, style, "未完工", 3, 10);
} else {
createSubHeadCell(workbook, row, style, "已完工", 3, 10);
createSubHeadCell(workbook, row, style, "完工时间：", 3, 12);
date = (Date) applyForm.getObject("F_WGSJ", "");
createSubHeadCell(workbook, row, style, formatter.format(date), 3, 14);
}
}
/**
* 罫線スタイルの<b>CellStyle</b>を生成
*
* @param workbook ワークブック
* @param backgroundColor 背景色
* @return <b>CellStyle</b>
*/
public static XSSFCellStyle createTableDataCellStyle(
XSSFWorkbook workbook,
boolean isTop,
boolean isBottom,
boolean isLeft,
boolean isRight,
Color backgroundColor,
short fontSize,
boolean bold) {
XSSFCellStyle style = workbook.createCellStyle();
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 必ずsetFillForegroundColorの直前に記述すること
style.setFillForegroundColor(new XSSFColor(backgroundColor));
style.setAlignment(CellStyle.ALIGN_LEFT); // 水平方法の位置
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true); // 折り返して全体を表示する
// style.setShrinkToFit(true);//縮小して全体を表示する
// 文字サイズ設定
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints(fontSize);
if (bold) {
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
}
style.setFont(font);
if (isTop) {
style.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
} else {
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
}
if (isBottom) {
style.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
} else {
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
}
if (isLeft) {
style.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
} else {
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
}
if (isRight) {
style.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
} else {
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
}
return style;
}
/**
* 创建单元格的样式
*
* @param workbook 工作表
* @param font 字代
* @param alignment 水平对齐 如：CellStyle.ALIGN_CENTER
* @param verticalAlignment 垂直对齐 如：CellStyle.VERTICAL_CENTER
* @param Border 边框 如：XSSFCellStyle.BORDER_THIN
* @param foregroundColor 前置背景色 如：CellStyle.BORDER_THIN
* @param fillPattern 填充模式 如：
* @return
*/
public CellStyle createCellStyle(
Workbook workbook,
Font font,
short alignment,
short verticalAlignment,
short Border,
short foregroundColor,
short fillPattern) {
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(alignment);
cellStyle.setVerticalAlignment(verticalAlignment);
cellStyle.setBorderRight(Border);
cellStyle.setFillForegroundColor(foregroundColor);
cellStyle.setFillPattern(fillPattern);
return cellStyle;
}
/**
* 创建标题，并设置字体、字号、加粗、颜色
*
* @param workbook
*/
private void setHeadCellFont(XSSFWorkbook workbook, String title) {
XSSFCellStyle style = workbook.createCellStyle();
XSSFRow row = (XSSFRow) workbook.getSheetAt(0).createRow(0); // 创建一个行对象
XSSFCell cell = row.createCell(0); // 创建单元格
XSSFFont font = workbook.createFont(); // 创建字体对象
font.setFontName(HSSFFont.FONT_ARIAL); // 字体
font.setFontHeightInPoints((short) 16); // 字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
row.setHeightInPoints(23); // 设置行高23像素
cell.setCellStyle(style); // 应用样式对象
workbook
.getSheetAt(0)
.addMergedRegion(new CellRangeAddress(0, 0, 0, 16)); // 四个参数分别是：起始行，结束行，起始列，结束列
cell.setCellValue(title); // 写入头标题
}
/**
* 设置表体的单元格样式
*
* @return
*/
public XSSFCellStyle getBodyStyle() {
// 创建单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体样式
XSSFFont font = wb.createFont();
// 设置字体加粗
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
public static XSSFCellStyle getNewStyle(
XSSFWorkbook workBook, CellStyle cellStyle, XSSFFont font) {
XSSFCellStyle style = workBook.createCellStyle();
// 对齐方式
style.setAlignment(cellStyle.getAlignment());
style.setVerticalAlignment(cellStyle.getVAlignment());
// 设置背景颜色
// 最好的设置Pattern
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 单元格背景的显示模式
style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式．
// style.setFillBackgroundColor(arg0);
// 设置边框
style.setBorderBottom(cellStyle.getBorderBottom()); // 下边框
style.setBorderLeft(cellStyle.getBorderLeft()); // 左边框
style.setBorderTop(cellStyle.getBorderTop()); // 上边框
style.setBorderRight(cellStyle.getBorderRight()); // 右边框
// 设置边框颜色
style.setBottomBorderColor(cellStyle.getBottomBorderColor());
style.setTopBorderColor(cellStyle.getTopBorderColor());
style.setLeftBorderColor(cellStyle.getLeftBorderColor());
style.setRightBorderColor(cellStyle.getRightBorderColor());
// 设置自动换行
style.setWrapText(cellStyle.getWrapText());
style.setHidden(cellStyle.getHidden());
// 数据格式
style.setDataFormat(cellStyle.getDataFormate());
style.setLocked(cellStyle.getLocked());
// 文本旋转 请注意，这里的Rotation取值是从-90到90，而不是0-180度
style.setRotation(cellStyle.getRotation());
// 文本缩进
style.setIndention(cellStyle.getIndention());
// 设置字体
style.setFont(font);
return style;
}
/**
* 创建列
*
* @param workbook
*/
private void exportColumnData(XSSFWorkbook workbook, List subHeadList) {
XSSFCellStyle style = null;
XSSFRow row = null;
XSSFCell cell = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
XSSFCellStyle topStyle = setColumnTopBorder(workbook);
XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook);
int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2 + 1;
row = workbook.getSheetAt(0).createRow(rowCount); // 创建一个行对象 列标题
style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
font.setFontHeightInPoints((short) 10); // 字号
style.setFont(font); // 设置字体
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置前景填充样式
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 204, 255))); // 设置单元格背景色
setColumnBorder(style, 0); // 设置列边框
workbook.getSheetAt(0).setColumnWidth(0, 32 * 100); // 对列设置宽度
/** 创建单元格、设置列名称、合并单元格 */
cell = row.createCell(0);
cell.setCellValue("会计期间");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 0, 0));
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("单据编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 1, 1));
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("分录编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 2, 2));
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("材料入库信息");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 3, 8));
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellStyle(topStyle);
cell = row.createCell(5);
cell.setCellStyle(topStyle);
cell = row.createCell(6);
cell.setCellStyle(topStyle);
cell = row.createCell(7);
cell.setCellStyle(topStyle);
cell = row.createCell(8);
cell.setCellStyle(topStyle);
cell = row.createCell(9);
cell.setCellValue("材料出库信息");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 9, 14));
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellStyle(topStyle);
cell = row.createCell(11);
cell.setCellStyle(topStyle);
cell = row.createCell(12);
cell.setCellStyle(topStyle);
cell = row.createCell(13);
cell.setCellStyle(topStyle);
cell = row.createCell(14);
cell.setCellStyle(topStyle);
cell = row.createCell(15);
cell.setCellValue("材料编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 15, 15));
cell.setCellStyle(style);
cell = row.createCell(16);
cell.setCellValue("材料名称");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 16, 16));
cell.setCellStyle(style);
cell = row.createCell(17);
cell.setCellValue("规格型号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 17, 17));
cell.setCellStyle(style);
cell = row.createCell(18);
cell.setCellValue("计量单位");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 18, 18));
cell.setCellStyle(style);
cell = row.createCell(19);
cell.setCellValue("供应商编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 19, 19));
cell.setCellStyle(style);
cell = row.createCell(20);
cell.setCellValue("供应商名称");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 20, 20));
cell.setCellStyle(style);
cell = row.createCell(21);
cell.setCellValue("出入方向");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 21, 21));
cell.setCellStyle(style);
cell = row.createCell(22);
cell.setCellValue("单据类型");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 22, 22));
cell.setCellStyle(style);
cell = row.createCell(23);
cell.setCellValue("材料单价");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 25, 25));
cell.setCellStyle(style);
cell = row.createCell(24);
cell.setCellValue("材料数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 23, 23));
cell.setCellStyle(style);
cell = row.createCell(25);
cell.setCellValue("材料总价");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 24, 24));
cell.setCellStyle(style);
row = workbook.getSheetAt(0).createRow(rowCount + 1); // 创建一个行对象 列标题
/** 创建单元格、设置列名称、合并单元格 */
// 材料入库信息
cell = row.createCell(3);
cell.setCellValue("仓库编号");
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("仓库编号");
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("仓库名称");
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("项目编号");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("项目名称");
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue("产品编号");
cell.setCellStyle(style);
// 材料出库信息
cell = row.createCell(9);
cell.setCellValue("产品名称");
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellValue("仓库编号");
cell.setCellStyle(style);
cell = row.createCell(11);
cell.setCellValue("仓库名称");
cell.setCellStyle(style);
cell = row.createCell(12);
cell.setCellValue("项目编号");
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellValue("项目名称");
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellValue("产品编号");
cell.setCellStyle(style);
for (int i = 0; i < 3; i++) {
cell = row.createCell(i);
cell.setCellStyle(buttomStyle);
}
for (int i = 15; i < 26; i++) {
cell = row.createCell(i);
cell.setCellStyle(buttomStyle);
}
}
private XSSFCellStyle buildBandElementStyle(
BandElement bandElement, Object value, int gridRow, int gridColumn, int colSpan) {
Map<String, Object> style = buildCellStyleMap(bandElement, value, gridRow, gridColumn, colSpan);
XSSFCellStyle cellStyle = null;
XSSFFont cellFont = null;
int fontKey = -1;
int styleKey = -1;
// we have to create new fonts and styles if some formatting conditions are met
// also for subreports we may have a subreportCellStyle passed by ReportBandElement
boolean cacheFont = false;
boolean cacheAllFont = false;
boolean cacheStyle = false;
boolean cacheAllStyle = false;
if ((modifiedStyle[gridRow][gridColumn]) || bean.isSubreport()) {
fontKey = getFontKey(style);
if (fontKey != -1) {
cellFont = condFonts.get(fontKey);
}
if (cellFont == null) {
cellFont = wb.createFont();
cacheFont = true;
}
styleKey = getStyleKey(style, bandElement);
if (styleKey != -1) {
cellStyle = condStyles.get(styleKey);
}
if (cellStyle == null) {
cellStyle = wb.createCellStyle();
cacheStyle = true;
}
modifiedStyle[gridRow][gridColumn] = false;
} else {
fontKey = getFontKey(style);
if (fontKey != -1) {
cellFont = fonts.get(fontKey);
}
if ((cellFont == null) && (bandElement != null)) {
cellFont = wb.createFont();
cacheAllFont = true;
}
styleKey = getStyleKey(style, bandElement);
if (styleKey != -1) {
cellStyle = styles.get(styleKey);
}
if (cellStyle == null) {
cellStyle = wb.createCellStyle();
cacheAllStyle = true;
}
}
// HSSFPalette cellPal = wb.getCustomPalette();
if (style.containsKey(StyleFormatConstants.FONT_FAMILY_KEY)) {
String val = (String) style.get(StyleFormatConstants.FONT_FAMILY_KEY);
cellFont.setFontName(val);
}
if (style.containsKey(StyleFormatConstants.FONT_SIZE)) {
Float val = (Float) style.get(StyleFormatConstants.FONT_SIZE);
cellFont.setFontHeightInPoints(val.shortValue());
}
if (style.containsKey(StyleFormatConstants.FONT_COLOR)) {
Color val = (Color) style.get(StyleFormatConstants.FONT_COLOR);
cellFont.setColor(ExcelColorSupport.getNearestColor(val));
}
if (style.containsKey(StyleFormatConstants.FONT_STYLE_KEY)) {
if (StyleFormatConstants.FONT_STYLE_NORMAL.equals(
style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
}
if (StyleFormatConstants.FONT_STYLE_BOLD.equals(
style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
}
if (StyleFormatConstants.FONT_STYLE_ITALIC.equals(
style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
cellFont.setItalic(true);
}
if (StyleFormatConstants.FONT_STYLE_BOLDITALIC.equals(
style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
cellFont.setItalic(true);
}
}
if (cacheFont && (fontKey != -1)) {
condFonts.put(fontKey, cellFont);
}
if (cacheAllFont && (fontKey != -1)) {
fonts.put(fontKey, cellFont);
}
if (style.containsKey(StyleFormatConstants.BACKGROUND_COLOR)) {
Color val = (Color) style.get(StyleFormatConstants.BACKGROUND_COLOR);
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(ExcelColorSupport.getNearestColor(val));
}
if (style.containsKey(StyleFormatConstants.HORIZONTAL_ALIGN_KEY)) {
if (StyleFormatConstants.HORIZONTAL_ALIGN_LEFT.equals(
style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
cellStyle.setAlignment((short) 1);
}
if (StyleFormatConstants.HORIZONTAL_ALIGN_RIGHT.equals(
style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
cellStyle.setAlignment((short) 3);
}
if (StyleFormatConstants.HORIZONTAL_ALIGN_CENTER.equals(
style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
cellStyle.setAlignment((short) 2);
}
}
if (style.containsKey(StyleFormatConstants.VERTICAL_ALIGN_KEY)) {
if (StyleFormatConstants.VERTICAL_ALIGN_TOP.equals(
style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
}
if (StyleFormatConstants.VERTICAL_ALIGN_MIDDLE.equals(
style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
}
if (StyleFormatConstants.VERTICAL_ALIGN_BOTTOM.equals(
style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);
}
} else {
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
}
short left = 0, right = 0, top = 0, bottom = 0;
Color leftColor = Color.BLACK,
rightColor = Color.BLACK,
topColor = Color.BLACK,
bottomColor = Color.BLACK;
if (style.containsKey(StyleFormatConstants.BORDER_LEFT)) {
Float val = (Float) style.get(StyleFormatConstants.BORDER_LEFT);
//
left = val.shortValue();
if (left == BORDER_THIN_VALUE) {
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
}
if (left == BORDER_MEDIUM_VALUE) {
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
}
if (left == BORDER_THICK_VALUE) {
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THICK);
}
Color color = (Color) style.get(StyleFormatConstants.BORDER_LEFT_COLOR);
leftColor = color;
cellStyle.setLeftBorderColor(ExcelColorSupport.getNearestColor(color));
}
if (style.containsKey(StyleFormatConstants.BORDER_RIGHT)) {
Float val = (Float) style.get(StyleFormatConstants.BORDER_RIGHT);
//
right = val.shortValue();
if (right == BORDER_THIN_VALUE) {
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
}
if (right == BORDER_MEDIUM_VALUE) {
cellStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
}
if (right == BORDER_THICK_VALUE) {
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THICK);
}
Color color = (Color) style.get(StyleFormatConstants.BORDER_RIGHT_COLOR);
rightColor = color;
cellStyle.setRightBorderColor(ExcelColorSupport.getNearestColor(color));
}
if (style.containsKey(StyleFormatConstants.BORDER_TOP)) {
Float val = (Float) style.get(StyleFormatConstants.BORDER_TOP);
//
top = val.shortValue();
if (top == BORDER_THIN_VALUE) {
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
}
if (top == BORDER_MEDIUM_VALUE) {
cellStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
}
if (top == BORDER_THICK_VALUE) {
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THICK);
}
Color color = (Color) style.get(StyleFormatConstants.BORDER_TOP_COLOR);
topColor = color;
cellStyle.setTopBorderColor(ExcelColorSupport.getNearestColor(color));
}
if (style.containsKey(StyleFormatConstants.BORDER_BOTTOM)) {
Float val = (Float) style.get(StyleFormatConstants.BORDER_BOTTOM);
//
bottom = val.shortValue();
if (bottom == BORDER_THIN_VALUE) {
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
}
if (bottom == BORDER_MEDIUM_VALUE) {
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
}
if (bottom == BORDER_THICK_VALUE) {
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THICK);
}
Color color = (Color) style.get(StyleFormatConstants.BORDER_BOTTOM_COLOR);
bottomColor = color;
cellStyle.setBottomBorderColor(ExcelColorSupport.getNearestColor(color));
}
border = new Border(left, right, top, bottom);
border.setLeftColor(leftColor);
border.setRightColor(rightColor);
border.setTopColor(topColor);
border.setBottomColor(bottomColor);
if (cellFont != null) {
cellStyle.setFont(cellFont);
}
if (style.containsKey(StyleFormatConstants.PATTERN)) {
String pattern = (String) style.get(StyleFormatConstants.PATTERN);
XSSFDataFormat format = wb.createDataFormat();
cellStyle.setDataFormat(format.getFormat(pattern));
} else {
cellStyle.setDataFormat((short) 0);
}
if (bandElement != null) {
cellStyle.setWrapText(bandElement.isWrapText());
}
cellStyle =
updateSubreportBandElementStyle(
cellStyle, bandElement, value, gridRow, gridColumn, colSpan);
if (cacheStyle && (styleKey != -1)) {
condStyles.put(styleKey, cellStyle);
}
if (cacheAllStyle && (styleKey != -1)) {
styles.put(styleKey, cellStyle);
}
return cellStyle;
}
/**
* 创建列
*
* @param workbook
*/
private void exportColumnData(XSSFWorkbook workbook) {
XSSFCellStyle style = null;
XSSFRow row = null;
XSSFCell cell = null;
XSSFFont font = workbook.createFont(); // 创建字体对象
XSSFCellStyle topStyle = setColumnTopBorder(workbook);
XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook);
row = workbook.getSheetAt(0).createRow(5); // 创建一个行对象 列标题
style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
font.setFontHeightInPoints((short) 10); // 字号
style.setFont(font); // 设置字体
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置前景填充样式
style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 204, 255))); // 设置单元格背景色
setColumnBorder(style, 0); // 设置列边框
workbook.getSheetAt(0).setColumnWidth(0, 32 * 100); // 对列设置宽度
/** 创建单元格、设置列名称、合并单元格 */
cell = row.createCell(0);
cell.setCellValue("材料编号");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 0, 0));
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("材料名称");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 1, 1));
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("型号规格");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 2, 2));
cell.setCellStyle(style);
cell = row.createCell(3);
cell.setCellValue("单位");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 3, 3));
cell.setCellStyle(style);
cell = row.createCell(4);
cell.setCellValue("申请数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 4, 4));
cell.setCellStyle(style);
cell = row.createCell(5);
cell.setCellValue("入库数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 5, 5));
cell.setCellStyle(style);
cell = row.createCell(6);
cell.setCellValue("出库数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 5, 6, 8));
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellStyle(topStyle);
cell = row.createCell(8);
cell.setCellStyle(topStyle);
cell = row.createCell(9);
cell.setCellValue("退货数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 5, 9, 10));
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellStyle(topStyle);
cell = row.createCell(11);
cell.setCellValue("调拨数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 11, 11));
cell.setCellStyle(style);
cell = row.createCell(12);
cell.setCellValue("库存数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 12, 12));
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellValue("领用数量");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 13, 13));
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellValue("领用总价");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 14, 14));
cell.setCellStyle(style);
cell = row.createCell(15);
cell.setCellValue("库存情况");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 15, 15));
cell.setCellStyle(style);
cell = row.createCell(16);
cell.setCellValue("备注");
workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 16, 16));
cell.setCellStyle(style);
row = workbook.getSheetAt(0).createRow(6); // 创建一个行对象 列标题
/** 创建单元格、设置列名称、合并单元格 */
cell = row.createCell(6);
cell.setCellValue("正常领用");
cell.setCellStyle(style);
cell = row.createCell(7);
cell.setCellValue("借调");
cell.setCellStyle(style);
cell = row.createCell(8);
cell.setCellValue("被借调");
cell.setCellStyle(style);
cell = row.createCell(9);
cell.setCellValue("材料退货");
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellValue("厂商退货");
cell.setCellStyle(style);
for (int i = 0; i < 5; i++) {
cell = row.createCell(i);
cell.setCellStyle(buttomStyle);
}
for (int i = 11; i < 16; i++) {
cell = row.createCell(i);
cell.setCellStyle(buttomStyle);
}
}
