java后台poi根据模板导出excel

public class ExcelUtils { private static final String INSPECTIONRECORD_SURFACE_TEMPLET_PATH = "download\\template\\materialList.xlsx";
private static XSSFCellStyle cellstyle = null; public static void exportBom(HttpServletRequest request, HttpServletResponse response, Map map) throws IOException {
//模板的路径，这个在自己的项目中很容易弄错，相对位置一定要写对啊
String psth = request.getRealPath("/") + INSPECTIONRECORD_SURFACE_TEMPLET_PATH;
Workbook webBook = readExcel(psth);
Sheet sheet = webBook.getSheetAt(0);
//调用样式方法
cellstyle = createCellStyle(webBook);
sheet.setColumnWidth(0, 3766);//列宽
sheet.setColumnWidth(1, 4766);//列宽
//开始操作模板，找到某行某列（某个cell），需要注意的是这里有个坑，行和列的计数都是从0开始的
//一次数据插入的位置不对，别灰心，多试几次就好啦，你要是能看懂我下面的代码，数据插在了什么位置，你就明白了
//打印时间
int rows = 0;
Row row = sheet.getRow(rows);
//调用方法，赋值并给定样式（写样式的时候这个工具类只能用XSSF而不能用HSSF,不然样式会没响应）
setCellStyleWithValue(row.createCell(0),(String) map.get("title")); rows = 2;
row = sheet.getRow(rows);
row.createCell(1).setCellValue((String) map.get("date"));
// setCellStyleWithValue(row.createCell(1),(String) map.get("date")); //负责人
rows = 3;
row = sheet.getRow(rows);
row.createCell(1).setCellValue((String) map.get("leader"));
// setCellStyleWithValue(row.createCell(1),(String) map.get("leader")); //审核人
rows = 4;
row = sheet.getRow(rows);
row.createCell(1).setCellValue((String) map.get("Auditleader"));
// setCellStyleWithValue(row.createCell(1),(String) map.get("Auditleader")); //在调用模板的时候，数据的插入不能直接应用模板行，不然数据会覆盖掉哪行
rows = 7;
row = sheet.createRow(rows);
row.createCell(0).setCellValue("编号");
row.createCell(1).setCellValue("名称");
row.createCell(2).setCellValue("规格");
row.createCell(3).setCellValue("数量");
row.createCell(4).setCellValue("益值");
row.setRowStyle(cellstyle); List<Map<String, Object>> list = (List<Map<String, Object>>) map.get("resultList");
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(rows++);
/* row.createCell(0).setCellValue(StringUtils.objToStr(list.get(i).get("物料编号")));
row.createCell(1).setCellValue(StringUtils.objToStr(list.get(i).get("物料名称")));
row.createCell(2).setCellValue(StringUtils.objToStr(list.get(i).get("物料规格")));
row.createCell(3).setCellValue(Double.parseDouble(StringUtils.objToStr(list.get(i).get("物料消耗数量"))));
row.createCell(4).setCellValue(Float.parseFloat(StringUtils.objToStr(list.get(i).get("物料消耗损益值"))));*/ setCellStyleWithValue(row.createCell(0),StringUtils.objToStr(list.get(i).get("物料编号")));
setCellStyleWithValue(row.createCell(1),StringUtils.objToStr(list.get(i).get("物料名称")));
setCellStyleWithValue(row.createCell(2),StringUtils.objToStr(list.get(i).get("物料规格")));
setCellStyleWithValue(row.createCell(3),Double.parseDouble(StringUtils.objToStr(list.get(i).get("物料消耗数量"))));
setCellStyleWithValue(row.createCell(4),Float.parseFloat(StringUtils.objToStr(list.get(i).get("物料消耗损益值")))); }
writeExcel(response, webBook, (String) map.get("title"));
} private static XSSFWorkbook readExcel(String filePath) {
InputStream in = null;
XSSFWorkbook work = null;
try {
in = new FileInputStream(filePath);
work = new XSSFWorkbook(in);
} catch (FileNotFoundException e) {
System.out.println("文件路径错误");
e.printStackTrace();
} catch (IOException e) {
System.out.println("文件输入流错误");
e.printStackTrace();
}
return work;
} private static void writeExcel(HttpServletResponse response, Workbook work, String fileName) throws IOException {
OutputStream out = null;
try {
out = response.getOutputStream();
response.setContentType("application/ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="
.concat(String.valueOf(URLEncoder.encode(fileName + ".xls", "UTF-8"))));
work.write(out);
} catch (IOException e) {
System.out.println("输出流错误");
e.printStackTrace();
} finally {
out.close();
}
} private static Cell setCellStyleWithStyleAndValue(CellStyle style, Cell cell, String value) {
cell.setCellStyle(style);
cell.setCellValue(value);
return cell;
} private static Cell setCellStyleWithStyleAndValue(CellStyle style, Cell cell, Double value) {
cell.setCellStyle(style);
cell.setCellValue(value);
return cell;
} private static Cell setCellStyleWithValue(Cell cell, String value) {
cell.setCellStyle(cellstyle);
cell.setCellValue(value);
return cell;
} private static Cell setCellStyleWithStyleAndValue(CellStyle style, Cell cell, RichTextString value) {
cell.setCellStyle(style);
cell.setCellValue(value);
return cell;
} private static Cell setCellStyleWithValue(Cell cell, int value) {
cell.setCellStyle(cellstyle);
cell.setCellValue(value);
return cell;
} private static Cell setCellStyleWithValue(Cell cell, double value) {
cell.setCellStyle(cellstyle);
cell.setCellValue(value);
return cell;
} private static XSSFCellStyle createCellStyle(Workbook wb) {
cellstyle = (XSSFCellStyle) wb.createCellStyle();
cellstyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// cellstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
// cellstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
// cellstyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
// cellstyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
cellstyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
return cellstyle;
}
}我自己的空模板，有数据的图就不贴出来了！