如何将页面的在Excel中正确换行

magic_evan 2022-01-02 原文

在页面的<br />导致导出Excel中是会以多行的方式显示，达不到页面在一个单元格中进行换行，为此我们有以下两种方式：

1.CSS样式方式

<br style=\'mso-data-placement:same-cell;\'/>

2.JS方式，如果能够100%命中的话，采用这种方式最好。

<script type="text/javascript">
        function toPcmExcel() {
            var myExcel, myBook;
            try {
                myExcel = new ActiveXObject("Excel.Application");
            } catch (e) {
                try {
                    myExcel = new ActiveXObject("ET.Application");
 
                } catch (e) {
                    alert("您的电脑没有安装Microsoft Excel或WPS软件！");
                    return;
                }
            }
            var excelRep = "------";
            var sel = document.body.createTextRange();
            var obj = document.getElementById("div_Report");
            var html = obj.innerHTML;//html副本
            obj.innerHTML = obj.innerHTML.replace(/<br\s*\/?>/gi, (excelRep));
            sel.moveToElementText(document.all.div_Report);
            sel.select();
            document.execCommand(\'Copy\');
            document.execCommand(\'Unselect\');
            myExcel.Visible = true;
            myBook = myExcel.Workbooks.Add();
            var sheet = myBook.sheets(1);
            sheet.name = document.all.tb_hiddenReportTitle.innerText;
            sheet.columns.ColumnWidth = 20;
            sheet.columns(3).ColumnWidth = 25;
            sheet.columns(5).ColumnWidth = 25;
            sheet.paste();
            obj.innerHTML = html;//恢复副本
            var itemCount = 2;
            for (var i = 1; i < 1000; i++) {
            //替换命中值
                if (sheet.rows(i).cells(1).value == priceText) {
                    sheet.rows(i).cells(2).replace(excelRep, \'\n\');//将页面<br>换成Excel的换行符
                    itemCount--;
                }
                if (sheet.rows(i).cells(1).value == CartonNoText) {
                    sheet.rows(i).cells(2).replace(excelRep, \'\n\');
                    itemCount--;
                }
                if (itemCount == 0) break;
            }
        }
    </script>