ExcelUtil —— excel相关通用方法总结
1、上传下载
2、样式设置
3、单元格设置检测
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
public class ExcelUtil {
private static final Logger LOG = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 将Excel工作簿用response输出流输出(默认导出为.xls格式)
*
* @param wb 输出的工作薄
* @param rp 前端响应response
*/
public static void exportExcel(Workbook wb, HttpServletResponse rp) {
String time = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
int random = (int) (Math.random() * 1000);
//获得随机文件名
String name = time + random;
rp.reset();
rp.setContentType("application/octet-stream;charset=GBK");
try {
rp.setHeader("Content-Disposition", "attachment;filename=" + new String((name + ".xls").getBytes("ISO8859-1"), "UTF-8"));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
rp.setCharacterEncoding("UTF-8");
try(OutputStream out = rp.getOutputStream()) {
wb.write(out);
} catch (Exception e) {
LOG.error(e.getMessage());
}
}
/**
* 设置通用样式的单元格
*
* @param cellNo 单元格号
* @param cellValue 单元格内容
*/
public void setCell(Row row, int cellNo, Object cellValue) {
Cell cell = row.getCell(cellNo);
if (cell == null){
cell = row.createCell(cellNo);
}
if (cellValue == null) {
cell.setCellValue("");
} else {
String cellType = cellValue.getClass().getName();
cellType = cellType.substring(cellType.lastIndexOf(".") + 1, cellType.length());
if (cellType.equals("String")) {
cell.setCellValue(cellValue.toString());
} else if (cellType.equals("Double") || cellType.equals("BigDecimal")) {
cell.setCellValue(Double.parseDouble(cellValue.toString()));
} else if (cellType.equals("Integer")) {
cell.setCellValue(Integer.parseInt(cellValue.toString()));
} else if (cellType.equals("HSSFPicture")) {
cell.setCellValue("");
} else if (cellType.equals("Timestamp")) {
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(formater.format(cellValue));
} else if (cellType.equals("Date")) {
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(formater.format(cellValue));
} else {
cell.setCellValue("未知类型");
}
}
}
/**
* 给指定单元格加边框,垂直水平居中,宋体8号,不加粗,自动换行
*
* @param cell 设置的单元格
*/
public void setFrameCommon(Cell cell, Workbook wb, Font font) {
CellStyle xc = wb.createCellStyle();
xc.setBorderBottom(CellStyle.BORDER_THIN);
xc.setBorderTop(CellStyle.BORDER_THIN);
xc.setBorderLeft(CellStyle.BORDER_THIN);
xc.setBorderRight(CellStyle.BORDER_THIN);
xc.setAlignment(CellStyle.ALIGN_CENTER);
xc.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
xc.setWrapText(true);
xc.setFont(font);
cell.setCellStyle(xc);
}
/**
* 通用文件上传
*
* @param file 上传的文件
* @param path 上传的地址
* @return 上传的文件本地路径
*/
@RequestMapping("importCstm")
public String importCstm(@RequestParam("file") MultipartFile file, String path) {
try {
UploadFile uf = new UploadFile();
uf.createFolder(path);
String fileName = uf.uploadFile(file.getInputStream(), file.getOriginalFilename(), path);
String fileUrl = path + "/" + fileName;
return fileUrl;
} catch (Exception e) {
// 文件上传异常
e.printStackTrace();
return null;
}
}
/**
* 根据相应路径获得工作簿
*
* @param url 文件路径
* @return 工作簿
*/
public Workbook readExcel(String url) {
Workbook wb = null;
String type = url.substring(url.lastIndexOf(".") + 1,
url.length());
try (FileInputStream fis = new FileInputStream(url)){
//判断文件类型
if (type.equals("xls")) {
wb = new HSSFWorkbook(fis);
} else if (type.equals("xlsx")) {
wb = new XSSFWorkbook(fis);
} else {
wb = null;
}
} catch (Exception e) {
wb = null;
}
return wb;
}
/**
* 给指定单元格加边框,垂直水平居中,宋体8号,加粗,自动换行
*/
public void setFrameBoldweight(CellStyle cs) {
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
/**
* 给指定单元格加边框,垂直水平居中,宋体8号,加粗,自动换行
* @param cell 设置的单元格
*/
public void setFrameBoldweight(Cell cell, Workbook wb, Font font) {
CellStyle xc = wb.createCellStyle();
xc.setBorderBottom(CellStyle.BORDER_THIN);
xc.setBorderTop(CellStyle.BORDER_THIN);
xc.setBorderLeft(CellStyle.BORDER_THIN);
xc.setBorderRight(CellStyle.BORDER_THIN);
xc.setAlignment(CellStyle.ALIGN_CENTER);
xc.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
xc.setWrapText(true);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
xc.setFont(font);
cell.setCellStyle(xc);
}
/**
* 给指定单元格加左边框,垂直水平居中,宋体8号,不加粗,自动换行
*/
public void setFrameCommonOnlyLeft(CellStyle cs) {
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_NONE);
cs.setBorderBottom(CellStyle.BORDER_NONE);
cs.setBorderTop(CellStyle.BORDER_NONE);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
/**
* 给指定单元格除上、右边外加边框,垂直水平居中,宋体8号,不加粗,自动换行
*/
public void setFrameCommonNoRT(CellStyle cs) {
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_NONE);
cs.setBorderRight(CellStyle.BORDER_NONE);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
/**
* 给指定单元格除上、左边外加边框,垂直水平居中,宋体8号,不加粗,自动换行
*/
public void setFrameCommonNoLT(CellStyle cs) {
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderLeft(CellStyle.BORDER_NONE);
cs.setBorderTop(CellStyle.BORDER_NONE);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
/**
* 给指定单元格除右边外加边框,垂直水平居中,宋体8号,不加粗,自动换行
*/
public void setFrameCommonNoRight(CellStyle cs) {
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_NONE);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
/**
* 给指定单元格除左、右边外加边框,垂直水平居中,宋体8号,不加粗,自动换行
*/
public void setFrameCommonNoLR(CellStyle cs) {
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderLeft(CellStyle.BORDER_NONE);
cs.setBorderRight(CellStyle.BORDER_NONE);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
/**
* 给指定单元格加右边框,垂直水平居中,宋体8号,不加粗,自动换行
*/
public void setFrameCommonOnlyRight(CellStyle cs) {
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderLeft(CellStyle.BORDER_NONE);
cs.setBorderBottom(CellStyle.BORDER_NONE);
cs.setBorderTop(CellStyle.BORDER_NONE);
cs.setAlignment(CellStyle.ALIGN_CENTER);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
/**
* 给指定单元格除左、右边外加边框,垂直水平居中,宋体8号,不加粗,自动换行
* @param cell 设置的单元格
*/
public void setFrameCommonNoLR(Cell cell, Workbook wb, Font font) {
CellStyle xc = wb.createCellStyle();
xc.setBorderBottom(CellStyle.BORDER_THIN);
xc.setBorderTop(CellStyle.BORDER_THIN);
xc.setAlignment(CellStyle.ALIGN_CENTER);
xc.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
xc.setWrapText(true);
xc.setFont(font);
cell.setCellStyle(xc);
}
}
Excel设置图片
public Workbook demo( String[] imgs, HttpServletRequest request) {
Workbook wb = null;
Sheet sheet = null;
BufferedImage bufferImg = null;
// 项目目录
String classPath = request.getServletContext().getRealPath("");
// 决裁书模板文件 webapp/
File file = new File(classPath, "/templet/demo.xls");
HSSFPatriarch patriarch = null;
try (FileInputStream is = new FileInputStream(file)) {
wb = new HSSFWorkbook(is);
sheet = wb.getSheetAt(0);
// 1、图片绘制管理器
patriarch = (HSSFPatriarch) sheet.createDrawingPatriarch();
ByteArrayOutputStream[] byteArrayOut = new ByteArrayOutputStream[8];
// 2、图片位置个数
HSSFClientAnchor[] anchor = new HSSFClientAnchor[8];
// 将图片写入excel中指定位置
for (int i = 0; i < imgs.length; i++) {
if (!"".equals(imgs[i])) {
ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
// 3、读取图片
bufferImg = ImageIO.read(new File(classPath + imgs[i]));
ImageIO.write(bufferImg, "jpg", byteArrayOut1);
byteArrayOut[i] = byteArrayOut1;
// 4、设置位置
anchor[i] = new HSSFClientAnchor(80, 50, 960, 215, (short) i, 3, (short) i, 3);
// 5、填充图片
patriarch.createPicture(anchor[i], wb.addPicture(byteArrayOut[i].toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
} else {
byteArrayOut[i] = null;
anchor[i] = null;
}
}
} catch (Exception e) {
LOG.warn(e.getMessage());
}
return wb;
}
Excel合并单元格
CellRangeAddress cra = new CellRangeAddress(1, 2, 0, 0);
sheet.addMergedRegion(cra);