/*
* Don't hardcode US Locale for datetimes in Excel. Experiment a bit.
*/
public static void issue26() throws InvalidFormatException, IOException {
System.out.println("Testing issue26 =================================");
Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("Sheet1");
Row row = sheet.createRow(0);
// first cell
Cell cell0 = row.createCell(0);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
cell0.setCellStyle(cellStyle);
// second cell using another format with French locale
CellStyle cs2 = wb.createCellStyle();
String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
System.out.println(
"Converted pattern in FRENCH locale is: "
+ DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));
DataFormat df = wb.createDataFormat();
cs2.setDataFormat(df.getFormat(excelFormatPattern));
Cell cell1 = row.createCell(1);
cell1.setCellValue(new Date(1385903303326L));
cell1.setCellStyle(cs2);
FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
wb.write(out);
out.close();
System.out.println("Wrote /tmp/issue26_out.xlsx");
}
@Override
public void createCellStyles(Workbook wb) {
if (wb == null) {
return;
}
CreationHelper createHelper = wb.getCreationHelper();
// Headers
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.WHITE.index);
headerStyle.setFont(font);
cellStyleMap.put(STYLE_HELP_HEADER, headerStyle);
cellStyleMap.put(STYLE_RECORD_HEADER, headerStyle);
cellStyleMap.put(STYLE_LOCATION_HEADER, headerStyle);
cellStyleMap.put(STYLE_TAXONOMY_HEADER, headerStyle);
// Date and Time
CellStyle dateStyle = wb.createCellStyle();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("D MMM YYYY"));
cellStyleMap.put(STYLE_DATE_CELL, dateStyle);
CellStyle timeStyle = wb.createCellStyle();
timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("HH:MM"));
cellStyleMap.put(STYLE_TIME_CELL, timeStyle);
}
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style;
// Title Style
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(titleFont);
style.setWrapText(true);
styles.put("title", style);
// Sub Title Style
Font subTitleFont = wb.createFont();
subTitleFont.setFontHeightInPoints((short) 14);
subTitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(subTitleFont);
style.setWrapText(true);
styles.put("subTitle", style);
Font headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 11);
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headerFont);
styles.put("header", 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);
return styles;
}
/** Create a library of cell styles */
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle 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) 11);
monthFont.setColor(IndexedColors.WHITE.getIndex());
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
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);
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;
}
private Result populateLoansTable(Sheet savingsTransactionSheet) {
Result result = new Result();
Workbook workbook = savingsTransactionSheet.getWorkbook();
CellStyle dateCellStyle = workbook.createCellStyle();
short df = workbook.createDataFormat().getFormat("dd/mm/yy");
dateCellStyle.setDataFormat(df);
int rowIndex = 1;
Row row;
Collections.sort(savings, CompactSavingsAccount.ClientNameComparator);
try {
for (CompactSavingsAccount savingsAccount : savings) {
row = savingsTransactionSheet.createRow(rowIndex++);
writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName());
writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
writeDate(
LOOKUP_SAVINGS_ACTIVATION_DATE_COL,
row,
savingsAccount.getTimeline().getActivatedOnDate().get(2)
+ "/"
+ savingsAccount.getTimeline().getActivatedOnDate().get(1)
+ "/"
+ savingsAccount.getTimeline().getActivatedOnDate().get(0),
dateCellStyle);
}
} catch (Exception e) {
result.addError(e.getMessage());
logger.error(e.getMessage());
}
return result;
}
// ����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 insertDataToExcel(int numRow, Object[] object) {
try {
if (null != wb.getSheetAt(0)) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.getRow((short) numRow);
if (row == null) row = aSheet.createRow((short) numRow);
for (int i = 0; i < object.length; i++) {
Cell csCell = row.createCell((short) i);
CellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
csCell.setCellStyle(style);
if (object[i] != null) csCell.setCellValue(object[i].toString());
else csCell.setCellValue("0");
}
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
private CellStyle getAmtCellStyle(Workbook wb) {
Font font = wb.createFont();
font.setColor(IndexedColors.GREEN.getIndex());
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
getAllAroundBorder(cellStyle);
return cellStyle;
}
protected CellStyle createMetaDataCellStyle(Workbook wb) {
synchronized (syncObj) {
// if( isClosed() )throw new IllegalStateException("is closed");
CellStyle cs = wb.createCellStyle();
Font font = wb.createFont();
font.setBold(true);
cs.setFont(font);
return cs;
}
}
private void makeCellAutosizeAndBold(Workbook wb, Row row) {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
for (int i = 0; i < row.getLastCellNum(); i++) {
row.getCell(i).setCellStyle(style);
}
makeCellsAutosize(wb, row);
}
private CellStyle getHeaderStyle(Workbook wb) {
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFont(font);
getAllAroundBorder(headerStyle);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
return headerStyle;
}
private static void setStyle(Workbook wb) {
titleFont = wb.createFont();
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
titleStyle = wb.createCellStyle();
titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
titleStyle.setBorderRight(CellStyle.BORDER_THIN);
titleStyle.setBorderTop(CellStyle.BORDER_THIN);
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
titleStyle.setFont(titleFont);
contentStyle = wb.createCellStyle();
contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
contentStyle.setBorderRight(CellStyle.BORDER_THIN);
contentStyle.setBorderTop(CellStyle.BORDER_THIN);
contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
private void makeTemporalCell(Cell retVal, Date cellObj, String format) {
CreationHelper creationHelper = workbook.getCreationHelper();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(format));
retVal.setCellStyle(cellStyle);
if (null != cellObj) {
Calendar calendar = Calendar.getInstance();
calendar.setTime((Date) cellObj);
retVal.setCellValue(calendar);
}
}
/**
* 创建表格样式
*
* @param wb 工作薄对象
* @return 样式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(titleFont);
styles.put("title", style);
style = wb.createCellStyle();
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
style.setFont(dataFont);
styles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_LEFT);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_CENTER);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_RIGHT);
styles.put("data3", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
// style.setWrapText(true);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
return styles;
}
/**
* This is used to get the cell style of the header cells. If you want a custom style for the
* column header cells, customize this method.
*
* @param theWorkbook the Excel workbook we are working in
* @return the cell style for the header
*/
public static CellStyle GetHeaderCellStyle(Workbook theWorkbook) {
CellStyle HeaderStyle = theWorkbook.createCellStyle();
Font HeaderFont = theWorkbook.createFont();
/*sets the style and font appropriately
* NOTE: This is the code block where customization should be done
*/
HeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
HeaderStyle.setFont(HeaderFont);
return HeaderStyle;
}
private CellStyle buildCellStyle() {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
return cellStyle;
}
/**
* This is used to get the cell style of the body cells. If you want a custom style for the cells
* that say the attribute values of the entries in the list, customize this method.
*
* @param theWorkbook the Excel workbook we are working in
* @return the cell style for the body
*/
public static CellStyle GetBodyCellStyle(Workbook theWorkbook) {
CellStyle theStyle = theWorkbook.createCellStyle();
/*set the style to wrap the text and center vertical alignment
* NOTE: This is the code block where customization should be done
*/
theStyle.setWrapText(true);
theStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
theStyle.setAlignment(CellStyle.ALIGN_LEFT);
return theStyle;
}
public void outputIntAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputIntAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
cell.setCellValue(NumberUtils.format(fieldValue, 0));
cell.setCellStyle(cellStyle);
}
/**
* 创建单元格的样式
*
* @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;
}
/**
* Discription:[设置文件内容的显示样式]
*
* @param wb
* @return
* @author:[代超]
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public CellStyle setContentSheetSysle(Workbook wb) {
CellStyle style = wb.createCellStyle();
// 生成并设置另一个样式
style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成一个字体
Font font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
/**
* Discription:[设置文件头显示样式]
*
* @param wb
* @return
* @author:[代超]
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public CellStyle setRootSheetSysle(Workbook wb) {
CellStyle style = wb.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 对齐方式：居中对齐
// 生成一个字体
Font font = wb.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
public void outputNumericAdapter(
DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputNumericAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
OutputNumericConfig config = dataBean.getOutputConfig(fieldName);
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
StringBuilder format = new StringBuilder("0");
for (int i = 0; i < config.floatCount(); i++) {
if (i == 0) format.append(".");
format.append("0");
}
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format.toString()));
cell.setCellValue(NumberUtils.format(fieldValue, config.floatCount()));
cell.setCellStyle(cellStyle);
}
private CellStyle getStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 设置单元格字体
Font headerFont = workbook.createFont(); // 字体
headerFont.setFontHeightInPoints((short) 14);
headerFont.setColor(HSSFColor.BLACK.index);
headerFont.setFontName("宋体");
style.setFont(headerFont);
style.setWrapText(true);
// 设置单元格边框及颜色
style.setBorderBottom((short) 1);
style.setBorderLeft((short) 1);
style.setBorderRight((short) 1);
style.setBorderTop((short) 1);
style.setWrapText(true);
return style;
}
/**
* 导出时间适配器
*
* @param fieldValue
* @param fieldName
* @return
* @throws AdapterException
*/
public void outputDateAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputDateAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
Date date = null;
if (fieldValue == null) {
log.debug("fieldValue is null return");
cell.setCellValue("");
return;
} else if (fieldValue instanceof Date) {
log.debug("fieldValue instanceof Date ");
date = (Date) fieldValue;
} else if (fieldValue instanceof String) {
log.debug("fieldValue instanceof String ");
InputDateConfig config = dataBean.getInputConfig(fieldName);
try {
date = DateUtil.formatToDate((String) fieldValue, config.format());
} catch (ParseException e) {
throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
}
} else if (fieldValue instanceof Long) {
log.debug("fieldValue instanceof Long ");
date = new Date((Long) fieldValue);
} else {
throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
}
Workbook workbook = cell.getSheet().getWorkbook();
OutputDateConfig outputConfig = dataBean.getOutputConfig(fieldName);
CellStyle cellStyle = cell.getCellStyle();
if (cellStyle == null) cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(outputConfig.format()));
cell.setCellStyle(cellStyle);
cell.setCellValue(date);
}
public void setCellMoney(int numRow, int numCol) {
try {
if (wb.getSheetAt(0) != null) {
Sheet aSheet = wb.getSheetAt(0);
DataFormat format = wb.createDataFormat();
Row row = aSheet.getRow((short) numRow);
Cell csCell = row.getCell((short) numCol);
CellStyle style = wb.createCellStyle();
style.setDataFormat(format.getFormat("0.00"));
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
csCell.setCellStyle(style);
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
/**
* Creates a new instance.
*
* @param workbook target workbook
* @throws IllegalArgumentException if some parameters were {@code null}
*/
WorkbookInfo(Workbook workbook) {
if (workbook == null) {
throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$
}
this.workbook = workbook;
Font font = workbook.createFont();
commonStyle = workbook.createCellStyle();
commonStyle.setFont(font);
commonStyle.setBorderTop(BorderStyle.THIN);
commonStyle.setBorderBottom(BorderStyle.THIN);
commonStyle.setBorderLeft(BorderStyle.THIN);
commonStyle.setBorderRight(BorderStyle.THIN);
titleStyle = workbook.createCellStyle();
titleStyle.cloneStyleFrom(commonStyle);
titleStyle.setLocked(true);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
titleStyle.setAlignment(HorizontalAlignment.CENTER);
CreationHelper helper = workbook.getCreationHelper();
DataFormat df = helper.createDataFormat();
dataStyle = workbook.createCellStyle();
dataStyle.cloneStyleFrom(commonStyle);
dateDataStyle = workbook.createCellStyle();
dateDataStyle.cloneStyleFrom(commonStyle);
dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$
timeDataStyle = workbook.createCellStyle();
timeDataStyle.cloneStyleFrom(commonStyle);
timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$
datetimeDataStyle = workbook.createCellStyle();
datetimeDataStyle.cloneStyleFrom(commonStyle);
datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$
}
private CellStyle getFirsetCellStyle(Workbook wb) {
CellStyle cellStyle = wb.createCellStyle();
getAllAroundBorder(cellStyle);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
return cellStyle;
}
private CellStyle getCommonCellStyle(Workbook wb) {
CellStyle cellStyle = wb.createCellStyle();
getAllAroundBorder(cellStyle);
return cellStyle;
}
private void saveExcelPoject(File file) throws IOException {
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("timeplan");
// Заголовок в 0 строке
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Филиал");
cell = row.createCell(1);
cell.setCellValue("Город");
Calendar cal = Calendar.getInstance();
cal.set(2017, 0, 5); // Начальная дата проекта
SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yy");
for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
cell = row.createCell(i + 2);
cell.setCellValue(sdf.format(cal.getTime()));
cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
}
// sheet.setColumnWidth(0, 256);
// Цвета ячеек
CellStyle[] styles = new CellStyle[6];
styles[0] = wb.createCellStyle();
styles[0].setFillForegroundColor(HSSFColor.RED.index);
styles[0].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[1] = wb.createCellStyle();
styles[1].setFillForegroundColor(HSSFColor.GREEN.index);
styles[1].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[2] = wb.createCellStyle();
styles[2].setFillForegroundColor(HSSFColor.BLUE.index);
styles[2].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[3] = wb.createCellStyle();
styles[3].setFillForegroundColor(HSSFColor.ROSE.index);
styles[3].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[4] = wb.createCellStyle();
styles[4].setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
styles[4].setFillPattern(FillPatternType.SOLID_FOREGROUND);
styles[5] = wb.createCellStyle();
styles[5].setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
styles[5].setFillPattern(FillPatternType.SOLID_FOREGROUND);
short rowIdx = 0;
for (Region region : this.regions) {
row = sheet.createRow(++rowIdx);
cell = row.createCell(0);
cell.setCellValue(region.filial);
cell = row.createCell(1);
cell.setCellValue(region.name);
cal = Calendar.getInstance();
cal.set(2017, 0, 5); // Начальная дата проекта
for (int i = 0; i < 3 * 52; i++) { // Счетчик по неделям
short color = region.getDateColorIndex(cal.getTime());
if (color >= 0) {
cell = row.createCell(i + 2);
cell.setCellStyle(styles[color]);
}
cal.add(Calendar.WEEK_OF_YEAR, 1); // Следующая неделя
}
}
try (FileOutputStream fileOut = new FileOutputStream(file)) {
wb.write(fileOut);
}
}
@Test
public void generateExcelPoiReport() throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("Accounts");
int rowNum = 1;
Row row = sheet.createRow(rowNum++);
Font font = wb.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName(FONT_TYPE);
font.setColor(FONT_COLOR_TITLE);
/** * Header **** */
CellStyle style = wb.createCellStyle();
style.setFont(font);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(BACKGROUND_COLOR);
Cell cell = row.createCell((short) 1);
cell.setCellValue("Account Report per Beneficiary");
cell.setCellStyle(style);
sheet.addMergedRegion(
new CellRangeAddress(
1, // first row (0-based)
2, // last row (0-based)
1, // first column (0-based)
16 // last column (0-based)
));
/** * Body **** */
font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName(FONT_TYPE);
font.setColor(FONT_COLOR);
style = wb.createCellStyle();
style.setFont(font);
rowNum = rowNum + 3;
List<Account> accounts = accountManager.getAllAccounts();
for (Account account : accounts) {
row = sheet.createRow(rowNum++);
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell.setCellValue(account.getName());
cell = row.createCell((short) 2);
cell.setCellValue(account.getNumber());
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue(account.getEntityId());
cell.setCellStyle(style);
}
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);
sheet.autoSizeColumn(3);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("C:/Temp/workbook.xls");
wb.write(fileOut);
fileOut.close();
}