Excel催化剂开源第15波-VSTO开发之DataTable数据导出至单元格区域

Excel催化剂

发布于 2021-08-19 15:00:35

2.4K0

发布于 2021-08-19 15:00:35

文章被收录于专栏：Excel催化剂

上篇提到如何从Excel界面上拿到用户的数据，另外反方向的怎样输出给用户数据，也是关键之处。

VSTO最大的优势是，这双向的过程中，全程有用户的交互操作。

而一般IT型的程序，都是脱离用户的操作，只能让用户准备好数据，离开Excel，或生成数据，要重新打开某文件才能获取到数据结果。

在VSTO中，用户随时在Excel最强UI前端中生产数据，同时也在获得数据结果后，无缝地利用Excel强大的数据处理能力进行数据的再次加工如Vlookup一些其他信息进来，做个图表展示等等。

VSTO程序在用户全程数据链条中，都无缝地参与了，只在用户无法解决的复杂数据转换时，才使用二次开发进行处理，处理完，立即可进行下游的其他操作，用户体验绝对是一流。

言归正传，如何DataTable数据导出至单元格区域

在传统的VBA方法中，有ADO的某个方法CopyToRange，直接将DataRecordset的数据输出至单元格区域。

许多VSTO用户还在抱怨着ADO.Net没有相应的方法而郁闷万方，看完此篇内容，会彻底改观，并且可以彻底放弃ADO这样的落后的技术，拥抱ADO.Net这样与时俱进的专业程序猿使用的技术。

DataTable当然可以很轻松地转换为二维数组，再用Range.Resize的方式将二维数组返回给单元格区域，这不失为一个方法，但此处再次强调，这不是最优的方法。

在VSTO中，提供了ListObject的数据对象，区别于传统VBA中能够使用的ListObject。它是在Microsoft.Office.Tools.Excel命名空间内，是VSTO的宿主项，较传统的ListObject，其命名空间为Microsoft.Office.Interop.Excel，新的ListObject得到很大的增强，其中最明显的是可以绑定数据，类似DataGridView一样。

具体代码实现先创建一个Microsoft.Office.Tools.Excel命名空间内下的ListObject

public static ListObject CreateNewVSTOListObject(string outputSheetName, bool isRetainExistingTable)
        {
            Excel.Worksheet sht;
            if (isRetainExistingTable)
            {
                sht = Common.ExcelApp.ActiveWorkbook.Worksheets.Add();
                try
                {
                    sht.Name = outputSheetName;
                }
                catch (Exception)
                {
                    outputSheetName = outputSheetName + DateTime.Now.ToString("yyyyMMddhhmmss");
                    sht.Name = outputSheetName;
                }
            }
            else
            {
                try
                {
                    sht = Common.ExcelApp.ActiveWorkbook.Worksheets[outputSheetName];
                    var displayAlertsStatus = Common.ExcelApp.DisplayAlerts;

                    if (displayAlertsStatus == true)
                    {
                        Common.ExcelApp.DisplayAlerts = false;
                        sht.Delete();
                        Common.ExcelApp.DisplayAlerts = true;
                    }
                }
                catch (Exception)
                {
                }
                sht = Common.ExcelApp.ActiveWorkbook.Worksheets.Add();
                sht.Name = outputSheetName;
            }

            Worksheet vstoSht = Globals.Factory.GetVstoObject(sht);
            ListObject listObject = vstoSht.Controls.AddListObject(sht.Range["A1"], outputSheetName);
            return listObject;
        }

再将DataTable绑定到ListObject的Datasource上，整个过程结束。

public static void OutputDataToListObject(ListObject listObject, DataTable outputTable, bool isTransDataType=false,string[] mapCols=null) { ////先提取listDstTable原有公式 DictionarydicListObjectCalColumns = GetCalColumnsOfListObject(listObject); //激活工作表 Excel.Worksheet actSht = (Excel.Worksheet)listObject.Parent; actSht.Activate();

        listObject.AutoSetDataBoundColumnHeaders = true;
        listObject.ShowHeaders = true;

        if (mapCols!=null)
        {
            listObject.MappedColumns = mapCols;
        }

        //转换列数据格式
        if (isTransDataType)
        {
            ////先绑定没有数据的dtEmpty，用于调整列的数字格式
            DataTable dtEmpty = outputTable.Clone();
            listObject.SetDataBinding(dtEmpty);
            TransDataTypeOfListObject(listObject, dtEmpty);
        }

        ////重新绑定数据，用有数据的DataTable
        listObject.SetDataBinding(outputTable);
        listObject.AutoSetDataBoundColumnHeaders = false;
        TransHeaderNameOfListObject(listObject);
        listObject.Disconnect();
        //追加之前用户加入的公式，有可能会报错的，用Try-Catch来处理
        AddCustomCalColumns(listObject, dicListObjectCalColumns);
        ////自动调整列宽
        AutoFitColumnWidth(listObject);

    }

大数据下的数据导出

对于数据量较大的数据导出需求时，用ListObject的数据绑定方式导出，有性能瓶颈，可考虑用EPPLUS的方式读写Excel文件。

 public static void OutputDataByEpplus(ListObject listObject, DataTable outputTable, bool isTransDataType)
        {

            var actSht = (Excel.Worksheet)listObject.Parent;
            Excel.Workbook actWkb = actSht.Parent;

            string shtName = actSht.Name;
            string tableName = listObject.Name;

            ////先提取listDstTable原有公式
            Dictionary<string, string> dicListObjectCalColumns = GetCalColumnsOfListObject(listObject);

            listObject.Delete();

            string filePath = actWkb.FullName;

            var listCustomStyleIncludeInfos = StyleSetting.GetCustomStyleIncludeInfos(actWkb);
            actWkb.Close(SaveChanges: true);

            var fi = new FileInfo(filePath);
            using (var p = new ExcelPackage(fi))
            {
                ExcelWorksheet sht = p.Workbook.Worksheets[shtName];
                sht.Cells[1, 1].LoadFromDataTable(outputTable, true, OfficeOpenXml.Table.TableStyles.Light19);
                sht.Tables[0].Name = tableName;
                p.Save();
            };
            Excel.Workbook workbook = Common.ExcelApp.Workbooks.Open(filePath);
            StyleSetting.SetCustomeStyleIncludInfos(actWkb, listCustomStyleIncludeInfos);

            Excel.Worksheet dstSht = workbook.Worksheets[shtName];
            dstSht.Activate();
            var vstoListObject = Globals.Factory.GetVstoObject(dstSht.ListObjects[tableName]);
            //转换格式
            if (isTransDataType)
            {
                TransDataTypeOfListObject(vstoListObject, outputTable);
            }
            //修改列名
            TransHeaderNameOfListObject(vstoListObject);
            //追加之前用户加入的公式，有可能会报错的，用Try-Catch来处理
            AddCustomCalColumns(vstoListObject, dicListObjectCalColumns);
            ////自动调整列宽
            AutoFitColumnWidth(vstoListObject);

        }