DEV 18.2 导出excel 数据格式异常

最新推荐文章于 2022-12-08 15:35:22 发布

z_shun

最新推荐文章于 2022-12-08 15:35:22 发布

阅读量610

点赞数 1

分类专栏： REMARK

本文链接：https://blog.csdn.net/z_shun/article/details/108831176

版权

REMARK 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

DEV 18.2 导出excel 数据格式异常

异常记录：使用DEV框架的项目升级成18.2之后，gridview设置了列DisplayFormat.FormatString = 0.## 的列，导出excel出现数据显示异常的问题：
处理：导出数据前将列的DisplayFormat.FormatString格式去掉，导完后再恢复数据格式（暂时没有找到别的解决方法，临时处理如下）：

/// <summary>
        /// 自定义列导出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bbtnCustomizeExport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (!GetExportPermission())
            {
                return;
            }
            if (xtraTabControl1.SelectedTabPage == xtraTabPage1)
            {
                //往来对账表
                if (gdReport.DataSource != null)
                {
                    reportDataSource.AsyncView = gvReport;
                    reportDataSource.Proxy = proxy;
                    IList<AsyncGridControl.ProxyParameter> parameters = new List<AsyncGridControl.ProxyParameter>();
                    reportDataSource.Parameters = parameters;
                    reportDataSource.InitAllData(this);
                    List<ReportCustomerReconciliationDeliveryViewModel> list = gvReport.DataSource as List<ReportCustomerReconciliationDeliveryViewModel>;

                    System.Windows.Forms.SaveFileDialog saveFileDialog = new SaveFileDialog();
                    saveFileDialog.Filter = "导出Excel (*.xlsx)|*.xlsx";
                    saveFileDialog.FilterIndex = 0;
                    saveFileDialog.RestoreDirectory = true;
                    saveFileDialog.FileName = GetReportExportFileName(AppConst.Module_CustomerReconciliation);
                    saveFileDialog.Title = "导出文件";

                    if (saveFileDialog.ShowDialog() == DialogResult.OK)
                    {   
                        ReportCustomerReconciliationDeliveryViewModel prevReceive = new ReportCustomerReconciliationDeliveryViewModel();
                        prevReceive.Abstract = "******：";
                        if (txtPrevReceive.EditValue != null && !string.IsNullOrEmpty(txtPrevReceive.EditValue.ToString()))
                        {
                            prevReceive.Abstract += decimal.Parse(txtPrevReceive.EditValue.ToString()).ToString("0.##");
                        }
                        ReportCustomerReconciliationDeliveryViewModel emptyRow = new ReportCustomerReconciliationDeliveryViewModel();
                        list.Add(emptyRow);
                        list.Add(prevReceive);
                        
                        //处理导出列格式
                        List<GridColumn> cols = new List<GridColumn>();
                        foreach (GridColumn item in gvReport.Columns)
                        {
                            GridColumn col = new GridColumn();
                            col.FieldName = item.FieldName;
                            col.DisplayFormat.FormatString = item.DisplayFormat.FormatString;
                            col.SummaryItem.DisplayFormat = item.SummaryItem.DisplayFormat;
                            cols.Add(col);
                            item.DisplayFormat.FormatString = null;
                            item.SummaryItem.DisplayFormat = null;
                        }
                        DevExpress.XtraPrinting.XlsxExportOptionsEx opEx = new DevExpress.XtraPrinting.XlsxExportOptionsEx();
                        opEx.ExportType = DevExpress.Export.ExportType.WYSIWYG;
                        gvReport.ExportToXlsx(saveFileDialog.FileName, opEx);
                        
                        //恢复列格式
                        foreach (GridColumn item in gvReport.Columns)
                        {
                            item.DisplayFormat.FormatString = cols.FirstOrDefault(r => r.FieldName == item.FieldName) == null ? null : cols.FirstOrDefault(r => r.FieldName == item.FieldName).DisplayFormat.FormatString;
                            item.SummaryItem.DisplayFormat = cols.FirstOrDefault(r => r.FieldName == item.FieldName) == null ? null : cols.FirstOrDefault(r => r.FieldName == item.FieldName).SummaryItem.DisplayFormat;
                        }
                        list.Remove(emptyRow);
                        list.Remove(prevReceive);
                    }
                }
            }
        }

处理导出格式后小数点异常得到解决：
方法提取

#region 自定义导出显示
        private void ExportToXlsx_DisplayFormat(string fileName,DevExpress.XtraPrinting.XlsxExportOptionsEx optionsEx, UserControls.AsyncReportGridControl.AsyncReportGridView gridView)
        {
            //处理导出列格式
            List<GridColumn> cols = new List<GridColumn>();
            foreach (GridColumn item in gridView.Columns)
            {
                GridColumn col = new GridColumn();
                col.FieldName = item.FieldName;
                col.DisplayFormat.FormatString = item.DisplayFormat.FormatString;
                col.SummaryItem.DisplayFormat = item.SummaryItem.DisplayFormat;
                cols.Add(col);
                item.DisplayFormat.FormatString = null;
                item.SummaryItem.DisplayFormat = null;
            }
            gridView.ExportToXlsx(fileName, optionsEx);
            //恢复列格式
            foreach (GridColumn item in gridView.Columns)
            {
                item.DisplayFormat.FormatString = cols.Any(r => r.FieldName == item.FieldName) ? cols.FirstOrDefault(r => r.FieldName == item.FieldName).DisplayFormat.FormatString : null;
                item.SummaryItem.DisplayFormat = cols.Any(r => r.FieldName == item.FieldName) ? cols.FirstOrDefault(r => r.FieldName == item.FieldName).SummaryItem.DisplayFormat : null;
            }
        }

        #endregion