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

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/JoneG/article/details/50522766

View

<DataGrid ItemsSource="{Binding KeringPickings}" IsReadOnly="True" Margin="0 5" x:Name="gridKeringPicking"/>

<Grid Margin="2" >
      <ProgressBar Maximum="{Binding KeringPickings.Count}" Value="{Binding CurrentPickingLine,Mode=TwoWay}"/>
      <TextBlock TextAlignment="Center">
                                        <TextBlock.Text>
                                            <MultiBinding StringFormat="{}{0} / {1}">
                                                <Binding Path="CurrentPickingLine"/>
                                                <Binding Path="KeringPickings.Count"/>
                                            </MultiBinding>
                                        </TextBlock.Text>
      </TextBlock>
</Grid>

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;
            }
        }
    }

展开阅读全文

datatable Image导出EXCEL

04-05

public void Bind(System.Data.DataTable dt)rn rn if (dt == null)rn rn return;rn rn elsern rn #region 操作excelrn Excel.Workbook xlWorkBook;rn Excel.Worksheet xlWorkSheet;rn xlWorkBook = new Excel.Application().Workbooks.Add(Type.Missing);rn xlWorkBook.Application.Visible = false;rn xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[1];rn #endregionrn //设置标题rn int rowIndex = 1; //行rn int columnIndex = 0; //列rn //将DataTable的列名导入Excel表第一行rn foreach (DataColumn dc in dt.Columns)rn rn columnIndex++;rn xlWorkSheet.Cells[rowIndex, columnIndex] = dc.ColumnName;rn //定义标头的宽度rn ((Excel.Range)xlWorkSheet.Cells[columnIndex, 11]).ColumnWidth = 10;rn ((Excel.Range)xlWorkSheet.Cells[columnIndex, 12]).ColumnWidth = 10;rn ((Excel.Range)xlWorkSheet.Cells[columnIndex, 15]).ColumnWidth = 10;rn ((Excel.Range)xlWorkSheet.Cells[columnIndex, 23]).ColumnWidth = 15;rn rn //将DataTable中的数据导入Excel中rn for (int i = 0; i < dt.Rows.Count; i++)rn rn rowIndex++;rn xlWorkSheet.Cells[rowIndex, 1] = dt.Rows[i]["uid"].ToString();rn xlWorkSheet.Cells[rowIndex, 2] = dt.Rows[i]["FRID"].ToString();rn xlWorkSheet.Cells[rowIndex, 3] = dt.Rows[i]["name"].ToString();rn xlWorkSheet.Cells[rowIndex, 4] = dt.Rows[i]["Age"].ToString();rn xlWorkSheet.Cells[rowIndex, 5] = dt.Rows[i]["Address"].ToString();rn xlWorkSheet.Cells[rowIndex, 6] = dt.Rows[i]["Aliases"].ToString();rn xlWorkSheet.Cells[rowIndex, 7] = dt.Rows[i]["CrimeName"].ToString();rn xlWorkSheet.Cells[rowIndex, 8] = dt.Rows[i]["Diploma"].ToString();rn xlWorkSheet.Cells[rowIndex, 9] = dt.Rows[i]["Nation"].ToString();rn xlWorkSheet.Cells[rowIndex, 10] = dt.Rows[i]["ImprisonTerm"].ToString();rn xlWorkSheet.Cells[rowIndex, 11] = Convert.ToDateTime(dt.Rows[i]["StartImprison"].ToString()).ToString("yyyy-MM-dd");rn xlWorkSheet.Cells[rowIndex, 12] = Convert.ToDateTime(dt.Rows[i]["StopImprison"].ToString()).ToString("yyyy-MM-dd");rn xlWorkSheet.Cells[rowIndex, 13] = dt.Rows[i]["PreMetier"].ToString();rn xlWorkSheet.Cells[rowIndex, 14] = dt.Rows[i]["Wedlock"].ToString();rn xlWorkSheet.Cells[rowIndex, 15] = Convert.ToDateTime(dt.Rows[i]["EnterPrisonTime"].ToString()).ToString("yyyy-MM-dd");rn xlWorkSheet.Cells[rowIndex, 16] = dt.Rows[i]["DelictTime"].ToString();rn xlWorkSheet.Cells[rowIndex, 17] = dt.Rows[i]["NativePlace"].ToString();rn xlWorkSheet.Cells[rowIndex, 18] = dt.Rows[i]["Religion"].ToString();rn xlWorkSheet.Cells[rowIndex, 19] = dt.Rows[i]["RewardAndPunish"].ToString();rn xlWorkSheet.Cells[rowIndex, 20] = dt.Rows[i]["Commute"].ToString();rn xlWorkSheet.Cells[rowIndex, 21] = dt.Rows[i]["BodyStatus"].ToString();rn xlWorkSheet.Cells[rowIndex, 22] = dt.Rows[i]["LinkMan"].ToString();rn xlWorkSheet.Cells[rowIndex, 23] = dt.Rows[i]["ContactTel"].ToString();rn //rn string filename = Server.MapPath("1.gif");rn if (dt.Rows[i]["Photo"] == DBNull.Value)rn rn xlWorkSheet.Cells[rowIndex, 24] = "暂无图片!";rn rn elsern rn byte[] filedata = (byte[])dt.Rows[i]["Photo"];rn System.IO.MemoryStream ms = new System.IO.MemoryStream(filedata);rn System.Drawing.Image img = System.Drawing.Image.FromStream(ms);rn img.Save(filename);rnrnrn //int rangeindex = 16 * i + 8;rn //string rangename = "D" + rangeindex;rn //Excel.Range range = xlWorkSheet.get_Range("A8", Type.Missing);rn //range.Select();rnrnrn Excel.Pictures pict = (Excel.Pictures)xlWorkSheet.Pictures(Type.Missing);rn pict.Insert(filename, Type.Missing);rn rn // xlWorkSheet.Cells[rowIndex, 24];rn rn xlWorkSheet.Cells[rowIndex, 25] = dt.Rows[i]["AdminID"].ToString();rn xlWorkSheet.Cells[rowIndex, 26] = dt.Rows[i]["AssessmentID"].ToString();rn xlWorkSheet.Cells[rowIndex, 27] = dt.Rows[i]["PersonnelID"].ToString();rn rn #region 保存excel文件rn string filePath = Server.MapPath("ReadExcel") + "" + System.DateTime.Now.ToString().Replace(":", "") + "导出.xls";rn xlWorkBook.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);rn xlWorkBook.Application.Quit();rn xlWorkSheet = null;rn xlWorkBook = null;rn GC.Collect();rn System.GC.WaitForPendingFinalizers();rn #endregionrn #region 导出到客户端rn Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");rn Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("导出", System.Text.Encoding.UTF8) + ".xls");rn Response.ContentType = "Application/excel";rn Response.WriteFile(filePath);rn Response.End();rn #endregionrn //结束进程rn EndExcel("EXCEL");rn rnrn rnrn这样导出是可以。但是图片我想吧固定大小,放到固定的位置。求方法。求代码。rn搞了1天了,都没弄出来。郁闷。 论坛

winform DataTable 导出excel

08-24

找到个源码是这样写的,但是有缺陷。。点击取消时会把所有excel进程关掉。。就是本来打开的excel文件就会关掉。有没有更好的写法???rnpublic void printAll(System.Data.DataTable dt)rn rnrn //导出到execl rn tryrn rn //没有数据的话就不往下执行 rn if (dt.Rows.Count == 0)rnrn //实例化一个Excel.Application对象 rn MessageBox.Show("当前没有数据!");rn rn Excel.Application excel = new Excel.Application();rnrn if (excel == null)rn rn MessageBox.Show("无法创建Excel对象,可能未安装Excel");rn return;rn rnrn //新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错 rn excel.Application.Workbooks.Add(true);rnrn //让后台执行设置为不可见,为true的话会看到打开一个Excel,然后数据在往里写 rn excel.Visible = false;rn //生成Excel中列头名称 rn for (int i = 0; i < dt.Columns.Count; i++)rn rn excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名 rn rnrn //把DataGridView当前页的数据保存在Excel中 rn if (dt.Rows.Count > 0)rn rn for (int i = 0; i < dt.Rows.Count; i++)//控制Excel中行,上下的距离,就是可以到Excel最下的行数,比数据长了报错,比数据短了会显示不完 rn rn for (int j = 0; j < dt.Columns.Count; j++)//控制Excel中列,左右的距离,就是可以到Excel最右的列数,比数据长了报错,比数据短了会显示不完 rn rn string str = dt.Rows[i][j].ToString();rn excel.Cells[i + 2, j + 1] = "'" + str;//i控制行,从Excel中第2行开始输出第一行数据,j控制列,从Excel中第1列输出第1列数据,"'" +是以string形式保存,所以遇到数字不会转成16进制 rn rn rn rn //设置禁止弹出保存和覆盖的询问提示框 rn excel.DisplayAlerts = false;rn excel.AlertBeforeOverwriting = false;rnrn //保存工作簿,值为false会报错 rn excel.Application.Workbooks.Add(true).Save();rn //保存excel文件 rn excel.Save("D:" + "\\KKHMD.xls");rnrn //确保Excel进程关闭 rn excel.Quit();rn excel = null;rnrn rn catch (Exception)rn rn KillProcess("EXCEL");//杀死进程EXCELrn //MessageBox.Show(ex.Message, "错误提示");rnrn rn 论坛

没有更多推荐了,返回首页