/**
* ************************************************************************* Public methods
* ************************************************************************
*/
@Override
public void open(String filepath) throws Exception {
this.filepath = filepath;
String author = ini.getValue("Excel", "Author", "Toël Hartmann");
String keywords = ini.getValue("Excel", "Keywords", "");
wb = new HSSFWorkbook();
// Set some properties
wb.createInformationProperties();
wb.getSummaryInformation().setAuthor(author);
wb.getSummaryInformation().setKeywords(keywords);
wb.getSummaryInformation().setCreateDateTime(new Date());
// Create the cell style for column titles
titleStyle = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(font);
String sheetName = getConfigValue("Excel", "sheet", null);
if (sheetName == null || sheetName.isEmpty())
sheetName = FileUtils.getFileNameWithoutExtention(filepath);
sheet = wb.createSheet(sheetName);
if (getConfigValue("Excel", "freezeFirstRow", "true").equalsIgnoreCase("true")) {
sheet.createFreezePane(0, 1);
}
}
/**
* Discription:[写入excel文件,可用于导出,修改excel文件.目前只产生xls文件,并不产生xlsx文件]
*
* @param sheetName 写入excel的sheet名称
* @param dataSet 要写入文件的内容(一个集合). 集合中存放符合javaBean格式的对象。 支持数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param out 要写入的文件流, 可用于导出, 或者写入硬盘文件
* @param map 要导出的字段。（格式：字段名 <-> 字段中文名）其中字段名应该是对应javaBean中的某属性。<br>
* 若该字段为公式的话，必须符合一定的格式：公式标识符formula公式名称&计算起始单元格列&计算终止单元格列&计算单元格行&分隔符（,:）&操作符<br>
* 如：SUM&A&C&2&,&- 它返回的公司是：SUM(A3,-C3)。具体参照： <code>com.integral.util.office.ExcelFormula
* </code>
* @param dateFormat 日期格式(可选)，默认：yyyy-MM-dd
* @author:[代超]
* @throws IOException
* @throws Exception
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
@SuppressWarnings("deprecation")
public void writExcelFile(
String sheetName, Collection<T> dataSet, OutputStream out, Map map, String dateFormat)
throws Exception {
if (dataSet == null || dataSet.size() < 1) {
return;
}
if (map == null || map.size() < 1) {
return;
}
if (dateFormat == null || "".equals(dateFormat.trim())) {
dateFormat = "yyyy-MM-dd";
}
sheetName = sheetName == null ? "" : sheetName;
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth(15);
// 设置表格的样式
CellStyle headerStyle = setRootSheetSysle(workbook);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
// 将第一行冻结
sheet.createFreezePane(1, 1);
if (map != null) {
Object[] obj = map.values().toArray();
for (int i = 0; i < obj.length; i++) {
HSSFCell cell = row.createCell(i);
String header = obj[i] == null ? "" : obj[i].toString();
cell.setCellStyle(headerStyle);
if (obj[i] != null && obj[i].toString().indexOf("formula") > -1) {
// 公式
header = obj[i].toString().replace("formula", "");
}
HSSFRichTextString text = new HSSFRichTextString(header);
cell.setCellValue(text);
}
}
// 遍历数据集合，产生数据行
Iterator<T> it = dataSet.iterator();
for (int i = 1; it.hasNext(); i++) {
row = sheet.createRow(i);
T t = it.next();
// BeanMap bm = new BeanMap(t);
Map b = PropertyUtils.describe(t);
Map c = new TreeMap();
// 排序
c.putAll(b);
writeRow(row, c, map, dateFormat, t);
}
workbook.write(out);
}
private void createDetailSheet() {
sheet = workbook.createSheet("Report");
for (int i = 0; i < lbls.length; i++) {
sheet.setDefaultColumnStyle(i, whiteStyle);
}
// the first row
createRow(_rowcnt++);
int _idx = lbls.length - 6;
setHeaderCell(_idx, null, 3);
// row.getCell(_idx).getCellStyle()
// .setBorderRight(HSSFCellStyle.BORDER_NONE);
setHeaderCell(++_idx, "Similarity Status", 4);
// row.getCell(_idx).getCellStyle()
// .setBorderRight(HSSFCellStyle.BORDER_NONE);
// row.getCell(_idx).getCellStyle()
// .setBorderLeft(HSSFCellStyle.BORDER_NONE);
setHeaderCell(++_idx, null, 2);
// row.getCell(_idx).getCellStyle()
// .setBorderLeft(HSSFCellStyle.BORDER_NONE);
setHeaderCell(++_idx, null, 3);
// row.getCell(_idx).getCellStyle()
// .setBorderRight(HSSFCellStyle.BORDER_NONE);
setHeaderCell(++_idx, "T13y Status", 4);
// row.getCell(_idx).getCellStyle()
// .setBorderRight(HSSFCellStyle.BORDER_NONE);
// row.getCell(_idx).getCellStyle()
// .setBorderLeft(HSSFCellStyle.BORDER_NONE);
setHeaderCell(++_idx, null, 2);
// row.getCell(_idx).getCellStyle()
// .setBorderLeft(HSSFCellStyle.BORDER_NONE);
for (int i = 0; i < lbls.length - 6; i++) {
setHeaderCell(i, lbls[i], 0);
// row.getCell(i).getCellStyle()
// .setBorderBottom(HSSFCellStyle.BORDER_NONE);
}
createRow(_rowcnt++);
// set background color
for (int i = 0; i < lbls.length - 6; i++) {
setHeaderCell(i, null, 1);
// row.getCell(i).getCellStyle()
// .setBorderTop(HSSFCellStyle.BORDER_NONE);
}
for (int i = lbls.length - 6; i < lbls.length; i++) {
setHeaderCell(i, lbls[i], -1);
}
sheet.createFreezePane(0, 2, 0, 2);
for (int i = 0; i < 11; i++) _sims[i] = 0;
}
public IndicatorEntryExcelTemplate(final IndicatorEntryData data, final HSSFWorkbook wb) {
this.data = data;
this.wb = wb;
final HSSFSheet sheet =
wb.createSheet(data.getLocalizedVersion("flexibleElementIndicatorsList"));
utils = new ExcelUtils(wb);
int rowIndex = -1;
int cellIndex = 0;
// empty row
utils.putEmptyRow(sheet, ++rowIndex, 8.65f);
// title
utils.putMainTitle(
sheet,
++rowIndex,
data.getLocalizedVersion("flexibleElementIndicatorsList").toUpperCase(),
data.getNumbOfCols());
// empty row
utils.putEmptyRow(sheet, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT);
// column headers
row = sheet.createRow(++rowIndex);
row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT);
cellIndex = 0;
utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("name"));
utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("code"));
utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("targetValue"));
utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("value"));
// empty row
utils.putEmptyRow(sheet, ++rowIndex, 8.65f);
// freeze pane
sheet.createFreezePane(0, rowIndex);
for (final IndicatorGroup group : data.getIndicators().getGroups()) {
row = sheet.createRow(++rowIndex);
row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT);
putGroupCell(sheet, rowIndex, group.getName());
for (final IndicatorDTO indicator : group.getIndicators()) {
// indicator's detail sheet
createDetailSheet(indicator);
row = sheet.createRow(++rowIndex);
row.setHeightInPoints(ExportConstants.TITLE_ROW_HEIGHT);
// ind name
utils.createLinkCell(
row.createCell(1),
indicator.getName(),
ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName(),
true);
// code
utils.putBorderedBasicCell(sheet, rowIndex, 2, indicator.getCode());
// target
putRightAlignedCell(sheet, rowIndex, 3, indicator.getObjective());
// current value
putRightAlignedCell(sheet, rowIndex, 4, data.getFormattedValue(indicator));
}
}
sheet.setColumnWidth(0, 256 * 2);
sheet.setColumnWidth(1, 256 * 45);
sheet.setColumnWidth(2, 256 * 27);
sheet.setColumnWidth(3, 256 * 27);
sheet.setColumnWidth(4, 256 * 27);
}
