private void createWorkbook() {
this.workbook = new HSSFWorkbook();
this.dateCellStyle = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat();
short dateFormat = format.getFormat(getDatePattern());
this.dateCellStyle.setDataFormat(dateFormat);
}
private void writeHeaderRow(Entity entity, HSSFSheet sheet) {
HSSFRow headerRow = sheet.createRow(0);
int colnum = 0;
for (Map.Entry<String, Object> component : getComponents(entity)) {
String componentName = component.getKey();
headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName));
ComponentDescriptor cd = entity.descriptor().getComponent(componentName);
PrimitiveType primitiveType;
if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor)
primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType();
else
throw new UnsupportedOperationException(
"Can only export simple type attributes, "
+ "failed to export "
+ entity.type()
+ '.'
+ cd.getName());
Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class);
String formatString = null;
if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern();
else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern();
else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern();
else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern();
else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern();
if (formatString != null) {
HSSFDataFormat dataFormat = workbook.createDataFormat();
CellStyle columnStyle = workbook.createCellStyle();
columnStyle.setDataFormat(dataFormat.getFormat(formatString));
sheet.setDefaultColumnStyle(colnum, columnStyle);
}
colnum++;
}
}
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);
}
/**
* 测试单元格样式
*
* @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 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();
}
/** Returns the format string, eg $##.##, for the given number format index. */
public String getFormatString(int formatIndex) {
String format = null;
if (formatIndex >= HSSFDataFormat.getNumberOfBuiltinBuiltinFormats()) {
FormatRecord tfr = _customFormatRecords.get(Integer.valueOf(formatIndex));
if (tfr == null) {
logger.log(
POILogger.ERROR, "Requested format at index " + formatIndex + ", but it wasn't found");
} else {
format = tfr.getFormatString();
}
} else {
format = HSSFDataFormat.getBuiltinFormat((short) formatIndex);
}
return format;
}
/**
* 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);
}
public void testDateFormulas() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("testSheet1");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
c.setCellValue(new Date());
c.setCellStyle(cellStyle);
// assertEquals("Checking hour = " + hour, date.getTime().getTime(),
// HSSFDateUtil.getJavaDate(excelDate).getTime());
for (int k = 1; k < 100; k++) {
r = s.createRow(k);
c = r.createCell(0);
c.setCellFormula("A" + (k) + "+1");
c.setCellStyle(cellStyle);
}
HSSFTestDataSamples.writeOutAndReadBack(wb);
}
// ����Excel
public String ExcelExport() throws Exception {
HttpServletRequest request = ServletActionContext.getRequest();
String ids = request.getParameter("ids");
List<Dise> list = new ArrayList<Dise>();
String[] array = ids.split(",");
int[] id = new int[array.length];
for (int i = 0; i < id.length; i++) {
Dise dise = DiseService.findById(Integer.valueOf(array[i]));
list.add(dise);
}
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("ѧ����Ϣ");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("ѧ��");
row.createCell(1).setCellValue("����");
row.createCell(2).setCellValue("����");
row.createCell(3).setCellValue("�Ա�");
row.createCell(4).setCellValue("��ַ");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
for (int i = 1; i <= list.size(); i++) {
Dise stu = list.get(i - 1);
row = sheet.createRow(i);
row.createCell(0).setCellValue(stu.getIds());
row.createCell(1).setCellValue(stu.getName());
row.createCell(2).setCellValue(stu.getA());
row.createCell(3).setCellValue(stu.getB());
row.createCell(4).setCellValue(stu.getC());
}
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
excelFile = new ByteArrayInputStream(baos.toByteArray());
baos.close();
return "excel";
}
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);
}
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 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;
}
public ExcelUtils(List items, ItemSearch itemSearch) {
this.wb = new HSSFWorkbook();
this.sheet = wb.createSheet("jtrac");
this.sheet.setDefaultColumnWidth((short) 12);
HSSFFont fBold = wb.createFont();
fBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
this.csBold = wb.createCellStyle();
this.csBold.setFont(fBold);
this.csDate = wb.createCellStyle();
this.csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
this.items = items;
this.itemSearch = itemSearch;
}
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;
}
/**
* 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 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);
}
/**
* 取对应单元格类型的值
*
* @param c 列数
* @return 单元格的值
*/
private String getCellValue(Cell c) {
String o = null;
switch (c.getCellType()) {
case Cell.CELL_TYPE_BLANK:
o = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
o = String.valueOf(c.getBooleanCellValue());
break;
case CELL_TYPE_FORMULA:
o = String.valueOf(c.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(c)) { // 处理日期格式、时间格式
SimpleDateFormat sdf;
if (c.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else { // 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = c.getDateCellValue();
o = sdf.format(date).equals("1899-12-31") ? "" : sdf.format(date);
} else if (c.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = c.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
o = sdf.format(date);
}
break;
case Cell.CELL_TYPE_STRING:
o = c.getStringCellValue();
break;
default:
o = null;
break;
}
return o;
}
/**
* Constructor HSSF - given a filename this outputs a sample sheet with just a Set of rows/cells.
*
* @param filename
* @param Write
* @exception IOException
*/
public HSSF(String filename, bool Write) {
short rownum = 0;
FileOutputStream out = new FileOutputStream(filename);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.CreateSheet();
HSSFRow r = null;
HSSFCell c = null;
HSSFCellStyle cs = wb.CreateCellStyle();
HSSFCellStyle cs2 = wb.CreateCellStyle();
HSSFCellStyle cs3 = wb.CreateCellStyle();
HSSFFont f = wb.CreateFont();
HSSFFont f2 = wb.CreateFont();
f.SetFontHeightInPoints((short) 12);
f.SetColor((short) 0xA);
f.SetBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
f2.SetFontHeightInPoints((short) 10);
f2.SetColor((short) 0xf);
f2.SetBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cs.SetFont(f);
cs.SetDataFormat(HSSFDataFormat.GetBuiltinFormat("($#,##0_);[Red]($#,##0)"));
cs2.SetBorderBottom(HSSFCellStyle.BORDER_THIN);
cs2.SetFillPattern((short) 1); // Fill w fg
cs2.SetFillForegroundColor((short) 0xA);
cs2.SetFont(f2);
wb.SetSheetName(0, "HSSF Test");
for (rownum = (short) 0; rownum < 300; rownum++) {
r = s.CreateRow(rownum);
if ((rownum % 2) == 0) {
r.SetHeight((short) 0x249);
}
// r.SetRowNum(( short ) rownum);
for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) {
c = r.CreateCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC);
c.SetCellValue(
rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
if ((rownum % 2) == 0) {
c.SetCellStyle(cs);
}
c = r.CreateCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING);
c.SetCellValue("TEST");
s.SetColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20)));
if ((rownum % 2) == 0) {
c.SetCellStyle(cs2);
}
} // 50 Chars divided by 1/20th of a point
}
// draw a thick black border on the row at the bottom using BLANKS
rownum++;
rownum++;
r = s.CreateRow(rownum);
cs3.SetBorderBottom(HSSFCellStyle.BORDER_THICK);
for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
c = r.CreateCell(cellnum, HSSFCell.CELL_TYPE_BLANK);
// c.SetCellValue(0);
c.SetCellStyle(cs3);
}
s.AddMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3));
s.AddMergedRegion(
new Region(
(short) 100, (short) 100,
(short) 110, (short) 110));
// end draw thick black border
// Create a sheet, Set its title then delete it
s = wb.CreateSheet();
wb.SetSheetName(1, "DeletedSheet");
wb.RemoveSheetAt(1);
// end deleted sheet
wb.Write(out);
out.close();
}
@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;
}