在c# WPF中插入二维数组到excel中

本文关键字：excel 二维数组 插入 WPF | 更新日期: 2023-09-27 18:08:48

我想将String[][] (2D数组)插入excel而不是"逐单元格"或按行。

的另一件事:我想打开一个模板XLS文件，写入其中并以不同的名称保存它。我已经在谷歌上搜索了2天了。

请帮帮我:-)

(c# WPF)

在c# WPF中插入二维数组到excel中

我最后做的是使用object[，]，并插入ro 

      xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open(ExcelFile);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        Excel.Range firstCell = xlWorkSheet.get_Range("A1", Type.Missing);
        Excel.Range lastCell = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);

        Excel.Range worksheetCells = xlWorkSheet.get_Range(firstCell, lastCell);
        int rowCount = worksheetCells.Rows.Count;
        int colCount = worksheetCells.Columns.Count;
         object[,] cellFormulas = new String[rowCount, colCount];
         cellFormulas = worksheetCells.Formula as object[,];
       //  String[][] ResultMatrix = new String[rowCount][];

        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Workbooks.Close();'

你想要的是:

虽然我认为它只适用于锯齿数组，而不是二维数组。未在2d数组上测试。

 var startCell = Worksheet.Cells[row, col];
 var endCell = Worksheet.Cells[row, col];
 var writeRange = (Excel.Range)Worksheet.Cells[startCell, endCell];
 writeRange.Value = myArray;

我可以解决第一个问题:

我想插入字符串[][](2D数组)excel而不是"单元格"或行。

首先你应该将String[][]转换为String[,]，然后使用这个(我测试过了):

        public static void ExportToExcel(String[,] data, String sheetName, String path)
        {
            var dt = new DataTable();
            for (var row = 0; row < data.GetLength(0); ++row)
            {
                for (var col = 0; col < data.GetLength(1); col++)
                {
                    dt.Rows[row][col] = data[row, col];
                }
            }
            Excel.Application oXL;
            Excel.Workbook oWB;
            Excel.Worksheet oSheet;
            //Excel.Range oRange;
            oXL = new Excel.Application();
            oXL.Visible = true;
            oXL.DisplayAlerts = false;
            oWB = oXL.Workbooks.Add(Missing.Value);
            oSheet = (Excel.Worksheet)oWB.ActiveSheet;
            oSheet.Name = sheetName;
            var rowCount = 1;
            foreach (DataRow dr in dt.Rows)
            {
                rowCount += 1;
                for (var i = 1; i < dt.Columns.Count + 1; i++)
                {
                    if (rowCount == 2)
                    {
                        oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                    }
                    oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                }
            }
            oSheet = null;
            oWB.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Excel.XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;
            oXL.Quit();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }

注意:用户应在参考资料中添加Microsoft.Office.Interop.Excel。

相关文章：