src.main.java.com.vincomobile.fw.basic.tools.ExcelUtil Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of vincofw-basic Show documentation
Show all versions of vincofw-basic Show documentation
Vincomobile framework (Basic components)
package com.vincomobile.fw.basic.tools;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.sql.Time;
import java.text.Collator;
import java.util.Date;
import java.util.Locale;
public class ExcelUtil {
public static final String[] columns = new String[] {
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
"AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ",
"BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ",
"CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ",
"DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", "DW", "DX", "DY", "DZ",
"EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK", "EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES", "ET", "EU", "EV", "EW", "EX", "EY", "EZ",
"FA", "FB", "FC", "FD", "FE", "FF", "FG", "FH", "FI", "FJ", "FK", "FL", "FM", "FN", "FO", "FP", "FQ", "FR", "FS", "FT", "FU", "FV", "FW", "FX", "FY", "FZ",
"GA", "GB", "GC", "GD", "GE", "GF", "GG", "GH", "GI", "GJ", "GK", "GL", "GM", "GN", "GO", "GP", "GQ", "GR", "GS", "GT", "GU", "GV", "GW", "GX", "GY", "GZ"
};
public static final String FORMAT_XLS = "XLS";
public static final String FORMAT_XLSX = "XLSX";
protected Workbook wb;
protected Sheet sheet;
protected CellStyle csCaption;
protected CellStyle csColTitle;
protected CellStyle csBold;
protected CellStyle csNormal;
protected CellStyle csNormalRed;
protected CellStyle csNormalDate;
protected CellStyle csNormalAmount;
protected CellStyle csBoldAmount;
protected String sheetName = "Hoja1";
protected String file;
protected boolean toFile = false;
protected boolean toRequest = true;
protected boolean xlsFormat = true;
public static String getColumnName(int indx) {
return indx < columns.length ? columns[indx] : "";
}
/**
* Load from file
*
* @param filename Excel file name
*/
public void loadFile(String filename) throws Exception {
loadFile(new File(filename));
}
/**
* Load from file (Web Upload)
*
* @param fileToImport Web uploaded file
*/
public void loadFile(MultipartFile fileToImport) throws Exception {
if (fileToImport.getOriginalFilename().toLowerCase().endsWith(".xlsx")) {
wb = new XSSFWorkbook(fileToImport.getInputStream());
xlsFormat = false;
} else {
wb = new HSSFWorkbook(new POIFSFileSystem(fileToImport.getInputStream()));
xlsFormat = true;
}
sheet = wb.getSheetAt(0);
}
/**
* Load from file
*
* @param file Excel file
*/
public void loadFile(File file) throws Exception {
InputStream inputStream = new FileInputStream(file);
if (file.getName().toLowerCase().endsWith(".xlsx")) {
this.wb = new XSSFWorkbook(inputStream);
xlsFormat = false;
} else {
this.wb = new HSSFWorkbook(new POIFSFileSystem(inputStream));
xlsFormat = true;
}
this.sheet = wb.getSheetAt(0);
this.file = file.getAbsolutePath();
this.toFile = true;
}
/**
* Initialize a book with a sheet
*
* @param sheetName Sheet name
* @param format Excel format (XLS or XLSX)
*/
public void initBook(String sheetName, String format) {
this.xlsFormat = FORMAT_XLS.equals(format);
if (FORMAT_XLSX.equals(format)) {
wb = new XSSFWorkbook();
} else {
wb = new HSSFWorkbook();
}
sheet = wb.createSheet(sheetName);
createStyle();
}
/**
* Initialize a book with a sheet (XLS)
*
* @param sheetName Sheet name
*/
public void initBook(String sheetName) {
initBook(sheetName, FORMAT_XLS);
}
/**
* Create a sheet
*
* @param name Sheet name
*/
public void createSheet(String name) {
sheet = wb.createSheet(name);
createStyle();
}
/**
* Set sheet
*
* @param name Sheet name
*/
public void setSheet(String name) {
sheet = wb.getSheet(name);
}
/**
* Verify row titles
*
* @param row Row number
* @param titles Column titles
* @return If success return null else message error
*/
public String checkRowTitles(int row, String[] titles) {
if (sheet == null || sheet.getLastRowNum() < row) {
return "Not found row Title at: " + row;
}
Collator c = Collator.getInstance(Locale.ENGLISH);
c.setStrength(Collator.PRIMARY);
Row rTitles = sheet.getRow(row);
// Check columns names
int cells = 0;
for (Cell cell : rTitles) {
if (cells == titles.length)
break;
Object value = getCellValue(cell);
int indx = cell.getColumnIndex();
if (value == null || !(value instanceof String) || c.compare(titles[indx], ((String) value).trim()) != 0) {
if (indx < titles.length && indx < columns.length)
return "In column[" + columns[indx] + "]: found value '" + value + "' but expected '" + titles[indx]+"'";
else
return "Invalid columns";
}
cells++;
}
if (cells != titles.length) {
return "Columns size not correct. Must be have " + titles.length + " columns and have " + cells;
}
return null;
}
/**
* Initialize default styles
*/
public void createStyle() {
csCaption = getStyleCaption();
csColTitle = getStyleColTitle();
csBold = getStyleBold();
csNormal = getStyleNormal();
csNormalRed = getStyleNormalRed();
csNormalDate = getStyleNormalDate();
csNormalAmount = getStyleNormalAmount();
csBoldAmount = getStyleBoldAmount();
}
/**
* Close Excel and redirect to HTTP or File
*
* @param response Servlet response
* @throws Exception Error
*/
public void closeExcel(HttpServletResponse response) throws Exception {
if (toRequest) {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Cache-Control", "no-cache");
if (getFile() != null)
response.setHeader("Content-disposition", "inline; filename=" + getFile() );
response.setHeader("Expires", "0");
response.setHeader("Pragma", "public");
ServletOutputStream output = response.getOutputStream();
wb.write(output);
output.flush();
}
}
public void closeExcel(File excel) throws Exception {
FileOutputStream output = new FileOutputStream(excel);
wb.write(output);
output.flush();
output.close();
}
public void closeExcel() throws Exception {
FileOutputStream output = new FileOutputStream(getFile());
wb.write(output);
output.flush();
output.close();
}
/**
* Get byte array
*
* @return Byte array
* @throws Exception
*/
public byte[] getBytes() throws Exception {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
try {
wb.write(bos);
} finally {
bos.close();
}
return bos.toByteArray();
}
/**
* Apply width to columns
*
* @param widths Width array
*/
public void setWidths(int[] widths) {
if (widths != null) {
int indx = 0;
for (int w : widths) {
sheet.setColumnWidth(indx++, (int) ((w * 8) / ((double) 1 / 20)));
}
}
}
public void setWidths(String widths) {
String[] wColumns = widths.split(",");
if (wColumns != null) {
int indx = 0;
for (String w : wColumns) {
if (Converter.validInt(w, false))
sheet.setColumnWidth(indx++, (int) ((Converter.getInt(w) * 8) / ((double) 1 / 20)));
}
}
}
/**
* Create a row with column captions
*
* @param rowIndex Row index
* @param colsCaptions Columns captions
* @return Excel row
*/
public Row createRowCaptions(int rowIndex, String[] colsCaptions) {
Row row = sheet.createRow(rowIndex);
for (int i = 0; i < colsCaptions.length; i++) {
createCell(row, i, colsCaptions[i], csColTitle);
}
return row;
}
/**
* Set a SUM formula into cell Ex: SUM(A2:A30)
*
* @param row Row to add cell
* @param col Cell
* @param sumCol Name of column to sum
* @param row1 Start row
* @param row2 En Row
*/
public void setSum(Row row, int col, String sumCol, int row1, int row2) {
createFormula(row, col, -1, "SUM(" + sumCol + row1 + ":" + sumCol + row2 + ")", getCsBoldAmount());
}
/**
* Create a row with a title
*
* @param rowIndex Row index
* @param title Sheet title
* @return Excel Row
*/
public Row createTitle(int rowIndex, String title) {
Row row = sheet.createRow(rowIndex);
createCell(row, 0, title, csCaption);
return row;
}
/*
* Create cells
*/
public void createCell(Row row, int col, String value, CellStyle cs) {
Cell c = row.createCell(col);
c.setCellValue(xlsFormat ? new HSSFRichTextString(value) : new XSSFRichTextString(value));
if (cs != null)
c.setCellStyle(cs);
}
public void createCell(Row row, int col, Double value, CellStyle cs) {
Cell c = row.createCell(col);
if (value == null)
c.setCellValue("");
else
c.setCellValue(value);
if (cs != null)
c.setCellStyle(cs);
}
public void createCell(Row row, int col, Date value, CellStyle cs) {
Cell c = row.createCell(col);
if (value == null)
c.setCellValue("");
else
c.setCellValue(value);
if (cs != null)
c.setCellStyle(cs);
}
public void createCell(Row row, int col, Time value, CellStyle cs) {
Cell c = row.createCell(col);
if (value == null)
c.setCellValue("");
else
c.setCellValue(Converter.formatDate(value, "HH:mm:ss"));
if (cs != null)
c.setCellStyle(cs);
}
public void createCell(Row row, int col, Long value, CellStyle cs) {
Cell c = row.createCell(col);
if (value == null)
c.setCellValue("");
else
c.setCellValue(value);
if (cs != null)
c.setCellStyle(cs);
}
public void createCell(Row row, int col, Object obj, CellStyle cs) {
if (obj == null)
return;
if (obj instanceof Time)
createCell(row, col, (Time) obj, cs);
else if (obj instanceof Date)
createCell(row, col, (Date) obj, csNormalDate);
else if (obj instanceof Double)
createCell(row, col, (Double) obj, cs);
else if (obj instanceof Integer)
createCell(row, col, ((Integer) obj).longValue(), cs);
else if (obj instanceof Long)
createCell(row, col, (Long) obj, cs);
else
createCell(row, col, obj.toString(), cs);
}
/**
* Create a cell width image
*
* @param row Row
* @param imagePath Image full path
* @param rowIndx Row index
* @param colIndx Column index
* @param rowHeight Row Height to set
* @throws IOException
*/
public void createCellImage(Row row, File imagePath, int rowIndx, int colIndx, int rowHeight) throws IOException {
row.createCell(colIndx);
row.setHeight((short) (rowHeight * 250));
// read the image to the stream
final FileInputStream stream = new FileInputStream(imagePath);
final CreationHelper helper = wb.getCreationHelper();
final Drawing drawing = sheet.createDrawingPatriarch();
final ClientAnchor anchor = helper.createClientAnchor();
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
final int pictureIndex = wb.addPicture(IOUtils.toByteArray(stream), Workbook.PICTURE_TYPE_PNG);
anchor.setCol1(colIndx);
anchor.setRow1(rowIndx);
anchor.setRow2(rowIndx);
anchor.setCol2(colIndx + 1);
final Picture pict = drawing.createPicture(anchor, pictureIndex);
pict.resize();
stream.close();
}
public void createFormula(Row row, int col, int rowIndx, String value, CellStyle cs) {
if (rowIndx != -1)
value = value.replaceAll("%", Integer.toString(rowIndx+1));
Cell c = row.createCell(col);
c.setCellFormula(value);
c.setCellStyle(cs);
}
public Row createRow(Sheet sheet, int indx, int col, String value, CellStyle cs) {
Row row = sheet.createRow(indx);
createCell(row, col, value, cs);
return row;
}
/**
* get predefined styles
*
* @return Style
*/
public CellStyle getStyleCaption() {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short) 14);
font.setColor(Font.COLOR_NORMAL);
font.setBold(true);
style.setFont(font);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
return style;
}
public CellStyle getStyleColTitle() {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setColor(Font.COLOR_NORMAL);
font.setColor(Font.COLOR_NORMAL);
font.setBold(true);
style.setFont(font);
return style;
}
public CellStyle getStyleBold() {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short) 8);
font.setBold(true);
font.setColor(Font.COLOR_NORMAL);
style.setFont(font);
return style;
}
public CellStyle getStyleNormal() {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short) 8);
font.setColor(Font.COLOR_NORMAL);
style.setFont(font);
return style;
}
public CellStyle getStyleNormalRed() {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short) 8);
font.setBold(true);
font.setColor(Font.COLOR_RED);
style.setFont(font);
return style;
}
public CellStyle getStyleNormalDate() {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short) 8);
font.setColor(Font.COLOR_NORMAL);
style.setFont(font);
style.setDataFormat((short) 14);
return style;
}
public CellStyle getStyleNormalAmount() {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short) 8);
font.setColor(Font.COLOR_NORMAL);
style.setFont(font);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
return style;
}
public CellStyle getStyleBoldAmount() {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
font.setFontHeightInPoints((short) 8);
font.setBold(true);
font.setColor(Font.COLOR_NORMAL);
style.setFont(font);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
return style;
}
/**
* Get a cell value
*
* @param cell Cell
* @return Value
*/
public static Object getCellValue(Cell cell) {
if (cell != null) {
CellType cellType = cell.getCellTypeEnum();
if (cellType == CellType.STRING) {
return cell.getRichStringCellValue().getString();
} else if (cellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
return cell.getNumericCellValue();
}
} else if (cellType == CellType.BOOLEAN) {
return cell.getBooleanCellValue();
} else if (cellType == CellType.FORMULA) {
cell.getCellFormula();
}
}
return null;
}
public static Date getDateValue(Cell cell) {
if (cell != null) {
CellType cellType = cell.getCellTypeEnum();
if (cellType == CellType.STRING) {
String value = cell.getRichStringCellValue().getString();
Date result = Converter.getDate(value, "dd/MM/yyyy");
if (result == null)
result = Converter.getDate(value, "dd-MM-yyyy");
return result;
} else if (cellType == CellType.NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
}
}
}
return null;
}
public static String getStringValue(Cell cell) {
if (cell == null)
return null;
CellType cellType = cell.getCellTypeEnum();
if (cellType == CellType.NUMERIC) {
Number value = cell.getNumericCellValue();
Double dV = value.doubleValue();
Long lV = value.longValue();
if (dV > lV)
return ""+ dV;
else
return ""+ lV;
} else {
Object value = getCellValue(cell);
return value != null ? value.toString() : null;
}
}
public static Long getLongValue(Cell cell) {
return getLongValue(cell, null);
}
public static Long getLongValue(Cell cell, Long defaultVal) {
if (cell != null) {
CellType cellType = cell.getCellTypeEnum();
if (cellType == CellType.STRING) {
return Converter.getLong(cell.getRichStringCellValue().getString().trim(), defaultVal);
} else if (cellType == CellType.NUMERIC) {
return ((Number) cell.getNumericCellValue()).longValue();
}
}
return defaultVal;
}
public static Double getDoubleValue(Cell cell) {
return getDoubleValue(cell, null);
}
public static Double getDoubleValue(Cell cell, Double defaultVal) {
if (cell != null) {
CellType cellType = cell.getCellTypeEnum();
if (cellType == CellType.STRING) {
return Converter.getFloat(cell.getRichStringCellValue().getString().trim(), defaultVal);
} else if (cellType == CellType.NUMERIC) {
return ((Number) cell.getNumericCellValue()).doubleValue();
}
}
return defaultVal;
}
public static Boolean getBooleanValue(Cell cell) {
if (cell != null) {
CellType cellType = cell.getCellTypeEnum();
if (cellType == CellType.STRING) {
String value = cell.getRichStringCellValue().getString().trim().toUpperCase();
return "SI".equals(value) || "SÍ".equals(value) || "TRUE".equals(value) || "YES".equals(value) || "Y".equals(value) || "1".equals(value);
} else if (cellType == CellType.NUMERIC) {
return cell.getNumericCellValue() > 0;
} else if (cellType == CellType.BOOLEAN) {
return cell.getBooleanCellValue();
} else if (cellType == CellType.FORMULA) {
return false;
}
}
return false;
}
/**
* Check if all cell in a row are empty
*
* @param row Row
* @param cols Column to check
* @return True if all cell are empty
*/
public static boolean isEmptyRow(Row row, int cols) {
if (row == null)
return true;
for (int i = 0; i < cols; i++) {
Object cell = row.getCell(i);
if (cell != null && !"".equals(cell.toString()))
return false;
}
return true;
}
/*
* Get/Set methods
*/
public String getFile() {
return file;
}
public void setFile(String file) {
this.file = file;
}
public boolean isToFile() {
return toFile;
}
public void setToFile(boolean toFile) {
this.toFile = toFile;
}
public boolean isToRequest() {
return toRequest;
}
public void setToRequest(boolean toRequest) {
this.toRequest = toRequest;
}
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public Workbook getWb() {
return wb;
}
public Sheet getSheet() {
return sheet;
}
public CellStyle getCsCaption() {
return csCaption;
}
public void setCsCaption(CellStyle csCaption) {
this.csCaption = csCaption;
}
public CellStyle getCsColTitle() {
return csColTitle;
}
public void setCsColTitle(CellStyle csColTitle) {
this.csColTitle = csColTitle;
}
public CellStyle getCsBold() {
return csBold;
}
public void setCsBold(CellStyle csBold) {
this.csBold = csBold;
}
public CellStyle getCsNormal() {
return csNormal;
}
public void setCsNormal(CellStyle csNormal) {
this.csNormal = csNormal;
}
public CellStyle getCsNormalRed() {
return csNormalRed;
}
public void setCsNormalRed(CellStyle csNormalRed) {
this.csNormalRed = csNormalRed;
}
public CellStyle getCsNormalDate() {
return csNormalDate;
}
public void setCsNormalDate(CellStyle csNormalDate) {
this.csNormalDate = csNormalDate;
}
public CellStyle getCsNormalAmount() {
return csNormalAmount;
}
public void setCsNormalAmount(CellStyle csNormalAmount) {
this.csNormalAmount = csNormalAmount;
}
public CellStyle getCsBoldAmount() {
return csBoldAmount;
}
public void setCsBoldAmount(CellStyle csBoldAmount) {
this.csBoldAmount = csBoldAmount;
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy