Beispiel #1
0
    static void ExcelOperations()
    {
        int spinnerInterval = 200;
        var spinner         = new Spinner(spinnerInterval);

        spinner.Start();
        Excel.Application excelApp   = new Excel.Application();
        Excel.Workbook    excelBook  = excelApp.Workbooks.Open(Environment.CurrentDirectory + @"\InputOutput_DailyStatus\" + InputExcelFileName);
        Excel.Workbook    excelBook2 = excelApp.Workbooks.Add(Type.Missing);

        try
        {
            Excel._Worksheet excelSheet         = excelBook.Sheets[1];
            Excel.Range      excelRange         = excelSheet.Columns[1];
            Excel.Range      sourceHeader       = excelSheet.Range["A1:I1"];
            Excel.Range      sourceBlankDataRow = excelSheet.Range["A10:I10"];
            var currentTime = DateTime.Now.ToShortTimeString();


            //For New Excel File
            Excel._Worksheet excelSheetNew = excelBook2.Sheets[1];
            excelSheetNew.Name = StatusDate.ToString("MM_dd_yyyy");
            Excel.Range destHeader       = excelSheetNew.Range["A1:I1"];
            Excel.Range destFirstDataRow = excelSheetNew.Range["A2:I2"];
            sourceHeader.Copy(destHeader);
            excelSheetNew.Range["A1:I1"].RowHeight = 15;
            for (int i = 0; i < dailyStatusModelList.Count; i++)
            {
                var         excelRangeRow      = i + 2;
                string      strDestRange       = string.Format("A{0}:I{1}", excelRangeRow, excelRangeRow);
                Excel.Range destDailyStatusRow = excelSheetNew.Range[strDestRange];
                var         dailyStatusModel   = dailyStatusModelList[i];
                sourceBlankDataRow.Copy(destDailyStatusRow);
                excelSheetNew.Cells[excelRangeRow, 1] = dailyStatusModel.Id;
                excelSheetNew.Cells[excelRangeRow, 2] = dailyStatusModel.Date;
                excelSheetNew.Cells[excelRangeRow, 3] = dailyStatusModel.Track;
                excelSheetNew.Cells[excelRangeRow, 4] = dailyStatusModel.Module;
                excelSheetNew.Cells[excelRangeRow, 5] = dailyStatusModel.Practitioner;
                excelSheetNew.Cells[excelRangeRow, 6] = dailyStatusModel.Activity;
                excelSheetNew.Cells[excelRangeRow, 7] = dailyStatusModel.ActivityDetails;
                excelSheetNew.Cells[excelRangeRow, 8] = dailyStatusModel.TFSID;
                excelSheetNew.Cells[excelRangeRow, 9] = dailyStatusModel.Comments;
            }
            excelSheetNew.Columns[1].ColumnWidth = 18;
            excelSheetNew.Columns[2].ColumnWidth = 18;
            excelSheetNew.Columns[3].ColumnWidth = 18;
            excelSheetNew.Columns[4].ColumnWidth = 30;
            excelSheetNew.Columns[5].ColumnWidth = 20;
            excelSheetNew.Columns[6].ColumnWidth = 20;
            excelSheetNew.Columns[7].ColumnWidth = 18;
            excelSheetNew.Columns[8].ColumnWidth = 18;
            excelSheetNew.Columns[9].ColumnWidth = 25;
            if (IsDev)
            {
                var currentDateTime = DateTime.Now.ToString("MMddyyyy-HHmmss");
                var newFileName     = "Dev_" + currentDateTime + ".xlsx";
                GeneratedExcelFileNamePath = ExcelFilePathProdForSave + newFileName;
                excelBook2.SaveAs2(GeneratedExcelFileNamePath);
            }
            else
            {
                var newFileName = "Status_SubhaDeb_" + StatusDate.ToString("MM_dd_yyyy") + ".xlsx";
                GeneratedExcelFileNamePath = ExcelFilePathProdForSave + newFileName;
                excelBook2.SaveAs2(GeneratedExcelFileNamePath);
            }
            Console.WriteLine();
            Console.WriteLine("Excel File Created");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
        finally
        {
            excelBook.Save();
            excelBook.Close();
            //excelBook2.Save();
            //excelBook2.Close();
            excelApp.Quit();
            spinner.Stop();
        }
    }