/**
* 测试单元格样式
*
* @author David
* @param wb
* @param cell
* @param td
*/
private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
Attribute typeAttr = td.getAttribute("type");
String type = typeAttr.getValue();
HSSFDataFormat format = wb.createDataFormat();
HSSFCellStyle cellStyle = wb.createCellStyle();
if ("NUMERIC".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
Attribute formatAttr = td.getAttribute("format");
String formatValue = formatAttr.getValue();
formatValue = StringUtils.isNotBlank(formatValue) ? formatValue : "#,##0.00";
cellStyle.setDataFormat(format.getFormat(formatValue));
} else if ("STRING".equalsIgnoreCase(type)) {
cell.setCellValue("");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellStyle.setDataFormat(format.getFormat("@"));
} else if ("DATE".equalsIgnoreCase(type)) {
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
} else if ("ENUM".equalsIgnoreCase(type)) {
CellRangeAddressList regions =
new CellRangeAddressList(
cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
Attribute enumAttr = td.getAttribute("format");
String enumValue = enumAttr.getValue();
// 加载下拉列表内容
DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));
// 数据有效性对象
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
wb.getSheetAt(0).addValidationData(dataValidation);
}
cell.setCellStyle(cellStyle);
}
public void createExcelFile(String filename) throws Exception {
out = new FileOutputStream(filename);
wb = new HSSFWorkbook();
ws = this.wb.createSheet();
cs1 = this.wb.createCellStyle();
cs2 = this.wb.createCellStyle();
cs3 = this.wb.createCellStyle();
df = this.wb.createDataFormat();
f1 = this.wb.createFont();
f2 = this.wb.createFont();
f1.setFontHeightInPoints((short) 10);
f1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
f2.setFontHeightInPoints((short) 13);
f2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
f2.setColor(HSSFFont.COLOR_RED);
cs1.setFont(f1);
cs1.setDataFormat(df.getFormat("text"));
cs2.setFont(f2);
cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
cs3.setFont(f1);
cs3.setDataFormat(df.getFormat("#,##0.0"));
wb.setSheetName(0, "QryDetail", HSSFWorkbook.ENCODING_UTF_16);
}
public void initStyles() {
// HSSFDataFormat format = workbook.createDataFormat();
redStyle = workbook.createCellStyle();
setBorder(redStyle, 1);
redStyle.setFillForegroundColor(HSSFColor.RED.index);
yellowStyle = workbook.createCellStyle();
setBorder(yellowStyle, 1);
yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
blueStyle = workbook.createCellStyle();
setBorder(blueStyle, 1);
blueStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
whiteStyle = workbook.createCellStyle();
setBorder(whiteStyle, 1);
whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index);
normalStyle = workbook.createCellStyle();
setBorder(normalStyle, 0);
// normalStyle.setFillForegroundColor(HSSFColor.WHITE.index);
normalPStyle = workbook.createCellStyle();
setBorder(normalPStyle, 0);
normalPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
// normalPStyle.setFillForegroundColor(HSSFColor.WHITE.index);
headerStyle = workbook.createCellStyle();
setBorder(headerStyle, 0);
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(font);
redPStyle = workbook.createCellStyle();
setBorder(redPStyle, 1);
redPStyle.setFillForegroundColor(HSSFColor.RED.index);
// redPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
redPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
yellowPStyle = workbook.createCellStyle();
setBorder(yellowPStyle, 1);
yellowPStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
// yellowPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
yellowPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
_fonts = new HashMap<String, HSSFFont>(2);
HSSFFont t13y_red = workbook.createFont();
t13y_red.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
t13y_red.setColor(HSSFFont.COLOR_RED);
_fonts.put("t13yr", t13y_red);
HSSFFont t13y_blue = workbook.createFont();
t13y_blue.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
t13y_blue.setColor(HSSFColor.BLUE_GREY.index);
_fonts.put("t13yb", t13y_blue);
}
/**
* 生成文件
*
* @throws IOException
*/
public byte[] run() throws IOException {
// 初始化参数
initParameter();
// 实例化decorator
initDecorator();
// 初始化
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
sheet.setDefaultColumnWidth((short) 20); // 设置默认宽度
try {
wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16);
} catch (Exception ex) {
logger.error("生成表单时出错：怀疑是表单名导致。说明：poi对中文的支持不好，可能会有问题（仅表单名）如中文?－－中文字加英文标点的情况");
}
baseCellStyle = wb.createCellStyle();
baseCellStyle.setAlignment(align);
baseCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
dateCellStyle = wb.createCellStyle();
dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateFormat));
// 创建标题行
HSSFRow row = sheet.createRow((short) 0);
// 创建标题
for (int i = 0; i < nameList.size(); i++) {
HSSFCell cell = row.createCell((short) i);
writeCell(cell, (String) nameList.get(i));
}
// 创建数据
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow((short) i + 1);
for (int j = 0; j < fieldList.size(); j++) {
HSSFCell cell = row.createCell((short) j);
Object value = getValue(dataList.get(i), (String) fieldList.get(j));
writeCell(cell, value);
}
}
// 写入文件
// FileOutputStream fileOut = new FileOutputStream(fileName);
ByteArrayOutputStream out = new ByteArrayOutputStream();
wb.write(out);
return out.toByteArray();
// fileOut.close();
}
private CoverageResults() {
fileName = "Coverage-results.xls";
workbook = new HSSFWorkbook();
allElementsSheet = workbook.createSheet("All Elements");
packagesSheet = workbook.createSheet("Packages");
boundClassesSheet = workbook.createSheet("Bound Classes");
allClassesSheet = workbook.createSheet("All Classes");
boundMethodsSheet = workbook.createSheet("Bound Methods");
allMethodsSheet = workbook.createSheet("All Methods");
normalFont = workbook.createFont();
normalFont.setFontName(HSSFFont.FONT_ARIAL);
normalFont.setFontHeightInPoints((short) 10);
boldFont = workbook.createFont();
boldFont.setFontName(HSSFFont.FONT_ARIAL);
boldFont.setFontHeightInPoints((short) 10);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
centerAlignmentCellStyle = workbook.createCellStyle();
centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
leftAlignmentCellStyle = workbook.createCellStyle();
leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
rightAlignmentCellStyle = workbook.createCellStyle();
rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
dataFormatCellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
}
public void applyTo(HSSFCellStyle style, HSSFWorkbook workbook) {
style.setAlignment(this.alignment);
style.setBorderBottom(this.borderBottom);
style.setBorderLeft(this.borderLeft);
style.setBorderRight(this.borderRight);
style.setBorderTop(this.borderTop);
style.setBottomBorderColor(this.bottomBorderColor);
style.setDataFormat(this.dataFormat);
style.setFillPattern(this.fillPattern);
style.setFillForegroundColor(this.fillForegroundColor);
style.setFillBackgroundColor(this.fillBackgroundColor);
style.setFont(workbook.getFontAt(this.fontIndex));
style.setHidden(this.hidden);
style.setIndention(this.indention);
style.setLeftBorderColor(this.leftBorderColor);
style.setLocked(this.locked);
style.setRightBorderColor(this.rightBorderColor);
style.setRotation(this.rotation);
style.setTopBorderColor(this.topBorderColor);
style.setVerticalAlignment(this.verticalAlignment);
style.setWrapText(this.wrapText);
}
/**
* 摘要： @说明：Excel样式 @创建：作者:yxy 创建时间：2011-8-17
*
* @param workBook
* @param cellStyle 样式模型
* @return @修改历史： [序号](yxy 2011-8-17)<修改说明>
*/
public static HSSFCellStyle getNewStyle(HSSFWorkbook workBook, CellStyle cellStyle) {
HSSFCellStyle style = workBook.createCellStyle();
// 对齐方式
style.setAlignment(cellStyle.getAlignment());
style.setVerticalAlignment(cellStyle.getVAlignment());
// 设置背景颜色
// 最好的设置Pattern
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 单元格背景的显示模式
style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式．
// 设置边框
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());
return style;
}
public void setCell(int index, Calendar value) {
HSSFCell cell = this.row.createCell((short) index);
// cell.setEncoding(XLS_ENCODING);
cell.setCellValue(value.getTime());
// for new cell style
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT));
cell.setCellStyle(cellStyle);
}
private static Map readStyles(NodeList styleList, Map fonts, HSSFWorkbook workbook)
throws ExcelTransformerException {
if (LOG.isLoggable(Level.FINE)) {
LOG.entering(
SimpleExcelRenderer.class.getName(), "readStyles", String.valueOf(styleList.getLength()));
}
Map results = new HashMap();
for (int i = 0; i < styleList.getLength(); i++) {
Element styleNode = (Element) styleList.item(i);
String name = styleNode.getAttribute("name");
HSSFCellStyle style = workbook.createCellStyle();
NodeList children = styleNode.getChildNodes();
for (int j = 0; j < children.getLength(); j++) {
Node child = children.item(j);
if (child.getNodeType() == Node.ELEMENT_NODE) {
Element childE = (Element) child;
String value = XmlUtils.getElementText(childE);
if (childE.getNodeName().equals("font")) {
HSSFFont font = (HSSFFont) fonts.get(value);
if (font == null) {
throw new ExcelTransformerException("Unable to locate referenced font: " + value);
}
style.setFont(font);
} else if (childE.getNodeName().equals("builtinformat")) {
style.setDataFormat(HSSFDataFormat.getBuiltinFormat(value));
} else if (childE.getNodeName().equals("dataformat")) {
style.setDataFormat(workbook.createDataFormat().getFormat(value));
}
}
}
results.put(name, style);
}
if (LOG.isLoggable(Level.FINE)) {
LOG.exiting(SimpleExcelRenderer.class.getName(), "readStyles");
}
return results;
}
public ExcelSupport() {
super();
workbook = new HSSFWorkbook();
numericCellStyle = workbook.createCellStyle();
numericData = workbook.createDataFormat();
numericCellStyle.setDataFormat(numericData.getFormat("#,##0.00"));
percentageCellStyle = workbook.createCellStyle();
percentageCellStyle.setDataFormat((short) 4);
decimalCellStyle = workbook.createCellStyle();
decimalCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.0#####"));
multiLineTextStyle = workbook.createCellStyle();
multiLineTextStyle.setWrapText(true);
multiLineTextStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
genericStyle = workbook.createCellStyle();
genericStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
dollarStyle = workbook.createCellStyle();
dollarStyle.setDataFormat((short) 8);
headlineStyle = workbook.createCellStyle();
HSSFFont headlineFont = workbook.createFont();
// set font 1 to 12 point type
headlineFont.setFontHeightInPoints((short) 24);
// make it bold
// arial is the default font
headlineFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headlineStyle.setFont(headlineFont);
boldFont = workbook.createFont();
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
boldStyle = workbook.createCellStyle();
boldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
boldStyle.setFont(boldFont);
}
@Test
public void testReadCell() throws Exception {
when(mockCell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC);
final HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
final HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
final Date currentDate = new Date();
cellStyle.setDataFormat(
hssfWorkbook.getCreationHelper().createDataFormat().getFormat("m/d/yyyy h:mm"));
when(mockCell.getCellStyle()).thenReturn(cellStyle);
when(mockCell.getDateCellValue()).thenReturn(currentDate);
final Calendar calendar = calendarConverter.readCell(mockCell);
assertNotNull(calendar);
assertEquals(currentDate.getTime(), calendar.getTimeInMillis());
}
// 创建长字符串样式
public static HSSFCellStyle createLongStringStyle(HSSFWorkbook wb) {
HSSFCellStyle normalStyle = wb.createCellStyle();
// normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
// normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
// normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// HSSFCell.CELL_TYPE_STRING
// normalStyle.set
normalStyle.setAlignment(align);
// normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
normalStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
// normalStyle.setWrapText(true);
return normalStyle;
}
/**
* Inserts a single bulleted item into a cell.
*
* @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
* @param listItem An instance of the String class encapsulating the items text.
* @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
* cell into which the list item will be written.
*/
public void bulletedItemInCell(HSSFWorkbook workbook, String listItem, HSSFCell cell) {
// A format String must be built to ensure that the contents of the
// cell appear as a bulleted item.
HSSFDataFormat format = workbook.createDataFormat();
String formatString = InCellLists.BULLET_CHARACTER + " @";
int formatIndex = format.getFormat(formatString);
// Construct an HSSFCellStyle and set it's data formt to use the
// object created above.
HSSFCellStyle bulletStyle = workbook.createCellStyle();
bulletStyle.setDataFormat((short) formatIndex);
// Set the cells contents and style.
cell.setCellValue(new HSSFRichTextString(listItem));
cell.setCellStyle(bulletStyle);
}
/**
* Fills the report with content
*
* @param worksheet
* @param startRowIndex starting row offset
* @param startColIndex starting column offset
* @param datasource the data source
*/
public static void fillReport(
HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Expense> datasource) {
// Row offset
startRowIndex += 2;
// Create cell style for the body
HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle();
bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
bodyCellStyle.setWrapText(true);
HSSFCellStyle numericStyle = worksheet.getWorkbook().createCellStyle();
numericStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
// Create body
for (int i = startRowIndex; i + startRowIndex - 2 < datasource.size() + 2; i++) {
// Create a new row
HSSFRow row = worksheet.createRow((short) i + 1);
HSSFCell cell0 = row.createCell(startColIndex + 0);
cell0.setCellValue(datasource.get(i - 2).getMember().getUsername());
cell0.setCellStyle(bodyCellStyle);
HSSFCell cell1 = row.createCell(startColIndex + 1);
cell1.setCellValue(datasource.get(i - 2).getExpensetype().getCode());
cell1.setCellStyle(bodyCellStyle);
HSSFCell cell2 = row.createCell(startColIndex + 2);
cell2.setCellValue(datasource.get(i - 2).getDocumentDate());
HSSFCell cell3 = row.createCell(startColIndex + 3);
cell3.setCellValue(datasource.get(i - 2).getCompany());
cell3.setCellStyle(bodyCellStyle);
HSSFCell cell4 = row.createCell(startColIndex + 4);
cell4.setCellValue(datasource.get(i - 2).getDescription());
cell4.setCellStyle(bodyCellStyle);
HSSFCell cell5 = row.createCell(startColIndex + 5);
cell5.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell5.setCellValue(datasource.get(i - 2).getAmount().doubleValue());
cell5.setCellStyle(numericStyle);
}
}
public void testTest1() throws Exception {
HSSFWorkbook _wb = new HSSFWorkbook();
HSSFSheet _s = _wb.createSheet("waterlevel");
for (int i = 0; i < HSSFDataFormat.getNumberOfBuiltinBuiltinFormats(); i++) {
System.out.println(HSSFDataFormat.getBuiltinFormat((short) i));
}
HSSFCellStyle _dateCellStyle = _wb.createCellStyle();
_dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
HSSFRow _rowHeader = _s.createRow(0);
HSSFCell _c = _rowHeader.createCell(0);
_c.setCellStyle(_dateCellStyle);
_c.setCellValue(new Date());
FileOutputStream _stream = new FileOutputStream(new File("d:\\tttt.xls"));
_wb.write(_stream);
}
public static HSSFCellStyle getNewStyles(
HSSFWorkbook workBook, CellStyle cellStyle, HSSFFont font) {
HSSFCellStyle style = workBook.createCellStyle();
// 对齐方式
style.setAlignment(cellStyle.getAlignment());
style.setVerticalAlignment(cellStyle.getVAlignment());
// 设置自动换行
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;
}
/**
* Создает новый стиль в документе Excel который полностью копирует свойства некоторого исходного
* стиля этого же документа. Данный метод используется для того чтобы в последствии поменять в
* новом стиле один или несколько свойств не затрагивая при этом свойства исходного стиля.
*
* @param wb документ Excel.
* @param src стиль ячейки взятый в качестве шаблона.
* @return созданная копия исходного стиля в этом же документе.
*/
public static HSSFCellStyle copyStyle(final HSSFWorkbook wb, final HSSFCellStyle src) {
final HSSFCellStyle dst = wb.createCellStyle();
dst.setAlignment(src.getAlignment());
dst.setBorderBottom(src.getBorderBottom());
dst.setBorderLeft(src.getBorderLeft());
dst.setBorderRight(src.getBorderRight());
dst.setBorderTop(src.getBorderTop());
dst.setBottomBorderColor(src.getBottomBorderColor());
dst.setDataFormat(src.getDataFormat());
dst.setFillForegroundColor(src.getFillForegroundColor());
dst.setFillBackgroundColor(src.getFillBackgroundColor());
dst.setFillPattern(src.getFillPattern());
dst.setFont(src.getFont(wb));
dst.setHidden(src.getHidden());
dst.setIndention(src.getIndention());
dst.setLeftBorderColor(src.getLeftBorderColor());
dst.setLocked(src.getLocked());
dst.setRightBorderColor(src.getRightBorderColor());
dst.setRotation(src.getRotation());
dst.setTopBorderColor(src.getTopBorderColor());
dst.setVerticalAlignment(src.getVerticalAlignment());
dst.setWrapText(src.getWrapText());
return dst;
}
/** Create a library of cell styles */
private HashMap<String, CellStyle> createStyles(HSSFWorkbook wb) {
HashMap<String, CellStyle> styles = new HashMap<String, CellStyle>();
HSSFCellStyle style;
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 18);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFont(titleFont);
styles.put("title", style);
Font monthFont = wb.createFont();
monthFont.setFontHeightInPoints((short) 10);
monthFont.setColor(IndexedColors.WHITE.getIndex());
// header in first sheet
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(monthFont);
style.setWrapText(true);
styles.put("header", style);
// header in second sheet
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1);
style.setFont(monthFont);
styles.put("header1", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1, 0);
style.setFont(monthFont);
styles.put("header1_b", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1, 1);
style.setFont(monthFont);
styles.put("header1_t", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1, 2);
style.setFont(monthFont);
styles.put("header1_l", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1, 3);
style.setFont(monthFont);
styles.put("header1_r", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1, 4);
style.setFont(monthFont);
styles.put("header1_a", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cell", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula_2", style);
return styles;
}
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
// 取得模型数据
SqlRowSet table = (SqlRowSet) model.get("table");
String title = model.get("title").toString();
// 创建工作表和标题
HSSFSheet sheet = workbook.createSheet(title); // 创建工作区
HSSFRow row_title = sheet.createRow(0); // 创建一行引用对象
HSSFFont title_font = workbook.createFont(); // 创建标题的字体
title_font.setFontHeightInPoints((short) 8);
title_font.setFontHeight((short) HSSFFont.BOLDWEIGHT_NORMAL);
title_font.setColor((short) (HSSFFont.COLOR_RED));
HSSFCellStyle title_style = workbook.createCellStyle(); // 创建样式
title_style.setFont(title_font);
HSSFCell cell_title = row_title.createCell(1); // 创建单元格引用对象
cell_title.setCellStyle(title_style);
cell_title.setCellValue(title);
// 创建数据表头
String titles[] = {"学生姓名", "性别", "年龄", "身份证号", "出生日期", "政治面貌", "家庭电话", "家庭地址", "健康状况"};
HSSFRow row = sheet.createRow((short) 1);
HSSFCellStyle items_style = workbook.createCellStyle();
items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);
HSSFFont celltbnamefont = workbook.createFont();
celltbnamefont.setFontHeightInPoints((short) 10);
celltbnamefont.setColor((short) (HSSFFont.COLOR_RED));
items_style.setFont(celltbnamefont);
items_style.setWrapText(true);
for (int i = 0; i < titles.length; i++) {
HSSFCell cell = row.createCell(i);
if (i == 3 || i == 6 || i == 2) {
sheet.setColumnWidth(i, 5335);
} else {
sheet.setColumnWidth(i, 3335);
}
cell.setCellValue(titles[i]);
cell.setCellStyle(items_style);
}
HSSFCellStyle datestyle = workbook.createCellStyle();
HSSFDataFormat df = workbook.createDataFormat();
datestyle.setDataFormat(df.getFormat("yyyy-mm-dd"));
int i = 0;
while (table.next()) {
HSSFRow dataRow = sheet.createRow((short) (i + 2));
for (int j = 0; j < 9; j++) {
HSSFCell cell = dataRow.createCell(j);
String data = table.getString(j + 2);
cell.setCellStyle(datestyle);
cell.setCellValue(data);
}
i++;
}
}
protected Map<Short, HSSFCellStyle> applyStyles(final Report report, final HSSFWorkbook wb) {
final StylePalette palette = report.getPalette();
final Map<Short, HSSFCellStyle> styles = new HashMap<Short, HSSFCellStyle>();
if (report.getTemplate() != null) {
for (final short styleIndex : palette.getStyles().keySet()) {
final HSSFCellStyle style = wb.getCellStyleAt(styleIndex);
if (style == null)
throw new RuntimeException(
"Inconsistent report template. Style not found: " + styleIndex);
styles.put(styleIndex, style);
}
return styles;
}
if (palette.getColors().size() > PaletteRecord.STANDARD_PALETTE_SIZE)
throw new RuntimeException("too many colors on report");
final HSSFPalette pal = wb.getCustomPalette();
for (final Color color : palette.getColors().values()) {
pal.setColorAtIndex(color.getId(), color.getRed(), color.getGreen(), color.getBlue());
}
final Map<Short, HSSFFont> fonts = new HashMap<Short, HSSFFont>();
final HSSFDataFormat formatter = wb.createDataFormat();
for (final Font font : palette.getFonts().values()) {
final HSSFFont f = POIUtils.ensureFontExists(wb, font);
fonts.put(font.getId(), f);
}
for (final CellStyle style : palette.getStyles().values()) {
final short bbc =
style.getBottomBorderColor() != null ? style.getBottomBorderColor().getId() : 0;
final short fbc =
style.getFillBackgroundColor() != null ? style.getFillBackgroundColor().getId() : 0;
final short ffc =
style.getFillForegroundColor() != null ? style.getFillForegroundColor().getId() : 0;
final short lbc = style.getLeftBorderColor() != null ? style.getLeftBorderColor().getId() : 0;
final short rbc =
style.getRightBorderColor() != null ? style.getRightBorderColor().getId() : 0;
final short tbc = style.getTopBorderColor() != null ? style.getTopBorderColor().getId() : 0;
final HSSFCellStyle s = wb.createCellStyle();
s.setAlignment(style.getAlignment());
s.setBorderBottom(style.getBorderBottom());
s.setBorderLeft(style.getBorderLeft());
s.setBorderRight(style.getBorderRight());
s.setBorderTop(style.getBorderTop());
s.setBottomBorderColor(bbc);
s.setDataFormat(formatter.getFormat(style.getDataFormat()));
s.setFillBackgroundColor(fbc);
s.setFillForegroundColor(ffc);
s.setFillPattern(style.getFillPattern());
s.setHidden(style.isHidden());
s.setIndention(style.getIndention());
s.setLeftBorderColor(lbc);
s.setLocked(style.isLocked());
s.setRightBorderColor(rbc);
s.setRotation(style.getRotation());
s.setTopBorderColor(tbc);
s.setVerticalAlignment(style.getVerticalAlignment());
s.setWrapText(style.isWrapText());
s.setFont(fonts.get(style.getFont().getId()));
styles.put(style.getId(), s);
}
return styles;
}
private Map initStyles(HSSFWorkbook wb, short fontHeight) {
Map result = new HashMap();
HSSFCellStyle titleStyle = wb.createCellStyle();
HSSFCellStyle textStyle = wb.createCellStyle();
HSSFCellStyle boldStyle = wb.createCellStyle();
HSSFCellStyle numericStyle = wb.createCellStyle();
HSSFCellStyle numericStyleBold = wb.createCellStyle();
HSSFCellStyle moneyStyle = wb.createCellStyle();
HSSFCellStyle moneyStyleBold = wb.createCellStyle();
HSSFCellStyle percentStyle = wb.createCellStyle();
HSSFCellStyle percentStyleBold = wb.createCellStyle();
result.put("titleStyle", titleStyle);
result.put("textStyle", textStyle);
result.put("boldStyle", boldStyle);
result.put("numericStyle", numericStyle);
result.put("numericStyleBold", numericStyleBold);
result.put("moneyStyle", moneyStyle);
result.put("moneyStyleBold", moneyStyleBold);
result.put("percentStyle", percentStyle);
result.put("percentStyleBold", percentStyleBold);
HSSFDataFormat format = wb.createDataFormat();
// Global fonts
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setColor(HSSFColor.BLACK.index);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setFontHeightInPoints(fontHeight);
HSSFFont fontBold = wb.createFont();
fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontBold.setColor(HSSFColor.BLACK.index);
fontBold.setFontName(HSSFFont.FONT_ARIAL);
fontBold.setFontHeightInPoints(fontHeight);
// Money Style
moneyStyle.setFont(font);
moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyle.setDataFormat(format.getFormat(moneyFormat));
// Money Style Bold
moneyStyleBold.setFont(fontBold);
moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));
// Percent Style
percentStyle.setFont(font);
percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
percentStyle.setDataFormat(format.getFormat(percentFormat));
// Percent Style Bold
percentStyleBold.setFont(fontBold);
percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
percentStyleBold.setDataFormat(format.getFormat(percentFormat));
// Standard Numeric Style
numericStyle.setFont(font);
numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// Standard Numeric Style Bold
numericStyleBold.setFont(fontBold);
numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// Title Style
titleStyle.setFont(font);
titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Standard Text Style
textStyle.setFont(font);
textStyle.setWrapText(true);
// Standard Text Style
boldStyle.setFont(fontBold);
boldStyle.setWrapText(true);
return result;
}
