java後台poi根據模板匯出excel

標籤：編號   xls   and   header   ott   head   http   size   order   

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;    }}

我自己的空模板，有資料的圖就不貼出來了！

 

java後台poi根據模板匯出excel