private Cell getOrCreateCell(CellReference cellRef) {
Cell cell = spreadsheet.getCell(cellRef.getRow(), cellRef.getCol());
if (cell == null) {
cell = spreadsheet.createCell(cellRef.getRow(), cellRef.getCol(), "");
}
return cell;
}
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;
}
public void printWorksheetContent() throws Exception {
FrontDesk desk = FrontDesk.getInstance();
// read policy & rule
DynamicPolicy policy = SifMarshaller.unmarshal(new File(policyPath));
@SuppressWarnings("unchecked")
DynamicInspectionRequest<Workbook> req =
(DynamicInspectionRequest<Workbook>)
desk.requestNewDynamicInspection("fubar", new File(filepath));
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();
Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
for (Cell cell : row) {
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
System.out.print(row.getRowNum() + ", " + cell.getColumnIndex());
System.out.print(" - ");
System.out.print(cellRef.formatAsString());
System.out.print(" - ");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println();
}
}
}
}
/**
* Gets the total number of rows in the selection. (Note: in this version autofiltering is
* ignored)
*
* @return
*/
public int getRowCount() {
CellReference from = getStartCellReference();
CellReference to = getEndCellReference();
int rowCount = -1;
if (from != null && to != null) {
rowCount = to.getRow() - from.getRow();
}
return rowCount;
}
public void testGetRowNumFromRef() {
String cellRef = "A1";
CellReference cellReference = new CellReference(cellRef);
assertEquals(0, cellReference.getRow());
cellRef = "A12";
cellReference = new CellReference(cellRef);
assertEquals(11, cellReference.getRow());
cellRef = "AS121";
cellReference = new CellReference(cellRef);
assertEquals(120, cellReference.getRow());
}
protected final String formatReferenceAsString() {
CellReference topLeft =
new CellReference(
getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative());
CellReference botRight =
new CellReference(
getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative());
if (AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL97, topLeft, botRight)) {
return (new AreaReference(topLeft, botRight)).formatAsString();
}
return topLeft.formatAsString() + ":" + botRight.formatAsString();
}
/**
* Matching cells with apache poi indexes
*
* @param cells Array with cell names in excel file The order of columns is following:
* ---------------------------------- Item name 0 Item price 1 Number of items 2 Number of
* items in pack 3 Number of packs 4 Net weight of 1 pack 5 Gross weight of 1 pack 6 Volume of
* 1 pack 7 Sum net weight(will be calculated further) 4*5 Sum gross weight(will be calculated
* further) 4*6 Sum volume(will be calculated further) 4*7
*/
private void setCells() {
int i = 0;
for (String cell : cells) {
cellCodes[i] = CellReference.convertColStringToIndex(cell);
i++;
}
}
public void testConvertNumColColString() {
assertEquals("A", CellReference.convertNumToColString(0));
assertEquals("AV", CellReference.convertNumToColString(47));
assertEquals("AW", CellReference.convertNumToColString(48));
assertEquals("BF", CellReference.convertNumToColString(57));
assertEquals("", CellReference.convertNumToColString(-1));
assertEquals("", CellReference.convertNumToColString(Integer.MIN_VALUE));
assertEquals("", CellReference.convertNumToColString(Integer.MAX_VALUE));
assertEquals("FXSHRXW", CellReference.convertNumToColString(Integer.MAX_VALUE - 1));
}
public void testModifyArrayCells_removeCell() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
// single-cell array formulas behave just like normal cells
CellRangeAddress cra = CellRangeAddress.valueOf("B5");
CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
Cell scell = srange.getTopLeftCell();
Row srow = sheet.getRow(cra.getFirstRow());
assertSame(srow, scell.getRow());
srow.removeCell(scell);
assertNull(srow.getCell(cra.getFirstColumn()));
// re-create the removed cell
scell = srow.createCell(cra.getFirstColumn());
assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
assertFalse(scell.isPartOfArrayFormulaGroup());
// we cannot remove cells included in a multi-cell array formula
CellRange<? extends Cell> mrange =
sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
for (Cell mcell : mrange) {
int columnIndex = mcell.getColumnIndex();
Row mrow = mcell.getRow();
try {
mrow.removeCell(mcell);
fail("expected exception");
} catch (IllegalStateException e) {
CellReference ref = new CellReference(mcell);
String msg =
"Cell "
+ ref.formatAsString()
+ " is part of a multi-cell array formula. You cannot change part of an array.";
assertEquals(msg, e.getMessage());
}
// a failed invocation of Row.removeCell leaves the row
// in the state that it was in prior to the invocation
assertSame(mcell, mrow.getCell(columnIndex));
assertTrue(mcell.isPartOfArrayFormulaGroup());
assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
}
}
public void testModifyArrayCells_setCellFormula() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
CellRange<? extends Cell> srange =
sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
Cell scell = srange.getTopLeftCell();
assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula());
assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
assertTrue(scell.isPartOfArrayFormulaGroup());
scell.setCellFormula("SUM(A4,A6)");
// we are now a normal formula cell
assertEquals("SUM(A4,A6)", scell.getCellFormula());
assertFalse(scell.isPartOfArrayFormulaGroup());
assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
// check that setting formula result works
assertEquals(0.0, scell.getNumericCellValue());
scell.setCellValue(33.0);
assertEquals(33.0, scell.getNumericCellValue());
// multi-cell array formula
CellRange<? extends Cell> mrange =
sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
for (Cell mcell : mrange) {
// we cannot set individual formulas for cells included in an array formula
try {
assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
mcell.setCellFormula("A1+A2");
fail("expected exception");
} catch (IllegalStateException e) {
CellReference ref = new CellReference(mcell);
String msg =
"Cell "
+ ref.formatAsString()
+ " is part of a multi-cell array formula. You cannot change part of an array.";
assertEquals(msg, e.getMessage());
}
// a failed invocation of Cell.setCellFormula leaves the cell
// in the state that it was in prior to the invocation
assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
assertTrue(mcell.isPartOfArrayFormulaGroup());
}
}
/**
* Parses out a potential LHS or RHS of a ':' intended to produce a plain AreaRef. Normally these
* are proper cell references but they could also be row or column refs like "$AC" or "10"
*
* @return <code>null</code> (and leaves {@link #_pointer} unchanged if a proper range part does
* not parse out
*/
private SimpleRangePart parseSimpleRangePart() {
int ptr = _pointer - 1; // TODO avoid StringIndexOutOfBounds
boolean hasDigits = false;
boolean hasLetters = false;
while (ptr < _formulaLength) {
char ch = _formulaString.charAt(ptr);
if (Character.isDigit(ch)) {
hasDigits = true;
} else if (Character.isLetter(ch)) {
hasLetters = true;
} else if (ch == '$') {
//
} else {
break;
}
ptr++;
}
if (ptr <= _pointer - 1) {
return null;
}
String rep = _formulaString.substring(_pointer - 1, ptr);
if (!CELL_REF_PATTERN.matcher(rep).matches()) {
return null;
}
// Check range bounds against grid max
if (hasLetters && hasDigits) {
if (!isValidCellReference(rep)) {
return null;
}
} else if (hasLetters) {
if (!CellReference.isColumnWithnRange(rep.replace("$", ""), _ssVersion)) {
return null;
}
} else if (hasDigits) {
int i;
try {
i = Integer.parseInt(rep.replace("$", ""));
} catch (NumberFormatException e) {
return null;
}
if (i < 1 || i > 65536) {
return null;
}
} else {
// just dollars ? can this happen?
return null;
}
resetPointer(ptr + 1); // stepping forward
return new SimpleRangePart(rep, hasLetters, hasDigits);
}
public void testModifyArrayCells_setCellType() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
// single-cell array formulas behave just like normal cells -
// changing cell type removes the array formula and associated cached result
CellRange<? extends Cell> srange =
sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
Cell scell = srange.getTopLeftCell();
assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
assertEquals(0.0, scell.getNumericCellValue());
scell.setCellType(Cell.CELL_TYPE_STRING);
assertEquals(Cell.CELL_TYPE_STRING, scell.getCellType());
scell.setCellValue("string cell");
assertEquals("string cell", scell.getStringCellValue());
// once you create a multi-cell array formula, you cannot change the type of its cells
CellRange<? extends Cell> mrange =
sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
for (Cell mcell : mrange) {
try {
assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
mcell.setCellType(Cell.CELL_TYPE_NUMERIC);
fail("expected exception");
} catch (IllegalStateException e) {
CellReference ref = new CellReference(mcell);
String msg =
"Cell "
+ ref.formatAsString()
+ " is part of a multi-cell array formula. You cannot change part of an array.";
assertEquals(msg, e.getMessage());
}
// a failed invocation of Cell.setCellType leaves the cell
// in the state that it was in prior to the invocation
assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
assertTrue(mcell.isPartOfArrayFormulaGroup());
}
}
public void testConvertColStringToIndex() {
assertEquals(0, CellReference.convertColStringToIndex("A"));
assertEquals(1, CellReference.convertColStringToIndex("B"));
assertEquals(14, CellReference.convertColStringToIndex("O"));
assertEquals(701, CellReference.convertColStringToIndex("ZZ"));
assertEquals(18252, CellReference.convertColStringToIndex("ZZA"));
assertEquals(0, CellReference.convertColStringToIndex("$A"));
assertEquals(1, CellReference.convertColStringToIndex("$B"));
try {
CellReference.convertColStringToIndex("A$");
fail("Should throw exception here");
} catch (IllegalArgumentException e) {
assertTrue(e.getMessage().contains("A$"));
}
}
private static void confirmCrInRange(
boolean expResult, String colStr, String rowStr, SpreadsheetVersion sv) {
if (expResult == CellReference.cellReferenceIsWithinRange(colStr, rowStr, sv)) {
return;
}
throw new AssertionFailedError(
"expected (c='"
+ colStr
+ "', r='"
+ rowStr
+ "' to be "
+ (expResult ? "within" : "out of")
+ " bounds for version "
+ sv.name());
}
public void testBadRowNumber() {
SpreadsheetVersion v97 = SpreadsheetVersion.EXCEL97;
SpreadsheetVersion v2007 = SpreadsheetVersion.EXCEL2007;
confirmCrInRange(true, "A", "1", v97);
confirmCrInRange(true, "IV", "65536", v97);
confirmCrInRange(false, "IV", "65537", v97);
confirmCrInRange(false, "IW", "65536", v97);
confirmCrInRange(true, "A", "1", v2007);
confirmCrInRange(true, "XFD", "1048576", v2007);
confirmCrInRange(false, "XFD", "1048577", v2007);
confirmCrInRange(false, "XFE", "1048576", v2007);
if (CellReference.cellReferenceIsWithinRange("B", "0", v97)) {
throw new AssertionFailedError("Identified bug 47312a");
}
confirmCrInRange(false, "A", "0", v97);
confirmCrInRange(false, "A", "0", v2007);
}
/** @return <code>true</code> if the specified name is a valid cell reference */
private boolean isValidCellReference(String str) {
// check range bounds against grid max
boolean result = CellReference.classifyCellReference(str, _ssVersion) == NameType.CELL;
if (result) {
/**
* Check if the argument is a function. Certain names can be either a cell reference or a
* function name depending on the contenxt. Compare the following examples in Excel 2007: (a)
* LOG10(100) + 1 (b) LOG10 + 1 In (a) LOG10 is a name of a built-in function. In (b) LOG10 is
* a cell reference
*/
boolean isFunc = FunctionMetadataRegistry.getFunctionByName(str.toUpperCase()) != null;
if (isFunc) {
int savePointer = _pointer;
resetPointer(_pointer + str.length());
SkipWhite();
// open bracket indicates that the argument is a function,
// the returning value should be false, i.e. "not a valid cell reference"
result = look != '(';
resetPointer(savePointer);
}
}
return result;
}
/**
* Prototype
*
* @author Cyrille Chopelet (https://keyboardplaying.org)
*/
public class XlsxBuilderPrototype {
private static final int NB_HEADER_ROWS = 2;
private static final int COLUMN_MONTH = CellReference.convertColStringToIndex("U");
private static final int COLUMN_DAY = CellReference.convertColStringToIndex("V");
private static final int COLUMN_TECHNICAL_FIRST = CellReference.convertColStringToIndex("U");
private static final int COLUMN_TECHNICAL_LAST = CellReference.convertColStringToIndex("AB");
@Test
@SuppressWarnings("javadoc")
public void generateWorkbook() {
try (InputStream in = getClass().getResourceAsStream("tracker-dev.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(in)) {
final int dayRow = NB_HEADER_ROWS;
final int monthRow = dayRow + 1;
final int startRow = monthRow + 1;
XSSFSheet sheet = wb.getSheet("Timesheet");
/* Prepare tracker rows */
int year = 2016;
createRowsFromTemplate(sheet, year, dayRow, monthRow, startRow);
/* Save and remove conditional formatting */
applyConditionalFormattingToCopiedRows(sheet, startRow);
/* Remove two first rows */
removeTemplateRows(sheet, startRow);
/* TODO Hide technical columns */
/* Write output file */
File out = new File("tracker-test.xlsx");
wb.write(new FileOutputStream(out));
wb.close();
System.out.println(out.getAbsolutePath());
} catch (Exception e) {
e.printStackTrace();
}
}
private void createRowsFromTemplate(
XSSFSheet sheet, int year, int dayRow, int monthRow, int startRow) {
LocalDate dt = new LocalDate(year, DateTimeConstants.JANUARY, 1);
int r = startRow;
final CellCopyPolicy policy = new CellCopyPolicy();
while (dt.year().get() == year) {
/* Create rows */
r = createMonth(sheet, dt, dayRow, monthRow, r, policy);
dt = dt.plusMonths(1);
}
}
private int createMonth(
XSSFSheet sheet, LocalDate dt, int dayRow, int monthRow, int row, CellCopyPolicy policy) {
int r = row;
final int nbDays = dt.dayOfMonth().getMaximumValue();
final int month = dt.monthOfYear().get();
/* Insert days for the month */
for (int day = 1; day <= nbDays; day++) {
XSSFRow inserted = copyRow(sheet, dayRow, r, policy);
inserted.getCell(COLUMN_MONTH).setCellValue(month);
inserted.getCell(COLUMN_DAY).setCellValue(day);
r++;
}
/* Insert month summary */
XSSFRow inserted = copyRow(sheet, monthRow, r, policy);
inserted.getCell(COLUMN_MONTH).setCellValue(month);
r++;
/* Group month rows */
int groupRow = r - 2;
sheet.groupRow(row, groupRow);
sheet.setRowGroupCollapsed(groupRow, true);
return r;
}
private XSSFRow copyRow(XSSFSheet sheet, int srcRow, int destRow, CellCopyPolicy policy) {
sheet.copyRows(srcRow, srcRow + 1, destRow, policy);
return sheet.getRow(destRow);
}
private void applyConditionalFormattingToCopiedRows(XSSFSheet sheet, int startRow) {
final int lastRow = sheet.getLastRowNum();
XSSFSheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
// Go from end to start because we will be removing them
for (int i = formatting.getNumConditionalFormattings() - 1; i >= 0; i--) {
// Get conditional formatting
XSSFConditionalFormatting format = formatting.getConditionalFormattingAt(i);
// Apply conditional formatting to new range
CellRangeAddress[] ranges =
computeNewFormattingRanges(format.getFormattingRanges(), startRow, lastRow);
applyRulesToRanges(formatting, format, ranges);
// remove previous version of the conditional formatting
formatting.removeConditionalFormatting(i);
}
}
private CellRangeAddress[] computeNewFormattingRanges(
CellRangeAddress[] ranges, int startRow, int lastRow) {
for (CellRangeAddress range : ranges) {
range.setFirstRow(startRow);
range.setLastRow(lastRow);
}
return ranges;
}
private void applyRulesToRanges(
XSSFSheetConditionalFormatting formatting,
XSSFConditionalFormatting format,
CellRangeAddress[] ranges) {
final int nbRules = format.getNumberOfRules();
for (int j = 0; j < nbRules; j++) {
ConditionalFormattingRule rule = format.getRule(j);
formatting.addConditionalFormatting(ranges, rule);
}
}
private void removeTemplateRows(XSSFSheet sheet, int startRow) {
sheet.shiftRows(startRow, sheet.getLastRowNum(), NB_HEADER_ROWS - startRow);
}
}
/**
* Handles a Stream event.
*
* @param event
* @throws SAXException
*/
private void handleEvent(XMLEvent event) throws SAXException {
if (event.getEventType() == XMLStreamConstants.CHARACTERS) {
Characters c = event.asCharacters();
lastContents += c.getData();
} else if (event.getEventType() == XMLStreamConstants.START_ELEMENT) {
StartElement startElement = event.asStartElement();
String tagLocalName = startElement.getName().getLocalPart();
if ("row".equals(tagLocalName)) {
Attribute rowIndex = startElement.getAttributeByName(new QName("r"));
if (firstRowIndex == -1) {
firstRowIndex = Integer.parseInt(rowIndex.getValue());
}
currentRow = new StreamingRow(Integer.parseInt(rowIndex.getValue()) - 1);
} else if ("cols".equals(tagLocalName)) {
parsingCols = true;
} else if ("col".equals(tagLocalName) && parsingCols) {
colNumber = colNumber + 1;
} else if ("c".equals(tagLocalName)) {
Attribute ref = startElement.getAttributeByName(new QName("r"));
String[] coord = ref.getValue().split("(?<=\\D)(?=\\d)|(?<=\\d)(?=\\D)");
currentCell =
new StreamingCell(
CellReference.convertColStringToIndex(coord[0]), Integer.parseInt(coord[1]) - 1);
setFormatString(startElement, currentCell);
Attribute type = startElement.getAttributeByName(new QName("t"));
if (type != null) {
currentCell.setType(type.getValue());
} else {
currentCell.setType("n");
}
Attribute style = startElement.getAttributeByName(new QName("s"));
if (style != null) {
String indexStr = style.getValue();
try {
int index = Integer.parseInt(indexStr);
currentCell.setCellStyle(stylesTable.getStyleAt(index));
} catch (NumberFormatException nfe) {
LOGGER.warn("Ignoring invalid style index {}", indexStr);
}
}
// we store the dimension as well to revert with this method when cols not found
// can happen see xlsx attached here https://jira.talendforge.org/browse/TDP-1957
// <dimension ref="A1:B60"/>
} else if ("dimension".equals(tagLocalName)) {
Attribute attribute = startElement.getAttributeByName(new QName("ref"));
if (attribute != null) {
this.dimension = attribute.getValue();
}
}
// Clear contents cache
lastContents = "";
} else if (event.getEventType() == XMLStreamConstants.END_ELEMENT) {
EndElement endElement = event.asEndElement();
String tagLocalName = endElement.getName().getLocalPart();
if ("v".equals(tagLocalName) || "t".equals(tagLocalName)) {
currentCell.setRawContents(unformattedContents());
currentCell.setContents(formattedContents());
} else if ("row".equals(tagLocalName) && currentRow != null) {
rowCache.add(currentRow);
} else if ("c".equals(tagLocalName)) {
currentRow.getCellMap().put(currentCell.getColumnIndex(), currentCell);
} else if ("cols".equals(tagLocalName)) {
parsingCols = false;
}
}
}
/**
* 资产明细表
*
* @param response
* @param customer_2
* @param customer_0or1
* @throws IOException
*/
@RequestMapping("/report/frontequip/exportFrontEquipListReport_assetclean.do")
public void exportFrontEquipListReport_assetclean(
HttpServletResponse response, String customer_2, String customer_0or1) throws IOException {
String customer_2_name = customerService.get(customer_2).getName();
String customer_0or1_name = customerService.get(customer_0or1).getName();
List<FrontEquipListReport> list =
frontEquipReportRepository.queryFrontEquipListReport(customer_2, customer_0or1);
List<FrontEquipListReport_subtype> list_subtype_prod =
frontEquipReportRepository.queryFrontEquipListReport_header(customer_2, customer_0or1);
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
int rownum = 0;
// 标题
Row title = sheet.createRow(rownum++);
Cell title_cell = title.createCell(0);
title_cell.setCellValue(customer_2_name + customer_0or1_name + "前端设备明细表");
CellStyle title_style = wb.createCellStyle();
Font title_font = wb.createFont();
title_font.setFontHeightInPoints((short) 16);
// f.setColor(IndexedColors.RED.getIndex());
title_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
title_style.setFont(title_font);
title_style.setAlignment(CellStyle.ALIGN_CENTER);
title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
title_cell.setCellStyle(title_style);
// 和并单元格
// sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) 15));
// ===========================================================================================
rownum = exportFrontEquipListReport_header_assetclean(list_subtype_prod, rownum, wb, sheet);
// =============================================================================================
// 开始构建整个excel的文件
// 处理对应的品名在excel的哪一列
Map<String, Integer> prod_col_index_map = new HashMap<String, Integer>();
int cellIndex = 3;
for (FrontEquipListReport_subtype subtype : list_subtype_prod) {
for (FrontEquipListReport_prod prod : subtype.getProds()) {
prod_col_index_map.put(prod.getProd_id(), cellIndex);
cellIndex = cellIndex + 2;
}
}
if (list != null && list.size() > 0) {
int i = 1;
for (FrontEquipListReport customer : list) {
Row row = sheet.createRow(rownum++);
Cell cell_prod_0 = row.createCell(0);
cell_prod_0.setCellValue(i);
// cell_prod_0.setCellStyle(style);
Cell cell_prod_1 = row.createCell(1);
cell_prod_1.setCellValue(customer.getPole_code());
Cell cell_prod_2 = row.createCell(2);
cell_prod_2.setCellValue(customer.getPole_name());
for (FrontEquipListReport_prod prod : customer.getProdes()) {
Cell cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()));
cell_prod.setCellValue(prod.getNum());
// cell_prod.setCellStyle(style);
cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()) + 1);
cell_prod.setCellValue(prod.getValue_net().doubleValue());
}
i++;
}
// 对标题行 进行单元格合并
sheet.addMergedRegion(
new CellRangeAddress(0, (short) 0, 0, (short) prod_col_index_map.size()));
}
sheet.createFreezePane(3, 3);
// 添加总计一行
CellStyle style_sum = wb.createCellStyle();
Font style_sum_font = wb.createFont();
style_sum_font.setFontHeightInPoints((short) 12);
// f.setColor(IndexedColors.RED.getIndex());
style_sum_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style_sum.setFont(style_sum_font);
style_sum.setAlignment(CellStyle.ALIGN_RIGHT);
style_sum.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Row row_sum = sheet.createRow(rownum++);
Cell cell_sum_2 = row_sum.createCell(2);
cell_sum_2.setCellValue("小计:");
cell_sum_2.setCellStyle(style_sum);
for (int i = 3; i < cellIndex; i++) {
String col = CellReference.convertNumToColString(i);
Cell cell_sum = row_sum.createCell(i);
// =SUM(C4:C29) 从第4行开始到最后一样
// cell_sum.setCellValue("SUM("+col+"4:"+col+(rownum-1)+")");
cell_sum.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")");
cell_sum.setCellStyle(style_sum);
Cell cell_net = row_sum.createCell(i);
cell_net.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")");
cell_net.setCellStyle(style_sum);
}
String filename = customer_2_name + customer_0or1_name + "前端设备明细表-净资产.xlsx";
// FileOutputStream out = new FileOutputStream(filename);
response.setHeader(
"content-disposition",
"attachment; filename=" + new String(filename.getBytes("UTF-8"), "ISO8859-1"));
// response.setContentType("application/vnd.ms-excel;charset=uft-8");
response.setContentType(
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=uft-8");
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
protected AreaPtgBase(AreaReference ar) {
CellReference firstCell = ar.getFirstCell();
CellReference lastCell = ar.getLastCell();
setFirstRow(firstCell.getRow());
setFirstColumn(firstCell.getCol() == -1 ? 0 : firstCell.getCol());
setLastRow(lastCell.getRow());
setLastColumn(lastCell.getCol() == -1 ? 0xFF : lastCell.getCol());
setFirstColRelative(!firstCell.isColAbsolute());
setLastColRelative(!lastCell.isColAbsolute());
setFirstRowRelative(!firstCell.isRowAbsolute());
setLastRowRelative(!lastCell.isRowAbsolute());
}
public static String offsetCellIndex(String cellIndex, int rowOffset, int colOffset) {
CellReference cell = new CellReference(cellIndex);
CellReference newCell = new CellReference(cell.getRow() + rowOffset, cell.getCol() + colOffset);
return newCell.formatAsString().replaceAll("\\$", "");
}
public static int[] getCellPosition(String cellIndex) {
CellReference cell = new CellReference(cellIndex);
return new int[] {cell.getRow(), cell.getCol()};
}
public static String getCellIndex(int row, int col) {
CellReference cell = new CellReference(row, col);
return cell.formatAsString().replaceAll("\\$", "");
}
public void testGetColNumFromRef() {
String cellRef = "A1";
CellReference cellReference = new CellReference(cellRef);
assertEquals(0, cellReference.getCol());
cellRef = "AA1";
cellReference = new CellReference(cellRef);
assertEquals(26, cellReference.getCol());
cellRef = "AB1";
cellReference = new CellReference(cellRef);
assertEquals(27, cellReference.getCol());
cellRef = "BA1";
cellReference = new CellReference(cellRef);
assertEquals(26 + 26, cellReference.getCol());
cellRef = "CA1";
cellReference = new CellReference(cellRef);
assertEquals(26 + 26 + 26, cellReference.getCol());
cellRef = "ZA1";
cellReference = new CellReference(cellRef);
assertEquals(26 * 26, cellReference.getCol());
cellRef = "ZZ1";
cellReference = new CellReference(cellRef);
assertEquals(26 * 26 + 25, cellReference.getCol());
cellRef = "AAA1";
cellReference = new CellReference(cellRef);
assertEquals(26 * 26 + 26, cellReference.getCol());
cellRef = "A1100";
cellReference = new CellReference(cellRef);
assertEquals(0, cellReference.getCol());
cellRef = "BC15";
cellReference = new CellReference(cellRef);
assertEquals(54, cellReference.getCol());
}
public void testGetCellRefParts() {
CellReference cellReference;
String[] parts;
String cellRef = "A1";
cellReference = new CellReference(cellRef);
assertEquals(0, cellReference.getCol());
parts = cellReference.getCellRefParts();
assertNotNull(parts);
assertEquals(null, parts[0]);
assertEquals("1", parts[1]);
assertEquals("A", parts[2]);
cellRef = "AA1";
cellReference = new CellReference(cellRef);
assertEquals(26, cellReference.getCol());
parts = cellReference.getCellRefParts();
assertNotNull(parts);
assertEquals(null, parts[0]);
assertEquals("1", parts[1]);
assertEquals("AA", parts[2]);
cellRef = "AA100";
cellReference = new CellReference(cellRef);
assertEquals(26, cellReference.getCol());
parts = cellReference.getCellRefParts();
assertNotNull(parts);
assertEquals(null, parts[0]);
assertEquals("100", parts[1]);
assertEquals("AA", parts[2]);
cellRef = "AAA300";
cellReference = new CellReference(cellRef);
assertEquals(702, cellReference.getCol());
parts = cellReference.getCellRefParts();
assertNotNull(parts);
assertEquals(null, parts[0]);
assertEquals("300", parts[1]);
assertEquals("AAA", parts[2]);
cellRef = "ZZ100521";
cellReference = new CellReference(cellRef);
assertEquals(26 * 26 + 25, cellReference.getCol());
parts = cellReference.getCellRefParts();
assertNotNull(parts);
assertEquals(null, parts[0]);
assertEquals("100521", parts[1]);
assertEquals("ZZ", parts[2]);
cellRef = "ZYX987";
cellReference = new CellReference(cellRef);
assertEquals(26 * 26 * 26 + 25 * 26 + 24 - 1, cellReference.getCol());
parts = cellReference.getCellRefParts();
assertNotNull(parts);
assertEquals(null, parts[0]);
assertEquals("987", parts[1]);
assertEquals("ZYX", parts[2]);
cellRef = "AABC10065";
cellReference = new CellReference(cellRef);
parts = cellReference.getCellRefParts();
assertNotNull(parts);
assertEquals(null, parts[0]);
assertEquals("10065", parts[1]);
assertEquals("AABC", parts[2]);
}
/**
* 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;
}
}
}
