/*
* 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 void evaluateWorkbook(Workbook wb) {
FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet s = wb.getSheetAt(i);
for (Row row : s) {
for (Cell cell : row) {
eval.evaluateFormulaCell(cell);
}
}
}
}
private void findData(final Workbook wb) {
this.evaluator = wb.getCreationHelper().createFormulaEvaluator();
Sheet sheet = wb.getSheetAt(zeroPos);
int lastRowNum = sheet.getLastRowNum();
for (int i = startPos; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
List<String> csvLines = Lists.newArrayList();
if (row != null) {
parseRow(row, csvLines);
}
}
}
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);
}
}
protected CreationHelper getHelper(Workbook wb) {
synchronized (syncObj) {
// if( isClosed() )throw new IllegalStateException("is closed");
CreationHelper ch = chelper.get(wb);
if (ch == null) {
ch = wb.getCreationHelper();
chelper.put(wb, ch);
}
return ch;
}
}
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);
}
public static Object getCellValue(Cell cell) {
Object value = new Object();
// Prevent a NullPointerException
if (cell != null) {
if (cell.getHyperlink() != null) {
Workbook workbook = new XSSFWorkbook();
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
Hyperlink link = cell.getHyperlink();
String address = link.getAddress();
if (logger.isTraceEnabled()) {
logger.trace(
"Found a Hyperlink to "
+ cell.getHyperlink().getAddress()
+ " in cell "
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex());
}
cell = evaluator.evaluateInCell(cell);
}
// Depending on the cell type, the value is read using Apache POI methods
switch (cell.getCellType()) {
// String are easy to handle
case Cell.CELL_TYPE_STRING:
logger.trace("Found string " + cell.getStringCellValue());
value = cell.getStringCellValue();
break;
// Since date formatted cells are also of the numeric type, this needs to be processed
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat df = SimpleDateFormat.getDateInstance();
logger.trace("Found date " + df.format(date));
value = date;
} else {
logger.trace("Found general number " + cell.getNumericCellValue());
value = cell.getNumericCellValue();
}
break;
}
} else {
logger.trace("Found cell with NULL value");
}
return value;
}
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);
}
/**
* 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 void readSimpleBlock(
Workbook wb,
int sheetNo,
ExcelBlock blockDefinition,
OgnlStack stack,
ReadStatus readStatus) {
// Simple Block will only care about cells in these Block
Sheet sheet = wb.getSheetAt(sheetNo);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for (ExcelCell cellDefinition : blockDefinition.getCells()) {
Row row = sheet.getRow(cellDefinition.getRow());
Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
try {
Object value = getCellValue(cell, evaluator);
value =
checkValue(
sheetNo,
ExcelUtil.getCellIndex(cellDefinition.getRow(), cellDefinition.getCol()),
value,
cellDefinition,
getPropertyType(stack.peek(), cellDefinition));
logger.debug(
"{}[Checked]:{}",
ExcelUtil.getCellIndex(cellDefinition.getRow(), cellDefinition.getCol()),
value);
stack.setValue(cellDefinition.getDataName(), value);
} catch (ExcelManipulateException e) {
if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
readStatus.addException(e);
} catch (Exception e) {
e.printStackTrace();
readStatus.setStatus(ReadStatus.STATUS_SYSTEM_ERROR);
readStatus.setMessage(e.getMessage());
}
}
}
/**
* 导出时间适配器
*
* @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);
}
@Override
public List<CsmStockImportVo> importStockCsmExcel(MultipartFile file) throws Exception {
List<CsmStockImportVo> importVoList = new ArrayList<CsmStockImportVo>();
byte[] byt = file.getBytes();
InputStream inputStream = new ByteArrayInputStream(byt);
String fileName = file.getOriginalFilename();
String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
Workbook workBook = null;
if (fileType.equals("xls")) {
workBook = new HSSFWorkbook(inputStream);
} else if (fileType.equals("xlsx")) {
workBook = new XSSFWorkbook(inputStream);
}
FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
Sheet sheet = workBook.getSheetAt(0);
int rows = sheet.getLastRowNum();
if (rows > 0) {
for (int i = 1; i <= rows; i++) {
CsmStockImportVo importVo = new CsmStockImportVo();
Integer rowIndex = i + 1;
Row row = sheet.getRow(i);
ThrowExp.isNull(row, "操作失败。第" + rowIndex + "行信息异常");
// Reference no
Cell referenceNoCell = row.getCell(0);
String piecesNo = ExcelUtils.getCellStrValue(workBook, referenceNoCell, rowIndex, 1);
ThrowExp.isNull(piecesNo, "操作失败。第" + rowIndex + "行关联号信息异常");
importVo.setReferenceNo(piecesNo);
// Actual weight
Cell actualWeightCell = row.getCell(1);
if (ExcelUtils.isFullCell(evaluator, actualWeightCell)) {
BigDecimal actualWeight =
ExcelUtils.getCellBigDecimalValue(workBook, actualWeightCell, rowIndex, 2);
importVo.setActualWeight(actualWeight);
}
// Shipper name
Cell shipperNameCell = row.getCell(2);
if (ExcelUtils.isFullCell(evaluator, shipperNameCell)) {
String shipperName = ExcelUtils.getCellStrValue(workBook, shipperNameCell, rowIndex, 3);
importVo.setShipperName(shipperName);
}
// Shipper mobile no
Cell shipperMobileCell = row.getCell(3);
if (ExcelUtils.isFullCell(evaluator, shipperMobileCell)) {
String shipperMobile =
ExcelUtils.getCellStrValue(workBook, shipperMobileCell, rowIndex, 4);
importVo.setShipperMobileNo(shipperMobile);
}
// Shipper company
Cell shipperCompanyCell = row.getCell(4);
if (ExcelUtils.isFullCell(evaluator, shipperCompanyCell)) {
String shipperCompany =
ExcelUtils.getCellStrValue(workBook, shipperCompanyCell, rowIndex, 5);
importVo.setShipperCompanyName(shipperCompany);
}
// Consignee name
Cell consigneeNameCell = row.getCell(5);
String consigneeName = ExcelUtils.getCellStrValue(workBook, consigneeNameCell, rowIndex, 6);
ThrowExp.isNull(consigneeName, "操作失败。第" + rowIndex + "行收货人信息异常");
importVo.setConsigneeName(consigneeName);
// Consignee mobile no
Cell consigneeMobileNoCell = row.getCell(6);
String consigneeMobile =
ExcelUtils.getCellStrValue(workBook, consigneeMobileNoCell, rowIndex, 7);
ThrowExp.isNull(consigneeMobile, "操作失败。第" + rowIndex + "行收货人电话异常");
importVo.setConsigneeMobileNo(consigneeMobile);
// Consignee state
Cell consigneeStateCell = row.getCell(7);
if (ExcelUtils.isFullCell(evaluator, consigneeStateCell)) {
String consigneeState =
ExcelUtils.getCellStrValue(workBook, consigneeStateCell, rowIndex, 8);
importVo.setConsigneeStateName(consigneeState);
}
// Consignee address
Cell consigneeAddressCell = row.getCell(8);
String consigneeAddress =
ExcelUtils.getCellStrValue(workBook, consigneeAddressCell, rowIndex, 9);
ThrowExp.isNull(consigneeAddress, "操作失败。第" + rowIndex + "行收货人地址异常");
importVo.setConsigneeChineseFullAddress(consigneeAddress);
// Goods desc
Cell goodsDescCell = row.getCell(9);
String goodsDesc = ExcelUtils.getCellStrValue(workBook, goodsDescCell, rowIndex, 10);
ThrowExp.isNull(goodsDesc, "操作失败。第" + rowIndex + "行商品名称异常");
importVo.setTotalGoodsDescription(goodsDesc);
// Goods brand
Cell goodsBrandCell = row.getCell(10);
if (ExcelUtils.isFullCell(evaluator, goodsBrandCell)) {
String goodsBrand = ExcelUtils.getCellStrValue(workBook, goodsBrandCell, rowIndex, 11);
importVo.setTotalGoodsBrand(goodsBrand);
}
// Goods unit
Cell goodsUnitCell = row.getCell(11);
if (ExcelUtils.isFullCell(evaluator, goodsUnitCell)) {
String goodsUnit = ExcelUtils.getCellStrValue(workBook, goodsUnitCell, rowIndex, 12);
importVo.setTotalGoodsUnit(goodsUnit);
}
// Goods qty
Cell goodsQtyCell = row.getCell(12);
if (ExcelUtils.isFullCell(evaluator, goodsQtyCell)) {
BigDecimal goodsQty =
ExcelUtils.getCellBigDecimalValue(workBook, goodsQtyCell, rowIndex, 13);
importVo.setTotalQty(goodsQty);
}
// Goods value
Cell goodsValueCell = row.getCell(13);
if (ExcelUtils.isFullCell(evaluator, goodsValueCell)) {
BigDecimal goodsValue =
ExcelUtils.getCellBigDecimalValue(workBook, goodsValueCell, rowIndex, 14);
importVo.setTotalGoodsValue(goodsValue);
}
// Goods value currency
Cell goodsValueCurrencyCell = row.getCell(14);
if (ExcelUtils.isFullCell(evaluator, goodsValueCurrencyCell)) {
String goodsValueCurrency =
ExcelUtils.getCellStrValue(workBook, goodsValueCurrencyCell, rowIndex, 15);
importVo.setTotalGoodsValueCurrency(goodsValueCurrency);
}
// Goods spec
Cell goodsSpecCell = row.getCell(15);
if (ExcelUtils.isFullCell(evaluator, goodsSpecCell)) {
String goodsSpec = ExcelUtils.getCellStrValue(workBook, goodsSpecCell, rowIndex, 16);
importVo.setTotalGoodsSpec(goodsSpec);
}
importVoList.add(importVo);
}
}
return importVoList;
}
public String process2xml() {
String result = "";
// String sourcefilenametag = config.getString("conversion.tags.sourcefilename");
String sourcefilename = file.getAbsolutePath();
try {
sourcefilename = file.getCanonicalPath();
} catch (IOException ioe) {
log.warn("Could not get cannonical path for file!", ioe);
}
String templatedir = config.getString("conversion.template.path");
// result += "<" + sourcefilenametag + "><![CDATA[" + file.toString() + "]]></" +
// sourcefilenametag + ">";
try {
Workbook input = WorkbookFactory.create(file);
log.debug("Processing " + file.toString() + " as an Excel file.");
FormulaEvaluator evaluator = input.getCreationHelper().createFormulaEvaluator();
String templateName = "";
STGroup g = new STRawGroupDir(templatedir, '$', '$');
// Go through each sheet
for (int sheetno = 0; sheetno < input.getNumberOfSheets(); sheetno++) {
Sheet sheet = input.getSheetAt(sheetno);
log.debug("Processing sheet #" + sheetno + ": " + sheet.getSheetName());
LandmarkMatchList lml = new LandmarkMatchList(landmarks.size());
for (Row row : sheet) {
// Go through each cell
for (Cell cell : row) {
String cellvalue = lml.getCellValue(cell, evaluator);
if (!cellvalue.equals("")) {
log.trace(
"Cell value is: "
+ cellvalue
+ " [Row,Col]=["
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex()
+ "]");
log.trace("Matching landmarks: " + landmarks.getLandmarksFor(cellvalue));
// Does Cell contents match a landmark?
lml.addMatches(landmarks.getLandmarksFor(cellvalue), cell);
} else {
log.trace(
"Cell value is blank. [Row,Col]=["
+ cell.getRowIndex()
+ ","
+ cell.getColumnIndex()
+ "]");
}
}
}
templateName = lml.getTemplateName(landmarks);
if (!templateName.equals("")) {
ST st = g.getInstanceOf(templateName);
if (st != null) {
// Set landmark name to value of cell given direction and distance
Hashtable templateValues =
lml.getCellTemplateValues(
templateName, sheet, landmarks, evaluator, sourcefilename, sheetno);
Enumeration templateValuesKeys = templateValues.keys();
while (templateValuesKeys.hasMoreElements()) {
String key = (String) templateValuesKeys.nextElement();
st.add(key, (String) templateValues.get(key));
}
ArrayList<String> sectionNames =
lml.getSectionNamesForTemplate(templateName, landmarks);
for (String sectionName : sectionNames) {
ArrayList<Hashtable> sectionrows =
lml.getSectionRows(templateName, sheet, landmarks, evaluator, sectionName);
st.add(sectionName, sectionrows);
}
result += st.render();
} else {
log.error(
"Unable to load template "
+ templateName
+ ".st! Cannot render data to template while processing "
+ file.toString()
+ " sheet number "
+ sheetno);
}
}
}
} catch (IOException ioe) {
log.error("Unable to open " + file.toString() + " as an Excel file.", ioe);
} catch (InvalidFormatException ife) {
log.error("Unable to open " + file.toString() + ". Format not recognized as Excel. ", ife);
} catch (IllegalArgumentException iae) {
log.error("Unable to open " + file.toString() + " as an Excel file.", iae);
} catch (Exception e) {
log.error("Unable to open " + file.toString() + " as an Excel file.", e);
}
// If cannot render, make sure don't reprocess unnecessarily
if (result.equals("")) {
result = "<Notemplatedata></Notemplatedata>";
}
return result;
}
/**
* Test method for {@link
* sif.IO.spreadsheet.poi.POIWriter#insertTestInput(sif.model.policy.policyrule.DynamicPolicyRule,
* sif.model.inspection.DynamicInspectionRequest)} .
*
* @throws Exception
*/
@Test
public void testInsertTestInput() throws Exception {
FrontDesk desk = FrontDesk.getInstance();
// read policy & rule
File spreadsheetFile = new File(filepath);
DynamicPolicy policy = SifMarshaller.unmarshal(new File(policyPath));
@SuppressWarnings("unchecked")
DynamicInspectionRequest<Workbook> req =
(DynamicInspectionRequest<Workbook>)
desk.requestNewDynamicInspection("TestInputInsertionTest", spreadsheetFile);
FrontDesk.getInstance().scan();
FrontDesk.getInstance().register(policy);
FrontDesk.getInstance().setPolicy(policy);
DynamicPolicyRule rule = (DynamicPolicyRule) policy.getRuleByName("greaterThan0");
assertTrue("No rule with name \"greaterThan0\" was found", rule != null);
// write TestInput in PoiWorkbook
POIWriter writer = new POIWriter();
writer.insertTestInput(rule, req.getExternalSpreadsheet());
Workbook wb = req.getExternalSpreadsheet();
CellReference cellRef = new CellReference(inputCell1);
Sheet sheet2 = wb.getSheet(cellRef.getSheetName());
Row row = sheet2.getRow(cellRef.getRow());
Cell cell = row.getCell(cellRef.getCol());
System.out.println(cell.toString());
assertTrue(cell.toString().equals(expectedValue1.toString()));
cellRef = new CellReference(inputCell2);
sheet2 = wb.getSheet(cellRef.getSheetName());
row = sheet2.getRow(cellRef.getRow());
cell = row.getCell(cellRef.getCol());
System.out.println(cell.toString());
assertTrue(cell.toString().equals(expectedValue2.toString()));
cellRef = new CellReference(resultCell);
sheet2 = wb.getSheet(cellRef.getSheetName());
row = sheet2.getRow(cellRef.getRow());
cell = row.getCell(cellRef.getCol());
// Just for information print result
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
if (cell != null) {
switch (evaluator.evaluateFormulaCell(cell)) {
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
System.out.println(cell.getErrorCellValue());
break;
// CELL_TYPE_FORMULA will never occur
case Cell.CELL_TYPE_FORMULA:
break;
}
}
}
public void process2HTTP(
ExportModel expModel, UISimpleViewExport uis, HttpServletResponse hsr, DateTimeHelper dth) {
try {
String strFileName = uis.getDownloadFileName();
Workbook wbCurrent = null;
if (strFileName.toLowerCase().endsWith(".xlsx")) {
wbCurrent = new XSSFWorkbook();
} else {
wbCurrent = new HSSFWorkbook();
}
HashMap<String, CellStyle> hsCS = new HashMap<String, CellStyle>();
CreationHelper cr = wbCurrent.getCreationHelper();
CellStyle csDate = wbCurrent.createCellStyle();
csDate.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDate().toPattern()));
CellStyle csDateTime = wbCurrent.createCellStyle();
csDateTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDateTime().toPattern()));
CellStyle csTime = wbCurrent.createCellStyle();
csTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFTime().toPattern()));
hsCS.put("DATE", csDate);
hsCS.put("TIME", csTime);
hsCS.put("DATETIME", csDateTime);
Sheet sh = wbCurrent.createSheet("SVE Export");
int nRowCount = 0;
// BUILDING HEADER
if (uis.isIncludeHeader()) {
Row rw = sh.createRow(nRowCount);
int nCol = 0;
for (ExportColumn expColumn : expModel.getColumns()) {
rw.createCell(nCol).setCellValue(expColumn.getColumnName());
nCol++;
}
nRowCount++;
}
// Processing Values
for (ExportDataRow expRow : expModel.getRows()) {
Row rw = sh.createRow(nRowCount);
int nCol = 0;
for (ExportColumn expColumn : expModel.getColumns()) {
Cell clCurrent = rw.createCell(nCol);
setCellValue(expRow.getValue(expColumn.getPosition()), clCurrent, expColumn, hsCS);
nCol++;
}
nRowCount++;
}
for (int nCol = 0; nCol < expModel.getColumns().size(); nCol++) {
sh.autoSizeColumn(nCol);
}
if (strFileName.toLowerCase().endsWith(".xlsx")) {
hsr.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
} else if (strFileName.toLowerCase().endsWith("xls")) {
hsr.setContentType("application/vnd.ms-excel");
} else {
hsr.setContentType("application/octet-stream");
}
hsr.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\"");
OutputStream os = hsr.getOutputStream();
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wbCurrent.write(bos);
bos.writeTo(os);
os.close();
} catch (Exception e) {
ErrorPageBuilder.getInstance()
.processError(hsr, "Error during SVE-Generation (Workbook Export)", e);
}
}
/**
* Read Block in loop condition
*
* @param <T>
* @param wb
* @param sheetNo
* @param blockDefinition
* @param startRow
* @param needCreate
* @param readStatus
* @return
* @throws Exception
*/
private Object readBlock(
Workbook wb, int sheetNo, ExcelBlock blockDefinition, int startRow, ReadStatus readStatus)
throws Exception {
Sheet sheet = wb.getSheetAt(sheetNo);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
if (blockDefinition.getLoopClass() == null) {
Map<String, Object> result = new HashMap<String, Object>();
for (ExcelCell cellDefinition : blockDefinition.getCells()) {
int rowOffSet = cellDefinition.getRow() - blockDefinition.getStartRow();
Row row = sheet.getRow(startRow + rowOffSet);
Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
try {
Object value = getCellValue(cell, evaluator);
value =
checkValue(
sheetNo,
ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
value,
cellDefinition,
getPropertyType(result, cellDefinition));
logger.debug(
"{}[Checked]:{}",
ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
value);
result.put(cellDefinition.getDataName(), value);
} catch (ExcelManipulateException e) {
if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
readStatus.addException(e);
}
}
return result;
} else {
Object result = blockDefinition.getLoopClass().newInstance();
OgnlStack ognlStack = new OgnlStack(result);
for (ExcelCell cellDefinition : blockDefinition.getCells()) {
int rowOffSet = cellDefinition.getRow() - blockDefinition.getStartRow();
Row row = sheet.getRow(startRow + rowOffSet);
Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
try {
Object value = getCellValue(cell, evaluator);
value =
checkValue(
sheetNo,
ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
value,
cellDefinition,
getPropertyType(result, cellDefinition));
logger.debug(
"{}[Checked]:{}",
ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
value);
ognlStack.setValue(cellDefinition.getDataName(), value);
} catch (ExcelManipulateException e) {
if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
readStatus.addException(e);
}
}
return result;
}
}
/**
* Read Excel format file.
*
* @param filename
*/
private void readExcel(String filename) {
try {
FileInputStream fin = new FileInputStream(filename);
if (flgDoubleQuotes) {
delimiter = "\"" + delimiter + "\"";
}
try {
Workbook wb = WorkbookFactory.create(fin);
// HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
String sheetname = sheet.getSheetName();
String fname = sheetname;
if (delimiter.equals("\t")) {
fname = fname + ".tsv";
} else {
fname = fname + ".csv";
}
File file = new File(fname);
FileOutputStream fout = new FileOutputStream(file);
OutputStreamWriter ow = new OutputStreamWriter(fout, charset);
BufferedWriter bw = new BufferedWriter(ow);
for (Iterator<Row> rowIter = sheet.rowIterator(); rowIter.hasNext(); ) {
Row row = rowIter.next();
String tmp = "";
if (flgDoubleQuotes) {
tmp = "\"";
}
if (row != null) {
for (int k = 0; k < row.getLastCellNum(); k++) {
Cell cell = row.getCell(k);
// CellValue celv = evaluator.evaluate(cell);
if (cell == null) {
tmp = tmp + delimiter;
continue;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
tmp = tmp + " " + delimiter;
break;
case Cell.CELL_TYPE_NUMERIC:
tmp = tmp + getNumericValue(cell) + delimiter;
break;
case Cell.CELL_TYPE_STRING:
tmp = tmp + getStringValue(cell) + delimiter;
break;
case Cell.CELL_TYPE_FORMULA:
try {
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
CellValue value = evaluator.evaluate(cell);
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
tmp = tmp + getNumericValue(cell) + delimiter;
} else if (value.getCellType() == Cell.CELL_TYPE_STRING) {
tmp = tmp + getStringValue(cell) + delimiter;
}
} catch (FormulaParseException e) {
// error
tmp = tmp + " " + delimiter;
System.err.println(e.getLocalizedMessage());
} catch (NotImplementedException e) {
// error
tmp = tmp + " " + delimiter;
System.err.println(e.getLocalizedMessage());
}
break;
default:
tmp = tmp + " " + delimiter;
}
}
tmp = tmp.substring(0, tmp.length() - 1);
}
bw.write(tmp + "\n");
}
bw.flush();
bw.close();
ow.close();
fout.close();
System.gc();
}
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
@Test
public void createex() {
Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillPattern(CellStyle.NO_FILL);
Sheet sheet = wb.createSheet("数据日志");
Sheet sheet2 = wb.createSheet("second sheet");
String safeName =
WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "
Sheet sheet3 = wb.createSheet(safeName);
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(
HSSFHeader.font("Stencil-Normal", "Italic")
+ HSSFHeader.fontSize((short) 16)
+ "Right w/ Stencil-Normal Italic font and size 16");
sheet.setColumnWidth(0, 15 * 256); // .autoSizeColumn(0 ); // 调整第一列宽度
sheet.setColumnWidth(1, 17 * 256); // 调整第二列宽度
sheet.setColumnWidth(3, 17 * 256); // 调整第三列宽度
sheet.autoSizeColumn(2); // 调整第四列宽度
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short) 1);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue("ID");
cell.setCellStyle(cellStyle);
// Or do it on one line.
row.createCell(1).setCellValue("表名");
row.createCell(2).setCellValue(createHelper.createRichTextString("操作类型"));
row.createCell(3).setCellValue("操作日期");
row.createCell(4).setCellValue("操作者");
List<Log> list = this.logList();
Row rw;
for (int i = 2; i < list.size(); i++) {
Log log = list.get(i);
rw = sheet.createRow((short) i);
rw.createCell(0).setCellValue(createHelper.createRichTextString(log.getId()));
rw.createCell(1).setCellValue(log.getDataTable());
rw.createCell(2).setCellValue(this.convertActionType(log.getActionType()));
rw.createCell(3)
.setCellValue(DateUtil.convertDateToString(log.getLogDate(), DateUtil.pattern2));
rw.createCell(4).setCellValue(log.getAdminId());
}
String bp = this.getClass().getResource("/").toString();
try {
System.out.println(bp);
File f = new File("workbook.xls");
FileOutputStream fileOut = new FileOutputStream(f);
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
