wpf 异步导出 DataGrid 数据到 excel（包括转换 List 到 DataTable）

来源：互联网发布：mac切换全角编辑：程序博客网时间：2024/05/09 14:23

View：

ViewModel：

private Thread expThread;
private string fileName;
private DataTable expDataTable;
private MainWindow mainView = System.Windows.Application.Current.MainWindow as MainWindow;

public ObservableCollection<KeringPicking> KeringPickings { get; set; }
public int CurrentPickingLine { get; set; }

public void ExportKeringPicking()
{
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel 文件|*.xlsx";
saveDialog.FileName = "KeringPicking";
if (saveDialog.ShowDialog() == false) return;
fileName = saveDialog.FileName;

expDataTable = ConvertToDataTable(KeringPickings);
isPicking = true;
expThread = new Thread(ExportToExcel);
expThread.Start();
}

private DataTable ConvertToDataTable<T>(IEnumerable<T> modelList)
{
if (modelList == null || !modelList.Any()) return null;

var first = modelList.FirstOrDefault();
DataTable result = new DataTable(typeof(T).Name);
// add columns
foreach (PropertyInfo pi in first.GetType().GetProperties())
{
Type ptype = pi.PropertyType;
if (ptype.FullName.Contains("Decimal"))
{
ptype = typeof(decimal);
}
else if (ptype.FullName.Contains("DateTime"))
{
ptype = typeof(DateTime);
}
else if (ptype.FullName.Contains("bool"))
{
ptype = typeof(bool);
}
result.Columns.Add(new System.Data.DataColumn(pi.Name, ptype));
}
// add rows
foreach (var model in modelList)
{
DataRow dr = result.NewRow();
foreach (PropertyInfo pi in model.GetType().GetProperties())
{
var value = pi.GetValue(model, null);
if (value != null)
{
dr[pi.Name] = value;
}
}
result.Rows.Add(dr);
}
return result;
}

private void ExportToExcel()
{
var xlApp = new Microsoft.Office.Interop.Excel.Application();
object missing = System.Reflection.Missing.Value;
if (xlApp == null)
{
MessageBox.Show("Can not create Excel Object，maybe your PC does not install Excel.");
return;
}
var workbooks = xlApp.Workbooks;
var workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
var worksheet = (Excel.Worksheet)workbook.Worksheets[1];//get sheet1
Excel.Range range;

// write columns
for (int i = 0; i < expDataTable.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = expDataTable.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
// write datas
for (int r = 0; r < expDataTable.Rows.Count; r++)
{
mainView.Dispatcher.Invoke(DispatcherPriority.Normal,
new Action<int>(UpdateCurrentLine), r + 1);
for (int i = 0; i < expDataTable.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = expDataTable.Rows[r][i];
}
}
worksheet.SaveAs(fileName, missing, missing, missing, missing, missing, missing, missing, missing);

//只有这2行代码的话，在完成导出后进程中会有 excel 进程
//workbook.Close(missing, missing, missing);
//xlApp.Quit();

// 补充 kill excel 进程代码
workbook.Close(missing, missing, missing);
workbook = null;
xlApp.Quit();
GC.Collect();
KillMyExcelProcess.Kill(xlApp);

MessageBox.Show("Export Completed.", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
}

private void UpdateCurrentLine(int rownum)
{
CurrentPickingLine = rownum;
RaisePropertyChanged("CurrentPickingLine");
}

下面这个删除进程中 excel 的方法是从网上拷贝来的。因为上面的方法在导出 excel 后会在进程中有个 excel 的进程，而且这个进程不会在导出完成后消失。
public class KillMyExcelProcess
{
[System.Runtime.InteropServices.DllImport("User32.dll", CharSet = System.Runtime.InteropServices.CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
{
try
{
IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口

int k = 0;
GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用

p.Kill(); //关闭进程k
}
catch (Exception ex)
{
throw ex;
}
}
}

0 0