Beispiel #1
0
        public void Save_to_Excel(ObservableCollection <CalData> ReturnList)
        {
            try {
                List <CalData> OldList = ReturnList.ToList();
                List <CalData> NewList;

                NewList = Bubble_Sort(OldList);

                NsExcel.Application excapp = new Microsoft.Office.Interop.Excel.Application
                {
                    //if you want to make excel visible
                    Visible = false
                };

                //create a blank workbook
                var workbook = excapp.Workbooks.Add(NsExcel.XlWBATemplate.xlWBATWorksheet);



                //Not done yet. You have to work on a specific sheet - note the cast
                //You may not have any sheets at all. Then you have to add one with NsExcel.Worksheet.Add()
                var sheet = (NsExcel.Worksheet)workbook.Sheets[1]; //indexing starts from

                //do something usefull: you select now an individual cell
                //var range = sheet.get_Range("A1", "A1");
                //range.Value2 = "test"; //Value2 is not a typo
                int    counter = 1;
                string cellName;

                cellName = "A" + counter.ToString();

                var range1 = sheet.get_Range(cellName, cellName);
                range1.NumberFormat = "@";
                range1.Value2       = "Ge Serial No";



                cellName      = "B" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "Ge Cat NO";



                cellName      = "C" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "AMR Serial No";


                cellName      = "D" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "AMR Assembly";

                cellName      = "E" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "Error Code";



                cellName      = "F" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "Physical Defect";


                cellName      = "G" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "Description";



                cellName      = "H" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "Return to Manufacturer";



                cellName      = "I" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "Return to Productuion";

                cellName      = "J" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "Scrap";

                cellName      = "K" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "Further Analysis";

                cellName      = "L" + counter.ToString();
                range1        = sheet.get_Range(cellName, cellName);
                range1.Value2 = "Date Processed";



                //now the list

                counter = 2;
                foreach (var item in NewList)
                {
                    cellName = "A" + counter.ToString();
                    var range = sheet.get_Range(cellName, cellName);
                    range.NumberFormat = "@";
                    range.Value2       = item.ge_serial_no.Trim();



                    cellName     = "B" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.assembly_catalog_no.Trim();



                    cellName     = "C" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.amr_serial_no.Trim();


                    cellName     = "D" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.amr_assembly_no.Trim();

                    cellName     = "E" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.reject_reason_1.Trim();



                    cellName     = "F" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.Physical_defect.ToString();


                    cellName     = "G" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.Description.Trim();



                    cellName     = "H" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.Return_Manufacturer.ToString();



                    cellName     = "I" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.Return_Production.ToString();

                    cellName     = "J" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.Scrap.ToString();

                    cellName     = "K" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.Further_Analysis.ToString();

                    cellName     = "L" + counter.ToString();
                    range        = sheet.get_Range(cellName, cellName);
                    range.Value2 = item.Date_Documented.ToString("dddd, dd MMMM yyyy HH:mm:ss");



                    ++counter;
                }
                sheet.Columns.AutoFit();

                //you've probably got the point by now, so a detailed explanation about workbook.SaveAs and workbook.Close is not necessary
                //important: if you did not make excel visible terminating your application will terminate excel as well - I tested it
                //but if you did it - to be honest - I don't kno
                workbook.SaveAs(@"\\som-fs02\I210\Jbl_SSN_Return\Jabil_SSN_" + DateTime.Now.ToString("dddd, dd MMMM yyyy ") + NewList.First().ge_serial_no.Trim() + "-" + NewList.Last().ge_serial_no.Trim() + ".xlsx", Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value,
                                Missing.Value, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                                Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, true,
                                Missing.Value, Missing.Value, Missing.Value);


                workbook.Close();

                excapp.Quit();

                Marshal.ReleaseComObject(workbook);

                Marshal.ReleaseComObject(excapp);
            }

            catch (Exception ex)
            {
            }
        }