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();
}
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 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);
}
/** 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;
}
// 创建长字符串样式
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);
}
}
/**
* 取对应单元格类型的值
*
* @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;
}
