288 lines
10 KiB
Plaintext
Executable File
288 lines
10 KiB
Plaintext
Executable File
package com.fp.excel;
|
|
|
|
import java.io.InputStream;
|
|
import java.io.OutputStream;
|
|
import java.math.BigDecimal;
|
|
import java.util.Date;
|
|
import java.util.HashMap;
|
|
import java.util.Iterator;
|
|
import java.util.Map;
|
|
import java.util.NoSuchElementException;
|
|
|
|
import org.apache.poi.ss.usermodel.Cell;
|
|
import org.apache.poi.ss.usermodel.CellStyle;
|
|
import org.apache.poi.ss.usermodel.DataFormat;
|
|
import org.apache.poi.ss.usermodel.Font;
|
|
import org.apache.poi.ss.usermodel.IndexedColors;
|
|
import org.apache.poi.ss.usermodel.Row;
|
|
import org.apache.poi.ss.usermodel.Sheet;
|
|
import org.apache.poi.ss.usermodel.Workbook;
|
|
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
|
|
import org.apache.poi.xssf.usermodel.XSSFColor;
|
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
|
|
import com.fp.common.helper.BeanManager;
|
|
|
|
public class XLSXUtil {
|
|
|
|
private Workbook book;
|
|
|
|
public Workbook getBook() {
|
|
return this.book;
|
|
}
|
|
private Sheet sheet;
|
|
private int sheetIndex;
|
|
private int maxSheets;
|
|
private int index;
|
|
|
|
public XLSXUtil() {
|
|
this.book = new XSSFWorkbook();
|
|
this.sheet = this.book.createSheet();
|
|
this.index = 0;
|
|
}
|
|
|
|
public XLSXUtil(InputStream pIn) throws Exception {
|
|
this.book = new XSSFWorkbook(pIn);
|
|
this.maxSheets = this.book.getNumberOfSheets();
|
|
this.sheet = this.book.getSheetAt(0);
|
|
this.index = 0;
|
|
}
|
|
|
|
public void addRecord(Object[] pRecord, String styleRow) throws Exception {
|
|
|
|
CellStyle style = (styleRow.equals("H"))?this.createStyle(this.book):null;
|
|
Row row = this.sheet.createRow(this.index++);
|
|
|
|
for (int i = 0; i < pRecord.length; i++) {
|
|
Object object = pRecord[i];
|
|
Cell cell = null;
|
|
if (object instanceof Number) {
|
|
cell = row.createCell(i, Cell.CELL_TYPE_NUMERIC);
|
|
cell.setCellValue(BeanManager.convertObject(object, BigDecimal.class).doubleValue());
|
|
cell.setCellStyle(style);
|
|
}
|
|
if (object instanceof Date) {
|
|
cell = row.createCell(i);
|
|
CellStyle styleDate = this.book.createCellStyle();
|
|
DataFormat fmt = this.book.createDataFormat();
|
|
styleDate.setDataFormat(fmt.getFormat("yyyy-MM-dd"));
|
|
cell.setCellStyle(styleDate);
|
|
cell.setCellValue((Date) object);
|
|
}
|
|
if (cell == null) {
|
|
cell = row.createCell(i, Cell.CELL_TYPE_STRING);
|
|
cell.setCellValue(BeanManager.convertObject(object, String.class));
|
|
cell.setCellStyle(style);
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
public Map<Integer, Map<Integer, Object>> readSheet() {
|
|
Map<Integer, Map<Integer, Object>> data = new HashMap<Integer, Map<Integer, Object>>();
|
|
for (int i = this.sheet.getFirstRowNum(); i <= this.sheet.getLastRowNum(); i++) {
|
|
Map<Integer, Object> record = this.manageRecord(i);
|
|
data.put(i, record);
|
|
}
|
|
return data;
|
|
}
|
|
|
|
public Map<Integer, Object> manageRecord(int pIndex) {
|
|
return this.manageRecord(pIndex, -1, FormulaManage.None);
|
|
}
|
|
|
|
public Map<Integer, Object> manageRecord(int pIndex, int pEnd, FormulaManage pManage) {
|
|
Map<Integer, Object> record = new HashMap<Integer, Object>();
|
|
Row r = this.sheet.getRow(pIndex);
|
|
if (r == null) {
|
|
return record;
|
|
}
|
|
Iterator<Cell> ic = r.cellIterator();
|
|
while (ic.hasNext()) {
|
|
Cell c = ic.next();
|
|
if ((c.getColumnIndex() > pEnd) && (pEnd > 0)) {
|
|
break;
|
|
}
|
|
Object value = this.manageCell(c,pManage);//(pManage==FormulaManage.None)?this.manageCell(c, pManage):this.manageCellValue(c);
|
|
if (value == null) {
|
|
continue;
|
|
}
|
|
record.put(c.getColumnIndex(), value);
|
|
}
|
|
return record;
|
|
}
|
|
|
|
public Map<Integer, Object> manageRecordForm(int pIndex, int pInit, int pEnd, FormulaManage pManage) {
|
|
Map<Integer, Object> record = new HashMap<Integer, Object>();
|
|
Row r = this.sheet.getRow(pIndex);
|
|
if (r == null) {
|
|
return record;
|
|
}
|
|
for (Cell c : r) {
|
|
if (c.getColumnIndex() >= pInit && c.getColumnIndex() < pEnd) {
|
|
Object value = this.manageCellValue(c);
|
|
if (value == null) {
|
|
continue;
|
|
}
|
|
record.put(c.getColumnIndex(), value);
|
|
} else if (c.getColumnIndex() >= pEnd) {
|
|
break;
|
|
}
|
|
}
|
|
return record;
|
|
}
|
|
|
|
private Object manageCell(Cell pCell, FormulaManage pManage) {
|
|
Object val = null;
|
|
switch (pCell.getCellType()) {
|
|
case Cell.CELL_TYPE_NUMERIC:
|
|
val = Math.round(pCell.getNumericCellValue() * 100) / 100d;
|
|
break;
|
|
case Cell.CELL_TYPE_STRING:
|
|
val = pCell.getStringCellValue();
|
|
val = pCell.getRichStringCellValue();
|
|
break;
|
|
case Cell.CELL_TYPE_BLANK:
|
|
val = pCell.getStringCellValue();
|
|
break;
|
|
case Cell.CELL_TYPE_FORMULA:
|
|
/*
|
|
* FormulaEvaluator evaluator = this.book.getCreationHelper().createFormulaEvaluator(); try { CellValue
|
|
* cellValue = evaluator.evaluate(pCell); switch (cellValue.getCellType()) { case Cell.CELL_TYPE_NUMERIC:
|
|
* val = cellValue.getNumberValue(); break; case Cell.CELL_TYPE_STRING: val = cellValue.getStringValue();
|
|
* break; case Cell.CELL_TYPE_BOOLEAN: val = cellValue.getBooleanValue(); break; case Cell.CELL_TYPE_ERROR:
|
|
* val = cellValue.getErrorValue(); break; default: val = pCell.getCellFormula(); } } catch (Exception e) {
|
|
* val = pCell.getCellFormula(); break; }
|
|
*/
|
|
if (pManage == FormulaManage.None) {
|
|
val = null;
|
|
break;
|
|
}
|
|
if (pManage == FormulaManage.Formula) {
|
|
val = pCell.getCellFormula();
|
|
break;
|
|
}
|
|
switch (pCell.getCachedFormulaResultType()) {
|
|
case Cell.CELL_TYPE_NUMERIC:
|
|
val = pCell.getNumericCellValue();
|
|
break;
|
|
case Cell.CELL_TYPE_STRING:
|
|
val = pCell.getRichStringCellValue();
|
|
break;
|
|
default:
|
|
val = pCell.getCellFormula();
|
|
// throw new IllegalArgumentException();
|
|
}
|
|
|
|
break;
|
|
case Cell.CELL_TYPE_BOOLEAN:
|
|
val = pCell.getBooleanCellValue();
|
|
break;
|
|
case Cell.CELL_TYPE_ERROR:
|
|
val = pCell.getErrorCellValue();
|
|
break;
|
|
}
|
|
return val;
|
|
}
|
|
|
|
/**
|
|
* Entrega el valor de la celda
|
|
* @param pCell Cell
|
|
* @return value Object
|
|
*/
|
|
private Object manageCellValue(Cell pCell) {
|
|
Object val = null;
|
|
double value=0;
|
|
switch (pCell.getCellType()) {
|
|
case Cell.CELL_TYPE_NUMERIC:
|
|
value=pCell.getNumericCellValue();
|
|
val = Math.round(value * 100) / 100d;
|
|
break;
|
|
case Cell.CELL_TYPE_STRING:
|
|
val = pCell.getStringCellValue();
|
|
break;
|
|
case Cell.CELL_TYPE_BLANK:
|
|
val = null;
|
|
break;
|
|
case Cell.CELL_TYPE_FORMULA:
|
|
|
|
switch (pCell.getCachedFormulaResultType()) {
|
|
case Cell.CELL_TYPE_NUMERIC:
|
|
value=pCell.getNumericCellValue();
|
|
val = Math.round(value * 100) / 100d;
|
|
break;
|
|
case Cell.CELL_TYPE_STRING:
|
|
val=pCell.getRichStringCellValue();
|
|
val=pCell.getStringCellValue();
|
|
// val = pCell.getRichStringCellValue();
|
|
break;
|
|
case Cell.CELL_TYPE_BLANK:
|
|
val = null;
|
|
break;
|
|
case Cell.CELL_TYPE_ERROR:
|
|
val = null;
|
|
break;
|
|
default:
|
|
val=null;
|
|
// val = pCell.getCellFormula();
|
|
}
|
|
|
|
break;
|
|
case Cell.CELL_TYPE_BOOLEAN:
|
|
val = pCell.getBooleanCellValue();
|
|
break;
|
|
case Cell.CELL_TYPE_ERROR:
|
|
val = pCell.getErrorCellValue();
|
|
break;
|
|
}
|
|
return val;
|
|
}
|
|
|
|
public void save(OutputStream pOut) throws Exception {
|
|
this.book.write(pOut);
|
|
}
|
|
|
|
public String getSheetName() {
|
|
return this.sheet.getSheetName();
|
|
}
|
|
|
|
public void nextSheet() {
|
|
if (this.sheetIndex >= this.maxSheets) {
|
|
throw new NoSuchElementException();
|
|
}
|
|
this.sheet = this.book.getSheetAt(++this.sheetIndex);
|
|
this.index = 0;
|
|
}
|
|
|
|
public void goSheet(String pName) {
|
|
int ind = this.book.getSheetIndex(pName);
|
|
if ((ind < 0) || (ind >= this.maxSheets)) {
|
|
throw new NoSuchElementException();
|
|
}
|
|
this.sheet = this.book.getSheetAt(ind);
|
|
this.index = 0;
|
|
}
|
|
|
|
private CellStyle createStyle(Workbook wb){
|
|
CellStyle style = wb.createCellStyle();
|
|
style.setBorderRight(CellStyle.BORDER_THIN);
|
|
style.setRightBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
|
|
style.setBorderBottom(CellStyle.BORDER_THIN);
|
|
style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
|
|
style.setBorderLeft(CellStyle.BORDER_THIN);
|
|
style.setLeftBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
|
|
style.setBorderTop(CellStyle.BORDER_THIN);
|
|
style.setTopBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
|
|
|
|
Font header2Font = wb.createFont();
|
|
header2Font.setColor(IndexedColors.WHITE.getIndex());
|
|
XSSFCellStyle style_h_2 = (XSSFCellStyle) style;
|
|
style_h_2.setAlignment(CellStyle.ALIGN_CENTER);
|
|
style_h_2.setFillForegroundColor(new XSSFColor(new java.awt.Color(86, 142, 213)));
|
|
style_h_2.setFillPattern(CellStyle.SOLID_FOREGROUND);
|
|
style_h_2.setFont(header2Font);
|
|
|
|
return style_h_2;
|
|
}
|
|
}
|