public static Date getBaseDateFromExcelWithPoi(File file) {
InputStream in = null;
try {
in = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(in);
Sheet sheet = workbook.getSheetAt(0);
Name name = workbook.getName("雷線基準日");
CellReference cellRef = new CellReference(name.getRefersToFormula());
Row row = sheet.getRow(cellRef.getRow());
Cell baseDateCell = row.getCell(cellRef.getCol());
// System.out.println("cellが日付か:"
// + PoiUtil.isCellDateFormatted(baseDateCell));
Date baseDate = baseDateCell.getDateCellValue();
return baseDate;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (in != null)
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/** Verify that FormulaParser handles defined names beginning with underscores, see Bug #49640 */
public void testFormulasWithUnderscore() {
HSSFWorkbook wb = new HSSFWorkbook();
Name nm1 = wb.createName();
nm1.setNameName("_score1");
nm1.setRefersToFormula("A1");
Name nm2 = wb.createName();
nm2.setNameName("_score2");
nm2.setRefersToFormula("A2");
Sheet sheet = wb.createSheet();
Cell cell = sheet.createRow(0).createCell(2);
cell.setCellFormula("_score1*SUM(_score1+_score2)");
assertEquals("_score1*SUM(_score1+_score2)", cell.getCellFormula());
}
private void setNames(Sheet worksheet) {
Workbook savingsTransactionWorkbook = worksheet.getWorkbook();
ArrayList<String> officeNames =
new ArrayList<String>(Arrays.asList(officeSheetPopulator.getOfficeNames()));
// Office Names
Name officeGroup = savingsTransactionWorkbook.createName();
officeGroup.setNameName("Office");
officeGroup.setRefersToFormula("Offices!$B$2:$B$" + (officeNames.size() + 1));
// Clients Named after Offices
for (Integer i = 0; i < officeNames.size(); i++) {
Integer[] officeNameToBeginEndIndexesOfClients =
clientSheetPopulator.getOfficeNameToBeginEndIndexesOfClients().get(i);
Name name = savingsTransactionWorkbook.createName();
if (officeNameToBeginEndIndexesOfClients != null) {
name.setNameName("Client_" + officeNames.get(i));
name.setRefersToFormula(
"Clients!$B$"
+ officeNameToBeginEndIndexesOfClients[0]
+ ":$B$"
+ officeNameToBeginEndIndexesOfClients[1]);
}
}
// Counting clients with active savings and starting and end addresses of cells for naming
HashMap<String, Integer[]> clientNameToBeginEndIndexes = new HashMap<String, Integer[]>();
ArrayList<String> clientsWithActiveSavings = new ArrayList<String>();
int startIndex = 1, endIndex = 1;
String clientName = "";
for (int i = 0; i < savings.size(); i++) {
if (!clientName.equals(savings.get(i).getClientName())) {
endIndex = i + 1;
clientNameToBeginEndIndexes.put(clientName, new Integer[] {startIndex, endIndex});
startIndex = i + 2;
clientName = savings.get(i).getClientName();
clientsWithActiveSavings.add(clientName);
}
if (i == savings.size() - 1) {
endIndex = i + 2;
clientNameToBeginEndIndexes.put(clientName, new Integer[] {startIndex, endIndex});
}
}
// Account Number Named after Clients
for (int j = 0; j < clientsWithActiveSavings.size(); j++) {
Name name = savingsTransactionWorkbook.createName();
name.setNameName("Account_" + clientsWithActiveSavings.get(j).replaceAll(" ", "_"));
name.setRefersToFormula(
"SavingsTransaction!$Q$"
+ clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[0]
+ ":$Q$"
+ clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[1]);
}
// Payment Type Name
Name paymentTypeGroup = savingsTransactionWorkbook.createName();
paymentTypeGroup.setNameName("PaymentTypes");
paymentTypeGroup.setRefersToFormula(
"Extras!$D$2:$D$" + (extrasSheetPopulator.getPaymentTypesSize() + 1));
}
@Override
public byte[] getImportTemplate(Boolean editProductType) throws IOException {
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet productSheet = workBook.createSheet(PRODUCT_SHEETNAME);
XSSFSheet typeSheet = createTypeSheet(workBook);
if (!(true == editProductType)) {
workBook.setSheetHidden(
workBook.getSheetIndex(typeSheet), XSSFWorkbook.SHEET_STATE_VERY_HIDDEN);
}
XSSFRow titleRow = productSheet.createRow(0);
List<TransformSetting> transSettings = new TransformSettingFactory().getProductSetting();
XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
for (int i = 0; i < transSettings.size(); i++) {
TransformSetting setting = transSettings.get(i);
XSSFCell titleCell = null;
titleCell = titleRow.createCell(i, Cell.CELL_TYPE_STRING);
String cellValue = setting.getDispName();
titleCell.setCellValue(cellValue);
productSheet.autoSizeColumn(i, true);
int columnWidth = productSheet.getColumnWidth(i);
int needWidth = cellValue.getBytes().length * 256;
if (columnWidth < needWidth) {
productSheet.setColumnWidth(i, needWidth);
}
if ("productTypeName".equals(setting.getPropertyName())) {
Name type = workBook.createName();
String name = "type";
type.setNameName(name); // 定义名称
type.setRefersToFormula(
"OFFSET("
+ typeSheet.getSheetName()
+ "!$A$3,0,0,COUNTA("
+ typeSheet.getSheetName()
+ "!$A:$A)-2)");
XSSFDataValidationConstraint constraint =
(XSSFDataValidationConstraint) helper.createFormulaListConstraint(name); // 使用定义的名称
CellRangeAddressList regions = new CellRangeAddressList(1, 10, i, i);
DataValidation dataValidation = helper.createValidation(constraint, regions);
productSheet.addValidationData(dataValidation);
} else if ("productTypeItem".equals(setting.getPropertyName())) { // item是通过查询前面的名称来自动取得
for (int j = 0; j < 10; j++) {
XSSFRow row = productSheet.createRow(j + 1);
XSSFCell itemCell = row.createCell(i, Cell.CELL_TYPE_STRING);
int cellnum = j + 2;
itemCell.setCellFormula(
"IF(A"
+ cellnum
+ "=\"\",\"\",VLOOKUP(A"
+ cellnum
+ ",OFFSET(productType!$A$3,,,COUNTA(productType!$A:$A),2),2,0))");
}
} else if ("costMethod".equals(setting.getPropertyName())) {
int len = CostMethod.values().length;
String[] costNames = new String[len];
for (int ci = 0; ci < len; ci++) {
costNames[ci] = CostMethod.values()[ci].getMethodName();
}
// XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
XSSFDataValidationConstraint constraint =
(XSSFDataValidationConstraint) helper.createExplicitListConstraint(costNames);
CellRangeAddressList addressList = new CellRangeAddressList(1, 10, i, i);
XSSFDataValidation validation =
(XSSFDataValidation) helper.createValidation(constraint, addressList);
validation.setShowErrorBox(true);
productSheet.addValidationData(validation);
}
}
workBook.setActiveSheet(workBook.getSheetIndex(productSheet));
ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
workBook.write(baos);
return baos.toByteArray();
}
