C# Windows 窗体 - 如何在不同的线程上运行导出以 excel，以及在哪里包含进度条的代码? - C# Windows Form - How do I run my export to excel on a different Thread AND where do I include code for a progress bar? 小贝子编程网

我有时会在数据网格视图中获得超过 100000+ 行，我希望用户能够更快地导出它们以 excel。目前，我的 Windows 表单处于"未响应"阶段，但它实际上是在后端执行导出。我想在不同的线程上执行导出，以便它更快，并且我想添加一个进度条来显示导出本身的进度。

我尝试了以下方法：

创建一个新任务 - 导出时间实际上变得更长
使用线程线程 = 新线程(导出(创建要运行的其他线程 - 当显示对话框行运行时，它会给出错误

我的代码如下：

private void BtnSearchExportCSV_Click(object sender, EventArgs e)
{
Export();
}
private void CopyAllToClipBoard()
{
dgvSearchFilter.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
dgvSearchFilter.MultiSelect = true;
dgvSearchFilter.RowHeadersVisible = false;
dgvSearchFilter.SelectAll();
DataObject dataObj = dgvSearchFilter.GetClipboardContent();
if (dataObj != null)
{
Invoke((Action)(() => { Clipboard.SetDataObject(dataObj); }));
//Clipboard.SetDataObject(dataObj);
}
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occurred while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
private void Export()
{
try
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Documents (*.xls)|*.xls";
sfd.FileName = "Export.xls";
if (sfd.ShowDialog() == DialogResult.OK)
{
// Copy DataGridView results to clipboard
CopyAllToClipBoard();
object misValue = System.Reflection.Missing.Value;
Excel.Application xlexcel = new Excel.Application();
// Without this you will get two confirm overwrite prompts
xlexcel.DisplayAlerts = false;
Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Paste clipboard results to worksheet range
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
// For some reason column A is always blank in the worksheet. ¯_(ツ)_/¯
// Delete blank column A and select cell A1
//Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
//delRng.Delete(Type.Missing);
//xlWorkSheet.get_Range("A1").Select();
// Save the excel file under the captured location from the SaveFileDialog
xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlexcel);
// Clear Clipboard and DataGridView selection
Clipboard.Clear();
dgvSearchFilter.ClearSelection();
// Open the newly saved excel file
if (File.Exists(sfd.FileName))
System.Diagnostics.Process.Start(sfd.FileName);
}
}
catch (Exception exception)
{
MessageBox.Show("The following exception occurred: " + exception.ToString());
}
}
}

我越来越熟悉 C#。但是，这是我第一次遇到这样的事情。

谢谢。

将操作移动到另一个线程不会使其更快，但它不会再阻止 UI。用户不会看到"无响应的应用程序"。

因为您的代码是由 UI 线程运行的Button.Click触发的。如果操作需要时间，则操作会阻止 UI。
不要将 UI 代码SaveFileDialog和操作逻辑混合在一起。
使用提前返回将提高代码的可读性。它减少了嵌套语句的大小。你可以谷歌搜索它。
发生System.Threading.ThreadStateException是因为您在线程中使用SaveFileDialog和ClipBoard。要解决此问题，您需要将这两个函数移出Thread调用函数。如果你真的想让它工作。跟随可能会让它工作。但是我不建议这种实现。
```
Thread op = new Thread( operation );
op.SetApartmentState( ApartmentState.STA );
op.Start();
```

下面的示例，包括放置进度窗口的位置：

private void Export()
{
// Do UI check first
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel Documents (*.xls)|*.xls";
sfd.FileName = "Export.xls";
// If failed , early return
if (sfd.ShowDialog() != DialogResult.OK)
{
return;
}
ProgressWindow prg = new ProgressWindow();
prg.Show();
// Do your copy and export code below, you may use task or thread if you don't want to let current form unresponsive.
operation();
// After finished, close your progress window
prg.Close();
}
void operation()
{
// Copy DataGridView results to clipboard
CopyAllToClipBoard();
object misValue = System.Reflection.Missing.Value;
Excel.Application xlexcel = new Excel.Application();
// Without this you will get two confirm overwrite prompts
xlexcel.DisplayAlerts = false;
Excel.Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// Paste clipboard results to worksheet range
Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
CR.Select();
xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
// For some reason column A is always blank in the worksheet. ¯_(ツ)_/¯
// Delete blank column A and select cell A1
//Excel.Range delRng = xlWorkSheet.get_Range("A:A").Cells;
//delRng.Delete(Type.Missing);
//xlWorkSheet.get_Range("A1").Select();
// Save the excel file under the captured location from the SaveFileDialog
xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlexcel.DisplayAlerts = true;
xlWorkBook.Close(true, misValue, misValue);
xlexcel.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlexcel);
// Clear Clipboard and DataGridView selection
Clipboard.Clear();
dgvSearchFilter.ClearSelection();
// Open the newly saved excel file
if (File.Exists(sfd.FileName))
System.Diagnostics.Process.Start(sfd.FileName);
}

根据您的描述，ProgressWindow可能是没有"关闭"按钮的Form。

所以我一直在四处走动，尝试不同的方法和代码，并实现我自己的代码。到目前为止，我遇到(和修改(的最成功和最快速的代码如下：

var headers = dgvSearchFilter.Columns.Cast<DataGridViewColumn>();
string delimiter = ",";
DataTable dt = new DataTable();
foreach (DataGridViewColumn col in dgvSearchFilter.Columns)
{
dt.Columns.Add(new DataColumn(col.Name, typeof(string)));
}
foreach (DataGridViewRow row in dgvSearchFilter.Rows)
{
DataRow dataRow = dt.NewRow();
foreach (DataGridViewCell cell in row.Cells)
{
if (row.Cells[cell.ColumnIndex].Value == null || row.Cells[cell.ColumnIndex].Value == DBNull.Value || String.IsNullOrWhiteSpace(row.Cells[cell.ColumnIndex].Value.ToString()))
{
dataRow[cell.ColumnIndex] = " ";
}
else
{
dataRow[cell.ColumnIndex] = cell.Value.ToString();
}
}
dt.Rows.Add(dataRow);
}
string unique = DateTime.Now.ToString("yyyyMMddHHmmssffff");
string fileName = "SQLQueryOutput_" + unique + ".csv";
using (StreamWriter swr = new StreamWriter(File.Open(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), fileName), FileMode.CreateNew), Encoding.Default, 1000000))
{
swr.WriteLine(string.Join(",", headers.Select(column => """ + column.HeaderText + """).ToArray()));
foreach (DataRow dr in dt.Rows)
{
var line = dr.ItemArray.Select(r => r.ToString().Contains(delimiter) || r.ToString().Contains("n") ? """ + r + """ : r);
swr.WriteLine(string.Join(delimiter, line));
}
}
MessageBox.Show("Your file was generated and its ready for use.");

它不是Excel格式，而是CSV。但是，您可以在其他线程上使用它。它会在您的桌面上生成带有name_uniqueValue的 CSV。

基本上，将数据网格视图的列转换为逗号分隔的值。然后将它们添加到数据表中。逐个遍历数据网格视图并向数据表添加值。然后，使用 StreamWriter 将这些值写入 CSV。几乎/不到一分钟即可完成 100 万行。

尝试一下所有想要将DataGridView转换为CSV的人。

C# Windows 窗体 - 如何在不同的线程上运行导出以 excel，以及在哪里包含进度条的代码?

相关内容

最新更新

热门标签：