导入处理excel的jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
然后是通用类
public class ExcelUtils {
/*
* 导出excel
*
* @param tempPath
* @param path
* @param list
* @return
*/
public String exportExcel(String tempPath, String path, List<Model> list) {
File newFile = createNewFile(tempPath, path);
InputStream is = null;
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
workbook = new XSSFWorkbook(is);// 创建个workbook,
// 获取第一个sheet
sheet = workbook.getSheetAt(0);
} catch (Exception e1) {
e1.printStackTrace();
}
if (sheet != null) {
try {
// 写数据
FileOutputStream fos = new FileOutputStream(newFile);
XSSFRow row = sheet.getRow(1);
if (row == null) {
row = sheet.createRow(1);
}
XSSFCell cell = row.getCell(0);
if (cell == null) {
cell = row.createCell(0);
}
cell.setCellValue("副标题");
for (int i = 0; i < list.size(); i++) {
Model vo = list.get(i);
row = sheet.createRow(i + 4);// 从第五行开始
CellStyle cellStyle = workbook.createCellStyle(); // 创建单元格样式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 设置单元格水平方向对其方式
cellStyle
.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格垂直方向对其方式
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
// 设置单元格的样式与内容
createRowAndCellStyle(vo.getId(), row, cell, 0, cellStyle);
createRowAndCellStyle(vo.getIssueTime(), row, cell, 1,
cellStyle);
createRowAndCellStyle(vo.getNeedTime(), row, cell, 2,
cellStyle);
createRowAndCellStyle(vo.getSignalType(), row, cell, 3,
cellStyle);
createRowAndCellStyle(vo.getSignalLevel(), row, cell, 4,
cellStyle);
createRowAndCellStyle(vo.getUnderWriter(), row, cell, 5,
cellStyle);
}
workbook.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != is) {
is.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
return newFile.getName();
}
/**
* 根据当前row行,来创建index标记的列数,并赋值数据(对样式做更改)
*
* @param obj
* @param row
* @param cell
* @param index
*/
private void createRowAndCellStyle(Object obj, XSSFRow row, XSSFCell cell,
int index, CellStyle cellStyle) {
cell = row.getCell(index);
if (cell == null) {
cell = row.createCell(index);
}
if (obj != null) {
cell.setCellValue(obj.toString());
} else {
cell.setCellValue("");
}
cell.setCellStyle(cellStyle);
}
/**
* 复制文件
*
* @param s
* 源文件
* @param t
* 复制到的新文件
*/
public void fileChannelCopy(File s, File t) {
try {
InputStream in = null;
OutputStream out = null;
try {
in = new BufferedInputStream(new FileInputStream(s), 1024);
out = new BufferedOutputStream(new FileOutputStream(t), 1024);
byte[] buffer = new byte[1024];
int len;
while ((len = in.read(buffer)) != -1) {
out.write(buffer, 0, len);
}
} finally {
if (null != in) {
in.close();
}
if (null != out) {
out.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取excel模板,并复制到新文件中供写入和下载
*
* @return
*/
public File createNewFile(String tempPath, String rPath) {
// 读取模板,并赋值到新文件************************************************************
// 文件模板路径
String path = (tempPath);
File file = new File(path);
// 保存文件的路径
String realPath = rPath;
// 新的文件名
String newFileName = System.currentTimeMillis() + ".xlsx";
// 判断路径是否存在
File dir = new File(realPath);
if (!dir.exists()) {
dir.mkdirs();
}
// 写入到新的excel
File newFile = new File(realPath, newFileName);
try {
newFile.createNewFile();
// 复制模板到新文件
fileChannelCopy(file, newFile);
} catch (Exception e) {
e.printStackTrace();
}
return newFile;
}
/**
* 对时间格式进行设置
*
* @param parseDate
* @return
*/
private String parseNeedDateFormat(String parseDate) {
String afterTime = parseDate;
SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat df2 = new SimpleDateFormat("yyyy年MM月dd日");
try {
Date time = df1.parse(parseDate);
afterTime = df2.format(time);
} catch (ParseException e) {
e.printStackTrace();
}
return afterTime;
}
}