/**
* 初始化函数
*
* @param title 表格标题，传“空值”，表示无标题
* @param headerList 表头列表
*/
private void initialize(String title, List<String> headerList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet("Export");
this.styles = createStyles(wb);
// Create title
if (StringUtils.isNotBlank(title)) {
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(
new CellRangeAddress(
titleRow.getRowNum(),
titleRow.getRowNum(),
titleRow.getRowNum(),
headerList.size() - 1));
}
// Create header
if (headerList == null) {
throw new RuntimeException("headerList not null!");
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length == 2) {
cell.setCellValue(ss[0]);
Comment comment =
this.sheet
.createDrawingPatriarch()
.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
} else {
cell.setCellValue(headerList.get(i));
}
sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i) * 2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
}
private void createSummerySheet() {
sheet0 = workbook.createSheet("Summary");
PrintSetup printSetup = sheet0.getPrintSetup();
printSetup.setLandscape(true);
sheet0.setFitToPage(true);
sheet0.setHorizontallyCenter(true);
// title row
Row titleRow = sheet0.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("File Health Report");
titleCell.setCellStyle(styles.get("title"));
sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
for (int i = 0; i < titles.length; i++) {
Row _row = sheet0.createRow(i + 1);
Cell headerCell = _row.createCell(0);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
_row.setHeightInPoints(20);
}
// finally set column widths, the width is measured in units of 1/256th
// of a character width
sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide
}
private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
throws Exception {
Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
nCell.setCellStyle(tCell.getCellStyle());
tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
nCell.setCellValue(
tCell
.getStringCellValue()
.replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
.replaceAll(
PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
.replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
nCell.setCellStyle(tCell.getCellStyle());
Row tempRow = tempSheet.getRow(4);
Row newRow = newSheet.createRow(4);
for (int c = 0; c < tempRow.getLastCellNum(); c++) {
tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
nCell = newRow.createCell(c, tCell.getCellType());
nCell.setCellValue(tCell.getStringCellValue());
nCell.setCellStyle(tCell.getCellStyle());
}
for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
newSheet.addMergedRegion(mergedRegion);
}
}
private static void createRow(
Sheet sheet,
Map<String, Object> rowData,
List<String> mapping,
List<CellStyle> styles,
int startRowNum) {
Row row = sheet.createRow(startRowNum);
for (int i = 0; i < mapping.size(); i++) {
String name = mapping.get(i);
Object obj = rowData.get(name);
Cell newCell = row.createCell(i);
CellStyle style = styles.get(i);
newCell.setCellStyle(style);
if (obj != null) {
if (obj instanceof Date) {
newCell.setCellValue((Date) obj);
} else if (obj instanceof BigDecimal) {
double dd = ((BigDecimal) obj).doubleValue();
newCell.setCellValue(dd);
} else {
newCell.setCellValue(obj.toString());
}
}
}
}
/**
* 默认的字典输出适配器
*
* @param fieldValue
* @param fieldName
* @param cell
* @throws AdapterException
*/
public void outputDicCodeAdapter(
DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputDicCodeAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
OutputDicConfig config = dataBean.getOutputConfig(fieldName);
String dicCode = config.dicCode();
if (fieldValue == null) {
log.debug("fieldValue is null return");
cell.setCellValue("");
return;
} else {
String byKey = dicCodePool.getByKey(dicCode, fieldValue.toString());
if (byKey == null) {
if (AdapterUtil.getAllMatch(config)) {
throw new AdapterException(Message.DIC_ERROR, cell);
} else {
cell.setCellValue(fieldValue.toString());
}
} else {
cell.setCellValue(byKey);
}
}
}
public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) {
if (copyStyle) {
newCell.setCellStyle(oldCell.getCellStyle());
}
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
newCell.setCellType(Cell.CELL_TYPE_BLANK);
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
/*
* 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");
}
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);
}
}
/**
* Forms report in excel file. Sum weight. Sum volume etc.
*
* @param sheet Sheet where write the items
* @param c Container from where take the info
* @param lastRow last row after all items were written method uses lastRow + 2 to write report in
* next 2 rows after all items of the container were written
*/
private void setReport(Container c, int lastRow) {
String[] reportHeadings = {
"Суммарный вес", // 6
"Суммарный объем", // 7
"Остаток вес", // 8
"Остаток объем" // 9
};
double[] values = {
c.getWeight(),
c.getVolume(),
c.getWeightLimit() - c.getWeight(),
c.getVolumeLimit() - c.getVolume()
};
int dataCell = 5;
int valuesIndex = 0; // index of the array of doubles(weigh, volume, etc)
lastRow++;
Row headings = outputSheet.createRow(lastRow);
lastRow += 2;
Row data = outputSheet.createRow(lastRow);
for (String report : reportHeadings) {
Cell heading = headings.createCell(dataCell);
heading.setCellValue(report);
Cell dataCellValue = data.createCell(dataCell);
dataCellValue.setCellValue(values[valuesIndex]);
dataCell++;
valuesIndex++;
}
}
private void dealWithAmt(Cell cell, String amt) {
if (StringUtil.isEmpty(amt)) {
cell.setCellValue("");
} else {
cell.setCellValue(Double.valueOf(amt));
}
}
public static Cell createCell(Row row, int index, Boolean value) {
Cell cell = row.createCell(index);
if (value == null) {
cell.setCellValue("");
}
cell.setCellValue(value);
return cell;
}
public static Cell createCell(Row row, int index, Date value) {
Cell cell = row.createCell(index);
if (value == null) {
cell.setCellValue("");
return cell;
}
cell.setCellValue(DateUtils.formatDate(value, DateUtils.DateFormatType.DATE_FORMAT_STR_CHINA));
return cell;
}
public static Cell createCell(Row row, int index, RichTextString value) {
Cell cell = row.createCell(index);
if (value == null) {
cell.setCellValue("");
return cell;
}
cell.setCellValue(value);
return cell;
}
public static File writerFile(String[] title, List<String[]> content, String filePath)
throws IOException {
checkDir(filePath);
File f = new File(filePath);
if (!f.exists()) {
f.createNewFile();
}
FileOutputStream out = new FileOutputStream(f);
wb = new SXSSFWorkbook();
setStyle(wb);
Sheet sheet = wb.createSheet("sheet1");
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(20);
int tCount = title.length;
for (int i = 0; i < tCount; i++) {
Cell cell = titleRow.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(title[i]);
sheet.setColumnWidth(i, 5000);
}
int rnum = 1;
for (String[] c : content) {
Row r = sheet.createRow(rnum);
for (int i = 0; i < c.length; i++) {
Cell cell = r.createCell(i);
cell.setCellStyle(contentStyle);
cell.setCellType(Cell.CELL_TYPE_STRING);
sheet.setColumnWidth(i, 5000);
String v = c[i];
if (v == null) {
v = "";
}
cell.setCellValue(v);
}
rnum++;
}
wb.write(out);
out.flush();
wb.close();
out.close();
return f;
}
public void addSummary(int realCnt) {
Cell _c = sheet0.getRow(10).createCell(1);
_c.setCellValue(_t13yb);
_c = sheet0.getRow(11).createCell(1);
_c.setCellValue(_t13ye);
_c = sheet0.getRow(12).createCell(1);
_c.setCellValue(_t13yw);
_c = sheet0.getRow(3).createCell(1);
_c.setCellValue(realCnt);
_c = sheet0.getRow(1).createCell(1);
_c.setCellValue(trueName);
_c = sheet0.getRow(2).createCell(1);
_c.setCellValue(fileContact);
for (int i = 4; i < 8; i++) {
_c = sheet0.getRow(i).createCell(1);
_c.setCellValue(_sims[4 + (10 - i)]);
}
int _less70 = 0;
for (int i = 1; i < 7; i++) _less70 += _sims[i];
_c = sheet0.getRow(8).createCell(1);
_c.setCellValue(_less70);
_c = sheet0.getRow(9).createCell(1);
_c.setCellValue(_sims[0]);
}
protected void exportExtra(Row row, Item item) {
QiyiTop50 rank = (QiyiTop50) item;
Cell cell = row.createCell(2);
cell.setCellValue(rank.getActorsString());
cell = row.createCell(3);
cell.setCellValue(rank.getLastWeek().longValue());
cell = row.createCell(4);
cell.setCellValue(rank.getYesterDay().longValue());
cell = row.createCell(5);
cell.setCellValue(rank.getHistory().longValue());
}
private void addSumBottom() {
for (int i = 0; i < book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
Row row = sheet.createRow(sheet.getLastRowNum() + 1);
row.setHeight((short) (ROW_HEIGHT + 100));
for (int j = 0; j < 1000000; j++) {
if (StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(0).getCell(j)))
&& StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(2).getCell(j)))) {
break;
}
Cell cell = row.createCell(j);
cell.setCellStyle(Style.get(book).SUM);
if (j == 0) {
cell.setCellValue("合计");
} else {
cell.setCellValue(0);
}
if (j >= 7) {
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula(
String.format(
"SUM(%s%s:%s%s)",
CellUtils.convertToABC(j + 1),
5,
CellUtils.convertToABC(j + 1),
sheet.getLastRowNum()));
}
}
sheet.addMergedRegion(
new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 6));
}
for (int i = 0; i < book.getNumberOfSheets(); i++) {
Sheet sheet = book.getSheetAt(i);
for (int j = 4; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
for (int k = 0; k <= row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
if (cell == null) {
continue;
}
if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(k)))) {
cell.setCellStyle(Style.get(book).SUM);
}
}
}
}
}
@SuppressWarnings("unchecked")
private void createBodyRow(Row currentRow, Object[] bodyRow, int[] types) {
Cell currentCell;
// write the section attributes to the row
for (int index = 0; index < bodyRow.length; index++) {
// create the cell
currentCell = currentRow.createCell(index);
// set the format of the cells
currentCell.setCellStyle(bodyStyle);
if (types[index] != Integer.MAX_VALUE) {
currentCell.setCellType(types[index]);
}
// put the value into the cell
switch (types[index]) {
case Cell.CELL_TYPE_NUMERIC:
currentCell.setCellValue((double) bodyRow[index]);
break;
case Cell.CELL_TYPE_FORMULA:
currentCell.setCellFormula(String.valueOf(bodyRow[index]));
break;
case Integer.MAX_VALUE:
HashMap<String, String> cellInfo = (HashMap<String, String>) bodyRow[index];
// sets the hyperlink to be blue and underline
HSSFCellStyle hyperlinkStyle = (HSSFCellStyle) GetBodyCellStyle(workbook);
Font hyperlinkFont = workbook.createFont();
hyperlinkFont.setUnderline(Font.U_SINGLE);
hyperlinkFont.setColor(HSSFColor.BLUE.index);
hyperlinkStyle.setFont(hyperlinkFont);
currentCell.setCellStyle(hyperlinkStyle);
currentCell.setCellValue(cellInfo.get("Label"));
currentCell.setHyperlink(LinkDataToMap(cellInfo));
break; // MAX_VALUE integer will be used to denote hyperlinks
case Cell.CELL_TYPE_STRING:
currentCell.setCellValue(String.valueOf(bodyRow[index]));
break;
default:
throw new UnsupportedOperationException(
"The type "
+ types[index]
+ " is not supported."
+ " Only types 0, 1, 2, and Integer.MAX_VALUE (for hyperlinks)"
+ " are supported. See the javadoc for org.apache.poi.ss.usermodel.Cell "
+ "for more information on the types of cells.");
}
}
}
private Cell headInfo(CellStyle headerStyle, Row row) {
Cell cell = row.createCell(0);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.serialNumber"));
cell = row.createCell(1);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("page.accountDetail.accountNum"));
cell = row.createCell(STR_2);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("page.accountDetail.accountName"));
cell = row.createCell(STR_3);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.tranDate"));
cell = row.createCell(STR_4);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.currency"));
cell = row.createCell(STR_5);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.expenditure"));
cell = row.createCell(STR_6);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.income"));
cell = row.createCell(STR_7);
cell.setCellStyle(headerStyle);
cell.setCellValue(getText("title.accountDetail.balance"));
return cell;
}
private void fillData(
List<Map<String, Object>> detailList,
Sheet sheet1,
CellStyle firstCellStyle,
CellStyle commonCellStyle,
CellStyle amtCellStyle)
throws IOException {
int i = 1;
for (Map<String, Object> detail : detailList) {
if (i > STR_65535) {
break;
}
Row row = sheet1.createRow(i);
Cell cell = row.createCell(0);
cell.setCellStyle(firstCellStyle);
cell.setCellValue(i);
cell = row.createCell(1);
cell.setCellStyle(commonCellStyle);
cell.setCellValue(detail.get("accountNo").toString());
cell = row.createCell(STR_2);
cell.setCellStyle(commonCellStyle);
cell.setCellValue(new String(cstNameHost.getBytes("ISO8859_1"), "UTF8"));
cell = row.createCell(STR_3);
cell.setCellStyle(commonCellStyle);
cell.setCellValue(formatDate(detail.get("transDate").toString()));
cell = row.createCell(STR_4);
cell.setCellStyle(firstCellStyle);
cell.setCellValue(detail.get("currency").toString());
cell = row.createCell(STR_5);
cell.setCellStyle(amtCellStyle);
dealWithAmt(cell, detail.get("debitamt").toString());
cell = row.createCell(STR_6);
cell.setCellStyle(amtCellStyle);
dealWithAmt(cell, detail.get("creditamt").toString());
cell = row.createCell(STR_7);
cell.setCellStyle(amtCellStyle);
dealWithAmt(cell, detail.get("balance").toString());
i++;
}
}
protected void createHeaderExtra(Row row, CellStyle style) {
Cell cell = row.createCell(2);
cell.setCellStyle(style);
cell.setCellValue("Actors");
cell = row.createCell(3);
cell.setCellStyle(style);
cell.setCellValue("LastWeek");
cell = row.createCell(4);
cell.setCellStyle(style);
cell.setCellValue("YesterDay");
cell = row.createCell(5);
cell.setCellStyle(style);
cell.setCellValue("History");
}
private void fillRowWith(Row r, String[] data) {
Cell c = null;
for (String s : data) {
if (c == null) c = r.createCell(0);
else c = r.createCell(c.getColumnIndex() + 1);
try {
double d = Double.valueOf(s);
c.setCellValue(d);
} catch (NumberFormatException e) {
c.setCellValue(s);
}
}
}
public String setTestData(String sheetName, int rowNum, int colNum, String data)
throws EncryptedDocumentException, InvalidFormatException, IOException {
FileInputStream fis = new FileInputStream(filepath);
Workbook wb = WorkbookFactory.create(fis);
Sheet sh = wb.getSheet(sheetName);
Row row = sh.getRow(rowNum);
Cell cel = row.createCell(colNum);
cel.setCellType(Cell.CELL_TYPE_STRING);
cel.setCellValue(data);
FileOutputStream fos = new FileOutputStream(filepath);
cel.setCellValue(data);
wb.write(fos);
wb.close();
return data;
}
public void format() {
wb = new XSSFWorkbook();
Map styles = createStyles(wb);
Sheet sheet = wb.createSheet("Loan Calculator");
sheet.setPrintGridlines(false);
sheet.setDisplayGridlines(false);
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
sheet.setColumnWidth(0, 768);
sheet.setColumnWidth(1, 768);
sheet.setColumnWidth(2, 2816);
sheet.setColumnWidth(3, 3584);
sheet.setColumnWidth(4, 3584);
sheet.setColumnWidth(5, 3584);
sheet.setColumnWidth(6, 3584);
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(35F);
for (int i = 1; i <= 7; i++)
titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));
Cell titleCell = titleRow.getCell(2);
titleCell.setCellValue("Simple");
}
private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheet1");
CellStyle headerStyle = getHeaderStyle(wb);
CellStyle firstCellStyle = getFirsetCellStyle(wb);
CellStyle commonCellStyle = getCommonCellStyle(wb);
CellStyle amtCellStyle = getAmtCellStyle(wb);
for (int i = 0; i < LENGTH_9; i++) {
sheet1.setColumnWidth(i, STR_15 * STR_256);
}
// 表头
Row row = sheet1.createRow(0);
row.setHeightInPoints(STR_20);
Cell cell = headInfo(headerStyle, row);
if (detailList.size() == 0) {
row = sheet1.createRow(1);
cell = row.createCell(0);
cell.setCellValue(NO_RECORD);
} else {
fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
wb.write(outputStream);
InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
outputStream.close();
return inputStream;
}
/**
* {@inheritDoc}
*
* @see
* org.eclipse.emfforms.spi.spreadsheet.core.converter.EMFFormsSpreadsheetValueConverter#setCellValue(org.apache.poi.ss.usermodel.Cell,
* java.lang.Object, org.eclipse.emf.ecore.EStructuralFeature,
* org.eclipse.emf.ecp.view.spi.context.ViewModelContext)
*/
@Override
public void setCellValue(
Cell cell,
Object fromObject,
EStructuralFeature eStructuralFeature,
ViewModelContext viewModelContext)
throws EMFFormsConverterException {
final EDataType eDataType = EAttribute.class.cast(eStructuralFeature).getEAttributeType();
final EFactory eFactory = eDataType.getEPackage().getEFactoryInstance();
final StringBuilder result = new StringBuilder();
for (final Object value : (List<?>) fromObject) {
if (result.length() != 0) {
result.append(SEPARATOR);
}
result.append(eFactory.convertToString(eDataType, value));
}
String valueString = result.toString();
if (isDecimalNumber(eDataType.getInstanceClass())) {
valueString =
valueString.replace(
'.',
DecimalFormatSymbols.getInstance(localeProvider.getLocale()).getDecimalSeparator());
}
cell.setCellValue(valueString);
cell.setCellStyle(
(CellStyle) viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT));
}
/**
* write excel sheet, specified value to specified cell.
*
* @param sheetName excel sheet name
* @param row row index which to be changed
* @param col column index which to be changed
* @param value value to be put into cell
* @throws RuntimeException
*/
public void setExcelValue(String sheetName, int row, int col, String value) {
Workbook workBook = null;
try {
if (new File(fileName).exists()) {
workBook = getWorkBook(new FileInputStream(fileName), false);
} else {
workBook = getWorkBook(null, false);
}
xlSheet = workBook.getSheet(sheetName);
if (xlSheet == null) {
xlSheet = workBook.createSheet(sheetName);
}
xlRow = xlSheet.getRow(row - 1);
if (xlRow == null) {
xlRow = xlSheet.createRow((short) row - 1);
}
xlCell = xlRow.getCell(col - 1);
if (xlCell == null) {
xlCell = xlRow.createCell(col - 1);
}
xlCell.setCellType(1); // set cell type as string
xlCell.setCellValue(value);
FileOutputStream fileOut = new FileOutputStream(fileName);
workBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
LOG.error(e);
throw new RuntimeException("set excel value failed:" + e.getMessage());
}
}
@SuppressWarnings("rawtypes")
private void generateHeaderRow(
HSSFWorkbook workBook, Sheet sheet, JSONArray columnConfigJSONObject) {
CellStyle center = workBook.createCellStyle();
center.setAlignment(HSSFCellStyle.ALIGN_CENTER);
int columnType = Cell.CELL_TYPE_STRING;
String col = null, text = null;
short width = 15;
sheet.setDefaultColumnWidth(width);
Row headerRow = createRow(sheet, headerRowNum);
for (short i = 0; i < columnConfigJSONObject.length(); i++) {
JSONObject headerObj = columnConfigJSONObject.getJSONObject(i);
for (Iterator iterator = headerObj.keys(); iterator.hasNext(); ) {
String key = (String) iterator.next();
if ("columnType".equals(key)) {
// if(isNumber(headerObj.getString(key))) {
// columnType = Cell.CELL_TYPE_NUMERIC;
// }
} else {
col = key;
text = headerObj.getString(key);
}
}
headerIdArray.add(col);
Cell cell = createCell(headerRow, i, columnType);
cell.setCellValue(new HSSFRichTextString(text));
cell.setCellStyle(center);
}
}
@Override
protected Workbook indexExcel(Map<String, String> params, Workbook excel) {
List<Map<String, Object>> list = service.financialIndexData_02(params);
Sheet sheet = excel.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
cell.setCellValue(params.get("statMonth") + reportName);
row = sheet.getRow(2);
if (list != null && list.size() > 0) {
for (Map<String, Object> map : list) {
sheet = PoiUtil.copyLastRow(sheet, false);
row = sheet.getRow(sheet.getLastRowNum() - 1);
row.getCell(0).setCellValue(map.get("ENT_NAME").toString());
row.getCell(1).setCellValue(StringUitl.obj2doule(map.get("TJ1")));
row.getCell(2).setCellValue(StringUitl.obj2doule(map.get("TB1")));
row.getCell(3).setCellValue(StringUitl.obj2doule(map.get("TJ2")));
row.getCell(4).setCellValue(StringUitl.obj2doule(map.get("TB2")));
row.getCell(5).setCellValue(StringUitl.obj2doule(map.get("TJ3")));
row.getCell(6).setCellValue(StringUitl.obj2doule(map.get("TB3")));
}
}
sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));
return excel;
}
/**
* Called to update the embedded Excel workbook. As the format and structire of the workbook are
* known in advance, all this code attempts to do is write a new value into the first cell on the
* first row of the first worksheet. Prior to executing this method, that cell will contain the
* value 1.
*
* @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException Rather than use the specific
* classes (HSSF/XSSF) to handle the embedded workbook this method uses those defeined in the
* SS stream. As a result, it might be the case that a SpreadsheetML file is opened for
* processing, throwing this exception if that file is invalid.
* @throws java.io.IOException Thrown if a problem occurs in the underlying file system.
*/
public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
PackagePart pPart = null;
Iterator<PackagePart> pIter = null;
List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
pIter = embeddedDocs.iterator();
while (pIter.hasNext()) {
pPart = pIter.next();
if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
|| pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {
// Get an InputStream from the pacage part and pass that
// to the create method of the WorkbookFactory class. Update
// the resulting Workbook and then stream that out again
// using an OutputStream obtained from the same PackagePart.
workbook = WorkbookFactory.create(pPart.getInputStream());
sheet = workbook.getSheetAt(SHEET_NUM);
row = sheet.getRow(ROW_NUM);
cell = row.getCell(CELL_NUM);
cell.setCellValue(NEW_VALUE);
workbook.write(pPart.getOutputStream());
}
}
// Finally, write the newly modified Word document out to file.
this.doc.write(new FileOutputStream(this.docFile));
}
}
