Excel匯出實列

weixin_41696089發表於2020-11-20

1、html呼叫

<td><button type="button" onclick="exportlist()"><span class="icon_export">匯出</span></button></td>

function exportlist() {
window.location.href=basePath+"Security/Risk/export_all?unit_id="+encodeURI(unit_id)+"&flag="+encodeURI($("#flag").val())+"&personnel_name="+encodeURI($("#personnel_name").val());
}

2、Controller

@ResponseBody
@RequestMapping("export_all")
public String export_all(HttpServletRequest request, HttpServletResponse response, RiskPersonnelEntity entity) {
    return riskService.export(request,response,entity);
}

3、server處理

單行表頭

@Override
    public String export(HttpServletRequest request, HttpServletResponse response, RiskPersonnelEntity entity) {
        // 建立excel
        HSSFWorkbook hssf = new HSSFWorkbook();
        // 樣式表
        HSSFCellStyle style = hssf.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 建立一個居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下邊框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左邊框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右邊框
        style.setWrapText(true);// 迴繞文字

        // 表頭樣式
        HSSFCellStyle style3 = hssf.createCellStyle();
        style3.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 建立一個居中格式
        style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下邊框
        style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左邊框
        style3.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
        style3.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右邊框
        style3.setWrapText(true);// 迴繞文字
        HSSFFont font1 = hssf.createFont();
        font1.setFontName("黑體");
        font1.setFontHeightInPoints((short) 11);
        font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗體顯示
        style3.setFont(font1);

        // 按日期建立工作表
        HSSFSheet sheet = hssf.createSheet("資料列");
        sheet.setColumnWidth(0, 1800);
        sheet.setColumnWidth(1, 3500);
        sheet.setColumnWidth(2, 3500);
        sheet.setColumnWidth(3, 3500);
        sheet.setColumnWidth(4, 3500);
        sheet.setColumnWidth(5, 3500);

        HSSFRow row = sheet.createRow(0);
        HSSFRow row1 = sheet.createRow(1);
        HSSFCell cell = row.createCell(0);
        row.setHeight((short) (15.625 * 30));
        row1.setHeight((short) (15.625 * 30));

        setCell(hssf, sheet, row, cell, style3, 0, 0, 0, 0, "序號");
        setCell(hssf, sheet, row, cell, style3, 0, 0, 1, 1, "單位");
        setCell(hssf, sheet, row, cell, style3, 0, 0, 2, 2, "姓名");

        List<RiskPersonnelEntity> list=riskPersonnelDao.selectByParam(entity);
        int i=1;
        for(RiskPersonnelEntity obj : list){
            HSSFRow rows = sheet.createRow(i);
            rows.setHeight((short) (15.625 * 25));
            setCell(hssf, sheet, rows, cell, style, i, i, 0, 0, i + "");
            setCell(hssf, sheet, rows, cell, style, i, i, 1, 1, obj.getUnit_name());
            setCell(hssf, sheet, rows, cell, style, i, i, 2, 2, obj.getPersonnel_name());
            i++;
        }
// 初始化封裝的excel實體
        workbook = new WorkbookEntity();
        workbook.setFileName("人員資訊");
        workbook.setWorkbook(hssf);
        OutputStream out=null;
        String excelName=null;
        try {
            out = response.getOutputStream();
            response.setContentType("application/msexcel;charset=utf-8");
            excelName = new String(workbook.getFileName().getBytes(),"iso8859-1");
            response.setHeader("Content-disposition", "attachment;filename="+excelName+".xls");
            workbook.getWorkbook().write(out);
            out.flush();
            out.close();
        } catch (IOException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        return "workbook";
    }
private void setCell(HSSFWorkbook hssf, HSSFSheet sheet, HSSFRow row, HSSFCell cell, int a, int b, int c, int d, String string) {
    cell = row.createCell(c);
    cell.setCellValue(string);
    if (a != b || c != d) {
        CellRangeAddress cra = new CellRangeAddress(a, b, c, d);
        sheet.addMergedRegion(cra);
    }
}

private void setCell(HSSFWorkbook hssf, HSSFSheet sheet, HSSFRow row, HSSFCell cell, HSSFCellStyle style, int a, int b, int c, int d, HSSFRichTextString hr) {
    cell = row.createCell(c);
    cell.setCellValue(hr);
    cell.setCellStyle(style);
    if (a != b || c != d) {
        CellRangeAddress cra = new CellRangeAddress(a, b, c, d);
        sheet.addMergedRegion(cra);
        RegionUtil.setBorderBottom(1, cra, sheet, hssf);
        RegionUtil.setBorderLeft(1, cra, sheet, hssf);
        RegionUtil.setBorderRight(1, cra, sheet, hssf);
        RegionUtil.setBorderTop(1, cra, sheet, hssf);
    }
}

public void setCell(HSSFWorkbook hssf, HSSFSheet sheet, HSSFRow row, HSSFCell cell, HSSFCellStyle style, int a, int b, int c, int d, String val) {
    cell = row.createCell(c);
    cell.setCellValue(val);
    cell.setCellStyle(style);
    if (a != b || c != d) {
        CellRangeAddress cra = new CellRangeAddress(a, b, c, d);
        sheet.addMergedRegion(cra);
        RegionUtil.setBorderBottom(1, cra, sheet, hssf);
        RegionUtil.setBorderLeft(1, cra, sheet, hssf);
        RegionUtil.setBorderRight(1, cra, sheet, hssf);
        RegionUtil.setBorderTop(1, cra, sheet, hssf);
    }
}

public void setCell(HSSFWorkbook hssf, HSSFSheet sheet, HSSFRow row, HSSFCell cell, HSSFCellStyle style, int a, int b, int c, int d, double val) {
    cell = row.createCell(c);
    cell.setCellValue(val);
    cell.setCellStyle(style);
    if (a != b || c != d) {
        CellRangeAddress cra = new CellRangeAddress(a, b, c, d);
        sheet.addMergedRegion(cra);
        RegionUtil.setBorderBottom(1, cra, sheet, hssf);
        RegionUtil.setBorderLeft(1, cra, sheet, hssf);
        RegionUtil.setBorderRight(1, cra, sheet, hssf);
        RegionUtil.setBorderTop(1, cra, sheet, hssf);
    }
}

多行表頭

@Override
    public String Export(HttpServletRequest request, HttpServletResponse response, Equipment_WzdbEntity entity) {
        //查詢主計劃資料
        String dbid=null,db_date=null,out_unit=null,enter_unit=null,examine1=null,examine2=null,examine4=null;
        HttpSession session=request.getSession();
        String oilfield = (String) session.getAttribute("oilfield");
        entity.setOilfield(oilfield);
        Equipment_WzdbEntity main = SbdbDao.Get_main(entity).get(0);//list
        dbid = main.getDbid();
        db_date = main.getDb_date();
        out_unit = main.getOut_unit();
        enter_unit = main.getEnter_unit();
        examine1 = main.getExamine1();
        examine2 = main.getExamine2();
        examine4 = main.getExamine4();
        /** 第一步，建立一個Workbook，對應一個Excel檔案  */
        HSSFWorkbook hssf = new HSSFWorkbook();
        /** 第二步，在Workbook中新增一個sheet,對應Excel檔案中的sheet  */
        HSSFSheet sheet = hssf.createSheet("調撥單匯出");
        /** 第三步，設定樣式以及字型樣式*/
        //標題樣式1
        HSSFCellStyle titleStyle1 = hssf.createCellStyle();
        titleStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 建立一個居中格式
        titleStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        HSSFFont headerFont1 = (HSSFFont) hssf.createFont(); // 建立字型樣式
        headerFont1.setFontName("黑體"); // 設定字型型別
        headerFont1.setFontHeightInPoints((short) 14); // 設定字型大小
        titleStyle1.setFont(headerFont1); // 為標題樣式設定字型樣式
        //標題樣式2
        HSSFCellStyle titleStyle2 = hssf.createCellStyle();
        titleStyle2.setAlignment(titleStyle2.ALIGN_CENTER);//水平居中
        titleStyle2.setVerticalAlignment(titleStyle2.VERTICAL_CENTER);//垂直對齊
        HSSFFont headerFont2 = (HSSFFont) hssf.createFont(); // 建立字型樣式
        headerFont2.setBold(true); //字型加粗
        headerFont2.setFontName("黑體"); // 設定字型型別
        headerFont2.setFontHeightInPoints((short) 17); // 設定字型大小
        titleStyle2.setFont(headerFont2); // 為標題樣式設定字型樣式
        //標題樣式3
        HSSFCellStyle titleStyle3 = hssf.createCellStyle();
        HSSFFont headerFont3 = (HSSFFont) hssf.createFont(); // 建立字型樣式
        headerFont3.setFontName("黑體"); // 設定字型型別
        headerFont3.setFontHeightInPoints((short) 11); // 設定字型大小
        titleStyle3.setFont(headerFont3); // 為標題樣式設定字型樣式
        // 表頭樣式
        HSSFCellStyle headerStyle = hssf.createCellStyle();
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 建立一個居中格式
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下邊框
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左邊框
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右邊框
        headerStyle.setWrapText(true);// 迴繞文字
        HSSFFont font1 = hssf.createFont();
        font1.setFontName("黑體");
        font1.setFontHeightInPoints((short) 11);
        font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗體顯示
        headerStyle.setFont(font1);
        //內容樣式
        HSSFCellStyle contentStyle = hssf.createCellStyle();
        contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 建立一個居中格式
        contentStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下邊框
        contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左邊框
        contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
        contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右邊框
        contentStyle.setWrapText(true);// 迴繞文字

        //底部樣式
        HSSFCellStyle footStyle = hssf.createCellStyle();
        footStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 建立一個居中格式
        footStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);// 垂直居中
        footStyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); // 下邊框
        footStyle.setBorderLeft(HSSFCellStyle.BORDER_NONE);// 左邊框
        footStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
        footStyle.setBorderRight(HSSFCellStyle.BORDER_NONE);// 右邊框
        footStyle.setWrapText(true);// 迴繞文字

        sheet.setColumnWidth(0, 1800);
        sheet.setColumnWidth(1, 4000);
        sheet.setColumnWidth(2, 4000);
        sheet.setColumnWidth(3, 3500);
        sheet.setColumnWidth(4, 3500);
        sheet.setColumnWidth(5, 3500);
        sheet.setColumnWidth(6, 3500);
        sheet.setColumnWidth(7, 3500);
        sheet.setColumnWidth(8, 3500);
        sheet.setColumnWidth(9, 3500);

        // 行號
        int rowNum = 0;
        int sb_number=0;
        //標題行1
        HSSFRow title1 = sheet.createRow(rowNum++);
        title1.setHeight((short) 500);// 設定行高
        HSSFCell c00 = title1.createCell(0);
        c00.setCellValue("中國石油冀東油田");
        c00.setCellStyle(titleStyle1);
        // 合併單元格，引數依次為起始行，結束行，起始列，結束列 （索引0開始）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));//標題合併單元格操作，6為總列數
        //標題行2
        HSSFRow title2 = sheet.createRow(rowNum++);
        title2.setHeight((short) 600);// 設定行高
        HSSFCell c01 = title2.createCell(0);
        c01.setCellValue("固定資產內部調撥單");
        c01.setCellStyle(titleStyle2);
        // 合併單元格，引數依次為起始行，結束行，起始列，結束列 （索引0開始）
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 9));//標題合併單元格操作，6為總列數
        //標題行3
        HSSFRow title3 = sheet.createRow(rowNum++);
        title3.setHeight((short) 300);// 設定行高
        String[] row_title = {"","","","","","","","","",""};
        for (int ii = 0; ii < row_title.length; ii++) {
            HSSFCell c02 = title3.createCell(ii);
            c02.setCellStyle(titleStyle3);
            if (ii == 0) {
                c02.setCellValue("調出單位:"+out_unit);
            } else if (ii == 3) {
                c02.setCellValue( "調字第("+dbid+")號");
            } else if (ii == 6) {
                c02.setCellValue(" 調入單位:"+enter_unit);
            } else if (ii == 9) {
                c02.setCellValue("調撥日期:"+db_date);
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 2));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 5));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 8));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 9, 9));
        //表頭行
        HSSFRow row1 = sheet.createRow(rowNum++);
        row1.setHeight((short) 500);
        String[] row_first = {"序號", "資產編碼", "資產名稱", "規格型號", " 單位 ", "數量","原值","淨值","調撥原因","備註"};
        for (int i = 0; i < row_first.length; i++) {
            HSSFCell tempCell = row1.createCell(i);
            tempCell.setCellStyle(headerStyle);
            tempCell.setCellValue(row_first[i]);
        }

        List<Equipment_WzdbEntity> list=SbdbDao.Get_detailed(entity);
        int i=1;
        for(Equipment_WzdbEntity obj : list){
            sb_number+=obj.getSb_number();
            HSSFRow rows = sheet.createRow(rowNum++);
            HSSFCell cell = rows.createCell(0);
            rows.setHeight((short) (15.625 * 25));
            setCell(hssf, sheet, rows, cell, contentStyle, i, i, 0, 0, i);
            setCell(hssf, sheet, rows, cell, contentStyle, i, i, 1, 1, obj.getAsset_number());
            setCell(hssf, sheet, rows, cell, contentStyle, i, i, 2, 2, obj.getSbmc());
            setCell(hssf, sheet, rows, cell, contentStyle, i, i, 3, 3, obj.getGgxh());
            setCell(hssf, sheet, rows, cell, contentStyle, i, i, 4, 4, obj.getUnit());
            setCell(hssf, sheet, rows, cell, contentStyle, i, i, 5, 5, obj.getSb_number());
            setCell(hssf, sheet, rows, cell, contentStyle, i, i, 6, 6, obj.getYz());
            setCell(hssf, sheet, rows, cell, contentStyle, i, i, 7, 7, obj.getJz());
            setCell(hssf, sheet, rows, cell, contentStyle, i, i, 8, 8, obj.getDbyy());
            setCell(hssf, sheet, rows, cell, contentStyle, i, i, 9, 9, obj.getRemark());
            i++;
        }
        //合計行
        HSSFRow sum = sheet.createRow(rowNum++);
        sum.setHeight((short) 500);
        String[] row_sum = {"","","","","","","","","",""};
        for (int ii = 0; ii < row_sum.length; ii++) {
            HSSFCell tempCell = sum.createCell(ii);
            tempCell.setCellStyle(contentStyle);
            if (ii == 1) {
                tempCell.setCellValue("合計:");
            } else if (ii == 5) {
                tempCell.setCellValue(sb_number);
            } else {
                tempCell.setCellValue(row_sum[ii]);
            }
        }
        // 尾行
        HSSFRow foot = sheet.createRow(rowNum++);
        foot.setHeight((short) 500);
        String[] row_foot = {"公司資產管理部門簽章:","","","調入單位簽章:","","調出單位簽章:","",""," 調出單位制單:",""};
        for (int ii = 0; ii < row_foot.length; ii++) {
            HSSFCell tempCell = foot.createCell(ii);
            tempCell.setCellStyle(footStyle);
            if (ii == 0) {
                tempCell.setCellValue(row_foot[ii] + isnull(examine1));
            } else if (ii == 3) {
                tempCell.setCellValue(row_foot[ii] + isnull(examine4));
            } else if (ii == 5) {
                tempCell.setCellValue(row_foot[ii] + isnull(examine2));
            } else if (ii == 8) {
                tempCell.setCellValue(row_foot[ii]);
            } else {
                tempCell.setCellValue(row_foot[ii]);
            }
        }
        int footRowNum=list.size()+5;
        sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 0, 2));
        sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 3, 4));
        sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 5, 7));
        sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 8, 9));
// 初始化封裝的excel實體
        workbook = new WorkbookEntity();
        workbook.setFileName("裝置調撥單");
        workbook.setWorkbook(hssf);
        OutputStream out=null;
        String excelName=null;
        try {
            out = response.getOutputStream();
            response.setContentType("application/msexcel;charset=utf-8");
            excelName = new String(workbook.getFileName().getBytes(),"iso8859-1");
            response.setHeader("Content-disposition", "attachment;filename="+excelName+".xls");
            workbook.getWorkbook().write(out);
            out.flush();
            out.close();
        } catch (IOException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        return "workbook";
    }

Vue + Element 實現匯入匯出Excel
2020-04-13
VueExcel
Vue框架下實現匯入匯出Excel、匯出PDF
2021-09-09
Vue框架Excel
Vue實現匯出excel表格
2018-03-28
VueExcel
前端實現Excel匯入和匯出功能
2019-02-16
前端Excel
c#設定匯出Excel的列寬
2018-11-23
C#Excel
Laravel6 配合 Maatwebsite\Excel 實現 Excel 匯出
2020-09-08
LaravelWebExcel
vue excel匯入匯出
2019-03-07
VueExcel
vue實現前端匯出excel表格
2018-08-01
Vue前端Excel
二進位制陣列實戰 - 純前端匯出Excel檔案
2019-01-06
陣列前端Excel
二進位制陣列實戰 – 純前端匯出Excel檔案
2019-01-06
陣列前端Excel
js匯出EXCELjs匯出EXCEL
2012-09-19
JSExcel
Laravel Maatwebsite-Excel 3.1 實現匯出匯入
2020-03-23
LaravelWebExcel
Vue+Element 實現excel的匯入匯出
2020-08-13
VueExcel
PHP 匯出 Excel
2019-08-06
PHPExcel
PHP匯出EXCEL
2022-03-21
PHPExcel
Vue匯出Excel
2018-05-03
VueExcel
Java匯出Excel
2018-07-06
JavaExcel
php 匯出excel
2014-09-18
PHPExcel
POI 匯出Excel
2015-05-05
Excel
java匯出Excel定義匯出模板
2020-04-05
JavaExcel
Excel模板匯出之動態匯出
2021-03-26
Excel
spring boot + easypoi快速實現excel匯入匯出
2019-01-19
Spring BootExcel
文字按列匯入excel
2017-02-16
Excel
Angular Excel 匯入與匯出
2019-04-17
AngularExcel
C# 實現NPOI的Excel匯出
2022-01-19
C#Excel
騰訊文件怎樣匯出excel表格騰訊文件如何匯出excel
2022-03-22
Excel
Java 通過Xml匯出Excel檔案，Java Excel 匯出工具類，Java匯出Excel工具類
2017-09-13
JavaXMLExcel
js匯出Excel表格
2018-12-29
JSExcel
vue匯出Excel表格
2020-12-28
VueExcel
Excel優雅匯出
2020-12-15
Excel
Springmvc匯出excel
2017-03-03
SpringMVCExcel
appfuse:Excel匯出
2015-11-21
APPExcel
DataGridView匯出Excel
2009-08-14
ViewExcel
spring boot + jdk1.8實現Excel匯入、匯出
2020-09-26
Spring BootJDKExcel
kxcel, 方便匯入和匯出 Excel
2019-09-05
Excel
vue + element + 匯入、匯出excel表格
2018-09-10
VueExcel
轉java操作excel匯入匯出
2016-04-12
JavaExcel
json陣列匯出到Excel
2018-01-30
JSON陣列Excel

Excel匯出實列

1、html呼叫

2、Controller

3、server處理

單行表頭

多行表頭

相關文章