话不多说,直接上代码
import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
@SuppressWarnings("deprecation")
public class ExportPerformanceTemplet {
// 显示的导出表的标题
//private String title;
//导出数据集合
private List<Object[]> dataList = new ArrayList<Object[]>();
private HttpServletResponse response;
//模版位置
private String templetPath = "";
//用户职位模版
private Integer job;
// 构造方法,传入要导出的数据
/*
* dataList 传入数据
* response response对象,用于获取流
* job 导出的模版属性(个人需要)
* templetPath 导出模版的位置
*/
public ExportPerformanceTemplet( List<Object[]> dataList, HttpServletResponse response, Integer job,String templetPath) {
this.dataList = dataList;
this.response = response;
this.job = job;
this.templetPath = templetPath;
}
/**
* 绩效模版导出方法
* @Title: export
* @Description: TODO
* @param @param out
* @param @throws Exception
* @return void
* @throws
*/
public void export(OutputStream out) throws Exception {
// 定义所需列数
//int columnNum = 4;
FileInputStream fis = null;
try {
//读取模版中的内容生成新的excel文档
fis = new FileInputStream(templetPath);
XSSFWorkbook workBook=new XSSFWorkbook(fis);
//定义新的excel名称
//String fileName=title+".xlsx";
out = response.getOutputStream();
//获取cell样式
XSSFCellStyle style = CellStyle.getStyle(workBook);
XSSFSheet sheet = workBook.getSheetAt(job);//获取到对应职位的sheet
//所有的sheet从第九行开始
int rowIndex=9;
XSSFRow firstRow = sheet.getRow(rowIndex);
firstRow.setHeight((short)(20*30));
Object[] objects = dataList.get(0);
//遍历放入第一行数据
for (int i = 1; i < 5; i++) {
XSSFCell Cell = firstRow.getCell(i);
Cell.setCellValue(objects[i]==null?"":objects[i].toString());
Cell.setCellStyle(style);
}
if(dataList.size()>1){
//将所有数据向下拖动N行(N为总数据条数-1)(模版中默认有第一行,只需下拉即可)
sheet.shiftRows(rowIndex+1, 30, dataList.size()-1);
//插入对应行数(在空白处插入行)
for (int i = 1; i < dataList.size(); i++) {
Object[] obj = dataList.get(i);
XSSFRow row=sheet.createRow(rowIndex+i);
//设置行高
row.setHeight((short)(20*30));
//循环放入数据并赋样式
for (int j = 0; j < obj.length; j++) {
XSSFCell cell = null; // 设置单元格的数据类型
cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
if (!"".equals(obj[j]) && obj[j] != null) {
cell.setCellValue(obj[j].toString()); // 设置单元格的值
}else{
cell.setCellValue(""); // 设置单元格的值
}
cell.setCellStyle(style); // 设置单元格样式
}
}
//合并第一列(看实际情况使用)
sheet.addMergedRegion(new CellRangeAddress(rowIndex,rowIndex+(dataList.size()-1),0,0));
}
workBook.write(out);
out.flush();
} catch (Exception e) {
e.printStackTrace();
}finally {
fis.close();
out.close();
}
}
}
个人成果,如有不足之处希望大家多指正,谢谢