Nemo

Nemo 关注TA

路漫漫其修远兮,吾将上下而求索。

Nemo

Nemo

关注TA

路漫漫其修远兮,吾将上下而求索。

  •  普罗旺斯
  • 负责帅就完事了
  • 写了1,493,291字

该文章投稿至Nemo社区   Java  板块 复制链接


ExcelxlsWriter java excel操作工具类

发布于 2017/11/02 16:16 2,650浏览 0回复 6,937


import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
* 说明:从XLS文件中创建模板 Copyright: Copyright (c) 2009
*
* 2003 或者更低版本
*
* @author Nemo
*/
public abstract class ExcelxlsWriter<T> {

protected Logger log = LoggerFactory.getLogger(this.getClass());

/**
* 替换掉 特殊字符
*
* @return
*/
public final static String regEx = "[`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~!@#¥%……&*()——+|{}【】《》';:”“’‘。,、?\\-]";

protected HSSFWorkbook workbook;

private int fromIdx = 1;

private String filename = "";

private List<T> list;

/**
* xls file 文件
*
* @param xlsFile
* @throws IOException
* @throws IOException
*/
public void build(java.io.InputStream xlsIs, List<T> list, String sheetName, Map<String, Object> headData) throws IOException {
workbook = new HSSFWorkbook(xlsIs);
buildHeader(headData);
buildBody(list);
buildFoot();
workbook.setSheetName(0, sheetName);
xlsIs.close();
}

/**
* xls file 文件
*
* @param xlsFile
* @throws IOException
* @throws IOException
*/
public void build(java.io.InputStream xlsIs, List<T> data, Map<String, Object> headData, String sheetName) throws IOException {
workbook = new HSSFWorkbook(xlsIs);
buildHeader(headData);
buildBody(data);
buildFoot();
workbook.setSheetName(0, sheetName);
xlsIs.close();
}

public HSSFSheet getSheetAt(int index) {
return workbook.getSheetAt(index);
}

abstract public void buildHeader(Map<String, Object> headData);

abstract public void buildBody(List<T> data);

abstract public void buildFoot();


public void createCell(HSSFRow row, int columIndex, BigDecimal content) {
HSSFCell cell = row.createCell(columIndex);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(content.doubleValue());
}

public void createCell(HSSFRow row, int columIndex, Double content) {
HSSFCell cell = row.createCell(columIndex);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(content);
}

public void createCell(HSSFRow row, int columIndex, Date content) {
HSSFCell cell = row.createCell(columIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(DateTimeUtils.format(content, DateTimeUtils.FORMAT_SHORT));
}


public void createCell(HSSFRow row, int columIndex, Long content) {
HSSFCell cell = row.createCell(columIndex);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(content);
}

public void createCell(HSSFRow row, int columIndex, Integer content) {
if (content != null) {
HSSFCell cell = row.createCell(columIndex);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(content);
} else {
HSSFCell cell = row.createCell(columIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("");
}
}

public void createCell(HSSFRow row, int columIndex, int content) {
if (content != 0) {
HSSFCell cell = row.createCell(columIndex);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(content);
} else {
HSSFCell cell = row.createCell(columIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("");
}
}

public void createCell(HSSFRow row, int columIndex, String content) {
HSSFCell cell = row.createCell(columIndex);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(content);
}

/**
* 设置单元格公式
*
* @param row
* @param columIndex
* @param content
*/
public void createCellFormula(HSSFRow row, int columIndex, String formula) {
HSSFCell cell = row.createCell(columIndex);
cell.setCellFormula(formula);
}

/**
* 拼接公式
*/
protected String contractFormula() {
return null;
}

/**
* @return the workbook
*/
public HSSFWorkbook getWorkbook() {
return workbook;
}


/**
* @param workbook the workbook to set
*/
public void setWorkbook(HSSFWorkbook workbook) {
this.workbook = workbook;
}

public static String replaceAllSpecChar(String srcStr) {
if (srcStr == null || "".equals(srcStr)) {
return srcStr;
}

Pattern pattern = Pattern.compile(regEx);
Matcher matcher = pattern.matcher(srcStr);

StringBuffer ret = new StringBuffer();
while (matcher.find()) {
matcher.appendReplacement(ret, "");
}
return ret.toString();
}


/**
* 向excel中插入图片
*
* @param sheet 操作对象
* @param filePath 文件路径
* @param imgFormat 图片格式
* @param col1 起始列
* @param row1 起始行
* @param col2 结束列
* @param row2 结束行 by.LanWei
*/
public void insertImage(HSSFSheet sheet, String filePath, String imgFormat, short col1, int row1, short col2, int row2) {
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg;
try {
bufferImg = ImageIO.read(new File(filePath));
ImageIO.write(bufferImg, imgFormat, byteArrayOut);
} catch (IOException e) {
log.error("插入图片异常", e);
}

HSSFPatriarch patriarch = (HSSFPatriarch) sheet.createDrawingPatriarch();
// excel默认单元格宽1023,高255
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, col1, row1, col2, row2);
patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
}


/**
* @return the fromIdx
*/
public int getFromIdx() {
return fromIdx;
}

/**
* @param fromIdx the fromIdx to set
*/
public void setFromIdx(int fromIdx) {
this.fromIdx = fromIdx;
}

public String getFilename() {
return filename;
}

public void setFilename(String filename) {
this.filename = filename;
}

/**
* @return: List<T> <br>
*/
public List<T> getList() {
return list;
}


/**
* @return: List<T> <br>
*/
public void setList(List<T> list) {
this.list = list;
}
}
点赞(0)
点了个评