Пример #1
1
 private void ExportarDataGridViewExcel(DataGridView grd)
 {
     SaveFileDialog fichero = new SaveFileDialog();
     fichero.Filter = "Excel (*.xls)|*.xls";
     if (fichero.ShowDialog() == DialogResult.OK)
     {
         Microsoft.Office.Interop.Excel.Application aplicacion;
         Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
         Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
         aplicacion = new Microsoft.Office.Interop.Excel.Application();
         libros_trabajo = aplicacion.Workbooks.Add();
         hoja_trabajo =
             (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
         //Recorremos el DataGridView rellenando la hoja de trabajo
         for (int i = 0; i < grd.Rows.Count ; i++)
         {
             for (int j = 0; j < grd.Columns.Count; j++)
             {
                 hoja_trabajo.Cells[i + 1, j + 1] = grd.Rows[i].Cells[j].Value.ToString();
             }
         }
         libros_trabajo.SaveAs(fichero.FileName,
             Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
         libros_trabajo.Close(true);
         aplicacion.Quit();
     }
 }
Пример #2
0
 private void btn_Excel_Click(object sender, EventArgs e)
 {
     if (dgv_Info.Rows.Count == 0)//判断是否有数据
         return;//返回
     Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
     excel.Application.Workbooks.Add(true);//在Excel中添加一个工作簿
     excel.Visible = true;//设置Excel显示
     //生成字段名称
     for (int i = 0; i < dgv_Info.ColumnCount; i++)
     {
         excel.Cells[1, i + 1] = dgv_Info.Columns[i].HeaderText;//将数据表格控件中的列表头填充到Excel中
     }
     //填充数据
     for (int i = 0; i < dgv_Info.RowCount - 1; i++)//遍历数据表格控件的所有行
     {
         for (int j = 0; j < dgv_Info.ColumnCount; j++)//遍历数据表格控件的所有列
         {
             if (dgv_Info[j, i].ValueType == typeof(string))//判断遍历到的数据是否是字符串类型
             {
                 excel.Cells[i + 2, j + 1] = "'" + dgv_Info[j, i].Value.ToString();//填充Excel表格
             }
             else
             {
                 excel.Cells[i + 2, j + 1] = dgv_Info[j, i].Value.ToString();//填充Excel表格
             }
         }
     }
 }
Пример #3
0
        /*
         * The method getTable opens a file, gets the first worksheet in the workbook, and fills the datatable Table with its contents.
         */
        public void setup()
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            openFile.Filter = "Excel Workbook|*.xls";

            if (openFile.ShowDialog() == true)
            {
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);

                wb = app.Workbooks.Open(openFile.FileName);
                Microsoft.Office.Interop.Excel.Worksheet ws = wb.Sheets.get_Item(1);

                if (wb != null) // If there is a worksheet
                {
                    if (ws.UsedRange != null) // if the worksheet is not empty
                    {
                        Table = toDataTable(ws);
                    }
                }

                app.Visible = true;
            }
        }
        void CreateExcelFile()
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            excel.Workbooks.Add();
            excel.Visible = true;
            excel.DisplayAlerts = false;

            excel.Range["A1"].Value = bankDataTable.Columns[0].ColumnName;
            excel.Range["B1"].Value = bankDataTable.Columns[1].ColumnName;
            excel.Range["C1"].Value = bankDataTable.Columns[2].ColumnName;
            excel.Range["D1"].Value = bankDataTable.Columns[3].ColumnName;
            excel.Range["E1"].Value = bankDataTable.Columns[4].ColumnName;
            excel.Range["F1"].Value = bankDataTable.Columns[5].ColumnName;
            // set date and time format for column F
            excel.Range["F1", "F99"].NumberFormat = "M/D/YYYY H:MM AM/PM";
            // set width for column F
            excel.Range["F1"].EntireColumn.ColumnWidth = 17;

            // shamelessly stolen from Terri
            int j = 2;
            foreach (DataRow row in bankDataTable.Rows)
            {
                int i = 65;
                foreach( var item in row.ItemArray)
                {
                    char c1 = (char)i++;
                    string cell = c1 + j.ToString();
                    excel.Range[cell].Value = item.ToString();
                }
                j++;
            }
        }
Пример #5
0
 private void btn_Gather_Click(object sender, EventArgs e)
 {
     object missing = System.Reflection.Missing.Value;//定义object缺省值
     string[] P_str_Names = txt_MultiExcel.Text.Split(',');//存储所有选择的Excel文件名
     string P_str_Name = "";//存储遍历到的Excel文件名
     List<string> P_list_SheetNames = new List<string>();//实例化泛型集合对象,用来存储工作表名称
     Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
     //打开指定的Excel文件
     Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
     Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
     for (int i = 0; i < P_str_Names.Length - 1; i++)//遍历所有选择的Excel文件名
     {
         P_str_Name = P_str_Names[i];//记录遍历到的Excel文件名
         //指定要复制的工作簿
         Microsoft.Office.Interop.Excel.Workbook Tempworkbook = excel.Application.Workbooks.Open(P_str_Name, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
         P_list_SheetNames = GetSheetName(P_str_Name);//获取Excel文件中的所有工作表名
         for (int j = 0; j < P_list_SheetNames.Count; j++)//遍历所有工作表
         {
             //指定要复制的工作表
             Microsoft.Office.Interop.Excel.Worksheet TempWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)Tempworkbook.Sheets[P_list_SheetNames[j]];//创建新工作表
             TempWorksheet.Copy(missing, newWorksheet);//将工作表内容复制到目标工作表中
         }
         Tempworkbook.Close(false, missing, missing);//关闭临时工作簿
     }
     workbook.Save();//保存目标工作簿
     workbook.Close(false, missing, missing);//关闭目标工作簿
     MessageBox.Show("已经将所有选择的Excel工作表汇总到了一个Excel工作表中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
     CloseProcess("EXCEL");//关闭所有Excel进程
 }
Пример #6
0
        private void importButton_Click(object sender, RoutedEventArgs e)
        {
            var app = new Microsoft.Office.Interop.Excel.Application();
            var workbook = app.Workbooks.Add();
            var worksheet = workbook.Worksheets[1];
            worksheet.Cells[1, 1] = "Ім'я";
            worksheet.Cells[1, 2] = "Дата і час";
            worksheet.Cells[1, 3] = "Кількість помилок";
            worksheet.Cells[1, 4] = "Витрачений час";
            worksheet.Cells[1, 5] = "Результат";

            using (var context = new Model.DB())
            {
                var results = context.ConfusedLinesTestResults.ToList();
                for (int i = 0; i < results.Count(); i++)
                {
                    worksheet.Cells[i + 2, 1] = results[i].Name;
                    worksheet.Cells[i + 2, 2] = results[i].Date;
                    worksheet.Cells[i + 2, 3] = results[i].ErrorsCount;
                    worksheet.Cells[i + 2, 4] = results[i].Time;
                    worksheet.Cells[i + 2, 5] = results[i].Result;
                }
            }

            app.Visible = true;
        }
Пример #7
0
 public static void Init()
 {
     lock (Lock)
     {
         _excelApp = new Microsoft.Office.Interop.Excel.Application();
     }
 }
Пример #8
0
        public static void print(DataGridView dataGridView1,string tableName)
        {
            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();//Создание объекта Excel
            ExcelApp.Application.Workbooks.Add(Type.Missing);
            ExcelApp.Columns.ColumnWidth = 15;
            ExcelApp.Cells[1, 1] = tableName;//Передаём имя таблицы
            ExcelApp.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
            for (int i = 1; i < dataGridView1.Columns.Count; i++)//Заполняем названия столбцов
            {
                ExcelApp.Cells[2, i] = dataGridView1.Columns[i].HeaderText;
                ExcelApp.Cells[2, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
            }

            for (int i = 1; i < dataGridView1.ColumnCount; i++)//Заполняем таблицу
            {
                for (int j = 0; j < dataGridView1.RowCount; j++)
                {
                    try
                    {
                        ExcelApp.Cells[j + 3, i] = (dataGridView1[i, j].Value).ToString();
                        ExcelApp.Cells[j + 3, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                    }
                    catch { }
                }
            }
            ExcelApp.Visible = true;//Открываем Excel
        }
        public void exportarDatosExcel()
        {
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            app.Visible = true;
            worksheet = workbook.Sheets[1];
            worksheet = workbook.ActiveSheet;
            worksheet.Name = "Receive Payment";

            //Se le manda a la cabezera los datos de las tabla
            worksheet.Cells[1, 1] = "Date";
            worksheet.Cells[1, 2] = "Num-Referent";
            worksheet.Cells[1, 3] = "Receive From";
            worksheet.Cells[1, 4] = "Amount";

            for (int i = 0; i < tblReceive.Items.Count; i++)
            {

                ReceivePayment rpaymentcheck = (ReceivePayment)tblReceive.Items.GetItemAt(i);
                worksheet.Cells[i + 3, 1] = rpaymentcheck.Date;
                worksheet.Cells[i + 3, 2] = rpaymentcheck.RefNumber;
                worksheet.Cells[i + 3, 3] = rpaymentcheck.Customer;
                worksheet.Cells[i + 3, 4] = rpaymentcheck.Amount;

            }
        }
Пример #10
0
        static void DisplayInExcel(IEnumerable<dynamic> entities)
        {
            var excelApp = new Microsoft.Office.Interop.Excel.Application();

            excelApp.Visible = true;

            excelApp.Workbooks.Add();

            dynamic workSheet = excelApp.ActiveSheet;

            workSheet.Cells[1, "A"] = "Header A";
            workSheet.Cells[1, "B"] = "Header B";

            var row = 1;

            foreach (var item in entities)
            {
                row++;
                workSheet.Cells[row, "A"] = item.ColumnA;
                workSheet.Cells[row, "B"] = item.ColumnB;
            }

            workSheet.Columns[1].AutoFit();
            workSheet.Columns[2].AutoFit();
        }
Пример #11
0
        public static bool DataTableExportAsExcel(DataTable dt, string FileFullPath)
        {
            if (!System.IO.Directory.Exists(Path.GetDirectoryName(FileFullPath)))
            {
                CreatePath(FileFullPath);
            }
            
            Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
            excelApplication.EnableEvents = false;
            excelApplication.Application.DisplayAlerts = false;
            excelApplication.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Worksheet myWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelApplication.ActiveSheet;
            excelApplication.Visible = false;
            int nRowIndex    = 0;
            int nColumnIndex = 0;
            int ColumnCount = dt.Columns.Count;
            int RowCount = dt.Rows.Count;
            object[,] strArr = new object[RowCount + 1, ColumnCount];

            foreach (DataColumn col in dt.Columns)
            {
                strArr[nRowIndex, nColumnIndex] = col.ColumnName;
                ++nColumnIndex;
            }
            ++nRowIndex;
            nColumnIndex = 0;
         

            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < ColumnCount; i++)
                {
                    strArr[nRowIndex, nColumnIndex] = row[i].ToString();
                    ++nColumnIndex;
                }
                ++nRowIndex;
                nColumnIndex = 0;
            }
            string strExcelMaxColumnIndex = GetExcelMaxColumnIndex(ColumnCount, RowCount + 1);
            Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myWorkSheet.get_Range("A1", strExcelMaxColumnIndex);
            myRange.get_Resize(RowCount + 1, ColumnCount);
            try
            {
                myRange.Value2 = strArr;
                myRange.Columns.AutoFit();
                myRange.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                myWorkSheet.SaveAs(FileFullPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            }
            catch (Exception ex)
            { 
                return false;
            }
           
            excelApplication.Quit();
            killexcel(excelApplication);
            GC.Collect();
            return true;
        }
Пример #12
0
 private void btn_Read_Click(object sender, EventArgs e)
 {
     int P_int_Count = 0;//记录正在读取的行数
     string P_str_Line, P_str_Content = "";//记录读取行的内容及遍历到的内容
     List<string> P_str_List = new List<string>();//存储读取的所有内容
     StreamReader SReader = new StreamReader(txt_Txt.Text, Encoding.Default);//实例化流读取对象
     while ((P_str_Line = SReader.ReadLine()) != null)//循环读取文本文件中的每一行
     {
         P_str_List.Add(P_str_Line);//将读取到的行内容添加到泛型集合中
         P_int_Count++;//使当前读取行数加1
     }
     Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
     object missing = System.Reflection.Missing.Value;//获取缺少的object类型值
     //打开指定的Excel文件
     Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
     Microsoft.Office.Interop.Excel.Worksheet newWorksheet;//声明工作表对象
     for (int i = 0; i < P_str_List.Count; i++)//遍历泛型集合
     {
         P_str_Content = P_str_List[i];//记录遍历到的值
         newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
         newWorksheet.Cells[1, 1] = P_str_Content;//直接将遍历到的内容添加到工作表中
     }
     excel.Application.DisplayAlerts = false;//不显示提示对话框
     workbook.Save();//保存工作表
     workbook.Close(false, missing, missing);//关闭工作表
     MessageBox.Show("已经将文本文件的内容分解到了Excel的不同数据表中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 }
Пример #13
0
        public void exportExcel(DataGridView table)
        {
            if (table.Columns.Count > 0)
            {
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

                excel.Application.Workbooks.Add(true);

                int _columns = 0;
                int _rows = 1;

                foreach (DataGridViewColumn column in table.Columns)
                {
                    excel.Cells[1, ++_columns] = column.Name;
                }

                foreach (DataGridViewRow row in table.Rows)
                {
                    _columns = 0;
                    _rows++;
                    foreach (DataGridViewColumn column in table.Columns)
                    {
                        excel.Cells[_rows, ++_columns] = row.Cells[column.Name].Value;
                    }
                }

                excel.Visible = true;

            }
        }
Пример #14
0
        public List<string> GetExcelSheets(string excelFileName)
        {
            Microsoft.Office.Interop.Excel.Application excelFileObject = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workBookObject = null;
            workBookObject = excelFileObject.Workbooks.Open(excelFileName, 0, true, 5, "", "", false,
            Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
            "",
            true,
            false,
            0,
            true,
            false,
            false);
            Microsoft.Office.Interop.Excel.Sheets sheets = workBookObject.Worksheets;
            // get the first and only worksheet from the collection of worksheets
            List<string> sheetNames = new List<string>();
            Regex regSheetName = new Regex("Sheet\\d+");
            foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in sheets) {
                if (!regSheetName.IsMatch(sheet.Name)) {
                    sheetNames.Add(sheet.Name);
                }
                Marshal.ReleaseComObject(sheet);
            }

            excelFileObject.Quit();
            Marshal.ReleaseComObject(sheets);
            Marshal.ReleaseComObject(workBookObject);
            Marshal.ReleaseComObject(excelFileObject);
            return sheetNames;
        }
Пример #15
0
        public ExcelWriter(string _fileName)
        {
            fileName = _fileName;

            if(File.Exists(FilePath))
                throw new ApplicationException("File already exists: " + FilePath);

            File.Create(FilePath);

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

            Console.Error.WriteLine("Connected to Excel");

            wbs = app.Workbooks;

            wb = wbs.Add(1);

            wb.Activate();

            wss = wb.Sheets;

            ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1);

            Console.Error.WriteLine("Excel Worksheet Initialized");
        }
Пример #16
0
        public void Print(YellowstonePathology.Business.Search.ReportSearchList caseList, string description, DateTime printDate)
        {
            Microsoft.Office.Interop.Excel.Application xlApp;
            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.Visible = false;

            Microsoft.Office.Interop.Excel.Workbook wb = xlApp.Workbooks.Add(@"\\CFileServer\documents\ReportTemplates\MolecularTesting\CaseList.xlt");
            Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];

            ws.Cells[3, 1] = "Batch: " + description + " - " + printDate.ToShortDateString();

            int rowPosition = 6;

            for (int i = caseList.Count - 1; i > -1; i--)
            {
                ws.Cells[rowPosition, 1] = caseList[i].ReportNo;
                ws.Cells[rowPosition, 2] = caseList[i].PanelSetName;
                ws.Cells[rowPosition, 3] = caseList[i].PatientName;
                ws.Cells[rowPosition, 4] = caseList[i].PhysicianName + " - " + caseList[i].ClientName;
                ws.Cells[rowPosition, 5] = caseList[i].OrderedBy;
                rowPosition++;
            }

            Object oMissing = Type.Missing;
            Object oFalse = false;

            ws.PrintOut(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            wb.Close(oFalse, oMissing, oMissing);
            xlApp.Quit();
        }
 public void Dispose()
 {
     excelApp.Quit();
     Marshal.ReleaseComObject(excelApp);
     Marshal.FinalReleaseComObject(excelApp);
     excelApp = null;
 }
Пример #18
0
        public void insertBTN(String query, String path)
        {
            sqlCon.ConnectionString = conn;
            sqlCon.Open();

            SqlDataAdapter da = new SqlDataAdapter(query, sqlCon);
            System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
            da.Fill(dtMainSQLData);
            DataColumnCollection dcCollection = dtMainSQLData.Columns;

            // Export Data into EXCEL Sheet
            Microsoft.Office.Interop.Excel.Application ExcelApp = new
            Microsoft.Office.Interop.Excel.Application();
            ExcelApp.Application.Workbooks.Add(Type.Missing);

            // ExcelApp.Cells.CopyFromRecordset(objRS);

            for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
            {

                for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
                {
                    if (i == 1)
                    {
                        ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
                    }
                    else
                        ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
                }
            }

            ExcelApp.ActiveWorkbook.SaveCopyAs(path + "\\Results.xlsx");
            ExcelApp.ActiveWorkbook.Saved = true;
            ExcelApp.Quit();
        }
Пример #19
0
 private void btn_Read_Click(object sender, EventArgs e)
 {
     int P_int_Count=0;//记录正在读取的行数
     string P_str_Line, P_str_Content = "";//记录读取行的内容及遍历到的内容
     List<string> P_str_List = new List<string>();//存储读取的所有内容
     StreamReader SReader = new StreamReader(txt_Txt.Text, Encoding.Default);//实例化流读取对象
     while ((P_str_Line = SReader.ReadLine()) != null)//循环读取文本文件中的每一行
     {
         P_str_List.Add(P_str_Line);//将读取到的行内容添加到泛型集合中
         P_int_Count++;//使当前读取行数加1
     }
     Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
     object missing = System.Reflection.Missing.Value;//获取缺少的object类型值
     //打开指定的Excel文件
     Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
     Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);
     excel.Application.DisplayAlerts = false;//不显示提示对话框
     for (int i = 0; i < P_str_List.Count; i++)//遍历泛型集合
     {
         P_str_Content = P_str_List[i];//记录遍历到的值
         if (Regex.IsMatch(P_str_Content, "^[0-9]*[1-9][0-9]*$"))//判断是否是数字
             newWorksheet.Cells[i + 1, 1] = Convert.ToDecimal(P_str_Content).ToString("¥00.00");//格式化为货币格式,再添加到工作表中
         else
             newWorksheet.Cells[i + 1, 1] = P_str_Content;//直接将遍历到的内容添加到工作表中
     }
     workbook.Save();//保存工作表
     workbook.Close(false, missing, missing);//关闭工作表
     MessageBox.Show("已经将文本文件内容成功导入Excel工作表中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 }
        public void ConvertCsvToExcel_MicrosoftOfficeInteropExcel()
        {
            StringBuilder content = new StringBuilder();
            content.AppendLine("param1\tparam2\tstatus");
            content.AppendLine("0.5\t10\tpassed");
            content.AppendLine("10\t20\tfail");

            using (TemporaryFile xlsxFile = new TemporaryFile(".xlsx"))
            {
                Clipboard.SetText(content.ToString());
                Microsoft.Office.Interop.Excel.Application xlexcel;
                Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                xlexcel = new Microsoft.Office.Interop.Excel.Application();
                // for excel visibility
                //xlexcel.Visible = true;
                // Creating a new workbook
                xlWorkBook = xlexcel.Workbooks.Add(misValue);
                // Putting Sheet 1 as the sheet you want to put the data within
                xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
                // creating the range
                Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range) xlWorkSheet.Cells[1, 1];
                CR.Select();
                xlWorkSheet.Paste(CR, false);
                xlWorkSheet.SaveAs(xlsxFile.FileName);                
                xlexcel.Quit();
                Console.WriteLine("Created file {0}", xlsxFile.FileName);
            }
        }
        public static void OpenExcelDocs2(string filename, double[] content)
        {

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
            Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);   //引用Excel工作簿
            Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets.get_Item(1); ;  //引用Excel工作页面
            excel.Visible = false;

            sheet.Cells[24, 3] = content[1];
            sheet.Cells[25, 3] = content[0];

            book.Save();
            book.Close(Type.Missing, Type.Missing, Type.Missing);
            excel.Quit();  //应用程序推出,但是进程还在运行

            IntPtr t = new IntPtr(excel.Hwnd);          //杀死进程的好方法,很有效
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();

            //sheet = null;
            //book = null;
            //excel = null;   //不能杀死进程

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);  //可以释放对象,但是不能杀死进程
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);


        }
Пример #22
0
        /// <summary>
        /// 加载数据同时保存数据到指定位置
        /// </summary>
        /// <param name="obj"></param>
        private void AddData(FarPoint.Win.Spread.FpSpread obj)
        {
            wait = new WaitDialogForm("", "正在加载数据, 请稍候...");
            try
            {
                //打开Excel表格
                //清空工作表
                fpSpread1.Sheets.Clear();
                obj.OpenExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls");
                PF.SpreadRemoveEmptyCells(obj);
                //this.AddCellChanged();
                //this.barEditItem2.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
                //S4_2_1.AddBarEditItems(this.barEditItem2, this.barEditItem1, this);
            }
            catch (System.Exception e)
            {
                //如果打开出错则重新生成并保存
                LoadData();
                //判断文件夹是否存在,不存在则创建
                if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls"))
                {
                    Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls");
                }
                //保存EXcel文件
                obj.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls", FarPoint.Excel.ExcelSaveFlags.NoFlagsSet);
                // 定义要使用的Excel 组件接口
                // 定义Application 对象,此对象表示整个Excel 程序
                Microsoft.Office.Interop.Excel.Application excelApp = null;
                // 定义Workbook对象,此对象代表工作薄
                Microsoft.Office.Interop.Excel.Workbook workBook;
                // 定义Worksheet 对象,此对象表示Execel 中的一张工作表
                Microsoft.Office.Interop.Excel.Worksheet ws = null;
                Microsoft.Office.Interop.Excel.Range range = null;
                excelApp = new Microsoft.Office.Interop.Excel.Application();
                string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls";
                workBook = excelApp.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                for (int i = 1; i <= workBook.Worksheets.Count; i++)
                {

                    ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
                    //取消保护工作表
                    ws.Unprotect(Missing.Value);
                    //有数据的行数
                    int row = ws.UsedRange.Rows.Count;
                    //有数据的列数
                    int col = ws.UsedRange.Columns.Count;
                    //创建一个区域
                    range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
                    //设区域内的单元格自动换行
                    range.WrapText = true;
                    //保护工作表
                    ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                }
                //保存工作簿
                workBook.Save();
                //关闭工作簿
                excelApp.Workbooks.Close();
            }
            wait.Close();
        }
Пример #23
0
        public static void Main(string[] args)
        {
            PowerMILL.Application pMAppliacation = (PowerMILL.Application) System.Runtime.InteropServices.Marshal.GetActiveObject("PowerMill.Application");

            Microsoft.Office.Interop.Excel.Application exApplication = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook exWorkbook = exApplication.Workbooks.Open(@"e:\ExTest.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t",false, false, 0, true, 1, 0);
            Microsoft.Office.Interop.Excel._Worksheet exWorksheet = (Microsoft.Office.Interop.Excel._Worksheet)exWorkbook.Sheets[1];
            Microsoft.Office.Interop.Excel.Range exRange = exWorksheet.UsedRange;

            int rowCount = exRange.Rows.Count;
            int colCount = exRange.Columns.Count;

            for (int i = 1; i <= rowCount; i++) {
                for (int j = 1; j <= colCount; j++) {
                    string valueForPM = null;
                    try {
                        valueForPM = (string)(exRange.Cells[i, j] as Microsoft.Office.Interop.Excel.Range).Value.ToString();
                    } catch {}

                    if (valueForPM != null) {
                        pMAppliacation.DoCommand(@"MESSAGE INFO """+ valueForPM +@"""");
                    }
                }
            }
        }
Пример #24
0
        public byte[] Convert()
        {
            var excel = new Application();

            if (File.Exists(FileToSave))
            {
                File.Delete(FileToSave);
            }
            try
            {
                excel.Workbooks.Open(Filename: FullFilePath);
                excel.Visible = false;
                if (excel.Workbooks.Count > 0)
                {
                    var wsEnumerator = excel.ActiveWorkbook.Worksheets.GetEnumerator();
                    object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
                    while (wsEnumerator.MoveNext())
                    {
                        var wsCurrent = (Microsoft.Office.Interop.Excel.Worksheet)wsEnumerator.Current;
                        wsCurrent.SaveAs(Filename: FileToSave, FileFormat: format);
                        break;
                    }
                    excel.Workbooks.Close();
                }
            }
            finally
            {
                excel.Application.Quit();
            }
            return base.ReadConvertedFile();
        }
Пример #25
0
 public void CreateExcelFile()
 {
     app = new Microsoft.Office.Interop.Excel.Application();
     workbook = app.Workbooks.Add(1);
     worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];
     AddExcelProcessTolist();
 }
Пример #26
0
        private void button2_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
            ////Книга.
            //ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
            ////Таблица.
            //ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);

            ExcelApp.Workbooks.Open(Application.StartupPath + "\\result.xlsx");
            for (int j = 0; j < dataGridView1.ColumnCount; j++)
            {
                ExcelApp.Cells[3, j + 1] = dataGridView1.Columns[j].HeaderText;
            }

            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                for (int j = 0; j < dataGridView1.ColumnCount; j++)
                {
                    ExcelApp.Cells[i + 4, j + 1] = dataGridView1.Rows[i].Cells[j].Value;
                }
            }
            //Вызываем нашу созданную эксельку.
            ExcelApp.Visible = true;
            ExcelApp.UserControl = true;
        }
Пример #27
0
 private void btn_Export_Click(object sender, EventArgs e)
 {
     object missing = System.Reflection.Missing.Value;//声明object缺省值
     Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
     //打开Excel文件
     Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(txt_Path.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
     Microsoft.Office.Interop.Excel.Worksheet worksheet;//声明工作表
     Microsoft.Office.Interop.Access.Application access = new Microsoft.Office.Interop.Access.Application();//实例化Access对象
     worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[cbox_SheetName.Text]);//获取选择的工作表
     worksheet.Move(workbook.Sheets[1], missing);//将选择的工作表作为第一个工作表
     object P_obj_Name = (object)worksheet.Name;//获取工作表名称
     excel.DisplayAlerts = false;//设置Excel保存时不显示对话框
     workbook.Save();//保存工作簿
     CloseProcess("EXCEL");//关闭所有Excel进程
     object P_obj_Excel = (object)txt_Path.Text;//记录Excel文件路径
     try
     {
         access.OpenCurrentDatabase(txt_Access.Text, true, "");//打开Access数据库
         //将Excel指定工作表中的数据导入到Access中
         access.DoCmd.TransferSpreadsheet(Microsoft.Office.Interop.Access.AcDataTransferType.acImport, Microsoft.Office.Interop.Access.AcSpreadSheetType.acSpreadsheetTypeExcel97, P_obj_Name, P_obj_Excel, true, missing, missing);
         access.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);//关闭并保存Access数据库文件
         CloseProcess("MSACCESS");//关闭所有Access数据库进程
         MessageBox.Show("已经将Excel的" + cbox_SheetName.Text + "工作表中的数据导入到Access数据库中!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
     }
     catch
     {
         MessageBox.Show("Access数据库中已经存在该表!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
     }
 }
Пример #28
0
 public ExcelFile(string filePath)
 {
     _application = new Microsoft.Office.Interop.Excel.Application();
     _workbook = _application.Workbooks.Open(filePath);
     _worksheet = (Microsoft.Office.Interop.Excel.Worksheet)_workbook.Worksheets.Item[1];
     _filename = filePath;
     _currentSheetNumber = 1;
 }
Пример #29
0
        /*
        private void loadKeyTable(Microsoft.Office.Interop.Excel.Worksheet sheet)
        {
            foreach (string key in keyList_)
            {
                for (int i = 1; i < 100; i++)
                {
                    Microsoft.Office.Interop.Excel.Range rgn = sheet.Cells[3, i];
                    dynamic val = rgn.Value2;
                    string title = Convert.ToString(val);

                    int indexof = title.IndexOf(key);
                    if (indexof != -1)
                    {
                        keyTable_.Add(key, i);
                        break;
                    }
                }
            }
        }
        */
        public DataExcelManager(Microsoft.Office.Interop.Excel.Application app, string path, string sheet)
        {
            excelApp_ = app;
            excelPath_ = path;
            sheetName_ = sheet;
            //keyList_ = new string[] { "Collection Retail", "Market", "Product reference", "Product Reference Name", "Product color code", "Product color", "Product size", "YEN", "Note" };
            //keyTable_ = new Hashtable();
        }
Пример #30
0
        //导出
        private void barBtnDaochuExcel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            string fname = "";
            saveFileDialog1.Filter = "Microsoft Excel (*.xls)|*.xls";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                fname = saveFileDialog1.FileName;

                try
                {
                    fpSpread1.SaveExcel(fname);
                    //以下是打开文件设表格自动换行

                    // 定义要使用的Excel 组件接口
                    // 定义Application 对象,此对象表示整个Excel 程序
                    Microsoft.Office.Interop.Excel.Application excelApp = null;
                    // 定义Workbook对象,此对象代表工作薄
                    Microsoft.Office.Interop.Excel.Workbook workBook;
                    // 定义Worksheet 对象,此对象表示Execel 中的一张工作表
                    Microsoft.Office.Interop.Excel.Worksheet ws = null;
                    Microsoft.Office.Interop.Excel.Range range = null;
                    excelApp = new Microsoft.Office.Interop.Excel.Application();
                    workBook = excelApp.Workbooks.Open(fname, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    for (int i = 1; i <= workBook.Worksheets.Count; i++)
                    {

                        ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
                        //取消保护工作表
                        ws.Unprotect(Missing.Value);
                        //有数据的行数
                        int row = ws.UsedRange.Rows.Count;
                        //有数据的列数
                        int col = ws.UsedRange.Columns.Count;
                        //创建一个区域
                        range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
                        //设区域内的单元格自动换行
                        range.WrapText = true;
                        //保护工作表
                        //ws.Protect(Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    }
                    //保存工作簿
                    workBook.Save();
                    //关闭工作簿
                    excelApp.Workbooks.Close();
                    if (MessageBox.Show("导出成功,是否打开该文档?","询问",MessageBoxButtons.YesNo,MessageBoxIcon.Question) != DialogResult.Yes)
                        return;

                    System.Diagnostics.Process.Start(fname);

                }
                catch
                {
                    MessageBox.Show("无法保存" + fname + "。请用其他文件名保存文件,或将文件存至其他位置。");
                    return;
                }
            }
        }
        private void OutputExecelFromDataTable(DataTable dt, string EXCEL_SAVE_PATH)
        {
            if (dt.Rows.Count > 0)
            {// Excelを参照設定する必要があります
             // [参照の追加],[COM],[Microsoft Excel *.* Object Library]
             // Imports Microsoft.Office.Interop (必要)
             // Imports System.Runtime.InteropServices (必要)

                //Excel出力
                // EXCEL関連オブジェクトの定義

                // Excelを起動する
                Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
                objExcel.Visible = true;
                try
                {
                    //待機状態
                    Cursor.Current = Cursors.WaitCursor;

                    // ブック(ファイル)を開く
                    Microsoft.Office.Interop.Excel.Workbook objWorkBook = objExcel.Workbooks.Open(EXCEL_SAVE_PATH);
                    try
                    {
                        // 現在日時を取得
                        string timestanpText = DateTime.Now.ToString(" yyyy/MM/dd HH:mm");    //bbl.GetDate();

                        int    count      = 0;
                        int    currentRow = (int)ERow.DetailStartRow;
                        int    page       = 1;
                        string breakKey   = "";
                        foreach (DataRow row in dt.Rows)
                        {
                            if (breakKey != "" && breakKey != row["VendorCD"].ToString())
                            {
                                count = DATA_COUNT;
                            }

                            if (count >= DATA_COUNT)
                            {
                                //行1からGYO_COUNTまでの行をこぴーする
                                objWorkBook.Sheets[1].Range("1:" + GYO_COUNT).Copy(objWorkBook.Sheets[1].Range("A" + (GYO_COUNT * page + 1)));

                                //Detail部をクリアする
                                currentRow = (int)ERow.DetailStartRow + page * GYO_COUNT;

                                objWorkBook.Sheets[1].Range("A" + currentRow + ":BZ" + (currentRow + DATA_COUNT - 1)).ClearContents();
                                page++;
                                count = 0;
                            }

                            if (count == 0)
                            {
                                breakKey = row["VendorCD"].ToString();

                                if (page == 1)
                                {
                                    objWorkBook.Sheets[1].Cells[2, 64] = timestanpText;
                                    objWorkBook.Sheets[1].Cells[(int)ERow.Print1, (int)ECell.Print1]           = row["Print1"].ToString();
                                    objWorkBook.Sheets[1].Cells[(int)ERow.Print2, (int)ECell.Print2]           = row["Print2"].ToString();
                                    objWorkBook.Sheets[1].Cells[(int)ERow.Print3, (int)ECell.Print3]           = row["Print3"].ToString();
                                    objWorkBook.Sheets[1].Cells[(int)ERow.Print4, (int)ECell.Print4]           = row["Print4"].ToString();
                                    objWorkBook.Sheets[1].Cells[(int)ERow.ZIP, (int)ECell.ZIP]                 = row["ZIP"].ToString();
                                    objWorkBook.Sheets[1].Cells[(int)ERow.CompanyName, (int)ECell.CompanyName] = row["CompanyName"].ToString();
                                    objWorkBook.Sheets[1].Cells[(int)ERow.TEL, (int)ECell.TEL]                 = row["TEL"].ToString();
                                }
                                objWorkBook.Sheets[1].Cells[(int)ERow.VendorName + (page - 1) * GYO_COUNT, (int)ECell.VendorName]   = row["VendorName"].ToString();
                                objWorkBook.Sheets[1].Cells[(int)ERow.OrderPerson + (page - 1) * GYO_COUNT, (int)ECell.OrderPerson] = row["OrderPerson"].ToString();
                            }

                            objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.PurchaseDate]       = row["PurchaseDate"].ToString();
                            objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.VendorDeliveryNo]   = row["VendorDeliveryNo"].ToString();
                            objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.MakerItem]          = row["MakerItem"].ToString();
                            objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.ColorName]          = row["ColorName"].ToString() + Environment.NewLine + row["SizeName"].ToString();
                            objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.PurchaserUnitPrice] = row["PurchaserUnitPrice"].ToString();
                            objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.OrderUnitPrice]     = row["OrderUnitPrice"].ToString();
                            objWorkBook.Sheets[1].Cells[currentRow, (int)ECell.PurchaseSu]         = row["PurchaseSu"].ToString();

                            currentRow++;
                            count++;
                        }

                        //// エクセル表示
                        //objExcel.Visible = true;
                        objWorkBook.Save();

                        // クローズ
                        objWorkBook.Close(false);
                        objExcel.Quit();
                    }
                    finally
                    {
                        if (objWorkBook != null)
                        {
                            try
                            {
                                //objWorkBook.Close();
                            }
                            finally
                            {
                                // EXCEL解放
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkBook);
                                objWorkBook = null /* TODO Change to default(_) if this is not a reference type */;
                            }
                        }
                    }
                }
                finally
                {
                    if (objExcel != null)
                    {
                        try
                        {
                            //objExcel.Quit();
                        }
                        finally
                        {
                            // EXCEL解放
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel);
                            objExcel = null /* TODO Change to default(_) if this is not a reference type */;
                        }
                    }

                    //元に戻す
                    Cursor.Current = Cursors.Default;
                }
            }
        }
Пример #32
0
        /// <summary>
        /// FUNCTION FOR EXPORT TO EXCEL
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="worksheetName"></param>
        /// <param name="saveAsLocation"></param>
        /// <returns></returns>
        public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
        {
            Microsoft.Office.Interop.Excel.Application excel;
            Microsoft.Office.Interop.Excel.Workbook    excelworkBook;
            Microsoft.Office.Interop.Excel.Worksheet   excelSheet;
            Microsoft.Office.Interop.Excel.Range       excelCellrange;

            try
            {
                // Start Excel and get Application object.
                excel = new Microsoft.Office.Interop.Excel.Application();

                // for making Excel visible
                excel.Visible       = false;
                excel.DisplayAlerts = false;

                // Creation a new Workbook
                excelworkBook = excel.Workbooks.Add(Type.Missing);

                // Workk sheet
                excelSheet      = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
                excelSheet.Name = worksheetName;


                excelSheet.Cells[1, 1] = ReporType;
                excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();

                // loop through each row and add values to our sheet
                int rowcount = 2;

                foreach (DataRow datarow in dataTable.Rows)
                {
                    rowcount += 1;
                    for (int i = 1; i <= dataTable.Columns.Count; i++)
                    {
                        // on the first iteration we add the column headers
                        if (rowcount == 3)
                        {
                            excelSheet.Cells[2, i]      = dataTable.Columns[i - 1].ColumnName;
                            excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
                        }

                        excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();

                        //for alternate rows
                        if (rowcount > 3)
                        {
                            if (i == dataTable.Columns.Count)
                            {
                                if (rowcount % 2 == 0)
                                {
                                    excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
                                    FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
                                }
                            }
                        }
                    }
                }

                // now we resize the columns
                excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
                excelCellrange.EntireColumn.AutoFit();
                Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
                border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                border.Weight    = 2d;


                excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
                FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);

                excelworkBook.SaveAs(saveAsLocation);
                excelworkBook.Close();
                excel.Quit();

                ShowSavedExcelFile(saveAsLocation);
                return(true);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return(false);
            }
            finally
            {
                excelSheet     = null;
                excelCellrange = null;
                excelworkBook  = null;
            }
        }
Пример #33
0
        private void expExportToExcel_Expanded(object sender, RoutedEventArgs e)
        {
            int intRowCounter;
            int intRowNumberOfRecords;
            int intColumnCounter;
            int intColumnNumberOfRecords;

            // Creating a Excel object.
            Microsoft.Office.Interop.Excel._Application excel     = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;

            try
            {
                expExportToExcel.IsExpanded = false;

                worksheet = workbook.ActiveSheet;

                worksheet.Name = "OpenOrders";

                int cellRowIndex    = 1;
                int cellColumnIndex = 1;
                intRowNumberOfRecords    = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Rows.Count;
                intColumnNumberOfRecords = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Columns.Count;

                for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
                {
                    worksheet.Cells[cellRowIndex, cellColumnIndex] = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Columns[intColumnCounter].ColumnName;

                    cellColumnIndex++;
                }

                cellRowIndex++;
                cellColumnIndex = 1;

                //Loop through each row and read value from each column.
                for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
                {
                    for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
                    {
                        worksheet.Cells[cellRowIndex, cellColumnIndex] = TheFindHelpDeskTicketsForUserDataSet.FindHelpDeskTicketsByUser.Rows[intRowCounter][intColumnCounter].ToString();

                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }

                //Getting the location and file name of the excel to save from user.
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Filter      = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                saveDialog.FilterIndex = 1;

                saveDialog.ShowDialog();

                workbook.SaveAs(saveDialog.FileName);
                MessageBox.Show("Export Successful");
            }
            catch (System.Exception ex)
            {
                TheEventLogClass.InsertEventLogEntry(DateTime.Now, "New Blue Jay ERP // My Tickets // Export To Excel " + ex.Message);

                MessageBox.Show(ex.ToString());
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
Пример #34
0
        static void TaskMethod(object title)
        {
            lock (taskMethodLocj)
            {
                DataGridView dataGridView1 = InDataWindow.InData_.dataGridView1;

                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
                    return;
                }
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook  workbook  = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

                //写入标题
                for (int i = 0; i < dataGridView1.ColumnCount; i++)
                {
                    worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
                }
                //写入数值
                for (int r = 0; r < dataGridView1.Rows.Count; r++)
                {
                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        if (i == 7)
                        {
                            worksheet.Cells[r + 2, i + 1] = "'" + dataGridView1.Rows[r].Cells[i].Value;
                        }
                        else
                        {
                            worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
                        }
                    }
                    Application.DoEvents();
                }
                worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应
                                                          //   if (Microsoft.Office.Interop.cmbxType.Text != "Notification")
                                                          //   {
                                                          //       Excel.Range rg = worksheet.get_Range(worksheet.Cells[2, 2], worksheet.Cells[ds.Tables[0].Rows.Count + 1, 2]);
                                                          //      rg.NumberFormat = "00000000";
                                                          //   }

                if (title.ToString() != "")
                {
                    try
                    {
                        workbook.Saved = true;
                        workbook.SaveCopyAs(title.ToString());
                        //fileSaved = true;
                    }
                    catch (Exception ex)
                    {
                        //fileSaved = false;
                        MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
                    }
                }
                //else
                //{
                //    fileSaved = false;
                //}
                xlApp.Quit();
                GC.Collect(); //强行销毁
                              // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
                MessageBox.Show("导出文件成功", "提示", MessageBoxButtons.OK);
            }
        }
Пример #35
0
        private void btn_exportExcel_Click(object sender, EventArgs e)
        {
            btn_exportExcel.Enabled = false;
            if (grdvw_SN_data.Rows.Count > 1)
            {
                // creating Excel Application
                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                // creating new WorkBook within Excel application
                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
                // creating new Excelsheet in workbook
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                // see the excel sheet behind the program
                Microsoft.Office.Interop.Excel.Range aRange;
                //app.Visible = true;
                // get the reference of first sheet. By default its name is Sheet1.
                // store its reference to worksheet
                worksheet = workbook.Sheets["Sheet1"];
                worksheet = workbook.ActiveSheet;

                // changing the name of active sheet
                //worksheet.Name = "Exported from gridview";
                //worksheet.AllocatedRange.AutoFitColumns();
                //worksheet.AllocatedRange.AutoFitRows();
                // storing header part in Excel

                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter   = "XLS (*.xlsx)|*.xlsx";
                sfd.FileName = "Output.xlsx";
                //string filename1 = @"D:\temp\test.csv";
                bool fileError = false;
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    if (File.Exists(sfd.FileName))
                    {
                        try
                        {
                            File.Delete(sfd.FileName);
                        }
                        catch (IOException ex)
                        {
                            fileError = true;
                            MessageBox.Show("It wasn't possible to write the data to the disk." + ex.Message);
                        }
                    }
                }
                if (!fileError)
                {
                    try
                    {
                        string CsvFpath = sfd.FileName;
                        for (int l = 1; l < grdvw_SN_data.Columns.Count + 1; l++)
                        {
                            worksheet.Cells[1, l] = grdvw_SN_data.Columns[l - 1].HeaderText;
                        }
                        int i = 0, j = 0;
                        // storing Each row and column value to excel sheet
                        for (i = 0; i < grdvw_SN_data.Rows.Count - 1; i++)
                        {
                            for (j = 0; j < grdvw_SN_data.Columns.Count; j++)
                            {
                                //app.Range["G"].NumberFormat =
                                app.Range["A" + (i + 2)].NumberFormat = "@";
                                app.Range["E" + (i + 2)].NumberFormat = "@";
                                app.Range["F" + (i + 2)].NumberFormat = "0";
                                app.Range["G" + (i + 2)].NumberFormat = "@";
                                app.Range["J" + (i + 2)].NumberFormat = "0";
                                app.Range["K" + (i + 2)].NumberFormat = "@";

                                worksheet.Cells[i + 2, j + 1] = grdvw_SN_data.Rows[i].Cells[j].Value.ToString();
                            }
                        }
                        aRange = worksheet.get_Range("A2", "N" + (i + 1));

                        aRange.Columns.AutoFit();
                        // CellRange cRange = worksheet.Range[]
                        aRange.Borders.LineStyle = LineStyleType.Dashed;
                        //aRange.Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
                        //aRange.Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;


                        // save the application
                        workbook.SaveAs(CsvFpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        // Exit from the application
                        MessageBox.Show("Data Exported Successfully !!!", "Info");


                        app.Quit();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error :" + ex.Message);
                    }
                }
            }
            else
            {
                MessageBox.Show("No Record To Export !!!", "Info");
            }

            btn_exportExcel.Enabled = true;
        }
Пример #36
0
        private bool ExportDataToExcel()
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            try
            {
                // creating new Excelsheet in workbook
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                // see the excel sheet behind the program
                app.Visible = true;
                // get the reference of first sheet. By default its name is Sheet1.
                // store its reference to worksheet
                worksheet = workbook.Sheets["Sheet1"];
                worksheet = workbook.ActiveSheet;
                // changing the name of active sheet
                worksheet.Name = cmbBillingCycle.Text;
                // storing header part in Excel
                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                }
                // storing Each row and column value to excel sheet
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value == null ? string.Empty : dataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }
                var outputPath = Properties.Settings.Default.OutputFolderPath;
                outputPath += cmbAccountName.Text + "\\" + cmbYear.Text + "\\" + cmbBillingCycle.Text + "\\";
                var path = string.Empty;
                if (errorMsgs.Count() > 0)
                {
                    outputPath = outputPath + "ErrorReport\\";
                    CreateFolderStructure(outputPath);
                    path = outputPath + "Error.xls";
                }
                else
                {
                    CreateFolderStructure(outputPath);
                    path = outputPath + cmbBillingCycle.Text + ".xls";
                }
                // save the application
                workbook.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //workbook.Close();

                workbook.Close(0);
                //app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                // Exit from the application
                return(true);
            }
            catch
            {
                return(false);
            }
            finally
            {
                workbook.Close(Type.Missing, Type.Missing, Type.Missing);
                app.Quit();

                if (workbook != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                }
                //app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            }
        }
Пример #37
0
        public ActionResult UploadFileAjax(string accion, string fec_informe, string fec_informe_hasta)
        {
            try
            {
                for (int i = 0; i < Request.Files.Count; i++)
                {
                    var fileContent = Request.Files[i];
                    if (fileContent != null && fileContent.ContentLength > 0)
                    {
                        string fileCliente    = Path.GetFileName(fileContent.FileName);
                        string fileExtension  = Path.GetExtension(fileContent.FileName);
                        string fileDestino    = string.Empty;
                        string fileDestinoDBS = string.Empty;
                        int    pid            = 0;

                        if (fileExtension != ".xlsx" && fileExtension != ".xls")
                        {
                            return(Json(
                                       new Response
                            {
                                Status = HttpStatusCode.BadRequest,
                                Message = "El archivo debe ser tipo Excel (.xlsx,.xls)",
                            },
                                       JsonRequestBehavior.AllowGet));
                        }

                        // get a stream
                        var stream = fileContent.InputStream;

                        var nomenclatura = "BAL_Car_" + DateTime.Now.ToString("yyMMddhhmmss") + ".xlsx";
                        if (HttpContext.IsDebuggingEnabled)
                        {
                            fileDestino    = Path.Combine(@"M:\MVC\com\xlsd", nomenclatura);
                            fileDestinoDBS = fileDestino.Replace(@"M:\MVC\com\xlsd", xlsPathDES);
                        }
                        else
                        {
                            fileDestino    = Path.Combine(Server.MapPath("~/XlsD"), nomenclatura);
                            fileDestinoDBS = fileDestino.Replace(Server.MapPath("~/XlsD"), xlsPathPRO);
                        }
                        var mensajeError = string.Empty;
                        if (accion == "CARGA")
                        {
                            using (var fileStream = System.IO.File.Create(fileDestinoDBS))
                            {
                                stream.CopyTo(fileStream);
                            }
                        }

                        // Formateando excel a 2 decimales

                        //Grabacion de temporal en sql
                        var cod_unidad_negocio = string.Empty;
                        if (Session["cod_unidad_negocio"] != null)
                        {
                            cod_unidad_negocio = Session["cod_unidad_negocio"].ToString();
                        }

                        try
                        {
                            var excelApp = new Microsoft.Office.Interop.Excel.Application(); //CreateObject("Excel.Application")
                            excelApp.Application.Visible = false;
                            excelApp.DisplayAlerts       = false;
                            var excelWorkbook = excelApp.Workbooks.Open(Filename: fileDestinoDBS, ReadOnly: false);
                            GetWindowThreadProcessId(excelApp.Hwnd, out pid);

                            try
                            {
                                excelWorkbook.Sheets["BD Balmet"].Range("F6:EO500").NumberFormat = "0.0000000000000000";
                            }
                            catch (Exception exc)
                            {
                                mensajeError = "ERROR: En la ejecución de Excel " + exc.Message;
                            }

                            //Close Excel
                            excelWorkbook.Close(SaveChanges: true);
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
                            excelWorkbook = null;
                            excelApp.Quit();
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                            excelApp = null;
                            var archivoDestino = fileDestinoDBS.Replace(@"\", "/");

                            try
                            {
                                //procesoExcel.Kill();
                                Process.GetProcessById(pid).Kill();
                            }
                            catch (Exception)
                            {
                            }
                        }
                        catch (Exception ex)
                        {
                            System.Threading.Thread.Sleep(1000);  //Espera un segundo para intentarlo de nuevo
                            mensajeError += " Servicio de Excel en estos momentos se encuentra sin recursos.\r Por favor intentelo mas tarde.";
                        }

                        DateTime dtfecInforme      = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                        DateTime dtfecInformeHasta = DateTime.ParseExact(fec_informe_hasta, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                        var      execute           = balNeg.up_bal_pro_cargaXLS(cod_unidad_negocio, Usuario.Item1.cod_usuario, fileDestinoDBS, fileDestino, "CARGA", 0, dtfecInforme, dtfecInformeHasta);

                        if (execute.tipo == "ERROR")
                        {
                            return(Json(
                                       new Response
                            {
                                Status = HttpStatusCode.BadRequest,
                                Message = execute.mensaje.Replace("\n", "<br>")
                            },
                                       JsonRequestBehavior.AllowGet));
                        }
                        else if (execute.tipo == "SUCCESS")
                        {
                            execute = balNeg.up_bal_pro_cargaXLS(cod_unidad_negocio, Usuario.Item1.cod_usuario, fileDestinoDBS, fileDestino, "START", 0, dtfecInforme, dtfecInformeHasta);

                            if (execute.tipo == "ERROR")
                            {
                                return(Json(
                                           new Response
                                {
                                    Status = HttpStatusCode.BadRequest,
                                    Message = execute.mensaje,
                                    Kind = execute.tipo,
                                    Id = execute.Id.ToString()
                                },
                                           JsonRequestBehavior.AllowGet));
                            }
                            else if (execute.tipo == "WARNING")
                            {
                                return(Json(
                                           new Response
                                {
                                    Status = HttpStatusCode.BadRequest,
                                    Message = execute.mensaje,
                                    Kind = execute.tipo,
                                    Id = execute.Id.ToString(),
                                    Aux = nomenclatura,
                                    Aux2 = fileDestinoDBS,
                                    Aux3 = fileDestino
                                },
                                           JsonRequestBehavior.AllowGet));
                            }
                            else
                            {
                                execute = balNeg.up_bal_pro_cargaXLS(cod_unidad_negocio, Usuario.Item1.cod_usuario, fileDestinoDBS, fileDestino, "DETALLE", execute.Id, dtfecInforme, dtfecInformeHasta);

                                if (execute.tipo == "ERROR")
                                {
                                    return(Json(
                                               new Response
                                    {
                                        Status = HttpStatusCode.BadRequest,
                                        Message = execute.mensaje,
                                        Kind = execute.tipo,
                                        Id = execute.Id.ToString()
                                    },
                                               JsonRequestBehavior.AllowGet));
                                }
                                else
                                {
                                    return(Json(
                                               new Response
                                    {
                                        Status = HttpStatusCode.OK,
                                        Message = execute.mensaje
                                    },
                                               JsonRequestBehavior.AllowGet));
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                return(Json(
                           new Response
                {
                    Status = HttpStatusCode.BadRequest,
                    Message = "El archivo no se cargó. " + e.Message,
                },
                           JsonRequestBehavior.AllowGet));
            }

            return(Json(
                       new Response
            {
                Status = HttpStatusCode.BadRequest,
                Message = "No se puede continuar por errores en el modelo",
                Errors = ModelState.Values.SelectMany(x => x.Errors).Select(x => x.ErrorMessage)
            },
                       JsonRequestBehavior.AllowGet));
        }
Пример #38
0
        public static void Run()
        {
            #region kill any Excel process before run the app
            Process[] prs = Process.GetProcesses();
            foreach (Process pr in prs)
            {
                if (pr.ProcessName == "EXCEL")
                {
                    pr.Kill();
                }
            }

            #endregion
            try
            {
                // Create a new FileSystemWatcher and set its properties.
                FileSystemWatcher watcher = new FileSystemWatcher();
                watcher.Path = ConfigurationManager.AppSettings["RootFolder"];
                /* Watch for changes in LastAccess and LastWrite times, and the renaming of files or directories. */
                watcher.NotifyFilter = NotifyFilters.LastAccess | NotifyFilters.LastWrite
                                       | NotifyFilters.FileName | NotifyFilters.DirectoryName;
                // Only watch xls files.
                watcher.Filter = "*.xls*";
                // Add event handlers.

                // Begin watching.
                watcher.EnableRaisingEvents = true;
                #region open and work with excel file
                foreach (string file in Directory.EnumerateFiles(ConfigurationManager.AppSettings["RootFolder"], "*.xls"))
                {
                    Microsoft.Office.Interop.Excel.Application xlApp       = new Microsoft.Office.Interop.Excel.Application();
                    Microsoft.Office.Interop.Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(file);
                    Microsoft.Office.Interop.Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
                    Microsoft.Office.Interop.Excel.Range       xlRange     = xlWorksheet.UsedRange;

                    int rowCount = xlRange.Rows.Count;
                    int colCount = xlRange.Columns.Count;

                    inputFileName = Path.GetFileNameWithoutExtension(file);
                    ArrayList vagonsList = new ArrayList();

                    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //create COM-object Excel
                    excel.Visible = false;                                                                               //make excel process invisible
                    excel.Workbooks.Add(Type.Missing);                                                                   //add workbook
                    excel.SheetsInNewWorkbook = 1;                                                                       //number of pages in workbook
                    Microsoft.Office.Interop.Excel.Workbook  workbook = excel.Workbooks[1];                              //get link on 1st book
                    Microsoft.Office.Interop.Excel.Worksheet sheet    = workbook.Worksheets.get_Item(1);                 //get link on 1st page
                    sheet.Name        = "Report" + " " + DateTime.Now.ToString("dd.MM.yy");
                    sheet.Cells[1, 1] = "№ КОНТЕЙНЕРА";
                    sheet.Cells[1, 2] = "СТАНЦ";
                    sheet.Cells[1, 3] = "ОПЕР";
                    sheet.Cells[1, 4] = "ДАТА";
                    sheet.Cells[1, 5] = "ВРЕМЯ";
                    sheet.Cells[1, 6] = "СОСТ";
                    sheet.Cells[1, 7] = "N ОТПР";
                    sheet.Cells[1, 8] = "N ВАГОНА";

                    for (int i = 1; i <= rowCount; i++)
                    {
                        for (int j = 1; j <= colCount; j++)
                        {
                            if (j == 1)
                            {
                                if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                                {
                                    //add useful things here!
                                    vagonsList.Add(xlRange.Cells[i, j].Value2.ToString());
                                }
                            }
                        }
                    }
                    ConsoleColor color = Console.ForegroundColor;
                    Console.ForegroundColor = ConsoleColor.DarkYellow;
                    Console.WriteLine($"Start processing file {inputFileName}.");
                    Console.ForegroundColor = color;
                    for (int i = 0; i < vagonsList.Count; i++)
                    {
                        try
                        {
                            int list      = vagonsList.Count;
                            var container = vagonsList[i];
                            if (vagonsList != null)
                            {
                                //Console.WriteLine($"Start processing file {inputFileName}.");
                                File.WriteAllText(ConfigurationManager.AppSettings["querryFolder"] + "01" + 11 + "000" + ".000", string.Format("(:217 0:1680 {0}:)", container));
                                Console.WriteLine($"Now processing: {container}");
                                System.Threading.Thread.Sleep(9900);
                            }
                            else
                            {
                                break;
                            }
                            string        answerFileName      = "01" + 11 /*15й порт Левитан, 11 мой*/ + "2400";
                            DirectoryInfo dirConcentratorPath = new DirectoryInfo(ConfigurationManager.AppSettings["answerFolder"]);
                            FileInfo[]    fileInDir           = dirConcentratorPath.GetFiles(answerFileName + "*.*");
                            foreach (FileInfo foundFile in fileInDir)
                            {
                                string fullName = foundFile.FullName;
                                var    lines    = File.ReadAllLines(foundFile.FullName, Encoding.GetEncoding(866));
                                sheet.get_Range("E2", string.Format("E{0}", vagonsList.Count)).NumberFormat = "@";
                                string text = ""; // search key variable
                                using (StreamReader sr = new StreamReader(foundFile.FullName, Encoding.GetEncoding(866)))
                                {
                                    text = sr.ReadToEnd();
                                    Regex regexNoInfo = new Regex("[Н][Е][Т]\\s[А-Я]{10}");
                                    foreach (var line in lines)
                                    {
                                        var NoInfoMatches = regexNoInfo.Matches(line);
                                        if (NoInfoMatches.Count > 0)
                                        {
                                            var    res    = NoInfoMatches[0].Value;
                                            string noinfo = "-";
                                            sheet.Cells[i + 2, 1].Value = container;
                                            sheet.Cells[i + 2, 2].Value = noinfo;
                                            sheet.Cells[i + 2, 3].Value = noinfo;
                                            sheet.Cells[i + 2, 4].Value = noinfo;
                                            sheet.Cells[i + 2, 5].Value = noinfo;
                                            sheet.Cells[i + 2, 6].Value = noinfo;
                                            sheet.Cells[i + 2, 7].Value = noinfo;
                                            sheet.Cells[i + 2, 8].Value = noinfo;
                                            break;
                                        }
                                        else
                                        {
                                            continue;
                                        }
                                    }
                                    Regex regexOkInfo = new Regex(@"([О][П][Е][Р][А][Ц][И][И]\s[С]\s[К])");
                                    foreach (var line in lines)
                                    {
                                        var okInfoMatches = regexOkInfo.Matches(line);
                                        if (okInfoMatches.Count > 0)
                                        {
                                            string          tempAnswer      = lines[lines.Length - 4];
                                            string          pattern         = @"([A-Я]{4})|([А-Я]{3}.)|(\d\d[.]\d\d[.]\d\d)|([А-Я]{3}.)|(\d{2}[-]\d{2})|(\d{8})|(\d{6}?)";
                                            Regex           rgx             = new Regex(pattern);
                                            MatchCollection matchList       = Regex.Matches(tempAnswer, pattern);
                                            var             results         = matchList.Cast <Match>().Select(match => match.Value).ToList();
                                            var             station         = matchList[0].Value;
                                            var             operation       = matchList[1].Value;
                                            var             dt              = matchList[2].Value;
                                            DateTime        dateOfOperation = Convert.ToDateTime(dt);
                                            string          timeOfOperation = matchList[3].Value;
                                            var             state           = matchList[4].Value;
                                            if (results.Count == 5)
                                            {
                                                sheet.Cells[i + 2, 1].Value = container; //get ontainer name in column
                                                sheet.Cells[i + 2, 2].Value = station;
                                                sheet.Cells[i + 2, 3].Value = operation;
                                                sheet.Cells[i + 2, 4].Value = dateOfOperation;
                                                sheet.Cells[i + 2, 5].Value = timeOfOperation;
                                                sheet.Cells[i + 2, 6].Value = state;
                                                string noinfo = "-";
                                                sheet.Cells[i + 2, 7].Value = noinfo;
                                                sheet.Cells[i + 2, 8].Value = noinfo;
                                            }

                                            else if (results.Count == 6)
                                            {
                                                var otpravka = matchList[5].Value;
                                                sheet.Cells[i + 2, 1].Value = container; //get ontainer name in column
                                                sheet.Cells[i + 2, 2].Value = station;
                                                sheet.Cells[i + 2, 3].Value = operation;
                                                sheet.Cells[i + 2, 4].Value = dateOfOperation;
                                                sheet.Cells[i + 2, 5].Value = timeOfOperation;
                                                sheet.Cells[i + 2, 6].Value = state;
                                                sheet.Cells[i + 2, 7].Value = otpravka;
                                                string noinfo = "-";
                                                sheet.Cells[i + 2, 8].Value = noinfo;
                                            }
                                            else
                                            {
                                                var otpravka = matchList[5].Value;
                                                var vagon    = matchList[6].Value;
                                                #endregion
                                                sheet.Cells[i + 2, 1].Value = container; //get ontainer name in column
                                                sheet.Cells[i + 2, 2].Value = station;
                                                sheet.Cells[i + 2, 3].Value = operation;
                                                sheet.Cells[i + 2, 4].Value = dateOfOperation;
                                                sheet.Cells[i + 2, 5].Value = timeOfOperation;
                                                sheet.Cells[i + 2, 6].Value = state;
                                                sheet.Cells[i + 2, 7].Value = otpravka;
                                                sheet.Cells[i + 2, 8].Value = vagon;
                                            }
                                        }
                                        else
                                        {
                                            continue;
                                        }
                                    }
                                }
                                File.Delete(foundFile.FullName);
                            }
                        }
                        catch (Exception e)
                        {
                            foreach (Process pr in prs)
                            {
                                if (pr.ProcessName == "EXCEL" && pr.ProcessName == "FastCustom")
                                {
                                    pr.Kill();
                                }
                            }
                            //this will use the "fileLogger" logger from our NLog.config file
                            Logger logger = LogManager.GetLogger("fileLogger");

                            //add custom message and pass in the exception
                            logger.Error(e, "Need fix some problems!");
                        }
                    }

                    #region Formatting excel
                    var formatTable = vagonsList.Count + 1;                     // fix applying formatting for last string
                    sheet.get_Range("B1", string.Format("H{0}", formatTable)).Cells.HorizontalAlignment =
                        Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //aligment center
                    Microsoft.Office.Interop.Excel.Range chartRange;
                    chartRange = sheet.get_Range("a1", "h1");
                    foreach (Microsoft.Office.Interop.Excel.Range cells in chartRange.Cells)
                    {
                        cells.BorderAround2();
                    }
                    #endregion
                    try
                    {
                        sheet.Columns.AutoFit(); // autofit
                        string reportName = inputFileName + " от " + DateTime.Now.ToString("dd.MM.yyyy, HH-mm");
                        workbook.SaveAs(ConfigurationManager.AppSettings["reportFolder"] + reportName + ".xlsx");
                        excel.Workbooks.Close();
                        excel.Quit();
                        Console.ForegroundColor = ConsoleColor.DarkGreen;
                        Console.WriteLine($"Processing file {inputFileName} is completed.");
                        Console.ForegroundColor = color;
                    }
                    catch (System.Runtime.InteropServices.COMException)
                    {
                        foreach (Process pr in prs)
                        {
                            if (pr.ProcessName == "EXCEL")
                            {
                                pr.Kill();
                            }
                        }
                    }
                    //release com objects to fully kill excel process from running in the background
                    Marshal.ReleaseComObject(xlRange);
                    Marshal.ReleaseComObject(xlWorksheet);

                    //close and release
                    xlWorkbook.Close();
                    Marshal.ReleaseComObject(xlWorkbook);

                    //quit and release
                    xlApp.Quit();
                    Marshal.ReleaseComObject(xlApp);

                    //Console.WriteLine("Press \'q\' to quit the console.");
                    //while (Console.Read() != 'q') ;
                }
                //cleanup
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            #region clean all excel processes again just in case
            catch (Exception e)
            {
                foreach (Process pr in prs)
                {
                    if (pr.ProcessName == "EXCEL" && pr.ProcessName == "FastCustom")
                    {
                        pr.Kill();
                    }
                }
                //this will use the "fileLogger" logger from our NLog.config file
                Logger logger = LogManager.GetLogger("fileLogger");

                //add custom message and pass in the exception
                logger.Error(e, "Need fix some problems!");
            }

            #endregion
        }
Пример #39
0
        public string CreatePdfFromExcel(int id = 0)
        {
            try {
                string workbookPath = @"C:\Users\fwang\source\Workspaces\Workspace\RiskNet\Dashboard\DashboardBranch\Presentation\Nop.Web\ExportPdfFiles\Temp";
                var    directory    = new DirectoryInfo(workbookPath);
                if (id != 0)
                {
                    foreach (FileInfo myFile in directory.GetFiles())
                    {
                        try {
                            myFile.Delete();
                        } catch (Exception ex) {
                        }
                    }
                    return("");
                }
                foreach (FileInfo myFile in directory.GetFiles("*.xlsx").OrderByDescending(f => f.LastWriteTime))
                {
                    using (new Impersonator("fwang", "mobius", "Yibing9!")) {
                        Microsoft.Office.Interop.Excel.Application excelApplication;
                        Microsoft.Office.Interop.Excel.Workbook    excelWorkbook;

                        excelApplication = new Microsoft.Office.Interop.Excel.Application();
                        excelApplication.ScreenUpdating = false;
                        excelApplication.DisplayAlerts  = false;

                        excelWorkbook = excelApplication.Workbooks.Open(myFile.FullName);

                        Microsoft.Office.Interop.Excel.Worksheet ws = excelWorkbook.Sheets[1];

                        ws.PageSetup.Zoom           = false;
                        ws.PageSetup.FitToPagesTall = false;
                        ws.PageSetup.FitToPagesWide = 1;
                        try {
                            excelWorkbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, myFile.FullName + Guid.NewGuid().ToString() + ".pdf");
                        } catch (System.Exception ex) {
                        } finally {
                            excelWorkbook.Close();
                            excelApplication.Quit();

                            excelApplication = null;
                            excelWorkbook    = null;
                        }
                    }
                }
            }catch (Exception ex) {
                string s = ex.Message + " " + ex.StackTrace;
                return(s);
            }
            return("test");

            MySimpleClass o  = new MySimpleClass();
            MySimpleClass o2 = new MySimpleClass();

            o.StringVal1 = "Test test";
            o.StringVal2 = "test2";
            try {
                this.AutoMapper(o, o2);
            }catch (Exception ex) {
                StackTrace st          = new StackTrace(true);
                string     stackIndent = "";
                for (int i = 0; i < st.FrameCount; i++)
                {
                    // Note that at this level, there are four
                    // stack frames, one for each method invocation.
                    StackFrame sf = st.GetFrame(i);
                    Console.WriteLine();
                    Console.WriteLine(stackIndent + " Method: {0}",
                                      sf.GetMethod());
                    Console.WriteLine(stackIndent + " File: {0}",
                                      sf.GetFileName());
                    Console.WriteLine(stackIndent + " Line Number: {0}",
                                      sf.GetFileLineNumber());
                    stackIndent += "  ";
                }
                throw ex;
            }
            return(o2.StringVal1);
        }
Пример #40
0
        private void ExcelCreation(DataSet dtDataByfilter)
        {
            try
            {
                dtDataByfilter.Tables[0].TableName = "Target";
                // dtDataByfilter.Tables[1].TableName = "Incentive Plan";
                // dtDataByfilter.Tables[2].TableName = "Report";
                string myServerPath = Server.MapPath("~/ExcelTemplate/SalePurchase-Template.xlsx");

                //var excel = new Microsoft.Office.Interop.Excel.Application();
                //var workbook = excel.Workbooks.Add(true);

                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                //excel.Visible = true;
                //excel.Worksheets.Add(dtDataByfilter.Tables[0], "Target1");
                Microsoft.Office.Interop.Excel.Workbook  wbExiting = excel.Workbooks.Open(myServerPath);
                Microsoft.Office.Interop.Excel.Worksheet sh        = wbExiting.Sheets[1];
                if (dtDataByfilter != null && dtDataByfilter.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < dtDataByfilter.Tables[0].Rows.Count; i++)
                    {
                        sh.Cells[i + 2, 1] = dtDataByfilter.Tables[0].Rows[i][0];
                        sh.Cells[i + 2, 2] = dtDataByfilter.Tables[0].Rows[i][1];
                        sh.Cells[i + 2, 3] = dtDataByfilter.Tables[0].Rows[i][2];
                        sh.Cells[i + 2, 4] = dtDataByfilter.Tables[0].Rows[i][3];
                        sh.Cells[i + 2, 5] = dtDataByfilter.Tables[0].Rows[i][4];
                        sh.Cells[i + 2, 6] = dtDataByfilter.Tables[0].Rows[i][5];
                        sh.Cells[i + 2, 7] = dtDataByfilter.Tables[0].Rows[i][6];
                        sh.Cells[i + 2, 8] = dtDataByfilter.Tables[0].Rows[i][7];
                        sh.Cells[i + 2, 9] = dtDataByfilter.Tables[0].Rows[i][8];
                    }
                }


                string strUniqueFileName = string.Empty;
                strUniqueFileName = Session["USERID"].ToString();

                string   myServerPath1 = Server.MapPath("~/ExcelTemplate/" + strUniqueFileName + ".xlsx");
                FileInfo file          = new FileInfo(myServerPath1);
                if (file.Exists)
                {
                    file.Delete();
                }

                wbExiting.SaveAs(Server.MapPath("~/ExcelTemplate/" + strUniqueFileName + ".xlsx"), Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
                                 Type.Missing, Type.Missing);
                excel.DisplayAlerts = false;
                wbExiting.Close(false, Type.Missing, Type.Missing);
                excel.Quit();

                FileInfo file1 = new FileInfo(myServerPath1);

                if (file1.Exists)
                {
                    Response.Clear();
                    Response.ClearHeaders();
                    Response.ClearContent();
                    Response.AddHeader("content-disposition", "attachment; filename=SalePurchase.xlsx");
                    Response.AddHeader("Content-Type", "application/Excel");
                    Response.ContentType = "application/vnd.xls";
                    Response.AddHeader("Content-Length", file1.Length.ToString());
                    Response.WriteFile(file1.FullName);
                    // file.Delete();
                    Response.End();
                }
                else
                {
                    Response.Write("This file does not exist.");
                }
            }
            catch (Exception ex)
            {
                ErrorSignal.FromCurrentContext().Raise(ex);
            }
        }
Пример #41
0
        protected List <lhh_LoginList_info> getlist()
        {
            try
            {
                Local_IP();
                InfoList = new List <lhh_LoginList_info>();
                System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                string[] ob = Regex.Split(ZFCEPath, @"\\", RegexOptions.IgnoreCase);
                //string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "辣皇后\\user control.xlsx");

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

                Microsoft.Office.Interop.Excel.Workbook analyWK = excelApp.Workbooks.Open(ZFCEPath, Type.Missing, true, Type.Missing,
                                                                                          "htc", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                                                          Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                Microsoft.Office.Interop.Excel.Worksheet WS = (Microsoft.Office.Interop.Excel.Worksheet)analyWK.Worksheets["配置"];
                Microsoft.Office.Interop.Excel.Range     rng;
                rng = WS.get_Range(WS.Cells[2, 7], WS.Cells[WS.UsedRange.Rows.Count, 10]);
                int rowCount = WS.UsedRange.Rows.Count - 2;
                object[,] o = new object[1, 1];
                o           = (object[, ])rng.Value2;
                clsCommHelp.CloseExcel(excelApp, analyWK);

                for (int i = 1; i <= rowCount; i++)
                {
                    //  bgWorker.ReportProgress(0, "读入数据中  :  " + i.ToString() + "/" + rowCount.ToString());
                    lhh_LoginList_info temp = new lhh_LoginList_info();

                    #region 基础信息

                    if (o[i, 1] != null)
                    {
                        temp.loginid = o[i, 1].ToString().Trim();
                    }
                    if (o[i, 2] != null)
                    {
                        temp.pwd = o[i, 2].ToString().Trim();
                    }
                    if (o[i, 3] != null)
                    {
                        temp.startTime = o[i, 3].ToString().Trim();
                    }
                    if (o[i, 4] != null)
                    {
                        temp.endTime = Convert.ToDateTime(o[i, 4].ToString());
                    }



                    #endregion

                    InfoList.Add(temp);
                }



                return(InfoList);
            }
            catch (Exception ex)
            {
                MessageBox.Show("表格存在异常,请参照使用说明修改:" + ex.Message);

                throw ex;
            }
        }
Пример #42
0
        private void ReadDataForAccount(string SelectedAccount)
        {
            try
            {
                List <int> rownumbers = new List <int>();
                //specify the file name where its actually exist
                var    readOutputfile = Properties.Settings.Default.OutputFolderPath;
                string filepath       = string.Empty;
                int    rows_count     = 0;
                Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
                try
                {
                    DirectoryInfo dirInfo = new DirectoryInfo(readOutputfile + SelectedAccount + "//");
                    var           files   = dirInfo.GetFiles();
                    int           counter = 1;
                    foreach (FileInfo fileInfo in files)
                    {
                        filepath = readOutputfile + SelectedAccount + "\\" + fileInfo.Name;
                        Microsoft.Office.Interop.Excel.Workbook WB = oExcel.Workbooks.Open(filepath);
                        try
                        {
                            // statement get the workbookname
                            string ExcelWorkbookname = WB.Name;
                            // statement get the worksheet count
                            int worksheetcount = WB.Worksheets.Count;

                            Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)WB.Worksheets[1];

                            // statement get the firstworksheetname
                            Microsoft.Office.Interop.Excel.Range range = wks.UsedRange;

                            if (counter == 1)
                            {
                                rows_count = range.Rows.Count;
                            }

                            string firstworksheetname = wks.Name;
                            if (counter == 1)
                            {
                                CreateColumnsForGrid(null);
                                dataGridView1.Rows.Add(rows_count - 17);
                                //statement get the first cell value
                                for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                                {
                                    dataGridView1.Columns[i - 1].HeaderText = wks.Cells[1, i].Text;
                                }
                            }
                            if (counter > 1)
                            {
                                dataGridView1.Columns.Add(fileInfo.Name.Replace(fileInfo.Extension, ""), fileInfo.Name.Replace(fileInfo.Extension, ""));
                            }
                            // storing Each row and column value to excel sheet
                            if (counter == 1)
                            {
                                for (int i = 0; i < rows_count - 1; i++)
                                {
                                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                                    {
                                        dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, j + 1].Text;
                                        decimal result;
                                        if (decimal.TryParse(dataGridView1.Rows[i].Cells[j].Value.ToString(), out result))
                                        {
                                            if (!rownumbers.Exists(a => a == i))
                                            {
                                                rownumbers.Add(i);
                                            }
                                        }
                                    }
                                }
                            }
                            else
                            {
                                for (int i = 0; i < rows_count - 1; i++)
                                {
                                    for (int j = dataGridView1.Columns.Count - 1; j < dataGridView1.Columns.Count; j++)
                                    {
                                        dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, 4].Text;
                                    }
                                }
                            }
                            counter++;
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Issue occured.." + e.Message);
                        }
                        finally
                        {
                            if (WB != null)
                            {
                                System.Runtime.InteropServices.Marshal.ReleaseComObject(WB);
                            }
                        }
                    }
                    DisplayAverageData(rownumbers);
                    MessageBox.Show("Data read successfully");
                }
                catch (Exception ex)
                {
                    //    MessageBox.Show("Something went wrong.." + ex.Message);
                }
                finally
                {
                    //oExcel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("There was a problem reading the excel sheet, please try again in sometime! " + ex.Message);
            }
        }
Пример #43
0
        private void ReadDataForAccountAndYear(string SelectedAccount, string selectedYear)
        {
            try
            {
                var           readOutputfile = Properties.Settings.Default.OutputFolderPath;
                DirectoryInfo dirInfo        = new DirectoryInfo(readOutputfile + SelectedAccount + "//" + selectedYear + "//");
                if (dirInfo.Exists)
                {
                    var directs = dirInfo.GetDirectories();
                    if (directs.Length > 0)
                    {
                        var messageResult = MessageBox.Show("Do you like to load all months data.", "Month Data Alert", MessageBoxButtons.YesNo);
                        if (messageResult == System.Windows.Forms.DialogResult.Yes)
                        {
                            int counter = 1;
                            foreach (var directory in directs)
                            {
                                List <int> rownumbers = new List <int>();
                                //specify the file name where its actually exist

                                string filepath   = string.Empty;
                                int    rows_count = 0;

                                try
                                {
                                    var files = directory.GetFiles();

                                    foreach (FileInfo fileInfo in files)
                                    {
                                        if (!fileInfo.Name.StartsWith("~"))
                                        {
                                            filepath = directory.FullName + "\\" + fileInfo.Name;
                                            Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
                                            Microsoft.Office.Interop.Excel.Workbook    WB     = oExcel.Workbooks.Open(filepath);
                                            // statement get the workbookname
                                            string ExcelWorkbookname = WB.Name;
                                            // statement get the worksheet count
                                            int worksheetcount = WB.Worksheets.Count;
                                            Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)WB.Worksheets[1];
                                            try
                                            {
                                                // statement get the firstworksheetname
                                                Microsoft.Office.Interop.Excel.Range range = wks.UsedRange;
                                                rows_count = range.Rows.Count;

                                                string firstworksheetname = wks.Name;
                                                if (counter == 1)
                                                {
                                                    CreateColumnsForGrid(null);
                                                    dataGridView1.Rows.Add(rows_count - 17);
                                                    //statement get the first cell value
                                                    for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                                                    {
                                                        dataGridView1.Columns[i - 1].HeaderText = wks.Cells[1, i].Text;
                                                    }
                                                }
                                                if (counter > 1)
                                                {
                                                    dataGridView1.Columns.Add(fileInfo.Name.Replace(fileInfo.Extension, ""), fileInfo.Name.Replace(fileInfo.Extension, ""));
                                                }
                                                // storing Each row and column value to excel sheet
                                                if (counter == 1)
                                                {
                                                    for (int i = 0; i < rows_count - 1; i++)
                                                    {
                                                        for (int j = 0; j < dataGridView1.Columns.Count; j++)
                                                        {
                                                            dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, j + 1].Text;
                                                            decimal result;
                                                            if (decimal.TryParse(dataGridView1.Rows[i].Cells[j].Value.ToString(), out result))
                                                            {
                                                                if (!rownumbers.Exists(a => a == i))
                                                                {
                                                                    rownumbers.Add(i);
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                                else
                                                {
                                                    for (int i = 0; i < rows_count - 1; i++)
                                                    {
                                                        for (int j = dataGridView1.Columns.Count - 1; j < dataGridView1.Columns.Count; j++)
                                                        {
                                                            dataGridView1.Rows[i].Cells[j].Value = wks.Cells[i + 2, 4].Text;
                                                        }
                                                    }
                                                }
                                                counter++;
                                            }
                                            catch (Exception e)
                                            {
                                                MessageBox.Show("Issue occured.." + e.Message);
                                            }
                                            finally
                                            {
                                                if (wks != null)
                                                {
                                                    System.Runtime.InteropServices.Marshal.ReleaseComObject(wks);
                                                    wks = null;
                                                }
                                                if (WB != null)
                                                {
                                                    WB.Close(false, null, null);
                                                    System.Runtime.InteropServices.Marshal.ReleaseComObject(WB);
                                                    WB = null;
                                                }

                                                if (oExcel != null)
                                                {
                                                    oExcel.Quit();
                                                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
                                                    oExcel = null;
                                                }
                                                GC.Collect();
                                                GC.WaitForPendingFinalizers();
                                                GC.Collect();
                                            }
                                        }
                                    }
                                    DisplayAverageData(rownumbers);
                                }
                                catch (Exception ex)
                                {
                                    //    MessageBox.Show("Something went wrong.." + ex.Message);
                                }
                            }
                            MessageBox.Show("Data read successfully");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("There was a problem reading the excel sheet, please try again in sometime! " + ex.Message);
            }
        }
Пример #44
0
        private void ExportToExcel(DataTable DataTable, string ExcelFilePath = null)
        {
            try
            {
                int ColumnsCount;

                if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
                {
                    throw new Exception("ExportToExcel: Null or empty input table!\n");
                }

                // load excel, and create a new workbook
                Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
                Excel.Workbooks.Add();

                // single worksheet
                Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;


                //object[] Header = new object[ColumnsCount];

                //// column headings
                //for (int i = 0; i < ColumnsCount; i++)
                //    Header[i] = DataTable.Columns[i].ColumnName;

                //Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)
                //    (Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
                //HeaderRange.Value = Header;
                //HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
                //HeaderRange.Font.Bold = true;

                // DataCells
                int RowsCount = DataTable.Rows.Count;
                object[,] Cells = new object[RowsCount, ColumnsCount];

                for (int j = 0; j < RowsCount; j++)
                {
                    for (int i = 0; i < ColumnsCount; i++)
                    {
                        Cells[j, i] = DataTable.Rows[j][i];
                    }
                }

                Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]),
                                    (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount, ColumnsCount])).Value = Cells;
                //Worksheet.Columns.AutoFit();//Extend column width if necessary
                // check fielpath
                if (ExcelFilePath != null && ExcelFilePath != "")
                {
                    try
                    {
                        Worksheet.Cells.NumberFormat = "@";//Type of all cells set to text

                        Worksheet.SaveAs(ExcelFilePath);
                        Excel.Quit();
                        MessageBox.Show("Excel file saved!");
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                                            + ex.Message);
                    }
                }
                else    // no filepath is given
                {
                    Excel.Visible = true;
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.ToString());
                throw new Exception("ExportToExcel: \n" + ex.Message);
            }
        }
Пример #45
0
        public Result Execute(ExternalCommandData commandData, ref string message, ElementSet elements)
        {
            Microsoft.Office.Interop.Excel.Application xlApp      = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook    xlWorkbook = xlApp.Workbooks.Open(@"D:\WerkStudent\Mail\Gen_Open_Fin_kel");

            Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1];
            Microsoft.Office.Interop.Excel.Range     xlRange     = xlWorksheet.UsedRange;

            int    rowCount = xlRange.Rows.Count;
            int    colCount = xlRange.Columns.Count;
            double cF       = 1 / 0.3048;
            //Get UI document
            UIDocument uidoc = commandData.Application.ActiveUIDocument;

            //Get document
            Document doc = uidoc.Document;

            string Level_1 = (String)xlRange.Cells[4, "A"].Value2;
            //Create levels
            Level Level1 = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Levels)
                           .WhereElementIsNotElementType().Cast <Level>().First(x => x.Name == Level_1);
            Level Level2 = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Levels)
                           .WhereElementIsNotElementType().Cast <Level>().First(x => x.Name == "Level 2");

            string ty1 = (String)xlRange.Cells[3, "A"].Value2;

            var FloorType = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_Floors)
                            .WhereElementIsElementType().Cast <FloorType>().First(x => x.Name == ty1);
            //var Floor = new FilteredElementCollector(doc).OfCategory(BuiltInCategory.OST_FloorsCut)
            //   .WhereElementIsElementType().Cast<Floor>().First(x => x.Name == ty1);

            int    points   = Convert.ToInt32(xlRange.Cells[2, "C"].value2);
            string keller   = (String)xlRange.Cells[2, "F"].value2;
            int    geschoss = Convert.ToInt32(xlRange.Cells[3, "F"].value2);

            List <XYZ> pointlistkf  = new List <XYZ>();
            List <XYZ> pointlistgf  = new List <XYZ>();
            List <XYZ> pointlistufs = new List <XYZ>();

            for (int i = 5; i <= points + 4; i++)
            {
                if (keller == "Ja")
                {
                    XYZ ptskf = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, -3.06 * cF);
                    pointlistkf.Add(ptskf);
                    XYZ ptsgf = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, 0 * cF);
                    pointlistgf.Add(ptsgf);
                    for (int j = 0; j < geschoss; j++)
                    {
                        XYZ ptsufs = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, (xlRange.Cells[i, "D"].value2 + (j * 3.06)) * cF);
                        pointlistufs.Add(ptsufs);
                    }
                }
                else
                {
                    XYZ ptsgf = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, 0 * cF);
                    pointlistgf.Add(ptsgf);
                    for (int j = 0; j < geschoss; j++)
                    {
                        XYZ ptsufs = new XYZ(xlRange.Cells[i, "B"].value2 * cF, xlRange.Cells[i, "C"].value2 * cF, (xlRange.Cells[i, "D"].value2 + (j * 3.06)) * cF);
                        pointlistufs.Add(ptsufs);
                    }
                }
            }

            List <Line> concurveskf = new List <Line>();
            List <Line> concurvesgf = new List <Line>();


            for (int i = 0; i < pointlistkf.Count; i++)
            {
                if (i < pointlistkf.Count - 1)
                {
                    Line Alllines = Line.CreateBound(pointlistkf.ElementAt(i), pointlistkf.ElementAt(i + 1));
                    concurveskf.Add(Alllines);
                }
                else
                {
                    Line Alllines = Line.CreateBound(pointlistkf.ElementAt(pointlistkf.Count - 1), pointlistkf.ElementAt(0));
                    concurveskf.Add(Alllines);
                }
            }

            for (int i = 0; i < pointlistgf.Count; i++)
            {
                if (i < pointlistgf.Count - 1)
                {
                    Line Alllines = Line.CreateBound(pointlistgf.ElementAt(i), pointlistgf.ElementAt(i + 1));
                    concurvesgf.Add(Alllines);
                }
                else
                {
                    Line Alllines = Line.CreateBound(pointlistgf.ElementAt(pointlistgf.Count - 1), pointlistgf.ElementAt(0));
                    concurvesgf.Add(Alllines);
                }
            }

            List <Line> concurvesuf  = new List <Line>();
            List <Line> concurvesuf1 = new List <Line>();
            List <Line> concurvesuf2 = new List <Line>();

            switch (geschoss)
            {
            case 1:
                break;

            case 2:
                for (int i = 0; i < 4; i++)
                {
                    if (i < 3)
                    {
                        Line Alllines = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(i + 1));
                        concurvesuf.Add(Alllines);
                    }
                    else
                    {
                        Line Alllines = Line.CreateBound(pointlistufs.ElementAt(pointlistufs.Count - 1), pointlistufs.ElementAt(0));
                        concurvesuf.Add(Alllines);
                    }
                }
                break;

            case 3:
                for (int i = 0; i < 4; i++)
                {
                    if (i < 3)
                    {
                        Line Alllines  = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(i + 1));
                        Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 5));
                        concurvesuf.Add(Alllines);
                        concurvesuf1.Add(Alllines1);
                    }
                    else
                    {
                        Line Alllines  = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(0));
                        Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 1));
                        concurvesuf.Add(Alllines);
                        concurvesuf1.Add(Alllines1);
                    }
                }
                break;

            case 4:
                for (int i = 0; i < 4; i++)
                {
                    if (i < 3)
                    {
                        Line Alllines  = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(i + 1));
                        Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 5));
                        Line Alllines2 = Line.CreateBound(pointlistufs.ElementAt(i + 8), pointlistufs.ElementAt(i + 9));
                        concurvesuf.Add(Alllines);
                        concurvesuf1.Add(Alllines1);
                        concurvesuf2.Add(Alllines2);
                    }
                    else
                    {
                        Line Alllines  = Line.CreateBound(pointlistufs.ElementAt(i), pointlistufs.ElementAt(0));
                        Line Alllines1 = Line.CreateBound(pointlistufs.ElementAt(i + 4), pointlistufs.ElementAt(i + 1));
                        Line Alllines2 = Line.CreateBound(pointlistufs.ElementAt(i + 8), pointlistufs.ElementAt(i + 5));
                        concurvesuf.Add(Alllines);
                        concurvesuf1.Add(Alllines1);
                        concurvesuf2.Add(Alllines2);
                    }
                }
                break;
            }

            List <Curve> licurvekf = new List <Curve>();
            List <Curve> licurvegf = new List <Curve>();

            for (int i = 0; i < concurveskf.Count; i++)
            {
                licurvekf.Add(concurveskf.ElementAt(i));
            }
            for (int i = 0; i < concurvesgf.Count; i++)
            {
                licurvegf.Add(concurvesgf.ElementAt(i));
            }

            List <Curve> licurveuf = new List <Curve>();

            for (int i = 0; i < concurvesuf.Count; i++)
            {
                licurveuf.Add(concurvesuf.ElementAt(i));
            }
            List <Curve> licurveuf1 = new List <Curve>();

            for (int i = 0; i < concurvesuf1.Count; i++)
            {
                licurveuf1.Add(concurvesuf1.ElementAt(i));
            }
            List <Curve> licurveuf2 = new List <Curve>();

            for (int i = 0; i < concurvesuf2.Count; i++)
            {
                licurveuf2.Add(concurvesuf2.ElementAt(i));
            }
            // Making a curve in a loop
            List <CurveLoop> loops    = new List <CurveLoop>();
            CurveLoop        crvloop0 = CurveLoop.Create(licurvekf);

            loops.Add(crvloop0);
            CurveLoop crvloop = CurveLoop.Create(licurvegf);

            loops.Add(crvloop);
            CurveLoop crvloop1 = CurveLoop.Create(licurveuf);

            loops.Add(crvloop1);
            CurveLoop crvloop2 = CurveLoop.Create(licurveuf1);

            loops.Add(crvloop2);
            CurveLoop crvloop3 = CurveLoop.Create(licurveuf2);

            loops.Add(crvloop3);

            List <CurveArray> curArri = new List <CurveArray>();

            for (int i = 0; i < loops.Count; i++)
            {
                CurveArray curArr = new CurveArray();
                foreach (Curve c in loops[i])
                {
                    //Put the curves to curve array
                    curArr.Append(c);
                }
                curArri.Add(curArr);
            }


            //
            List <XYZ> opointlist = new List <XYZ>();

            for (int i = 3; i <= 6; i++)
            {
                XYZ opoints = new XYZ(xlRange.Cells[i, "H"].value2 * cF, xlRange.Cells[i, "I"].value2 * cF, xlRange.Cells[i, "J"].value2 * cF);
                opointlist.Add(opoints);
            }
            List <Line> opline = new List <Line>();

            for (int i = 0; i < opointlist.Count; i++)
            {
                if (i < opointlist.Count - 1)
                {
                    Line oline = Line.CreateBound(opointlist.ElementAt(i), opointlist.ElementAt(i + 1));
                    opline.Add(oline);
                }
                else
                {
                    Line oline = Line.CreateBound(opointlist.ElementAt(3), opointlist.ElementAt(0));
                    opline.Add(oline);
                }
            }
            List <Curve> ocurve = new List <Curve>();

            for (int i = 0; i < opline.Count; i++)
            {
                ocurve.Add(opline.ElementAt(i));
            }
            CurveLoop  crvvloopopen = CurveLoop.Create(ocurve);
            CurveArray curvopen     = new CurveArray();

            foreach (Curve co in crvvloopopen)
            {
                //Put the curves to curve array
                curvopen.Append(co);
            }
            //
            try
            {
                Floor        FL;
                List <Floor> stories = new List <Floor>();

                if (keller == "Ja")
                {
                    for (int i = 0; i <= geschoss; i++)
                    {
                        using (Transaction trans = new Transaction(doc, "Bungalow Floors"))
                        {
                            trans.Start();
                            FL = doc.Create.NewFloor(curArri[i], FloorType, Level1, false);
                            stories.Add(FL);
                            trans.Commit();
                        }
                    }
                }
                else
                {
                    for (int i = 0; i < geschoss; i++)
                    {
                        using (Transaction trans = new Transaction(doc, "Bungalow Floors"))
                        {
                            trans.Start();
                            FL = doc.Create.NewFloor(curArri[i], FloorType, Level1, false);
                            stories.Add(FL);
                            trans.Commit();
                        }
                    }
                }
                for (int i = 1; i < stories.Count; i++)
                {
                    using (Transaction trans1 = new Transaction(doc, "Bung open"))
                    {
                        trans1.Start();
                        //var Open = doc.Create.NewFloor(curArr1, FloorType, Level2, false);
                        doc.Create.NewOpening(stories[i], curvopen, true);
                        trans1.Commit();
                    }
                }

                return(Result.Succeeded);
            }

            catch (Exception e)
            {
                message = e.Message;
                return(Result.Failed);
            }
        }
        private void Export(DataTable dt, string sheetName, string title)
        {
            //Tạo các đối tượng Excel
            Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks   oBooks;
            Microsoft.Office.Interop.Excel.Sheets      oSheets;
            Microsoft.Office.Interop.Excel.Workbook    oBook;
            Microsoft.Office.Interop.Excel.Worksheet   oSheet;

            //Tạo mới một Excel WorkBook
            oExcel.Visible       = true;
            oExcel.DisplayAlerts = false;
            oExcel.Application.SheetsInNewWorkbook = 1;
            oBooks = oExcel.Workbooks;

            oBook       = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
            oSheets     = oBook.Worksheets;
            oSheet      = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
            oSheet.Name = sheetName;
            try
            {
                // Tạo phần đầu nếu muốn
                Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "O1");
                head.MergeCells          = true;
                head.Value2              = title;
                head.Font.Bold           = true;
                head.Font.Name           = "Tahoma";
                head.Font.Size           = "18";
                head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                // Tạo tiêu đề cột
                Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");
                cl1.Value2      = "Mã KH";
                cl1.ColumnWidth = 10.5;

                Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");
                cl2.Value2      = "Họ KH";
                cl2.ColumnWidth = 20.0;

                Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");
                cl3.Value2      = "Tên KH";
                cl3.ColumnWidth = 20.0;

                Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3");
                cl4.Value2      = "Giới tính";
                cl4.ColumnWidth = 10.0;

                Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("E3", "E3");
                cl5.Value2      = "Ngày sinh";
                cl5.ColumnWidth = 15.0;

                Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("F3", "F3");
                cl6.Value2      = "Điện thoại";
                cl6.ColumnWidth = 15.0;

                Microsoft.Office.Interop.Excel.Range cl7 = oSheet.get_Range("G3", "G3");
                cl7.Value2      = "Địa chỉ";
                cl7.ColumnWidth = 25.0;

                Microsoft.Office.Interop.Excel.Range cl8 = oSheet.get_Range("H3", "H3");
                cl8.Value2      = "CMND";
                cl8.ColumnWidth = 15.0;

                Microsoft.Office.Interop.Excel.Range cl9 = oSheet.get_Range("I3", "I3");
                cl9.Value2      = "Ngày mua";
                cl9.ColumnWidth = 15;

                Microsoft.Office.Interop.Excel.Range cl10 = oSheet.get_Range("J3", "J3");
                cl10.Value2      = "Sổ BH";
                cl10.ColumnWidth = 10.0;

                Microsoft.Office.Interop.Excel.Range cl11 = oSheet.get_Range("K3", "K3");
                cl11.Value2      = "Tên xe";
                cl11.ColumnWidth = 15.0;

                Microsoft.Office.Interop.Excel.Range cl12 = oSheet.get_Range("L3", "L3");
                cl12.Value2      = "Biển số";
                cl12.ColumnWidth = 15.0;

                Microsoft.Office.Interop.Excel.Range cl13 = oSheet.get_Range("M3", "M3");
                cl13.Value2      = "Số khung";
                cl13.ColumnWidth = 15.0;

                Microsoft.Office.Interop.Excel.Range cl14 = oSheet.get_Range("N3", "N3");
                cl14.Value2      = "Số máy";
                cl14.ColumnWidth = 15.0;

                Microsoft.Office.Interop.Excel.Range cl15 = oSheet.get_Range("O3", "O3");
                cl15.Value2      = "Loại KH";
                cl15.ColumnWidth = 10.0;

                Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "O3");
                rowHead.Font.Bold = true;

                // Kẻ viền
                rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
                // Thiết lập màu nền
                rowHead.Interior.ColorIndex = 15;
                rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

                // Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
                // vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
                object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];

                //Chuyển dữ liệu từ DataTable vào mảng đối tượng
                for (int r = 0; r < dt.Rows.Count; r++)
                {
                    DataRow dr = dt.Rows[r];
                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        arr[r, c] = dr[c];
                    }
                }

                //Thiết lập vùng điền dữ liệu
                int rowStart    = 4;
                int columnStart = 1;

                int rowEnd    = rowStart + dt.Rows.Count - 1;
                int columnEnd = dt.Columns.Count;

                // Ô bắt đầu điền dữ liệu
                Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];
                // Ô kết thúc điền dữ liệu
                Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];
                // Lấy về vùng điền dữ liệu
                Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);

                //Điền dữ liệu vào vùng đã thiết lập
                range.Value2 = arr;

                // Kẻ viền
                range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
                // Căn giữa cột STT
                Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];
                Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);
                oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                MessageBox.Show(errorMessage, "Error");
            }
        }
Пример #47
0
        private void btnExportXL_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                dt = ((DataView)dgVariationReport.ItemsSource).ToTable();
                if (dt.Rows.Count > 0)
                {
                    Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();
                    dlg.InitialDirectory = Environment.CurrentDirectory;
                    dlg.Title            = "Variation Report";
                    dlg.DefaultExt       = ".xlsx";
                    dlg.Filter           = "XL files|*.xls;*.xlsx|All files|*.*";
                    dlg.FileName         = "VariationReport";
                    if (dlg.ShowDialog() == true)
                    {
                        Microsoft.Office.Interop.Excel._Application app       = new Microsoft.Office.Interop.Excel.Application();
                        Microsoft.Office.Interop.Excel._Workbook    workbook  = app.Workbooks.Add(Type.Missing);
                        Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;
                        app.Visible = true;
                        worksheet   = workbook.Sheets["Sheet1"];

                        worksheet      = workbook.ActiveSheet;
                        worksheet.Name = "Variation Report";

                        worksheet.Cells[3, 4]           = "VARIATION REPORT ";
                        worksheet.Cells[3, 4].Font.Bold = true;
                        for (int i = 1; i < dt.Columns.Count + 1; i++)
                        {
                            if (i == 3)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Member", dtpFromDate.SelectedDate);
                            }
                            else if (i == 4)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Amount", dtpFromDate.SelectedDate);
                            }
                            else if (i == 5)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Member", dtpFromDate.SelectedDate);
                            }
                            else if (i == 6)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Amount", dtpFromDate.SelectedDate);
                            }
                            else if (i == 7)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Member", dtpFromDate.SelectedDate);
                            }
                            else if (i == 8)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Amount", dtpFromDate.SelectedDate);
                            }
                            else if (i == 9)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Member", dtpToDate.SelectedDate);
                            }
                            else if (i == 10)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} A Amount", dtpToDate.SelectedDate);
                            }
                            else if (i == 11)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Member", dtpToDate.SelectedDate);
                            }
                            else if (i == 12)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} S Amount", dtpToDate.SelectedDate);
                            }
                            else if (i == 13)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Member", dtpToDate.SelectedDate);
                            }
                            else if (i == 14)
                            {
                                worksheet.Cells[5, i + 2] = string.Format("{0:MMM} Tot Amount", dtpToDate.SelectedDate);
                            }
                            else if (i != 1)
                            {
                                worksheet.Cells[5, i + 2] = dt.Columns[i - 1].ColumnName;
                            }
                            worksheet.Cells[5, i + 2].Font.Bold = true;
                        }

                        for (int i = 0; i < dt.Rows.Count - 1; i++)
                        {
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                if (j != 0)
                                {
                                    worksheet.Cells[i + 6, j + 3] = dt.Rows[i][j].ToString();
                                }
                            }
                        }

                        workbook.SaveAs(dlg.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        app.Quit();
                        MessageBox.Show("Exported Sucessfully !", "Sucess");
                    }
                }
                else
                {
                    MessageBox.Show("No Data Found !", "Empty");
                }
            }
            catch (Exception ex)
            {
                ExceptionLogging.SendErrorToText(ex);
            }
        }
Пример #48
0
        public void Export(DataTable dt, string sheetName, string title)
        {
            //Tạo các đối tượng Excel

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

            Microsoft.Office.Interop.Excel.Workbooks oBooks;

            Microsoft.Office.Interop.Excel.Sheets oSheets;

            Microsoft.Office.Interop.Excel.Workbook oBook;

            Microsoft.Office.Interop.Excel.Worksheet oSheet;

            //Tạo mới một Excel WorkBook

            oExcel.Visible = true;

            oExcel.DisplayAlerts = false;

            oExcel.Application.SheetsInNewWorkbook = 1;

            oBooks = oExcel.Workbooks;

            oBook = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));

            oSheets = oBook.Worksheets;

            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);

            oSheet.Name = sheetName;

            // Tạo phần đầu

            Microsoft.Office.Interop.Excel.Range head = oSheet.get_Range("A1", "H1");

            head.MergeCells = true;

            head.Value2 = title;

            head.Font.Bold = true;

            head.Font.Name = "Tahoma";

            head.Font.Size = "18";

            head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

            // Tạo tiêu đề cột

            Microsoft.Office.Interop.Excel.Range cl1 = oSheet.get_Range("A3", "A3");

            cl1.Value2 = "Mã nhân viên";

            cl1.ColumnWidth = 10.5;

            Microsoft.Office.Interop.Excel.Range cl2 = oSheet.get_Range("B3", "B3");

            cl2.Value2 = "Tên Nhân viên";

            cl2.ColumnWidth = 25.0;

            Microsoft.Office.Interop.Excel.Range cl3 = oSheet.get_Range("C3", "C3");

            cl3.Value2 = "Giới tính";

            cl3.ColumnWidth = 10.0;

            Microsoft.Office.Interop.Excel.Range cl4 = oSheet.get_Range("D3", "D3");

            cl4.Value2 = "Ngày sinh";

            cl4.ColumnWidth = 13.0;

            Microsoft.Office.Interop.Excel.Range cl5 = oSheet.get_Range("E3", "E3");

            cl5.Value2 = "Mail";

            cl5.ColumnWidth = 25.0;

            Microsoft.Office.Interop.Excel.Range cl6 = oSheet.get_Range("F3", "F3");

            cl6.Value2 = "Số điện thoại";

            cl6.ColumnWidth = 20.0;

            Microsoft.Office.Interop.Excel.Range cl7 = oSheet.get_Range("G3", "G3");

            cl7.Value2 = "Địa chỉ";

            cl7.ColumnWidth = 30.0;

            Microsoft.Office.Interop.Excel.Range cl8 = oSheet.get_Range("H3", "H3");

            cl8.Value2 = "Vị trí";

            cl8.ColumnWidth = 13.0;

            Microsoft.Office.Interop.Excel.Range rowHead = oSheet.get_Range("A3", "H3");

            rowHead.Font.Bold = true;

            // Kẻ viền

            rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;

            // Thiết lập màu nền

            rowHead.Interior.ColorIndex = 15;

            rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

            // Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,

            // vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.

            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];

            //Chuyển dữ liệu từ DataTable vào mảng đối tượng

            for (int r = 0; r < dt.Rows.Count; r++)

            {
                DataRow dr = dt.Rows[r];

                for (int c = 0; c < dt.Columns.Count; c++)

                {
                    string tmp = dr[c].ToString();
                    arr[r, c] = tmp;
                }
            }

            //Thiết lập vùng điền dữ liệu

            int rowStart = 4;

            int columnStart = 1;

            int rowEnd = rowStart + dt.Rows.Count - 1;

            int columnEnd = dt.Columns.Count;

            // Ô bắt đầu điền dữ liệu

            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowStart, columnStart];

            // Ô kết thúc điền dữ liệu

            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnEnd];

            // Lấy về vùng điền dữ liệu

            Microsoft.Office.Interop.Excel.Range range = oSheet.get_Range(c1, c2);

            //Điền dữ liệu vào vùng đã thiết lập

            range.Value2 = arr;

            // Kẻ viền

            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;

            // Căn giữa cột STT

            Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[rowEnd, columnStart];

            Microsoft.Office.Interop.Excel.Range c4 = oSheet.get_Range(c1, c3);

            oSheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;


            //

            releaseObject(oSheet);
            releaseObject(oBook);
            releaseObject(oSheets);
            releaseObject(oBooks);
            releaseObject(oExcel);
        }
Пример #49
0
        public ActionResult RunXlsCampania(string cod_unidad_negocio, string fec_informe, string campania, int batch_desde, int batch_hasta)
        {
            if (Session["cod_unidad_negocio"] != null)
            {
                cod_unidad_negocio = Session["cod_unidad_negocio"].ToString();
            }
            var mensajeError     = string.Empty;
            var tablero          = string.Empty;
            var codUnidadNegocio = cod_unidad_negocio;
            var fecInforme       = fec_informe;
            var batchDesde       = batch_desde;
            var batchHasta       = batch_hasta;

            var resultado      = string.Empty;
            var mensaje        = string.Empty;
            var archivoDestino = string.Empty;
            int pid            = 0;

            //Identificar el archivo a descargar, incluyendo la ruta de acceso.
            var excelFuente  = string.Empty;
            var excelDestino = string.Empty;
            var fileDestino  = string.Empty;

            excelFuente = Server.MapPath("~/xlso/KPI_PI_Campañas.xlsm");

            DateTime?dtfecInforme = null;

            if (!(fec_informe == "" || fec_informe == string.Empty))
            {
                dtfecInforme = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                //Cambiamos el nombre del archivo Destino
                fileDestino = "Campaña" + "_" + campania + "_" + codUnidadNegocio + "_" + dtfecInforme.Value.ToString("yyMMdd") + DateTime.Now.ToString("hhmmsss") + ".xlsx";
            }
            else
            {
                fileDestino = "Campaña" + "_" + campania + "_" + codUnidadNegocio + "_" + batchDesde + "_" + batch_hasta + "_" + DateTime.Now.ToString("hhmmsss") + ".xlsx";
            }

            excelDestino = Server.MapPath("~/xlsd/" + fileDestino);

            try
            {
                var excelApp = new Microsoft.Office.Interop.Excel.Application();
                excelApp.Application.Visible = false;
                excelApp.DisplayAlerts       = false;

                var excelWorkbook = excelApp.Workbooks.Open(Filename: excelFuente, ReadOnly: true);
                GetWindowThreadProcessId(excelApp.Hwnd, out pid);

                excelWorkbook.Sheets["Registro"].Range("A2").Value = codUnidadNegocio;
                excelWorkbook.Sheets["Registro"].Range("B2").Value = campania;

                var fecExcel = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", null).ToString("MM/dd/yyyy");
                excelWorkbook.Sheets["Registro"].Range("C2").Value = fecExcel;
                excelWorkbook.Sheets["Registro"].Range("D2").Value = excelDestino;
                excelWorkbook.Sheets["Registro"].Range("E2").Value = batchDesde;
                excelWorkbook.Sheets["Registro"].Range("F2").Value = batchHasta;

                try
                {
                    mensajeError = excelApp.Run("BtnRegistro_Carga");
                }
                catch (Exception)
                {
                    mensajeError = "ERROR: En la ejecución de Excel";
                }

                if (mensajeError == null)
                {
                    mensajeError = "";
                }

                mensajeError.Replace("'", "´");
                mensajeError.Replace(((char)34).ToString(), "´");
                mensajeError.Replace(((char)13).ToString(), "\r");
                mensajeError.Replace(((char)10).ToString(), "\r");

                //Close Excel
                excelWorkbook.Close(SaveChanges: false);
                excelWorkbook = null;
                excelApp.Quit();
                archivoDestino = excelDestino.Replace(@"\", "/");

                try
                {
                    //procesoExcel.Kill();
                    Process.GetProcessById(pid).Kill();
                }
                catch (Exception)
                {
                }
            }
            catch (Exception ex)
            {
                System.Threading.Thread.Sleep(1000);  //Espera un segundo para intentarlo de nuevo
                mensajeError += " Servicio de Excel en estos momentos se encuentra sin recursos.\r Por favor intentelo mas tarde.";
                try
                {
                    //procesoExcel.Kill();
                    Process.GetProcessById(pid).Kill();
                }
                catch (Exception)
                {
                }
            }

            if (mensajeError == "")
            {
                return(Json(
                           new Response
                {
                    Status = HttpStatusCode.OK,
                    Message = archivoDestino
                },
                           JsonRequestBehavior.AllowGet));
            }

            return(Json(
                       new Response
            {
                Status = HttpStatusCode.BadRequest,
                Message = mensajeError
            },
                       JsonRequestBehavior.AllowGet));
        }
Пример #50
0
        public int print(bool b)
        {
            // 打开一个Excel进程
            Microsoft.Office.Interop.Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            // 利用这个进程打开一个Excel文件
            //Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\CSBag\SOP-MFG-109-R01A 产品内包装记录.xlsx");
            Microsoft.Office.Interop.Excel._Workbook wb = oXL.Workbooks.Open(System.IO.Directory.GetCurrentDirectory() + @"\..\..\xls\PTV\8 SOP-MFG-412-R01A 底封机运行记录");

            // 选择一个Sheet,注意Sheet的序号是从1开始的
            Microsoft.Office.Interop.Excel._Worksheet my = wb.Worksheets[wb.Worksheets.Count];
            // 修改Sheet中某行某列的值
            fill_excel(my, wb);
            //"生产指令-步骤序号- 表序号 /&P"
            my.PageSetup.RightFooter = Instruction + "-" + find_indexofprint().ToString("D3") + " &P/" + wb.ActiveSheet.PageSetup.Pages.Count;  // &P 是页码


            if (b)
            {
                //true->预览
                // 设置该进程是否可见
                oXL.Visible = true;
                // 让这个Sheet为被选中状态
                my.Select();  // oXL.Visible=true 加上这一行  就相当于预览功能
                return(0);
            }
            else
            {
                int  pageCount = 0;
                bool isPrint   = true;
                //false->打印
                try
                {
                    // 设置该进程是否可见
                    //oXL.Visible = false; // oXL.Visible=false 就会直接打印该Sheet
                    // 直接用默认打印机打印该Sheet
                    my.PrintOut();
                }
                catch
                { isPrint = false; }
                finally
                {
                    if (isPrint)
                    {
                        //写日志
                        string log = "=====================================\n";
                        log += DateTime.Now.ToString("yyyy年MM月dd日 hh时mm分ss秒") + "\n" + label角色.Text + ":" + mySystem.Parameter.userName + " 打印文档\n";
                        dt记录.Rows[0]["日志"] = dt记录.Rows[0]["日志"].ToString() + log;

                        bs记录.EndEdit();
                        da记录.Update((DataTable)bs记录.DataSource);
                    }
                    // 关闭文件,false表示不保存
                    pageCount = wb.ActiveSheet.PageSetup.Pages.Count;
                    wb.Close(false);
                    // 关闭Excel进程
                    oXL.Quit();
                    // 释放COM资源
                    Marshal.ReleaseComObject(wb);
                    Marshal.ReleaseComObject(oXL);
                    wb  = null;
                    oXL = null;
                }
                return(pageCount);
            }
        }
Пример #51
0
        public static void ExportXLSX(DataGridView dgv, int[] x, int[] y, string[] vanban)
        {
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            //Khởi tạo WorkBook
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            //Khởi tạo WorkSheet
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            worksheet   = workbook.Sheets["Sheet1"];
            worksheet   = workbook.ActiveSheet;
            app.Visible = true;
            //Đổ dữ liệu vào Sheet

            for (int i = 0; i < vanban.Length; i++)
            {
                worksheet.Cells[x[i], y[i]] = vanban[i];
            }

            int dongbatdau = 7;

            for (int i = 0; i < dgv.Columns.Count; i++)
            {
                worksheet.Cells[dongbatdau, i + 1] = dgv.Columns[i].HeaderText;
            }


            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                for (int j = 0; j < dgv.Columns.Count; j++)
                {
                    worksheet.Cells[i + 1 + dongbatdau, j + 1] = dgv.Rows[i].Cells[j].Value.ToString();
                }
            }
            worksheet.Range["A" + dongbatdau, "G" + dongbatdau].Font.Bold = true; //đậm tiêu đề cột
            worksheet.Range["A1", "J100"].Font.Name = "Times New Roman";
            worksheet.Range["A1", "G2"].Font.Bold   = true;
            worksheet.Range["A2", "G2"].Font.Italic = true;
            worksheet.Range["A2", "A2"].Font.Bold   = false;
            worksheet.Range["C4"].Font.Bold         = true;
            worksheet.Range["C4"].Font.Size         = 18;
            //kẻ viền
            worksheet.Range["A" + (dongbatdau), Convert.ToChar(dgv.Columns.Count + 65 - 1).ToString() + (dongbatdau + dgv.Rows.Count)].Borders.LineStyle = 1;


            int ii = 20;

            worksheet.Range["A1"].ColumnWidth = ii;
            worksheet.Range["B1"].ColumnWidth = ii;
            worksheet.Range["C1"].ColumnWidth = ii;
            worksheet.Range["D1"].ColumnWidth = ii;
            worksheet.Range["E1"].ColumnWidth = ii;
            worksheet.Range["F1"].ColumnWidth = ii;
            worksheet.Range["G1"].ColumnWidth = ii;

            //tính ttoongr
            worksheet.Cells[dongbatdau + dgv.Rows.Count + 1, dgv.Columns.Count] =
                string.Format("=SUM({0}:{1})",
                              Convert.ToChar(dgv.Columns.Count + 64).ToString() + (dongbatdau + 1),
                              Convert.ToChar(dgv.Columns.Count + 64).ToString() + (dongbatdau + dgv.Rows.Count)
                              );
            worksheet.Cells[dongbatdau + dgv.Rows.Count + 1, dgv.Columns.Count - 1] = "Tổng";
        }
Пример #52
0
        private void buttonStart_Click(object sender, EventArgs e)
        {
            if (textBoxPath.Text != "")
            {
                this.label1.Visible      = true;
                this.pictureBox1.Visible = true;
                this.label1.Text         = "正在整理中......";
                this.pictureBox1.Image   = Resources._123;
                List <String> list = Director(textBoxPath.Text);
                foreach (var path in list)
                {
                    CrawlData(path);
                }
            }
            try
            {
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                excel.Visible = true;
                //新增加一个工作簿,Workbook是直接保存,不会弹出保存对话框,加上Application会弹出保存对话框,值为false会报错
                Microsoft.Office.Interop.Excel.Workbook  xBook  = excel.Workbooks.Add(Missing.Value);
                Microsoft.Office.Interop.Excel.Worksheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];

                xSheet.Cells[1, 1] = "ip地址";
                xSheet.Cells[1, 2] = "漏洞名称";
                xSheet.Cells[1, 3] = "详细描述";
                xSheet.Cells[1, 4] = "加固建议";
                xSheet.Cells[1, 5] = "风险端口";
                xSheet.Cells[1, 6] = "危险程度";
                List <string> loopholeInfoDicKey = new List <string>(loopholeInfoDic.Keys);
                for (int i = 0; i < loopholeInfoDicKey.Count; i++)
                {
                    for (int j = 0; j < 6; j++)
                    {
                        if (j == 2)
                        {
                            if (loopholeInfoDic[loopholeInfoDicKey[i]].Count > 4)
                            {
                                xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][4];
                            }
                        }
                        else if (j == 3)
                        {
                            if (loopholeInfoDic[loopholeInfoDicKey[i]].Count > 4)
                            {
                                xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][5];
                            }
                        }
                        else if (j == 4)
                        {
                            xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][2];
                        }
                        else if (j == 5)
                        {
                            xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][3];
                        }
                        else
                        {
                            xSheet.Cells[i + 2, j + 1] = loopholeInfoDic[loopholeInfoDicKey[i]][j];
                        }
                    }
                }
                excel.DisplayAlerts = false;
                string saveName = GetSystemSecond().ToString();
                string savePath = Application.StartupPath + "\\" + saveName + ".xlsx";
                xBook.SaveAs(savePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                xSheet = null;
                xBook  = null;
                excel.Quit();
                excel = null;
                GC.Collect();//如果不使用这条语句会导致excel进程无法正常退出,使用后正常退出
            }
            catch (Exception ex)
            {
                MessageBox.Show("保存excel出错: " + ex.Message);
                Close();
            }
            this.label1.Text       = "整理完成!";
            this.pictureBox1.Image = Resources._234;
        }
Пример #53
0
        private void button1_Click(object sender, EventArgs e)
        {
            List <int> id_select = new List <int>();


            SqlConnection connecting = new SqlConnection(Program.connection);

            connecting.Open();
            string contact = "SELECT ID  FROM [Documents] WHERE TypeDoc='" + CbTypeDoc.Text + "' AND DateIssued>='" + dateTimeOt.Value + "' AND DateIssued<='" + dateTimeDo.Value + "'";


            SqlCommand cmd = new SqlCommand(contact, connecting);


            SqlDataReader dataReader = cmd.ExecuteReader();

            try
            {
                if (dataReader.HasRows) // если есть данные
                {
                    while (dataReader.Read())
                    {
                        id_select.Add(Convert.ToInt32(dataReader["ID"]));
                    }
                }



                nomera = new int[id_select.Count];
                int k = 0;
                foreach (int i in id_select)
                {
                    nomera[k] = i;


                    k++;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            connecting.Close();



            try
            {
                //Объявляем приложение
                Microsoft.Office.Interop.Excel.Application ex = new Microsoft.Office.Interop.Excel.Application();
                //Отобразить Excel

                //Количество листов в рабочей книге
                ex.SheetsInNewWorkbook = 1;
                //Добавить рабочую книгу
                Microsoft.Office.Interop.Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing);
                //Получаем первый лист документа (счет начинается с 1)
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)ex.Worksheets.get_Item(1);

                string rootFolder = @"D:\";                                                       //Путь
                string fileName   = $"ExportUsers{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"; // название файла чтобы не повторялось

                FileInfo file = new FileInfo(rootFolder + fileName);                              //создаем файл
                                                                                                  // Microsoft.Office.Interop.Excel.Application package  = new Microsoft.Office.Interop.Excel.Application();
                                                                                                  //  package.Visible = true;

                //      string rootFolder = @"D:\"; //Путь

                //            string fileName = "shab";
                //
                //            FileInfo file = new FileInfo(rootFolder + fileName);



                var excelcells = worksheet.get_Range("A1", "Q1");
                excelcells.Merge(Type.Missing);
                excelcells.Value = "Выписка о выданных документах типа'" + CbTypeDoc.Text + "'";
                excelcells.get_Range("A1", "Q1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;


                var excelcells1 = worksheet.get_Range("A2", "Q2");
                excelcells1.Merge(Type.Missing);
                excelcells1.Value = "Период: от '" + dateTimeOt.Value.ToShortDateString() + "' до '" + dateTimeDo.Value.ToShortDateString() + "'";
                excelcells1.get_Range("A2", "Q2").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;



                //  ExcelWorksheet worksheet = package.Worksheets.Add("Дипломы"); //Необязательно
                worksheet.Cells[3, 1].Value  = "Вид документа";
                worksheet.Cells[3, 2].Value  = "Статус документа";
                worksheet.Cells[3, 3].Value  = "Подтверждение утраты";
                worksheet.Cells[3, 4].Value  = "Подтверждение обмена";
                worksheet.Cells[3, 5].Value  = "Подтверждение уничтожения";
                worksheet.Cells[3, 6].Value  = "Серия документа";
                worksheet.Cells[3, 7].Value  = "Номер документа";
                worksheet.Cells[3, 8].Value  = "Дата выдачи документа";
                worksheet.Cells[3, 9].Value  = "Регистрационный номер";
                worksheet.Cells[3, 10].Value = "Дополнительная профессиональная программа";
                worksheet.Cells[3, 11].Value = "Наименование дополнительной профессиональной программы";
                worksheet.Cells[3, 12].Value = "Дата начала обучения";
                worksheet.Cells[3, 13].Value = "Дата окончания обучения";
                worksheet.Cells[3, 14].Value = "Фамилия слушателя";
                worksheet.Cells[3, 15].Value = "Имя слушателя";
                worksheet.Cells[3, 16].Value = "Отчество слушателя";
                worksheet.Cells[3, 17].Value = "Дата рождения";


                SqlConnection connect = new SqlConnection(Program.connection);
                connect.Open();

                //выбираем статус документа
                //    SqlCommand statusdoc = new SqlCommand("SELECT StatusDoc FROM [Documents] WHERE ID =" + nomera[1], connect);
                //    statusdoc.ExecuteScalar();
                //     MessageBox.Show(Convert.ToString(statusdoc.ExecuteScalar()));

                for (int u = 0; u < nomera.Length; u++)
                {
                    //выбираем статус документа
                    SqlCommand statusdoc = new SqlCommand("SELECT StatusDoc FROM [Documents] WHERE ID =" + nomera[u], connect);
                    statusdoc.ExecuteScalar();



                    //выбираем утрату документа
                    SqlCommand utrata = new SqlCommand("SELECT Utrata FROM [Documents] WHERE ID =" + nomera[u], connect);
                    utrata.ExecuteScalar();

                    //выбираем обмен документа
                    SqlCommand obmen = new SqlCommand("SELECT Obmen FROM [Documents] WHERE ID =" + nomera[u], connect);
                    obmen.ExecuteScalar();

                    //выбираем уничтожение документа
                    SqlCommand unichtog = new SqlCommand("SELECT Unichtog FROM [Documents] WHERE ID =" + nomera[u], connect);
                    unichtog.ExecuteScalar();

                    //выбираем серию документа
                    SqlCommand seriesdoc = new SqlCommand("SELECT SeriesDoc FROM [Documents] WHERE ID =" + nomera[u], connect);
                    seriesdoc.ExecuteScalar();

                    //выбираем номер документа
                    SqlCommand numberdoc = new SqlCommand("SELECT NumberDoc FROM [Documents] WHERE ID =" + nomera[u], connect);
                    numberdoc.ExecuteScalar();

                    //выбираем регистрационный номер документа
                    SqlCommand regnumber = new SqlCommand("SELECT RegNumber FROM [Documents] WHERE ID =" + nomera[u], connect);
                    regnumber.ExecuteScalar();

                    //выбираем дату выдачи документа
                    SqlCommand dateissued = new SqlCommand("SELECT DateIssued FROM [Documents] WHERE ID =" + nomera[u], connect);
                    dateissued.ExecuteScalar();

                    //Выбираем группу к которой привязан курс
                    SqlCommand gr = new SqlCommand("SELECT [Group] FROM [Documents] WHERE ID=" + nomera[u], connect);
                    gr.ExecuteScalar();

                    //выбираем ID курса по названию группы
                    SqlCommand course = new SqlCommand("SELECT IDCourse FROM [Group] WHERE NameGroup ='" + gr.ExecuteScalar() + "'", connect);
                    course.ExecuteScalar();

                    //выбираем тип программы( повышение квалификации..)
                    SqlCommand nametype = new SqlCommand("SELECT NameType FROM [TypeProg] WHERE ID=(SELECT Type FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "')", connect);
                    nametype.ExecuteScalar();

                    //выбираем количество часов в курсе
                    SqlCommand kolchas = new SqlCommand("SELECT KolChas FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "'", connect);
                    kolchas.ExecuteScalar();

                    //выбираем наименование программы (медицинская оптика...)
                    SqlCommand nameprog = new SqlCommand("SELECT NameProg FROM [Program] WHERE ID=(SELECT Program FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "')", connect);
                    nameprog.ExecuteScalar();

                    //выбираем ID курса
                    SqlCommand idcourse = new SqlCommand("SELECT Course FROM [NewCourse] WHERE ID='" + course.ExecuteScalar() + "'", connect);
                    idcourse.ExecuteScalar();

                    //выбираем наименование курса
                    SqlCommand namecourse = new SqlCommand("SELECT NameCourse FROM [Course] WHERE IDCourse='" + idcourse.ExecuteScalar() + "'", connect);
                    namecourse.ExecuteScalar();

                    //выбираем дату начала курса
                    SqlCommand datestart = new SqlCommand("SELECT DateStart FROM [NewCourse] WHERE ID=" + course.ExecuteScalar(), connect);
                    datestart.ExecuteScalar();

                    //выбираем дату окончания курса
                    SqlCommand dateend = new SqlCommand("SELECT DateEnd FROM [NewCourse] WHERE ID=" + course.ExecuteScalar(), connect);
                    dateend.ExecuteScalar();

                    //выбираем id студента
                    SqlCommand student = new SqlCommand("SELECT IdStudent FROM [Documents] WHERE ID=" + nomera[u], connect);
                    student.ExecuteScalar();

                    //выбираем фамилию студента
                    SqlCommand surname = new SqlCommand("SELECT Surname FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect);
                    surname.ExecuteScalar();

                    //выбираем имя студента
                    SqlCommand name = new SqlCommand("SELECT Name FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect);
                    name.ExecuteScalar();

                    //выбираем отчество студента
                    SqlCommand patronimyc = new SqlCommand("SELECT Patronymic FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect);
                    patronimyc.ExecuteScalar();

                    //выбираем дату рождения студента
                    SqlCommand dateofbirth = new SqlCommand("SELECT DateOfBirth FROM [Student] WHERE IDStudent=" + student.ExecuteScalar(), connect);
                    dateofbirth.ExecuteScalar();


                    worksheet.Cells[u + 4, 1].Value  = CbTypeDoc.Text;
                    worksheet.Cells[u + 4, 2].Value  = statusdoc.ExecuteScalar();
                    worksheet.Cells[u + 4, 3].Value  = utrata.ExecuteScalar();
                    worksheet.Cells[u + 4, 4].Value  = obmen.ExecuteScalar();
                    worksheet.Cells[u + 4, 5].Value  = unichtog.ExecuteScalar();
                    worksheet.Cells[u + 4, 6].Value  = seriesdoc.ExecuteScalar();
                    worksheet.Cells[u + 4, 7].Value  = numberdoc.ExecuteScalar();
                    worksheet.Cells[u + 4, 8].Value  = dateissued.ExecuteScalar();
                    worksheet.Cells[u + 4, 9].Value  = regnumber.ExecuteScalar();
                    worksheet.Cells[u + 4, 10].Value = nametype.ExecuteScalar();
                    worksheet.Cells[u + 4, 11].Value = nameprog.ExecuteScalar();
                    worksheet.Cells[u + 4, 12].Value = datestart.ExecuteScalar();
                    worksheet.Cells[u + 4, 13].Value = dateend.ExecuteScalar();
                    worksheet.Cells[u + 4, 14].Value = surname.ExecuteScalar();
                    worksheet.Cells[u + 4, 15].Value = name.ExecuteScalar();
                    worksheet.Cells[u + 4, 16].Value = patronimyc.ExecuteScalar();
                    worksheet.Cells[u + 4, 17].Value = dateofbirth.ExecuteScalar();
                }


                var oRng = worksheet.get_Range("A1", "Q1");
                oRng.EntireColumn.AutoFit();



                // закрываем соединение с БД



                //ex.Visible = true;
                // worksheet.SaveAs("otchet");


                SaveFileDialog sfd = new SaveFileDialog();

                //выбираем путь для сохранения
                sfd.InitialDirectory = @"D:\SDO\otchet\";

                string path = "";

                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    path = sfd.FileName;
                    worksheet.SaveAs(path);
                    MessageBox.Show("Файл сохранен");
                }


                connect.Close();
                MessageBox.Show("Файл сформирован");
            }



            catch (Exception ex)
            {
                MessageBox.Show("ошибка" + ex);
            }
        }
Пример #54
0
 private void BtnExport_Click(object sender, EventArgs e)
 {
     try {
         if (dgvMonthlyReports.Rows.Count != 0)
         {
             SaveFileDialog saveFileDialog1 = new SaveFileDialog();
             saveFileDialog1.Filter   = "Excel Files (*.xls)|*.xls";
             saveFileDialog1.FileName = "Doctor Wise Summary(" + DateTime.Now.ToString("dd-MM-yy h.mm.ss tt") + ").xls";
             if (saveFileDialog1.ShowDialog() == DialogResult.OK)
             {
                 PathName = saveFileDialog1.FileName;
                 Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
                 ExcelApp.Application.Workbooks.Add(Type.Missing);
                 ExcelApp.Columns.ColumnWidth = 20;
                 int count = dgvMonthlyReports.Columns.Count;
                 ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1, count]].Merge();
                 ExcelApp.Cells[1, 1] = "DOCTOR WISE SUMMARY";
                 ExcelApp.Cells[1, 1].HorizontalAlignment = HorizontalAlignment.Center;
                 ExcelApp.Cells[1, 1].Font.Size           = 12;
                 ExcelApp.Cells[1, 1].Interior.Color      = Color.FromArgb(153, 204, 255);
                 ExcelApp.Columns.ColumnWidth             = 20;
                 ExcelApp.Cells[2, 1]                     = "From Date";
                 ExcelApp.Cells[2, 1].Font.Size           = 10;
                 ExcelApp.Cells[2, 2]                     = dptMonthly_From.Value.ToString("dd-MM-yyyy");
                 ExcelApp.Cells[2, 2].Font.Size           = 10;
                 ExcelApp.Cells[3, 1]                     = "To Date";
                 ExcelApp.Cells[3, 1].Font.Size           = 10;
                 ExcelApp.Cells[3, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                 ExcelApp.Cells[3, 2]                     = dptMonthly_To.Value.ToString("dd-MM-yyyy");
                 ExcelApp.Cells[3, 2].Font.Size           = 10;
                 ExcelApp.Cells[4, 1]                     = "Running Date";
                 ExcelApp.Cells[4, 1].Font.Size           = 10;
                 ExcelApp.Cells[4, 2]                     = DateTime.Now.ToString("dd-MM-yyyy");
                 ExcelApp.Cells[4, 2].Font.Size           = 10;
                 ExcelApp.Cells[4, 2].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
                 for (int i = 1; i < dgvMonthlyReports.Columns.Count + 1; i++)
                 {
                     ExcelApp.Cells[5, i]                     = dgvMonthlyReports.Columns[i - 1].HeaderText;
                     ExcelApp.Cells[5, i].ColumnWidth         = 25;
                     ExcelApp.Cells[5, i].EntireRow.Font.Bold = true;
                     ExcelApp.Cells[5, i].Interior.Color      = Color.FromArgb(0, 102, 204);
                     ExcelApp.Cells[5, i].Font.Size           = 10;
                     ExcelApp.Cells[5, i].Font.Name           = "Segoe UI";
                     ExcelApp.Cells[5, i].Font.Color          = Color.FromArgb(255, 255, 255);
                     ExcelApp.Cells[5, i].Interior.Color      = Color.FromArgb(0, 102, 204);
                 }
                 for (int i = 0; i <= dgvMonthlyReports.Rows.Count; i++)
                 {
                     try
                     {
                         for (int j = 0; j < dgvMonthlyReports.Columns.Count; j++)
                         {
                             ExcelApp.Cells[i + 6, j + 1] = dgvMonthlyReports.Rows[i].Cells[j].Value.ToString();
                             ExcelApp.Cells[i + 6, j + 1].BorderAround(true);
                             ExcelApp.Cells[i + 6, j + 1].Borders.Color = Color.FromArgb(0, 102, 204);
                             ExcelApp.Cells[i + 6, j + 1].Font.Size     = 8;
                         }
                     }
                     catch { }
                 }
                 ExcelApp.ActiveWorkbook.SaveAs(PathName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
                 ExcelApp.ActiveWorkbook.Saved = true;
                 ExcelApp.Quit();
                 checkStr = "1";
                 MessageBox.Show("Successfully Exported to Excel", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
             }
         }
         else
         {
             MessageBox.Show("No records found,please change the date and try again!..", "Failed ", MessageBoxButtons.OK, MessageBoxIcon.Error);
         }
     }
     catch (Exception ex)
     { MessageBox.Show(ex.Message, "Error!...", MessageBoxButtons.OK, MessageBoxIcon.Error); }
 }
        /// <summary>
        /// 导出Excel 的方法
        /// </summary>
        private void tslExport_Excel(string fileName, DataGridView myDGV)
        {
            string         saveFileName = "";
            SaveFileDialog saveDialog   = new SaveFileDialog();

            saveDialog.DefaultExt = "xls";
            saveDialog.Filter     = "Excel文件|*.xls";
            saveDialog.FileName   = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0)
            {
                return;                                //被点了取消
            }
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象,可能您的机子未安装Excel");
                return;
            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook  workbook  = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1

            Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range("A1", "Z" + (myDGV.SelectedRows.Count + 10));         //把Execl设置问文本格式
            range.NumberFormatLocal = "@";
            //写入标题
            for (int i = 1; i < myDGV.ColumnCount; i++)
            {
                worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
            }
            //写入数值
            int s = 0;

            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                if (Convert.ToBoolean(myDGV.Rows[r].Cells[0].Value))
                {
                    for (int i = 1; i < myDGV.ColumnCount; i++)
                    {
                        worksheet.Cells[s + 2, i] = myDGV.Rows[r].Cells[i].Value;
                    }
                    System.Windows.Forms.Application.DoEvents();
                    s++;
                }
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            Microsoft.Office.Interop.Excel.Range rang = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[myDGV.Rows.Count + 2, 2]);
            rang.NumberFormat = "000000000000";

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    //fileSaved = true;
                }
                catch
                {
                    //fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开!\n");
                }
            }
            //else
            //{
            //    fileSaved = false;
            //}
            xlApp.Quit();
            GC.Collect();//强行销毁
            MessageBox.Show(fileName + ",保存成功", "提示", MessageBoxButtons.OK);
        }
Пример #56
0
        private void ExportToExcel(DataTable dt)
        {
            Microsoft.Office.Interop.Excel.Application oexcel = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                object misValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Workbook  obook  = oexcel.Workbooks.Add(misValue);
                Microsoft.Office.Interop.Excel.Worksheet osheet = new Microsoft.Office.Interop.Excel.Worksheet();
                osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets["Sheet1"];
                int columnsCount;

                if (dt == null || (columnsCount = dt.Columns.Count) == 0)
                {
                    throw new Exception("ExportToExcel: Null or empty input table!\n");
                }

                object[] Header = new object[columnsCount];

                for (int i = 0; i < 7; i++)
                {
                    Header[i] = dt.Columns[i].ColumnName;
                }

                Header[6] = string.Empty;
                Header[7] = "Total  Work Time";
                Header[8] = string.Empty;

                int hours = 6;
                for (var i = 6; i <= columnsCount - 1; i++)
                {
                    Header[i] = hours + "h";
                    hours++;
                }

                Microsoft.Office.Interop.Excel.Range HeaderRange = osheet.get_Range((Microsoft.Office.Interop.Excel.Range)
                                                                                        (osheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)
                                                                                        (osheet.Cells[1, columnsCount - 2]));
                HeaderRange.Value          = Header;
                HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
                HeaderRange.Font.Bold      = true;

                int rowsCount = dt.Rows.Count;
                object[,] Cells = new object[rowsCount, columnsCount];

                for (int j = 0; j < rowsCount; j++)
                {
                    for (int i = 0; i < columnsCount - 2; i++)
                    {
                        Cells[j, i] = dt.Rows[j][i];
                    }
                }

                osheet.get_Range((Microsoft.Office.Interop.Excel.Range)(osheet.Cells[2, 1]),
                                 (Microsoft.Office.Interop.Excel.Range)
                                     (osheet.Cells[rowsCount + 1, columnsCount])).Value = Cells;
                oexcel.Columns.AutoFit();

                string filePath = string.Empty;

                var save_file_dialog = new SaveFileDialog();
                save_file_dialog.FileName   = "Efficiency " + DateTime.Now.Date.ToString("dd'-'MM'-'yyyy");
                save_file_dialog.DefaultExt = ".xlsx";
                save_file_dialog.Filter     = "Excel File|*.xlsx|All Files|*.*";

                if (save_file_dialog.ShowDialog() == DialogResult.OK)
                {
                    filePath = save_file_dialog.FileName;
                    oexcel.ActiveWorkbook.SaveCopyAs(filePath);
                    oexcel.ActiveWorkbook.Saved = true;
                    oexcel.Quit();
                }
                save_file_dialog.Dispose();
            }
            catch (Exception ex)
            {
                throw new Exception("ExportToExcel: \n" + ex.Message);
            }
        }
        private void ExportDepartmentToExcel()
        {
            int intRowCounter;
            int intRowNumberOfRecords;
            int intColumnCounter;
            int intColumnNumberOfRecords;

            // Creating a Excel object.
            Microsoft.Office.Interop.Excel._Application excel     = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook    workbook  = excel.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet   worksheet = null;

            try
            {
                worksheet = workbook.ActiveSheet;

                worksheet.Name = "OpenOrders";

                int cellRowIndex    = 1;
                int cellColumnIndex = 1;
                intRowNumberOfRecords    = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Rows.Count;
                intColumnNumberOfRecords = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Columns.Count;

                for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
                {
                    worksheet.Cells[cellRowIndex, cellColumnIndex] = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Columns[intColumnCounter].ColumnName;

                    cellColumnIndex++;
                }

                cellRowIndex++;
                cellColumnIndex = 1;

                //Loop through each row and read value from each column.
                for (intRowCounter = 0; intRowCounter < intRowNumberOfRecords; intRowCounter++)
                {
                    for (intColumnCounter = 0; intColumnCounter < intColumnNumberOfRecords; intColumnCounter++)
                    {
                        worksheet.Cells[cellRowIndex, cellColumnIndex] = TheDesignDepartmentProductivityDataSet.designdepartmentproductivity.Rows[intRowCounter][intColumnCounter].ToString();

                        cellColumnIndex++;
                    }
                    cellColumnIndex = 1;
                    cellRowIndex++;
                }

                //Getting the location and file name of the excel to save from user.
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.Filter      = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
                saveDialog.FilterIndex = 1;

                saveDialog.ShowDialog();

                workbook.SaveAs(saveDialog.FileName);
                MessageBox.Show("Export Successful");
            }
            catch (System.Exception ex)
            {
                TheEventLogClass.InsertEventLogEntry(DateTime.Now, "Blue Jay ERP // Design Employee Productivity // Export Department to Excel " + ex.Message);

                MessageBox.Show(ex.ToString());
            }
            finally
            {
                excel.Quit();
                workbook = null;
                excel    = null;
            }
        }
Пример #58
0
        private void Export2Excel()
        {
            DateTime tgl;
            var      excelApp = new Microsoft.Office.Interop.Excel.Application();

            excelApp.Visible = true;
            Microsoft.Office.Interop.Excel.Workbook  excelbk      = excelApp.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)excelbk.Worksheets["Sheet1"];

            int worksheetcol = WORKSHEETSTARTCOL;
            int jmlCol       = dgvGrid.Columns.Count;

            for (int colCount = 0; colCount < jmlCol; colCount++)
            {
                Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet1.Cells[WORKSHEETSTARTROW, worksheetcol];

                if (colCount > 1 && colCount < (jmlCol - JML_COL_TETAP))
                {
                    try
                    {
                        tgl            = DateTime.Parse(dgvGrid.Columns[colCount].Name);
                        xlRange.Value2 = tgl.ToString("dd");
                        //xlRange.Value2 = tgl.ToString("dd-MMM-yyyy") + "(" + tgl.DayOfWeek.ToString().ToUpper().Substring(0, 3) + ")";
                    }
                    catch
                    {
                    }
                }
                else
                {
                    xlRange.Value2 = dgvGrid.Columns[colCount].HeaderText;
                }

                worksheetcol += 1;
            }

            int worksheetRow = WORKSHEETSTARTROW + 1;

            for (int rowCount = 0; rowCount < dgvGrid.Rows.Count; rowCount++)
            {
                if (dgvGrid.Rows[rowCount].IsNewRow)
                {
                    continue;
                }

                worksheetcol = WORKSHEETSTARTCOL;
                for (int colCount = 0; colCount < jmlCol; colCount++)
                {
                    Microsoft.Office.Interop.Excel.Range xlRange = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet1.Cells[worksheetRow, worksheetcol];
                    xlRange.Value2 = dgvGrid.Rows[rowCount].Cells[colCount].Value.ToString();
                    //xlRange.Font.Background = dataGridView1.Rows[rowCount].Cells[colCount].Style.BackColor;
                    //xlRange.Font.Color = dgvGrid.Rows[rowCount].Cells[colCount].Style.ForeColor.ToArgb();
                    if (colCount > 1 && colCount < (jmlCol - JML_COL_TETAP))
                    {
                        if (chkUseColor.Checked)
                        {
                            xlRange.Value2         = "";
                            xlRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(dgvGrid.Rows[rowCount].Cells[colCount].Style.BackColor);
                        }
                        else
                        {
                            switch (dgvGrid.Rows[rowCount].Cells[colCount].Value.ToString())
                            {
                            case "0":
                                xlRange.Value2 = STAT_ALPA;
                                break;

                            case "1":
                                xlRange.Value2 = STAT_KERJA;
                                break;

                            case "2":
                                xlRange.Value2 = STAT_TIDAKABSEN;
                                break;

                            case "3":
                                xlRange.Value2 = STAT_TELAT;
                                break;

                            case "4":
                                xlRange.Value2 = STAT_PULANGCEPAT;
                                break;

                            case "5":
                                xlRange.Value2 = STAT_OFF;
                                break;

                            case "6":
                                xlRange.Value2 = STAT_IJIN;
                                break;

                            case "7":
                                xlRange.Value2 = STAT_OFFMASUK;
                                break;

                            case "8":
                                xlRange.Value2 = STAT_IJIN;
                                break;
                            }
                        }
                    }
                    //if (dgvGrid.Rows[rowCount].Cells[colCount].Style.Font != null)
                    //{
                    //    xlRange.Font.Bold = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.Bold;
                    //    xlRange.Font.Italic = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.Italic;
                    //    xlRange.Font.Underline = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.Underline;
                    //    xlRange.Font.FontStyle = dgvGrid.Rows[rowCount].Cells[colCount].Style.Font.FontFamily;
                    //}
                    worksheetcol += 1;
                }
                worksheetRow += 1;
            }
        }
        public void ExportToExcel(List <PrintList> print)
        {
            // Load Excel application
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            // Create empty workbook
            excel.Workbooks.Add();

            // Create Worksheet from active sheet
            Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;

            // I created Application and Worksheet objects before try/catch,
            // so that i can close them in finnaly block.
            // It's IMPORTANT to release these COM objects!!
            try
            {
                // ------------------------------------------------
                // Creation of header cells
                // ------------------------------------------------
                workSheet.Cells[1, "A"] = translationText.Get("excelA");
                workSheet.Cells[1, "B"] = translationText.Get("excelB");
                workSheet.Cells[1, "C"] = translationText.Get("excelC");
                workSheet.Cells[1, "D"] = translationText.Get("excelD");
                workSheet.Cells[1, "E"] = translationText.Get("excelE");

                // ------------------------------------------------
                // Populate sheet with some real data from "cars" list
                // ------------------------------------------------
                int row = 2; // start row (in row 1 are header cells)
                foreach (PrintList prn in print)
                {
                    workSheet.Cells[row, "A"] = prn.EmployeeName;
                    workSheet.Cells[row, "B"] = prn.DateTime;
                    workSheet.Cells[row, "C"] = prn.CtrlID;
                    workSheet.Cells[row, "D"] = prn.Ordinal;
                    workSheet.Cells[row, "E"] = prn.DepartmentName;

                    row++;
                }

                // Apply some predefined styles for data to look nicely :)
                workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);

                // Define filename
                string fileName = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));

                // Save this data as a file
                workSheet.SaveAs(fileName);

                // Display SUCCESS message
                Console.WriteLine(string.Format("The file '{0}' is saved successfully!", fileName));
                //MessageBox.Show(string.Format("The file '{0}' is saved successfully!", fileName));
            }
            catch (Exception exception)
            {
                Console.WriteLine("Exception", "There was a PROBLEM saving Excel file!\n" + exception.Message);
                //MessageBox.Show("Exception", "There was a PROBLEM saving Excel file!\n" + exception.Message, MessageBoxButtons.OK, essageBoxIcon.Error);
            }
            finally
            {
                // Quit Excel application
                excel.Quit();

                // Release COM objects (very important!)
                if (excel != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                }

                if (workSheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                }

                // Empty variables
                excel     = null;
                workSheet = null;

                // Force garbage collector cleaning
                GC.Collect();
            }
        }
        public void KetXuatLuongNV(DataTable dt, string SheetName, string title)
        {
            //Tạo các đối tượng trong Excel
            Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbooks   oBooks;
            Microsoft.Office.Interop.Excel.Sheets      oSheets;
            Microsoft.Office.Interop.Excel.Workbook    book;
            Microsoft.Office.Interop.Excel.Worksheet   sheet;

            //tạo mới 1 Exel WorkBook
            oExcel.Visible       = true;
            oExcel.DisplayAlerts = false;
            oExcel.Application.SheetsInNewWorkbook = 1;
            oBooks = oExcel.Workbooks;

            book       = (Microsoft.Office.Interop.Excel.Workbook)(oExcel.Workbooks.Add(Type.Missing));
            oSheets    = book.Worksheets;
            sheet      = (Microsoft.Office.Interop.Excel.Worksheet)oSheets.get_Item(1);
            sheet.Name = SheetName;
            //tạo phần đầu nếu muốn
            Microsoft.Office.Interop.Excel.Range head = sheet.get_Range("A1", "I1");
            head.MergeCells          = true;
            head.Value2              = title;
            head.Font.Bold           = true;
            head.Font.Name           = "Tahoma";
            head.Font.Size           = "18";
            head.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
            //Tạo tiêu đề cột
            Microsoft.Office.Interop.Excel.Range cl1 = sheet.get_Range("A3", "A3");
            cl1.Value2      = "Mã NV";
            cl1.ColumnWidth = 18.5;

            Microsoft.Office.Interop.Excel.Range cl2 = sheet.get_Range("B3", "B3");
            cl2.Value2      = "Họ tên";
            cl2.ColumnWidth = 25.5;

            Microsoft.Office.Interop.Excel.Range cl3 = sheet.get_Range("C3", "C3");
            cl3.Value2      = "HSL";
            cl3.ColumnWidth = 25.5;

            Microsoft.Office.Interop.Excel.Range cl4 = sheet.get_Range("D3", "D99");
            cl4.Value2       = "";
            cl4.NumberFormat = "dd/MM/yyyy";
            cl4.ColumnWidth  = 33.5;

            Microsoft.Office.Interop.Excel.Range cl5 = sheet.get_Range("E3", "E3");
            cl5.Value2      = "BHXH";
            cl5.ColumnWidth = 16;

            Microsoft.Office.Interop.Excel.Range cl6 = sheet.get_Range("F3", "F3");
            cl6.Value2 = "BHYT";

            cl6.ColumnWidth = 16.0;

            Microsoft.Office.Interop.Excel.Range cl7 = sheet.get_Range("G3", "G3");
            cl7.Value2      = "BHTN";
            cl7.ColumnWidth = 16;

            Microsoft.Office.Interop.Excel.Range cl8 = sheet.get_Range("H3", "H3");
            cl8.Value2      = "Tổng phụ cấp";
            cl8.ColumnWidth = 16;

            Microsoft.Office.Interop.Excel.Range cl9 = sheet.get_Range("I3", "I3");
            cl9.Value2      = "Lương thực lãnh";
            cl9.ColumnWidth = 30;
            Microsoft.Office.Interop.Excel.Range rowHead = sheet.get_Range("A3", "I3");
            rowHead.Font.Bold = true;
            //kẻ viền
            rowHead.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            //thiết lập màu nền
            rowHead.Interior.ColorIndex = 15;
            rowHead.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

            //tạo mảng đối tượng để lưu trữ toàn bộ dữ liệu trong Database
            //vì dữ liệu đc gán vào các Cell trong Excel phải thông qua object thuần
            object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
            //chuyển dữ liệu từ Database vào mảng đối tượng
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                DataRow dr = dt.Rows[r];
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    arr[r, c] = dr[c];
                }
            }
            //thiết lập vùng điền dữ liệu
            int rowStart    = 4;
            int columnStart = 1;
            int rowEnd      = rowStart + dt.Rows.Count - 1;
            int columnEnd   = dt.Columns.Count;

            //Ô bắt đầu điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c1 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowStart, columnStart];
            //Ô kết thúc điền dữ liệu
            Microsoft.Office.Interop.Excel.Range c2 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowEnd, columnEnd];
            //Lấy về vùng điền dữ liệu
            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(c1, c2);
            //Điền dữ liệu vào vùng đã thiết lập
            range.Value2 = arr;

            // Kẻ viền
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.Constants.xlSolid;
            // Căn giữa cột STT
            Microsoft.Office.Interop.Excel.Range c3 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowEnd, columnStart];
            Microsoft.Office.Interop.Excel.Range c4 = sheet.get_Range(c1, c3);
            sheet.get_Range(c3, c4).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        }