c#导出Excel 使用EXCEL进程

时间:2022-01-31 23:39:50

private void exportExcel(string filename, string path,string title, List<ArchivedWcsTask> wcstask, List<ArchivedMovement> movement)
        {
             Excel.Application excel1 = new Excel.Application();
            try
            {               
                excel1.DisplayAlerts = false;
                Excel.Workbook workbook1 = excel1.Workbooks.Add(Type.Missing);
                excel1.Visible = false;
                Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Worksheets["sheet1"];  //表头

Excel.Range r, th, td;
                r = worksheet1.get_Range(worksheet1.Cells[1, 1], worksheet1.Cells[2, 6]);     //取得合并的区域  
                r.Font.Size = 15;
                r.MergeCells = true;
                worksheet1.Cells[1, 1] = title;

th = worksheet1.get_Range(worksheet1.Cells[4, 1], worksheet1.Cells[4, 6]);
                th.Font.Bold = true;

worksheet1.Cells[4, 1] = "任务号";  //Excel里从第1行,第1列计算            
                worksheet1.Cells[4, 2] = "条码号";
                worksheet1.Cells[4, 3] = "起点";
                worksheet1.Cells[4, 4] = "终点";
                worksheet1.Cells[4, 5] = "创建时间";
                worksheet1.Cells[4, 6] = "任务";

td = worksheet1.get_Range(worksheet1.Cells[3, 1], worksheet1.Cells[wcstask.Count, 6]);
                td.ColumnWidth = 15;
                int i = 5;
                int j = 1;
                foreach (var item in wcstask)
                {
                    worksheet1.Cells[i, j++] = "T" + item.TaskCode;
                    worksheet1.Cells[i, j++] = item.ContainerCodes;
                    worksheet1.Cells[i, j++] = item.StartLocation;
                    worksheet1.Cells[i, j++] = item.EndLocation;
                    worksheet1.Cells[i, j++] = item.CreatedAt.ToString();
                    worksheet1.Cells[i, j] = movement.Single(x => x.Id == item.MovementId).Tag;
                    i++;
                    j = 1;
                }

string fileName = DateTime.Parse(wcstask.FirstOrDefault().CompletedAt.ToString()).ToString("yyyyMMddHHmmss") + ".xls";
                string filePath = "c:\\WMS_Export";// Server.MapPath("~/" + fileName);

if (!Directory.Exists(filePath))
                {
                    System.IO.Directory.CreateDirectory(filePath);
                }
                filePath =filePath+"\\"+ fileName;
                workbook1.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                closeExcel(excel1);
                GC.Collect();//垃圾回收
            }catch(Exception e)
            {
                closeExcel(excel1);
                GC.Collect();//垃圾回收
            }
        }

[DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
        public void closeExcel(Excel.Application excel)
        {
            try
            {
                if (excel != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(excel.Hwnd), out lpdwProcessId);

System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                    excel = null;
                   
                }
            }
            catch
            {
               
            }
           
        }

private void exportTxt(string filename, string path, List<ArchivedWcsTask> wcstask, List<ArchivedMovement> movement)
        {
            string line = "";
            string lable = "任务号\t条码号\t起点\t终点\t创建时间\t任务" + "\n";
            FileStream wfs = System.IO.File.Create("c:\\1.txt");
            StreamWriter sw = new StreamWriter(wfs);
            sw.Write(lable, System.Text.Encoding.ASCII);
            foreach (var item in wcstask)
            {
                line = item.TaskCode + "\t" + item.ContainerCodes + "\t" + item.StartLocation
                    + "\t" + item.EndLocation + "\t" + item.CreatedAt + "\t" + movement.Single(x => x.Id == item.MovementId).Tag + "\n";
                sw.Write(line, System.Text.Encoding.ASCII);
            }
            sw.Close();
            wfs.Close();
        }
       
    }