gpt4 book ai didi

c# - 用多线程写入excel文件

转载 作者:行者123 更新时间:2023-11-30 22:00:41 30 4
gpt4 key购买 nike

我正在尝试将数据表写入具有大量记录的 excel。我正在尝试使用分而治之的策略来实现,其中每个线程都被分配写入各自的 excelworkbook 工作表。但我得到的文件是只读的,单击“确定”覆盖文件。

class Program
{
int processorCount = 2;
static volatile bool processing = true;
DataTable employeeTable = new DataTable("Employee");
ManualResetEvent mre = new ManualResetEvent(false);

AutoResetEvent ar = new AutoResetEvent(true);
int record_count;
static void Main(string[] args)
{
Program p = new Program();

//Create an Emplyee DataTable

p.employeeTable.Columns.Add("Employee ID");
p.employeeTable.Columns.Add("Employee Name");
for (int i = 0; i <= 2; i++)
{
p.employeeTable.Rows.Add(i.ToString(), "ABC");
}
p.record_count = p.employeeTable.Rows.Count / p.processorCount;


Excel.Application excelApp = new Excel.Application();

//Create an Excel workbook instance and open it from the predefined location
Excel.Workbook excelWorkBook1 = excelApp.Workbooks.Open(@"F:\Org.xlsx");

Thread[] threads = new Thread[3];
for (int i = 0; i < 3; i++)
{

// p.ExportDataSetToExcel(i);
ParameterizedThreadStart ps = new ParameterizedThreadStart(p.ExportDataSetToExcel);
threads[i] = new Thread(ps);
threads[i].Start(new Custom() { sheetNo = i, excelWorkBook = excelWorkBook1 });
}

for (int j = 0; j < 3; j++)
{
threads[j].Join();
}

Console.WriteLine("Succeess");

Console.ReadKey();



}

private void ExportDataSetToExcel(object sheet1)
{

lock (this)
{
bool found = false;
Excel.Worksheet excelWorkSheet;

int sheetNo = ((Custom)sheet1).sheetNo;
Excel.Workbook excelWorkBook = ((Custom)sheet1).excelWorkBook;
excelWorkSheet = (excelWorkBook).Sheets["Sheet" + ((int)sheetNo + 1).ToString()];

for (int i = 1; i < employeeTable.Columns.Count + 1; i++)
{
excelWorkSheet.Cells[1, i] = employeeTable.Columns[i - 1].ColumnName;
}

int baseIndex = (int)sheetNo * record_count;
for (int j = baseIndex; j < baseIndex + record_count; j++)
{
for (int k = 0; k < employeeTable.Columns.Count; k++)
{
excelWorkSheet.Cells[j + 2, k + 1] = employeeTable.Rows[j].ItemArray[k].ToString();
}
}

Console.WriteLine(sheetNo.ToString());
Console.WriteLine("\n");

(excelWorkBook).Save();
(excelWorkBook).Close();
}
}



}**strong text**
public class Custom
{
public int sheetNo;
public Excel.Workbook excelWorkBook;
}

最佳答案

遗憾的是,Excel 并未设计为多线程。但我建议的是,您编写的内容会更有效。逐个单元地写入是减速的最大部分。

消除这两个因素(组织数据和写入数据)将减少实际写入时间,使其可能消除并发写入的需要。

我有一个旧的 VSTO 项目,我必须从数据库中写入数据集,然后将数据提取到二维数组中,然后将整个数组写入工作表上的一个区域,如下所示:

Microsoft.Office.Tools.Excel.Worksheet TheSheet;


private void PublishToSheet( int totalRows, int maxColumns, ref string[,] OutputArray )
{
Excel.Range Range = TheSheet.Range["A1", TheSheet.Cells[totalRows, maxColumns]];
Range.NumberFormat = "@";
Range.Value2 = OutputArray;

LastRow = totalRows;
LastColumn = maxColumns;

}

关于c# - 用多线程写入excel文件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28344273/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com