精华内容
下载资源
问答
  • excel 合并单元格

    2012-05-11 17:30:00
    最近用devexpress做excel export,发现devexpress对cell merge的case支持不是很好,没办法,只要调用excel api自己去做merge了。devexpress...

    最近用devexpress做excel export,发现devexpress对cell merge的case支持不是很好,没办法,只要调用excel api自己去做merge了。devexpress的mege就是对某列按顺序对相同的值进行merge。写了一个方法,对导出的excel进行merge,如下:

     1 private void mergeCell2(string fileName, int rowNumber, List<int> columnIndex)
     2         {
     3             Microsoft.Office.Interop.Excel.Application _excelApp = new Microsoft.Office.Interop.Excel.Application();
     4 
     5             try
     6             {
     7                 _excelApp.DisplayAlerts = false;
     8                 _excelApp.ScreenUpdating = true;
     9 
    10 
    11                 Workbook workBook = _excelApp.Workbooks.Open(fileName,
    12                     XlUpdateLinks.xlUpdateLinksAlways, Type.Missing, Type.Missing, Type.Missing,
    13                     Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    14                     Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    15                     Type.Missing, Type.Missing);
    16 
    17                 Worksheet sheet1 = workBook.Worksheets[1as Worksheet;
    18 
    19                 for (int loopClmIndx = 0; loopClmIndx < columnIndex.Count; loopClmIndx++)
    20                 {
    21                     char columnName = Convert.ToChar(Convert.ToInt32('A') + columnIndex[loopClmIndx]);
    22 
    23                     Range c1 = sheet1.get_Range(string.Format("{0}{1}", columnName, 2),
    24                         string.Format("{0}{1}", columnName, rowNumber + 1));
    25 
    26                     object[,] vv = c1.Cells.Value2 as object[,];
    27                     if (vv != null)
    28                     {
    29                         int mergeStartIndx = -1;
    30 
    31                         for (int rowIndx = 1; rowIndx <= vv.Length;rowIndx++)
    32                         {
    33                             mergeStartIndx = rowIndx;
    34                             object tempValue = vv[rowIndx, 1];
    35                             string strTempValue = tempValue == null ? string.Empty : tempValue.ToString();
    36 
    37                             while (rowIndx < vv.Length)
    38                             {
    39                                 object tempNextValue = vv[rowIndx + 11];
    40                                 string strTempNextValue = tempNextValue == null ? string.Empty : tempNextValue.ToString();
    41 
    42                                 if (strTempNextValue.Equals(strTempValue))
    43                                 {
    44                                     rowIndx++;
    45                                 }
    46                                 else
    47                                 {
    48                                     break;
    49                                 }
    50                             }
    51 
    52                             if (mergeStartIndx != rowIndx)
    53                             {
    54                                 Range rg = sheet1.get_Range(string.Format("{0}{1}", columnName, mergeStartIndx+1),
    55                                      string.Format("{0}{1}", columnName, rowIndx+1));
    56 
    57                                 if (rg != null)
    58                                 {
    59                                     rg.Merge(false);
    60                                 }
    61 
    62                                 Marshal.ReleaseComObject(rg);
    63                             }
    64                         }                       
    65                     }
    66                 }
    67 
    68                 workBook.Save();
    69                 workBook.Close(true, Type.Missing, Type.Missing);
    70 
    71                 Marshal.ReleaseComObject(sheet1);
    72                 Marshal.ReleaseComObject(workBook);
    73             }
    74             finally
    75             {
    76                 if (_excelApp != null)
    77                 {
    78                     _excelApp.Quit();
    79                     _excelApp = null;
    80                     GC.SuppressFinalize(this);
    81                 }
    82             }
    83         }

     注意,如果excel的cell没有值,没有值的相邻的格子也必须合并。

    转载于:https://www.cnblogs.com/fgynew/archive/2012/05/11/2496428.html

    展开全文
  • npm install --save xlsx file-saver创建@/vendor/Export2Excel.js:require('script-loader!file-saver');require('script-loader!@/vendor/Blob'); //在vendor文件加下建立Blob.js,代码在最下方import XLSXfrom '...

    npm install --save xlsx file-saver

    创建@/vendor/Export2Excel.js:

    require('script-loader!file-saver');

    require('script-loader!@/vendor/Blob'); //在vendor文件加下建立Blob.js,代码在最下方

    import XLSXfrom 'xlsx'

    function datenum(v, date1904) {

    if (date1904) v +=1462;

    var epoch =Date.parse(v);

    return (epoch -new Date(Date.UTC(1899,11,30))) / (24 *60 *60 *1000);

    }

    function sheet_from_array_of_arrays(data, opts) {

    var ws = {};

    var range = {s: {c:10000000,r:10000000},e: {c:0,r:0}};

    for (var R =0;R != data.length; ++R) {

    for (var C =0;C != data[R].length; ++C) {

    if (range.s.r >R)range.s.r =R;

    if (range.s.c >C)range.s.c =C;

    if (range.e.r

    if (range.e.c

    var cell = {v: data[R][C]};

    if (cell.v ==null)continue;

    var cell_ref = XLSX.utils.encode_cell({c:C,r:R});

    if (typeof cell.v ==='number')cell.t ='n';

    else if (typeof cell.v ==='boolean')cell.t ='b';

    else if (cell.v instanceof Date) {

    cell.t ='n';

    cell.z = XLSX.SSF._table[14];

    cell.v =datenum(cell.v);

    }

    else cell.t ='s';

    ws[cell_ref] =cell;

    }

    }

    if (range.s.c <10000000)ws['!ref'] = XLSX.utils.encode_range(range);

    return ws;

    }

    function Workbook() {

    if (!(this instanceof Workbook))return new Workbook();

    this.SheetNames = [];

    this.Sheets = {};

    }

    function s2ab(s) {

    var buf =new ArrayBuffer(s.length);

    var view =new Uint8Array(buf);

    for (var i =0;i != s.length; ++i)view[i] = s.charCodeAt(i) &0xFF;

    return buf;

    }

    // 单列合并数据加工

    //在tableData数据中对colName列进行加工(header:['xx','yy',...],mergeRule:[2,1,1,3,2,1...])

    // 返回格式 newTableDatas = [[列头s], ["abc", "ss", "yyy", 0], [null, "xx", "y", 1], ["bca", "xxx", "yy", 1], [null, "xxx", "yy", 1]]

    // 返回格式 newMergeRules = [{"e":{"r":2,"c":0}, "s":{"r":1,"c":0}},{"e":{"r":4,"c":0}, "s":{"r":3,"c":0}}]

    function formatTableData(header,tableDatas,mergeRules,colIndex,colName){

    var newTableDatas = [header]// 第一行为列头,

    var newMergeRules= []//加工合并规则

    var currRowIndex =1 // 因为第一行为列头所以起始为1

    var mergeIndexs = [currRowIndex]// 因为第一行为列头所以起始为1,为创建newTableDatas使用

    for(var i=0;i

    var endRowIndex =currRowIndex + mergeRules[i] -1

    var merge = {"e":{"r":endRowIndex,"c": colIndex},"s":{"r":currRowIndex,"c":colIndex}}

    newMergeRules.push(merge)

    currRowIndex =currRowIndex + mergeRules[i]

    mergeIndexs.push(currRowIndex)

    }

    for(var i=0;i

    var currRow = tableDatas[i]

    if(mergeIndexs.indexOf(i+1) === -1){

    currRow[colName] =null //合并的数据 但不是第一行时,此列赋值null

    }

    var newTabRow = []

    for (var j in currRow) {

    newTabRow.push(currRow[j])

    }

    newTableDatas.push(newTabRow)

    }

    return [newTableDatas,newMergeRules]

    }

    @/vendor/Blob.js:

    export function export_data_to_excel(header,tableDatas,mergeRules,colIndex,colName,excelName) {

    var oo =formatTableData(header,tableDatas,mergeRules,colIndex,colName);

    var ranges =oo[1];

    /* original data */

    var data =oo[0];

    var ws_name ="SheetJS";

    var wb =new Workbook(),ws =sheet_from_array_of_arrays(data);

    /* add ranges to worksheet */

    // ws['!cols'] = ['apple', 'banan'];

    ws['!merges'] =ranges;

    /* add worksheet to workbook */

    wb.SheetNames.push(ws_name);

    wb.Sheets[ws_name] =ws;

    var wbout = XLSX.write(wb, {bookType:'xlsx',bookSST:false,type:'binary'});

    saveAs(new Blob([s2ab(wbout)], {type:"application/octet-stream"}), excelName+".xlsx")

    }

    (function (view) {

    "use strict";

    view.URL = view.URL || view.webkitURL;

    if (view.Blob && view.URL) {

    try {

    new Blob;

    return;

    }catch (e) {}

    }

    // Internally we use a BlobBuilder implementation to base Blob off of

    // in order to support older browsers that only have BlobBuilder

    var BlobBuilder = view.BlobBuilder || view.WebKitBlobBuilder || view.MozBlobBuilder || (function(view) {

    var

    get_class =function(object) {

    return Object.prototype.toString.call(object).match(/^\[object\s(.*)\]$/)[1];

    }

    ,FakeBlobBuilder =function BlobBuilder() {

    this.data = [];

    }

    ,FakeBlob =function Blob(data, type, encoding) {

    this.data = data;

    this.size = data.length;

    this.type = type;

    this.encoding = encoding;

    }

    ,FBB_proto =FakeBlobBuilder.prototype

    ,FB_proto =FakeBlob.prototype

    ,FileReaderSync = view.FileReaderSync

    ,FileException =function(type) {

    this.code =this[this.name = type];

    }

    ,file_ex_codes = (

    "NOT_FOUND_ERR SECURITY_ERR ABORT_ERR NOT_READABLE_ERR ENCODING_ERR "

    +"NO_MODIFICATION_ALLOWED_ERR INVALID_STATE_ERR SYNTAX_ERR"

    ).split(" ")

    ,file_ex_code =file_ex_codes.length

    ,real_URL = view.URL || view.webkitURL || view

    ,real_create_object_URL =real_URL.createObjectURL

    ,real_revoke_object_URL =real_URL.revokeObjectURL

    ,URL =real_URL

    ,btoa = view.btoa

    ,atob = view.atob

    ,ArrayBuffer = view.ArrayBuffer

    ,Uint8Array = view.Uint8Array

    ;

    FakeBlob.fake =FB_proto.fake =true;

    while (file_ex_code--) {

    FileException.prototype[file_ex_codes[file_ex_code]] =file_ex_code +1;

    }

    if (!real_URL.createObjectURL) {

    URL = view.URL = {};

    }

    URL.createObjectURL =function(blob) {

    var

    type = blob.type

    ,data_URI_header

    ;

    if (type ===null) {

    type ="application/octet-stream";

    }

    if (blobinstanceof FakeBlob) {

    data_URI_header ="data:" +type;

    if (blob.encoding ==="base64") {

    return data_URI_header +";base64," + blob.data;

    }else if (blob.encoding ==="URI") {

    return data_URI_header +"," +decodeURIComponent(blob.data);

    }if (btoa) {

    return data_URI_header +";base64," +btoa(blob.data);

    }else {

    return data_URI_header +"," +encodeURIComponent(blob.data);

    }

    }else if (real_create_object_URL) {

    return real_create_object_URL.call(real_URL, blob);

    }

    };

    URL.revokeObjectURL =function(object_URL) {

    if (object_URL.substring(0,5) !=="data:" &&real_revoke_object_URL) {

    real_revoke_object_URL.call(real_URL, object_URL);

    }

    };

    FBB_proto.append =function(data/*, endings*/) {

    var bb =this.data;

    // decode data to a binary string

    if (Uint8Array && (datainstanceof ArrayBuffer || datainstanceof Uint8Array)) {

    var

    str =""

    ,buf =new Uint8Array(data)

    ,i =0

    ,buf_len =buf.length

    ;

    for (;i

    str +=String.fromCharCode(buf[i]);

    }

    bb.push(str);

    }else if (get_class(data) ==="Blob" ||get_class(data) ==="File") {

    if (FileReaderSync) {

    var fr =new FileReaderSync;

    bb.push(fr.readAsBinaryString(data));

    }else {

    // async FileReader won't work as BlobBuilder is sync

    throw new FileException("NOT_READABLE_ERR");

    }

    }else if (datainstanceof FakeBlob) {

    if (data.encoding ==="base64" &&atob) {

    bb.push(atob(data.data));

    }else if (data.encoding ==="URI") {

    bb.push(decodeURIComponent(data.data));

    }else if (data.encoding ==="raw") {

    bb.push(data.data);

    }

    }else {

    if (typeof data !=="string") {

    data +="";// convert unsupported types to strings

    }

    // decode UTF-16 to binary string

    bb.push(unescape(encodeURIComponent(data)));

    }

    };

    FBB_proto.getBlob =function(type) {

    if (!arguments.length) {

    type =null;

    }

    return new FakeBlob(this.data.join(""), type,"raw");

    };

    FBB_proto.toString =function() {

    return "[object BlobBuilder]";

    };

    FB_proto.slice =function(start, end, type) {

    var args =arguments.length;

    if (args <3) {

    type =null;

    }

    return new FakeBlob(

    this.data.slice(start,args >1 ? end :this.data.length)

    , type

    ,this.encoding

    );

    };

    FB_proto.toString =function() {

    return "[object Blob]";

    };

    FB_proto.close =function() {

    this.size =this.data.length =0;

    };

    return FakeBlobBuilder;

    }(view));

    view.Blob =function Blob(blobParts, options) {

    var type = options ? (options.type ||"") :"";

    var builder =new BlobBuilder();

    if (blobParts) {

    for (var i =0,len = blobParts.length;i

    builder.append(blobParts[i]);

    }

    }

    return builder.getBlob(type);

    };

    }(typeof self !=="undefined" &&self ||typeof window !=="undefined" &&window ||this.content ||this));

    展开全文
  • 安装blob和export2excel两个文件放到vendor文件夹下

    安装blob和export2excel两个文件放到vendor文件夹下

    <template>
      <div class="wrapper">
        <button @click='downloadOut'>下载</button>
      </div>
    </template>
    
    <script>
    export default {
      components:{},
      props:{},
      data(){
        return {
          list:[{
    		"id": "1",
    		"cityId": "222401",
    		"createUser": "延边分公司传输测试账号",
    		"createTime": "2015-04-20 16:27:00",
    		"updateTime": "2019-01-09 18:25:56",
    		"cityName": "延吉市",
    		"nodes": [{
    			"targetName": "设备0",
    			"cards": [{
    				"targetName": "板卡1",
    				"ports": [{
    					"targetName": "端口1-1"
    				}, {
    					"targetName": "端口1-2"
    				}, {
    					"targetName": "端口1-3"
    				}]
    			}, {
    				"targetName": "板卡2",
    				"ports": [{
    					"targetName": "端口2-1"
    				}, {
    					"targetName": "端口2-2"
    				}, {
    					"targetName": "端口2-3"
    				}]
    			}]
        }]
    }],
    list2:[{
      'name':'zjm',
      'list':[{
        type:'数学',
        score:[{
        'fenshu':'34'
      }]},{
        type:'语文',
        score:[{
        'fenshu':'30'
      }]}]
    }]
        }
      },
      watch:{},
      computed:{},
      methods:{
        downloadOutline() {
                            require.ensure([], () => {
                                const { export_json_to_excel } = require('../vendor/Export2Excel')
                                const { export_get_title_time } = require('../vendor/Export2Excel')
                                const { format_json } = require('../vendor/Export2Excel')
                                const tHeader = ['姓名', '创建时间','设备','板卡','端口']
                                const filterVal = [ 'cityName',  'createTime','node','card','port']
                                const list = this.list
                                //1.拆分数据 存放在新建数据中 2.获取要合并的集合
                                var node_c = filterVal.indexOf("node")//node的列下标
                                var card_c = filterVal.indexOf("card")//card的列下标
                                var current_r = 1;//当前行从1开始(因为0是表头)
                                var current_s_r = 1//每条数据开始行
                                var node_s_r = 1;//node的开始行 从1开始
                                var card_s_r = 1;//card的开始行 从1开始
                                //要合并集合
                                var merges = [];
                                //     {
                                //     s: {//s为开始
                                //         c: 0,//开始列
                                //         r: 0//可以看成开始行,实际是取值范围
                                //     },
                                //     e: {//e结束
                                //         c: 0,//结束列
                                //         r: 0//结束行
                                //     }
                                // }
                                var nowList = [];//新建数据
                                for (let i = 0; i < list.length; i++) {
                                    const element = list[i];
                                    for (let j = 0; j < element.nodes.length; j++) {
                                        const node = element.nodes[j];
                                        for (let k = 0; k < node.cards.length; k++) {
                                            const card = node.cards[k];
                                            for (let l = 0; l < card.ports.length; l++) {
                                                const port = card.ports[l];
                                                current_r++//当前行 //最后是7
                                                //制造重复数据
                                                var obj = JSON.parse(JSON.stringify(element))
                                                obj.node = node.targetName
                                                obj.card = card.targetName
                                                obj.port = port.targetName
                                                nowList.push(obj)
                                            }
                                            if(card_s_r != (current_r-1)){//开始r != 结束行 则是合并
                                                var cardMerges = {
                                                    s: {c: card_c,r: card_s_r},
                                                    e: {c: card_c,r: current_r-1}
                                                };
                                                merges.push(cardMerges)
                                            }
                                            card_s_r = current_r
                                        }
                                        if(node_s_r != (current_r-1)){//开始r != 结束行 则是合并
                                            var nodeMerges = {
                                                s: {c: node_c,r: node_s_r},
                                                e: {c: node_c,r: current_r-1}
                                            };
                                            merges.push(nodeMerges)
                                        }
                                        node_s_r = current_r
                                    }
                                    //除node、card、port外其他列(下标1到10) 均合并 port总行数
                                    //['归属城市'0, '归属专业'1, '抑制时间'2, '抑制原因'3,'规则状态'4,'启用状态'5,'审核意见'6,'审核人'7,'审核时间'8,
                                    //'创建人'9,'创建时间'10,'node'11,'card'12,'port'13]
                                    for (let c = 0; c < 2 ; c++) {
                                        if(current_s_r != (current_r-1)){//开始r != 结束行 则是合并
                                            var nodeMerges = {
                                                s: {c: c,r: current_s_r},
                                                e: {c: c,r: current_r-1}
                                            };
                                            merges.push(nodeMerges)
                                        }
                                    }
                                    current_s_r = current_r
                                }
                                const data = format_json(filterVal, nowList)
                                const currentdate = export_get_title_time()
                                export_json_to_excel(tHeader, data, '监控平台-规则-抑制派单-规则列表_'+'('+currentdate+')',merges)
                            })
                },
                 downloadOut() {
    console.log(111)
                            require.ensure([], () => {
                                const { export_json_to_excel } = require('../vendor/Export2Excel')
                                const { export_get_title_time } = require('../vendor/Export2Excel')
                                const { format_json } = require('../vendor/Export2Excel')
                                const tHeader = ['姓名',  '学科', '分数']
                                const filterVal = [ 'name',  'type',  'score']
                                const list2 = this.list2
                                //1.拆分数据 存放在新建数据中 2.获取要合并的集合
                                var node_c = filterVal.indexOf("list")//node的列下标
                                var card_c = filterVal.indexOf("type")//card的列下标
                                var current_r = 1;//当前行从1开始(因为0是表头)
                                var current_s_r = 1//每条数据开始行
                                var node_s_r = 1;//node的开始行 从1开始
                                var card_s_r=1
                                //要合并集合
                                var merges = [];
                                var nowList = [];//新建数据
                                for (let i = 0; i < list2.length; i++) {
                                    const element = list2[i];
                                    for (let j = 0; j < element.list.length; j++) {
                                        const node = element.list[j];
                                        for(let k = 0; k < node.score.length; k++){
                                          const score =node.score[k];
                                               current_r++//当前行
                                                //制造重复数据
                                                var obj = JSON.parse(JSON.stringify(element))
                                                obj.type = node.type
                                                obj.score=score.fenshu
                                                nowList.push(obj)
                                            if(card_s_r != (current_r-1)){//开始r != 结束行 则是合并
                                                var cardMerges = {
                                                    s: {c: card_c,r: card_s_r},
                                                    e: {c: card_c,r: current_r-1}
                                                };
                                                merges.push(cardMerges)
                                            }
                                            card_s_r = current_r
                                        if(node_s_r != (current_r-1)){//开始r != 结束行 则是合并
                                            var nodeMerges = {
                                                s: {c: node_c,r: node_s_r},
                                                e: {c: node_c,r: current_r-1}
                                            };
                                            merges.push(nodeMerges)
                                        }
                                        node_s_r = current_r
                                        }
                                              
                                    }
                                    //除node、card、port外其他列(下标1到10) 均合并 port总行数
                                    //['归属城市'0, '归属专业'1, '抑制时间'2, '抑制原因'3,'规则状态'4,'启用状态'5,'审核意见'6,'审核人'7,'审核时间'8,
                                    //'创建人'9,'创建时间'10,'node'11,'card'12,'port'13]
                                    for (let c = 0; c < 1 ; c++) {
                                        if(current_s_r != (current_r-1)){//开始r != 结束行 则是合并
                                            var nodeMerges = {
                                                s: {c: c,r: current_s_r},
                                                e: {c: c,r: current_r-1}
                                            };
                                            merges.push(nodeMerges)
                                        }
                                    }
                                    current_s_r = current_r
                                }
                                console.log(nowList)
                                const data = format_json(filterVal, nowList)
                                const currentdate = export_get_title_time()
                                export_json_to_excel(tHeader, data, '监控平台-规则-抑制派单-规则列表_'+'('+currentdate+')',merges)
                            })
                }
      },
      created(){},
      mounted(){
        // this.downloadOutline()
      }
    }
    </script>
    
    
    
    展开全文
  • 1、jsp页面代码:<button type="button" class="btn btn-danger btn-optional oper-box-btn-export-common">导出到Excel </button>2、js中的请求代码:$(".oper-box-btn-export-common").click(function(){var flag=...

    1、jsp页面代码:

    <button type="button" class="btn btn-danger btn-optional oper-box-btn-export-common">导出到Excel
    </button>

    2、js中的请求代码:

    $(".oper-box-btn-export-common").click(function(){var flag=confirm("是否确定将查询的通用参数导出Excel表格?");
            if(flag == false){
                return;
            }
             location.href="${ctp}/para/excel/export/common";  
     });

    3、controller中的代码:

    @RequestMapping("/excel/export/common")
    public void exportExcelCommon(HttpServletRequestrequest, HttpServletResponse response )throws IOException{
    
            List<TParams> list = new ArrayList<>();
            List<TParams> params = paramService.getlist();
            for (TParams tParams : params) {
                list.add(tParams);
            }
            HSSFWorkbook wb = paramService.exportCommon(list);
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Content-Disposition", "attachment;filename="+ java.net.URLEncoder.encode("通用参数列表", "UTF-8")+".xls");  
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } 

    4、ServiceImpl代码:

    @SuppressWarnings("deprecation")
        @Override
        public HSSFWorkbook export(List<TAQIDataReport> list) {
    
            // 声明String数组,并初始化元素(表头名称)
            //第一行表头字段,合并单元格时字段跨几列就将该字段重复几次
            String[] excelHeader0 = { "城市名称", "监测点", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)",
                    "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)",
                    "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)", "污染物浓度及空气质量分指数(AQI)",
                    "污染物浓度及空气质量分指数(AQI)", "空气质量指数(AQI)", "首要污染物", "空气质量指数级别", "空气质量指数类别", "空气质量指数类别" };
               //  “0,2,0,0”  ===>  “起始行,截止行,起始列,截止列”
            String[] headnum0 = { "0,2,0,0", "0,2,1,1", "0,0,2,13", "0,2,14,14", "0,2,15,15", "0,2,16,16", "0,1,17,18" };
    
            //第二行表头字段,其中的空的双引号是为了补全表格边框
            String[] excelHeader1 = { "二氧化硫(SO₂)24小时平均", "二氧化硫(SO₂)24小时平均", "二氧化氮(NO₂)24小时平均", "二氧化氮(NO₂)24小时平均",
                    "颗粒物(粒径小于等于10μm)24小时平均", "颗粒物(粒径小于等于10μm)24小时平均", "一氧化碳(CO)24小时平均", "一氧化碳(CO)24小时平均", "臭氧(O₃)最大8小时平均",
                    "臭氧(O₃)最大8小时平均", "颗粒物(粒径小于等于2.5μm)24小时平均", "颗粒物(粒径小于等于2.5μm)24小时平均","","","","","" };
            // 合并单元格
            String[] headnum1 = { "1,1,2,3", "1,1,4,5", "1,1,6,7", "1,1,8,9", "1,1,10,11", "1,1,12,13" };
    
            //第三行表头字段
            String[] excelHeader2 = {  "", "", "浓度/(μg/m3)", "分指数", "浓度/(μg/m3)", "分指数", "浓度/(μg/m3)", "分指数", "浓度/(μg/m3)", "分指数",
                    "浓度/(μg/m3)", "分指数", "浓度/(μg/m3)", "分指数","", "类别", "颜色" };
    
            String[] headnum2 = { "2,2,2,2", "2,2,3,3", "2,2,4,4", "2,2,5,5", "2,2,6,6", "2,2,7,7", "2,2,8,8", "2,2,9,9",
                    "2,2,10,10", "2,2,11,11", "2,2,12,12", "2,2,13,13", "2,2,17,17", "2,2,18,18" };
    
            // 声明一个工作簿
            HSSFWorkbook wb = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = wb.createSheet("TAQIDataReport");
    
            // 生成一种样式
            HSSFCellStyle style = wb.createCellStyle();
            // 设置样式
            style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
            // 生成一种字体
            HSSFFont font = wb.createFont();
            // 设置字体
            font.setFontName("微软雅黑");
            // 设置字体大小
            font.setFontHeightInPoints((short) 12);
            // 字体加粗
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 在样式中引用这种字体
            style.setFont(font);
    
            // 生成并设置另一个样式
            HSSFCellStyle style2 = wb.createCellStyle();
            style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    
            // 生成另一种字体2
            HSSFFont font2 = wb.createFont();
            // 设置字体
            font2.setFontName("微软雅黑");
            // 设置字体大小
            font2.setFontHeightInPoints((short) 12);
            // 字体加粗
            // font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 在样式2中引用这种字体
            style2.setFont(font2);
    
            // 生成表格的第一行
            // 第一行表头
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < excelHeader0.length; i++) {
    
                sheet.autoSizeColumn(i, true);// 根据字段长度自动调整列的宽度
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(excelHeader0[i]);
                cell.setCellStyle(style);
    
                // System.out.println(excelHeader0[i]);
    
                if (i >= 0 && i <= 18) {
                    for (int j = 0; j < excelHeader0.length; j++) {
                        // 从第j列开始填充
                        cell = row.createCell(j);
                        // 填充excelHeader1[j]第j个元素
                        cell.setCellValue(excelHeader0[j]);
                        cell.setCellStyle(style);
                    }
    
                }
    
                // 设置列宽
    
                // sheet.setColumnWidth(0, 5500);
                // sheet.setColumnWidth(1, 6500);
                // sheet.setColumnWidth(2, 6500);
                // sheet.setColumnWidth(3, 6000);
                // sheet.setColumnWidth(4, 6500);
                // sheet.setColumnWidth(5, 6500);
                // sheet.setColumnWidth(6, 6500);
                // sheet.setColumnWidth(7, 6500);
                // sheet.setColumnWidth(8, 6500);
                // sheet.setColumnWidth(9, 6500);
                // sheet.setColumnWidth(10, 6500);
                // sheet.setColumnWidth(11, 6500);
                // sheet.setColumnWidth(12, 6500);
                // sheet.setColumnWidth(13, 6500);
                // sheet.setColumnWidth(14, 6500);
                // sheet.setColumnWidth(15, 6500);
                // sheet.setColumnWidth(16, 6500);
                // sheet.setColumnWidth(17, 6500);
                // sheet.setColumnWidth(18, 6500);
                // sheet.setDefaultRowHeight((short) 360);// 设置行高
    
            }
    
            // 动态合并单元格
            for (int i = 0; i < headnum0.length; i++) {
    
                sheet.autoSizeColumn(i, true);
                String[] temp = headnum0[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
            }
    
            // 第二行表头
            row = sheet.createRow(1);
            for (int i = 0; i < excelHeader1.length; i++) {
    
                sheet.autoSizeColumn(i, true);// 自动调整宽度
                HSSFCell cell = row.createCell(i + 1);
                cell.setCellValue(excelHeader1[i]);
                cell.setCellStyle(style);
    
                if (i >= 2 && i <= 18) {
                    for (int j = 0; j < excelHeader1.length; j++) {
                        // 从第j+1列开始填充
                        cell = row.createCell(j + 2);
                        // 填充excelHeader1[j]第j个元素
                        cell.setCellValue(excelHeader1[j]);
                        cell.setCellStyle(style);
                    }
                }
            }
    
            // 动态合并单元格
            for (int i = 0; i < headnum1.length; i++) {
    
                sheet.autoSizeColumn(i, true);
                String[] temp = headnum1[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
            }
            // 第三行表头
            row = sheet.createRow(2);
            for (int i = 0; i < excelHeader2.length; i++) {
    
                HSSFCell cell = row.createCell(i + 2);
                cell.setCellValue(excelHeader2[i]);
                cell.setCellStyle(style);
                // System.out.println(excelHeader2[i]);
                sheet.autoSizeColumn(i, true);// 自动调整宽度
    
                if (i > 1 && i <= 18) {
                    for (int j = 0; j < excelHeader2.length; j++) {
                        // 从第j+2列开始填充
                        cell = row.createCell(j);
                        // 填充excelHeader1[j]第j个元素
                        cell.setCellValue(excelHeader2[j]);
                        cell.setCellStyle(style);
                    }
                }
            }
            // 动态合并单元格
            for (int i = 0; i < headnum2.length; i++) {
    
                sheet.autoSizeColumn(i, true);
                String[] temp = headnum2[i].split(",");
                Integer startrow = Integer.parseInt(temp[0]);
                Integer overrow = Integer.parseInt(temp[1]);
                Integer startcol = Integer.parseInt(temp[2]);
                Integer overcol = Integer.parseInt(temp[3]);
                sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol));
            }
    
            // 第四行数据
            for (int i = 0; i < list.size(); i++) {
    
                row = sheet.createRow(i + 3);
                TAQIDataReport report = list.get(i);
    
                // 导入对应列的数据
                HSSFCell cell = row.createCell(0);
                cell.setCellValue(report.getCity());
                cell.setCellStyle(style2);
    
                HSSFCell cell1 = row.createCell(1);
                cell1.setCellValue(report.getAdd());
                cell1.setCellStyle(style2);
    
                HSSFCell cell2 = row.createCell(2);
                cell2.setCellValue(report.getSo2Concentration());
                cell2.setCellStyle(style2);
                HSSFCell cell3 = row.createCell(3);
                cell3.setCellValue(report.getSo2Subindex());
                cell3.setCellStyle(style2);
    
                HSSFCell cell4 = row.createCell(4);
                cell4.setCellValue(report.getNo2Concentration());
                cell4.setCellStyle(style2);
                HSSFCell cell5 = row.createCell(5);
                cell5.setCellValue(report.getNo2Subindex());
                cell5.setCellStyle(style2);
    
                HSSFCell cell6 = row.createCell(6);
                cell6.setCellValue(report.getPm10Concentration());
                cell6.setCellStyle(style2);
                HSSFCell cell7 = row.createCell(7);
                cell7.setCellValue(report.getPm10Subindex());
                cell7.setCellStyle(style2);
    
                HSSFCell cell8 = row.createCell(8);
                cell8.setCellValue(report.getCoConcentration());
                cell8.setCellStyle(style2);
                HSSFCell cell9 = row.createCell(9);
                cell9.setCellValue(report.getCoSubindex());
                cell9.setCellStyle(style2);
    
                HSSFCell cell10 = row.createCell(10);
                cell10.setCellValue(report.getO3Concentration());
                cell10.setCellStyle(style2);
                HSSFCell cell11 = row.createCell(11);
                cell11.setCellValue(report.getO3Subindex());
                cell11.setCellStyle(style2);
    
                HSSFCell cell12 = row.createCell(12);
                cell12.setCellValue(report.getPm25Concentration());
                cell12.setCellStyle(style2);
                HSSFCell cell13 = row.createCell(13);
                cell13.setCellValue(report.getPm25Subindex());
                cell13.setCellStyle(style2);
    
                HSSFCell cell14 = row.createCell(14);
                cell14.setCellValue(report.getAirSubindex());
                cell14.setCellStyle(style2);
    
                HSSFCell cell15 = row.createCell(15);
                cell15.setCellValue(report.getKeyPollution());
                cell15.setCellStyle(style2);
    
                HSSFCell cell16 = row.createCell(16);
                cell16.setCellValue(report.getLevel());
                cell16.setCellStyle(style2);
    
                HSSFCell cell17 = row.createCell(17);
                cell17.setCellValue(report.getType());
                cell17.setCellStyle(style2);
    
                HSSFCell cell18 = row.createCell(18);
                cell18.setCellValue(report.getColor());
                cell18.setCellStyle(style2);
            }
            return wb;
        }
    

    5、效果图展示:

    效果图

    展开全文
  • vue 使用Export2Excel实现表格导出功能

    千次阅读 2019-07-11 16:02:06
    vue 使用Export2Excel上传和导出excel表格 https://www.jianshu.com/p/257d6d23f85d 前端导出excel☞js-xlsx实现合并单元格 https://www.jianshu.com/p/7bfe4dcdb43b 社区版本的js-xlsx提供支持 ...
  • 1.在src下的libs文件夹下新建一个excel文件夹,里面放入下载好的两个文件(Blob.js和Export2Excel2.js) 2.在所需要导出的vue文件里引入: import { toExportExcel } from “…/libs/excel/Export2Excel2.js”; 3....
  • jQuery table2excel 导出Excel

    千次阅读 2017-07-03 11:10:23
    导出Excel的功能非常常见,...只需要引入jQuery的table2excel.js ,即可非常方便的导出excel。 下载地址:jQuery Plugin to export HTML tabled to Excel Spreadsheet Compatible Files 1.引入 &lt;scri...
  • java-Excel导出中的坑

    2019-10-05 15:59:20
    Excel导出过程中,若遇到合并单元格样式只有第一行合并,而下面要合并的行没有边框显示。 一般问题出在将单元格样式设置与合并单元格放在同一个循环中导致。 以下为一个完整版的demo以供参考 定义边框样式方法...
  • 利用PHPExcel导出Excel并设置Excel格式

    千次阅读 2017-06-17 11:29:23
    2合并指定的单元格 3、设置Excel数据源,并将数据源保护起来(这个是为了实现单元格下拉选项功能) 4、设置字体样式 public function export(){  //此处全是一维数组  $resumeState = C('RESUME_STATE'); ...
  • 2合并指定的单元格 3、设置Excel数据源,并将数据源保护起来(这个是为了实现单元格下拉选项功能) 4、设置字体样式 public function export(){ //此处全是一维数组 $resumeState = C('RESUME_STATE'); $...
  • 2合并指定的单元格 3、设置Excel数据源,并将数据源保护起来(这个是为了实现单元格下拉选项功能) 4、设置字体样式 public function export(){ //此处全是一维数组 $resumeState = C('RESUME_STATE'); ...
  • public void exportData(HttpServletResponse response) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = null; // 创建一行 HSSFRow row = null; HSSFCell ...
  • // 合并单元格 for (int t = 0; t ; t++) { SXSSFRow row = (SXSSFRow) sheet.createRow(t + startRow); List<CellModel> cellNameList = cellListMap.get(String.valueOf(t)); for (CellModel ...
  • VBA常用技巧

    2014-12-21 16:39:28
    15-2 合并单元格时连接每个单元格的文本 12 15-3 合并内容相同的连续单元格 12 15-4 取消合并单元格时在每个单元格中保留内容 12 技巧16 高亮显示单元格区域 12 技巧17 双击被保护单元格时不显示提示消息框 12 技巧...
  • VBA编程技巧大全

    2013-08-05 09:03:19
    15-2 合并单元格时连接每个单元格的文本 53 15-3 合并内容相同的连续单元格 54 15-4 取消合并单元格时在每个单元格中保留内容 56 技巧16 高亮显示单元格区域 57 技巧17 双击被保护单元格时不显示提示消息框 58 技巧...
  • 一个ViewGrid控件

    2008-11-05 15:56:00
    控件使用 1、鼠标经过行的时候改变该行的样式,鼠标离开行的时候恢复该行的样式 使用方法(设置属性): MouseOverCssClass - 鼠标经过行时行的 CSS 类名...MergeCells - 需要合并单元格的列的索引(用逗号“,”分隔)
  • gridview扩展

    2008-01-23 15:24:46
    控件使用 1、鼠标经过行的时候改变该行的样式,鼠标...{3}-总页数) <br>11、合并指定列的相邻且内容相同的单元格 使用方法(设置属性): MergeCells - 需要合并单元格的列的索引(用逗号“,”分隔)
  • tableMerge.js 单元格合并 可单独使用 源码版位置:ext 压缩版位置:docs/ext 2.定义入口模块soulTable // 自定义模块,这里只需要开放soulTable即可 layui.config({ base: 'ext/', // 模块目录 }).extend({ ...

空空如也

空空如也

1 2
收藏数 21
精华内容 8
关键字:

export2excel合并单元格