C# EXcel 打印

2019独角兽企业重金招聘Python工程师标准>>>

 Microsoft.Office.Interop.Excel.Application appexcel = new Microsoft.Office.Interop.Excel.Application();

            SaveFileDialog savefiledialog = new SaveFileDialog();

            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Workbook workbookdata;
            Microsoft.Office.Interop.Excel.Worksheet worksheetdata=null;




            //设置对象不可见

            appexcel.Visible = false;

            System.Globalization.CultureInfo currentci = System.Threading.Thread.CurrentThread.CurrentCulture;

            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-us");

            workbookdata = appexcel.Workbooks.Add(miss);
            Dictionary<string, string> dicData = new Dictionary<string, string>();
            Microsoft.Office.Interop.Excel.Range range1 = null;
            Microsoft.Office.Interop.Excel.Range rngfirst = null;
            Microsoft.Office.Interop.Excel.Range range2 = null;
            Microsoft.Office.Interop.Excel.Range range3 = null;
            Microsoft.Office.Interop.Excel.Range range4 = null;
            Microsoft.Office.Interop.Excel.Range range5 = null;
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                if (!dicData.ContainsKey(dataGridView1.Rows[i].Cells["type"].Value.ToString().Trim() + "," + dataGridView1.Rows[i].Cells["IClot_no"].Value.ToString().Trim()))
                {
                    dicData.Add(dataGridView1.Rows[i].Cells["type"].Value.ToString().Trim() + "," + dataGridView1.Rows[i].Cells["IClot_no"].Value.ToString().Trim(), null);
                }

            }
            int xiangshu = 0;///GF5118M  GF3118M箱数
            foreach (string key in dicData.Keys)
            {
                worksheetdata = (Microsoft.Office.Interop.Excel.Worksheet)workbookdata.Worksheets.Add(miss, miss, miss, miss);///新增sheet
                worksheetdata.Name = key;

                List<Maticsoft.Model.Pmc_WorderFT> PMCList = PMC.GetModelList(" type='" + key.Split(',')[0] + "' and  IncomeLot_no='" + key.Split(',')[1] + "'");

                worksheetdata.get_Range("A1:H1").Merge(worksheetdata.get_Range("A1:H1").MergeCells);//合并单元格
              
                 rngfirst = (Microsoft.Office.Interop.Excel.Range)worksheetdata.Cells[1, 1];
                rngfirst.Font.Size = 25;

                rngfirst.Font.Name = "Arial Unicode MS";//设置单元格字体

                rngfirst.RowHeight = 40;
                rngfirst.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                //rngfirst.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                rngfirst.Font.Bold = true;
                worksheetdata.Cells[1, 1] = "LY测试报告单";
                worksheetdata.Cells[2, 1] = "客户";
                worksheetdata.Cells[2, 2] = "产品型号";
                worksheetdata.Cells[2, 3] = "产品批次";
                worksheetdata.Cells[2, 4] = "程序名称";
                worksheetdata.Cells[2, 5] = "版本号";
                worksheetdata.Cells[2, 6] = "CheckSum";
                worksheetdata.Cells[2, 7] = "固件版本";
                worksheetdata.Cells[2, 8] = "上货日期";
                if (PMCList.Count > 0)
                {
                    worksheetdata.Cells[3, 1] = PMCList[0].cu_no.Trim();
                    worksheetdata.Cells[3, 2] = PMCList[0].type;
                    worksheetdata.Cells[3, 3] = PMCList[0].IncomeLot_no;
                    worksheetdata.Cells[3, 4] = PMCList[0].tver;
                    worksheetdata.Cells[3, 5] = PMCList[0].tver;
                    worksheetdata.Cells[3, 6] = PMCList[0].checksum;
                    worksheetdata.Cells[3, 7] = PMCList[0].gver;
                    worksheetdata.Cells[3, 8] = PMCList[0].wo_date;
                }

                worksheetdata.get_Range("A4:H4").Merge(worksheetdata.get_Range("A4:H4").MergeCells);
                worksheetdata.get_Range("E5:F5").Merge(worksheetdata.get_Range("E5:F5").MergeCells);
                worksheetdata.get_Range("G5:H5").Merge(worksheetdata.get_Range("G5:H5").MergeCells);
                worksheetdata.Cells[5, 1] = "箱号";
                worksheetdata.Cells[5, 2] = "序列号";
                worksheetdata.Cells[5, 3] = "Frame总条数";
                worksheetdata.Cells[5, 4] = "Total";
                worksheetdata.Cells[5, 5] = "PASS";
                worksheetdata.Cells[5, 7] = "Fail";
                int l = 0;
                for (int k = 0; k < dataGridView1.Rows.Count; k++)
                {

                    if (dataGridView1.Rows[k].Cells["type"].Value.ToString().Trim() + "," + dataGridView1.Rows[k].Cells["IClot_no"].Value.ToString().Trim() == key)
                    {
                        if (dataGridView1.Rows[k].Cells["type"].Value.ToString().Trim() == "GF5118M" || dataGridView1.Rows[k].Cells["type"].Value.ToString().Trim() == "GF3118M")
                        {
                            worksheetdata.Cells[6 + l, 1] = xiangshu + 1;
                            xiangshu++;
                        }else
                        {
                            worksheetdata.Cells[6 + l, 1] = l + 1;
                        }
                        
                        worksheetdata.Cells[6 + l, 2] = "B" + dataGridView1.Rows[k].Cells["Code"].Value.ToString().Trim().Split('B')[1];
                        worksheetdata.Cells[6 + l, 3] = (int.Parse(dataGridView1.Rows[k].Cells["OK"].Value.ToString().Trim()) + int.Parse(dataGridView1.Rows[k].Cells["NG"].Value.ToString().Trim())) / 90;
                        worksheetdata.Cells[6 + l, 4] = int.Parse(dataGridView1.Rows[k].Cells["OK"].Value.ToString().Trim()) + int.Parse(dataGridView1.Rows[k].Cells["NG"].Value.ToString().Trim());
                        worksheetdata.get_Range("E" +(6 + l) + ":F" + (6 + l) + "").Merge(worksheetdata.get_Range("E" + (6 + l) + ":F" + (6 + l) + "").MergeCells);///合并EF
          
                        worksheetdata.get_Range("G" + (6 + l)+ ":H" + (6 + l) + "").Merge(worksheetdata.get_Range("G" + (6 + l) + ":H" + (6 + l) + "").MergeCells);///合并GH
                        worksheetdata.Cells[6 + l, 5] = int.Parse(dataGridView1.Rows[k].Cells["OK"].Value.ToString().Trim());
                        worksheetdata.Cells[6 + l, 7] = int.Parse(dataGridView1.Rows[k].Cells["NG"].Value.ToString().Trim());
                        l++;
                       
                       
                    }
                }
                int shu = 0;
                shu = l +5;
                 range1 = worksheetdata.get_Range("A5", "H" + shu);
                range1.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range1.RowHeight = 22;///设置高度
                range1.ColumnWidth = 17;///设置宽度

                //range1.EntireColumn.AutoFit();
                range1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
              
                range2 = worksheetdata.get_Range("A2", "H5" );
                range2.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range2.RowHeight = 28;
                range2.ColumnWidth = 17;
                //range2.EntireColumn.AutoFit();
                range2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                range3 = worksheetdata.get_Range("A2", "A" + shu);
                range3.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range3.RowHeight = 28;
                range3.ColumnWidth = 8;
                //range3.EntireColumn.AutoFit();
                range3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                range4 = worksheetdata.get_Range("A2", "H" + 2);
                range4.Font.Bold = true;
                range5 = worksheetdata.get_Range("A5", "H" + 5);
                range5.Font.Bold = true;
                worksheetdata.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;///设置横向打印
            }

            appexcel.Visible = true;
            workbookdata.Close();
            appexcel.Quit();
           
            // 9.释放资源  
            System.Runtime.InteropServices.Marshal.ReleaseComObject(rngfirst);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(range3);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(range4);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(range5);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetdata);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookdata);

            System.Runtime.InteropServices.Marshal.ReleaseComObject(appexcel);

            // 10.调用GC的垃圾收集方法  
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
    }

转载于:https://my.oschina.net/u/2494395/blog/544858