c# - 将 List 写入 Excel.Range

Question

我有以下 excel 范围:

workSheet.Range[workSheet.Cells[12, 2], workSheet.Cells[5000, 2]]

我已将此 excel 范围转换为列表，并对列表执行了一些数据操作。

object[,] cellValues = (object[,])inputRng.Value2;
List<string> lst = cellValues.Cast<object>().ToList().ConvertAll(x => Convert.ToString(x));

我想将列表分配回 Excel 范围。

Answer 1

这里有一些适合您的起始代码，可以执行您想要的操作，它可能会被改进。

您的主要“问题”是从多维数组 ( object[,] cellValues = (object[,])excelRange.Value; ) 到 List<string> 的转换- 如果你能以某种方式将其保留为多维数组，那么这将有助于你的“设置”代码 - 这取决于你的其他代码在做什么。

当您想“设置”您的更改时，我展示了两种方法:

• 遍历单元格(范围)并设置每个值

• 或者预先提供一个多维值数组，并获取一次使用它们的范围

这里:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using Microsoft.Office.Interop.Excel;

    namespace WindowsFormsApplication10
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                // Create starting spreadsheet

            Microsoft.Office.Interop.Excel.Application excelApp;
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook;
            Microsoft.Office.Interop.Excel.Worksheet excelWorksheet;

            excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.Visible = true;
            excelWorkbook = excelApp.Workbooks.Add();
            excelWorksheet = excelWorkbook.Sheets.Add();

            excelWorksheet.Activate();

            Microsoft.Office.Interop.Excel.Range excelRange = excelWorksheet.Range[excelWorksheet.Cells[12, 2], excelWorksheet.Cells[5000, 2]];

            // Turn off updating to make it faster

            excelApp.ScreenUpdating = false;

            // Set some initial data

            int i = 1;
            foreach (Microsoft.Office.Interop.Excel.Range cell in excelRange.Cells)
            {
                cell.Value = i;

                i++;
            }

            // Get the data from those cells as a list of strings

            object[,] cellValues = (object[,])excelRange.Value;
            List<string> lst = cellValues.Cast<object>().ToList().ConvertAll(x => Convert.ToString(x));

            // Modify the strings in some way

            for (int l = 0; l < lst.Count; l++)
            {
                lst[l] = lst[l] + "modified";
            }

            // Here are some different ways set the "cells" back

            // Set the cells back with the changes
            //------------------------------------

            // Option 1: using a multidimensional array

/* 
            object[,] cellValuesToWrite = new string[excelRange.Rows.Count, excelRange.Columns.Count];

            int z = 0;
            foreach (string str in lst)
            {
                cellValuesToWrite[z,0] = lst[z];
                z++;
            }

            excelRange.Value2 = cellValuesToWrite;
*/

            // Option 2: iterating the range of cells and "setting" the value

/*
            int z = 0;
            foreach (Microsoft.Office.Interop.Excel.Range cell in excelRange.Cells)
            {
                cell.Value = lst[z];
                z++;
            }

            excelRange.Value2 = lst;
*/

            // Turn updating back on

            excelApp.ScreenUpdating = true;
        }
    }
}